HIVE HOW TO LOAD DATA

How to load data into Hive

------------------------

In order to load data into Hive, we need to tell Hive the format of the data

through "CREATE TABLE" statement:

FileFormat: the data has to be in Text or SequenceFile.

Format of the row:

If the data is in delimited format, use MetadataTypedColumnsetSerDe

If the data is in delimited format and has more than 1 levels of delimitor,

use DynamicSerDe with TCTLSeparatedProtocol

If the data is a serialized thrift object, use ThriftSerDe

The steps to load the data:

1 Create a table:

CREATE TABLE t (foo STRING, bar STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

STORED AS TEXTFILE; 

CREATE TABLE t2 (foo STRING, bar ARRAY<STRING>)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ','

STORED AS TEXTFILE; 

CREATE TABLE t3 (foo STRING, bar MAP<STRING,STRING>)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ','

MAP KEYS TERMINATED BY ':'

STORED AS TEXTFILE;

CREATE TABLE t4 (foo STRING, bar MAP<STRING,STRING>)

ROW FORMAT SERIALIZER 'org.apache.hadoop.hive .serde2.MetadataTypedColumnsetSerDe'

WITH SERDEPROPERTIES ('columns'='foo,bar','SERIALIZATION.FORMAT'='9'); 

(RegexDeserializer is not done yet)

CREATE TABLE t5 (foo STRING, bar STRING)

ROW FORMAT SERIALIZER 'org.apache.hadoop.hive.serde2.RegexDeserializer'

WITH SERDEPROPERTIES ('regex'='([a-z]*) ([a-z])'); 

2 Load the data:

LOAD DATA LOCAL INPATH '../examples/files/kv1.txt' OVERWRITE INTO TABLE t; 

How to read data from Hive tables

------------------------

In order to read data from Hive tables, we need to know the same 2 things:

File Format

Row Format

Then we just need to directly open the HDFS file and read the data.

 

 

CREATE TABLE table1 (a STRING, b STRING)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ESCAPED BY '\\'

STORED AS TEXTFILE; 

ESCAPED BY 指定转义字符