hive Table Creation Script with sample data Complex Types
Example
ARRAY
CREATE TABLE array_data_type(
c_array array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '&';
Create data.csv with data:
arr1&arr2
arr2&arr4
Put data.csv in /tmp folderand load this data in Hive
LOAD DATA LOCAL INPATH '/tmp/data.csv' INTO TABLE array_data_type;
Or you can put this CSV in HDFS say at /tmp. Load data from CSV at HDFS using
LOAD DATA INPATH '/tmp/data.csv' INTO TABLE array_data_type;
MAP
CREATE TABLE map_data_type(
c_map map<int,string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '&'
MAP KEYS TERMINATED BY '#';
data.csv file:
101#map1&102#map2
103#map3&104#map4
Load data into hive:
LOAD DATA LOCAL INPATH '/tmp/data.csv' INTO TABLE map_data_type;
STRUCT
CREATE TABLE struct_data_type(
c_struct struct<c1:smallint,c2:varchar(30)>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '&';
data.csv file:
101&struct1
102&struct2
Load data into hive:
LOAD DATA LOCAL INPATH '/tmp/data.csv' INTO TABLE struct_data_type;
UNIONTYPE
CREATE TABLE uniontype_data_type(
c_uniontype uniontype<int, double, array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '&';
data.csv file:
0&10
1&10.23
2&arr1&arr2
Load data into hive:
LOAD DATA LOCAL INPATH '/tmp/data.csv' INTO TABLE uniontype_data_type;
