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;