Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

I have few column to make external table in hive from csv file but some column is empty what data type I need to define or leave it empty?

avatar
Rising Star

I have trouble to create external table in hive

CREATE EXTERNAL TABLE IF NOT EXISTS DB.TableName( SOURCE_ID VARCHAR(30) , SOURCE_ID_TYPE VARCHAR(30) , SOURCE_NAME VARCHAR(30) , DEVICE_ID_1 VARCHAR(30)

) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE location 'hdfs:///user/hive';

column name SOURCE_NAME IS EMPTY(null)

I am failed to create the external table can you help me where I do mistake?

1 ACCEPTED SOLUTION

avatar
Super Guru

@mike pal

You probably need to convert empty values into NULL, add below option while creating table.

EDITED: Full DDL.

CREATE EXTERNAL TABLE IF NOT EXISTS DB.TableName(SOURCE_ID VARCHAR(30) ,SOURCE_ID_TYPE VARCHAR(30) ,SOURCE_NAME VARCHAR(30) ,DEVICE_ID_1 VARCHAR(30)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE location 'hdfs:///user/hive' TBLPROPERTIES ('serialization.null.format'='');

View solution in original post

5 REPLIES 5

avatar
Super Guru

@mike pal

You probably need to convert empty values into NULL, add below option while creating table.

EDITED: Full DDL.

CREATE EXTERNAL TABLE IF NOT EXISTS DB.TableName(SOURCE_ID VARCHAR(30) ,SOURCE_ID_TYPE VARCHAR(30) ,SOURCE_NAME VARCHAR(30) ,DEVICE_ID_1 VARCHAR(30)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE location 'hdfs:///user/hive' TBLPROPERTIES ('serialization.null.format'='');

avatar
Rising Star

Thanks to all

avatar
Rising Star

Can you write full create table what looks like if you can I really apreciated

avatar
Super Guru

I edited my answer with DDL, hope that will help. Thanks

avatar
Rising Star

Thanks Jitendra