Reply
New Contributor
Posts: 7
Registered: ‎04-26-2018

Escaping Single Quote in Hive

[ Edited ]

How, exactly, should a single quote ' be escaped in an Hive SQL insert statement?

 

In the Hive editor in HUE I jave tried \' and 2 single quotes '' which gives the following results when queried so neither of these look to be correct:

 

35 We Rmyoyi''w Rymih

35 We Rmyoyi\'w Rymih

 

The JDBC driver (org.apache.hive.jdbc.HivePreparedStatement) is escaping single quotes by doing 

 

  public void setString(int parameterIndex, String x) throws SQLException {
     x=x.replace("'", "\\'");
     this.parameters.put(parameterIndex,"'"+x+"'");
  }

 

however this is also results as records being inserted as which is not correct either.

 

35 We Rmyoyi\'w Rymih

Posts: 474
Topics: 14
Kudos: 77
Solutions: 41
Registered: ‎09-02-2016

Re: Escaping Single Quote in Hive

@Alan-H

 

 

 

There could be different ways, but I tried the below steps and it is working for me

 

Step1: using select class with hardcoded value

create table default.mytest(col1 string, col2 int);
insert into default.mytest
select 'For testing single quote\'s', 1;
insert into default.mytest
select 'For testing double quote\"s', 2;

select * from default.mytest;

Step2: using select class by passing value in parameter

set hivevar:col1 = 'For testing single quote\'s';
set hivevar:col2 = 3;

insert into default.mytest
select ${hivevar:col1}, ${hivevar:col2};

select * from default.mytest;


Step3: using select class by passing value in parameter

set hivevar:col1 = 'For testing double quote\"s';
set hivevar:col2 = 4;

insert into default.mytest
select ${hivevar:col1}, ${hivevar:col2};

select * from default.mytest;

Step4:

drop table default.mytest;

 

New Contributor
Posts: 7
Registered: ‎04-26-2018

Re: Escaping Single Quote in Hive

Any idea why 

 

insert into default.mytest
select 'For testing single quote\'s', 1;

works and 

 

insert into default.mytest
values ('For testing single quote\'s', 1);

doesn't work? 

Highlighted
New Contributor
Posts: 7
Registered: ‎04-26-2018

Re: Escaping Single Quote in Hive

Announcements