在select语句中选择多个模式

Question

正在访问的数据库位于Snowflake;不确定幕后的存储细节。

我现在有一个查询，从41个数据表中创建一个新视图，存储在同一个数据库下的不同模式中，看起来像这样:

CREATE VIEW all_data AS
SELECT * FROM db.schema1.data UNION ALL
SELECT * FROM db.schema2.data UNION ALL
SELECT * FROM db.schema3.data

该查询每天运行。我的问题是，我每隔几天就会添加新的数据表，我必须手动编辑查询以包含这些新表，因为它们存储在不同的模式下(模式的命名方案也不一致，由于我无法控制的原因)。是否有一种方法，我可以选择数据库中的所有模式与子查询，这将允许我每天运行查询，而不需要手动更新时，新的模式+表添加?

我希望结果查询的结构有点像

CREATE VIEW all_data as 
SELECT * FROM [SELECT schemas from db].data

但不确定如何工作，以及如何正确地合并结果数据

Answer 1

不幸的是，在Snowflake中还不能动态构造SQL语句。当然，你也可以用支持的一种语言(如Python、JS)编写一个脚本，先找到所有的模式，然后构造一个完整的SQL语句。

Answer 2

可以查询可用的表和模式列表。SQL权威有一篇很好的文章:http://blog.sqlauthority.com/2009/06/17/sql-server-list-schema-name-and-table-name-for-database/

简而言之，查询最终是沿着这些行来拉出表和模式的列表:

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables

尽管您必须在where子句中添加数据库名称以指向正确的数据库

Answer 3

随着Snowflake Scripting的发布，在Snowflake内部动态重建视图现在是非常可能的。

create database dynamic_views;
create schema dynamic_views.schema_base;
create schema dynamic_views.schema1;
create table dynamic_views.schema1.data(id int) as select * from values (1);

我们可以使用INFORMATION_SCEMA。查找所有的DATA表:

SELECT table_schema 
FROM dynamic_views.information_schema.tables 
WHERE table_name = 'DATA';

<表类> TABLE_SCHEMA tbody> <<tr>代码基于SCHEMA1中tbody>

对于任何想要解决这个问题的人来说，这是我使用FETCH的想法

Declare @str nvarchar(maX)
 Declare @i int 
 Set @i =(Select max(id ) from Clinics );
set @str='';
declare @Id int
 declare cur CURSOR LOCAL for
    select  [Id]  from  [dbo].[Clinics]    
    GROUP BY [Id]
open cur
fetch next from cur into @Id
while @@FETCH_STATUS = 0 BEGIN
if @i>@id 
    begin
set @str=@str+ 'sELECT '+ LTRIM(RTRIM(Convert(varchar(6),@Id))) + ',* fROM ' + quotename(LTRIM(RTRIM(CONVERT(VARCHAR(8),@Id))))+'.[Clinic_Benefits] UNION ALL ';
end
else
begin
set @str=@str+ 'sELECT '+ LTRIM(RTRIM(Convert(varchar(6),@Id))) + ',* fROM ' + quotename(LTRIM(RTRIM(CONVERT(VARCHAR(8),@Id))))+'.[Clinic_Benefits]  ';
end
fetch next from cur into @Id
END
close cur
deallocate cur
print @str;
exec (@str);