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

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

用于处理 CSV 的 OpenCSVSerDe

当您为 CSV 数据创建 Athena 表时,请确定 SerDe 以使用它包含的值的类型:

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

  • 如果您的数据具有 UNIX 数字TIMESTAMP值(例如,1579059880000),请使用 OpenCSVSerDe。如果您的数据使用java.sql.Timestamp格式,请使用 LazySimpleSerDe。

CSV SerDe (OpenCSVSerDe)

这些区域有:OpenCSV SerDe对于字符串数据具有以下特性:

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

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

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

对于除以外的数据类型STRING,OpenCSVSerDe 的行为如下所示:

  • 识别BOOLEANBIGINTINT, 和DOUBLE数据类型。

  • 不识别定义为数值数据类型的列中的空值或 null 值,将其留为string. 一种解决方法是创建空值为string,然后使用CAST将查询中的字段转换为数字数据类型,提供默认值0对于 NULL。有关更多信息,请参阅 。当我在 Athena 中查询 CSV 数据时,出现错误 HIVE_BAD_DATA:解析字段值时出错中的Amazon。

  • 对于使用timestamp数据类型CREATE TABLE语句,识别TIMESTAMP数据(如果它是以毫秒为单位指定的,例如)。1579059880000.

    • OOpenCSVSerDe 不支持TIMESTAMP在符合 JDBC 标准的java.sql.Timestamp格式,例如"YYYY-MM-DD HH:MM:SS.fffffffff"(9 位小数精度)。

  • 对于使用DATE数据类型CREATE TABLE语句,如果值表示自 1970 年 1 月 1 日以来已过去的天数,则会将值识别为日期。例如,值18276列中的date数据类型渲染为2020-01-15查询时。在这种 UNIX 格式下,每天都被认为有 86,400 秒。

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

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

请考虑以下三列以逗号分隔的数据。每列中的值都包含在双引号内。

"unixvalue creationdate 18276 creationdatetime 1579059880000","18276","1579059880000"

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

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://DOC-EXAMPLE-BUCKET'

接下来运行以下查询:

SELECT * FROM testtimestamp1

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

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

例 示例:ESCAPE\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://DOC-EXAMPLE-BUCKET/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 SERDE. 还需指定在 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://DOC-EXAMPLE-BUCKET/mycsv/ 中且具有以下内容:

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

使用CREATE TABLE语句创建基于数据的 Athena 表。引用ROW FORMAT SERDE并指定字符分隔符、引号字符和转义字符WITH SERDEPROPERTIES,如以下示例所示。

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://DOC-EXAMPLE-BUCKET/mycsv/';

查询表中的所有值:

SELECT * FROM myopencsvtable;

查询将返回以下值:

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