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

用于处理 CSV 的 OpenCSVSerDe

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

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

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

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 数据类型,而且无需更改就解析它们。在定义为数值数据类型的列中,解析器无法识别其空值或 null 值,请将其留为默认数据类型 STRING。解决方法是将列声明为 STRING,然后在 SELECT 查询或视图中进行 CAST

  • 识别 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 列的信息,请参阅 AWS 知识中心 中的文章当我查询 Amazon Athena 中的表时,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" = "\\" )

示例

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

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

使用 CREATE TABLE 语句根据此数据创建一个 Athena 表,引用 ROW FORMAT 中的 OpenCSVSerDe 类,还指定字符分隔符、引号字符和转义字符的 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
注意

航班表数据来自美国运输部交通统计局提供的航班。对初始数据做了缩减。