{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Snowflake connector and Ingesting .csv files\n",
    "### Requirements:\n",
    "\n",
    "install snowflake-sqlalchemy in your jupyter environemnt  \n",
    "(didn't come automatically from anaconda, which was a first)\n",
    "\n",
    "From the virtual environment you're running the notebook within:\n",
    "   \n",
    "```{python}\n",
    "pip install snowflake-sqlalchemy\n",
    "```\n",
    "\n",
    "\n",
    "these links were useful:  \n",
    "https://conda.io/docs/user-guide/tasks/manage-pkgs.html   \n",
    "https://docs.snowflake.net/manuals/user-guide/sqlalchemy.html\n",
    "\n",
    "\n",
    "populate `user`, `password`, etc in the following cell. \n",
    "\n",
    "\n",
    "\n",
    "In the following cell I refer to a sample table `names` that was created via the snowflake console. There are examples of creating tables through this inteface later in this notebook. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, Table, MetaData, Column, String\n",
    "from sqlalchemy.sql import select, text\n",
    "from snowflake.sqlalchemy import URL\n",
    "metadata = MetaData()\n",
    "\n",
    "table_name = 'test_table'\n",
    "names = Table(table_name, metadata, Column('name', String))\n",
    "\n",
    "\n",
    "engine = create_engine(URL(\n",
    "    user='seggensperger',\n",
    "    password='',\n",
    "    account='orchard',\n",
    "    database='dev_engineering',\n",
    "    schema='events_streams',\n",
    "    role='dev_engineering',\n",
    "    warehouse='dev_ows_warehouse'\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inserting Data\n",
    "   \n",
    "With a DB connect engine in place, we can begin insert operations. Commented out is an approach to inserting one item at a time. More than likely many items at a time will be the use case. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Inset One Name\n",
    "# insert = names.insert().values(name='Scott')\n",
    "# insert.compile().params\n",
    "# below replace this with connection statement \n",
    "# connection.execute(insert)\n",
    "\n",
    "\n",
    "# Insert Many Names\n",
    "names_list = ['andi', 'james', 'josh', 'meghan', 'scott']\n",
    "names_list = [{'name': name} for name in names_list]\n",
    "print(names_list)\n",
    "\n",
    "try:\n",
    "    connection = engine.connect()\n",
    "    connection.execute(names.insert(), names_list)\n",
    "finally:\n",
    "    connection.close()\n",
    "    engine.dispose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting Data\n",
    "   \n",
    "With data inserted into the snowflake table, we can go about fetching this data, again using the SQLAlchemy builtin function `select()` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "select_names = select([names])\n",
    "print('constructed sql:')\n",
    "print(str(select_names))\n",
    "try:\n",
    "    connection = engine.connect()\n",
    "    results = connection.execute(select_names).fetchall()\n",
    "finally:\n",
    "    connection.close()\n",
    "    engine.dispose()\n",
    "print('results:')\n",
    "print(results)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "In addition to the SQLAlchemy builtins, raw SQL can be used. In this example the results are filtered to a single with matching condition. This can be accomplised with either sqlalchemy builtin functions or raw sql.\n",
    "\n",
    "See the snowflake sqlalchemy or plain sqlalchemy documentation for more:  \n",
    "https://docs.snowflake.net/manuals/user-guide/sqlalchemy.html\n",
    "https://docs.sqlalchemy.org/en/latest/core/expression_api.html\n",
    "https://docs.sqlalchemy.org/en/latest/orm/tutorial.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_text = \"\"\"\n",
    "select * from {table}\n",
    "where name = :name\n",
    "\"\"\".format(table=table_name)\n",
    "\n",
    "sql_text = text(sql_text)\n",
    "\n",
    "params = {'name': 'scott'}\n",
    "\n",
    "try:\n",
    "    connection = engine.connect()\n",
    "    results = connection.execute(sql_text, params).fetchall()\n",
    "finally:\n",
    "    connection.close()\n",
    "    engine.dispose()\n",
    "\n",
    "print(results)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "### Loading from .csv files\n",
    "   \n",
    "For inserting data, our typical case will be to load some data from a .csv file, originating from some obscure far away corner of someone's desktop computer. Luckly pandas has some great tooling for this:\n",
    "   \n",
    "https://pandas.pydata.org/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "placement_df = pd.read_csv('placement_sample.csv')\n",
    "placement_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Tables in Snowflake to contain pandas data frame\n",
    "Create table 'placement_sample' in snowflake\n",
    "\n",
    "In this example, the production placement table is used as a template for the table, which avoids the need to specify columns. This step will be necessary when ingesting data that doesn't match an existing table. \n",
    "\n",
    "See https://docs.snowflake.net/manuals/sql-reference/sql/create-table.html for full documentation on creating tables and specifying column types. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "placement_table = 'placement_sample'\n",
    "\n",
    "create_table_sql = \"\"\"\n",
    "create or replace table dev_engineering.scott_mobile.{table} \n",
    "like facts.prod.placement\n",
    "\"\"\".format(table=placement_table)\n",
    "\n",
    "try:\n",
    "    connection = engine.connect()\n",
    "    connection.execute(create_table_sql)\n",
    "finally:\n",
    "    connection.close()\n",
    "    engine.dispose()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading data to SF from a pandas dataframe\n",
    "\n",
    "Here's the easy part: the DataFrame class provides a simple `.to_sql(...)` method which will write to snowflake using the table specified and the connection engine we specified above. \n",
    "\n",
    "This is not reccommended for larger tables as the connection will timeout. Anything less than 1M rows is likely fine. In the case or larger ingestions, we will need to leverage an S3 location and Snowflake's `copy_into` functionality."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "placement_df.to_sql(\n",
    "    placement_table, \n",
    "    engine, \n",
    "    if_exists='replace', \n",
    "    index=False, \n",
    "    index_label=None, \n",
    "    chunksize=20000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query the ingested data\n",
    "To verify the data has been ingested, we can now query the data from snowflake. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "select_placement_data_sql = 'select * from {}'.format(placement_table)\n",
    "\n",
    "try:\n",
    "    connection = engine.connect()\n",
    "    results = connection.execute(select_placement_data_sql).fetchall()\n",
    "finally:\n",
    "    connection.close()\n",
    "    engine.dispose()\n",
    "\n",
    "print(len(results))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}