question Re: how to insert semicolon in hive table in Support Questions

how to insert semicolon in hive table

mark_hadoop — Sun, 18 Feb 2018 23:09:54 GMT

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

Re: how to insert semicolon in hive table

Shu_ashu — Mon, 19 Feb 2018 00:16:17 GMT

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

Re: how to insert semicolon in hive table

mark_hadoop — Tue, 20 Feb 2018 05:25:59 GMT

@Shu

Thank you

Re: how to insert semicolon in hive table

sadapa — Wed, 03 Oct 2018 08:21:23 GMT

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

Re: how to insert semicolon in hive table

Shu_ashu — Wed, 03 Oct 2018 08:37:35 GMT

@sadapa

Try with \u003F(unicode for ?)

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

Re: how to insert semicolon in hive table

sadapa — Thu, 04 Oct 2018 02:09:05 GMT

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

Re: how to insert semicolon in hive table

mark_hadoop — Tue, 16 Oct 2018 20:35:38 GMT

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

Re: how to insert semicolon in hive table

manhar_seewooth — Fri, 28 Dec 2018 21:32:51 GMT

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 |

+------+--+

| ; |

+------+--+