使用加载数据将csv文件转换为hive表-如何在csv中格​​式化日期以被hive表接受 [英] csv file to hive table using load data - How to format the date in csv to accept by hive table

问题描述

我正在使用加载数据语法将csv文件加载到表中.该文件与hive接受的格式相同.但是仍然在发出加载数据后，最后2列在选择时返回null.

I am using load data syntax to load a csv file to a table.The file is same format as hive accepts. But still after load data is issued, Last 2 columns returns null on select.

1750,651,'2013-03-11','2013-03-17'
1751,652,'2013-03-18','2013-03-24'
1752,653,'2013-03-25','2013-03-31'
1753,654,'2013-04-01','2013-04-07'

create table dattable(
DATANUM    INT,  
ENTRYNUM BIGINT, 
START_DATE  DATE,
END_DATE    DATE ) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

 LOAD DATA LOCAL INPATH '/path/dtatable.csv' OVERWRITE INTO TABLE dattable ;

Select返回最后2个列的NULL值

Select returns NULL values for the last 2 cols

另一个问题是，如果日期格式与YYYY-MM-DD不同.是否可以让配置单元识别格式? (因为现在我正在修改csv文件格式以被配置单元接受)

Other question was what if the date format is different than YYYY-MM-DD. is it possible to make hive identify the format? (Because right now i am modifying the csv file format to accept by hive)

推荐答案

LasySimpleSerDe(默认)不适用于带引号的CSV.使用 CSVSerDe :

LasySimpleSerDe (default) does not work with quoted CSV. Use CSVSerDe:

create table dattable(
DATANUM    INT,  
ENTRYNUM BIGINT, 
START_DATE  DATE,
END_DATE    DATE ) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = ",",
   "quoteChar"     = "'"
)  
STORED AS TEXTFILE;

也请阅读以下内容: CSVSerDe将所有列都视为String类型

将日期列定义为字符串，并在select中应用转换.

Define you date columns as string and apply conversion in select.

这篇关于使用加载数据将csv文件转换为hive表-如何在csv中格​​式化日期以被hive表接受的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！