HIve CLI doesn't support MySql style data import to tables

0
votes

Why can't we import data into hive CLI as following, The hive_test table has user, comments columns.

insert into table hive_test (user, comments) 
value ("hello", "this is a test query");

Hive throws following exception in hive CLI

FAILED: ParseException line 1:28 cannot recognize input near '(' 'user' ',' in select clause

I don't want to import the data through csv file like following for a testing perpose.

load data local inpath '/home/hduser/test_data.csv' into table hive_test;
2
sqlhadoophive

2 Answers

4
votes

It's worth noting that Hive advertises "SQL-like" syntax, rather than actual SQL syntax. There's no particular reason to think that pure SQL queries will actually run on Hive. HiveQL's DML is documented here on the Wiki, and does not support the column specification syntax or the VALUES clause. However, it does support this syntax:

INSERT INTO TABLE tablename1 SELECT ... FROM ...

Extrapolating from these test queries, you might be able to get something like the following to work:

INSERT INTO TABLE hive_test SELECT 'hello', 'this is a test query' FROM src LIMIT 1

However, it does seem that Hive is not really optimized for this small-scale data manipulation. I don't have a Hive instance to test any of this on.

0
votes

I think, it is because user is a built-in (Reserved) keyword.

Try this:

insert into table hive_test ("user", comments) 
value ('hello', 'this is a test query');