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?

[#1]

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?

[#2 · Accepted solution]

@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'='');

[#3]

Thanks to all

[#4]

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

[#5]

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

[#6]

Thanks Jitendra