Load data into Hive table from a file on local system

To Load data from a csv (it can be pipe,tab,comma seprated ) file :

Step 1 : Create a table with delimiter as given in file

Command :

Create table [schema_name.table_name] (

id int ,

name string,

load_date string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

Here we are creating a table which would accept data in ',' format.

Step 2 : Create/Get file on local FS and run below command -

hive -e "LOAD DATA LOCAL INPATH '[fullpath/file_name.csv] ' OVERWRITE INTO TABLE schema_name.table_name ;"

Above command will delete old data (if any) and insert new data into table .

In case you need to insert data into a partition table , then you can first load to a temp table and then run below command :

hive -e "insert overwrite table [schema_name.new_table] PARTITION(date) select * from [schema_name.table_name];"

• Here new_table is the destination partition table , partitioned on date and table_name is the table which was loaded from file as in above steps .