{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Tutorial" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false }, "outputs": [], "source": [ "from Snowflake2 import connect" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Snowflake2 defaults to authentication via enviornment variables:\n", "- SF_USER\n", "- SF_PASS\n", "- SF_ROLE\n", "- SF_ACCOUNT\n", "\n", "...when a connect object is instantiated within a warehouse." ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [], "source": [ "sf = connect(warehouse='SCIENCE')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "we can also look for the same variables from a json file by assigning a file path to the `env` param" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "sf = connect(warehouse='SCIENCE', \n", " env='/users/lyin/chamber_of_secrets.json')" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sf" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The main functionality of this module is running queries (which I sggest you store in a config .py file or read from a .sql file." ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": true }, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT *\n", "FROM PROD.PRODUCTION.DIM_RELEASE\n", "LIMIT 100000\n", "\"\"\"" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note that warehouse and schema need to be included in the tables and views we reference.\n", "\n", "We can run queries using the q method of the sf connection object." ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false }, "outputs": [], "source": [ "resp = sf.q(query)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "the response is a-kin to a http body contianing:\n", "- status code\n", "- a Pandas dataframe\n", "- first tuple fetched\n", "- sfqid of the querry" ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "200" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "resp['code']" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "(843041002394,\n", " 'Legendary Solo',\n", " datetime.datetime(1992, 1, 1, 0, 0),\n", " 11,\n", " 410360,\n", " 'AVM CD - 115',\n", " 'AVM FILM STUDIOS',\n", " datetime.datetime(2005, 1, 1, 0, 0),\n", " datetime.datetime(2014, 2, 21, 12, 26, 13),\n", " None,\n", " 'Y',\n", " None,\n", " None,\n", " None,\n", " datetime.datetime(2016, 11, 4, 20, 32, 57),\n", " datetime.datetime(2014, 2, 21, 19, 7, 25),\n", " 20304,\n", " None,\n", " '843041002394',\n", " None,\n", " 1,\n", " 2972021,\n", " 56934,\n", " None,\n", " 'N',\n", " None,\n", " None,\n", " None,\n", " None,\n", " None,\n", " 'in_content',\n", " None,\n", " None,\n", " None,\n", " 'N',\n", " None,\n", " None,\n", " 'N',\n", " 2972021)" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "resp['first']" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "'e4905dfb-bd26-436e-9ba5-ab5a0e67fc76'" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "resp['sfqid']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pandas dataframes are the standard for analysis and wrangling" ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "collapsed": false }, "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", "
RELEASEIDRELEASENAMERELEASEDATEGENREIDARTISTIDVENDOR_CATALOG_NUMBERIMPRINTSALE_START_DATEDATE_ADDEDORIGINAL_RELEASE_DATEDELETIONSMKT_PRIORITYMANUFACTURER_UPCVERSIONLAST_UPDATEDDATE_CREATEDLABELIDSUBACCOUNTIDDISPLAY_UPCVENDOR_RELEASE_IDENTIFIERPRODUCT_TYPE_IDCATALOGIDIMPRINTIDPRODUCT_SUBTYPE_IDCOMPILATIONPREORDER_DATEPHYSICAL_RELEASE_DATETHEATRICAL_RELEASE_DATEVOD_START_DATEORIGINAL_DIGITAL_RELEASE_DATERELEASE_STATUSCHANNEL_IDITUNES_PREVIEWABLENEW_RELEASESYNC_ONLYESTVODDIGITAL_ONLYPROJECTID
0843041001748Classical Bliss (Nithyasree Mahadevan)1990-01-0111410347AVM CD - 056AVM FILM STUDIOS2005-01-012014-02-21 12:26:07NoneYNoneNoneNone2016-11-04 20:32:572014-02-21 19:07:2520304NaN843041001748None1297194556934NaNNNoneNoneNoneNoneNonein_contentNaNNoneNoneNNoneNoneN2971945.0
1843041002394Legendary Solo1992-01-0111410360AVM CD - 115AVM FILM STUDIOS2005-01-012014-02-21 12:26:13NoneYNoneNoneNone2016-11-04 20:32:572014-02-21 19:07:2520304NaN843041002394None1297202156934NaNNNoneNoneNoneNoneNonein_contentNaNNoneNoneNNoneNoneN2972021.0
2843041002653Manithan & Nallavanukku Nallavan & Paayum Puli1984-01-0127410411AVM CD - 156AVM FILM STUDIOS2005-01-012014-02-21 13:14:00NoneYNoneNoneNone2016-11-04 20:32:572014-02-21 19:07:2520304NaN843041002653None1297286956934NaNNNoneNoneNoneNoneNonein_contentNaNNoneNoneNNoneNoneN2972869.0
3843041086578Punya Varshini2005-01-0111392871MM-060MANORAMA MUSIC2005-01-012013-03-14 00:00:00NoneYNoneNoneNone2016-11-04 20:32:572013-09-13 13:47:33203053491.0843041086578None123972524772NaNN1900-01-01NoneNoneNoneNonein_contentNaNNoneNoneNNoneNoneN2397252.0
4847108060105Ennennum M G - Hits Of M.G.Sreekumar Vol 5 To 82009-01-0111392871847108060105JOHNY SAGARIGA2009-01-012013-03-14 00:00:00NoneYNoneNoneNone2016-11-04 20:32:572013-09-13 13:47:332030511400.0847108060105None1204214442401NaNN1900-01-01NoneNoneNoneNonein_contentNaNNoneNoneNNoneNoneN2042144.0
\n", "
" ], "text/plain": [ " RELEASEID RELEASENAME RELEASEDATE \\\n", "0 843041001748 Classical Bliss (Nithyasree Mahadevan) 1990-01-01 \n", "1 843041002394 Legendary Solo 1992-01-01 \n", "2 843041002653 Manithan & Nallavanukku Nallavan & Paayum Puli 1984-01-01 \n", "3 843041086578 Punya Varshini 2005-01-01 \n", "4 847108060105 Ennennum M G - Hits Of M.G.Sreekumar Vol 5 To 8 2009-01-01 \n", "\n", " GENREID ARTISTID VENDOR_CATALOG_NUMBER IMPRINT SALE_START_DATE \\\n", "0 11 410347 AVM CD - 056 AVM FILM STUDIOS 2005-01-01 \n", "1 11 410360 AVM CD - 115 AVM FILM STUDIOS 2005-01-01 \n", "2 27 410411 AVM CD - 156 AVM FILM STUDIOS 2005-01-01 \n", "3 11 392871 MM-060 MANORAMA MUSIC 2005-01-01 \n", "4 11 392871 847108060105 JOHNY SAGARIGA 2009-01-01 \n", "\n", " DATE_ADDED ORIGINAL_RELEASE_DATE DELETIONS MKT_PRIORITY \\\n", "0 2014-02-21 12:26:07 None Y None \n", "1 2014-02-21 12:26:13 None Y None \n", "2 2014-02-21 13:14:00 None Y None \n", "3 2013-03-14 00:00:00 None Y None \n", "4 2013-03-14 00:00:00 None Y None \n", "\n", " MANUFACTURER_UPC VERSION LAST_UPDATED DATE_CREATED LABELID \\\n", "0 None None 2016-11-04 20:32:57 2014-02-21 19:07:25 20304 \n", "1 None None 2016-11-04 20:32:57 2014-02-21 19:07:25 20304 \n", "2 None None 2016-11-04 20:32:57 2014-02-21 19:07:25 20304 \n", "3 None None 2016-11-04 20:32:57 2013-09-13 13:47:33 20305 \n", "4 None None 2016-11-04 20:32:57 2013-09-13 13:47:33 20305 \n", "\n", " SUBACCOUNTID DISPLAY_UPC VENDOR_RELEASE_IDENTIFIER PRODUCT_TYPE_ID \\\n", "0 NaN 843041001748 None 1 \n", "1 NaN 843041002394 None 1 \n", "2 NaN 843041002653 None 1 \n", "3 3491.0 843041086578 None 1 \n", "4 11400.0 847108060105 None 1 \n", "\n", " CATALOGID IMPRINTID PRODUCT_SUBTYPE_ID COMPILATION PREORDER_DATE \\\n", "0 2971945 56934 NaN N None \n", "1 2972021 56934 NaN N None \n", "2 2972869 56934 NaN N None \n", "3 2397252 4772 NaN N 1900-01-01 \n", "4 2042144 42401 NaN N 1900-01-01 \n", "\n", " PHYSICAL_RELEASE_DATE THEATRICAL_RELEASE_DATE VOD_START_DATE \\\n", "0 None None None \n", "1 None None None \n", "2 None None None \n", "3 None None None \n", "4 None None None \n", "\n", " ORIGINAL_DIGITAL_RELEASE_DATE RELEASE_STATUS CHANNEL_ID ITUNES_PREVIEWABLE \\\n", "0 None in_content NaN None \n", "1 None in_content NaN None \n", "2 None in_content NaN None \n", "3 None in_content NaN None \n", "4 None in_content NaN None \n", "\n", " NEW_RELEASE SYNC_ONLY EST VOD DIGITAL_ONLY PROJECTID \n", "0 None N None None N 2971945.0 \n", "1 None N None None N 2972021.0 \n", "2 None N None None N 2972869.0 \n", "3 None N None None N 2397252.0 \n", "4 None N None None N 2042144.0 " ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "resp['df'].head()" ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "RELEASEID int64\n", "RELEASENAME object\n", "RELEASEDATE datetime64[ns]\n", "GENREID int64\n", "ARTISTID int64\n", "VENDOR_CATALOG_NUMBER object\n", "IMPRINT object\n", "SALE_START_DATE datetime64[ns]\n", "DATE_ADDED datetime64[ns]\n", "ORIGINAL_RELEASE_DATE object\n", "DELETIONS object\n", "MKT_PRIORITY object\n", "MANUFACTURER_UPC object\n", "VERSION object\n", "LAST_UPDATED datetime64[ns]\n", "DATE_CREATED datetime64[ns]\n", "LABELID int64\n", "SUBACCOUNTID float64\n", "DISPLAY_UPC object\n", "VENDOR_RELEASE_IDENTIFIER object\n", "PRODUCT_TYPE_ID int64\n", "CATALOGID int64\n", "IMPRINTID int64\n", "PRODUCT_SUBTYPE_ID float64\n", "COMPILATION object\n", "PREORDER_DATE object\n", "PHYSICAL_RELEASE_DATE object\n", "THEATRICAL_RELEASE_DATE object\n", "VOD_START_DATE object\n", "ORIGINAL_DIGITAL_RELEASE_DATE object\n", "RELEASE_STATUS object\n", "CHANNEL_ID float64\n", "ITUNES_PREVIEWABLE object\n", "NEW_RELEASE object\n", "SYNC_ONLY object\n", "EST object\n", "VOD object\n", "DIGITAL_ONLY object\n", "PROJECTID float64\n", "dtype: object" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "resp['df'].dtypes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "we can also return the dataframe directly" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false }, "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", "
RELEASEIDRELEASENAMERELEASEDATEGENREIDARTISTIDVENDOR_CATALOG_NUMBERIMPRINTSALE_START_DATEDATE_ADDEDORIGINAL_RELEASE_DATEDELETIONSMKT_PRIORITYMANUFACTURER_UPCVERSIONLAST_UPDATEDDATE_CREATEDLABELIDSUBACCOUNTIDDISPLAY_UPCVENDOR_RELEASE_IDENTIFIERPRODUCT_TYPE_IDCATALOGIDIMPRINTIDPRODUCT_SUBTYPE_IDCOMPILATIONPREORDER_DATEPHYSICAL_RELEASE_DATETHEATRICAL_RELEASE_DATEVOD_START_DATEORIGINAL_DIGITAL_RELEASE_DATERELEASE_STATUSCHANNEL_IDITUNES_PREVIEWABLENEW_RELEASESYNC_ONLYESTVODDIGITAL_ONLYPROJECTID
99995851431001896AO2010-09-201420551MAR070MARRIAGE RECORDS2010-09-202013-03-14 00:00:00NoneNNoneNoneNone2016-11-04 20:32:572013-09-13 13:47:3321436NaN851431001896None1275034247842NaNN1900-01-01NoneNoneNoneNonein_contentNaNNoneNoneNNoneNoneN2750342.0
999964607087288046После2007-09-187575348MLT000065MEGALINER RECORDS2007-09-182013-03-14 00:00:00NoneYNoneNoneNone2016-11-04 20:32:572013-09-13 13:47:3321437NaN4607087288046None1243066438255NaNNNoneNoneNoneNoneNonein_contentNaNyesNoneNNoneNoneN2430664.0
999974607087289326Оптимизм Излучая: Состояние Трубадур2013-07-0811603111MLT000097MEGALINER RECORDS2013-07-082013-07-15 17:25:04NoneNNoneNoneNone2016-11-04 20:32:572013-09-13 13:47:3321437NaN4607087289326None1281743138255NaNNNoneNoneNoneNoneNonein_contentNaNyesNewNNoneNoneN2817431.0
99998858886001259Reachin' (Ian Friday & B.O.P. Remixes)2008-10-072450116JSL3521JELLYBEAN SOUL2008-10-282013-03-14 00:00:002008-10-07 00:00:00NNoneNoneNone2016-11-04 20:32:572013-09-13 13:47:3321444NaN858886001259None1218629317788NaNN1900-01-01NoneNoneNoneNonein_contentNaNNoneNoneNNoneNoneN2186293.0
99999713117266718Love Will Save the Day2004-04-272487401JEL2667JELLYBEAN SOUL2008-06-242013-03-14 00:00:002004-04-27 00:00:00NNoneNoneNone2016-11-04 20:32:572013-09-13 13:47:3321444NaN713117266718None1223083017788NaNN1900-01-01NoneNoneNoneNonein_contentNaNNoneNoneNNoneNoneN2230830.0
\n", "
" ], "text/plain": [ " RELEASEID RELEASENAME RELEASEDATE \\\n", "99995 851431001896 AO 2010-09-20 \n", "99996 4607087288046 После 2007-09-18 \n", "99997 4607087289326 Оптимизм Излучая: Состояние Трубадур 2013-07-08 \n", "99998 858886001259 Reachin' (Ian Friday & B.O.P. Remixes) 2008-10-07 \n", "99999 713117266718 Love Will Save the Day 2004-04-27 \n", "\n", " GENREID ARTISTID VENDOR_CATALOG_NUMBER IMPRINT \\\n", "99995 1 420551 MAR070 MARRIAGE RECORDS \n", "99996 7 575348 MLT000065 MEGALINER RECORDS \n", "99997 11 603111 MLT000097 MEGALINER RECORDS \n", "99998 2 450116 JSL3521 JELLYBEAN SOUL \n", "99999 2 487401 JEL2667 JELLYBEAN SOUL \n", "\n", " SALE_START_DATE DATE_ADDED ORIGINAL_RELEASE_DATE DELETIONS \\\n", "99995 2010-09-20 2013-03-14 00:00:00 None N \n", "99996 2007-09-18 2013-03-14 00:00:00 None Y \n", "99997 2013-07-08 2013-07-15 17:25:04 None N \n", "99998 2008-10-28 2013-03-14 00:00:00 2008-10-07 00:00:00 N \n", "99999 2008-06-24 2013-03-14 00:00:00 2004-04-27 00:00:00 N \n", "\n", " MKT_PRIORITY MANUFACTURER_UPC VERSION LAST_UPDATED \\\n", "99995 None None None 2016-11-04 20:32:57 \n", "99996 None None None 2016-11-04 20:32:57 \n", "99997 None None None 2016-11-04 20:32:57 \n", "99998 None None None 2016-11-04 20:32:57 \n", "99999 None None None 2016-11-04 20:32:57 \n", "\n", " DATE_CREATED LABELID SUBACCOUNTID DISPLAY_UPC \\\n", "99995 2013-09-13 13:47:33 21436 NaN 851431001896 \n", "99996 2013-09-13 13:47:33 21437 NaN 4607087288046 \n", "99997 2013-09-13 13:47:33 21437 NaN 4607087289326 \n", "99998 2013-09-13 13:47:33 21444 NaN 858886001259 \n", "99999 2013-09-13 13:47:33 21444 NaN 713117266718 \n", "\n", " VENDOR_RELEASE_IDENTIFIER PRODUCT_TYPE_ID CATALOGID IMPRINTID \\\n", "99995 None 1 2750342 47842 \n", "99996 None 1 2430664 38255 \n", "99997 None 1 2817431 38255 \n", "99998 None 1 2186293 17788 \n", "99999 None 1 2230830 17788 \n", "\n", " PRODUCT_SUBTYPE_ID COMPILATION PREORDER_DATE PHYSICAL_RELEASE_DATE \\\n", "99995 NaN N 1900-01-01 None \n", "99996 NaN N None None \n", "99997 NaN N None None \n", "99998 NaN N 1900-01-01 None \n", "99999 NaN N 1900-01-01 None \n", "\n", " THEATRICAL_RELEASE_DATE VOD_START_DATE ORIGINAL_DIGITAL_RELEASE_DATE \\\n", "99995 None None None \n", "99996 None None None \n", "99997 None None None \n", "99998 None None None \n", "99999 None None None \n", "\n", " RELEASE_STATUS CHANNEL_ID ITUNES_PREVIEWABLE NEW_RELEASE SYNC_ONLY \\\n", "99995 in_content NaN None None N \n", "99996 in_content NaN yes None N \n", "99997 in_content NaN yes New N \n", "99998 in_content NaN None None N \n", "99999 in_content NaN None None N \n", "\n", " EST VOD DIGITAL_ONLY PROJECTID \n", "99995 None None N 2750342.0 \n", "99996 None None N 2430664.0 \n", "99997 None None N 2817431.0 \n", "99998 None None N 2186293.0 \n", "99999 None None N 2230830.0 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = sf.q(query, resp='df')\n", "df.tail()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "we can also force a dtype for all columns, which is nice when working with UPCs with leading zeros." ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "RELEASEID object\n", "RELEASENAME object\n", "RELEASEDATE object\n", "GENREID object\n", "ARTISTID object\n", "VENDOR_CATALOG_NUMBER object\n", "IMPRINT object\n", "SALE_START_DATE object\n", "DATE_ADDED object\n", "ORIGINAL_RELEASE_DATE object\n", "DELETIONS object\n", "MKT_PRIORITY object\n", "MANUFACTURER_UPC object\n", "VERSION object\n", "LAST_UPDATED object\n", "DATE_CREATED object\n", "LABELID object\n", "SUBACCOUNTID object\n", "DISPLAY_UPC object\n", "VENDOR_RELEASE_IDENTIFIER object\n", "PRODUCT_TYPE_ID object\n", "CATALOGID object\n", "IMPRINTID object\n", "PRODUCT_SUBTYPE_ID object\n", "COMPILATION object\n", "PREORDER_DATE object\n", "PHYSICAL_RELEASE_DATE object\n", "THEATRICAL_RELEASE_DATE object\n", "VOD_START_DATE object\n", "ORIGINAL_DIGITAL_RELEASE_DATE object\n", "RELEASE_STATUS object\n", "CHANNEL_ID object\n", "ITUNES_PREVIEWABLE object\n", "NEW_RELEASE object\n", "SYNC_ONLY object\n", "EST object\n", "VOD object\n", "DIGITAL_ONLY object\n", "PROJECTID object\n", "dtype: object" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = sf.q(query, resp='df', dtype='O')\n", "df.dtypes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "When working with large datasets that don't fit in memory we can iterate throuh a query chunkwise" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "collapsed": false }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/Users/lyin/anaconda/lib/python3.5/site-packages/ipykernel/__main__.py:7: UserWarning: This pattern has match groups. To actually get the groups, use str.extract.\n" ] } ], "source": [ "i = 0\n", "\n", "for df in sf.q(query, resp='iterator', chunksize=1000):\n", " # let's filter by this artist ID\n", " # and get a count of UPCs containing the string 'Jesus'\n", " danks = df[(df['ARTISTID'] == 174459) &\n", " (df['RELEASENAME'].str.contains('(sister)|(brother)', case=False))]\n", " \n", " # append to a gzipped tsv file!\n", " if i == 0:\n", " danks.to_csv('release_search.tsv.gz', \n", " index=False, header=True,\n", " compression='gzip',\n", " sep='\\t')\n", " else:\n", " danks.to_csv('release_search.tsv.gz', \n", " index=False, header=False, \n", " compression='gzip',\n", " sep='\\t', mode='a')\n", " i += 1" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Using Pandas we can view the 5 first results." ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false }, "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", "
RELEASEIDRELEASENAMERELEASEDATEGENREIDARTISTIDVENDOR_CATALOG_NUMBERIMPRINTSALE_START_DATEDATE_ADDEDORIGINAL_RELEASE_DATEDELETIONSMKT_PRIORITYMANUFACTURER_UPCVERSIONLAST_UPDATEDDATE_CREATEDLABELIDSUBACCOUNTIDDISPLAY_UPCVENDOR_RELEASE_IDENTIFIERPRODUCT_TYPE_IDCATALOGIDIMPRINTIDPRODUCT_SUBTYPE_IDCOMPILATIONPREORDER_DATEPHYSICAL_RELEASE_DATETHEATRICAL_RELEASE_DATEVOD_START_DATEORIGINAL_DIGITAL_RELEASE_DATERELEASE_STATUSCHANNEL_IDITUNES_PREVIEWABLENEW_RELEASESYNC_ONLYESTVODDIGITAL_ONLYPROJECTID
0886788377905(You're My) Soul and Inspiration [In the Style...2011-12-25 00:00:00717445980791STINGRAY MUSIC2011-12-25 00:00:002011-12-19 10:11:00NaNNNaN882387807911x-iTunes2016-11-04 20:32:572013-09-13 13:47:338062NaN886788377905NaN1281623446896NaNN2011-12-19NaNNaNNaNNaNin_contentNaNNaNNaNNNaNNaNN2816234
1886788384750Don't Sit Under the Apple Tree (With Anyone El...2011-12-25 00:00:00717445981428STINGRAY MUSIC2011-12-25 00:00:002011-12-19 10:11:17NaNNNaN882387814285x-iTunes2016-11-04 20:32:572013-09-13 13:47:338062NaN886788384750NaN1191102646896NaNN2011-12-19NaNNaNNaNNaNin_contentNaNNaNNaNNNaNNaNN1911026
2886788383142Alexander's Ragtime Band (In the Style of the ...2011-12-25 00:00:00717445981268STINGRAY MUSIC2011-12-25 00:00:002011-12-19 10:11:12NaNNNaN882387812687x-iTunes2016-11-04 20:32:572013-09-13 13:47:338062NaN886788383142NaN1235572346896NaNN2011-12-19NaNNaNNaNNaNin_contentNaNNaNNaNNNaNNaNN2355723
3887396570764Sister2012-09-18 00:00:00117445983949STINGRAY MUSIC2012-09-18 00:00:002012-11-13 16:41:58NaNNNaN882387839493NaN2016-11-04 20:32:572013-09-13 13:47:338062NaN887396570764NaN1260829046896NaNNNaNNaNNaNNaNNaNin_contentNaNyesNaNNNaNNaNN2608290
4886788383166Lullaby of Broadway (In the Style of the Andre...2011-12-25 00:00:00717445981270STINGRAY MUSIC2011-12-25 00:00:002011-12-19 10:11:12NaNNNaN882387812700x-iTunes2016-11-04 20:32:572013-09-13 13:47:338062NaN886788383166NaN1203727746896NaNN2011-12-19NaNNaNNaNNaNin_contentNaNNaNNaNNNaNNaNN2037277
\n", "
" ], "text/plain": [ " RELEASEID RELEASENAME \\\n", "0 886788377905 (You're My) Soul and Inspiration [In the Style... \n", "1 886788384750 Don't Sit Under the Apple Tree (With Anyone El... \n", "2 886788383142 Alexander's Ragtime Band (In the Style of the ... \n", "3 887396570764 Sister \n", "4 886788383166 Lullaby of Broadway (In the Style of the Andre... \n", "\n", " RELEASEDATE GENREID ARTISTID VENDOR_CATALOG_NUMBER \\\n", "0 2011-12-25 00:00:00 7 174459 80791 \n", "1 2011-12-25 00:00:00 7 174459 81428 \n", "2 2011-12-25 00:00:00 7 174459 81268 \n", "3 2012-09-18 00:00:00 1 174459 83949 \n", "4 2011-12-25 00:00:00 7 174459 81270 \n", "\n", " IMPRINT SALE_START_DATE DATE_ADDED \\\n", "0 STINGRAY MUSIC 2011-12-25 00:00:00 2011-12-19 10:11:00 \n", "1 STINGRAY MUSIC 2011-12-25 00:00:00 2011-12-19 10:11:17 \n", "2 STINGRAY MUSIC 2011-12-25 00:00:00 2011-12-19 10:11:12 \n", "3 STINGRAY MUSIC 2012-09-18 00:00:00 2012-11-13 16:41:58 \n", "4 STINGRAY MUSIC 2011-12-25 00:00:00 2011-12-19 10:11:12 \n", "\n", " ORIGINAL_RELEASE_DATE DELETIONS MKT_PRIORITY MANUFACTURER_UPC VERSION \\\n", "0 NaN N NaN 882387807911 x-iTunes \n", "1 NaN N NaN 882387814285 x-iTunes \n", "2 NaN N NaN 882387812687 x-iTunes \n", "3 NaN N NaN 882387839493 NaN \n", "4 NaN N NaN 882387812700 x-iTunes \n", "\n", " LAST_UPDATED DATE_CREATED LABELID SUBACCOUNTID \\\n", "0 2016-11-04 20:32:57 2013-09-13 13:47:33 8062 NaN \n", "1 2016-11-04 20:32:57 2013-09-13 13:47:33 8062 NaN \n", "2 2016-11-04 20:32:57 2013-09-13 13:47:33 8062 NaN \n", "3 2016-11-04 20:32:57 2013-09-13 13:47:33 8062 NaN \n", "4 2016-11-04 20:32:57 2013-09-13 13:47:33 8062 NaN \n", "\n", " DISPLAY_UPC VENDOR_RELEASE_IDENTIFIER PRODUCT_TYPE_ID CATALOGID \\\n", "0 886788377905 NaN 1 2816234 \n", "1 886788384750 NaN 1 1911026 \n", "2 886788383142 NaN 1 2355723 \n", "3 887396570764 NaN 1 2608290 \n", "4 886788383166 NaN 1 2037277 \n", "\n", " IMPRINTID PRODUCT_SUBTYPE_ID COMPILATION PREORDER_DATE \\\n", "0 46896 NaN N 2011-12-19 \n", "1 46896 NaN N 2011-12-19 \n", "2 46896 NaN N 2011-12-19 \n", "3 46896 NaN N NaN \n", "4 46896 NaN N 2011-12-19 \n", "\n", " PHYSICAL_RELEASE_DATE THEATRICAL_RELEASE_DATE VOD_START_DATE \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " ORIGINAL_DIGITAL_RELEASE_DATE RELEASE_STATUS CHANNEL_ID \\\n", "0 NaN in_content NaN \n", "1 NaN in_content NaN \n", "2 NaN in_content NaN \n", "3 NaN in_content NaN \n", "4 NaN in_content NaN \n", "\n", " ITUNES_PREVIEWABLE NEW_RELEASE SYNC_ONLY EST VOD DIGITAL_ONLY PROJECTID \n", "0 NaN NaN N NaN NaN N 2816234 \n", "1 NaN NaN N NaN NaN N 1911026 \n", "2 NaN NaN N NaN NaN N 2355723 \n", "3 yes NaN N NaN NaN N 2608290 \n", "4 NaN NaN N NaN NaN N 2037277 " ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "\n", "pd.read_csv('release_search.tsv.gz', sep='\\t', compression='gzip', nrows=5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can use this file to create a table in Snowflake." ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'first': 'Table SF2_EXAMPLE successfully created.',\n", " 'sfqid': 'c398d398-b5ed-4fe1-b005-be4c5e057331'}" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sf.create_table(ref='release_search.tsv.gz',\n", " tbl='LY_DEV.TEST.SF2_EXAMPLE',\n", " fmt='LY_DEV.TEST.TSV_HEAD')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also use dataframes and s3 files." ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'first': 'Table SF2_EXAMPLE successfully created.',\n", " 'sfqid': '847e89e2-8eda-49ed-a22e-e7ce326000ce'}" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sf.create_table(ref=df,\n", " tbl='LY_DEV.TEST.SF2_EXAMPLE',\n", " fmt='LY_DEV.TEST.TSV_HEAD')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Read all about the s3 module here" ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "\"'release_search.tsv.gz' loaded to 's3://dev-rsaporta/DataAnalyticsDept/one_offs/demo/release_search.tsv.gz'\"" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import s3\n", "\n", "s3.disk_2_s3(file='release_search.tsv.gz',\n", " s3_path='s3://dev-rsaporta/DataAnalyticsDept/'\n", " 'one_offs/demo/release_search.tsv.gz')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "we can specify colum dtypes using a dictionary and the dtype param" ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'first': 'Table SF2_EXAMPLE successfully created.',\n", " 'sfqid': 'e2baa12d-27e2-45b0-bdf2-77e2f1c59518'}" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sf.create_table(ref='s3://dev-rsaporta/DataAnalyticsDept/'\n", " 'one_offs/demo/release_search.tsv.gz',\n", " tbl='LY_DEV.TEST.SF2_EXAMPLE',\n", " fmt='LY_DEV.TEST.TSV_HEAD',\n", " dtype={'UPC': 'O'})" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "After we've created the table, we can populate it from s3." ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "{'code': 200,\n", " 'first': 's3://dev-rsaporta/DataAnalyticsDept/one_offs/demo/release_search.tsv.gz',\n", " 'sfqid': '0466b369-0260-4f95-8a6e-5051bdcdd4c4'}" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sf.s3_2_table(s3_path='s3://dev-rsaporta/DataAnalyticsDept/'\n", " 'one_offs/demo/release_search.tsv.gz',\n", " tbl='LY_DEV.TEST.SF2_EXAMPLE',\n", " fmt='LY_DEV.TEST.TSV_HEAD')" ] } ], "metadata": { "kernelspec": { "display_name": "root_py3", "language": "python", "name": "root_py3" }, "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.5.1" } }, "nbformat": 4, "nbformat_minor": 0 }