全部产品

CSV

更新时间:2020-09-09 14:40:29

本文介绍如何在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'不过滤文件中的数据,即读取全部文件数据,详情请参见skip.header.line.count

注意:

  • “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-0100: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-0100:00:00|120.19|30.26|
|2|2|中国,香港|2018-01-02|2018-01-0211: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-0100: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|