connect-to-snowflake.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to connect a SageMaker notebook to Snowflake using Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, I will walk through the steps to connect to a Snowflake data warehouse and pull data and tables to a dataframe on the Sagemaker notebook. This is largely explained in the post from Robert Fehrmann here: https://www.snowflake.com/blog/connecting-a-jupyter-notebook-to-snowflake-through-python-part-3/.\n",
    "\n",
    "The connection is achieved using the Snowflake Connector for Python, which is a pure Python package that has no dependencies on JDBC or ODBC. More can be read here: https://docs.snowflake.com/en/user-guide/python-connector.html."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First check if the necessary dependencies are installed, and if not (or there is an older version), de-install and reinstall the required version."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "CFFI_VERSION=$(pip list 2>/dev/null | grep cffi )\n",
    "echo $CFFI_VERSION\n",
    "if [[ \"$CFFI_VERSION\" == \"cffi (1.10.0)\" ]]\n",
    "then \n",
    "   pip uninstall --yes cffi\n",
    "fi\n",
    "yum_log=$(sudo yum install -y libffi-devel openssl-devel)\n",
    "pip_log=$(pip install --upgrade snowflake-connector-python)  \n",
    "if [[ \"$CFFI_VERSION\" == \"cffi (1.10.0)\" ]]\n",
    "then \n",
    "   echo \"configuration has changed; restart notebook\"\n",
    "fi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Install the Pandas-compatible version of the Snowflake Connector for Python. While not necessary (and perfectly fine to just use the snowflake connector imported below), it allows you to use some Pandas-oriented APIs with the Snowflake connector (like `fetch_pandas_all()` below).\n",
    "\n",
    "You may have to update the snowflake connector for some of the APIs that work with pandas dataframes to work properly. Use the following line of code to do so:\n",
    "\n",
    "`!pip install snowflake-connector-python[pandas]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import snowflake.connector\n",
    "\n",
    "# Connecting to Snowflake using the default authenticator\n",
    "# You will have to enter appropriate strings for the required connector parameters\n",
    "'''\n",
    "param_values = {\n",
    "    '/SNOWFLAKE/USER_ID': '',\n",
    "    '/SNOWFLAKE/PASSWORD': '',\n",
    "    '/SNOWFLAKE/ACCOUNT_ID': '',\n",
    "    '/SNOWFLAKE/WAREHOUSE': '',\n",
    "    '/SNOWFLAKE/DATABASE': '',\n",
    "    '/SNOWFLAKE/SCHEMA': ''\n",
    "}\n",
    "'''\n",
    "\n",
    "ctx = snowflake.connector.connect(\n",
    "  user=param_values['/SNOWFLAKE/USER_ID'],\n",
    "  password=param_values['/SNOWFLAKE/PASSWORD'],\n",
    "  account=param_values['/SNOWFLAKE/ACCOUNT_ID'],\n",
    "  warehouse=param_values['/SNOWFLAKE/WAREHOUSE'],\n",
    "  database=param_values['/SNOWFLAKE/DATABASE'],\n",
    "  schema=param_values['/SNOWFLAKE/SCHEMA'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: it is actually a best practice to use a secure key/value management system, like AWS Systems Manger Paramter Store (SSM) to store and manage your credentials, as opposed to hard-coding them. You can read more about AWS SSM here: https://docs.aws.amazon.com/systems-manager/latest/userguide/systems-manager-parameter-store.html."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a cursor object.\n",
    "cur = ctx.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute a statement that will generate a result set.\n",
    "sql = \"select * from MGML_AWS_SOURCE\"\n",
    "cur.execute(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you opted not to install the newer version of the snowflake python connector you can use the following line of code to assign the executed sql command: `allrows = cur.execute(sql)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "# Fetch the result set from the cursor and deliver it as the Pandas DataFrame.\n",
    "\n",
    "df = cur.fetch_pandas_all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you opted not to install the newer version of the snowflake python connector you can use the following line of code to assign the executed sql command to a pandas dataframe: `df = pd.DataFrame(allrows)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we manipulate a slice of the dataframe and write it back to the table in Snowflake. This could be your results at the end of ML pipeline, Step Functions, etc.\n",
    "\n",
    "Or, if you write large outputs to an S3 bucket for staging and/or cost, you can create and execute a connection with the snowflake connector object and move those files directly from S3 to Snowflake. There is a sample program demonstrating this in the Snowflake documentation linked above. I will reporoduce the link here: https://docs.snowflake.com/en/user-guide/python-connector-example.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from snowflake.connector.pandas_tools import write_pandas\n",
    "import random\n",
    "from datetime import datetime\n",
    "\n",
    "random.seed(datetime.now())\n",
    "temp = random.randint(0,100)\n",
    "\n",
    "df.loc[0,('X0')] = temp\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice we have changed the value of the cell under column 'X0' in the 0th index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the data from the DataFrame to the table named \"MGML_AWS_SOURCE\".\n",
    "success, nchunks, nrows, _ = write_pandas(ctx, df, 'MGML_AWS_SOURCE')\n",
    "print(success)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can double check the success of the write operation by running a SELECT * on your table in Snowflake"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}