如何通过单次调用在AWS Athena中仅列出真实表排除视图

原有方案失效原因 #

SHOW TABLES、SHOW VIEWS属于Athena的DDL类元数据语句，本身不支持在CTE、子查询中嵌套使用，你之前编写的with views as (SHOW VIEWS) SHOW TABLES NOT IN views类语句属于语法不兼容，无法执行。
同时原生SHOW TABLES语句不会对返回结果做表类型区分，会同时输出物理表和视图，无法直接满足只取真实表的需求。

可行实现（单次SQL调用即可完成） #

直接查询Athena内置的information_schema标准元数据库，通过表类型字段过滤即可，不需要多次调用接口、不需要客户端侧做差集计算：

-- 将下方字符串替换为你需要查询的目标数据库名
SELECT table_name
FROM information_schema.tables
WHERE 
  table_schema = '替换为你的目标数据库名'
  AND table_type = 'BASE TABLE';

规则说明 #

• table_schema对应Athena中的数据库名，过滤后就只会返回指定库下的元数据
• table_type = 'BASE TABLE'是固定过滤条件，返回结果仅包含真实物理表，自动排除所有视图
• 如果需要单独查询视图列表，只需要把过滤条件改为table_type = 'VIEW'即可，返回结果和SHOW VIEWS语句完全一致

方案优势 #

• 完全符合单次调用要求，只需要执行一次SQL即可拿到结果
• 兼容Athena v2、v3所有主流引擎版本，无版本依赖
• 查询性能远高于多次执行SHOW语句再做差集的方案，没有多余数据传输

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