【Hive】CSV序列化方式

来源：

用于 CSV、TSV 和自定义分隔文件的 LazySimpleSerDe - Amazon Athena

用于处理 CSV 的 OpenCSVSerDe - Amazon Athena

当您为 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 的默认值。有关更多信息，请参阅 AWS 知识中心中的当我在 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 秒。
- OpenCSVSerDe 不直接支持任何其他格式的 DATE。要处理其他格式的时间戳数据，可以将列定义为 string，然后使用时间转换函数在 SELECT 查询中返回所需的值。有关更多信息，请参阅 AWS 知识中心中的文章：当在 Amazon Athena 中查询表时，时间戳结果为空。
要进一步将表中的列转换为所需的类型，您可以针对表创建视图，并使用 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'
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/'
DOC-EXAMPLE-BUCKET

接下来运行以下查询：

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

查询表中的所有值：

SELECT * FROM myopencsvtable;

查询将返回以下值：

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

此 SerDe 的指定是可选的。这是 Athena 在预设情况下使用的 CSV、TSV 和自定义分隔格式数据的 SerDe。如果不指定任何 SerDe，并且只指定 ROW FORMAT DELIMITED，则会使用此 SerDe。如果您的数据没有用引号引起来的值，请使用此 SerDe。

有关 LazySimpleSerDe 的参考文档，请参阅《Apache Hive 开发人员指南》中的 Hive SerDe 部分。

库名称

LazySimpleSerDe 的类库名称为 org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe。有关 LazySimpleSerDe 类的信息，请参阅 GitHub.com 上的 LazySimpleSerDe.java。

忽略标题

要在您定义表时忽略标题，可以使用 skip.header.line.count 表属性，如下例所示。

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

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

示例

以下示例显示如何使用 LazySimpleSerDe 根据 CSV 和 TSV 数据在 Athena 中创建表。要使用此 SerDe 反序列化自定义分隔的文件，请遵循示例中的模式，但使用 FIELDS TERMINATED BY 子句指定单字符分隔符。LazySimpleSerDe 不支持多字符分隔符。

注意

在 s3://athena-examples-myregion/path/to/data/ 中，将 myregion 替换为您运行 Athena 所在的区域标识符，例如 s3://athena-examples-us-west-1/path/to/data/。

注意

飞行表数据来自由美国运输部交通统计局提供的航班。从原来的数据中进行稀释。

CSV 示例

使用 CREATE TABLE 语句根据在 Amazon S3 中存储的底层 CSV 数据创建一个 Athena 表。

CREATE EXTERNAL TABLE flight_delays_csv (
    yr INT,
    quarter INT,
    month INT,
    dayofmonth INT,
    dayofweek INT,
    flightdate STRING,
    uniquecarrier STRING,
    airlineid INT,
    carrier STRING,
    tailnum STRING,
    flightnum STRING,
    originairportid INT,
    originairportseqid INT,
    origincitymarketid INT,
    origin STRING,
    origincityname STRING,
    originstate STRING,
    originstatefips STRING,
    originstatename STRING,
    originwac INT,
    destairportid INT,
    destairportseqid INT,
    destcitymarketid INT,
    dest STRING,
    destcityname STRING,
    deststate STRING,
    deststatefips STRING,
    deststatename STRING,
    destwac INT,
    crsdeptime STRING,
    deptime STRING,
    depdelay INT,
    depdelayminutes INT,
    depdel15 INT,
    departuredelaygroups INT,
    deptimeblk STRING,
    taxiout INT,
    wheelsoff STRING,
    wheelson STRING,
    taxiin INT,
    crsarrtime INT,
    arrtime STRING,
    arrdelay INT,
    arrdelayminutes INT,
    arrdel15 INT,
    arrivaldelaygroups INT,
    arrtimeblk STRING,
    cancelled INT,
    cancellationcode STRING,
    diverted INT,
    crselapsedtime INT,
    actualelapsedtime INT,
    airtime INT,
    flights INT,
    distance INT,
    distancegroup INT,
    carrierdelay INT,
    weatherdelay INT,
    nasdelay INT,
    securitydelay INT,
    lateaircraftdelay INT,
    firstdeptime STRING,
    totaladdgtime INT,
    longestaddgtime INT,
    divairportlandings INT,
    divreacheddest INT,
    divactualelapsedtime INT,
    divarrdelay INT,
    divdistance INT,
    div1airport STRING,
    div1airportid INT,
    div1airportseqid INT,
    div1wheelson STRING,
    div1totalgtime INT,
    div1longestgtime INT,
    div1wheelsoff STRING,
    div1tailnum STRING,
    div2airport STRING,
    div2airportid INT,
    div2airportseqid INT,
    div2wheelson STRING,
    div2totalgtime INT,
    div2longestgtime INT,
    div2wheelsoff STRING,
    div2tailnum STRING,
    div3airport STRING,
    div3airportid INT,
    div3airportseqid INT,
    div3wheelson STRING,
    div3totalgtime INT,
    div3longestgtime INT,
    div3wheelsoff STRING,
    div3tailnum STRING,
    div4airport STRING,
    div4airportid INT,
    div4airportseqid INT,
    div4wheelson STRING,
    div4totalgtime INT,
    div4longestgtime INT,
    div4wheelsoff STRING,
    div4tailnum STRING,
    div5airport STRING,
    div5airportid INT,
    div5airportseqid INT,
    div5wheelson STRING,
    div5totalgtime INT,
    div5longestgtime INT,
    div5wheelsoff STRING,
    div5tailnum STRING
)
    PARTITIONED BY (year STRING)
    ROW FORMAT DELIMITED
      FIELDS TERMINATED BY ','
      ESCAPED BY '\\'
      LINES TERMINATED BY '\n'
    LOCATION 's3://athena-examples-myregion/flight/csv/';
myregion

每次将新分区添加到此表时，请运行 MSCK REPAIR TABLE 以刷新分区元数据：

MSCK REPAIR TABLE flight_delays_csv;

查询前 10 个延迟超过 1 小时的路线：

SELECT origin, dest, count(*) as delays
FROM flight_delays_csv
WHERE depdelayminutes > 60
GROUP BY origin, dest
ORDER BY 3 DESC
LIMIT 10;

TSV 示例

使用 CREATE TABLE 语句根据在 Amazon S3 中存储的 TSV 数据创建一个 Athena 表。使用 ROW FORMAT DELIMITED 并指定制表符字段分隔符、行分隔符和转义字符，如下所示：

...
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
...

以下示例 CREATE TABLE 语句。

CREATE EXTERNAL TABLE flight_delays_tsv (
 yr INT,
 quarter INT,
 month INT,
 dayofmonth INT,
 dayofweek INT,
 flightdate STRING,
 uniquecarrier STRING,
 airlineid INT,
 carrier STRING,
 tailnum STRING,
 flightnum STRING,
 originairportid INT,
 originairportseqid INT,
 origincitymarketid INT,
 origin STRING,
 origincityname STRING,
 originstate STRING,
 originstatefips STRING,
 originstatename STRING,
 originwac INT,
 destairportid INT,
 destairportseqid INT,
 destcitymarketid INT,
 dest STRING,
 destcityname STRING,
 deststate STRING,
 deststatefips STRING,
 deststatename STRING,
 destwac INT,
 crsdeptime STRING,
 deptime STRING,
 depdelay INT,
 depdelayminutes INT,
 depdel15 INT,
 departuredelaygroups INT,
 deptimeblk STRING,
 taxiout INT,
 wheelsoff STRING,
 wheelson STRING,
 taxiin INT,
 crsarrtime INT,
 arrtime STRING,
 arrdelay INT,
 arrdelayminutes INT,
 arrdel15 INT,
 arrivaldelaygroups INT,
 arrtimeblk STRING,
 cancelled INT,
 cancellationcode STRING,
 diverted INT,
 crselapsedtime INT,
 actualelapsedtime INT,
 airtime INT,
 flights INT,
 distance INT,
 distancegroup INT,
 carrierdelay INT,
 weatherdelay INT,
 nasdelay INT,
 securitydelay INT,
 lateaircraftdelay INT,
 firstdeptime STRING,
 totaladdgtime INT,
 longestaddgtime INT,
 divairportlandings INT,
 divreacheddest INT,
 divactualelapsedtime INT,
 divarrdelay INT,
 divdistance INT,
 div1airport STRING,
 div1airportid INT,
 div1airportseqid INT,
 div1wheelson STRING,
 div1totalgtime INT,
 div1longestgtime INT,
 div1wheelsoff STRING,
 div1tailnum STRING,
 div2airport STRING,
 div2airportid INT,
 div2airportseqid INT,
 div2wheelson STRING,
 div2totalgtime INT,
 div2longestgtime INT,
 div2wheelsoff STRING,
 div2tailnum STRING,
 div3airport STRING,
 div3airportid INT,
 div3airportseqid INT,
 div3wheelson STRING,
 div3totalgtime INT,
 div3longestgtime INT,
 div3wheelsoff STRING,
 div3tailnum STRING,
 div4airport STRING,
 div4airportid INT,
 div4airportseqid INT,
 div4wheelson STRING,
 div4totalgtime INT,
 div4longestgtime INT,
 div4wheelsoff STRING,
 div4tailnum STRING,
 div5airport STRING,
 div5airportid INT,
 div5airportseqid INT,
 div5wheelson STRING,
 div5totalgtime INT,
 div5longestgtime INT,
 div5wheelsoff STRING,
 div5tailnum STRING
)
 PARTITIONED BY (year STRING)
 ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
 LOCATION 's3://athena-examples-myregion/flight/tsv/';
myregion

每次将新分区添加到此表时，请运行 MSCK REPAIR TABLE 以刷新分区元数据：

MSCK REPAIR TABLE flight_delays_tsv;

查询前 10 个延迟超过 1 小时的路线：

SELECT origin, dest, count(*) as delays
FROM flight_delays_tsv
WHERE depdelayminutes > 60
GROUP BY origin, dest
ORDER BY 3 DESC
LIMIT 10;

posted @ 2022-09-29 15:23 梦醒江南·Infinite 阅读(483) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 【Hive】Hive客户端命令行官方说明文档

· 【Hive】Hive数据类型

· 大规模-MLOps-工程（全）

· hive OpenCSVSerde

· Hive实战之航空数据分析

阅读排行：
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 字符编码：从基础到乱码解决

会挽雕弓如满月

【Hive】CSV序列化方式

CSV SerDe (OpenCSVSerDe)

SerDe 名称

库名称

忽略标题

示例

库名称

忽略标题

示例

CSV 示例

TSV 示例

公告

搜索

常用链接

我的标签

随笔档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论