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

CodePudding user response：

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

CodePudding user response：

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 ;