时间戳

首页 > 解决方案 > 从作为输入传递给存储过程 Snowflake 的表数组创建主视图

stored-procedures - 从作为输入传递给存储过程 Snowflake 的表数组创建主视图

问题描述

我想创建一个主视图，将所有作为输入传递给雪花存储过程的表结合起来。请帮助了解如何为此构建代码。

创建或替换过程 TEST_PROC("SRC_DB" VARCHAR(30), "SRC_SCHEMA" VARCHAR(30), "TGT_DB" VARCHAR(30), "TGT_SCHEMA" VARCHAR(30))

  RETURNS varchar

  LANGUAGE JAVASCRIPT

  EXECUTE AS OWNER

as

$$
var result = '';

var tab = 'TABLE1,TABLE2'
var get_tables = `
with cte as(select value from table(SPLIT_TO_TABLE 
(('${tab}'),','))
) select value from cte;`
var tables_name_master=snowflake.execute ({sqlText: get_tables});
var lcols_agg = '';

while(tables_name_master.next()){
var table_value = tables_name_master.getColumnValue(1); 

var column_list = `
WITH cte2 as (select COLUMN_NAME , listagg(TABLE_NAME, ', ') 
within group (order by COLUMN_NAME)  A
    from  ${SRC_DB}.information_schema.COLUMNS 
    where TABLE_SCHEMA= '${SRC_SCHEMA}' and TABLE_NAME in (select 
value from table(SPLIT_TO_TABLE (('${tab}'),',')))
 group by COLUMN_NAME order by COLUMN_NAME
),

 cte3 as (select 'x' x, COLUMN_NAME,iff(contains(A,'${table_value}'),COLUMN_NAME,CONCAT('NULL AS \"',COLUMN_NAME,'\"')) valuess from cte2 order by COLUMN_NAME
    )select listagg(valuess,',') final FROM cte3 GROUP BY x
    `;

var rs = snowflake.execute({ sqlText: column_list });
while(rs.next()){
lcols_agg += "SELECT " + rs.getColumnValue(1) + " FROM "+ SRC_DB+"."+SRC_SCHEMA+"."+tables_name_master.getColumnValue(1) + "\n" +"UNION " +"\n"
}
      }
var count1 = 0 ;
count1 = lcols_agg.length
result =   lcols_agg.substring(0,(count1-7)); 

const create_union_view = `
        create or replace view abcd AS ${result}
;`
var view_create = snowflake.execute({ sqlText: create_union_view });
view_create.next()
return result

$$
;

call  SP_TEST('ABC','DEF','PQR','STU');

THis generates my final view statement as 
CREATE OR REPLACE VIEW ABCD AS 
COL1,COL2,COL3
UNION
NULL AS "COL2",NULL AS "COL3",COL1

Now due to mismatch of order od columns in union the view is throwing error while we do select * from abcd, any way we can have the columns of both tables in same order or any other work around?

标签： stored-proceduressnowflake-cloud-data-platform

解决方案

您需要在 cte3 表达式之后按函数 listagg 排序：

代替

select listagg(valuess,',') final FROM cte3 GROUP BY x

将其更改为：

select listagg(valuess,',') within group (order by valuess) final FROM cte3 GROUP BY x

看看这是否有助于解决订单问题。

推荐阅读