Hadoop/Hive : Loading data from .csv on a local machine

Question

As this is coming from a newbie...

I had Hadoop and Hive set up for me, so I can run Hive queries on my computer accessing data on AWS cluster. Can I run Hive queries with .csv data stored on my computer, like I did with MS SQL Server?

How do I load .csv data into Hive then? What does it have to do with Hadoop and which mode I should run that one?

What settings I should care about so that if I did something wrong I can always go back and run queries on Amazon without compromising what was set up for me earlier?

score 58 · Answer 1 · edited Nov 08 '17 at 14:16

58

Let me work you through the following simple steps:

Steps:

First, create a table on hive using the field names in your csv file. Lets say for example, your csv file contains three fields (id, name, salary) and you want to create a table in hive called "staff". Use the below code to create the table in hive.

hive> CREATE TABLE Staff (id int, name string, salary double) row format delimited fields terminated by ',';

Second, now that your table is created in hive, let us load the data in your csv file to the "staff" table on hive.

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

Lastly, display the contents of your "Staff" table on hive to check if the data were successfully loaded

hive> SELECT * FROM Staff;

Thanks.

edited Nov 08 '17 at 14:16

Piotr Findeisen

19,480
2
52
82

answered Sep 26 '15 at 08:21

Adewole Kayode

735
6
7

2

There should be double quotes here `terminated by ",";` – deepdive Nov 22 '16 at 05:39
5

Setting delimiter during table creation is not intuitive. I expect to be able to set delimiter at the time of loading data from particular file. What if new data file has different delimiter? – munikarmanish Nov 24 '17 at 06:04
can i try this from any other system, not from cluster or edge node? I mean any other linux box not part of hdfs cluster. If yes, what am I supposed to do? – Jaraws Apr 01 '21 at 16:23
is overwrite into table required? – Bharath Ram Sep 03 '21 at 04:15

score 17 · Answer 2 · edited Oct 11 '13 at 20:49

if you have a hive setup you can put the local dataset directly using Hive load command in hdfs/s3.

You will need to use "Local" keyword when writing your load command.

Syntax for hiveload command

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

Refer below link for more detailed information. https://cwiki.apache.org/confluence/display/Hive/LanguageManual%20DML#LanguageManualDML-Loadingfilesintotables

score 15 · Answer 3 · edited Jul 05 '18 at 12:15

15

There is another way of enabling this,

use hadoop hdfs -copyFromLocal to copy the .csv data file from your local computer to somewhere in HDFS, say '/path/filename'
enter Hive console, run the following script to load from the file to make it as a Hive table. Note that '\054' is the ascii code of 'comma' in octal number, representing fields delimiter.

CREATE EXTERNAL TABLE table name (foo INT, bar STRING)
 COMMENT 'from csv file'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
 STORED AS TEXTFILE
 LOCATION '/path/filename';

edited Jul 05 '18 at 12:15

Romain

19,910
6
56
65

answered Oct 14 '13 at 00:43

Lex Lian

585
3
12

Does the field delimiter need to be speicified in octal? Why not just use ',' ? – John Prior Dec 05 '14 at 19:48
John, yes, octal 054 is just ascii ',' and Hive support use ',' directly – Lex Lian Dec 13 '14 at 03:53

score 9 · Answer 4 · answered Dec 09 '17 at 19:27

For csv file formate data will be in below format

"column1", "column2","column3","column4"

And if we will use field terminated by ',' then each column will get values like below.

"column1"    "column2"     "column3"     "column4"

also if any of the column value has comma as value then it will not work at all .

So the correct way to create a table would be by using OpenCSVSerde

create table tableName (column1 datatype, column2 datatype , column3 datatype , column4 datatype)
ROW FORMAT SERDE 
'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
STORED AS TEXTFILE ;

This has the downside of saving all columns as Strings, no matter how your table is defined. — Susensio, Mar 08 '18 at 12:04

score 6 · Answer 5 · answered Jul 13 '15 at 14:45

You may try this, Following are few examples on how files are generated. Tool -- https://sourceforge.net/projects/csvtohive/?source=directory

Select a CSV file using Browse and set hadoop root directory ex: /user/bigdataproject/

Tool Generates Hadoop script with all csv files and following is a sample of generated Hadoop script to insert csv into Hadoop

#!/bin/bash -v

hadoop fs -put ./AllstarFull.csv /user/bigdataproject/AllstarFull.csv
hive -f ./AllstarFull.hive


hadoop fs -put ./Appearances.csv /user/bigdataproject/Appearances.csv
hive -f ./Appearances.hive


hadoop fs -put ./AwardsManagers.csv /user/bigdataproject/AwardsManagers.csv
hive -f ./AwardsManagers.hive

Sample of generated Hive scripts

CREATE DATABASE IF NOT EXISTS lahman;

USE lahman;

CREATE TABLE AllstarFull (playerID string,yearID string,gameNum string,gameID string,teamID string,lgID string,GP string,startingPos string) row format delimited fields terminated by ',' stored as textfile;

LOAD DATA INPATH '/user/bigdataproject/AllstarFull.csv' OVERWRITE INTO TABLE AllstarFull;

SELECT * FROM AllstarFull;

Thanks Vijay

score 0 · Answer 6 · answered Sep 19 '18 at 13:46

0

You can load local CSV file to Hive only if:

You are doing it from one of the Hive cluster nodes.
You installed Hive client on non-cluster node and using hive or beeline for upload.

answered Sep 19 '18 at 13:46

Alex B

2,165
2
27
37

Hadoop/Hive : Loading data from .csv on a local machine

6 Answers6

Linked