全部产品
云市场

CSV

更新时间:2019-06-20 17:42:09

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

前提条件

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

  1. CREATE SCHEMA dla_oss_db with DBPROPERTIES(
  2. catalog='oss',
  3. location 'oss://oss-bucket-name/dla/'
  4. )

创建UTF-8格式的表

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

  1. Beijing,China,010
  2. ShangHai,China,021
  3. Tianjin,China,022

建表语句如下所示:

  1. CREATE EXTERNAL TABLE city (
  2. city STRING,
  3. country STRING,
  4. code INT
  5. )
  6. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  7. STORED AS TEXTFILE
  8. 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,存储的数据如下所示。

  1. hangzhou,aliyuncs
  2. shanghai,aliyuncs

建表语句如下所示:

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

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

通过OpenCSVSerDe处理CSV文件数据

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,1,"中国,杭州",2018-01-01,2018-01-01 00:00:00,120.19,30.26
  2. 2,2,"中国,香港","2018-01-02","2018-01-02 11:11:11",114.10,22.20

建表语句如下所示:

  1. CREATE EXTERNAL TABLE openCsv (
  2. id STRING,
  3. name STRING,
  4. location STRING,
  5. create_date STRING
  6. )
  7. ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' with serdeproperties(
  8. 'separatorChar'=',',
  9. 'quoteChar'='"',
  10. 'escapeChar'='\\'
  11. )
  12. STORED AS TEXTFILE
  13. LOCATION 'oss://oss-bucket-name/dla';

查询结果为:

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

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

  1. CREATE EXTERNAL TABLE csv_table2 (
  2. id STRING,
  3. name STRING,
  4. location STRING,
  5. create_date STRING,
  6. create_timestamp STRING,
  7. longitude STRING,
  8. latitude STRING
  9. )
  10. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  11. STORED AS TEXTFILE LOCATION 'oss://oss-bucket-name/dla';

查询结果为:

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

自定义CSV文件分隔符

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

  1. ROW FORMAT DELIMITED
  2. FIELDS TERMINATED BY '\t'
  3. ESCAPED BY '\\'
  4. 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,存储的数据如下所示。

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

建表语句为:

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

查询结果为:

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