【2】How to load data into Hive

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 指定转义字符