OpenCSVSerDe 用于处理 CSV - Amazon Athena
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

OpenCSVSerDe 用于处理 CSV

当您在 Athena 中通过 CSV 数据创建表时,请确定它包含的值的类型:

  • 如果数据包含用双引号 (") 括起的值,则可以使用 OpenCSV SerDe 反序列化 中的值Athena。在以下部分中,请注意此 的行为SerDe以及 STRING 数据类型。

  • 如果数据不包含使用双引号 (") 括起的值,则可以省略指定任何 SerDe。 在这种情况下, Athena 使用默认的 LazySimpleSerDe。 有关信息,请参阅LazySimpleSerDe适用于 CSV、TSV 和自定义分隔文件的

CSV SerDe (OpenCSVSerDe)

OpenCSV SerDe 的行为如下所示:

  • 将所有列类型值转换为 STRING.

  • 为识别 STRING 以外的数据类型,依赖于 Presto 解析器,并将 STRING 中的值转换为这些数据类型 (如果它可以识别它们)。

  • 使用双引号 (") 作为默认引号字符,还允许您指定分隔符、引号和转义符,例如:

    WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )
  • \t\n 无法直接转义。要对其进行转义,请使用 "escapeChar" = "\\"。 请参阅本主题中的示例。

  • 不支持 CSV 文件中的嵌入换行符。

  • 不支持定义为数值数据类型的列中的空字段。

注意

当您将 Athena 与 结合使用时OpenCSVSerDe, SerDe 会将所有列类型转换为 STRING。 接下来, 中的解析程序根据找到的内容将值从 Athena 解析STRING为实际类型。例如,它会将可以识别的值解析为 BOOLEANBIGINTINTDOUBLE 数据类型。如果值为 UNIX 格式TIMESTAMP的 ,则 将其Athena解析为 TIMESTAMP。 如果值位于 Hive 格式TIMESTAMP的 中, 会将其Athena解析为 INTDATE 类型值也将解析为 INT

要进一步将表中的列转换为所需的类型,您可以为表创建一个视图并使用 CAST 转换为所需的类型。

对于 之外的数据类型STRING,当 中的解析程序Athena可以识别它们时,其SerDe行为如下所示:

  • 识别 BOOLEANBIGINTINTDOUBLE 数据类型,而且无需更改就解析它们。解析程序无法识别定义为数字数据类型的列中的空值或空值,保留它们作为 的默认数据类型STRING。 解决方法是将列声明为 STRING ,然后在CAST查询或视图中SELECT进行声明。

  • 识别 TIMESTAMP 类型(如果它是以 UNIX 数字格式指定的,例如 1564610311 )。

  • 不支持采用 JDBC 兼容 TIMESTAMP 格式的 java.sql.Timestamp,例如 "YYYY-MM-DD HH:MM:SS.fffffffff" (9 位小数精度)。如果您正在处理来自 Hive 的 CSV 数据,请使用 UNIX 数字格式。

  • 识别 DATE 类型(如果它是以 UNIX 数字格式指定的,如 1562112000 )。

  • 不支持其他格式的 DATE 如果您正在处理来自 Hive 的 CSV 数据,请使用 UNIX 数字格式。

注意

有关在未指定 UNIX 数字格式的 TIMESTAMPDATE 列时使用 的信息,请参阅文章在 Amazon Athena 中查询表时,在知识中心AWS中 TIMESTAMP 结果为空

例 示例:使用以 UNIX 数字格式指定的 TIMESTAMP 类型和 DATE 类型。

请考虑使用以下测试数据:

"unixvalue creationdate 18276 creationdatetime 1579146280000","18276","1579146280000"

以下语句在 Athena 中根据指定的 Amazon S3 存储桶位置创建表。

CREATE EXTERNAL TABLE IF NOT EXISTS testtimestamp1( `profile_id` string, `creationdate` date, `creationdatetime` timestamp ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' LOCATION 's3://<location>'

接下来运行以下查询:

select * from testtimestamp1

查询返回以下结果,同时显示日期和时间数据:

profile_id creationdate creationdatetime 1 unixvalue creationdate 18276 creationdatetime 1579146280000 2020-01-15 2020-01-16 03:44:40.000

例 示例:针对 \t\n 进行转义

请考虑使用以下测试数据:

" \\t\\t\\n 123 \\t\\t\\n ",abc " 456 ",xyz

以下语句在 Athena 中创建一个表,指定该 "escapeChar" = "\\".

CREATE EXTERNAL TABLE test1 ( f1 string, s2 string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\") LOCATION 's3://user-test-region/dataset/test1/'

接下来运行以下查询:

select * from test1;

它会返回此结果,针对 \t\n: 正确进行转义:

f1 s2 \t\t\n 123 \t\t\n abc 456 xyz

SerDe 名称

CSV SerDe

库名称

要使用此 SerDe,请在 中指定其完全限定类名ROW FORMAT。 另请在 中指定分隔符SERDEPROPERTIES,如下所示:

... ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( "separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )

忽略标头

要在定义表时忽略数据中的标头,您可以使用 skip.header.line.count 表属性,如以下示例所示。

TBLPROPERTIES ("skip.header.line.count"="1")

有关示例,请参阅 CREATE TABLE 和 中的查询 Amazon VPC 流日志语句查询 Amazon CloudFront 日志

Example

此示例假定 CSV 中的数据保存在 s3://mybucket/mycsv/ 中且具有以下内容:

"a1","a2","a3","a4" "1","2","abc","def" "a","a1","abc3","ab4"

使用 CREATE TABLE 语句根据数据创建 Athena 表,并在 中引用 OpenCSVSerDe 类ROW FORMAT,还指定字符分隔符、引号字符和转义字符SerDe的属性,如下所示:

CREATE EXTERNAL TABLE myopencsvtable ( col1 string, col2 string, col3 string, col4 string ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( 'separatorChar' = ',', 'quoteChar' = '"', 'escapeChar' = '\\' ) STORED AS TEXTFILE LOCATION 's3://location/of/csv/';

查询表中的所有值:

SELECT * FROM myopencsvtable;

查询将返回以下值:

col1 col2 col3 col4 ----------------------------- a1 a2 a3 a4 1 2 abc def a a1 abc3 ab4