如何从雪花存储过程中的给定表中动态选择列？
我正在尝试创建一个从两个表动态选择列的过程。我已经有过硬编码的查询，可以正常工作。
＃存在查询
create table mydb.result_table as
select
t1.id,
t1.name,
t1.place,
t1.product
t1.prize
t2.id,
t2.name,
t2.place,
t2.product,
t2.prize
from source_db.source_table t1
full outer join target_db_target_table t2 on
t1.id=t2.id and t1.name=t2.name
where t1.place<>t2.place
现在我需要创建一个过程，该过程基本上从给定的表名称中动态选择列，并使用给定的键连接，
因此下面类似的东西
CREATE OR REPLACE PROCEDURE result(source_db varchar, source_table VARCHAR, targt_db varchar, target_table VARCHAR, key_join varchar<not sure can pass list>, filter_col varchar )
returns string not null
language javascript
as
$$
var query= `create table mydb.result_table as
select
t1.<all columns from source_db and source_table>
t2.<all columns from targt_db and target_table >
from <source_db and source_table> as t1
full outer join <target_db_target_table> as t2
on <key_join> where <t1.filter_col <> t2.filter_col>
`
return 'success';
$$;
我看不到为这种用例动态选择列的示例。 有什么解决方案吗？
I am trying to create a procedure that choose columns dynamically from two table. I have already hardcoded query which works fine.
#Existing query
create table mydb.result_table as
select
t1.id,
t1.name,
t1.place,
t1.product
t1.prize
t2.id,
t2.name,
t2.place,
t2.product,
t2.prize
from source_db.source_table t1
full outer join target_db_target_table t2 on
t1.id=t2.id and t1.name=t2.name
where t1.place<>t2.place
Now i need to create a procedure which basically select column dynamically from given table name and join both with given keys
so something like below
CREATE OR REPLACE PROCEDURE result(source_db varchar, source_table VARCHAR, targt_db varchar, target_table VARCHAR, key_join varchar<not sure can pass list>, filter_col varchar )
returns string not null
language javascript
as
$
var query= `create table mydb.result_table as
select
t1.<all columns from source_db and source_table>
t2.<all columns from targt_db and target_table >
from <source_db and source_table> as t1
full outer join <target_db_target_table> as t2
on <key_join> where <t1.filter_col <> t2.filter_col>
`
return 'success';
$;
i don't see example of selecting columns dynamically for this kind of use case.
Any solution to this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要查询信息架构中的每个表中的列列表，并使用结果动态构建您的SQL语句。
您还需要注意多个表中存在的相同列名称，因为您显然不能在表中多次出现相同的列 - 因此您需要在适当的情况下动态重命名列
You need to query the information schema for the list of columns in each table and use the result to dynamically build your SQL statement.
You also need to be aware of the same column name existing in multiple tables as you obviously can’t have the same column appearing multiple times in a table - so you’ll need to dynamically rename columns as appropriate
只需查询信息_schema.columns视图即可访问表的列，然后您可以动态构建查询：
https://docs.snowflake.com/en/sql-reference/info-schema/columns.html
Just query the INFORMATION_SCHEMA.COLUMNS view to access the table's columns and then you can build your query dynamically:
https://docs.snowflake.com/en/sql-reference/info-schema/columns.html