如何在Snowflake中查询LIST命令输出并按last_modified排序？

Snowflake的LIST是系统命令而非表函数，无法直接嵌入SELECT语句的FROM子句中，要对其输出进行查询或排序，需借助RESULT_SCAN函数捕获它的执行结果。

具体操作步骤： #

1. 先执行目标LIST命令：

LIST @MY_DATABASE.MY_SCHEMA.%my_table/path/to/data PATTERN = '.*[.]csv.*';

2. 立即执行SELECT语句，通过RESULT_SCAN(LAST_QUERY_ID())获取上一步LIST的结果并排序：

SELECT *
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY last_modified DESC;

合并执行方式（单语句完成） #

如果想在一个语句里完成，可使用EXECUTE IMMEDIATE执行LIST，再扫描结果：

EXECUTE IMMEDIATE 'LIST @MY_DATABASE.MY_SCHEMA.%my_table/path/to/data PATTERN = ''[.]csv$''';
SELECT name, last_modified, size
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY last_modified ASC;

说明 #

• RESULT_SCAN函数用于捕获指定查询ID的结果集，LAST_QUERY_ID()默认返回最近一次查询的ID，确保它指向刚执行的LIST命令即可。
• 捕获结果后，你可以像操作普通表一样添加筛选、排序、聚合等SQL逻辑。

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