是否可以生成动态SQL以返回雪花中具有不同列的行

admin

是否可以生成动态SQL以返回雪花中具有不同列的行

sql

我们将各种数据作为值/对存储在JSON列中。所有行的对名称都不相同，并且取决于某些元数据。

有没有一种方法可以编写一条SQL语句来根据某些元数据检索其中一些值对？

像动态生成的东西

SELECT MyJson:FruitShape, MyJson:Fruitsize FROM MyTable WHERE ...

我知道我可以在存储过程中创建和执行动态SQL，但是此SQL仅限于返回标量值。我们需要一个记录集。

阅读 190

2021-07-01

共1个答案

admin

鉴于此输入：

create or replace table t as
select parse_json($1) my_json
from values
('{ "FruitShape":"Round", "FruitSize":55 } '),
('{ "FruitShape":"Square"  } '),
('{ "FruitShape":"Oblong", "FruitSize":22, "FruitColor":"Chartreuse" } ')
;

此查询将生成动态SQL：

select 'select ' 
  || (select listagg(distinct 'my_json:'||key::text, ',') from t, lateral flatten(input=>t.my_json, mode=>'OBJECT')) 
  || ' from t;';

生成的SQL及其输出：

select my_json:FruitShape, my_json:FruitSize, my_json:FruitColor from t;

MY_JSON:FRUITSHAPE | MY_JSON:FRUITSIZE | MY_JSON:FRUITCOLOR
-------------------+-------------------+-------------------
"Round"            | 55                | NULL              
"Square"           | NULL              | NULL              
"Oblong"           | 22                | "Chartreuse"

此存储过程将执行动态SQL，而无需剪切和粘贴：

create or replace procedure p()
returns string
language javascript
strict
execute as caller
as
$$
  const statement1 =  `
    select 'select ' 
      || (select listagg(distinct 'my_json:'||key::text, ', ') from t, lateral flatten(input=>t.my_json, mode=>'OBJECT')) 
      || ' from t'
  `
  const rs1 = snowflake.execute ({sqlText: statement1})
  rs1.next()
  const statement2 = rs1.getColumnValue(1)
  const rs2 = snowflake.execute ({sqlText: statement2})
  return 'SUCCESS'
$$
;

然后，您可以调用存储过程并收集结果：

call p();
select * from table(result_scan(-2))

您提到了根据某些元数据来限制输出。您可以在动态SQL中执行此操作，例如，通过过滤不同的字段列表。

感谢davidgarrison的result_scan（）技术！

希望对您有所帮助。

2021-07-01