Snippets
This is a generated reference page for complete code snippets used throughout the rest of the documentation.
Configuration
inspect_configuration.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 | """
Instantiate a delayed snowmobile.Snowmobile object and inspect configuration model.
../docs/snippets/configuration/inspect_configuration.py
"""
import snowmobile
sn = snowmobile.Snowmobile(delay=True)
type(sn.cfg) #> snowmobile.core.configuration.Configuration
print(sn.cfg.location) # 'path/to/your/snowmobile.toml'
type(sn.cfg.connection) #> snowmobile.core.cfg.connection.Connection
type(sn.cfg.loading) #> snowmobile.core.cfg.loading.Loading
type(sn.cfg.script) #> snowmobile.core.cfg.script.Script
type(sn.cfg.sql) #> snowmobile.core.cfg.other.SQL
type(sn.cfg.ext_sources) #> snowmobile.core.cfg.other.Location
# -- complete example; should run 'as is' --
|
Overview
sample_table.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36 | -- ..docs/snippets/getting_started/sample_table.sql
create or replace table sample_table (
col1 number(18,0),
col2 number(18,0)
);
insert into sample_table with
sample_data as (
select
uniform(1, 10, random(1)) as rand_int
from table(generator(rowcount => 3)) v
)
select
row_number() over (order by a.rand_int) as col1
,(col1 * col1) as col2
from sample_data a;
select * from sample_table;
/*-qa-empty~verify 'sample_table' is distinct on 'col1'-*/
select
a.col1
,count(*)
from sample_table a
group by 1
having count(*) <> 1;
/*-insert into~any_other_table-*/
insert into any_other_table (
select
a.*
,tmstmp.tmstmp as insert_tmstmp
from sample_table a
cross join (select current_timestamp() as tmstmp)tmstmp
);
|
Script
intro.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 | /*
../snippets/script/intro.sql
Demonstrate basic parsing functionality.
*/
/*-
__intro.sql__
__authored-by: Some Chap or Lass
__authored-on: Some Day or Year
__p*_***:
**Impetus**: *SQL is older than time and isn't going anywhere; might we allow a simple markup syntax?*
-*/
/*-
create table~sample_table; DDL
__description: This is an example statement description
-*/
create or replace table sample_table (
col1 number(18,0),
col2 number(18,0)
);
|
Intro
intro1.sql
| -- ./docs/snippets/script/intro/intro1.sql
create or replace table sample_table (
col1 number(18,0),
col2 number(18,0)
);
insert into sample_table (col1, col2) values(1, 2);
select * from sample_table;
|
keyword_exceptions.sql
| -- ..docs/snippets/script/keyword_exceptions.sql
-- kw = 'select'
select * from any_table;
-- kw = 'select'
with some_cte as (
select * from any_table
)
select * from some_cte;
|
markup.sql
| -- ..docs/snippets/script/markup.sql
/*-
__name: Example.sql
__description: This is an example description field.
-*/
|
overview-base-sn.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71 | -- ..docs/snippets/script/overview-base.sql
/*-
__overview-base-sn.sql__
__authored-by: some person
__authored-on: some date
__context*_***: This is a contrived example of how a script can be marked up and parsed by Snowmobile.
-*/
/*-create table sample_table~DDL-*/
create or replace table sample_table (
col1 number(18,0),
col2 number(18,0),
insert_tmstmp timestamp
);
/*-insert into~sample_table-*/
insert into sample_table with
sample_data as (
select
uniform(1, 10, random(1)) as rand_int
from table(generator(rowcount => 3)) v
)
select
row_number() over (order by a.rand_int) as col1
,(col1 * col1) as col2
,tmstmp.tmstmp as insert_tmstmp
from sample_data a
cross join (select current_timestamp() as tmstmp) tmstmp;
/*-sample records~sample_table-*/
select
*
from sample_table;
/*-qa-empty~verify sample_table is distinct on col1-*/
select
a.col1
,count(*)
from sample_table a
group by 1
having count(*) > 1;
/*-create table~any_other_table-*/
create or replace table any_other_table
clone sample_table;
/*-alter table~staged_tmstmp addition-*/
alter table any_other_table add column staged_tmstmp timestamp;
/*-insert into~any_other_table-*/
insert into any_other_table (
select
a.col1
,a.col2
,tmstmp.tmstmp
,a.insert_tmstmp
from sample_table a
cross join (select current_timestamp() as tmstmp) tmstmp
);
/*-qa-empty~verify any_other_table is distinct on col1-*/
select
a.col1
,count(*)
from any_other_table a
group by 1
having count(*) > 1;
/*-truncate table~sample_table-*/
truncate table sample_table;
|
overview-base.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73 | -- ..docs/snippets/script/overview-base.sql
/*
author: some person
date: some date
context: this is a contrived example of what a messy sql file can look like
*/
-- DDL: one-time execution
create or replace table sample_table (
col1 number(18,0),
col2 number(18,0),
insert_tmstmp timestamp
);
-- update only
insert into sample_table with
sample_data as (
select
uniform(1, 10, random(1)) as rand_int
from table(generator(rowcount => 3)) v
)
select
row_number() over (order by a.rand_int) as col1
,(col1 * col1) as col2
,tmstmp.tmstmp as insert_tmstmp
from sample_data a
cross join (select current_timestamp() as tmstmp) tmstmp;
-- select * from sample_table;
-- ensure distinct
-- select
-- a.col1
-- ,count(*)
-- from sample_table a
-- group by 1
-- having count(*) > 1;
-- select * from some_random_table_that_no_longer_matters;
-- clone stage
create or replace table any_other_table
clone sample_table;
-- add original tmstmp
alter table any_other_table add column staged_tmstmp timestamp;
-- insert data
insert into any_other_table (
select
a.col1
,a.col2
,tmstmp.tmstmp
,a.insert_tmstmp
from sample_table a
cross join (select current_timestamp() as tmstmp) tmstmp
);
-- ensure distinct
-- select
-- a.col1
-- ,count(*)
-- from any_other_table a
-- group by 1
-- having count(*) > 1;
-- compare final table to staged values
-- select * from sample_table a
-- union all
-- select a.col1, a.col2, a.staged_tmstmp from any_other_table a;
-- truncate staging table
truncate table sample_table;
|
overview-statement-intro.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67 | """
Instantiate `script` from 'overview.sql' and inspect high-level contents.
../docs/snippets/script/overview-base-parsing.py
"""
# Setup -----------------------------------------------------------------------
from pathlib import Path
paths = {p.name: p for p in Path.cwd().glob('**/*.sql')}
path = paths['overview.sql']
import snowmobile
# Example ---------------------------------------------------------------------
# -- Block 1 --
script = snowmobile.Script(path=path)
script.dtl()
"""
>>>
overview.sql
============
1: Statement('create table~s1')
2: Statement('insert into~s2')
3: Statement('select data~s3')
4: Statement('select all~sample_table')
5: Statement('create transient table~s5')
6: Statement('insert into~s6')
7: Statement('drop table~s7')
"""
# -- Block 2 --
# Store a few st, accessed by index position
s_first, s_last = script(1), script(-1)
# first sql keyword
print(s_first.kw) #> create
print(s_last.kw) #> drop
# position within `script`
print(s_first.index) #> 1
print(s_last.index) #> 7
# -- Block 3 --
script.run(1) # .run() from `script`
script(1).run() # .run() from `statement`
# -- Block 4 --
# `script` details as read from 'overview.sql'
print(script.depth) #> 7
print(script(1).nm) #> create table~s1
print(script(-1).nm) #> drop table~s7
with script.filter(excl_kw=['select', 'drop']) as s:
print(s.depth) #> 4
print(s(1).nm) #> create table~s1
print(s(-1).nm) #> insert into~s4
s.dtl()
"""
>>>
overview.sql
============
1: Statement('create table~s1')
2: Statement('insert into~s2')
3: Statement('create transient table~s3')
4: Statement('insert into~s4')
"""
|
overview.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 | -- ..docs/snippets/script/overview.sql
create or replace table sample_table (
col1 number(18,0),
col2 number(18,0)
);
insert into sample_table with
sample_data as (
select
uniform(1, 10, random(1)) as rand_int
from table(generator(rowcount => 3)) v
)
select
row_number() over (order by a.rand_int) as col1
,(col1 * col1) as col2
from sample_data a;
select * from sample_table;
/*-select all~sample_table-*/
select * from sample_table;
create or replace transient table any_other_table clone sample_table;
insert into any_other_table (
select
a.*
from sample_table a
);
drop table if exists sample_table;
|
1
2
3
4
5
6
7
8
9
10
11
12
13 | -- ..docs/snippets/script/tags_multi-line.sql
/*-
__name: I am a wrap
__description: This is an example of a wrap with the name explicitly declared.
-*/
select * from sample_table;
/*-
I am another wrap
__description: This is an example of a wrap with the name implicitly declared.
-*/
select * from sample_table;
|
| -- ..docs/snippets/script/tags_single-line.sql
/*-I am a wrap-*/
select * from sample_table;
/*-I am a wrap that isn't positioned correctly-*/
select * from sample_table;
|
Snowmobile
connecting.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 | """ Establish a basic connection.
../docs/snippets/connecting.py
"""
import snowmobile
sn = snowmobile.connect()
print(sn) #> snowmobile.Snowmobile(creds='creds1')
print(sn.cfg) #> snowmobile.Configuration('snowmobile.toml')
print(type(sn.con)) #> <class 'snowflake.connector.connection.SnowflakeConnection'>
sn2 = snowmobile.connect(creds="creds1")
sn.cfg.connection.current == sn2.cfg.connection.current #> True
sn.current("schema") == sn2.sql.current("schema") #> True
sn.current("session") == sn2.sql.current("session") #> False
# -- complete example; should run 'as is' --
|
connector_cursor_note.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 | """
Demonstrate instance exhaustion component of Connector.cursor.
../snippets/connector_cursor_note.py
"""
import snowmobile
sn = snowmobile.connect()
cur1 = sn.cursor.execute("select 1")
cur2 = sn.cursor.execute("select 2")
cursor = sn.cursor
cur11 = cursor.execute("select 1")
cur22 = cursor.execute("select 2")
id(cur1) == id(cur2) #> False
id(cur11) == id(cur22) #> True
# -- complete example; should run 'as is' --
|
connector_delayed1.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 | """
Create a delayed snowmobile.Snowmobile object.
..docs/snippets/snowmobile/connector_delayed1.py
"""
import snowmobile
sn = snowmobile.connect(delay=True)
type(sn.con) #> None
print(sn.alive) #> False
_ = sn.query("select 1")
type(sn.con) #> snowflake.connector.connection.SnowflakeConnection
print(sn.alive) #> True
# -- complete example; should run 'as is' --
|
connector_delayed2.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | """
Demonstrate calling .connect() on existing Snowmobile instances.
..docs/snippets/snowmobile/connector_delayed2.py
"""
import snowmobile
# -- Delayed Connection --
sn_del = snowmobile.connect(delay=True)
print(type(sn_del.con)) #> None
sn_del.connect()
print(type(sn_del.con)) #> snowflake.connector.connection.SnowflakeConnection
# -- Live Connection --
sn_live = snowmobile.connect()
session1 = sn_live.sql.current('session')
sn_live.connect()
session2 = sn_live.sql.current('session')
print(session1 != session2) #> True
# -- complete example; should run 'as is' --
|
ensure_alive.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39 | """
Demonstrate behavior of Connector's 'ensure_alive' parameter.
..docs/snippets/connector_ensure_alive.py
"""
import snowmobile
# --- SESSION #1 ---
# Explicitly providing default argument for clarity
sn = snowmobile.connect(ensure_alive=True)
print(sn.alive) #> True
type(sn.con) #> snowflake.connector.connection.SnowflakeConnection
# Storing 1st session ID
session1 = sn.current('session')
# Killing connection
sn.disconnect()
print(sn.alive) #> False
type(sn.con) #> NoneType
# --- SESSION #2 ---
# Calling any method requiring a connection
_ = sn.query("select 1")
# Storing 2nd session ID
session2 = sn.current('session')
# Verifying both session IDs are valid
print(type(session1)) #> str
print(type(session2)) #> str
# Verifying they're unique
print(session1 != session2) #> True
# -- complete example; should run 'as is' --
|
executing.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41 | """Demonstrate primary methods for executing raw sql.
../docs/snippets/snowmobile/executing.py
"""
import snowmobile
sn = snowmobile.connect()
# -- sn.query() ---------------------------------------------------------------
df = sn.query("select 1") # == pd.read_sql()
type(df) #> pandas.core.frame.DataFrame
# -- pd.read_sql() --
import pandas as pd
df2 = pd.read_sql(sql="select 1", con=sn.con)
print(df2.equals(df)) #> True
# -- sn.ex() ------------------------------------------------------------------
cur = sn.ex("select 1") # == SnowflakeConnection.cursor().execute()
type(cur) #> snowflake.connector.cursor.SnowflakeCursor
# -- SnowflakeConnection.cursor().execute() --
cur2 = sn.con.cursor().execute("select 1")
print(cur.fetchone() == cur2.fetchone()) #> True
# -- sn.exd() -----------------------------------------------------------------
dcur = sn.exd("select 1") # == SnowflakeConnection.cursor(DictCursor).execute()
type(dcur) #> snowflake.connector.DictCursor
# -- SnowflakeConnection.cursor(DictCursor).execute() --
from snowflake.connector import DictCursor
dcur2 = sn.con.cursor(cursor_class=DictCursor).execute("select 1")
print(dcur.fetchone() == dcur2.fetchone()) #> True
# -- complete example; should run 'as is' --
|
inspect_connector.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 | """
Instantiate a vanilla Snowmobile and inspect key attributes.
../docs/snippets/snowmobile/inspect_connector.py
"""
import snowmobile
sn = snowmobile.connect()
type(sn) #> snowmobile.core.connection.Snowmobile
type(sn.cfg) #> snowmobile.core.configuration.Configuration
str(sn.cfg) #> snowmobile.Configuration('snowmobile.toml')
type(sn.con) #> snowflake.connector.connection.SnowflakeConnection
type(sn.cursor) #> snowflake.connector.cursor.SnowflakeCursor
# -- complete example; should run 'as is' --
|
specifying_configuration.py
| """
Demonstrate specifying an alternate snowmobile.toml file *path*.
../docs/snippets/snowmobile/specifying_configuration.py
"""
from pathlib import Path
import snowmobile
path = Path.cwd() / 'snowmobile_v2.toml' # any alternate file path
sn = snowmobile.connect(from_config=path)
|
specifying_configuration2.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46 | """
Demonstrate specifying an alternate snowmobile.toml file *name*.
../docs/snippets/snowmobile/specifying_configuration2.py
"""
# -- SETUP --------------------------------------------------------------------
import time
import shutil
import snowmobile
# Instantiate sn from snowmobile.toml; omit unnecessary connection
sn = snowmobile.connect(delay=True)
# Create alternate snowmobile.toml file called 'snowmobile2.toml'
path_cfg_orig = sn.cfg.location
path_cfg2 = path_cfg_orig.parent / 'snowmobile2.toml'
shutil.copy(path_cfg_orig, path_cfg2)
# -- EXAMPLE ------------------------------------------------------------------
def alt_sn(n: int) -> snowmobile.Snowmobile:
"""Instantiate sn from snowmobile2.toml and print time elapsed."""
pre = time.time()
sn = snowmobile.connect(
config_file_nm='snowmobile2.toml',
delay=True # omit connection - not needed
)
print(f"n={n}, time-required: ~{int(time.time() - pre)} seconds")
return sn
sn_alt1 = alt_sn(n=1) #> n=1, time-required: ~6 seconds -> locates file, caches path
sn_alt2 = alt_sn(n=2) #> n=2, time-required: ~0 seconds -> uses cache from sn_alt1
"""
Note:
The time required for `sn_alt1` to locate 'snowmobile2.toml' is arbitrary and
will vary based the file's location relative to the current working directory.
"""
# -- TEARDOWN -----------------------------------------------------------------
# Deleting 'snowmobile2.toml' from file system post-example
import os
os.remove(sn_alt1.cfg.location)
|
verify_default_alias_change.py
| """
Verify `default-creds` has been changed to `creds2`.
../docs/snippets/snowmobile/verify_default_alias_change.py
"""
import snowmobile
sn = snowmobile.connect()
assert sn.cfg.connection.default_alias == 'creds2', (
"Something's not right here; expected default_alias =='creds2'"
)
|
SQL
sql_cross_schema.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81 | """
Demonstrate prefixing object names with an alternative schema.
../docs/snippets/sql/sql_cross_schema.py
"""
import snowmobile
sn = snowmobile.connect()
# -- SETUP --------------------------------------------------------------------
setup_sql = (
"""
create or replace table sample_table as with
sample_data as (
select
uniform(1, 10, random(1)) as rand_int
from table(generator(rowcount => 3)) v
)
select
row_number() over (order by a.rand_int) as col1
,(col1 * col1) as col2
from sample_data a
"""
)
sn.ex(setup_sql) # create 'sample_table'
# -- EXAMPLE ------------------------------------------------------------------
import snowmobile
from snowflake.connector.errors import DatabaseError
try:
# setup
schema_nms = ['sample_schema1', 'sample_schema2']
for schema in schema_nms:
_ = snowmobile.connect().ex(f"create or replace schema {schema}")
sn = snowmobile.connect()
assert sn.current('schema').lower() not in schema_nms
# ==================
# - Start Example -
# ==================
# Clone some tables
sn.clone(nm='sample_table', to='other_schema.sample_table')
sn.drop(nm='other_schema.sample_table')
sn.clone( # other to current schema
nm='other_schema.sample_table',
to='sample_table',
)
# Query metadata
print(sn.exists('sample_table')) #> True
print(sn.exists('sample_schema.sample_table2')) #> True
print(sn.exists('gem7318.sample_table3')) #> True
print(sn.current_schema())
# sn.drop() works the same way
for t in [
'sample_table',
'sample_schema.sample_table2',
# 'sample_table3',
]:
sn.drop(t)
# ==================
# - End Example -
# ==================
except DatabaseError as e:
raise e
finally:
# teardown
sn.drop(nm='sample_schema', obj='schema')
# snowmobile-include
sn = snowmobile.connect()
sn.current('schema').lower()
|