How to retrieve all the catalog names , schema names and the table names in a database like snowflake or any such database?

user_13711545 0
Viewed 1653

I need to drop some columns and uppercase the data in snowflake tables. For which I need to loop through all the catalogs/ dbs, its respective schemas and then the tables. I need this to be in python to list of the catalogs schemas and then the tables after which I will be exicuting the SQL query to do the manipulations.

How to proceed with this?

1.List all the catalog names

2.List all the schema names

3.List alll the table names

I have established a connection using python snowflake connector

snowflake-schema snowflake-cloud-data-platform sql python database
3 Answers

Your best source for this information is in your SNOWFLAKE.ACCOUNT_USAGE share that Snowflake provides. You'l need to grant privileges to whatever role you are using to connect with Python. From there, though, there is are the following views: DATABASES, SCHEMATA, TABLES, and more.

user_121789800

The easiest way would be to follow the below process

show databases;
select "name" from table(result_scan(last_query_id()));

This will give you the list of Databases. Put them in a list. Traverse through this list and on each item do the following:

use <DBNAME>;
show schemas;
select "name" from table(result_scan(last_query_id()));

Get the list of schemas

use schema <SchemaName>;
show tables;
select "name" from table(result_scan(last_query_id()));

Get the list of tables and then run your queries.

user_36011300

You probably will not need the result_scan. Recently, I created a python program to list all columns for all tables within Snowflake. My requirement was to validate each column and calculate some numerical statistics of the columns. I was able to do it using 'Show Columns' only. I have open sourced some of the common snowflake operations which is available here

https://github.com/Infosys/Snowflake-Python-Development-Framework

You can clone this code and then use this framework to create your python program to list the columns as below and then you can do whatever you would like with the column details

##
from utilities.sf_operations import Snowflakeconnection

connection = Snowflakeconnection(profilename ='snowflake_host')
sfconnectionresults = connection.get_snowflake_connection()

sfconnection = sfconnectionresults.get('connection')
statuscode = sfconnectionresults.get('statuscode')
statusmessage = sfconnectionresults.get('statusmessage')

print(sfconnection,statuscode,statusmessage)

snow_sql = 'SHOW COLUMNS;'
queryresult = connection.execute_snowquery(sfconnection,snow_sql);
print(queryresult['result'])
print('column_name|table_name|column_attribute')
print('---------------------------------------------')
for rows in queryresult['result']:
    table_name = rows[0]
    schema_name = rows[1]
    column_name = rows[2]
    column_attribute = rows[3]
    is_Null = rows[4]
    default_Value = rows[5]
    kind = rows[6]
    expression = rows[7]
    comment = rows[8]
    database_name = rows[9]
    autoincrement = rows[10]

    print(column_name+'|'+table_name+'|'+column_attribute)
user_100178900
Related