• 【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 秒。

    • 要进一步将表中的列转换为所需的类型，您可以针对表创建视图，并使用 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

    此 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/';

    每次将新分区添加到此表时，请运行 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/';

    每次将新分区添加到此表时，请运行 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;
     
  • 相关阅读:
    GoogleMaps 之创意应用——数码照片定位
    SGI面临破产
    Google地图搜索的触角伸向月球
    说“丛林法则”
    比Google Map更加清晰的网络地图——RealBird
    Office 12眩酷界面，先睹为快
    Paypal将正式登陆中国——贝宝
    地图技术的领导者RealBird与Google Maps的无缝结合
    微软欲封杀OpenGL？
    网络地图服务究竟商机何在？
  • 原文地址：https://www.cnblogs.com/144823836yj/p/16741732.html
Copyright © 2020-2023  润新知