{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# snowmobile\n",
"\n",
"```{include} /description.md\n",
"```\n",
"\n",
"```{eval-rst}\n",
"\n",
".. toctree::\n",
" :maxdepth: 1\n",
" :hidden:\n",
"\n",
" ./setup.md\n",
"\n",
".. toctree::\n",
" :caption: Core\n",
" :maxdepth: 1\n",
" :hidden:\n",
"\n",
" ./usage/snowmobile.md\n",
" ./usage/script.md\n",
" ./usage/table.md\n",
" ./usage/sql.ipynb\n",
" ./usage/snowmobile_toml.md\n",
"\n",
".. toctree::\n",
" :caption: Technical Resources\n",
" :maxdepth: 1\n",
" :hidden:\n",
"\n",
" ./autoapi/snowmobile/core/index\n",
" ./snippets.md\n",
"\n",
".. toctree::\n",
" :caption: Other\n",
" :maxdepth: 1\n",
" :hidden:\n",
"\n",
" ./acknowledgements.md\n",
" ./changelog.md\n",
" ./authors.md\n",
" ./license.md\n",
"```\n",
"\n",
"+++\n",
"\n",
"## Overview\n",
"\n",
"```{div} sn-dedent-list, sn-depth1\n",
"- *[Connecting](#connecting)*\n",
"- *[Query Execution](#query-execution)*\n",
"- *[Information API](#information-api)*\n",
"- *[Loading Data](#loading-data)*\n",
"- *[Working with SQL Scripts](#working-with-sql-scripts)*\n",
"```\n",
"\n",
"
\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-phantom\n",
" \n",
"```\n",
"### *Connecting*\n",
"\n",
"````{admonition} *Connecting*\n",
":class: toggle, sn-gradient-header, sn-indent-h-cell-right-m, sn-toggle-expand\n",
"\n",
"```{div} sn-dedent-v-b-h, sn-dedent-v-t-container-neg\n",
"{func}`snowmobile.connect()` returns a [Snowmobile](./usage/snowmobile.md#snowmobile) whose purpose is to:\n",
"```\n",
"1. Locate, instantiate, and store [snowmobile.toml](./usage/snowmobile_toml.md#snowmobiletoml)\n",
" as a {class}`~snowmobile.Configuration` object ({class}`sn.cfg`)\n",
"1. Establish a connection to {xref}`snowflake` and store the {xref}`SnowflakeConnection` ({class}`sn.con`)\n",
"1. Serve as the primary entry point to the {xref}`SnowflakeConnection` and {xref}`snowmobile` APIs\n",
"+++\n",
"```{div} sn-dedent-v-t-h\n",
" The first time it's invoked, [Snowmobile](./usage/snowmobile.md) will find [snowmobile.toml](./usage/snowmobile_toml) and cache its location;\n",
" this step isn't repeated unless the file is moved, the cache is manually cleared, or a new version of {xref}`snowmobile` is installed.\n",
"```\n",
"+++\n",
"**With all arguments omitted, it will authenticate with the default credentials and connection arguments specified in** [**snowmobile.toml**](./usage/snowmobile_toml).\n",
"\n",
"
\n",
"\n",
"```{div} sn-link-container\n",
"{link-badge}`./usage/snowmobile.html,cls=badge-primary text-white sn-usage,Usage: Snowmobile,tooltip=Intro & usage documentation for snowmobile.Snowmobile`\n",
"{link-badge}`./autoapi/snowmobile/core/connection/index.html,cls=badge-secondary text-white sn-api,snowmobile.core.connection,tooltip=API Documentation`\n",
"```\n",
"\n",
"````"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Establishing a connection from configured defaults is done with:\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"# -- SETUP --"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [],
"source": [
"# -- setup --\n",
"\n",
"import sys\n",
"\n",
"from pathlib import Path\n",
"\n",
"sys.path.insert(0, str(Path.cwd().parent))"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"tags": [
"remove-output"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Locating credentials..\n",
"(1 of 2) Finding snowmobile.toml..\n",
"(2 of 2) Cached path found at ../Snowmobile/snowmobile.toml\n",
"..connected: snowmobile.Snowmobile(creds='creds1')\n"
]
}
],
"source": [
"import snowmobile\n",
"\n",
"sn = snowmobile.connect()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"`sn` is a [](./usage/snowmobile) with the following attributes:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"tags": [
"remove-output"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"snowmobile.Snowmobile(creds='creds1')\n",
"snowmobile.Configuration('snowmobile.toml')\n",
"\n"
]
}
],
"source": [
"print(sn) #> snowmobile.Snowmobile(creds='creds1')\n",
"print(sn.cfg) #> snowmobile.Configuration('snowmobile.toml')\n",
"print(type(sn.con)) #> "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"(fixture-sn)=\n",
"Specific connection arguments are accessed by pre-configured alias:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {
"tags": [
"remove-output"
]
},
"outputs": [],
"source": [
"sn2 = snowmobile.connect(creds='sandbox')\n",
"\n",
"print(sn.cfg.connection.current != sn2.cfg.connection.current) #> True"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"sample_table.sql\n",
"================\n",
"<1 of 5> create table~s1 (0s)......................................... \n",
"<2 of 5> insert into~s2 (0s).......................................... \n"
]
}
],
"source": [
"# -- setup --\n",
"\n",
"# Verify table doesn't exist\n",
"if sn.exists('sample_table'):\n",
" sn.drop('sample_table')\n",
"\n",
"# Truncate 'any_other_table'\n",
"sn.truncate('any_other_table')\n",
"\n",
"# Path to sample .sql file\n",
"from pathlib import Path\n",
"\n",
"path = Path.cwd() / 'snippets' / 'overview' / 'sample_table.sql'\n",
"\n",
"# Execute setup statements from script\n",
"script = snowmobile.Script(sn=sn, path=path)\n",
"script.run((1,2))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"`````{admonition} **sn**\n",
":class: sn-fixture, sn-fixture-global\n",
"\n",
"````{div} sn-dedent-v-b-h, sn-dedent-v-t-container-neg\n",
"The variable `sn` represents a generic instance of [](./usage/snowmobile) roughly\n",
"equivalent to that created with the snippet below; it's referred to as {fa}`fixture sn` \n",
"throughout the documentation, and applicable examples make use of it as a fixture \n",
"without explicitly re-instantiating.\n",
"````\n",
"\n",
"```{code-block} python\n",
":emphasize-lines: 3, 3\n",
"\n",
"import snowmobile\n",
"\n",
"sn = snowmobile.connect()\n",
"```\n",
"\n",
"
\n",
"\n",
"`````\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-phantom\n",
"
\n",
"```\n",
"### *Query Execution*\n",
"\n",
"`````{admonition} *Query Execution*\n",
":class: toggle, sn-gradient-header, sn-indent-h-cell-right-m, sn-toggle-expand\n",
"\n",
"```{div} sn-dedent-v-b-h, sn-dedent-v-container-neg\n",
" [{fa}`fixture sn`](fixture-sn) provides three convenience methods for executing raw SQL:\n",
"```\n",
"\n",
"```{div} sn-left-pad\n",
"{meth}`~snowmobile.Snowmobile.query()` implements {meth}`pandas.read_sql()` for querying results into a {class}`~pandas.DataFrame`\n",
"\n",
"{meth}`~snowmobile.Snowmobile.ex()` implements {meth}`SnowflakeConnection.cursor().execute()` for executing commands within a {xref}`SnowflakeCursor`\n",
"\n",
"{meth}`~snowmobile.Snowmobile.exd()` implements {meth}`SnowflakeConnection.cursor(DictCursor).execute()` for executing commands within a {xref}`DictCursor`\n",
"```\n",
"\n",
"
\n",
"\n",
"```{div} sn-link-container\n",
"{link-badge}`./usage/snowmobile.html#executing-raw-sql,cls=badge-primary text-white sn-usage,Usage: Executing Raw SQL,tooltip=Usage documentation for Executing Raw SQL`\n",
"{link-badge}`./autoapi/snowmobile/core/connection/index.html,cls=badge-secondary text-white sn-api,snowmobile.core.connection,tooltip=API Documentation`\n",
"```\n",
"\n",
"`````"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"````{admonition} Setup\n",
":class: is-setup, sn-indent-h-cell-m, sn-indent-h-sub-cell-right, sn-inline-block-container, sn-dedent-v-b-container\n",
"\n",
"(sn)=\n",
"Assume a pre-existing \\\n",
"**sample_table**:\n",
"```{div} sn-dedent-v-b-container\n",
"| COL1 | COL2 |\n",
"|-------:|-------:|\n",
"| 1 | 1 |\n",
"| 2 | 4 |\n",
"| 4 | 9 |\n",
"```\n",
"````\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Into a {class}`~pandas.DataFrame`:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" col1 | \n",
" col2 | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
"
\n",
" \n",
" 1 | \n",
" 2 | \n",
" 4 | \n",
"
\n",
" \n",
" 2 | \n",
" 3 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" col1 col2\n",
"0 1 1\n",
"1 2 4\n",
"2 3 9"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sn.query('select * from sample_table')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Into a {xref}`SnowflakeCursor`:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 91,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[(1, 1), (2, 4), (3, 9)]"
]
},
"execution_count": 91,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sn.ex('select * from sample_table').fetchall()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Into a {xref}`DictCursor`:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 92,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[{'COL1': 1, 'COL2': 1}, {'COL1': 2, 'COL2': 4}, {'COL1': 3, 'COL2': 9}]"
]
},
"execution_count": 92,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sn.exd('select * from sample_table').fetchall()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Or to get a single value:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"3"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sn.query('select count(*) from sample_table', as_scalar=True)"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"```{div} sn-pre-code\n",
"{xref}`SnowflakeCursor` and {xref}`DictCursor` are also accessible by passing argumentsto \n",
"{meth}`sn.query()`:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [],
"source": [
"sql = 'select * from sample_table'\n",
"\n",
"cur1, cur2 = (\n",
" sn.ex(sql).fetchall(),\n",
" sn.query(sql, as_cur=True).fetchall()\n",
")\n",
"print(cur1 == cur2) #> True\n",
"\n",
"dcur1, dcur2 = (\n",
" sn.exd(sql).fetchall(),\n",
" sn.query(sql, as_dcur=True).fetchall()\n",
")\n",
"print(dcur1 == dcur2) #> True"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"```{div} sn-pre-code\n",
"Conflicting arguments will results in following error:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Only one of ('as_df', 'as_cur', 'as_dcur', 'as_scalar') can evaluate to `True`\n"
]
}
],
"source": [
"try:\n",
" sn.query(sql, as_df=True, as_dcur=True)\n",
"except ValueError as e:\n",
" print(e)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-phantom\n",
"
\n",
"```\n",
"### *Information API*\n",
"\n",
"`````{admonition} *Information API*\n",
":class: toggle, sn-gradient-header, sn-indent-h-cell-right-m, sn-toggle-expand\n",
"\n",
"```{div} sn-dedent-v-container-neg\n",
" [{fa}`fixture sn`](fixture-sn) inherits everything from a [**SQL**](./usage/sql.ipynb) class that generates and executes raw SQL from inputs; its purpose \n",
" is to provide a bare bones Python API to query metadata and execute administrative commands against {xref}`snowflake`.\n",
"```\n",
"\n",
"
\n",
"\n",
"```{div} sn-link-container\n",
"{link-badge}`./usage/sql.html,cls=badge-primary text-white sn-usage,Usage: SQL,tooltip=Usage documentation for snowmobile.SQL`\n",
"{link-badge}`./autoapi/snowmobile/core/sql/index.html,cls=badge-secondary text-white sn-api,snowmobile.core.sql,tooltip=API Documentation`\n",
"```\n",
"\n",
"`````"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Check existence:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"tags": [
"remove-output"
]
},
"outputs": [
{
"data": {
"text/plain": [
"True"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sn.exists('sample_table') #> True"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Select records:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 93,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" col1 | \n",
" col2 | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" col1 col2\n",
"0 1 1"
]
},
"execution_count": 93,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sn.select('sample_table', n=1)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Query metadata:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"tags": [
"remove-output"
]
},
"outputs": [],
"source": [
"sn.count('sample_table') #> 3\n",
"sn.count('sample_table', dst_of='col1') #> 3\n",
"sn.columns('sample_table') #> ['COL1', 'COL2']"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Verify dimensionality:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"tags": [
"remove-output"
]
},
"outputs": [],
"source": [
"sn.is_distinct('sample_table', field='col1') #> True"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Submit basic administrative commands:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"tags": [
"remove-output"
]
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" status | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" Table SAMPLE_TABLE2 successfully created. | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" status\n",
"0 Table SAMPLE_TABLE2 successfully created."
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sn.clone(nm='sample_table', to='sample_table2')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Fetch DDL:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 53,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"create or replace TABLE SAMPLE_TABLE (\n",
"\tCOL1 FLOAT,\n",
"\tCOL2 FLOAT\n",
");\n"
]
}
],
"source": [
"print(sn.ddl('sample_table'))"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"```{div} sn-pre-code\n",
"Provide `run=False` to get the raw sql\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"tags": [
"remove-output",
"remove-cell"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"drop table if exists GEM7318.SAMPLE_TABLE\n"
]
}
],
"source": [
"drop_sql = sn.drop('sample_table', run=False)\n",
"\n",
"print(drop_sql) #> drop table if exists GEM7318.SAMPLE_TABLE"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Drop objects:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [],
"source": [
"for t in ['sample_table', 'sample_table2']:\n",
" sn.drop(t, obj='table')\n",
"\n",
"sn.exists('sample_table') #> False\n",
"sn.exists('sample_table2') #> False"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-phantom\n",
"
\n",
"```\n",
"### *Loading Data*\n",
"\n",
"(intro/loading-data)=\n",
"`````{admonition} *Loading Data*\n",
":class: toggle, sn-gradient-header, sn-indent-h-cell-right-m, sn-toggle-expand, sn-incl-tabbed-shadow-b-blue\n",
"\n",
"
\n",
"\n",
"````{tabbed} -\n",
" {class}`~snowmobile.Table` is a loading solution that at minimum accepts a `df` ({class}`~pandas.DataFrame`)\n",
" and a `table` name ({class}`str`).\n",
"+++\n",
" In the same way that [**Snowmobile**](./usage/snowmobile) handles its keyword arguments,\n",
" {class}`~snowmobile.Table` will adhere to any arguments explicitly provided and defer\n",
" to the values configured in [snowmobile.toml](./usage/snowmobile_toml) otherwise.\n",
"````\n",
"\n",
"````{tabbed} More Info\n",
" ```{div} sn-dedent-v-t\n",
" *The behavior outlined below reflects those within the\n",
" [default snowmobile.toml file](./usage/snowmobile_toml.md#file-contents)*, meaning that `t1` will:\n",
" ```\n",
" 1. Check if *sample_table* exists in the schema associated with {attr}`sn.con`\n",
" 2. If *sample_table* **does** exist, it will validate `df` against *sample_table* and throw an error\n",
" if their dimensions are not identical\n",
" 3. If *sample_table* does **not** exist (as is the case here), it will generate DDL from `df` and execute it as part of the loading process\n",
"````\n",
"\n",
"```{div} sn-link-container\n",
"{link-badge}`./usage/table.html,cls=badge-primary text-white sn-usage,Usage: snowmobile.Table,tooltip=Intro & usage documentation for snowmobile.Table`\n",
"{link-badge}`./autoapi/snowmobile/core/table/index.html,cls=badge-secondary text-white sn-api,snowmobile.core.table,tooltip=API Documentation`\n",
"```\n",
"\n",
"``````"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"`````{admonition} Setup\n",
":class: is-setup, sn-block-container, sn-indent-h-cell-m, sn-indent-h-sub-cell-right\n",
"\n",
"````{panels}\n",
"\n",
"Assume *sample_table* does not \\\n",
"yet exist and `df` was created with:\n",
"\n",
"```python\n",
"import pandas as pd\n",
"import numpy as np\n",
"\n",
"df = pd.DataFrame(\n",
" data = {'COL1': [1, 2, 3], 'COL2': [1, 4, 9]}\n",
")\n",
"print(df.shape) #> (3, 2)\n",
"```\n",
"\n",
"---\n",
"\n",
"| COL1 | COL2 |\n",
"|-------:|-------:|\n",
"| 1 | 1 |\n",
"| 2 | 4 |\n",
"| 3 | 9 |\n",
"\n",
"````\n",
"\n",
"`````"
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [],
"source": [
"# Verify table does not exist before moving forward with example\n",
"if sn.exists('sample_table'):\n",
" sn.drop('sample_table')"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" COL1 | \n",
" COL2 | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
"
\n",
" \n",
" 1 | \n",
" 2 | \n",
" 4 | \n",
"
\n",
" \n",
" 2 | \n",
" 3 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" COL1 COL2\n",
"0 1 1\n",
"1 2 4\n",
"2 3 9"
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\"\"\"Generate dummy DataFrame for snowmobile.Table example.\"\"\"\n",
"import pandas as pd\n",
"import numpy as np\n",
"\n",
"df = pd.DataFrame(\n",
" data = {'COL1': [1, 2, 3], 'COL2': [1, 4, 9]}\n",
")\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-indent-v-t-container, sn-ov-indent\n",
"Given [{fa}`cog`](#loading-data), instantiating the following [**Table**](./usage/table), `t1`,\n",
"with *as_is=True* will:\n",
"1. Generate and execute DDL to create *sample_table*\n",
"1. Load `df` into *sample_table* via the {xref}`bulk loading from a local file system` standard\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {
"collapsed": true,
"jupyter": {
"outputs_hidden": true
},
"tags": [
"remove-output"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Locating credentials..\n",
"(1 of 2) Finding snowmobile.toml..\n",
"(2 of 2) Cached path found at ../Snowmobile/snowmobile.toml\n",
"..connected: snowmobile.Snowmobile(creds='creds1')\n",
"Loading into 'gem7318.SAMPLE_TABLE`..\n",
"(1 of 4)\n",
"\tCREATE OR REPLACE TABLE SAMPLE_TABLE ( ..\n",
"(2 of 4)\n",
"\tcreate stage SAMPLE_TABLE_stage file_format = snowmobile_default_psv;\n",
"(3 of 4)\n",
"\tput file://C:/Users/gem7318/Documents/Github/Snowmobile/docs/sample_table.csv @SAMPLE_TABLE_stage\n",
"\tauto_compress = true\n",
"(4 of 4)\n",
"\tcopy into SAMPLE_TABLE from @SAMPLE_TABLE_stage\n",
"\ton_error = continue\n",
"..completed: 3 rows in 2 seconds\n"
]
}
],
"source": [
"import snowmobile\n",
"\n",
"t1 = snowmobile.Table(\n",
" df=df,\n",
" table='sample_table',\n",
" as_is=True,\n",
")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"In absence of providing an existing [{fa}`fixture sn`](fixture-sn) to {class}`snowmobile.Table()`,\n",
"an instance was created and stored as a public attribute; the create and load for *sample_table* can be verified with either of:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"tags": [
"remove-output"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"True\n",
"True\n"
]
}
],
"source": [
"print(t1.sn.exists('sample_table')) #> True\n",
"print(t1.loaded) #> True"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-indent-h-cell-m\n",
"
\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"When compatability between the *df* and the *table* is unknown, *as_is=True* can be omitted and \n",
"the [**Table**](./usage/table) that's returned inspected further prior to continuing with the loading process:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {
"collapsed": true,
"jupyter": {
"outputs_hidden": true
},
"tags": [
"remove-output"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"['COL1', 'COL2', 'COL1', 'COL2']\n",
"['COL1', 'COL2']\n",
"Locating credentials..\n",
"(1 of 2) Finding snowmobile.toml..\n",
"(2 of 2) Cached path found at ../Snowmobile/snowmobile.toml\n",
"..connected: snowmobile.Snowmobile(creds='creds1')\n"
]
}
],
"source": [
"df2 = pd.concat([df, df], axis=1)\n",
"print(list(df2.columns)) #> ['COL1', 'COL2', 'COL1', 'COL2']\n",
"print(t1.sn.columns('sample_table')) #> ['COL1', 'COL2']\n",
"\n",
"t2 = snowmobile.Table(\n",
" df=df2,\n",
" table='sample_table',\n",
")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Primary compatability checks are centered around things like:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {
"collapsed": true,
"jupyter": {
"outputs_hidden": true
},
"tags": [
"remove-output"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"True\n",
"False\n",
"{0: ('col1', 'col1'), 1: ('col2', 'col2'), 2: (None, 'col1_1'), 3: (None, 'col2_1')}\n"
]
}
],
"source": [
"print(t2.exists) #> True\n",
"print(t2.cols_match) #> False"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"With [snowmobile.toml](./usage/snowmobile_toml) defaults, calling {meth}`Table.load()` on `t2` will throw an error:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {
"collapsed": true,
"jupyter": {
"outputs_hidden": true
},
"tags": [
"remove-output"
]
},
"outputs": [
{
"ename": "ColumnMismatchError",
"evalue": "`SAMPLE_TABLE` columns do not equal those in the local DataFrame and if_exists='append' was specified.\nEither provide if_exists='replace' to overwrite the existing table or see `table.col_diff` to inspect the mismatched columns.",
"output_type": "error",
"traceback": [
"\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[1;31mColumnMismatchError\u001b[0m Traceback (most recent call last)",
"\u001b[1;32m\u001b[0m in \u001b[0;36m\u001b[1;34m\u001b[0m\n\u001b[0;32m 2\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 3\u001b[0m \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 4\u001b[1;33m \u001b[0mt3\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 5\u001b[0m \u001b[1;32mexcept\u001b[0m \u001b[0mExistingTableError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
"\u001b[1;32m~\\Documents\\Github\\Snowmobile\\snowmobile\\core\\table.py\u001b[0m in \u001b[0;36mload\u001b[1;34m(self, if_exists, from_script, verbose, **kwargs)\u001b[0m\n\u001b[0;32m 332\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0me\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mseen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mto_raise\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 333\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mon_error\u001b[0m \u001b[1;33m!=\u001b[0m \u001b[1;34m'c'\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 334\u001b[1;33m \u001b[1;32mraise\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0me\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mto_raise\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlast\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 335\u001b[0m \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 336\u001b[0m \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
"\u001b[1;31mColumnMismatchError\u001b[0m: `SAMPLE_TABLE` columns do not equal those in the local DataFrame and if_exists='append' was specified.\nEither provide if_exists='replace' to overwrite the existing table or see `table.col_diff` to inspect the mismatched columns."
]
}
],
"source": [
"from snowmobile.core.errors import ColumnMismatchError\n",
"\n",
"try:\n",
" t2.load()\n",
"except ColumnMismatchError as e:\n",
" print(e)\n",
"\"\"\"\n",
">>>\n",
"ColumnMismatchError: `SAMPLE_TABLE` columns do not equal those in the local DataFrame and \n",
"if_exists='append' was specified.\n",
"\n",
"Either provide if_exists='replace' to overwrite the existing table or see `table.col_diff()`\n",
"to inspect the mismatched columns.\n",
"\"\"\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"{meth}`Table.col_diff()` returns a dictionary of tuples containing the table \n",
"and DataFrame columns by index position; providing *mismatch=True* limits the results to only those responsible \n",
"for the {class}`~snowmobile.core.errors.ColumnMismatchError`:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 48,
"metadata": {
"collapsed": true,
"jupyter": {
"outputs_hidden": true
},
"tags": [
"remove-output"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"{\n",
" \"1\": [\n",
" \"col1\",\n",
" \"col1\"\n",
" ],\n",
" \"2\": [\n",
" \"col2\",\n",
" \"col2\"\n",
" ],\n",
" \"3\": [\n",
" null,\n",
" \"col1_1\"\n",
" ],\n",
" \"4\": [\n",
" null,\n",
" \"col2_1\"\n",
" ]\n",
"}\n"
]
}
],
"source": [
"import json\n",
"\n",
"print(json.dumps(t2.col_diff(mismatched=True), indent=4))\n",
"\"\"\"\n",
">>>\n",
"{\n",
" \"3\": [\n",
" null,\n",
" \"col1_1\"\n",
" ],\n",
" \"4\": [\n",
" null,\n",
" \"col2_1\"\n",
" ]\n",
"}\n",
"\"\"\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Keyword arguments take precedent over configurations in [snowmobile.toml](./usage/snowmobile_toml), so `df2` can still be loaded with:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 79,
"metadata": {
"collapsed": true,
"jupyter": {
"outputs_hidden": true
},
"tags": [
"remove-output"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Loading into 'gem7318.SAMPLE_TABLE`..\n",
"(1 of 4)\n",
"\tCREATE OR REPLACE TABLE SAMPLE_TABLE ( ..\n",
"(2 of 4)\n",
"\tcreate stage SAMPLE_TABLE_stage file_format = snowmobile_default_psv;\n",
"(3 of 4)\n",
"\tput file://C:/Users/GEM7318/Documents/Github/Snowmobile/docs/sample_table.csv @SAMPLE_TABLE_stage\n",
"\tauto_compress = true\n",
"(4 of 4)\n",
"\tcopy into SAMPLE_TABLE from @SAMPLE_TABLE_stage\n",
"\ton_error = continue\n",
"..completed: 3 rows in 2 seconds\n",
"True\n",
"3\n",
"['COL1', 'COL2', 'COL1_1', 'COL2_1']\n"
]
}
],
"source": [
"t2.load(if_exists='replace')\n",
"\n",
"print(t2.loaded) #> True\n",
"print(t2.sn.columns('sample_table')) #> ['COL1', 'COL2', 'COL1_1', 'COL2_1']"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"`````{div} sn-indent-h-cell, sn-dedent-v-t-container\n",
" ````{note}\n",
" With default behavior, the duplicate column names in `df2` were automatically renamed\n",
" by `t2` before loading into *sample_table*:\n",
" \n",
" ```python\n",
" print(list(df2.columns)) #> ['COL1', 'COL2', 'COL1', 'COL2']\n",
" print(list(t2.df.columns)) #> ['COL1', 'COL2', 'COL1_1', 'COL2_1']\n",
" ```\n",
"
\n",
" ````\n",
"`````"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-phantom\n",
" \n",
"```\n",
"### *Working with SQL Scripts*\n",
"\n",
"(intro/working-with-sql-scripts)=\n",
"`````{admonition} *Working with SQL Scripts*\n",
":class: toggle, sn-gradient-header, sn-indent-h-cell-right-m, sn-toggle-expand, sn-incl-tabbed-shadow-b-blue\n",
"\n",
"
\n",
"\n",
"````{tabbed} -\n",
" [**snowmobile.Script**](./usage/script.md) accepts a full `path` to a sql file and parses its contents based on \n",
" patterns specified in [snowmobile.toml](./usage/snowmobile_toml).\n",
"+++ \n",
" At a minimum, the file is split into individual st, each of which is \n",
" checked for decorated information in the form of a string directly preceding it\n",
" wrapped in an opening (`/*-`) and closing (`-*/`) pattern, the simplest form of\n",
" which is a single-line string that can be used as an accessor to the statement\n",
" it precedes.\n",
"+++\n",
" When no information is provided, [Script](./usage/script.ipynb) generates a \n",
" generic name for the statement based on the literal first SQL keyword \n",
" it contains and its index position.\n",
"````\n",
"\n",
"````{tabbed} More Info\n",
" Line **27** within *sample_table.sql* represents the minimum markup required to associate a \n",
" name with an individual statement; consistency in tag structure has obvious benefits, but\n",
" this is a freeform string that can be anything.\n",
"+++\n",
" Line **19** is an example of a special tag; the leading `qa-empty` tells \n",
" [**Script**](./usage/script.ipynb) to run assertion that its results are\n",
" null (0 records) before continuing execution of the script.\n",
"+++\n",
" The tags for statements beginning on lines **1**, **6**, and **17** were generated by \n",
" [**Script**](./usage/script.ipynb) based their contents and relative positions within the script.\n",
"````\n",
"\n",
"```{div} sn-link-container\n",
"{link-badge}`./usage/script.html,cls=badge-primary text-white sn-usage,Usage: snowmobile.Script,tooltip=Intro & usage documentation for snowmobile.Script`\n",
"{link-badge}`./autoapi/snowmobile/core/script/index.html,cls=badge-secondary text-white sn-api,snowmobile.core.script,tooltip=API Documentation`\n",
"```\n",
"\n",
"`````"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"````{admonition} Setup\n",
":class: is-setup, sn-block-container, sn-indent-h-cell-m\n",
"\n",
"```{div} sn-dedent-v-t-h, hanging\n",
"`path` is a full path ({class}`pathlib.Path` or {class}`str`) to a file, \n",
"*sample_table.sql*, containing 5 standard sql st: \n",
"```\n",
"\n",
"```{literalinclude} ./snippets/overview/sample_table.sql\n",
":language: sql\n",
":lines: 2-36\n",
":lineno-start: 1\n",
":emphasize-lines: 1, 6, 17, 20, 28\n",
"```\n",
"```{div} sn-snippet-trunc2, sn-float-right\n",
"[{fa}`file-code-o` sample_table.sql](./snippets.md#overview)\n",
"```\n",
"\n",
"````"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [],
"source": [
"# -- setup --\n",
"\n",
"# Verify table doesn't exist\n",
"if sn.exists('sample_table'):\n",
" sn.drop('sample_table')\n",
"\n",
"# Truncate 'any_other_table'\n",
"sn.truncate('any_other_table')\n",
"\n",
"# Path to sample .sql file\n",
"from pathlib import Path\n",
"\n",
"path = Path.cwd() / 'snippets' / 'overview' / 'sample_table.sql'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code\n",
"Given a `path` to *sample_table.sql*, a [Script](./usage/script) can be created with:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"sample_table.sql\n",
"================\n",
"1: Statement('create table~s1')\n",
"2: Statement('insert into~s2')\n",
"3: Statement('select data~s3')\n",
"4: Statement('qa-empty~verify 'sample_table' is distinct on 'col1'')\n",
"5: Statement('insert into~any_other_table')\n"
]
}
],
"source": [
"import snowmobile\n",
"\n",
"script = snowmobile.Script(path=path, silence=True)\n",
"\n",
"print(script) #> snowmobile.Script('sample_table.sql')\n",
"print(script.depth) #> 5\n",
"script.dtl()"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
" Any [Statement](/autoapi/snowmobile/core/statement/index) can be accessed by \n",
" their {attr}`~snowmobile.core.Name.nm` or index position:\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
" Statements can be accessed by their index position or name ({attr}`~snowmobile.core.Name.nm`):\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 51,
"metadata": {
"tags": [
"remove-output"
]
},
"outputs": [
{
"data": {
"text/plain": [
"Statement('insert into~any_other_table')"
]
},
"execution_count": 51,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"script(5) #> Statement('insert into~any_other_table')\n",
"script(-1) #> Statement('insert into~any_other_table')\n",
"script('insert into~any_other_table') #> Statement('insert into~any_other_table')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Each [Statement](/autoapi/snowmobile/core/statement/index) has its own set of attributes:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {
"collapsed": true,
"jupyter": {
"outputs_hidden": true
},
"tags": [
"remove-output"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"3\n",
"select\n",
"sample_table\n",
"select~sample_table\n",
"select * from sample_table\n",
"False\n",
"Empty DataFrame\n",
"Columns: []\n",
"Index: []\n"
]
}
],
"source": [
"s3 = script(3) # store 3rd statement\n",
"\n",
"print(s3.index) #> 3\n",
"print(s3.sql) #> select * from sample_table\n",
"print(s3.kw) #> select\n",
"print(s3.desc) #> sample_table\n",
"print(s3.nm) #> select~sample_table"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Based on statement attributes, `script` can be subsetted and ran within that context:\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Based on statement attributes, `script` can be filtered and used within that context:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"sample_table.sql\n",
"================\n",
"<1 of 3> create table~s1 (1s)......................................... \n",
"<2 of 3> insert into~s2 (0s).......................................... \n",
"<3 of 3> qa-empty~verify 'sample_table' is distinct on 'col1' (0s).... \n"
]
}
],
"source": [
"with script.filter(\n",
" excl_desc=['.*any_other_table'], # throwing out s5; pattern is regex not glob\n",
" excl_kw=['select'], # throwing out s3\n",
") as s:\n",
" s.run()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Spans of statements are directly executable by index boundaries:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 68,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"sample_table.sql\n",
"================\n",
"<1 of 6> create table~s1 (0s)............................... \n",
"<2 of 6> insert into~s2 (0s)................................ \n",
"<3 of 6> select data~s3 (0s)................................ \n"
]
}
],
"source": [
"script.run((1, 3))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"And their results accessible retroactively:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 48,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" col1 | \n",
" col2 | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
"
\n",
" \n",
" 1 | \n",
" 2 | \n",
" 4 | \n",
"
\n",
" \n",
" 2 | \n",
" 3 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" col1 col2\n",
"0 1 1\n",
"1 2 4\n",
"2 3 9"
]
},
"execution_count": 48,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"script(3).results.head()"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"Generated SQL can be added directly to `script`:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 60,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"sample_table.sql\n",
"================\n",
"1: Statement('create table~s1')\n",
"2: Statement('insert into~s2')\n",
"3: Statement('select data~s3')\n",
"4: Statement('qa-empty~verify 'sample_table' is distinct on 'col1'')\n",
"5: Statement('insert into~any_other_table')\n",
"6: Statement('drop table~s6')\n"
]
}
],
"source": [
"# Generate some sql\n",
"drop_sql = sn.drop(nm='sample_table', obj='table', run=False)\n",
"print(drop_sql) #> drop table if exists SAMPLE_TABLE\n",
"\n",
"# Details (pre)\n",
"print(script.depth) #> 5\n",
"print(script(-1)) #> Statement('insert into~any_other_table')\n",
"\n",
"# Add statement\n",
"script.parse_one(s=drop_sql)\n",
"\n",
"# Details (post)\n",
"print(script.depth) #> 6\n",
"print(script(-1)) #> Statement('drop table~s6')\n",
"\n",
"script.dtl()"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"```{div} sn-pre-code, sn-post-code\n",
"And descriptive statements can be skipped instead of commented out:\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 72,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"sample_table.sql\n",
"================\n",
"<1 of 5> create table~s1 (0s)............................... \n",
"<2 of 5> insert into~s2 (0s)................................ \n",
"<3 of 5> qa-empty~verify 'sample_table' is distinct on 'col1' (0s).... \n",
"<4 of 5> insert into~any_other_table (0s)............................. \n",
"<5 of 5> drop table~s5 (0s)................................. \n"
]
}
],
"source": [
"with script.filter(excl_kw=['select']) as s:\n",
" s.run()"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"
\n",
"\n",
"```{div} sn-pre-code\n",
"See [Usage: Script](./usage/script.md) for more in-depth use of [snowmobile.Script](./usage/script).\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"
\n",
"\n",
"### *Wrap-Up*\n",
"
\n",
"\n",
"`````{admonition} Note\n",
":class: note, sn-indent-h-cell-m\n",
"\n",
" ```{div} sn-dedent-v-b-h\n",
" By instantiating `t1` and `script` with the same instance of [{fa}`fixture sn`](fixture-sn),\n",
" **the same instance of {xref}`SnowflakeConnection` and [Configuration](./usage/snowmobile_toml) \n",
" is shared amongst:**\n",
" ```\n",
" - {class}`sn:` {class}`~snowmobile.Snowmobile`\n",
" - {class}`t1:` {class}`~snowmobile.Table`\n",
" - {class}`script:` {class}`~snowmobile.Script`\n",
"\n",
"`````"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
]
},
"source": [
"# NO RENDER BELOW THIS POINT\n",
"---\n",
"\n",
"All cells below this are either excluded from output via the `remove-cell` cell-tag\n",
"or contain contents that will not visibly render in the output."
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove-cell"
],
"toc-hr-collapsed": true,
"toc-nb-collapsed": true
},
"source": [
"## Style\n",
"\n",
"\n",
"```css\n",
"\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
""
]
}
],
"metadata": {
"kernelspec": {
"display_name": "snowmobile2",
"language": "python",
"name": "snowmobile2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.7.0"
},
"toc-showtags": false
},
"nbformat": 4,
"nbformat_minor": 4
}