Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Escaping Single Quote in Hive

avatar
Explorer

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

3 REPLIES 3

avatar
Champion

@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;

 

avatar
Explorer

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? 

avatar
Explorer