How to download all views stored in snowflake to local machine

Question

I want to create a backup of all SQL scripts (views) that are saved on snowflake (not data).
How can I do it? Obviously manual copy and pasting is not an answer.

Expected result: I have all views (sql scripts) that are in snowflake database on my local machine, file per view.

Expected result perfect version: I have all views (sql scripts) that are in snowflake on my local machine, where folders would correspond to schemas in snowflake and files would correspond to views in snowflake (files are also placed in correct folders).

Great answers below, but you could take it a step further and manage all your DDL through a version controlled tool like [DBT](https://www.getdbt.com/). That way, you would have a mechanism not only to store your DDL in text files, but also to run that DDL (instead of relying on error-prone manual processes). Otherwise, how would you know whether or not your text files are up-to-date? — Marco Roy, Nov 07 '22 at 21:32

score 3 · Answer 1 · answered Mar 04 '20 at 17:02

3

SHOW VIEWS includes the DDL in the text column. To get all views in the database:

show views in database my_database;
select "text" from table(result_scan(-1));

You can invoke it from the CLI with SNOWSQL.

answered Mar 04 '20 at 17:02

waldente

1,324
9
12

score 3 · Answer 2 · answered Mar 04 '20 at 17:04

3

You can run:

SELECT get_ddl('schema',{schema_name});

This will get you the DDL of all objects in the schema, which you can then save to a file in a folder.

answered Mar 04 '20 at 17:04

Mike Walton

6,595
2
11
22

score 3 · Answer 3 · answered Mar 05 '20 at 01:57

You just download the full database DDL

Option 1

select GET_DDL('database','databasename') and copy and save it to your machine

Option 2

Write a Python Script to get a list of schemas, views, tables, stored procs etc. and save it to its corresponding folder on your local machine. Something like this, you just have to extend it to get your perfect version output. Just install the snowflake Python connector to run the following code.

import snowflake.connector

con = snowflake.connector.connect(
    user='YourUsername',
    password='YourPassword',
    account='your snowflakeaccount',
    database='databasename',
    warehouse='datawarehousename',
    role='dbrole'
    )

cur = con.cursor()
try:
    cur.execute("SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE from information_schema.tables")
    for (TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE) in cur:
        print('{0}, {1}'.format(TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE))

        #Have another loop  to get the DDL for each object and save it to a file/folder structure, something like this..
        #cur2.execute("SELECT GET_DDL('object type information from previous query','object name'")

finally:
    cur.close()

score 0 · Answer 4 · answered Nov 07 '22 at 21:34

Great answers above, but you could take it a step further and manage all your DDL through a version controlled tool like DBT.

That way, you would have a mechanism not only to store your DDL in text files, but also to run that DDL (instead of relying on error-prone manual processes).

Otherwise, how would you know whether or not your text files are up-to-date?

How to download all views stored in snowflake to local machine

4 Answers4