Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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?

SOLVED Go to solution
Highlighted

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?

New Contributor

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

Accepted Solutions

Re: 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?

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

5 REPLIES 5

Re: 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?

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

Re: 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?

New Contributor

Thanks to all

Re: 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?

New Contributor

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

Re: 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?

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

Re: 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?

New Contributor

Thanks Jitendra