Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Escaping Single Quote in Hive

Escaping Single Quote in Hive

New Contributor

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

Re: Escaping Single Quote in Hive

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;

 

Highlighted

Re: Escaping Single Quote in Hive

New Contributor

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? 

Re: Escaping Single Quote in Hive

New Contributor