csv - 将 csv 文件加载到 Hive 表

Question

我有一个包含如下内容的 csv 文件。

"DepartmentID","Name","GroupName","ModifiedDate"
"1","Engineering","Research and Development","2008-04-30 00:00:00"

我有

create external table if not exists AdventureWorks2014.Department
( 
    DepartmentID smallint , 
    Name string ,
   GroupName string, 
    rate_code string, 
    ModifiedDate timestamp 
)   
ROW FORMAT DELIMITED FIELDS TERMINATED BY '","' lines terminated by '\n'
STORED AS TEXTFILE LOCATION 'wasb:///ds/Department' TBLPROPERTIES('skip.header.line.count'='1');`

加载数据后

LOAD DATA INPATH 'wasb:///ds/Department.csv' INTO TABLE AdventureWorks2014.Department;

数据未加载。

select * from AdventureWorks2014.Department;

上面的选择不返回任何内容。

我认为每个文件周围的双引号是问题所在。有没有办法将数据从这样的文件加载到配置单元表，而不必去掉双引号？

Answer 1

试试这个(手机...)

create external table if not exists AdventureWorks2014.Department ( DepartmentID smallint , Name string , GroupName string, rate_code string, ModifiedDate timestamp )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'      
STORED AS TEXTFILE 
LOCATION 'wasb:///ds/Department' 

** Limitation **
This SerDe treats all columns to be of type String. Even if you create a table with non-string column types using this SerDe, the DESCRIBE TABLE output would show string column type. The type information is retrieved from the SerDe. To convert columns to the desired type in a table, you can create a view over the table that does the CAST to the desired type.

https://cwiki.apache.org/confluence/display/Hive/CSV+Serde