How can you filter Snowflake EXPLAIN AS TABULAR syntax when its embedded in the TABLE function? Can you filter it with anything?

huangapple go评论64阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年2月14日 00:11:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/75438443.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定