CODEWITHSUNDEEP
snowflake-cloud-data-platform
drdot
drdot

Reputation: 3347

Use query return results as DB names, add schema and table, then use them in another query

I have a catalog DB that stores the names of other DBs. These DBs contains the same schema and tabls. Now I want to extract all the DB names from the catalog DB and query a specific table in all those DBs.

Here is an example:

catalog DB name: CatalogDB 
schema name: schemaExp
table name: tableExp
CatalogDB contains a list of otherDBs, e.g., otherDB1, otherDB2, otherDBXYZ, etc.

So I can get all the other DB names by

select DBName
from CatalogDB;

I can query the table in otherDB1 using the following query

select *
from otherDB1.schemaExp.tableExp;

I want to query the same tableExp in all the other DBs. How can I do that?

EDIT: I am not interested in combining tables since table content can get updated. Is it possible to query the catalog db and put the return db names in a parameter then run a query to select from each DBs from the parameter?

Upvotes: 1

Views: 564

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

So if you wanted to select row count for "all those tables" you could:

create table test.test.cat_tab(cat_db string, cat_schema string, cat_table string, other_dbs array);

create database test_a;
create schema test_a.test;
create table test_a.test.table_exp(val int);
insert into test_a.test.table_exp values (1),(2);

create database test_b;
create schema test_b.test;
create table test_b.test.table_exp(val int);
insert into test_b.test.table_exp values (3);

insert into test.test.cat_tab select 'test', 'test', 'table_exp', array_construct('test_a','test_b');

then dynamically count those great data driven rows:

declare
  counts int;
  total_counts int := 0; 
begin
 let c1 cursor for select f.value::text ||'.'|| cat_schema ||'.'|| cat_table as fqn from test.test.cat_tab,table(flatten(input=>other_dbs)) f where cat_table = 'table_exp';
  for record in c1 do
    let str := 'select count(*) from ' || record.fqn;
    execute immediate str;
    
    select $1 into counts from table(result_scan(last_query_id()));
    total_counts := total_counts + counts;
  end for;
  return total_counts;
end;
anonymous block
3

if you want to select from all those tables in a union greatness:

declare
  sql string := '';
  res resultset;
begin
 let c1 cursor for select f.value::text ||'.'|| cat_schema ||'.'|| cat_table as fqn from test.test.cat_tab,table(flatten(input=>other_dbs)) f where cat_table = 'table_exp';
  for record in c1 do
    if (sql <> '') then 
        sql := sql || ' union all '; 
    end if;
    
    sql := sql || 'select * from ' || record.fqn;
  end for;
  
  res := (execute immediate :sql);
  return table(res);
end;

gives:

VAL
1
2
3

Upvotes: 1

Related Questions