Insert values from file to an existing table on hive

Question

I am new to hadoop ecosystem. I was trying to create hive table from CSV file using the below query.

CREATE EXTERNAL TABLE IF NOT EXISTS proxy_data(
  date_time TIMESTAMP,time_taken INT, c_ip STRING,
  sc_status INT, s_action STRING, sc_bytes INT,
  cs_bytes INT, cs_method STRING, cs_uri STRING,
  cs_host STRING, uri_port INT, uri_path STRING,
  uri_query STRING, username STRING, auth STRING,
  supplier_name STRING, content_type STRING, referer STRING,
  user_agent STRING, filter_result STRING, categories STRING,
  x_virus_id STRING, proxy_ip STRING
)
COMMENT 'Proxy logs' 
LOCATION '/user/admin'
tblproperties ("skip.header.line.count"="1");

This query actually created a table proxy_data and populated the values present in the csv files which are located in the prescribed location.

Now, I want to append values from another set of CSVs to the same table(it should skip the headings present in the csv file). I checked for various solutions, but nothing is meeting my need.

What about just _moving_ the new files to the existing directory? Or creating a partitioned table, and adding a new directory (= partition with arbitrary key)? — Samson Scharfrichter, Mar 28 '18 at 10:12

score 2 · Answer 1 · answered Mar 28 '18 at 11:32

2

You can add a property to your table which will skip the first row of csv. "skip.header.line.count"="1"

In your case,

Alter Table proxy_data SET TBLPROPERTIES ("skip.header.line.count"="1").

answered Mar 28 '18 at 11:32

Mahesh Mogal

638
9
13

Satya Pavan · Accepted Answer · 2018-03-28T23:42:03.137

You may follow this approach:

Create a staging table (temporary table) with this property - skip.header.line.count=1
Create a main table with same schema (no need to use skip.header.line.count clause in this table).
Every time you have a new file, load overwrite into the staging table

Then, load append staging table's data into the main table.

create table <my_table_stg>(col1 data_type1, col2, data_type2...)
row format delimited fields terminated by ','
tblproperties ("skip.header.line.count"="1");

create table <my_table>(col1 data_type1, col2, data_type2...);

load data inpath '/file/location/my_file.csv' overwrite into table <my_table_stg>;

insert into table <my_table> select * from <my_table_stg>;

P.S: Your table syntax doesn't haverow format delimited clause. Please make sure you add it as shown above

This sort of helped. I was able to create a temporary table to which I populated the data overwriting each time and later on selected the data from the temporary data and inserted into my existing table. And like you mentioned I added "ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' " line to the code and it worked like charm. — Akash, Mar 29 '18 at 05:14

HISI · Answer 3 · 2018-03-28T10:28:47.520

0

Could you try this:

hive>  LOAD DATA LOCAL INPATH '/home/yourcsvfile.csv' INTO TABLE proxy_data;

edited Mar 28 '18 at 10:28

answered Mar 28 '18 at 09:41

HISI

4,557
4
35
51

What about the header row? – Ben Watson Mar 28 '18 at 09:43
@Ben Watson I think it's gonna just retrieve values from CSV – HISI Mar 28 '18 at 09:46
As OP says, "it should skip the headings present in the csv file". This answer won't do that. – Ben Watson Mar 28 '18 at 09:47
https://stackoverflow.com/questions/20813355/skip-first-line-of-csv-while-loading-in-hive-table https://stackoverflow.com/questions/15751999/hive-external-table-skip-first-row/15753145#15753145 – Ben Watson Mar 28 '18 at 09:50
1

When I use this, it is not appending, it is overwriting the old data with the new one. – Akash Mar 28 '18 at 12:27

Insert values from file to an existing table on hive

3 Answers3