{"cells":[{"metadata":{"_cell_guid":"be9fbbf9-c097-4ba9-8f62-1a117b372e12","_uuid":"627e05b0f499c71aafe1d4920eb76d2af58345f0"},"cell_type":"markdown","source":"# Super fast feature generation with Google Cloud Big Query\n\nGenerating new features on big datasets can be very challenging. If you don't have a huge amount of RAM, creating features with pandas in a traditional way can be really difficult.\n\nIf you want to work with pandas, there is an excellent notebook which explains how to optimize RAM usage: [How to Work with BIG Datasets on Kaggle Kernels (16G RAM)](https://www.kaggle.com/yuliagm/how-to-work-with-big-datasets-on-16g-ram-dask)\n\nBut even if you have access to a lot of RAM, pandas can be quite slow.\n\nIn this notebook, we will use Google Cloud Big Query service to **generate a csv with 19 new features in less than 4 minutes on the whole AdTracking competition training dataset!** With current google pricing policy, the free tier allows to do this without paying anything."},{"metadata":{"_cell_guid":"119a39c5-dfcc-4872-9aaa-ce0c6965f87a","_uuid":"8f544aa65fcc0c84af75d2e4ae1cc5dd2629e931"},"cell_type":"markdown","source":"## Let's go! Upload our csv\nI will not write a full tutorial on how to import a csv in Big Query. Google documentation is clear. In brief, you have to:\n\n* Upload the csv in a bucket on Google Cloud Storage\n* Go to Big Query and create a table by importing from Google Cloud Storage\n\n**Important: On my computers, Big Query website does not work with Chrome of Firefox. It resulted in \"Network Unreachable\" error. I had to use Internet Explorer!** Google, please fix this bug..."},{"metadata":{"_cell_guid":"d7ef89f7-cae9-413e-9eb1-06700e7dc5bd","_uuid":"7ddb9668a843069c97a06f5732d9c9d8d5e12b28"},"cell_type":"markdown","source":"## Create an index column\n\nUnlike pandas, Big Query does not have an index column. For the test dataset, we could just use 'click_id', but for the training dataset, we have to create one, as we will need it further.\n\nWe could do it with pandas before uploading the csv, but it is really easy to do it directly in Big Query.\n\nClick on COMPOSE QUERY, and copy/paste the following standardSQL request.\n\n```sql\n#standardSQL\nSELECT ROW_NUMBER() OVER(ORDER BY click_time) as index, *\nFROM `my_dataset.my_table`\nORDER BY index\n```\nBefore clicking on RUN QUERY:\n\n* replace my_dataset and my_table by the names of your dataset and your table (keep the ` chars around)\n* click on SHOW OPTIONS and specify a name for a destination table. This will save the result of the query in a new table.\n* in the options, validate the ALLOW LARGE RESULTS checkbox."},{"metadata":{"_cell_guid":"52c2c9ea-e8f4-4c35-aea9-f63d14eac8b2","_uuid":"7784ece76d8a062efe9740f005d633a28b500cf9"},"cell_type":"markdown","source":"## Generate our features\n\nNow we have a table with an index, we can generate our new features columns. We are going to generate different types of features:\n\n* counts on groupby\n* cumulative counts on groupby\n* last click time and next click time\n\nThe groupbys should be applied to any field from the dataset, but also to HOUR extracted from click_time.\n\nAs it would be fastidious to write this in SQL, I prepared a python script that will generate the standardSQL query for us."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","collapsed":true,"trusted":false},"cell_type":"code","source":"# Defines the new features\n\nqueries_config = [    \n    #########################\n    # COUNT\n    #########################\n    {\n        'type': 'count',\n        'groupby': ['ip', 'HOUR']   # the HOUR keyword is processed for truncating HOUR from click_time timestamp\n    },\n    {\n        'type': 'count',\n        'groupby': ['ip', 'app']\n    },\n    {\n        'type': 'count',\n        'groupby': ['ip', 'app', 'os']\n    },    \n    \n    ##########################\n    # COUNT UNIQUE\n    ##########################\n    {\n        'type': 'countunique',\n        'groupby': ['ip'],\n        'unique': 'channel'\n    },\n    {\n        'type': 'countunique',\n        'groupby': ['ip', 'device', 'os'],\n        'unique': 'app'\n    }, \n    {\n        'type': 'countunique',\n        'groupby': ['ip'],\n        'unique': 'app'\n    }, \n    {\n        'type': 'countunique',\n        'groupby': ['ip', 'app'],\n        'unique': 'os'\n    }, \n    {\n        'type': 'countunique',\n        'groupby': ['ip'],\n        'unique': 'device'\n    }, \n    {\n        'type': 'countunique',\n        'groupby': ['app'],\n        'unique': 'channel'\n    },   \n    \n    #######################\n    # CUMULATIVE COUNT\n    #######################  \n    {\n        'type': 'cumcount',\n        'groupby': ['ip'],\n    },   \n    {\n        'type': 'cumcount',\n        'groupby': ['ip', 'device', 'os'],\n    },   \n    \n    #######################\n    # NEXT CLICK\n    #######################    \n    {\n        'type': 'last_click',\n        'groupby': ['ip', 'app', 'device', 'os', 'channel'],\n        'order': 'DESC'       # ASC for last_click, DESC for next_click\n    },\n    {\n        'type': 'last_click',\n        'groupby': ['ip', 'os', 'device'],\n        'order': 'DESC'       # ASC for last_click, DESC for next_click\n    },\n    {\n        'type': 'last_click',\n        'groupby': ['ip', 'os', 'device', 'app'],\n        'order': 'DESC'       # ASC for last_click, DESC for next_click\n    },\n    {\n        'type': 'last_click',\n        'groupby': ['ip', 'channel'],\n        'order': 'DESC'       # ASC for last_click, DESC for next_click\n    }, \n    #######################\n    # LAST CLICK    \n    #######################    \n    {\n        'type': 'last_click',\n        'groupby': ['ip', 'app', 'device', 'os', 'channel'],\n        'order': 'ASC'       # ASC for last_click, DESC for next_click\n    },\n    {\n        'type': 'last_click',\n        'groupby': ['ip', 'os', 'device'],\n        'order': 'ASC'       # ASC for last_click, DESC for next_click\n    },\n    {\n        'type': 'last_click',\n        'groupby': ['ip', 'os', 'device', 'app'],\n        'order': 'ASC'       # ASC for last_click, DESC for next_click\n    },\n    {\n        'type': 'last_click',\n        'groupby': ['ip', 'channel'],\n        'order': 'ASC'       # ASC for last_click, DESC for next_click\n    },    \n\n    \n]","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"5889cd6f-bba1-48c5-96b8-1ac2b0fadc43","_uuid":"72b57d64060fd6327c61be7c5a02d32e40130d34","collapsed":true,"trusted":false},"cell_type":"code","source":"def create_query(main_table_name, train=False):\n\n    query = \"#standardSQL \\nWITH \"\n    #####################\n    # Create WITH section\n    #####################\n    with_sections = []\n    field_names = []\n    temp_table_names = []\n    where_clauses = []\n\n    for c in queries_config:\n        section = \"\"\n        # Create field_name and table_name\n        if c['type'] == 'countunique':\n            field_name = c['type'] + \"_\" + c['unique'] + \"_\" + \"by\" + \"_\" + \"_\".join(c['groupby'])\n        else:\n            field_name = c['type'] + \"_\" + \"by\" + \"_\" + \"_\".join(c['groupby'])\n        if 'order' in c:\n            field_name += \"_\" + c['order']\n        temp_table_name = field_name + \"_table\"\n        field_names.append(field_name)\n        temp_table_names.append(temp_table_name)\n        section += temp_table_name + \" AS (\\n\"\n        # SELECT\n        section += \"  SELECT \"\n        # Insert function to select hours from timestamp when needed\n        processed_groupby = [gb if gb != \"HOUR\" else \"TIMESTAMP_TRUNC(click_time, HOUR, 'UTC') as HOUR\" for gb in c['groupby']]    \n        if   c['type'] == 'count':\n            section += \", \".join(processed_groupby) + \", \"\n            section += \"COUNT(*) \"\n        elif c['type'] == 'countunique':\n            section += \", \".join(processed_groupby) + \", \"\n            section += \"COUNT(DISTINCT \" + c['unique'] + \") \"\n        elif c['type'] == 'cumcount':\n            section += \"index, ROW_NUMBER() OVER (PARTITION BY \" + \", \".join(c['groupby']) + \" ORDER BY click_time) \"\n        elif c['type'] == 'last_click':\n            section += \"index, TIMESTAMP_DIFF(click_time, LAG(click_time) OVER (PARTITION BY \" + \", \".join(c['groupby']) + \" ORDER BY click_time \" + c['order'] + \" ), SECOND)\\n    \"\n        section += \"as \" + field_name + \"\\n\"\n        # FROM\n        section += \"  FROM \" + main_table_name + \"\\n\"\n        # GROUP BY\n        if c['type'] == 'count' or c['type'] == 'countunique' :\n            section += \"  GROUP BY \" + \", \".join(c['groupby']) + \"\\n\"\n            where_clause = \" AND \".join([main_table_name + \".\" + gb + \" = \" + temp_table_name + \".\" + gb for gb in c['groupby'] ])\n            # Process HOUR\n            where_clause = where_clause.replace(main_table_name + \".HOUR\", \"TIMESTAMP_TRUNC(\" + main_table_name  +\".click_time, HOUR, 'UTC')\")\n        else:\n            where_clause = main_table_name + \".index = \" + temp_table_name + \".index\"\n        section += \")\"\n        # Append to with_sections\n        with_sections.append(section)\n        where_clauses.append(where_clause)\n\n    query += \", \\n\".join(with_sections) + \"\\n\\n\"\n\n    #######################\n    # Create SELECT section\n    #######################\n    query += \"SELECT\\n  \"\n    if not train:\n        query += main_table_name + \".click_id, \"\n    query += main_table_name + \".ip, \" + main_table_name + \".app, \" + main_table_name + \".device, \" + main_table_name + \".os, \" + main_table_name + \".channel, \" + main_table_name + \".click_time, \"\n    if train:\n        query += \"is_attributed, \"\n    query += \", \".join(field_names) + \"\\n\"\n\n    #######################\n    # Create FROM section\n    #######################\n    query += \"FROM \" + main_table_name + \", \"\n    query += \", \".join(temp_table_names) + \"\\n\"\n\n    #######################\n    # Create WHERE section\n    #######################\n\n    query += \"WHERE\\n  \"\n    query += \"\\n  AND \".join(where_clauses)\n    query += \"\\n\"\n\n    #########################\n    # Create ORDER BY section\n    #########################\n\n    # query += \"ORDER BY ip, click_time\" # Not needed for final computation, use it for debug if you wish\n\n    return query","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"7f1b896f-d788-447a-b2eb-30e5e20d0c68","_uuid":"d3f85297264312c03badf419d799fb2b7562d652"},"cell_type":"markdown","source":"## Generate your query!\n\nTo generate your query, you just need to replace table_name below with your value, and run the code.\n\nIf you want to work on the test set, change the train variable to False. This is required because the columns of the training set and the test set differs slightly (is_attributed in the training set, click_id in the test set)."},{"metadata":{"_cell_guid":"fdab74c1-6c26-469c-a46e-39aaad88f87c","_uuid":"fb302684751778c981029b214933338350cbce7d","collapsed":true,"trusted":false},"cell_type":"code","source":"# Create query for train data\n\ntrain = True\ntable_name = \"`my_dataset.my_table_with_index`\"\n\nprint(create_query(table_name, train=train))","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"3d4ed3cf-d430-4a49-873c-03b8c5288d09","_uuid":"1512aa0a57bba47fcb783dae0f6abc02a5156131"},"cell_type":"markdown","source":"## Run the query\n\nNow, copy/paste the generated query in Big Query. Before clicking on run query, you will have to set the options to define a destination table, and to allow large results.\n\nIt will take less than 4 minutes! That's really quick compared to pandas!"},{"metadata":{"_cell_guid":"0b265b95-7bf9-4946-b7a5-1468faea3bb0","_uuid":"b7b6d055e3222266382030ac393c5811d11d2cfb"},"cell_type":"markdown","source":"## Export result\n\nAfter running the query, you will get a new table with all your new features.\n\nYou cannot download this table directly from Big Query, as is it too large. You will have to export it to Google Cloud Storage, by giving it a name like my_export_data_\\*.csv. The \\* char will enable Big Query to split your file in several smaller files. If you wish, you can ask Big Query to gzip your files for smaller download sizes.\n\nWhen the files are exported to Google Cloud Storage, you can download them from the web UI, or with gsutil [(see documentation)](https://cloud.google.com/storage/docs/gsutil/commands/cp)."},{"metadata":{"_cell_guid":"fb05f6f0-5f81-4fde-8a14-10bfc182fa23","_uuid":"0f0a418c78099c1a77fb2c518209c63d768ede11","collapsed":true,"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.5","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}