虫子和尖角是生活的一部分.我并不过分担心这件事的发生.我更关心的是如何理解why,因为这意味着Spark如何解析查询,这是我根本不理解的.此外,我想不出任何原因,为什么您会故意想要这样做,从而导致这个错误.但很容易意外地这样做(定义一个未使用的CTE),这会给您留下一个需要解决的噩梦般的错误.
除了"EXPLAIN YADA_YADA"之外,我还可以做什么来进一步了解失败的SQL命令的背后?
提供屏幕截图只是为了证明我没有失go 理智,相关代码也以纯文本形式包含在内.
型号:DBR 13.3 LTS
Spark版本:3.4.1
Set Up and Summary: N个独立运行的临时视图.最终相关临时视图.使用全局或局部临时视图也会观察到相同的行为.
当主临时视图在最终依赖视图的CTE中被引用时,所有CTE必须位于最终 Select 的命名空间中(即联接到),否则主视图完全超出命名空间的范围.
create or replace global temporary view gtv_1 as
select 1 as dummy_1;
create or replace global temporary view gtv_2 as
select 1 as dummy_2;
This works (note the superfluous join to cte2 despite not being in the select):个
create or replace global temporary view gtv_3 as
with cte1 as (
select dummy_1
from global_temp.gtv_1
),
cte2 as (
select dummy_2
from global_temp.gtv_2
)
select cte1.*
from cte1
inner
join cte2
on cte1.dummy_1 = cte2.dummy_2;
select *
from global_temp.gtv_3;
dummy_1
1
This does not work: Note the absence of the superfluous join个
create or replace global temporary view gtv_3 as
with cte1 as (
select dummy_1
from global_temp.gtv_1
),
cte2 as (
select dummy_2
from global_temp.gtv_2
)
select cte1.*
from cte1;
select *
from global_temp.gtv_3;
失败并返回错误:
[TABLE_OR_VIEW_NOT_FOUND]找不到表或视图global_temp
.gtv_2
.验证架构和目录的拼写和正确性.
如果没有使用模式限定名称,请验证Current_SCHEMA()输出,或者使用正确的模式和目录限定名称.
若要容忍删除时的错误,请使用DROP VIEW(如果存在)或DROP TABLE(如果存在).
explain select * from global_temp.gtv_3;
Issue | Description |
---|---|
Error Type | Query Planning Error |
Error Message | [TABLE_OR_VIEW_NOT_FOUND] The table or view global_temp .gtv_2 cannot be found. Verify the spelling and correctness of the schema and catalog. If not qualified, check the current_schema() output or qualify the name with the correct schema and catalog. To tolerate the error on drop, use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS . |
Error Location | Line 7, Position 13 |
EDIT:个
已将其追溯到此配置选项
不会假装我有资格解释为什么这很重要.看起来像是引入了用嵌套CTE命名解决其他问题的行为.但只要看一看代码,我就很满意CTE引用足够复杂,奇怪的事情是可以理解的.
所以这是可行的,有遗产,只有遗产.
%sql
set spark.sql.legacy.ctePrecedencePolicy = LEGACY; -- LEGACY works. Switching to CORRECTED or EXCEPTION does not.
create or replace temporary view gtv_1 as
select 1 as dummy_1;
create or replace temporary view gtv_2 as
select 1 as dummy_2;
create or replace temporary view gtv_3 as
with cte1 as (
select dummy_1
from gtv_1
),
cte2 as (
select dummy_2
from gtv_2
)
select cte1.*
from cte1;
select *
from gtv_3