how to insert semicolon in hive table

[#1]

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

[#2 · Accepted solution]

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

[#3]

@Shu

Thank you

[#4]

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

[#5]

@sadapa

Try with \u003F(unicode for ?)

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

[#6]

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

[#7]

@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()

[#8]

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 |

+------+--+

| ; |

+------+--+