如何高效获取AWS Athena中指定设备的表与列列表？

我正在开发集成AWS Athena的Grafana仪表盘，需要支持用户选择特定设备数据库中的所有可用列。表的命名规则是deviceid_signalgroupname，信号数据存储在列中。

当前使用的查询语句能实现需求，但耗时8-10秒：

SELECT 
    CONCAT(table_name, '.', column_name) AS table_column
FROM 
    information_schema.columns 
WHERE 
    table_name LIKE '%${device:csv}%'
    AND column_name NOT IN ('date_created', 't')
ORDER BY 
    table_name, 
    ordinal_position;

现有环境：约400张表，每张表包含5-10列，此次查询需要提取约50张表的各5列，希望找到更高效的实现方式。

查询执行计划分析 #

查询计划
排队: 618.32微秒, 分析: 2.13毫秒, 规划: 2.51秒, 执行: 22.13秒
Fragment 1 [单节点]
    CPU: 1.53毫秒, 调度: 1.56毫秒, 阻塞 1.62分钟 (输入: 19.49秒, 输出: 0.00纳秒), 输入: 1554行 (142.90KB), 扫描数据量: 0B; 每个任务: 平均: 1554.00行 标准差: 0.00, 输出: 1554行 (83.05KB)
    输出结构: [concat]
    输出分区: 单节点 []
    投影[投影位置=本地]
    │   结构: [concat:字符串]
    │   CPU: 0.00纳秒 (0.00%), 调度: 0.00纳秒 (0.00%), 阻塞: 0.00纳秒 (0.00%), 输出: 1554行 (83.05KB)
    │   输入平均: 388.50行, 输入标准差: 173.21%
    └─ 本地交换[分区方式=轮询]
       │   结构: [ordinal_position:大整数, concat:字符串, table_name:字符串]
       │   CPU: 0.00纳秒 (0.00%), 调度: 0.00纳秒 (0.00%), 阻塞: 1.30分钟 (40.00%), 输出: 1554行 (142.90KB)
       │   输入平均: 1554.00行, 输入标准差: 0.00%
       └─ 远程合并[源FragmentID = [2]]
              结构: [ordinal_position:大整数, concat:字符串, table_name:字符串]
              CPU: 1.00毫秒 (0.28%), 调度: 1.00毫秒 (0.01%), 阻塞: 19.49秒 (10.00%), 输出: 1554行 (142.90KB)
              输入平均: 1554.00行, 输入标准差: 0.00%

Fragment 2 [轮询分区]
    CPU: 39.00毫秒, 调度: 41.26毫秒, 阻塞 1.62分钟 (输入: 1.30分钟, 输出: 0.00纳秒), 输入: 1554行 (142.90KB), 扫描数据量: 0B; 每个任务: 平均: 1554.00行 标准差: 0.00, 输出: 1554行 (142.90KB)
    输出结构: [ordinal_position, concat, table_name]
    输出分区: 单节点 []
    本地合并[排序依据 = [table_name 升序 空值最后, ordinal_position 升序 空值最后]]
    │   结构: [ordinal_position:大整数, concat:字符串, table_name:字符串]
    │   CPU: 4.00毫秒 (1.10%), 调度: 4.00毫秒 (0.02%), 阻塞: 19.49秒 (10.00%), 输出: 1554行 (142.90KB)
    │   输入平均: 388.50行, 输入标准差: 34.28%
    └─ 部分排序[排序依据 = [table_name 升序 空值最后, ordinal_position 升序 空值最后]]
       │   结构: [ordinal_position:大整数, concat:字符串, table_name:字符串]
       │   CPU: 34.00毫秒 (9.39%), 调度: 36.00毫秒 (0.18%), 阻塞: 0.00纳秒 (0.00%), 输出: 1554行 (142.90KB)
       │   输入平均: 388.50行, 输入标准差: 34.28%
       └─ 远程源[源FragmentID = [3]]
              结构: [ordinal_position:大整数, concat:字符串, table_name:字符串]
              CPU: 0.00纳秒 (0.00%), 调度: 0.00纳秒 (0.00%), 阻塞: 1.30分钟 (40.00%), 输出: 1554行 (142.90KB)
              输入平均: 388.50行, 输入标准差: 34.28%

Fragment 3 [数据源]
    CPU: 322.87毫秒, 调度: 19.47秒, 阻塞 0.00纳秒 (输入: 0.00纳秒, 输出: 0.00纳秒), 输入: 2128行 (132.89KB), 扫描数据量: 132.89KB; 每个任务: 平均: 2128.00行 标准差: 0.00, 输出: 1554行 (142.90KB)
    输出结构: [ordinal_position, concat, table_name]
    输出分区: 轮询 []
    扫描过滤投影[表 = awsdatacatalog:InformationSchemaTableHandle{catalogName=awsdatacatalog, table=COLUMNS, prefixes=[awsdatacatalog.information_schema.*, awsdatacatalog.dbparquet123.*, awsdatacatalog.dbparquet231004.*, awsdatacatalog.dbparquet456.*, awsdatacatalog.manualcrawlerdbtest1.*, awsdatacatalog.testdatabase.*, awsdatacatalog.testdatabase2.*], limit=OptionalLong.empty}, 过滤条件 = ((NOT ("column_name" IN (字符串 'date_created', 字符串 't'))) AND "$like"("table_name", "$literal$"(from_base64('DgAAAFZBUklBQkxFX1dJRFRIAQAAAAEAAAAPAAAAAA8AAAAKAAAAJTk1OGQyMjE5JQA=')))), 投影位置=本地]
        结构: [ordinal_position:大整数, concat:字符串, table_name:字符串]
        CPU: 323.00毫秒 (89.23%), 调度: 19.47秒 (99.79%), 阻塞: 0.00纳秒 (0.00%), 输出: 1554行 (142.90KB)
        输入平均: 2128.00行, 输入标准差: 0.00%
        concat := concat("table_name", 字符串 '.', "column_name")
        ordinal_position := ordinal_position
        column_name := column_name
        table_name := table_name
        输入: 2128行 (132.89KB), 过滤比例: 26.97%, 物理输入: 132.89KB, 物理输入时间: 0.00纳秒

优化方案 #

• 避免前缀通配符LIKE：当前%${device:csv}%会触发全表扫描，若设备ID是表名前缀，改为${device:csv}%；若是后缀则改为%${device:csv}。如果设备ID在表名中间，建议调整表命名规则（比如统一将deviceid放在开头），或提前导出元数据到带索引的专用表。
• 预存元数据到专用表：通过Glue爬虫或Lambda定期同步information_schema.columns的必要字段（table_name、column_name、ordinal_position）到Parquet格式的元数据表，对table_name设置分区或索引。查询时直接读取该表，速度远快于查询information_schema。
• 限制数据库范围：如果明确目标表所在数据库，添加table_schema = '目标数据库名'条件，缩小扫描范围。从执行计划看，当前扫描了多个数据库，过滤后能大幅减少处理行数。
• 转移排序到前端：若Grafana支持前端排序，去掉查询中的ORDER BY，减少Athena的计算开销。
• 启用元数据缓存：开启Athena元数据缓存，同时在Grafana中配置查询缓存，减少重复请求的耗时。

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