如何将含JSON字段的CSV文件加载到Amazon Athena并实现查询？

我来帮你一步步搞定这个需求，在Athena里处理带JSON字段的制表符分隔（TSV）文件并不复杂，跟着下面的步骤操作就能实现你想要的查询：

1. 创建匹配数据结构的外部表 #

首先你需要在Athena中创建一个外部表，用来映射S3里的TSV文件。因为你的文件是制表符分隔，且address字段是JSON字符串，我们可以直接把address定义为JSON类型（Athena引擎版本2及以上支持该类型，兼容性更好），或者先定义为STRING再解析。

以下是创建表的DDL示例，记得替换成你自己的S3文件路径和数据库名：

CREATE EXTERNAL TABLE IF NOT EXISTS your_database.table1 (
  uid INT,
  name STRING,
  address JSON
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t' -- 因为是制表符分隔的TSV
LOCATION 's3://your-bucket/path-to-your-tsv-file/';

如果你的Athena版本较低不支持JSON类型，也可以把address定义为STRING，后续查询时再解析：

CREATE EXTERNAL TABLE IF NOT EXISTS your_database.table1 (
  uid INT,
  name STRING,
  address STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION 's3://your-bucket/path-to-your-tsv-file/';

2. 验证表数据是否正确加载 #

创建完表后，先执行一条简单查询确认数据是否正常读取：

SELECT * FROM table1 LIMIT 5;

检查返回结果里的address字段是否正确显示为JSON字符串，确保没有加载错误。

3. 执行你想要的目标查询 #

根据你定义的address字段类型，选择对应的查询语句：

情况一：address是JSON类型 #

这时候你可以直接用你想要的类似字典访问的语法查询：

SELECT uid 
FROM table1 
WHERE address['street'] = 'kemp road';

也可以用点符号语法，效果一样：

SELECT uid 
FROM table1 
WHERE address.street = 'kemp road';

情况二：address是STRING类型 #

如果是字符串类型，需要用Athena的JSON解析函数来提取字段，比如json_extract_scalar：

SELECT uid 
FROM table1 
WHERE json_extract_scalar(address, '$.street') = 'kemp road';

json_extract_scalar会提取JSON字符串中指定路径的字符串值，刚好匹配你的需求。

额外注意事项 #

• 确保Athena的执行角色有访问目标S3路径的权限，不然会出现权限错误。
• 如果你的TSV文件有表头，记得在创建表时加上TBLPROPERTIES ('skip.header.line.count'='1')来跳过表头行（你的示例数据里好像没有表头，所以可以忽略这条）。

内容的提问来源于stack exchange，提问作者al.moorthi