Support Questions

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

how to insert semicolon in hive table

avatar
Expert Contributor

Hi,

I have data with semicolon as part of it.

I have to insert such data in to hive table.

ex:

create table semicolon(a string);

insert into semicolon values ('Mozilla/5.0 (iPhone; CPU iPhone OS 5_0)');

I am getting below error.

mismatched input '/' expecting ) near '...

could you please help

1 ACCEPTED SOLUTION

avatar
Master Guru

@Mark

As you are trying to insert values having semicolon(;) in it, hive thinks semi colon will be end of statement even if you escape semicolon with back slash(\).

To insert values with semicolon use unicode for semicolon \u003B in your insert values statement and back slash to escape /,space,).

Insert statement:-

hive> insert into semicolon values ('Mozilla\/5\.0\ \(iPhone\u003B\ CPU\ iPhone\ OS\ 5_0\)');
hive> select * from semicolon;
+------------------------------------------+--+
|                    a                     |
+------------------------------------------+--+
| Mozilla/5.0 (iPhone; CPU iPhone OS 5_0)  |
+------------------------------------------+--+

(or)

keep your data file into HDFS directory and create semicolon table with string datatype, pointing to that HDFS directory.

Semicolon Table reading from HDFS directory:-

hive> select * from semicolon;
+------------------------------------------+--+
|                    a                     |
+------------------------------------------+--+
| Mozilla/5.0 (iPhone; CPU iPhone OS 5_0)  |
+------------------------------------------+--+

Results will be same from both ways.

View solution in original post

7 REPLIES 7

avatar
Master Guru

@Mark

As you are trying to insert values having semicolon(;) in it, hive thinks semi colon will be end of statement even if you escape semicolon with back slash(\).

To insert values with semicolon use unicode for semicolon \u003B in your insert values statement and back slash to escape /,space,).

Insert statement:-

hive> insert into semicolon values ('Mozilla\/5\.0\ \(iPhone\u003B\ CPU\ iPhone\ OS\ 5_0\)');
hive> select * from semicolon;
+------------------------------------------+--+
|                    a                     |
+------------------------------------------+--+
| Mozilla/5.0 (iPhone; CPU iPhone OS 5_0)  |
+------------------------------------------+--+

(or)

keep your data file into HDFS directory and create semicolon table with string datatype, pointing to that HDFS directory.

Semicolon Table reading from HDFS directory:-

hive> select * from semicolon;
+------------------------------------------+--+
|                    a                     |
+------------------------------------------+--+
| Mozilla/5.0 (iPhone; CPU iPhone OS 5_0)  |
+------------------------------------------+--+

Results will be same from both ways.

avatar
Expert Contributor

@Shu

Thank you

avatar
Cloudera Employee

@Shu if I want to insert a question mark (?) to a column with datatype as int then what should be syntax?

avatar
Master Guru
@sadapa

Try with \u003F(unicode for ?)

hive> select('Hi How Are You\u003F');
+------------------+--+
|       _c0        |
+------------------+--+
| Hi How Are You?  |
+------------------+--+

avatar
Cloudera Employee

@Shu

Thank you for the update.


1) I have created two tables, one for datatype int and one for string.


 create table test001_int(ida int);


 create table test001_string(ida string);


2) When I try to insert " ? " to string table I am able to insert successfully 


insert into test001_string values('?');


select * from test001_string;
+---------------------+--+
| test001_string.ida  |
+---------------------+--+
| ?                   |
+---------------------+--+






3) When I try to insert " ? " to int table I am getting below Error


insert into test001_int values(?);
Error: Error while compiling statement: FAILED: ParseException line 1:31 cannot recognize input near '?' ')' '<EOF>' in value row constructor (state=42000,code=40000)




insert into test001_int values('?');


select * from test001_int;
+------------------+--+
| test001_int.ida  |
+------------------+--+
| NULL             |
+------------------+--+




insert into test001_int values('\u003F');


select * from test001_int;
+------------------+--+
| test001_int.ida  |
+------------------+--+
| NULL             |
| NULL             |
+------------------+--+


So my question is how can I insert " ? " in column with INT datatype (NOT string) and for select query I am getting NULL is there any different way to display " ? " in select query.

avatar
Expert Contributor
@sadapa

you can never insert a "?" in to a column which has a datatype int.

because you can never find a number as "?", and hive knows it.

I am not sure why you want to do that, but if you want to still convert a "?" in to a number, which you want to change it later, you can try ascii()

avatar
New Contributor

For some reason, the unicode \u003B translates to ) for me.

Beeline version 1.1.0-cdh5.13.3 by Apache Hive 0: jdbc:hive2://gbrdsr000002995.intranet.barc> select '\u003B' as col;

+------+--+

| col |

+------+--+

| ) |

+------+--+

I have tried "\u0059" instead to replace semicolon (;) which works for me

0: jdbc:hive2://gbrdsr000002995.intranet.barc> select '\u0059' as col;

+------+--+

| col |

+------+--+

| ; |

+------+--+