Why I get a "Syntax error:L compilation error: (line …" when trying to create a SNOWFLAKE UDTF which use IDENTIFIER function?

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

Why I get a "Syntax error:L compilation error: (line ..." when trying to create a SNOWFLAKE UDTF which use IDENTIFIER function?

问题

我尝试创建一个Snowflake UDTF时，为什么会出现“Syntax error: L compilation error: (line ...)”错误？如果我注释掉这行：“AND IDENTIFIER(baseline) = I.COMP”，函数就能正确编译，但不会生成预期的结果。

SQL是该函数的处理程序。

  1. CREATE OR REPLACE FUNCTION fn_Shopper_Insights(dx_id varchar, business varchar, baseline varchar)    
  2.    RETURNS TABLE(name varchar, value varchar)    
  3.    LANGUAGE SQL    
  4.    AS    
  5. $$    
  6.    SELECT V.name, V.value    
  7.    FROM (    
  8.          SELECT name, value    
  9.          FROM (    
  10.            SELECT    
  11.                Q1.GRP AS NAME,    
  12.                Q1.AGG AS VALUE,    
  13.                ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY INDEX DESC) AS RNK    
  14.            FROM (             
  15.             
  16.                  -- demographics    
  17.                  (    
  18.                    SELECT    
  19.                        D.DX_ID, D.PROJECT_TYPE AS GRP, D.MVS_DESC AS AGG, D.PCT, DIV0NULL(D.PCT, I.PCT) AS INDEX    
  20.                    FROM store_pro_demos_long D    
  21.                      INNER JOIN index_dim A USING(DX_ID)    
  22.                      INNER JOIN index_demos_pct I    
  23.                        ON D.MVS_DESC = I.MVS_DESC    
  24.                        AND IDENTIFIER(baseline) = I.COMP    
  25.                        AND I.BASELINE = baseline    
  26.                    WHERE D.DX_ID = dx_id
  27.                  )
  28.            ) Q1
  29.          ) WHERE RNK = 1
  30.        ) V
  31. $$;
英文:

Why I get a "Syntax error:L compilation error: (line ..." when trying to create a Snowflake UDTF? If I comment the line: "AND IDENTIFIER(baseline) = I.COMP" the function compiles correctly but do not generate the expected results.

SQL is the handler for the function.

  1. CREATE OR REPLACE FUNCTION fn_Shopper_Insights(dx_id varchar, business varchar, baseline varchar)    
  2.    RETURNS TABLE(name varchar, value varchar)    
  3.    LANGUAGE SQL    
  4.    AS    
  5. $$    
  6.    SELECT V.name, V.value    
  7.    FROM (    
  8.          SELECT name, value    
  9.          FROM (    
  10.            SELECT    
  11.                Q1.GRP AS NAME,    
  12.                Q1.AGG AS VALUE,    
  13.                ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY INDEX DESC) AS RNK    
  14.            FROM (             
  15.                  -- demographics    
  16.                  (    
  17.                    SELECT    
  18.                        D.DX_ID, D.PROJECT_TYPE AS GRP, D.MVS_DESC AS AGG, D.PCT, DIV0NULL(D.PCT, I.PCT) AS INDEX    
  19.                    FROM store_pro_demos_long D    
  20.                      INNER JOIN index_dim A USING(DX_ID)    
  21.                      INNER JOIN index_demos_pct I    
  22.                        ON D.MVS_DESC = I.MVS_DESC    
  23.                        AND IDENTIFIER(baseline) = I.COMP    
  24.                        AND I.BASELINE = baseline    
  25.                    WHERE D.DX_ID = dx_id
  26.                  )
  27.            ) Q1
  28.          ) WHERE RNK = 1
  29.        ) V
  30. $$;

答案1

得分: 1

三级嵌套是不必要的，可以简化为：

  1. SELECT    
  2.     D.PROJECT_TYPE AS GRP, 
  3.     D.MVS_DESC AS AGG
  4. FROM store_pro_demos_long AS D    
  5. JOIN index_dim AS A USING(DX_ID)    
  6. JOIN index_demos_pct AS I    
  7.   ON D.MVS_DESC = I.MVS_DESC    
  8.  AND IDENTIFIER(baseline) = I.COMP    
  9.  AND I.BASELINE = baseline    
  10. WHERE D.DX_ID = dx_id
  11. QUALIFY ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY DIV0NULL(D.PCT, I.PCT) DESC)=1;

第二个 IDENTIFIER(baseline) 可以替换为 CASE 表达式：

  1. AND IDENTIFIER(baseline) = I.COMP
  2. <=>
  3. AND I.COMP = CASE WHEN baseline = 'col_name1' THEN D.col_name1
  4.                   WHEN baseline = 'col_name2' THEN D.col_name2
  5.                   ELSE D.col_name_default
  6.              END

注意：代码部分未被翻译。

英文:

Three level nesting is unnecessary and could be simplified:

  1. SELECT V.name, V.value    
  2. FROM (    
  3.      SELECT name, value    
  4.      FROM (    
  5.        SELECT    
  6.            Q1.GRP AS NAME,    
  7.            Q1.AGG AS VALUE,    
  8.            ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY INDEX DESC) AS RNK    
  9.        FROM (             
  10.              -- demographics    
  11.              (    
  12.                SELECT    
  13.                    D.DX_ID, D.PROJECT_TYPE AS GRP, D.MVS_DESC AS AGG, D.PCT, DIV0NULL(D.PCT, I.PCT) AS INDEX    
  14.                FROM store_pro_demos_long D    
  15.                  INNER JOIN index_dim A USING(DX_ID)    
  16.                  INNER JOIN index_demos_pct I    
  17.                    ON D.MVS_DESC = I.MVS_DESC    
  18.                    AND IDENTIFIER(baseline) = I.COMP    
  19.                    AND I.BASELINE = baseline    
  20.                WHERE D.DX_ID = dx_id
  21.              )
  22.        ) Q1
  23.      ) WHERE RNK = 1
  24.    ) V

To:

  1. SELECT    
  2.     D.PROJECT_TYPE AS GRP, 
  3.     D.MVS_DESC AS AGG
  4. FROM store_pro_demos_long AS D    
  5. JOIN index_dim AS A USING(DX_ID)    
  6. JOIN index_demos_pct AS I    
  7.   ON D.MVS_DESC = I.MVS_DESC    
  8.  AND IDENTIFIER(baseline) = I.COMP    
  9.  AND I.BASELINE = baseline    
  10. WHERE D.DX_ID = dx_id
  11. QUALIFY ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY DIV0NULL(D.PCT, I.PCT) DESC)=1;

Second IDENTIFIER(baseline) could be replaced with CASE expression:

  1. AND IDENTIFIER(baseline) = I.COMP
  2. &lt;=&gt;
  3. AND I.COMP = CASE WHEN baseline = &#39;col_name1&#39; THEN D.col_name1
  4.                   WHEN baseline = &#39;col_name2&#39; THEN D.col_name2
  5.                   ELSE D.col_name_default
  6.              END

答案2

得分: 0

我在考虑使用您的建议，将 IDENTIFIER 函数替换为 dbt 和 Jinja。我将创建UDTF作为dbt模型，然后使用pre_hook或macro来查询不同的基线，并使用jinja动态生成CASE语句：

  1. AND IDENTIFIER(baseline) = I.COMP
  2. <=> 
  3. AND I.COMP = CASE {%- for b in baselines -%}
  4.                    WHEN baseline = {{b}} THEN D.{{b}}
  5.                   {%- endfor -%}
  6.                    ELSE D.col_name_default
  7.              END
英文:

I am thinking in using your recommendation of replacing IDENTIFIER function with dbt and Jinja. I will create the UDTF as a dbt model, and then use a pre_hook or a macro to query the different baselines and generate the case dynamically with jinja:

  1. AND IDENTIFIER(baseline) = I.COMP
  2. &lt;=&gt;
  3. AND I.COMP = CASE {%- for b in baselines -%}
  4.                    WHEN baseline = {{b}} THEN D.{{b}}
  5.                   {%- endfor -%}
  6.                    ELSE D.col_name_default
  7.              END

huangapple
  • 本文由 发表于 2023年6月22日 03:55:59
  • 转载请务必保留本文链接：https://go.coder-hub.com/76526722.html
  • snowflake-cloud-data-platform
  • user-defined-functions
匿名

发表评论

匿名网友

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

确定