AWS Athena连接两表时如何避免主键key_id重复

一、移除重复的key_id列 #

由于使用SELECT *会同时返回两张表的key_id列导致重复，而Athena的Presto语法不支持EXCEPT排除列，可通过以下两种方式处理：

1. 利用系统表自动生成列列表（适合大量列场景） #

通过查询Athena的信息模式表，自动拼接出不含重复key_id的列清单：

• 查询TableA的所有列：

SELECT string_agg(column_name, ', ') AS table_a_cols
FROM information_schema.columns
WHERE table_schema = '你的数据库名' AND table_name = 'TableA';

• 查询TableB中除key_id外的所有列：

SELECT string_agg(column_name, ', ') AS table_b_cols
FROM information_schema.columns
WHERE table_schema = '你的数据库名' AND table_name = 'TableB' AND column_name != 'key_id';

• 将上述两个查询结果拼接，替换到SELECT语句中：

SELECT [table_a_cols结果], [table_b_cols结果]
FROM TableA A
LEFT JOIN TableB B ON A.key_id = B.key_id;

2. 显式指定列（适合列数较少场景） #

直接指定保留TableA的key_id，并列出TableB的其他列：

SELECT
    A.key_id,
    A.col1, A.col2, ..., -- TableA的其他列
    B.col1, B.col2, ...  -- TableB的其他列（排除key_id）
FROM TableA A
LEFT JOIN TableB B ON A.key_id = B.key_id;

二、优化连接操作耗时 #

针对大表JOIN性能问题，可从以下几个方向优化：

• 过滤分区数据：如果表是分区表（比如按日期、地域分区），在JOIN前通过WHERE子句过滤分区列，减少扫描的数据量：

SELECT ...
FROM TableA A
LEFT JOIN TableB B ON A.key_id = B.key_id
WHERE A.partition_date >= '2024-01-01' AND B.partition_date >= '2024-01-01';

• 更新表统计信息：执行ANALYZE命令让查询优化器获取表的准确数据分布，生成更优执行计划：

ANALYZE TABLE TableA COMPUTE STATISTICS;
ANALYZE TABLE TableB COMPUTE STATISTICS;

• 使用列式存储格式：确保表采用Parquet或ORC格式存储，这类格式比CSV等行式格式的查询效率更高，能大幅减少IO开销。
• 提前过滤无关数据：在JOIN前通过子查询过滤掉不需要的行或列，缩小参与JOIN的数据规模：

SELECT ...
FROM (SELECT key_id, col1, col2 FROM TableA WHERE 过滤条件) A
LEFT JOIN (SELECT key_id, col3, col4 FROM TableB WHERE 过滤条件) B ON A.key_id = B.key_id;

• 指定JOIN策略：如果两张表数据量差异较大，可使用广播JOIN将小表分发到所有节点，避免数据 shuffle（假设TableB是小表）：

SELECT ...
FROM TableA A
LEFT JOIN /*+ BROADCAST(B) */ TableB B ON A.key_id = B.key_id;

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