用于处理 CSV 的 OpenCSVSerDe - Amazon Athena
CSV SerDe (OpenCSVSerDe)
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 的行为如下所示：

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

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

  • 对于使用 CREATE TABLE 语句中的 timestamp 数据类型指定的列，如果它使用的以毫秒为单位指定的 UNIX 数字格式，例如 1579059880000，则识别 TIMESTAMP 数据。

    • OpenCSVSerDe 不支持采用 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 转换为所需的类型。

例 示例：使用以 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://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

例 示例：针对 \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")

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

示例

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

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

使用 CREATE TABLE 语句根据数据创建 Athena 表。ROW FORMAT SERDE 之后应用 OpenCSVSerDe 类并指定 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