用于处理 CSV 的 Open CSV SerDe - Amazon Athena
序列化库名称使用 Open CSV SerDe示例
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

用于处理 CSV 的 Open CSV SerDe

使用 Open CSV SerDe 库从逗号分隔（CSV）的数据创建 Athena 表。

序列化库名称

Open CSV SerDe 的序列化库名称是 org.apache.hadoop.hive.serde2.OpenCSVSerde。有关源代码信息，请参阅 Apache 文档中的 CSV SerDe。

使用 Open 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")

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

使用 NULL 表示无效数据

要使用 NULL 值表示无法反序列化为列定义类型的数据，可以使用 use.null.for.invalid.data 表属性，如下例所示。

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

将 use.null.for.invalid.data 设置为 TRUE 可能会导致不正确或意外的结果，因为 NULL 值会替换架构不匹配的列中的无效数据。我们建议您修复文件或表架构中的数据，而不是启用此属性。启用此属性后，查询不会因无效数据而失败，这可能会阻止您发现数据质量问题。

字符串数据的注意事项

Open CSV SerDe 具有以下字符串数据特性：

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

    WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )

  • \t 或 \n 无法直接转义。要对它们进行转义，请使用 "escapeChar" = "\\"。有关示例，请参阅Example: Escaping \t or \n。

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

非字符串数据的注意事项

对于不是 STRING 的数据类型，Open CSV SerDe 的行为如下所示：

  • 识别 BOOLEAN、BIGINT、INT 和 DOUBLE 数据类型。

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

  • 对于使用 CREATE TABLE 语句中的 timestamp 数据类型指定的列，如果它使用的以毫秒为单位指定的 UNIX 数字格式，例如 1579059880000，则识别 TIMESTAMP 数据。有关示例，请参阅Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format。

    • Open CSV SerDe 不支持采用 JDBC 兼容 java.sql.Timestamp 格式的 TIMESTAMP，例如 "YYYY-MM-DD HH:MM:SS.fffffffff"（9 位小数精度）。

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

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

示例

例 示例：查询简单的 CSV 数据

以下示例假设您已将包含以下内容的 CSV 数据保存在位置 s3://amzn-s3-demo-bucket/mycsv/：

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

使用 CREATE TABLE 语句根据数据创建 Athena 表。ROW FORMAT SERDE 之后引用 OpenCSVSerde（注意小写字母“d”）并指定 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://amzn-s3-demo-bucket/mycsv/';

查询表中的所有值：

SELECT * FROM myopencsvtable;

查询将返回以下值：

col1     col2    col3    col4
-----------------------------
a1       a2      a3      a4
1        2       abc     def
a        a1      abc3    ab4
例 示例：使用以 UNIX 数字格式指定的 TIMESTAMP 类型和 DATE 类型

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

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

以下语句在 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://amzn-s3-demo-bucket'

接下来运行以下查询：

SELECT * FROM testtimestamp1

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

profile_id                                                        creationdate     creationdatetime
unixvalue creationdate 18276 creationdatetime 1579146280000       2020-01-15       2020-01-15 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://amzn-s3-demo-bucket/dataset/test1/'

接下来运行以下查询：

SELECT * FROM test1;

它会返回此结果，针对 \t 或 \n 正确进行转义：

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