全部产品
Search
文档中心

CSV

更新时间: 2021-06-17

本文介绍如何在DLA中为不同编码格式的CSV类型的数据文件创建表,以及如何通过OpenCSVSerDe处理CSV文件中特殊格式的数据。

创建数据文件表之前,您需要先创建OSS Schema,本文示例中所有数据文件表均使用以下OSS Schema。

CREATE SCHEMA dla_oss_db  
with 
 DBPROPERTIES 
( 
 catalog 
= 
'oss' 
, 
 location  
'oss://oss-bucket-name/dla/' 
)

创建UTF-8格式的表

例如,OSS中有一个编码格式为UTF-8的city.csv文件(存储地址为oss://oss-bucket-name/dla/city.csv),列与列之间以逗号分隔,存储的数据如下所示。

Beijing 
, 
China 
, 
010 
ShangHai 
, 
China 
, 
021 
Tianjin 
, 
China 
, 
022

建表语句如下所示:

CREATE EXTERNAL TABLE city  
( 
 city STRING 
, 
 country STRING 
, 
 code INT 
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY  
',' 
STORED AS TEXTFILE  
LOCATION  
'oss://oss-bucket-name/dla/' 
;
  • STORED AS TEXTFILE指定数据文件类型为TEXTFILE。

  • ROW FORMAT DELIMITED FIELDS TERMINATED BY ','列与列之间以逗号分隔 。

创建GBK格式的表

文件编码格式为GBK时,需要在建表语句中指定SERDE和编码格式,即SERDEWITH SERDEPROPERTIES ('serialization.encoding'='gbk')

例如,OSS中有一个编码格式为GBK的gbk.csv文件,存储路径为oss://oss-bucket-name/dla/gbk.csv,存储的数据如下所示。

hangzhou 
, 
aliyuncs 
shanghai 
, 
aliyuncs

建表语句如下所示:

CREATE EXTERNAL TABLE gbk  
( 
 region  
string 
, 
 owner  
string 
) 
ROW FORMAT SERDE  
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES 
( 
'serialization.encoding' 
= 
'gbk' 
, 
'field.delim' 
= 
',' 
) 
STORED AS TEXTFILE 
LOCATION  
'oss://oss-bucket-name/dla/' 
TBLPROPERTIES  
( 
'skip.header.line.count' 
= 
'0' 
)
  • LazySimpleSerDe: 内置SerDe(org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe) ,用于处理文本格式的文件。

  • 'skip.header.line.count' = '0'不过滤文件中的数据,即读取全部文件数。

注意:

  • “serialization.encoding”参数:需要根据原始文件的编码来配置,其中Linux平台查看文件编码可以使用“file filepath”查看。(原始文件编码为“ISO-8859”,对于中文乱码可以设置’serialization.encoding’=’gbk’)

  • OpenCSVSerDe目前不支持传入编码参数

通过OpenCSVSerDe处理引号引用的字段

CSV文件以纯文本形式存储数据,一个CSV文件由任意数目的记录组成,记录间以逗号或者其他某种换行符分隔。

创建CSV文件的表时,需要对CSV文件中的数据内容进行确认。如果文件内容包含引号引起来的内容(通常包含众多特殊字符),可以使用OpenCSVSerDe对数据进行反序列化处理。OpenCSVSerDe为每行的字段指定字段分隔符、字段内容引用服务和转义字符,例如with serdeproperties('separatorChar'=',','quoteChar'='"','escapeChar'='\\')

注意事项

使用OpenCSVSerDe时需遵守以下注意事项,否则系统将报错。

  • OpenCSVSerDe不支持字段内嵌入的行分割符。

  • DLA建表时,所有字段的数据类型只能定义为STRING。若数据类型不是STRING类型,请参考转换函数进行类型转换。

例如,OSS中有一个编码格式为UTF-8的openCsv.csv文件,存储路径为oss://oss-bucket-name/dla/openCsv.csv,存储的数据如下所示。

1 
, 
1 
, 
"中国,杭州" 
, 
2018 
- 
01 
- 
01 
, 
2018 
- 
01 
- 
01 
00 
: 
00 
: 
00 
, 
120.19 
, 
30.26 
2 
, 
2 
, 
"中国,香港" 
, 
"2018-01-02" 
, 
"2018-01-02 11:11:11" 
, 
114.10 
, 
22.20

建表语句如下所示:

CREATE EXTERNAL TABLE openCsv  
( 
 id STRING 
, 
 name STRING 
, 
 location STRING 
, 
 create_date STRING 
) 
ROW FORMAT SERDE  
'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
with 
 serdeproperties 
( 
'separatorChar' 
= 
',' 
, 
'quoteChar' 
= 
'"' 
, 
'escapeChar' 
= 
'\\' 
) 
STORED AS TEXTFILE  
LOCATION  
'oss://oss-bucket-name/dla' 
;

查询结果为:

select 
* 
from 
 openCsv 
; 
+------+------+---------------+-------------+---------------------+-----------+----------+ 
c 
| 
 id  
| 
 name  
| 
 location  
| 
 create_date  
| 
 create_timestamp  
| 
 longitude  
| 
 latitude  
| 
+------+------+---------------+-------------+---------------------+-----------+----------+ 
| 
1 
| 
1 
| 
中国,杭州 
| 
2018 
- 
01 
- 
01 
| 
2018 
- 
01 
- 
01 
00 
: 
00 
: 
00 
| 
120.19 
| 
30.26 
| 
| 
2 
| 
2 
| 
中国,香港 
| 
2018 
- 
01 
- 
02 
| 
2018 
- 
01 
- 
02 
11 
: 
11 
: 
11 
| 
114.10 
| 
22.20

如果文件内容不包含引号引起来的内容,建表时可以不指定SerDe参数。

CREATE EXTERNAL TABLE csv_table2  
( 
id STRING 
, 
name STRING 
, 
location STRING 
, 
create_date STRING 
, 
create_timestamp STRING 
, 
longitude STRING 
, 
latitude STRING 
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY  
',' 
STORED AS TEXTFILE LOCATION  
'oss://oss-bucket-name/dla' 
;

查询结果为:

select 
* 
from 
 csv_table2 
; 
+------+------+----------+-------------+------------------+-----------------------+----------+ 
| 
 id  
| 
 name  
| 
 location  
| 
 create_date  
| 
 create_timestamp  
| 
 longitude  
| 
 latitude  
| 
+------+------+----------+-------------+------------------+-----------------------+----------+ 
| 
1 
| 
1 
| 
"中国 | 杭州" 
| 
2018 
- 
01 
- 
01 
| 
2018 
- 
01 
- 
01 
00 
: 
00 
: 
00 
| 
120.19 
| 
| 
2 
| 
2 
| 
"中国 | 香港" 
| 
"2018-01-02" 
| 
"2018-01-02 11:11:11" 
| 
114.10 
|

自定义CSV文件分隔符

DLA支持自定义CSV文件中的列分隔符(FIELDS TERMINATED BY)、转义字符(ESCAPED BY)以及行结束符(LINES TERMINATED BY)。

ROW FORMAT DELIMITED 
 FIELDS TERMINATED BY  
'\t' 
 ESCAPED BY  
'\\' 
 LINES TERMINATED BY  
'\n'

忽略CSV文件中的HEADER

当CSV文件中带有HEADER信息,但是读取数据时需要忽略HEADER信息时,您可以在建表语句中定义skip.header.line.count="x"将文件中从第一行到x行的数据过滤掉。

x和数据文件的实际行数n有如下关系:

  • 当x<=0时,DLA在读取文件时,不会过滤掉任何信息,即全部读取。

  • 当0<x<n时,DLA在读取文件时,会过滤掉前x行,从第x+1行开始读取。

  • 当x>=n时,DLA在读取文件时,会过滤掉所有文件内容。

例如,OSS中有一个编码格式为UTF-8的nation_header.csv文件,存储路径为oss://oss-bucket-name/dla/nation_header.csv,存储的数据如下所示。

N_NATIONKEY 
| 
N_NAME 
| 
N_REGIONKEY 
| 
N_COMMENT 
0 
| 
ALGERIA 
| 
0 
| 
 haggle 
. 
 carefully  
final 
 deposits detect slyly agai 
| 
1 
| 
ARGENTINA 
| 
1 
| 
al foxes promise slyly according to the regular accounts 
. 
 bold requests alon 
| 
2 
| 
BRAZIL 
| 
1 
| 
y alongside of the pending deposits 
. 
 carefully special packages are about the ironic forges 
. 
 slyly special  
| 
3 
| 
CANADA 
| 
1 
| 
eas hang ironic 
, 
 silent packages 
. 
 slyly regular packages are furiously over the tithes 
. 
 fluffily bold 
| 
4 
| 
EGYPT 
| 
4 
| 
y above the carefully unusual theodolites 
. 
final 
 dugouts are quickly across the furiously regular d 
| 
5 
| 
ETHIOPIA 
| 
0 
| 
ven packages wake quickly 
. 
 regu 
|

建表语句为:

CREATE EXTERNAL TABLE nation_header  
( 
 N_NATIONKEY INT 
, 
 N_NAME STRING 
, 
 N_REGIONKEY INT 
, 
 N_COMMENT STRING 
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY  
'|' 
STORED AS TEXTFILE  
LOCATION  
'oss://oss-bucket-name/dla/' 
TBLPROPERTIES  
( 
"skip.header.line.count" 
= 
"1" 
);

查询结果为:

SELECT  
* 
 FROM nation_header 
; 
+-------------+--------+-------------+-------------+----------- 
| 
 n_nationkey  
| 
 n_name  
| 
 n_regionkey  
| 
 n_comment  
| 
+-------------+--------+-------------+-------------+----------- 
| 
0 
| 
 ALGERIA  
| 
0 
| 
 haggle 
. 
 carefully  
final 
 deposits detect slyly agai 
VARCHAR  
| 
+-------------+-------- 
+-------------+-------------+----------- 
| 
1 
| 
 ARGENTINA  
| 
1 
| 
 al foxes promise slyly according to the regular accounts 
. 
 bold requests alon  
| 
+-------------+-------- 
+-------------+-------------+----------- 
| 
2 
| 
 BRAZIL  
| 
1 
| 
 y alongside of the pending deposits 
. 
 carefully special packages are about the ironic forges 
. 
 slyly special  
| 
+-------------+-------- 
+-------------+-------------+----------- 
| 
3 
| 
 CANADA  
| 
1 
| 
 eas hang ironic 
, 
 silent packages 
. 
 slyly regular packages are furiously over the tithes 
. 
 fluffily bold 
| 
+-------------+-------- 
+-------------+-------------+----------- 
| 
4 
| 
 EGYPT  
| 
4 
| 
 y above the carefully unusual theodolites 
. 
final 
| 
+-------------+-------- 
+-------------+-------------+----------- 
| 
5 
| 
 ETHIOPIA  
| 
0 
| 
 ven packages wake quickly 
. 
 regu 
|