Create table in Hive using octal code

By | April 17, 2017

Create table in Hive using octal code

Reference Example is take from Programming in Hive book

Let’s create a table in hive

CREATE TABLE employee ( name  STRING, salary FLOAT, 

subordinates ARRAY<STRING>

deductions MAP<STRING, FLOAT>

address STRUCT<street:STRING, city:STRING, state:STRING,zip:INT>)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘\001’

COLLECTION ITEMS TERMINATED BY ‘\002’

MAP KEYS TERMINATED BY ‘\003’

LINES TERMINATED BY ‘\n’

STORED AS TEXTFILE;

Let’s first create a input file save it as employee.txt and upload to HDFS

John Doe^A100000.0^AMary Smith^BTodd Jones^AFederal Taxex^C.2^BState Taxes^C.05^BInsurance^C.1^A1 Michigan Ave.^BChicago^BIL^B60600

John Doe1^A200000.0^AMary Smith^BTodd Jones^AFederal Taxex^C.2^BState Taxes^C.05^BInsurance^C.1^A1 Michigan Ave.^BChicago^BIL^B60600

John Doe2^A300000.0^AMary Smith^BTodd Jones^AFederal Taxex^C.2^BState Taxes^C.05^BInsurance^C.1^A1 Michigan Ave.^BChicago^BIL^B60600

Now let’s load the data into employee table.

LOAD DATA INPATH “employee.txt” into table employee;