SQL

---

snowmobile.core.sql

inherits all methods of a SQL class that generates and executes raw SQL from inputs; its purpose is to provide a simple, on-hand Python API for querying metadata and executing basic administrative commands against Snowflake.

By default, sql will execute the generated sql and return its results; execution can be omitted and the generated sql returned as a raw string by providing run=False to the method being invoked or by manually setting its auto_run attribute to False prior to calling the method.

---

Warning

These methods will not ask twice before querying, altering or dropping a Snowflake object; isolated testing to ensure the API is understood before use is recommended.

Providing run=False and printing the returned string to the console is one of the easiest ways to inspect the sql that’s generated by a given method.

print(sn.drop('sample_table', run=False))
"""
>>>
drop table if exists sample_table
"""

---

---

Usage

• Command Overview

• Execution Control

• Setting nm and obj

Setup

These examples make use
of a sample_table containing:

COL1	COL2
1	1
2	4
4	9

---

Command Overview

---

-

FYI

The snippets below encompass the most widely applicable methods available off snowmobile.SQL; see the API Docs for exhaustive method documentation.

The following statements can be run to interact with sample_table defined by .

---

---

Verify it exists:

sn.exists('sample_table')  #> True

Sample its records:

sn.select('sample_table', n=1)

Query its columns from selecting a sample record:

sn.columns('sample_table')  #> ['COL1', 'COL2']

Or from the information schema:

sn.columns('sample_table', from_info_schema=True)  #> ['COL1', 'COL2']

Check its depth:

sn.count('sample_table')  #> 3

Query its DDL:

print(sn.ddl('sample_table'))
"""
>>>
create or replace TABLE SAMPLE_TABLE (
  COL1 FLOAT,
  COL2 FLOAT
);
"""

Clone it to another table:

sn.clone(nm='sample_table', to='sample_table2')

Drop objects:

sn.drop('sample_table2')
sn.exists('sample_table2')  #> False

Cross-Schema

Applicable methods of sql inspect the value passed through the nm argument for schema-prefixes; when provided, sn will compare the schema passed as an argument to the schema associated with in order to generate the appropriate sql.

---

---

For example, if other_schema represents a different schema than is currently connected to, the following two statements could be run:

Clone sample_table to other_schema.sample_table:

sn.clone(nm='sample_table', to='other_schema.sample_table')

Drop other_schema.sample_table from the current schema:

sn.drop(nm='other_schema.sample_table')

---

Execution Control

---

run=False

---

Also demonstrated above, methods can be provided with run=False to return the raw sql as a string as opposed to executing the generated command:

print(sn.drop('sample_table', run=False))
"""
>>>
drop table if exists sample_table
"""

print(sn.select('sample_table', n=1))
"""
>>>
select
    *
from sample_table
limit 1
"""

Using auto_run

---

The run method argument has the following signature:

run: Optional[bool] = None

If a valid bool isn’t passed in the place of None, the current value of its auto_run attribute determines whether or not to execute the sql it generates.

An alternative to providing run=False across a series of methods in order to inspect the sql being generated is then to modify this attribute’s value on a given instance of , done with:

sn.auto_run = False

Once set to False, an equivalent sample1 and sample2 can be created with:

sample1 = sn.select('sample_table', run=False)
sample2 = sn.select('sample_table')

print(type(sample1))       #> <class 'str'>
print(sample1 == sample2)  #> True

Because methods defer to auto_run in absence of an explicit argument, it can be executed off the same instance of with:

df_sample = sn.select('sample_table', run=True)
print(type(df_sample))  #> <class 'pandas.core.frame.DataFrame'>

---

Setting nm and obj

---

Most SQL methods need to know an in-warehouse object’s name (nm) and type (obj), which default to None and table respectively.

These defaults are why we can write:

_ = sn.drop('sample_table', run=False)

Instead of:

_ = sn.drop('sample_table', obj='table', run=False)

---

---

In the same way as the run method argument and the auto_run attribute, SQL deffers to the values of its nm and obj attributes in absence of valid strings passed through the nm and obj method arguments.

Bringing these together and assuming a default instace of , the following can be run:

sn.auto_run = False
sn.nm = 'sample_table'

sample1 = sn.select('sample_table')
sample2 = sn.select()
df_sample = sn.select(run=True)

print(type(sample1))       #> <class 'str'>
print(sample1 == sample2)  #> True
print(type(df_sample))  #> <class 'pandas.core.frame.DataFrame'>

---