Pythonic way to optimize SQL VIEW count to extract information schema metadata from Snowflake

I have 12 VIEW tables in Snowflake and I would like to extract TABLE_NAME,CREATED,LAST_ALTERED from Snowflakes INFORMATION Schema for View tables, and also want to get row count for each 12 VIEW tables, along with metadata for Base table mentioned in below code.I was wondering if there is way get row count using below code for 12 VIEW tables or there is any better approach for this task i.e. getting TABLE_NAME,CREATED,LAST_CREATED metadata for Base Table and VIEW table in Snowflake ?

Let’s say my first 4 VIEW table names are “V_ACCOUNT”,”V_ADDRESS”,”V_COUNTRY”,”V_ORDER”

Below is my code so far.

Thanks in advance for your time and efforts!

Python Code:

    import pandas as pd
    import snowflake.connector
        
        conn = snowflake.connector.connect(
                    user="MY_USER",
                    password="MY_PSWD",
                    account="MY_ACCOUNT",
                    warehouse="COMPUTE_WH",
                    database="SNOWFLAKE_SAMPLE_DATA",
                    schema="INFORMATION_SCHEMA",
                    role="SYSADMIN"
                    )
        
        cur = conn.cursor()
        
       tables = ['CUSTOMER', 'CALL_CENTER', 'CUSTOMER_ADDRESS']
       view_tables=['V_ACCOUNT','V_ADDRESS','V_COUNTRY','V_ORDER']

cur.execute(
    f"""
    SELECT TABLE_NAME, ROW_COUNT, CREATED, LAST_ALTERED 
    FROM TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_SCHEMA='TPCDS_SF100TCL' 
    AND TABLE_NAME IN ({','.join("'" + x + "'" for x in tables)})
    UNION
    SELECT (select count(*) from DB.SCHEMA.V_ACCOUNT) AS ROW_COUNT,TABLE_NAME,CREATED,LAST_ALTERED FROM VIEWS WHERE TABLE_NAME="V_ACCOUNT"

    """
)
            df = cur.fetch_pandas_all()
        finally:
            cur.close()
        conn.close()

Answer

If you are looking for how to dynamically create a view in Python, then please take a look at Barmar’s answer on my other question here. This will give you a hint of what needs to be done for dynamic view creation. Thanks, Barmar for help!

Advertisement

Answer