Hive Insertion showing error

Question

i have created a table in hive .

create table demo(no int, name string)

INSERT demo values (1,'haris')

But when iam inserting values in that table it showing the following error .

FAILED: Parse Error: line 2:0 mismatched input 'INSERT' expecting EOF near ')'

and tried this also

INSERT OVERWRITE TABLE demo
PARTITION (no = 1, name = 'Hana')

FAILED: Parse Error: line 4:0 cannot recognize input near 'PARTITION' '(' 'country' in select clause

Is any 1 pls help me? How can i do insertion in hive. If possible please tell me about external files also?

Answer 1

Do this :

hive> LOAD DATA INPATH '/path_of_the_file_to_insert' INTO TABLE demo;

By external files do you mean external tables??Please format your question properly so that it is visible properly. Thank you.

---

In response to your comment :

Hive does not provide record-level insert. You can generate new tables from queries or output query results to files. So I don't think something like

insert into table_name (id, name) VALUES (12,"xyz);

is possible.

When you talk about insertion in Hive, you have 2 options :

Loading files into tables
Inserting data into Hive Tables from queries

Load operations are just copy/move operations that move datafiles into locations corresponding to Hive tables and Insertion implies inserting data into tables by using the insert clause.

Syntax of LOAD :

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

Example :

LOAD DATA INPATH '/path/to/file/test.csv' INTO TABLE table_name;

Syntax of INSERT :

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

Example :

INSERT OVERWRITE TABLE table2 SELECT * FROM table1;

External Tables :

External tables are just like any other table in Hive with the exception that in external tables you yourself control the creation and deletion of the data. The location of the external data is specified at table creation time. The base files doesn't get copied into the Hive table directory.

CREATE EXTERNAL TABLE external_table (dummy STRING) LOCATION '/user/tom/external_table';
LOAD DATA INPATH '/user/tom/data.txt' INTO TABLE external_table;

HTH