全部产品
Search

搜索本产品

    云原生数据湖分析 DLA - 文档停止维护：CSV

    文档中心

    云原生数据湖分析 DLA - 文档停止维护：CSV

    更新时间：Jun 17, 2021

    本文介绍如何在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和编码格式，即SERDE和WITH 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 
|