{ "cells": [ { "cell_type": "code", "execution_count": 21, "id": "625cdddd", "metadata": {}, "outputs": [], "source": [ "#snowflake connector and pytest\n", "!pip -q install snowflake-connector-python pytest pytest-sugar " ] }, { "cell_type": "code", "execution_count": 22, "id": "7abc3c06", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "DEBUG:absl:READY!!!\n" ] } ], "source": [ "# main imports\n", "import snowflake.connector\n", "import pandas as pd\n", "import os\n", "import boto3 \n", "\n", "# utils\n", "from getpass import getpass\n", "\n", "# logging and re\n", "from absl import logging\n", "import re\n", "\n", "\n", "log_level = \"DEBUG\"\n", "ticket_code = \"EXP_1\"\n", "\n", "logging.set_verbosity(log_level)\n", "logging.debug(\"READY!!!\")" ] }, { "cell_type": "markdown", "id": "9d940946", "metadata": {}, "source": [ "## (1) Direct Snowflake Access " ] }, { "cell_type": "code", "execution_count": 26, "id": "a18bc275", "metadata": {}, "outputs": [], "source": [ "sec_id = 'dev/sagemaker-notebook-instance/SNOWFLAKE_PASSWORD'\n", "\n", "\n", "def get_secret_value(name, version=None):\n", " \"\"\"Gets the value of a secret.\n", "\n", " Version (if defined) is used to retrieve a particular version of\n", " the secret.\n", "\n", " \"\"\"\n", " secrets_client = boto3.client(\"secretsmanager\")\n", " kwargs = {'SecretId': name}\n", " if version is not None:\n", " kwargs['VersionStage'] = version\n", " response = secrets_client.get_secret_value(**kwargs)\n", " return response\n", "\n", "\n", "def get_snowflake_creds(username=\"SAGEMAKER\", account=\"orchard\",\n", " warehouse=\"DEV_OWS_ENGINEERING\"):\n", " \"\"\"\n", " Fetches and returns snowflake creds for connecting to snowflake\n", "\n", " Please use this within the scope of a function if using this on a shared instance\n", " This is so that the password is in memory only when its needed and gets dropped \n", " once its no longer required.\n", "\n", " returns:\n", " - creds (dict) - a dictionary containing user creds\n", "\n", " \"\"\"\n", " creds = {\n", " \"user\": username,\n", " \"password\": get_secret_value(sec_id)['SecretString'],\n", " \"account\": \"orchard\",\n", " \"warehouse\": warehouse,\n", " \"protocol\": 'https'\n", " }\n", " return creds\n", "\n", "\n", "def snowflake_connector_factory(creds=None):\n", " \"\"\"\n", " A Factory for creating snowflake connectors.\n", "\n", " This returns the cursor after opening a session with snowflake.\n", "\n", " params:\n", " - creds - snowflake credentials \n", "\n", " returns:\n", " - cursor - snowflake session cursor\n", " \"\"\"\n", " try:\n", " if creds:\n", " _creds = creds\n", " else:\n", " _creds = get_snowflake_creds()\n", " return snowflake.connector.connect(**_creds).cursor()\n", " except Exception as e:\n", " logging.error(f\"Something went wrong - {str(e)}\")\n", "\n", "\n", "def _is_version_number(s):\n", " \"Check and returns true if its a version number\"\n", " return re.search(\"^[0-9][.0-9]*[0-9]$\", s) is not None\n", "\n", "\n", "def test_connection():\n", " \"\"\" tests connection to snowflake \"\"\"\n", " with snowflake_connector_factory() as cs:\n", " try:\n", " cs.execute(\"SELECT current_version()\")\n", "# cs.execute(\"USE WAREHOUSE DEV_PERFORMANCE_WAREHOUSE;\")\n", "# cs.execute(\"select * from INTELLIGENCE.DEV_TALLMAN.TALLMAN_K_PROTO_TABLE_JUNE_23 limit 1;\")\n", " one_row = cs.fetchone()\n", " # make sure its just one row\n", " assert len(one_row) == 1\n", " # make sure it is a version number\n", " assert _is_version_number(one_row[0])\n", " logging.info(f\"Your snowflake version - {one_row[0]} PASSED!\")\n", " except Exception as e:\n", " logging.error(f\"Something went wrong - {str(e)}\")" ] }, { "cell_type": "code", "execution_count": 27, "id": "3d60c564", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "INFO:absl:Your snowflake version - 7.24.2 PASSED!\n" ] } ], "source": [ "test_connection()" ] }, { "cell_type": "code", "execution_count": 32, "id": "d99fc93b", "metadata": {}, "outputs": [], "source": [ "with snowflake_connector_factory() as cs:\n", " try:\n", " cs.execute(\"USE WAREHOUSE DEV_PERFORMANCE_WAREHOUSE;\")\n", " cs.execute(\"SELECT * from INTELLIGENCE.DEV_TALLMAN.TALLMAN_K_PROTO_TABLE_JUNE_23 LIMIT 100;\")\n", " rows = cs.fetchall()\n", " except Exception as e:\n", " logging.error(f\"Something went wrong - {str(e)}\")" ] }, { "cell_type": "code", "execution_count": 33, "id": "5da973b7", "metadata": {}, "outputs": [], "source": [ "data_df = pd.DataFrame(rows, columns=map(lambda meta: meta[0], cs.description))" ] }, { "cell_type": "code", "execution_count": 34, "id": "64d2ee29", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ISRCPRED_TYPEMSERMSENRMSEAVG_RMSEMAEAVG_MAESUM_FORECAST_ERRORSAVG_FELEN_DFFIRST_DAY_TRAIN_SET_STREAMSLAST_DAY_TRAIN_SET_STREAMSLINEAR_GRADIENTAVERAGE_DAILY_STREAMSTEST_DATA_MIN_DATE_STREAMSTEST_DATA_MAX_DATE_STREAMSTEST_DATA_LINEAR_GRADIENTMEDIAN_STREAMS
0QMBZ92156553actuals_crossing_predicted149.13664412.2121510.8722970.0872304.1829500.02987811.8640.08474314049620.09848547.91729344460.28571446.000
1USQE10910023actuals_crossing_predicted67.3648568.2076100.2647620.05862629.8424340.213160-18.904-0.13502914015577-0.590909111.18797079871.142857107.000
2QM4TX1960783actuals_crossing_predicted593.51370124.3621370.4350380.17401527.6051890.19718094.4030.67430714084880.03030377.5338357972-1.00000072.000
3USLZJ2190718actuals_crossing_predicted55013.011845234.5485280.2822491.675347411.3134732.937953-463.238-3.308843140244836048.7575763137.39097740433212-118.7142863011.000
4QMDA72177286actuals_crossing_predicted2572.35864250.7184250.4057470.36227428.1287090.200919243.0241.735886140246179-0.507576204.12782017828214.857143196.000
............................................................
95GBQCP1400107actuals_crossing_predicted11834.600948108.7869520.1178620.7770501674.93999811.963857-132.728-0.948057140295231141.2272733585.71428633912915-68.0000003438.000
96UK9951702001actuals_crossing_predicted635.69762825.2130450.3191520.18009324.3447690.173891-55.210-0.3943571408863-0.18939470.66165411754-9.00000068.000
97USSTT2200007actuals_crossing_predicted141.87884211.9112910.4411590.0850815.4397140.038855-40.160-0.286857140811030.166667118.0601509793-0.57142995.000
98ROCMA1911874actuals_crossing_predicted180.72676213.4434650.2987440.0960252.0810310.01486517.9110.127936140321190.65909190.879699115109-0.85714397.000
99JPQ941500014actuals_crossing_predicted80.2321428.9572400.3732180.0639801.6970800.012122-22.805-0.16289314045460.00757641.59398541450.57142941.000
\n", "

100 rows × 19 columns

\n", "
" ], "text/plain": [ " ISRC PRED_TYPE MSE RMSE \\\n", "0 QMBZ92156553 actuals_crossing_predicted 149.136644 12.212151 \n", "1 USQE10910023 actuals_crossing_predicted 67.364856 8.207610 \n", "2 QM4TX1960783 actuals_crossing_predicted 593.513701 24.362137 \n", "3 USLZJ2190718 actuals_crossing_predicted 55013.011845 234.548528 \n", "4 QMDA72177286 actuals_crossing_predicted 2572.358642 50.718425 \n", ".. ... ... ... ... \n", "95 GBQCP1400107 actuals_crossing_predicted 11834.600948 108.786952 \n", "96 UK9951702001 actuals_crossing_predicted 635.697628 25.213045 \n", "97 USSTT2200007 actuals_crossing_predicted 141.878842 11.911291 \n", "98 ROCMA1911874 actuals_crossing_predicted 180.726762 13.443465 \n", "99 JPQ941500014 actuals_crossing_predicted 80.232142 8.957240 \n", "\n", " NRMSE AVG_RMSE MAE AVG_MAE SUM_FORECAST_ERRORS AVG_FE \\\n", "0 0.872297 0.087230 4.182950 0.029878 11.864 0.084743 \n", "1 0.264762 0.058626 29.842434 0.213160 -18.904 -0.135029 \n", "2 0.435038 0.174015 27.605189 0.197180 94.403 0.674307 \n", "3 0.282249 1.675347 411.313473 2.937953 -463.238 -3.308843 \n", "4 0.405747 0.362274 28.128709 0.200919 243.024 1.735886 \n", ".. ... ... ... ... ... ... \n", "95 0.117862 0.777050 1674.939998 11.963857 -132.728 -0.948057 \n", "96 0.319152 0.180093 24.344769 0.173891 -55.210 -0.394357 \n", "97 0.441159 0.085081 5.439714 0.038855 -40.160 -0.286857 \n", "98 0.298744 0.096025 2.081031 0.014865 17.911 0.127936 \n", "99 0.373218 0.063980 1.697080 0.012122 -22.805 -0.162893 \n", "\n", " LEN_DF FIRST_DAY_TRAIN_SET_STREAMS LAST_DAY_TRAIN_SET_STREAMS \\\n", "0 140 49 62 \n", "1 140 155 77 \n", "2 140 84 88 \n", "3 140 2448 3604 \n", "4 140 246 179 \n", ".. ... ... ... \n", "95 140 2952 3114 \n", "96 140 88 63 \n", "97 140 81 103 \n", "98 140 32 119 \n", "99 140 45 46 \n", "\n", " LINEAR_GRADIENT AVERAGE_DAILY_STREAMS TEST_DATA_MIN_DATE_STREAMS \\\n", "0 0.098485 47.917293 44 \n", "1 -0.590909 111.187970 79 \n", "2 0.030303 77.533835 79 \n", "3 8.757576 3137.390977 4043 \n", "4 -0.507576 204.127820 178 \n", ".. ... ... ... \n", "95 1.227273 3585.714286 3391 \n", "96 -0.189394 70.661654 117 \n", "97 0.166667 118.060150 97 \n", "98 0.659091 90.879699 115 \n", "99 0.007576 41.593985 41 \n", "\n", " TEST_DATA_MAX_DATE_STREAMS TEST_DATA_LINEAR_GRADIENT MEDIAN_STREAMS \n", "0 46 0.285714 46.000 \n", "1 87 1.142857 107.000 \n", "2 72 -1.000000 72.000 \n", "3 3212 -118.714286 3011.000 \n", "4 282 14.857143 196.000 \n", ".. ... ... ... \n", "95 2915 -68.000000 3438.000 \n", "96 54 -9.000000 68.000 \n", "97 93 -0.571429 95.000 \n", "98 109 -0.857143 97.000 \n", "99 45 0.571429 41.000 \n", "\n", "[100 rows x 19 columns]" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data_df" ] }, { "cell_type": "code", "execution_count": 35, "id": "7f1a42f4", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
MSERMSENRMSEAVG_RMSEMAEAVG_MAESUM_FORECAST_ERRORSAVG_FELEN_DFFIRST_DAY_TRAIN_SET_STREAMSLAST_DAY_TRAIN_SET_STREAMSTEST_DATA_MIN_DATE_STREAMSTEST_DATA_MAX_DATE_STREAMS
count100.000000100.000000100.000000100.000000100.000000100.000000100.000000100.000000100.0100.000000100.000000100.000000100.000000
mean1954.56497422.6523580.4326300.16180358.5463340.418188-26.183940-0.187028140.0266.220000215.170000226.500000207.010000
std9071.58795438.1575110.1601460.272554204.9029251.463592191.4686571.3676330.0710.818813590.032683627.256355537.015217
min21.1597664.5999750.1178620.0328570.1029230.000735-1391.398000-9.938557140.016.00000014.00000016.00000014.000000
25%74.0346488.6029110.3218150.0614494.6833710.033453-46.959250-0.335423140.045.00000035.00000035.00000037.000000
50%136.14082411.6653480.4153620.0833249.7796300.069855-18.344500-0.131032140.074.50000056.00000057.00000058.000000
75%376.94766519.4140590.4980620.13867227.1775680.19412520.8470000.148907140.0142.000000120.000000117.50000097.000000
max70338.410860265.2138960.8974601.8943851674.93999811.963857685.3770004.895550140.04948.0000003604.0000004043.0000003212.000000
\n", "
" ], "text/plain": [ " MSE RMSE NRMSE AVG_RMSE MAE \\\n", "count 100.000000 100.000000 100.000000 100.000000 100.000000 \n", "mean 1954.564974 22.652358 0.432630 0.161803 58.546334 \n", "std 9071.587954 38.157511 0.160146 0.272554 204.902925 \n", "min 21.159766 4.599975 0.117862 0.032857 0.102923 \n", "25% 74.034648 8.602911 0.321815 0.061449 4.683371 \n", "50% 136.140824 11.665348 0.415362 0.083324 9.779630 \n", "75% 376.947665 19.414059 0.498062 0.138672 27.177568 \n", "max 70338.410860 265.213896 0.897460 1.894385 1674.939998 \n", "\n", " AVG_MAE SUM_FORECAST_ERRORS AVG_FE LEN_DF \\\n", "count 100.000000 100.000000 100.000000 100.0 \n", "mean 0.418188 -26.183940 -0.187028 140.0 \n", "std 1.463592 191.468657 1.367633 0.0 \n", "min 0.000735 -1391.398000 -9.938557 140.0 \n", "25% 0.033453 -46.959250 -0.335423 140.0 \n", "50% 0.069855 -18.344500 -0.131032 140.0 \n", "75% 0.194125 20.847000 0.148907 140.0 \n", "max 11.963857 685.377000 4.895550 140.0 \n", "\n", " FIRST_DAY_TRAIN_SET_STREAMS LAST_DAY_TRAIN_SET_STREAMS \\\n", "count 100.000000 100.000000 \n", "mean 266.220000 215.170000 \n", "std 710.818813 590.032683 \n", "min 16.000000 14.000000 \n", "25% 45.000000 35.000000 \n", "50% 74.500000 56.000000 \n", "75% 142.000000 120.000000 \n", "max 4948.000000 3604.000000 \n", "\n", " TEST_DATA_MIN_DATE_STREAMS TEST_DATA_MAX_DATE_STREAMS \n", "count 100.000000 100.000000 \n", "mean 226.500000 207.010000 \n", "std 627.256355 537.015217 \n", "min 16.000000 14.000000 \n", "25% 35.000000 37.000000 \n", "50% 57.000000 58.000000 \n", "75% 117.500000 97.000000 \n", "max 4043.000000 3212.000000 " ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data_df.describe()" ] }, { "cell_type": "code", "execution_count": 36, "id": "40bd9732", "metadata": {}, "outputs": [], "source": [ "data_df.to_csv(\"sample_df.csv\", index=False)" ] }, { "cell_type": "code", "execution_count": null, "id": "3fc5737f", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "conda_tensorflow2_p310", "language": "python", "name": "conda_tensorflow2_p310" }, "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.10.10" } }, "nbformat": 4, "nbformat_minor": 5 }