json - Athena AWS 错误的字段名称和带有 Hive DDL 的多个文件夹

Question

我是 AWS Athena 的新手，我正在尝试查询包含 JSON 文件的多个 S3 存储桶。我遇到了一些在文档中没有任何答案的问题(遗憾的是他们的错误日志没有足够的信息来尝试自己解决):

1. 如何查询以括号命名的 JSON 字段？例如，我有一个名为“Capacity(GB)”的字段，当我尝试将其包含在 CREATE EXTERNAL 语句中时，我收到一个错误:

   CREATE EXTERNAL TABLE IF NOT EXISTS test-scema.test_table (
  `device`: string,
  `Capacity(GB)`: string)

Your query has the following error(s):

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.IllegalArgumentException: Error: : expected at the position of 'Capacity(GB):string>' but '(' is found.

1. 我的文件位于 S3 中的子文件夹中，结构如下:

   '位置名称/YYYY/MM/DD/appstring/'

我想查询特定应用字符串的所有日期(很多)。有没有我可以用来替换日期路径的“通配符”？像这样:

LOCATION 's3://location_name/%/%/%/appstring/'

1. 我是否必须使用 CREATE EXTERNAL TABLE 按原样加载原始数据，然后才对其进行查询，或者我可以添加一些内置的 WHERE 语句？具体是这样的事情是可能的:

CREATE EXTERNAL TABLE IF NOT EXISTS test_schema.test_table (
  field1:string,
  field2:string
  )

ROW FORMAT SERDE  'org.apache.hive.hcatalog.data.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://folder/YYYY/MM/DD/appstring'

WHERE field2='value'

在计费方面会有什么结果？因为现在我构建这个 CREATE 语句只是为了再次在 SQL 查询中重新使用数据。

谢谢!

Answer 1

<强>1。以括号命名的 JSON 字段

无需创建名为 Capacity(GB) 的字段。相反，创建具有不同名称的字段:

CREATE EXTERNAL TABLE test_table (
    device string,
    capacity string
)
ROW FORMAT  serde 'org.apache.hive.hcatalog.data.JsonSerDe'
with serdeproperties ( 'paths'='device,Capacity(GB)')
LOCATION 's3://xxx';

如果您使用的是 Nested JSON，那么您可以使用 Serde 的 mapping 属性(我在 issue with Hive Serde dealing nested structs 上看到的):

CREATE external TABLE test_table (
   top string,
   inner struct<device:INT,
               capacity:INT>
   )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
with serdeproperties
(
"mapping.capacity" = "Capacity(GB)"
)
LOCATION 's3://xxx';

这与以下输入配合得很好:

{ "top" : "123", "inner": { "Capacity(GB)": 12, "device":2}}

<强>2。子文件夹

您不能在中间路径中使用通配符 (s3://location_name/*/*/*/appstring/)。最接近的选择是使用 partitioned data但这需要为您的目录使用不同的命名格式。

<强>3。创建表格

您不能将 WHERE 语句指定为 CREATE TABLE 语句的一部分。

如果您的目标是降低数据成本，请使用 partitioned data以减少扫描的文件数量或以基于列的格式(例如 Parquet)存储。

有关示例，请参阅:Analyzing Data in S3 using Amazon Athena