英文:
How can you filter Snowflake EXPLAIN AS TABULAR syntax when its embedded in the TABLE function? Can you filter it with anything?
问题
I have a table named Posts I would like to count and profile in Snowflake using the current Snowsight UI.
当我使用当前的Snowsight UI通过EXPLAIN使用TABULAR返回结果时,我能够通过TABLE、RESULT_SCAN和LAST_QUERY_ID函数的组合返回集合,但任何谓词、筛选器或列引用似乎都无法正常工作。
Is there a valid way to do this in Snowflake with the TABLE function or is there another way to query the output of the EXPLAIN using TABULAR?
是否有一种有效的方法在Snowflake中使用TABLE函数来完成这个操作,或者是否有另一种方法来查询使用TABULAR解释的输出?
-- Works
-- 可行
EXPLAIN using TABULAR SELECT COUNT(*) from Posts;
-- Works
-- 可行
SELECT t.* FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) as t;
-- Does not work
-- 不起作用
SELECT t.* FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) as t where operation = 'GlobalStats';
-- 无效的标识符 'OPERATION',似乎无法识别该列。
Tried the third example and expected the predicate to apply to the function output. I don't understand why the filter works on some TABLE() results and not others.
尝试了第三个示例,并期望谓词应用于函数输出。我不明白为什么筛选器适用于某些TABLE()的结果,但对于其他结果则不适用。
英文:
I have a table named Posts I would like to count and profile in Snowflake using the current Snowsight UI.
When I return the results via EXPLAIN using TABLULAR I am able to return the set with the combination of TABLE, RESULT_SCAN, and LAST_QUERY_ID functions, but any predicate or filter or column reference seems to fail.
Is there a valid way to do this in Snowflake with the TABLE function or is there another way to query the output of the EXPLAIN using TABLULAR?
-- Works
EXPLAIN using TABULAR SELECT COUNT(*) from Posts;
-- Works
SELECT t.* FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) as t;
-- Does not work
SELECT t.* FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) as t where operation = 'GlobalStats';
-- invalid identifier 'OPERATION', the column does not seem recognized.
Tried the third example and expected the predicate to apply to the function output. I don't understand why the filter works on some TABLE() results and not others.
答案1
得分: 2
需要在列名上使用双引号
其中 "operation" =
来自文档
请注意,由于DESC USER命令的输出列名是小写生成的,所以命令在查询中使用了限定标识符表示(双引号),以确保查询中的列名与已扫描的输出中的列名匹配
英文:
You need to double quote the column name
where "operation"=
From the Documentation
> Note that because the output column names from the DESC USER command
> were generated in lowercase, the commands use delimited identifier
> notation (double quotes) around the column names in the query to
> ensure that the column names in the query match the column names in
> the output that was scanned
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论