{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "acded6d6", "metadata": {}, "outputs": [], "source": [ "#!pip install snowflake-connector-python" ] }, { "cell_type": "code", "execution_count": 1, "id": "8a47ea48", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n", "black 23.7.0 requires packaging>=22.0, but you have packaging 21.3 which is incompatible.\n", "sparkmagic 0.20.5 requires nest-asyncio==1.5.5, but you have nest-asyncio 1.5.6 which is incompatible.\n", "sparkmagic 0.20.5 requires pandas<2.0.0,>=0.17.1, but you have pandas 2.0.3 which is incompatible.\u001b[0m\u001b[31m\n", "\u001b[0m" ] } ], "source": [ "!pip -q install snowflake-connector-python #pytest pytest-sugar " ] }, { "cell_type": "code", "execution_count": 3, "id": "2d4aab03", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n", "sparkmagic 0.20.5 requires nest-asyncio==1.5.5, but you have nest-asyncio 1.5.6 which is incompatible.\n", "sparkmagic 0.20.5 requires pandas<2.0.0,>=0.17.1, but you have pandas 2.0.3 which is incompatible.\u001b[0m\u001b[31m\n", "\u001b[0m" ] } ], "source": [ "#!pip install pyarrow==8.0.0 pandas>=1.0.0 pyyaml==6.0.1 snowflake-connector-python==3.2.0 " ] }, { "cell_type": "code", "execution_count": 4, "id": "16896d7d", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\u001b[33mWARNING: Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError(': Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/pyecharts/\u001b[0m\u001b[33m\n", "\u001b[0m\u001b[33mWARNING: Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError(': Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/pyecharts/\u001b[0m\u001b[33m\n", "\u001b[0m\u001b[33mWARNING: Retrying (Retry(total=2, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError(': Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/pyecharts/\u001b[0m\u001b[33m\n", "\u001b[0m\u001b[33mWARNING: Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError(': Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/pyecharts/\u001b[0m\u001b[33m\n", "\u001b[0m\u001b[33mWARNING: Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError(': Failed to establish a new connection: [Errno -2] Name or service not known')': /simple/pyecharts/\u001b[0m\u001b[33m\n", "\u001b[0m\u001b[31mERROR: Could not find a version that satisfies the requirement pyecharts (from versions: none)\u001b[0m\u001b[31m\n", "\u001b[0m\u001b[31mERROR: No matching distribution found for pyecharts\u001b[0m\u001b[31m\n", "\u001b[0m" ] } ], "source": [ "# echarts library\n", "!pip -q install pyecharts absl-py" ] }, { "cell_type": "code", "execution_count": 2, "id": "214b9438", "metadata": { "tags": [] }, "outputs": [ { "ename": "ModuleNotFoundError", "evalue": "No module named 'snowflake'", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mModuleNotFoundError\u001b[0m Traceback (most recent call last)", "Cell \u001b[0;32mIn[2], line 2\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[38;5;66;03m# main imports\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01msnowflake\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mconnector\u001b[39;00m\n\u001b[1;32m 3\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mpd\u001b[39;00m\n\u001b[1;32m 4\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mnumpy\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mnp\u001b[39;00m\n", "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'snowflake'" ] } ], "source": [ "# main imports\n", "import snowflake.connector\n", "import pandas as pd\n", "import numpy as np\n", "import pyecharts as echarts\n", "import os\n", "import boto3 \n", "import pickle\n", "import datetime\n", "from datetime import datetime, date\n", "from sklearn.preprocessing import StandardScaler, OrdinalEncoder\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": "333a1c7e", "metadata": { "tags": [] }, "source": [ "## Direct Snowflake Connection" ] }, { "cell_type": "code", "execution_count": null, "id": "8a1e9b77", "metadata": { "tags": [] }, "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", " one_row = cs.fetchone()\n", " assert len(one_row) == 1\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": null, "id": "b5a451ec", "metadata": { "tags": [] }, "outputs": [], "source": [ "test_connection()" ] }, { "cell_type": "markdown", "id": "26bd9e84", "metadata": {}, "source": [] }, { "cell_type": "code", "execution_count": null, "id": "da6f9288", "metadata": { "tags": [] }, "outputs": [], "source": [ "with snowflake_connector_factory() as cs:\n", " try:\n", " cs.execute(\"USE WAREHOUSE DEV_OWS_WAREHOUSE;\")\n", " cs.execute(\"\"\"\n", " \n", " select * from intelligence.dbt_prod.orch_spotify_top_200_chart_appearances;\n", " \n", " \"\"\")\n", " rows = cs.fetchall()\n", " except Exception as e:\n", " logging.error(f\"Something went wrong - {str(e)}\")" ] }, { "cell_type": "code", "execution_count": null, "id": "a4e71ef3", "metadata": {}, "outputs": [], "source": [ "data_df = pd.DataFrame(rows, columns=map(lambda meta: meta[0], cs.description))\n", "real_data = data_df.drop_duplicates().copy()\n", "print(data_df.shape)\n", "print(real_data.shape)" ] }, { "cell_type": "code", "execution_count": null, "id": "a579bd2e", "metadata": {}, "outputs": [], "source": [ "bucket = 'dev-cucumbers'\n", "path = 'eimpara/MAZE/log_model_6_sept_2023.pkl'\n", "#s3uri = f's3://{bucket}/{path}'\n", "\n", "s3 = boto3.resource('s3')\n", "pickled_model = pickle.loads(s3.Bucket(bucket).Object(path).get()['Body'].read())\n", "pickled_model\n", "\n", "# with open('/Users/impr001/Documents/Jupyter_Notebooks/Maze/log_model.pkl', 'rb') as file:\n", "# pickled_model = pickle.load(file)" ] }, { "cell_type": "code", "execution_count": null, "id": "ceafd461", "metadata": {}, "outputs": [], "source": [ "real_data['FIXED_RELEASE_DATE'] = pd.to_datetime(real_data['FIXED_RELEASE_DATE'])\n", "real_data['Month_name'] = real_data['FIXED_RELEASE_DATE'].dt.month_name()\n", "real_data['FIXED_RELEASE_DATE'] = real_data['FIXED_RELEASE_DATE'].dt.date\n", "#real_data = real_data[(real_data['FIXED_RELEASE_DATE'] >= date(2023, 8, 11))]" ] }, { "cell_type": "code", "execution_count": null, "id": "4ce8de08", "metadata": {}, "outputs": [], "source": [ "conditions = [(real_data['Month_name']=='November'),\n", " (real_data['Month_name']=='August'),\n", " (real_data['Month_name']=='October'),\n", " (real_data['Month_name']=='April'),\n", " (real_data['Month_name']=='September'),\n", " (real_data['Month_name']=='June'),\n", " (real_data['Month_name']=='May'),\n", " (real_data['Month_name']=='March'),\n", " (real_data['Month_name']=='July'),\n", " (real_data['Month_name']=='January'),\n", " (real_data['Month_name']=='December'),\n", " (real_data['Month_name']=='February')\n", " ]\n", "choices = [9., 1., 10., 0., 11., 6., 8., 7., 5., 4., 2., 3.]\n", "\n", "real_data['Month_release'] = np.select(conditions, choices)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "52c6c881", "metadata": {}, "outputs": [], "source": [ "#{('no_previous_hit',): array([0.]), ('non_recent_hit',): array([1.]), ('recent_hit',): array([2.])}\n", "conditions = [(real_data['PREVIOUS_HIT']==0),\n", " (real_data['PREVIOUS_HIT']==1) & (real_data['DAYS_SINCE_LAST_HIT'] > 365),\n", " (real_data['PREVIOUS_HIT']==1) & (real_data['DAYS_SINCE_LAST_HIT'] <= 365)]\n", "choices = [0., 1., 2.]\n", "\n", "real_data['artist_score'] = np.select(conditions, choices)" ] }, { "cell_type": "code", "execution_count": null, "id": "0e1a767b", "metadata": {}, "outputs": [], "source": [ "real_data['ISRC_first_two_characters'] = real_data['ISRC'].str[:2]\n", "real_data.shape" ] }, { "cell_type": "code", "execution_count": null, "id": "586f87e7", "metadata": {}, "outputs": [], "source": [ "conditions = [(real_data['ISRC_first_two_characters']=='AR'),\n", " (real_data['ISRC_first_two_characters']=='AU'),\n", " (real_data['ISRC_first_two_characters']=='AT'),\n", " (real_data['ISRC_first_two_characters']=='BY'),\n", " (real_data['ISRC_first_two_characters']=='BE'),\n", " (real_data['ISRC_first_two_characters']=='BO'),\n", " (real_data['ISRC_first_two_characters'].isin(['BR', 'BP', 'BX', 'BC', 'BK'])),\n", " (real_data['ISRC_first_two_characters'].isin(['CA', 'CB'])),\n", " (real_data['ISRC_first_two_characters']=='CL'),\n", " (real_data['ISRC_first_two_characters']=='CO'),\n", " (real_data['ISRC_first_two_characters']=='CY'),\n", " (real_data['ISRC_first_two_characters']=='CY'),\n", " (real_data['ISRC_first_two_characters']=='CZ'),\n", " (real_data['ISRC_first_two_characters'].isin(['DK', 'GL', 'FO'])),\n", " (real_data['ISRC_first_two_characters']=='DO'),\n", " (real_data['ISRC_first_two_characters']=='EC'),\n", " (real_data['ISRC_first_two_characters']=='EG'),\n", " (real_data['ISRC_first_two_characters']=='SV'),\n", " (real_data['ISRC_first_two_characters']=='EE'),\n", " (real_data['ISRC_first_two_characters']=='FI'),\n", " (real_data['ISRC_first_two_characters'].isin(['FR', 'FX'])),\n", " (real_data['ISRC_first_two_characters']=='DE'),\n", " (real_data['ISRC_first_two_characters']=='GR'),\n", " (real_data['ISRC_first_two_characters']=='GT'),\n", " (real_data['ISRC_first_two_characters']=='HN'),\n", " (real_data['ISRC_first_two_characters']=='HK'),\n", " (real_data['ISRC_first_two_characters']=='HU'),\n", " (real_data['ISRC_first_two_characters']=='IS'),\n", " (real_data['ISRC_first_two_characters']=='IN'),\n", " (real_data['ISRC_first_two_characters']=='ID'),\n", " (real_data['ISRC_first_two_characters']=='IE'),\n", " (real_data['ISRC_first_two_characters']=='IL'),\n", " (real_data['ISRC_first_two_characters']=='IT'),\n", " (real_data['ISRC_first_two_characters']=='JP'),\n", " (real_data['ISRC_first_two_characters']=='KZ'),\n", " (real_data['ISRC_first_two_characters']=='LV'),\n", " (real_data['ISRC_first_two_characters']=='LT'),\n", " (real_data['ISRC_first_two_characters']=='LU'),\n", " (real_data['ISRC_first_two_characters']=='MY'),\n", " (real_data['ISRC_first_two_characters']=='MX'),\n", " (real_data['ISRC_first_two_characters']=='MA'),\n", " (real_data['ISRC_first_two_characters']=='NL'),\n", " (real_data['ISRC_first_two_characters']=='NZ'),\n", " (real_data['ISRC_first_two_characters']=='NG'),\n", " (real_data['ISRC_first_two_characters']=='NO'),\n", " (real_data['ISRC_first_two_characters']=='PK'),\n", " (real_data['ISRC_first_two_characters']=='PA'),\n", " (real_data['ISRC_first_two_characters']=='PY'),\n", " (real_data['ISRC_first_two_characters']=='PE'),\n", " (real_data['ISRC_first_two_characters']=='PH'),\n", " (real_data['ISRC_first_two_characters']=='PL'),\n", " (real_data['ISRC_first_two_characters']=='PT'),\n", " (real_data['ISRC_first_two_characters']=='RO'),\n", " (real_data['ISRC_first_two_characters']=='SA'),\n", " (real_data['ISRC_first_two_characters']=='SG'),\n", " (real_data['ISRC_first_two_characters']=='SK'),\n", " (real_data['ISRC_first_two_characters'].isin(['ZA', 'ZB'])),\n", " (real_data['ISRC_first_two_characters']=='ES'),\n", " (real_data['ISRC_first_two_characters']=='SE'),\n", " (real_data['ISRC_first_two_characters']=='CH'),\n", " (real_data['ISRC_first_two_characters']=='TH'),\n", " (real_data['ISRC_first_two_characters']=='TR'),\n", " (real_data['ISRC_first_two_characters']=='AE'),\n", " (real_data['ISRC_first_two_characters']=='UA'),\n", " (real_data['ISRC_first_two_characters'].isin(['UK', 'GX', 'GB'])),\n", " (real_data['ISRC_first_two_characters']=='UY'),\n", " (real_data['ISRC_first_two_characters'].isin(['US', 'QM', 'QZ'])),\n", " (real_data['ISRC_first_two_characters']=='VE'),\n", " (real_data['ISRC_first_two_characters']=='VN'),\n", " (real_data['ISRC_first_two_characters'].isin(['PK', 'TC', 'CN', 'ZZ', 'TW', 'BB', 'BG', 'RU', 'KR', 'LK', 'DG', 'SI', 'YE', 'BH', 'JM', 'GD', 'WZ', 'NP', 'SF', 'AI', 'CU']))\n", " ]\n", "\n", "choices = ['Argentina','Australia','Austria', 'Belarus', 'Belgium', 'Bolivia', 'Brazil', 'Bulgaria', 'Canada', 'Chile', 'Colombia', 'Cyprus', 'Czech_Republic', 'Denmark',\n", " 'Dominican_Republic', 'Ecuador', 'Egypt', 'El_Salvador', 'Estonia', 'Finland', 'France', 'Germany', 'Greece', 'Guatemala', 'Honduras', 'Hong_Kong', 'Hungary',\n", " 'Iceland', 'India', 'Indonesia', 'Ireland', 'Israel', 'Italy', 'Japan', 'Kazakhstan', 'Latvia', 'Lithuania', 'Luxembourg', 'Malaysia', 'Mexico', 'Morocco',\n", " 'Netherlands', 'New_Zealand', 'Nigeria', 'Norway', 'Pakistan', 'Panama', 'Paraguay', 'Peru', 'Philippines', 'Poland', 'Portugal', 'Romania', 'Saudi_Arabia', \n", " 'Singapore', 'Slovakia', 'South_Africa', 'Spain', 'Sweden', 'Switzerland', 'Thailand', 'Turkey', 'UAE', 'Ukraine', 'United_Kingdom', 'Uruguay', 'USA', 'Venezuela',\n", " 'Vietnam', 'Worldwild/Other']\n", "\n", "\n", "real_data['Market_manually_derived'] = np.select(conditions, choices)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "05c60c37", "metadata": {}, "outputs": [], "source": [ "real_data = real_data[real_data['Market_manually_derived'].isin(['USA','United_Kingdom', 'Uruguay', 'Canada', 'Chile', 'Colombia', 'Dominican_Republic', 'Ecuador', \n", " 'El_Salvador', 'Guatemala', 'Honduras', 'Mexico', 'Paraguay', 'Peru', 'Venezuela', 'Ireland', 'Argentina','Australia'])].copy() " ] }, { "cell_type": "code", "execution_count": null, "id": "ce8341d1", "metadata": {}, "outputs": [], "source": [ "\n", "X_real = real_data.drop(columns=['ISRC'])\n", "numeric_features= X_real[['ACOUSTICNESS', 'DANCEABILITY', 'DURATION_MS', 'ENERGY', 'INSTRUMENTALNESS', \n", " 'KEY', 'LIVENESS', 'LOUDNESS', 'MODE', 'SPEECHINESS', 'TEMPO', 'TIME_SIGNATURE', 'VALENCE']]\n", "categorical_features = X_real[['Month_release', 'artist_score']]\n", "scaler = StandardScaler() \n", "X_processed = pd.DataFrame(scaler.fit_transform(numeric_features), columns = numeric_features.columns)\n", "real_test = pd.concat([X_processed.reset_index(), categorical_features.reset_index()], axis=1)\n", "real_test = real_test.drop(columns=['index'], axis=1)\n", "\n", "real_test.head()" ] }, { "cell_type": "code", "execution_count": null, "id": "b7871b75", "metadata": {}, "outputs": [], "source": [ "predicted = pickled_model.predict(real_test)\n", "probs = pickled_model.predicted_probabilities(real_test)" ] }, { "cell_type": "code", "execution_count": null, "id": "1165854e", "metadata": {}, "outputs": [], "source": [ "res = pd.DataFrame({'Run_date': datetime.now(),\n", " 'ISRC': real_data['ISRC'], \n", " 'Release_date':real_data['FIXED_RELEASE_DATE'],\n", " 'Model_spec': pickled_model.model,\n", " 'Predicted_probabilities_0': probs[:,0],\n", " 'Predicted_probabilities_1': probs[:,1],\n", " 'Prediction': predicted,\n", " 'Latest_chart_date': real_data['MAX_CHART_DATE']})\n", "res.head()" ] }, { "cell_type": "code", "execution_count": null, "id": "96598693", "metadata": {}, "outputs": [], "source": [ "def compute_flag(row):\n", " if row['Prediction'] == 0 and row['Latest_chart_date'] == None:\n", " return 1\n", " elif row['Prediction'] == 1 and row['Latest_chart_date'] != None:\n", " return 1\n", " else:\n", " return 0\n", "\n", "res['Predicted_correctly'] = res.apply(compute_flag, axis=1)\n", "\n", "res.head()" ] }, { "cell_type": "code", "execution_count": null, "id": "6831d254", "metadata": {}, "outputs": [], "source": [ "sub_for_usa = [(res['Latest_chart_date']>= date(2023,9,8)) & (res['Latest_chart_date']<= date(2023,9,14))]\n", "sub_for_usa" ] }, { "cell_type": "code", "execution_count": 20, "id": "376cb384", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Table saved to S3 bucket: dev-cucumbers, with file name: eimpara/MAZE/ST200_predictions_2023-08-29.csv\n" ] } ], "source": [ "def save_dataframe_s3(sub_for_usa):\n", " s3 = boto3.client('s3')\n", " current_date = datetime.now().strftime(\"%Y-%m-%d\")\n", " bucket_name = 'dev-cucumbers'\n", " filepath = \"eimpara/MAZE/ST200_predictions_{}.csv\".format(current_date)\n", " csv_buffer = df.to_csv(index=False).encode('utf-8')\n", " # Save the CSV file to S3\n", " s3.put_object(Body=csv_buffer, Bucket=bucket_name, Key=filepath)\n", " print(f\"Table saved to S3 bucket: {bucket_name}, with file name: {filepath}\")\n", "\n", "save_dataframe_s3(res)" ] }, { "cell_type": "code", "execution_count": null, "id": "1a7b477f", "metadata": {}, "outputs": [], "source": [] } ], "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.10.12" } }, "nbformat": 4, "nbformat_minor": 5 }