{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [ "remove-cell" ] }, "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": [ "# -- setup --\n", "\n", "# Connection\n", "import snowmobile\n", "sn = snowmobile.connect()\n", "\n", "# Find all code files\n", "from pathlib import Path\n", "\n", "snippets = Path.cwd().parent / 'snippets'\n", "scripts = {p.name: p for p in snippets.glob('**/*.sql')}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "(sql)=\n", "# SQL\n", "
\n", "\n", " snowmobile.core.sql\n", "\n", "\n", "\n", "inherits all methods of a {class}`~snowmobile.core.sql.SQL` class that generates and \n", "executes raw SQL from inputs; its purpose is to provide a simple, on-hand Python\n", "API for querying metadata and executing basic administrative commands against {xref}`snowflake`.\n", "\n", "By default, {class}`~snowmobile.core.connection.Snowmobile.sql` will\n", "execute the generated sql and return its results; execution can be omitted\n", "and the generated sql returned as a raw string by providing *run=False* \n", "to the method being invoked or by manually setting its \n", "{attr}`~snowmobile.SQL.auto_run` attribute to *False* prior to calling the \n", "method.\n", "\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "````{admonition} Warning\n", " :class: warning, toggle, toggle-shown\n", " \n", " ```{div} sn-pre-code-s\n", " (sql/warning)=\n", " These methods will not ask twice before querying, altering or dropping a \n", " {xref}`snowflake` object; isolated testing to ensure the API\n", " is understood before use is recommended.\n", " \n", " Providing *run=False* and printing the returned string to the console is one \n", " of the easiest ways to inspect the sql that's generated by a given method.\n", " ```\n", " \n", " ```python\n", " print(sn.drop('sample_table', run=False))\n", " \"\"\"\n", " >>>\n", " drop table if exists sample_table\n", " \"\"\"\n", " ```\n", " \n", "
\n", "\n", "````\n", "\n", "
\n", "\n", "(setup)=\n", "## Usage\n", "\n", "```{div} sn-dedent-list, sn-depth1\n", "- [](#command-overview)\n", "- [](#execution-control)\n", "- [](usage/sql/setting-nm-and-obj)\n", "```\n", "\n", "
\n", "
\n", "\n", " ````{admonition} Setup\n", " :class: is-setup, sn-clear-title\n", "\n", " These examples make use\\\n", " of a **sample_table** containing:\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": [ "### Command Overview\n", "
\n", "\n", "````````{div} sn-tabbed-section\n", "\n", " ```````{tabbed} -\n", " \n", " ```{admonition} FYI\n", " :class: tip, sn-clear-title\n", " The snippets below encompass the most widely applicable methods available off \n", " [snowmobile.SQL](#sql); see\n", " \n", " the API Docs\n", " \n", " for exhaustive method documentation.\n", " ```\n", " \n", " *The following statements can be run to interact with* **sample_table** *defined by* [{fa}`cog`](setup).\n", " \n", "
\n", "
\n", " \n", " ```{div} sn-pre-code-s, sn-post-code\n", " Verify it exists:\n", " ```\n", " ```python\n", " sn.exists('sample_table') #> True\n", " ```\n", " \n", " ```{div} sn-pre-code-s, sn-post-code\n", " Sample its records:\n", " ```\n", " ```python\n", " sn.select('sample_table', n=1)\n", " ```\n", " \n", " ```{div} sn-pre-code-s, sn-post-code\n", " Query its columns from selecting a sample record:\n", " ```\n", " ```python\n", " sn.columns('sample_table') #> ['COL1', 'COL2']\n", " ```\n", " ```{div} sn-pre-code-s\n", " Or from the information schema:\n", " ```\n", " ```python\n", " sn.columns('sample_table', from_info_schema=True) #> ['COL1', 'COL2']\n", " ```\n", " \n", " ```{div} sn-pre-code-s, sn-post-code\n", " Check its depth:\n", " ```\n", " ```python\n", " sn.count('sample_table') #> 3\n", " ```\n", "\n", " ```{div} sn-pre-code-s, sn-post-code\n", " Query its DDL:\n", " ```\n", " ```python\n", " print(sn.ddl('sample_table'))\n", " \"\"\"\n", " >>>\n", " create or replace TABLE SAMPLE_TABLE (\n", " COL1 FLOAT,\n", " COL2 FLOAT\n", " );\n", " \"\"\"\n", " ```\n", "\n", " ```{div} sn-pre-code-s, sn-post-code\n", " Clone it to another table:\n", " ```\n", " ```python\n", " sn.clone(nm='sample_table', to='sample_table2')\n", " ```\n", "\n", " ```{div} sn-pre-code-s\n", " Drop objects:\n", " ```\n", " ```python\n", " sn.drop('sample_table2')\n", " sn.exists('sample_table2') #> False\n", " ```\n", " ```````\n", " \n", " ```````{tabbed} Cross-Schema\n", " Applicable methods of {attr}`~snowmobile.core.connection.Snowmobile.sql` inspect the value \n", " passed through the `nm` argument for schema-prefixes; when provided, \n", " `sn` will compare the schema passed as an argument to the schema associated with\n", " in order to generate the \n", " appropriate sql.\n", " \n", "
\n", "
\n", "\n", " For example, if `other_schema` represents a different schema than\n", " is currently connected\n", " to, the following two statements could be run:\n", " \n", " ```{div} sn-pre-code-s\n", " Clone `sample_table` to `other_schema.sample_table`:\n", " ```\n", " ```python\n", " sn.clone(nm='sample_table', to='other_schema.sample_table')\n", " ```\n", " \n", " ```{div} sn-pre-code-s, sn-post-code\n", " Drop `other_schema.sample_table` from the current schema:\n", " ```\n", " ```python\n", " sn.drop(nm='other_schema.sample_table')\n", " ```\n", "\n", " ```````\n", "\n", "````````\n", "\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Execution Control\n", "
\n", "\n", "````````{div} sn-tabbed-section\n", "\n", " ```````{tabbed}  \n", " \n", "

run=False

\n", "
\n", " \n", " ```{div} sn-pre-code-s\n", " (using-auto-run)=\n", " Also demonstrated [above](sql/warning), methods can be provided with *run=False* to return \n", " the raw sql as a string as opposed to executing the generated command:\n", " ```\n", " ```python\n", " print(sn.drop('sample_table', run=False))\n", " \"\"\"\n", " >>>\n", " drop table if exists sample_table\n", " \"\"\"\n", "\n", " print(sn.select('sample_table', n=1))\n", " \"\"\"\n", " >>>\n", " select\n", " *\n", " from sample_table\n", " limit 1\n", " \"\"\"\n", " ```\n", " \n", "
\n", "

Using auto_run

\n", "
\n", " \n", " The `run` method argument has the following signature:\n", " ```{div} sn-left-pad2\n", " *run: Optional[*{class}`bool`*] = None*\n", " ```\n", " If a valid {class}`bool` isn't passed in the place of *None*, the current value of its\n", " {attr}`~snowmobile.core.sql.SQL.auto_run` attribute determines whether or not\n", " to execute the sql it generates.\n", " \n", " ```{div} sn-pre-code-s\n", " An alternative to providing *run=False* across a series of methods in order to inspect the sql being\n", " generated is then to modify this attribute's value on a given instance of \n", " , done with:\n", " ```\n", " ```python\n", " sn.auto_run = False\n", " ```\n", " \n", " ```{div} sn-pre-code-s, sn-post-code\n", " Once set to *False*, an equivalent `sample1` and `sample2` can be created with:\n", " ```\n", " ```python\n", " sample1 = sn.select('sample_table', run=False)\n", " sample2 = sn.select('sample_table')\n", " \n", " print(type(sample1)) #> \n", " print(sample1 == sample2) #> True\n", " ```\n", "\n", " ```{div} sn-pre-code-s, sn-post-code\n", " Because methods defer to {attr}`~snowmobile.core.sql.SQL.auto_run`\n", " in absence of an explicit argument, it can be executed off the same instance of\n", " with:\n", " ```\n", " ```python\n", " df_sample = sn.select('sample_table', run=True)\n", " print(type(df_sample)) #> \n", " ```\n", " \n", "\n", "````````\n", "\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "(usage/sql/setting-nm-and-obj)=\n", "### Setting {attr}`~snowmobile.SQL.nm` and {attr}`~snowmobile.SQL.obj`\n", "
\n", "\n", "````````{div} sn-tabbed-section\n", "\n", " ```````{tabbed}  \n", " Most {class}`~snowmobile.SQL` methods need to know an in-warehouse object's name \n", " ({attr}`~snowmobile.SQL.nm`) and type ({attr}`~snowmobile.SQL.obj`), which default \n", " to *None* and *table* respectively. \n", " \n", " ```{div} sn-pre-code-s\n", " These defaults are why we can write:\n", " ```\n", " ```python\n", " _ = sn.drop('sample_table', run=False)\n", " ```\n", " \n", " ```{div} sn-pre-code-s\n", " Instead of:\n", " ```\n", " ```python\n", " _ = sn.drop('sample_table', obj='table', run=False)\n", " ```\n", " \n", "
\n", "
\n", " \n", " In the same way as the `run` method argument and the [**auto_run**](using-auto-run) attribute, \n", " {class}`~snowmobile.SQL` deffers to the values of its {attr}`~snowmobile.SQL.nm` and {attr}`~snowmobile.SQL.obj` \n", " attributes in absence of valid strings passed through the `nm` and `obj` method arguments.\n", " \n", " ```{div} sn-pre-code-s\n", " Bringing these together and assuming a default instace of\n", " , the following can be run:\n", " ```\n", " ```python\n", " sn.auto_run = False\n", " sn.nm = 'sample_table'\n", " \n", " sample1 = sn.select('sample_table')\n", " sample2 = sn.select()\n", " df_sample = sn.select(run=True)\n", " \n", " print(type(sample1)) #> \n", " print(sample1 == sample2) #> True\n", " print(type(df_sample)) #> \n", " ```\n", " \n", " ```````\n", "\n", "````````\n", "\n", "
" ] }, { "cell_type": "markdown", "metadata": { "tags": [ "remove-cell" ] }, "source": [ "# NO RENDER BELOW THIS POINT\n", "---" ] }, { "cell_type": "markdown", "metadata": { "tags": [ "remove-cell" ] }, "source": [ "
\n", "\n", "##### HEADER GOES HERE\n", "
\n", "\n", "``````{div} sn-tabbed-section\n", "\n", " `````{tabbed}  \n", " CONTENT GOES HERE\n", " `````\n", "\n", " `````{tabbed} +\n", " MORE CONTENT GOES HERE\n", " ````` \n", "\n", "```````\n", "\n", "
" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "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.9" }, "toc-autonumbering": false, "toc-showmarkdowntxt": true }, "nbformat": 4, "nbformat_minor": 4 }