如何使用从DynamoDB获取的S3路径创建Athena表并查询

你已经能通过Athena连通查询DynamoDB表的话，不需要额外导出路径、迁移数据，按业务场景选对应方案即可直接建表查询。

适合DynamoDB里的路径会动态增删、每次查询需要匹配最新有效路径的场景，不用手动维护表配置。

1. 先给S3上的源数据建基础外部表，LOCATION填所有目标文件的公共父级前缀即可，不需要精准到单个文件。以你存的gzip压缩JSON格式用户数据为例，建表语句参考：

CREATE EXTERNAL TABLE IF NOT EXISTS raw_user_data (
  -- 按你JSON文件里的实际字段定义即可，下方为示例
  user_id STRING,
  nick_name STRING,
  register_ts BIGINT,
  last_login_ip STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1',
  'ignore.malformed.json' = 'true' -- 自动跳过格式异常的JSON行，避免查询报错
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://bucket/' -- 替换成你所有文件的公共父路径
TBLPROPERTIES (
  'compressionType' = 'gzip' -- 必须指定gzip压缩，否则会报解压错误
)

2. 查询时直接关联DynamoDB里的路径表，用Athena自带的隐藏元字段$path做匹配——这个字段是Athena给所有S3外部表自动生成的，存储当前数据行所属的完整S3文件路径，关联后只会扫描你DynamoDB里登记过的文件，不会扫公共前缀下的其他无关文件：

SELECT
  t.*
FROM raw_user_data t
INNER JOIN (
  SELECT DISTINCT s3_file_path -- s3_file_path替换成你DynamoDB表里存S3路径的字段名
  FROM dynamodb_s3_path_store -- 替换成你DynamoDB路径表在Athena里的表名
  -- 可按需加路径筛选条件，比如只查2022年4月的文件
  WHERE s3_file_path LIKE 's3://bucket/2022/04/%'
) valid_path
ON t."$path" = valid_path.s3_file_path
-- 下方直接加业务查询条件即可
WHERE t.register_ts >= 1648742400

如果某一批路径是固定的、需要反复查询，可以直接基于路径列表建专属视图，避免每次查询都关联DynamoDB，性能更高。

1. 先在Athena里跑下面的语句，从DynamoDB表中把需要的路径拼成IN条件支持的格式：

SELECT array_join(array_agg(DISTINCT concat('''', s3_file_path, '''')), ',') AS path_in_condition
FROM dynamodb_s3_path_store
-- 加筛选条件圈定你需要的路径范围
WHERE s3_file_path LIKE 's3://bucket/2022/04/%'

2. 把查询结果里的路径串替换到视图创建语句里即可：

CREATE OR REPLACE VIEW v_user_data_202204 AS
SELECT * FROM raw_user_data
WHERE "$path" IN (
  -- 替换成上一步生成的路径串，格式为's3://xxx/xxx.json.gz','s3://yyy/yyy.json.gz'
  's3://bucket/2022/04/01/user__2022-04-01.json.gz',
  's3://bucket/2022/04/02/user__2022-04-02.json.gz'
)

后续直接查询这个视图就会只扫描你指定的S3文件，不需要再关联DynamoDB表。

• 引用隐藏字段$path的时候必须加双引号，否则Athena会把它识别为普通自定义字段，报字段不存在的错误
• 如果DynamoDB里存的路径不带s3://前缀，关联或者拼IN条件的时候要先用concat('s3://', 路径字段)补全前缀，否则会匹配失败
• 如果JSON文件存在嵌套结构，建表时可以用STRUCT、ARRAY类型定义对应字段，JsonSerDe会自动解析嵌套内容

内容的提问来源于stack exchange，提问作者RhinoH