amazon-web-services - AWS Athena 查询输出格式

Question

我有包含 csv 文件的 S3 存储桶(请参阅“数据示例”)。

这些文件是作为 Athena 查询的结果创建的。

我想将“时间”列视为 decimal(17,7) 并将“大小”列视为 bigint。

我定义一个表(参见“表定义”)并使用查询(参见下面的“查询”)我在下面收到“错误”。

我的问题是:
有没有办法控制 Athena 查询输出格式并获得不带引号的 csv？ (1545980216.9581780,1349 对比“1545980216.9581780”，“1349”)

假设上一个问题的答案是否定的。如何查询表 '`del_me_later_4' 中的数据并将字段视为 decimal 和 bigint。

数据样本

"time","size"
"1545751457.8957720","432"
"1545751458.0753407","583"
"1545751458.1407920","583"
"1545751458.1683733","560"

表定义

CREATE EXTERNAL TABLE `del_me_later_4`(
  `time` string, 
  `size` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://del-me-later/'
TBLPROPERTIES (
  'skip.header.line.count'='1'  
)

查询

SELECT cast(time AS decimal(17,7)) as time,
       cast(size AS bigint) as size  
FROM "del_me_later_4"

错误

INVALID_CAST_ARGUMENT: Cannot cast VARCHAR '"1545751457.8957720"' to DECIMAL(17, 7)

Answer 1

我找到了一个解决方案。我使用 org.apache.hadoop.hive.serde2.OpenCSVSerde。

When you use Athena with OpenCSVSerde, the SerDe converts all column types to STRING. Next, the parser in Athena parses the values from STRING into actual types based on what it finds. For example, it parses the values into BOOLEAN, BIGINT, INT, and DOUBLE data types when it can discern them. If the values are in TIMESTAMP in the UNIX format, Athena parses them as TIMESTAMP. If the values are in TIMESTAMP in Hive format, Athena parses them as INT. DATE type values are also parsed as INT.

参见 https://docs.aws.amazon.com/athena/latest/ug/csv.html

''' CREATE EXTERNAL TABLE `{}`(
  `time` string, 
  `size` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  "separatorChar" = ","
)
LOCATION
  's3://{}/{}'
TBLPROPERTIES (
  'skip.header.line.count'='1'  
)