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¶
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
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.
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'>