{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import snowflake.connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "conn = snowflake.connector.connect(\n",
    "    account=\"orchard\",\n",
    "    user=\"efedorov\",\n",
    "    password=\"<your password>\",\n",
    ")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [],
   "source": [
    "with open('query.sql') as f:\n",
    "    sql_query = f.read()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [],
   "source": [
    "cur = conn.cursor()\n",
    "df = cur.execute(sql_query).fetch_pandas_all()\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "       ROW_ID          INGESTION_TIME DOWNLOAD_DATE REPORTING_START_DATE  \\\n0  7061422721 2021-07-23 07:02:04.825    2021-02-02           2021-02-02   \n1  7061422722 2021-07-23 07:02:04.825    2021-02-02           2021-02-02   \n2  7061422723 2021-07-23 07:02:04.825    2021-02-02           2021-02-02   \n3  7061422724 2021-07-23 07:02:04.825    2021-02-02           2021-02-02   \n4  7061422725 2021-07-23 07:02:04.825    2021-02-02           2021-02-02   \n\n  REPORTING_END_DATE COUNTRY                       CUSTOMER_ID   TRACK_ID  \\\n0         2021-02-02      US  3242a25fe82489093fe32e051fbddc01  855001783   \n1         2021-02-02      GB  8984943409879cf4ce41166950c771cb  814280749   \n2         2021-02-02      PR  0c80fc011426db69f696962c7a8bb6db  920168872   \n3         2021-02-02      US  2e9812b877636897cf5ba92c08dbe05d  829444150   \n4         2021-02-02      US  71f1790e5178619a0c7a68cd5349952e  343503401   \n\n           ISRC                       TIME_STAMP  ... CLIENT_APPLICATION  \\\n0  QM6P42054010 2021-02-02 16:47:42.233000+00:00  ...     SoundCloud iOS   \n1  USQX92002451 2021-02-02 02:44:11.506000+00:00  ...     SoundCloud iOS   \n2  QMFME2004132 2021-02-02 03:14:17.577000+00:00  ...     SoundCloud iOS   \n3  QZ57A2005884 2021-02-02 07:41:50.151000+00:00  ...     SoundCloud iOS   \n4  QZ57A1701108 2021-02-02 21:23:14.332000+00:00  ...     SoundCloud iOS   \n\n                                 PLAYLIST_TYPE  \\\n0                                         None   \n1                                         None   \n2                                         None   \n3  3rd_party_playlist_with_rightsholder_tracks   \n4                                         None   \n\n                        PLAYLIST_ID POSITION_IN_PLAYLIST TRACK_TO_PLAYLIST  \\\n0                              None                    0                 0   \n1                              None                    0                 0   \n2                              None                    0                 0   \n3  f737b2785eb361cd6bdf716832cbd76d                   13          20200529   \n4                              None                    0                 0   \n\n   PLAY_LENGTH  TRACK_FAVOURITED  TRACK_REPOSTED_SHARED  TRACK_MESSAGE_POSTED  \\\n0       135445                 0                      0                     0   \n1       184186                 0                      0                     0   \n2       410280                 0                      0                     0   \n3       249842                 0                      0                     0   \n4       219033                 0                      0                     0   \n\n   TRACK_TO_PLAYLIST_ADDED  \n0                        0  \n1                        0  \n2                        0  \n3                        0  \n4                        0  \n\n[5 rows x 21 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>ROW_ID</th>\n      <th>INGESTION_TIME</th>\n      <th>DOWNLOAD_DATE</th>\n      <th>REPORTING_START_DATE</th>\n      <th>REPORTING_END_DATE</th>\n      <th>COUNTRY</th>\n      <th>CUSTOMER_ID</th>\n      <th>TRACK_ID</th>\n      <th>ISRC</th>\n      <th>TIME_STAMP</th>\n      <th>...</th>\n      <th>CLIENT_APPLICATION</th>\n      <th>PLAYLIST_TYPE</th>\n      <th>PLAYLIST_ID</th>\n      <th>POSITION_IN_PLAYLIST</th>\n      <th>TRACK_TO_PLAYLIST</th>\n      <th>PLAY_LENGTH</th>\n      <th>TRACK_FAVOURITED</th>\n      <th>TRACK_REPOSTED_SHARED</th>\n      <th>TRACK_MESSAGE_POSTED</th>\n      <th>TRACK_TO_PLAYLIST_ADDED</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>7061422721</td>\n      <td>2021-07-23 07:02:04.825</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>US</td>\n      <td>3242a25fe82489093fe32e051fbddc01</td>\n      <td>855001783</td>\n      <td>QM6P42054010</td>\n      <td>2021-02-02 16:47:42.233000+00:00</td>\n      <td>...</td>\n      <td>SoundCloud iOS</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>135445</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>7061422722</td>\n      <td>2021-07-23 07:02:04.825</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>GB</td>\n      <td>8984943409879cf4ce41166950c771cb</td>\n      <td>814280749</td>\n      <td>USQX92002451</td>\n      <td>2021-02-02 02:44:11.506000+00:00</td>\n      <td>...</td>\n      <td>SoundCloud iOS</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>184186</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>7061422723</td>\n      <td>2021-07-23 07:02:04.825</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>PR</td>\n      <td>0c80fc011426db69f696962c7a8bb6db</td>\n      <td>920168872</td>\n      <td>QMFME2004132</td>\n      <td>2021-02-02 03:14:17.577000+00:00</td>\n      <td>...</td>\n      <td>SoundCloud iOS</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>410280</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>7061422724</td>\n      <td>2021-07-23 07:02:04.825</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>US</td>\n      <td>2e9812b877636897cf5ba92c08dbe05d</td>\n      <td>829444150</td>\n      <td>QZ57A2005884</td>\n      <td>2021-02-02 07:41:50.151000+00:00</td>\n      <td>...</td>\n      <td>SoundCloud iOS</td>\n      <td>3rd_party_playlist_with_rightsholder_tracks</td>\n      <td>f737b2785eb361cd6bdf716832cbd76d</td>\n      <td>13</td>\n      <td>20200529</td>\n      <td>249842</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>7061422725</td>\n      <td>2021-07-23 07:02:04.825</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>2021-02-02</td>\n      <td>US</td>\n      <td>71f1790e5178619a0c7a68cd5349952e</td>\n      <td>343503401</td>\n      <td>QZ57A1701108</td>\n      <td>2021-02-02 21:23:14.332000+00:00</td>\n      <td>...</td>\n      <td>SoundCloud iOS</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>219033</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 21 columns</p>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}