00_Setup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import snowflake.snowpark as snp\n",
    "import json\n",
    "import getpass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Initial Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need a way to save state throughout the project.  We will initially login as the ACCOUNTADMIN role in order to setup some additional users as well as the compute resources we will need. \n",
    "\n",
    "We will specify a couple of different compute resources which allows us to scale up and down easily.  Most of the workflow can use an extra-small warehouse but for certain tasks (ie. feature engineering and model training) we may need larger compute.  By specifying them in the state dictionary we can easily select the correct compute for any particular task.\n",
    "  \n",
    "Update the <i>username, account_locator, cloud_region_id </i> in the state dictionary below with the initial user that was created with your trial account. \n",
    "\n",
    "You can copy and paste those from your Welcome to Snowflake email:\n",
    "<i>Your account has been activated. Your username is <username> and your account URL is https://{account_locator}.{cloud_region_id}.snowflakecomputing.com."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_dict = {\n",
    "    \"connection_parameters\": {\"user\": \"<username>\",\n",
    "                              \"account\": \"<account_locator>.<cloud_region_id>\",\n",
    "                              \"role\": \"ACCOUNTADMIN\"\n",
    "                             },\n",
    "    \"compute_parameters\" : {\"default_warehouse\": \"XSMALL_WH\",  \n",
    "                            \"ds_warehouse\": \"LARGE_WH\",  \n",
    "                            },\n",
    "    \"additional_roles\" : { \"default_role\": \"PUBLIC\",\n",
    "                            \"project_role\": \"ACCOUNTADMIN\",  \n",
    "                         }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('./include/state.json', 'w') as sdf:\n",
    "    json.dump(state_dict, sdf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will connect with username and password.  In a non-demo system it is very important to use properly secured passwords with secret managers and/or oauth."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "account_admin_pass = getpass.getpass('Enter pass for user with ACCOUNTADMIN access: ')\n",
    "with open('./include/state.json') as sdf:\n",
    "    state_dict = json.load(sdf)\n",
    "\n",
    "state_dict['connection_parameters']['password'] = account_admin_pass\n",
    "\n",
    "session = snp.Session.builder.configs(state_dict[\"connection_parameters\"]).create()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a sample user which will be used for the hands-on-lab. Normally you will have different roles (and possibly different users) for data scientists, data engineers, ML engineers, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.use_role('securityadmin')\n",
    "\n",
    "demo_username='jack'\n",
    "\n",
    "session.sql(\"CREATE USER IF NOT EXISTS \"+demo_username+\\\n",
    "            \" LOGIN_NAME = '\"+demo_username+\"'\"+\\\n",
    "            \" FIRST_NAME = 'SNOWPARK'\"+\\\n",
    "            \" LAST_NAME = 'SQUIRREL'\"+\\\n",
    "            \" EMAIL = 'jack@squirrelly.snowpark'\"+\\\n",
    "            \" DEFAULT_ROLE = 'PUBLIC'\"+\\\n",
    "            \" MUST_CHANGE_PASSWORD = FALSE\")\\\n",
    "        .collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.use_role('securityadmin')\n",
    "demo_user_password=getpass.getpass('Enter a new password for the demo user '+demo_username)\n",
    "try:\n",
    "    session.sql(\"ALTER USER \"+demo_username+\" SET PASSWORD = '\"+demo_user_password+\"'\").collect()\n",
    "except:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create compute instance as per the state dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.use_role('ACCOUNTADMIN')\n",
    "\n",
    "session.sql(\"GRANT ROLE \"+state_dict['additional_roles']['project_role']+' TO USER '+demo_username).collect()\n",
    "\n",
    "for role in state_dict['additional_roles'].values():\n",
    "    for wh in state_dict['compute_parameters'].values():\n",
    "        session.sql(\"GRANT CREATE DATABASE ON ACCOUNT TO ROLE \"+role).collect()\n",
    "        session.sql(\"CREATE WAREHOUSE IF NOT EXISTS \"+wh+\\\n",
    "                \" WITH WAREHOUSE_SIZE = '\"+wh.split('_')[0]+\\\n",
    "                \"' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE initially_suspended = true;\")\\\n",
    "            .collect()\n",
    "        session.sql(\"GRANT USAGE ON WAREHOUSE \"+wh+\" TO ROLE \"+role).collect() \n",
    "        session.sql(\"GRANT OPERATE ON WAREHOUSE \"+wh+\" TO ROLE \"+role).collect() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Allow users to import data shares"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.use_role('ACCOUNTADMIN')\n",
    "session.sql(\"GRANT IMPORT SHARE ON ACCOUNT TO \"+state_dict['additional_roles']['default_role']).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now update the state dictionary to use the non-admin account.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_dict['connection_parameters']['user'] = demo_username\n",
    "state_dict['connection_parameters']['password'] = demo_user_password\n",
    "state_dict['connection_parameters']['role'] = state_dict['additional_roles']['project_role']\n",
    "state_dict['connection_parameters']['database'] = 'SQUIRRELLY_'+demo_username\n",
    "state_dict['connection_parameters']['schema'] = 'DEMO'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save the updated state dictionary for the project team to use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('./include/state.json', 'w') as sdf:\n",
    "    json.dump(state_dict, sdf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a python function to simplify the users' steps of starting a session.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%writefile steps/snowpark_connection.py\n",
    "def snowpark_connect(state_file='./include/state.json'):\n",
    "    import snowflake.snowpark as snp\n",
    "    import json\n",
    "    \n",
    "    with open(state_file) as sdf:\n",
    "        state_dict = json.load(sdf)    \n",
    "    \n",
    "    session=None\n",
    "    session = snp.Session.builder.configs(state_dict[\"connection_parameters\"]).create()\n",
    "    session.use_warehouse(state_dict['compute_parameters']['default_warehouse'])\n",
    "    return session, state_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Test the function that users will use.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from steps.snowpark_connection import snowpark_connect\n",
    "session, state_dict = snowpark_connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.get_current_warehouse()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.close()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.13 ('squirrelly_env')",
   "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.8.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "2ba27bfd1749f7c3a7bc0f0e0b81a34114b55e33b7aa6e17a375be311f1d8668"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}