How to merge two tables with different column number in Snowflake?

Question

I am querying TABLE_SCHEMA,TABLE_NAME,CREATED,LAST_ALTERED columns from Snowflake information schema. VIEWS. Next, I would like to MERGE that table with row count for the view. Below are my queries I am running in Snowflake my issue is I am not sure how to combine these two table in 1 table ?

Note: I am new to Snowflake. Please provide code with explanation.

Thanks in advance for help!

Query 1

 SELECT TABLE_SCHEMA,TABLE_NAME,CREATED,LAST_ALTERED FROM DB.SCHEMA.VIEWS
WHERE TABLE_SCHEMA="MY_SHEMA" AND TABLE_NAME IN ('VIEW_TABLE1','VIEW_TABLE2','VIEW_TABLE3')

Query 2

SELECT COUNT(*) FROM DB.SCHEMA.VIEW_TABLE1
UNION ALL SELECT COUNT(*) FROM DB.SCHEMA.VIEW_TABLE2

Lukasz Szozda · Accepted Answer · 2022-05-20T17:24:03.683

To get result of the COUNT(*) needs to be built dynamically and attached to the "driving query".

Sample data:

CREATE VIEW VIEW_TABLE1(c)
AS 
SELECT 1;

CREATE VIEW VIEW_TABLE2(e)
AS 
SELECT 2 UNION ALL SELECT 4;

CREATE VIEW VIEW_TABLE3(f)
AS 
SELECT 3;

Full query:

DECLARE 
    QUERY STRING;
    RES RESULTSET;
BEGIN
   SELECT 
    LISTAGG(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        $$SELECT  '<TABLE_SCHEMA>'   AS TABLE_SCHEMA,
                  '<TABLE_NAME>'     AS TABLE_NAME,
                  '<CREATED>'        AS CREATED,
                  '<LAST_ALTERED>'   AS LAST_ALTERED,
                  COUNT(*) AS cnt
           FROM <tab_name>
        $$,
       '<TABLE_SCHEMA>', v.TABLE_SCHEMA),
       '<TABLE_NAME>', v.TABLE_NAME),
       '<CREATED>', v.CREATED),
       '<LAST_ALTERED>', v.LAST_ALTERED),
       '<tab_name>', CONCAT_WS('.', v.table_catalog, v.table_schema, v.table_name)),
                                       
        ' UNION ALL ') WITHIN GROUP (ORDER BY CONCAT_WS('.', v.table_catalog, v.table_schema, v.table_name))
   INTO :QUERY
   FROM INFORMATION_SCHEMA.VIEWS v
   WHERE TABLE_SCHEMA='PUBLIC'
     AND TABLE_NAME IN ('VIEW_TABLE1','VIEW_TABLE2','VIEW_TABLE3');


  RES := (EXECUTE IMMEDIATE :QUERY);
  RETURN TABLE(RES);
END;

Output:

Rationale:

The ideal query would be(pseudocode):

SELECT TABLE_SCHEMA,TABLE_NAME,CREATED,LAST_ALTERED,
      EVAL('SELECT COUNT(*) FROM ' ||  view_name) AS row_count
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='MY_SHEMA'
  AND TABLE_NAME IN ('VIEW_TABLE1','VIEW_TABLE2','VIEW_TABLE3');

Such construct EVAL(dynamic query) at SELECT list does not exist as it would require building a query on the fly and execute per each row. Though for some RDBMSes are workaround like dbms_xmlgen.getxmltype

Receiving an error that says "SQL Compilation error: Suntax error line 2 at position 17 unexpected — biggboss2019, May 20 '22 at 17:13
@biggboss2019 Please use Snowsight, not classic web UI! - https://docs.snowflake.com/en/developer-guide/snowflake-scripting/running-examples.html#introduction — Lukasz Szozda, May 20 '22 at 17:14
I am trying to run Ideal query by repalcing view_name with the view name of my table. I repalced schema and other view_table#'s. But receiving "Unknown function EVAL" error in Web UI. The first code works fine. Thank you for that! — biggboss2019, May 20 '22 at 17:21
@biggboss2019 `But receiving "Unknown function EVAL" error in Web U` => It is pseudocode to explain why the dynamic SQL was necessary in step one. EVAL does not exist unfortunately. — Lukasz Szozda, May 20 '22 at 17:22

score 0 · Answer 2 · answered May 20 '22 at 17:02

0

Include table/view names as string in your count(*) queries and then you can join.

Example below -

select * from
(SELECT TABLE_SCHEMA,TABLE_NAME,CREATED FROM information_schema.tables
WHERE TABLE_SCHEMA='PUBLIC' AND TABLE_NAME IN ('D1','D2')) t1
left join
(
SELECT 'D1' table_name, COUNT(*) FROM d1
UNION ALL SELECT 'D2',COUNT(*) FROM d2) t2
on t1.table_name = t2.table_name ;

TABLE_SCHEMA	TABLE_NAME	CREATED	TABLE_NAME	COUNT(*)
PUBLIC	D1	2022-04-06 14:24:56.224 -0700	D1	12
PUBLIC	D2	2022-04-06 14:25:27.276 -0700	D2	5

answered May 20 '22 at 17:02

Pankaj

2,692
2
6
18

Thank you this works too! Howevr what will be the step for adding more view tables ? – biggboss2019 May 20 '22 at 17:33
1

This is more of a static solution where UNION all clause needs to be added for more view counts. In case you only have limited number of views to check then you can use this. Dynamic solution is provided by @Lukasz which will help to get as needed on the fly. – Pankaj May 20 '22 at 17:44

How to merge two tables with different column number in Snowflake?

2 Answers2