Cloud SQL Query for COS.ipynb

{
    "nbformat_minor": 1, 
    "cells": [
        {
            "source": "# IBM Cloud SQL Query\ub97c \ud65c\uc6a9, COS\uc5d0 \uc800\uc7a5\ub41c \ud654\uc77c\uc744 \ucc98\ub9ac\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.\n<div class=\"pull-left\"><left><img style=\"float: right;\" src=\"http://developer.ibm.com/clouddataservices/wp-content/uploads/sites/85/2018/01/ibm-cloud-object-storage-logo-small.png\" width=\"100\" margin=50></left></div>\n<div style=\"text-align:center\">\n<b>IBM Cloud SQL Query</b> is IBM's <b>serverless SQL</b> service on data in <b>Cloud Object Storage</b>. It allows to run ANSI SQL on Parquet, CSV and JSON data sets. It is based on Apache Spark SQL as the query engine in the background. This means you do <b>not</b> have to provision any Apache Spark instance or service. A simple Python client is sufficient.<br><br></div>\nThis notebook is meant to be a generic starter to use the SQL Query API in order to run SQL statements in a programmatic way. It uses the <a href=\"https://github.com/IBM-Cloud/sql-query-clients/tree/master/Python\" target=\"_blank\" rel=\"noopener noreferrer\">ibmcloudsql</a> Python library for this purpose. The notebook also demonstrates how you can combine SQL Query with visualization libraries such as **PixieDust**. The notebook has been verified to work with Python 3.5. As mentioned above it does not require a Spark service bound to the notebook.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "## Table of contents\n1. [Setup libraries](#setup)<br>\n2. [Configure SQL Query](#configure)<br>\n    2.1 [Using the project bucket](#projectbucket)<br>\n    2.2 [Setting SQL Query parameters](#parameters)<br>\n3. [Your SQL](#sql)<br>\n4. [Run the SQL](#run)<br>\n    4.1 [Low level SQL job submission](#lowlevel)<br>\n5. [List recent SQL submissions](#joblist)<br>\n6. [Useful things](#other)<br>\n7. [Next steps](#next)<br>", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "### - \uba87\uac00\uc9c0 \uc0ac\uc804 \uc900\ube44 \uc791\uc5c5\n*** 1. platform API Key \uc0dd\uc131*** (https://github.com/moreal70/IBM-Watson-Studio/blob/master/HowTo-Create%20Platform%20API%20Key.ipynb)     \n*** 2. project token insert ***\n![1](https://github.com/moreal70/IBM-Watson-Studio/raw/master/images/project_token_insert.png)       \n*** 3. IBM Cloud SQL Query\uc5d0\uc11c CRN \uac00\uc838\uc624\uae30 ***  \n![1](https://github.com/moreal70/IBM-Watson-Studio/raw/master/images/cloud_sql_query_CRN.png)       ", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "### <a id=\"setup\"></a> 1. Setup libraries", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "* ***-q \uc635\uc158\uc744 \uc8fc\uba74 output \uc774 \uc801\uac8c \ub610\ub294 \uc544\uc608 \uc548\ubcf4\uc785\ub2c8\ub2e4.***", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 17, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "!pip -q install ibmcloudsql\n!pip -q install sqlparse"
        }, 
        {
            "execution_count": 2, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Pixiedust database opened successfully\nTable VERSION_TRACKER created successfully\nTable METRICS_TRACKER created successfully\n\nShare anonymous install statistics? (opt-out instructions)\n\nPixieDust will record metadata on its environment the next time the package is installed or updated. The data is anonymized and aggregated to help plan for future releases, and records only the following values:\n\n{\n   \"data_sent\": currentDate,\n   \"runtime\": \"python\",\n   \"application_version\": currentPixiedustVersion,\n   \"space_id\": nonIdentifyingUniqueId,\n   \"config\": {\n       \"repository_id\": \"https://github.com/ibm-watson-data-lab/pixiedust\",\n       \"target_runtimes\": [\"Data Science Experience\"],\n       \"event_id\": \"web\",\n       \"event_organizer\": \"dev-journeys\"\n   }\n}\nYou can opt out by calling pixiedust.optOut() in a new cell.\n"
                }, 
                {
                    "output_type": "display_data", 
                    "data": {
                        "text/html": "\n        <div style=\"margin:10px\">\n            <a href=\"https://github.com/ibm-watson-data-lab/pixiedust\" target=\"_new\">\n                <img src=\"https://github.com/ibm-watson-data-lab/pixiedust/raw/master/docs/_static/pd_icon32.png\" style=\"float:left;margin-right:10px\"/>\n            </a>\n            <span>Pixiedust version 1.1.9</span>\n        </div>\n        ", 
                        "text/plain": "<IPython.core.display.HTML object>"
                    }, 
                    "metadata": {}
                }, 
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Pixiedust runtime updated. Please restart kernel\nTable USER_PREFERENCES created successfully\nTable service_connections created successfully\n"
                }
            ], 
            "source": "import ibmcloudsql\nfrom pixiedust.display import *\nimport pandas as pd\ntargeturl=''"
        }, 
        {
            "source": "### <a id=\"configure\"></a> 2. Configure SQL Query\n1. You need an **API key** for an IBM cloud identity that has access to your Cloud Object Storage bucket for writing SQL results and to your SQL Query instance. To create API keys log on to the IBM Cloud console and go to <a href=\"https://console.bluemix.net/iam/#/apikeys\" target=\"_blank\">Manage->Security->Platform API Keys</a>, click the `Create` button, give the key a custom name and click `Create`. In the next dialog click `Show` and copy the key to your clipboard and paste it below in this notebook.\n2. You need the **instance CRN** for the SQL Query instance. You can find it in the <a href=\"https://console.bluemix.net/dashboard/apps\" target=\"_blank\">IBM Cloud console dashboard</a>. Make sure you have `All Resources` selected as resource group. In the section `Services` you can see your instances of SQL Query and Cloud Object Storage. Select the instance of SQL Query that you want to use. In the SQL Query dashboard page that opens up you find a section titled **REST API** with a button labelled **Instance CRN**. Click the button to copy the CRN into your clipboard and paste it here into the notebook. If you don't have an SQL Query instance created yet, <a href=\"https://console.bluemix.net/catalog/services/sql-query\" target=\"_blank\">create one</a> first.\n3. You need to specify the location on Cloud Object Storage where your **query results** should be written. This comprises three parts of information that you can find in the Cloud Object Storage UI for your instance in the IBM Cloud console. You need to provide it as a **URL** using the format `cos://<endpoint>/<bucket>/[<prefix>]`. You have the option to use the cloud object storage **bucket that is associated with your project**. In this case, execute the following section before you proceed.  \n<br/>\nFor more background information, check out the SQL Query <a href=\"https://console.bluemix.net/docs/services/sql-query/getting-started.html#getting-started-tutorial\" target=\"_blank\">documentation</a>.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "#### <a id=\"projectbucket\"></a> 2.1 Using the project bucket\n**Only** follow the instructions in this section when you want to write your SQL query results to the bucket that has been created for the project for which you have created this notebook. In any other case proceed directly with section **2.2**.\n<br><br>\n__Inserting the project token__:  \nClick the `More` option in the toolbar above (the three stacked dots) and select `Insert project token`.\n * If you haven't created an access token for this project before, you will see a dialog that asks you to create one first. Follow the link to open your project settings, scroll down to `Access tokens` and click `New token`. Give the token a custom name and make sure you select `Editor` as `Access role for project`. After you created your access token you can come back to this notebook, select the empty cell below and again select `Insert project token` from the toolbar at the top.\n[//]: # \nThis will add a new cell at the top of your notebook with content that looks like this:\n```\n# @hidden_cell\n# The project token is an authorization token that is used to access project resources like data sources, connections, and used by platform APIs.\nfrom project_lib import Project\nproject = Project(project_id='<some id>', project_access_token='<some access token>')\npc = project.project_context\n```\nLeave that cell content as inserted and run the cell. Then then proceed with the following cell below:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "* ***project token \uac00\uc838\uc624\uae30***", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 3, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# The code was removed by DSX for sharing."
        }, 
        {
            "source": "* ***project meta data \uc5d0\uc11c cos bucket \uc815\ubcf4\ub97c \ucd94\ucd9c\ud569\ub2c8\ub2e4.***", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 18, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "cos_bucket = project.get_metadata()['entity']['storage']['properties']\ntargeturl=\"cos://\" + cos_bucket['bucket_region'] + \"/\" + cos_bucket['bucket_name'] + \"/\""
        }, 
        {
            "execution_count": null, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "print(cos_bucket)"
        }, 
        {
            "source": "#### <a id=\"parameters\"></a> 2.2 Setting the SQL Query parameters", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 5, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Enter IBM Cloud API Key (leave empty to use previous one): \u00b7\u00b7\u00b7\u00b7\u00b7\u00b7\u00b7\u00b7\nEnter SQL Query Instance CRN (leave empty to use previous one): crn%3Av1%3Abluemix%3Apublic%3Asql-query%3Aus-south%3Aa%2F651c233bb1b6ff339e8628e3ccd5b96a%3A9646c9a5-6825-45af-a24d-39b04c8d98da%3A%3A\nEnter target URL for SQL results (leave empty to use cos://us-geo/projectparkhsu02d1be16ce7e164fc694f1c69332955f0b/): \n\nYour SQL Query web console link:\n\nhttps://sql.ng.bluemix.net/sqlquery/?instance_crn=crn:v1:bluemix:public:sql-query:us-south:a/651c233bb1b6ff339e8628e3ccd5b96a:9646c9a5-6825-45af-a24d-39b04c8d98da::\n"
                }
            ], 
            "source": "import getpass\napikey=getpass.getpass('Enter IBM Cloud API Key (leave empty to use previous one): ') or apikey\ninstnacecrn=input('Enter SQL Query Instance CRN (leave empty to use previous one): ') or instnacecrn\nif targeturl == '':\n    targeturl=input('Enter target URL for SQL results: ')\nelse:\n    targeturl=input('Enter target URL for SQL results (leave empty to use ' + targeturl + '): ') or targeturl\nsqlClient = ibmcloudsql.SQLQuery(apikey, instnacecrn, targeturl, client_info='SQL Query Starter Notebook')\nsqlClient.logon()\nprint('\\nYour SQL Query web console link:\\n')\nsqlClient.sql_ui_link()"
        }, 
        {
            "source": "* ***\uc704\uc5d0\uc11c \ud280\uc5b4 \ub098\uc628 url \ub85c \ub4e4\uc5b4\uac00\uc11c \uc544\ub798 sql \ubb38\uc7a5\uc744 \ub3cc\ub824\ubd10\ub3c4 \uc88b\uc2b5\ub2c8\ub2e4.***", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "### <a id=\"sql\"></a> 3. Your SQL\nTo author your own SQL query, use the interactive SQL Query web console (**link above**) of your SQL Query service instance.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 6, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Enter your SQL statement (leave empty to use a simple sample SQL)\n\nYour SQL statement is:\n\n\u001b[38;5;24;01mSELECT\u001b[39;00m \u001b[38;5;0mo\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mOrderID\u001b[39m\u001b[38;5;0;01m,\u001b[39;00m\n\t\u001b[38;5;24;01mc\u001b[39;00m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mCompanyName\u001b[39m\u001b[38;5;0;01m,\u001b[39;00m\n\t\u001b[38;5;0me\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mFirstName\u001b[39m\u001b[38;5;0;01m,\u001b[39;00m\n\t\u001b[38;5;0me\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mLastName\u001b[39m\n\u001b[38;5;24;01mFROM\u001b[39;00m \u001b[38;5;0mcos\u001b[39m\u001b[38;5;0;01m:\u001b[39;00m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;0mus\u001b[39m\u001b[38;5;166;01m-\u001b[39;00m\u001b[38;5;0mgeo\u001b[39m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;24;01mSQL\u001b[39;00m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;0morders\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mparquet\u001b[39m \u001b[38;5;0mSTORED\u001b[39m \u001b[38;5;24;01mAS\u001b[39;00m \u001b[38;5;0mPARQUET\u001b[39m \u001b[38;5;0mo\u001b[39m\u001b[38;5;0;01m,\u001b[39;00m\n\t\u001b[38;5;0mcos\u001b[39m\u001b[38;5;0;01m:\u001b[39;00m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;0mus\u001b[39m\u001b[38;5;166;01m-\u001b[39;00m\u001b[38;5;0mgeo\u001b[39m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;24;01mSQL\u001b[39;00m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;0memployees\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mparquet\u001b[39m \u001b[38;5;0mSTORED\u001b[39m \u001b[38;5;24;01mAS\u001b[39;00m \u001b[38;5;0mPARQUET\u001b[39m \u001b[38;5;0me\u001b[39m\u001b[38;5;0;01m,\u001b[39;00m\n\t\u001b[38;5;0mcos\u001b[39m\u001b[38;5;0;01m:\u001b[39;00m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;0mus\u001b[39m\u001b[38;5;166;01m-\u001b[39;00m\u001b[38;5;0mgeo\u001b[39m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;24;01mSQL\u001b[39;00m\u001b[38;5;166;01m/\u001b[39;00m\u001b[38;5;0mcustomers\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mparquet\u001b[39m \u001b[38;5;0mSTORED\u001b[39m \u001b[38;5;24;01mAS\u001b[39;00m \u001b[38;5;0mPARQUET\u001b[39m \u001b[38;5;24;01mc\u001b[39;00m\n\u001b[38;5;24;01mWHERE\u001b[39;00m \u001b[38;5;0me\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mEmployeeID\u001b[39m \u001b[38;5;166;01m=\u001b[39;00m \u001b[38;5;0mo\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mEmployeeID\u001b[39m\n\t\t\u001b[38;5;24;01mAND\u001b[39;00m \u001b[38;5;24;01mc\u001b[39;00m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mCustomerID\u001b[39m \u001b[38;5;166;01m=\u001b[39;00m \u001b[38;5;0mo\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mCustomerID\u001b[39m\n\t\t\u001b[38;5;24;01mAND\u001b[39;00m \u001b[38;5;0mo\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mShippedDate\u001b[39m \u001b[38;5;166;01m>\u001b[39;00m \u001b[38;5;0mo\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mRequiredDate\u001b[39m\n\t\t\u001b[38;5;24;01mAND\u001b[39;00m \u001b[38;5;0mo\u001b[39m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mOrderDate\u001b[39m \u001b[38;5;166;01m>\u001b[39;00m \u001b[38;5;64m\"1998-01-01\"\u001b[39m\n\u001b[38;5;24;01mORDER\u001b[39;00m \u001b[38;5;24;01mBY\u001b[39;00m \u001b[38;5;24;01mc\u001b[39;00m\u001b[38;5;0;01m.\u001b[39;00m\u001b[38;5;0mCompanyName\u001b[39m\n\n"
                }
            ], 
            "source": "import sqlparse\nfrom pygments import highlight\nfrom pygments.lexers import get_lexer_by_name\nfrom pygments.formatters import HtmlFormatter, Terminal256Formatter\n\nsql=input('Enter your SQL statement (leave empty to use a simple sample SQL)')\nif sql == '':\n    sql='SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName FROM cos://us-geo/sql/orders.parquet STORED AS PARQUET o, cos://us-geo/sql/employees.parquet STORED AS PARQUET e, cos://us-geo/sql/customers.parquet STORED AS PARQUET c WHERE e.EmployeeID = o.EmployeeID AND c.CustomerID = o.CustomerID AND o.ShippedDate > o.RequiredDate AND o.OrderDate > \"1998-01-01\" ORDER BY c.CompanyName'\nformatted_sql = sqlparse.format(sql, reindent=True, indent_tabs=True, keyword_case='upper')\nlexer = get_lexer_by_name(\"sql\", stripall=True)\nformatter = Terminal256Formatter(style='tango')\nresult = highlight(formatted_sql, lexer, formatter)\nfrom IPython.core.display import display, HTML\nprint('\\nYour SQL statement is:\\n')\nprint(result)"
        }, 
        {
            "source": "### <a id=\"run\"></a> 4. Running the SQL\nThe following cell submits the SQL statement and waits for it to finish before printing a sample of the result set.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 20, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "result_df = sqlClient.run_sql(sql)\nif isinstance(result_df, str):\n    print(result_df)"
        }, 
        {
            "execution_count": 21, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 21, 
                    "metadata": {}, 
                    "data": {
                        "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>OrderID</th>\n      <th>CompanyName</th>\n      <th>FirstName</th>\n      <th>LastName</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>10924</td>\n      <td>Berglunds snabbk\u00f6p</td>\n      <td>Janet</td>\n      <td>Leverling</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>11058</td>\n      <td>Blauer See Delikatessen</td>\n      <td>Anne</td>\n      <td>Dodsworth</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10827</td>\n      <td>Bon app'</td>\n      <td>Nancy</td>\n      <td>Davolio</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>11076</td>\n      <td>Bon app'</td>\n      <td>Margaret</td>\n      <td>Peacock</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>11045</td>\n      <td>Bottom-Dollar Markets</td>\n      <td>Michael</td>\n      <td>Suyama</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>10970</td>\n      <td>B\u00f3lido Comidas preparadas</td>\n      <td>Anne</td>\n      <td>Dodsworth</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>11054</td>\n      <td>Cactus Comidas para llevar</td>\n      <td>Laura</td>\n      <td>Callahan</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>11008</td>\n      <td>Ernst Handel</td>\n      <td>Robert</td>\n      <td>King</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>11072</td>\n      <td>Ernst Handel</td>\n      <td>Margaret</td>\n      <td>Peacock</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>10816</td>\n      <td>Great Lakes Food Market</td>\n      <td>Margaret</td>\n      <td>Peacock</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "   OrderID                 CompanyName FirstName   LastName\n0  10924    Berglunds snabbk\u00f6p          Janet     Leverling\n1  11058    Blauer See Delikatessen     Anne      Dodsworth\n2  10827    Bon app'                    Nancy     Davolio  \n3  11076    Bon app'                    Margaret  Peacock  \n4  11045    Bottom-Dollar Markets       Michael   Suyama   \n5  10970    B\u00f3lido Comidas preparadas   Anne      Dodsworth\n6  11054    Cactus Comidas para llevar  Laura     Callahan \n7  11008    Ernst Handel                Robert    King     \n8  11072    Ernst Handel                Margaret  Peacock  \n9  10816    Great Lakes Food Market     Margaret  Peacock  "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "result_df.head(10)"
        }, 
        {
            "source": "* ***\uc55e\uc5d0 url \uc5d0 \ub4e4\uc5b4\uac00\uc11c \uc870\ud68c\ud55c \uac83\uacfc \ub3d9\uc77c\ud55c \uacb0\uacfc\uac00 \ub098\uc640\uc57c\ud569\ub2c8\ub2e4***", 
            "cell_type": "markdown", 
            "metadata": {
                "pixiedust": {
                    "displayParams": {
                        "orientation": "horizontal", 
                        "title": "Orders per Employee", 
                        "chartsize": "89", 
                        "aggregation": "SUM", 
                        "rowCount": "20", 
                        "handlerId": "barChart", 
                        "rendererId": "brunel", 
                        "sortby": "Values ASC", 
                        "keyFields": "FirstName"
                    }
                }
            }
        }, 
        {
            "execution_count": 9, 
            "cell_type": "code", 
            "metadata": {
                "pixiedust": {
                    "displayParams": {
                        "orientation": "horizontal", 
                        "title": "Orders per Employee", 
                        "chartsize": "64", 
                        "aggregation": "SUM", 
                        "clusterby": "CompanyName", 
                        "rowCount": "20", 
                        "handlerId": "barChart", 
                        "rendererId": "brunel", 
                        "sortby": "Values ASC", 
                        "keyFields": "FirstName"
                    }
                }
            }, 
            "outputs": [
                {
                    "output_type": "display_data", 
                    "data": {
                        "text/html": "<style type=\"text/css\">.pd_warning{display:none;}</style><div class=\"pd_warning\"><em>Hey, there's something awesome here! To see it, open this notebook outside GitHub, in a viewer like Jupyter</em></div>\n        <div class=\"pd_save is-viewer-good\" style=\"padding-right:10px;text-align: center;line-height:initial !important;font-size: xx-large;font-weight: 500;color: coral;\">\n            Orders per Employee\n        </div>\n    \n        <div id=\"chartFigure6befb6df\" class=\"pd_save\" style=\"overflow-x:auto\">\n            <!--\n  ~ Copyright (c) 2015 IBM Corporation and others.\n  ~\n  ~ Licensed under the Apache License, Version 2.0 (the \"License\");\n  ~ You may not use this file except in compliance with the License.\n  ~ You may obtain a copy of the License at\n  ~\n  ~     http://www.apache.org/licenses/LICENSE-2.0\n  ~\n  ~ Unless required by applicable law or agreed to in writing, software\n  ~ distributed under the License is distributed on an \"AS IS\" BASIS,\n  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n  ~ See the License for the specific language governing permissions and\n  ~ limitations under the License.\n  -->\n\n\n<link rel=\"stylesheet\" type=\"text/css\" href=\"/data/jupyter2/static-file-content-delivery-network/nbextensions/brunel_ext/brunel.2.3.css\">\n<link rel=\"stylesheet\" type=\"text/css\" href=\"/data/jupyter2/static-file-content-delivery-network/nbextensions/brunel_ext/sumoselect.css\">\n\n<style>\n    \n</style>\n\n<div id=\"controlsid1db56360-4cf1-11e8-be5d-86c880cdd486\" class=\"brunel\"></div>\n<svg id=\"visid1db56162-4cf1-11e8-be5d-86c880cdd486\" width=\"518\" height=\"388\"></svg>\n<script type=\"text/javascript\">/*\n * Copyright (c) 2015 IBM Corporation and others.\n *\n * Licensed under the Apache License, Version 2.0 (the \"License\");\n * You may not use this file except in compliance with the License.\n * You may obtain a copy of the License at\n *\n *     http://www.apache.org/licenses/LICENSE-2.0\n *\n * Unless required by applicable law or agreed to in writing, software\n * distributed under the License is distributed on an \"AS IS\" BASIS,\n * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n * See the License for the specific language governing permissions and\n * limitations under the License.\n */\n\nrequire.config({\n    waitSeconds: 60,\n    paths: {\n        'd3': '//cdnjs.cloudflare.com/ajax/libs/d3/4.2.1/d3.min',\n        'topojson': '//cdnjs.cloudflare.com/ajax/libs/topojson/1.6.20/topojson.min',\n        'brunel' : '/data/jupyter2/static-file-content-delivery-network/nbextensions/brunel_ext/brunel.2.3.min',\n        'brunelControls' : '/data/jupyter2/static-file-content-delivery-network/nbextensions/brunel_ext/brunel.controls.2.3.min'\n    },\n    shim: {\n       'brunel' : {\n            exports: 'BrunelD3',\n            deps: ['d3', 'topojson'],\n            init: function() {\n               return {\n                 BrunelD3 : BrunelD3,\n                 BrunelData : BrunelData\n              }\n            }\n        },\n       'brunelControls' : {\n            exports: 'BrunelEventHandlers',\n            init: function() {\n               return {\n                 BrunelEventHandlers: BrunelEventHandlers,\n                 BrunelJQueryControlFactory: BrunelJQueryControlFactory\n              }\n            }\n        }\n\n    }\n\n});\n\nrequire([\"d3\"], function(d3) {\n    require([\"brunel\", \"brunelControls\"], function(brunel, brunelControls) {\n        function  BrunelVis(visId) {\n  \"use strict\";                                                                       // strict mode\n  var datasets = [],                                      // array of datasets for the original data\n      pre = function(d, i) { return d },                         // default pre-process does nothing\n      post = function(d, i) { return d },                       // default post-process does nothing\n      transitionTime = 200,                                        // transition time for animations\n      charts = [],                                                       // the charts in the system\n      vis = d3.select('#' + visId).attr('class', 'brunel');                     // the SVG container\n\n  BrunelD3.addDefinitions(vis);                                   // ensure standard symbols present\n\n  // Define chart #1 in the visualization //////////////////////////////////////////////////////////\n\n  charts[0] = function(parentNode, filterRows) {\n    var geom = BrunelD3.geometry(parentNode || vis.node(), 0, 0, 1, 1, 5, 71, 37, 187),\n      elements = [];                                              // array of elements in this chart\n    geom.transpose();\n\n    // Define groups for the chart parts ///////////////////////////////////////////////////////////\n\n    var chart =  vis.append('g').attr('class', 'chart1')\n      .attr('transform','translate(' + geom.chart_left + ',' + geom.chart_top + ')');\n    var overlay = chart.append('g').attr('class', 'element').attr('class', 'overlay');\n    var zoom = d3.zoom().scaleExtent([1/3,3]);\n    var zoomNode = overlay.append('rect').attr('class', 'overlay')\n      .attr('x', geom.inner_left).attr('y', geom.inner_top)\n      .attr('width', geom.inner_rawWidth).attr('height', geom.inner_rawHeight)\n      .style('cursor', 'move').call(zoom)\n      .node();\n    zoomNode.__zoom = d3.zoomIdentity;\n    chart.append('rect').attr('class', 'background').attr('width', geom.chart_right-geom.chart_left).attr('height', geom.chart_bottom-geom.chart_top);\n    var interior = chart.append('g').attr('class', 'interior zoomNone')\n      .attr('transform','translate(' + geom.inner_left + ',' + geom.inner_top + ')')\n      .attr('clip-path', 'url(#clip_visid1db56162-4cf1-11e8-be5d-86c880cdd486_chart1_inner)');\n    interior.append('rect').attr('class', 'inner').attr('width', geom.inner_width).attr('height', geom.inner_height);\n    var gridGroup = interior.append('g').attr('class', 'grid');\n    var axes = chart.append('g').attr('class', 'axis')\n      .attr('transform','translate(' + geom.inner_left + ',' + geom.inner_top + ')');\n    var legends = chart.append('g').attr('class', 'legend')\n      .attr('transform','translate(' + (geom.chart_right-geom.chart_left - 3) + ',' + 0 + ')');\n    vis.append('clipPath').attr('id', 'clip_visid1db56162-4cf1-11e8-be5d-86c880cdd486_chart1_inner').append('rect')\n      .attr('x', 0).attr('y', 0)\n      .attr('width', geom.inner_rawWidth+1).attr('height', geom.inner_rawHeight+1);\n\n    // Scales //////////////////////////////////////////////////////////////////////////////////////\n\n    var scale_x = d3.scalePoint().padding(0.5)\n      .domain(['Margaret', 'Anne', 'Robert', 'Laura', 'Andrew', 'Nancy', 'Michael', 'Janet'])\n      .range([geom.inner_width, 0]);\n    var scale_inner = d3.scaleSqrt().domain([3.5000004, 0])\n      .range([-0.5, 0.5]);\n    var scale_y = d3.scaleSqrt().domain([0, 3.5000004])\n      .range([0, geom.inner_height]);\n    var base_scales = [scale_x, scale_y];                           // untransformed original scales\n\n    // Axes ////////////////////////////////////////////////////////////////////////////////////////\n\n    axes.append('g').attr('class', 'x axis')\n      .attr('transform','translate(0,' + geom.inner_rawHeight + ')')\n      .attr('clip-path', 'url(#clip_visid1db56162-4cf1-11e8-be5d-86c880cdd486_chart1_haxis)');\n    vis.append('clipPath').attr('id', 'clip_visid1db56162-4cf1-11e8-be5d-86c880cdd486_chart1_haxis').append('polyline')\n      .attr('points', '-1,-1000, -1,-1 -5,5, -1000,5, -100,1000, 10000,1000 10000,-1000');\n    axes.select('g.axis.x').append('text').attr('class', 'title').text('Pd Count').style('text-anchor', 'middle')\n      .attr('x',geom.inner_rawWidth/2)\n      .attr('y', geom.inner_bottom - 2.0).attr('dy','-0.27em');\n    axes.append('g').attr('class', 'y axis')\n      .attr('clip-path', 'url(#clip_visid1db56162-4cf1-11e8-be5d-86c880cdd486_chart1_vaxis)');\n    vis.append('clipPath').attr('id', 'clip_visid1db56162-4cf1-11e8-be5d-86c880cdd486_chart1_vaxis').append('polyline')\n      .attr('points', '-1000,-10000, 10000,-10000, 10000,' + (geom.inner_rawHeight+1) + ', -1,' + (geom.inner_rawHeight+1) + ', -1,' + (geom.inner_rawHeight+5) + ', -1000,' + (geom.inner_rawHeight+5) );\n    axes.select('g.axis.y').append('text').attr('class', 'title').text('First Name').style('text-anchor', 'middle')\n      .attr('x',-geom.inner_rawHeight/2)\n      .attr('y', 4-geom.inner_left).attr('dy', '0.7em').attr('transform', 'rotate(270)');\n\n    var axis_bottom = d3.axisBottom(scale_y).ticks(7);\n    var axis_left = d3.axisLeft(scale_x).ticks(Math.min(10, Math.round(geom.inner_width / 20)));\n\n    function buildAxes(time) {\n      var axis_x = axes.select('g.axis.x');\n      BrunelD3.transition(axis_x, time).call(axis_bottom.scale(scale_y));\n      axis_left.tickValues(BrunelD3.filterTicks(scale_x))\n      var axis_y = axes.select('g.axis.y');\n      BrunelD3.transition(axis_y, time).call(axis_left.scale(scale_x));\n    }\n    zoom.on('zoom', function(t, time) {\n        t = t ||BrunelD3.restrictZoom(d3.event.transform, geom, this);\n        scale_y = t.rescaleX(base_scales[1]);\n        zoomNode.__zoom = t;\n        interior.attr('class', 'interior ' + BrunelD3.zoomLabel(t.k));;\n        build(time || -1);\n    });\n\n    // Define element #1 ///////////////////////////////////////////////////////////////////////////\n\n    elements[0] = function() {\n      var original, processed,                           // data sets passed in and then transformed\n        element, data,                                 // brunel element information and brunel data\n        selection, merged;                                      // d3 selection and merged selection\n      var elementGroup = interior.append('g').attr('class', 'element1')\n        .attr('transform','matrix(0,1,1,0,0,0)'),\n        main = elementGroup.append('g').attr('class', 'main'),\n        labels = BrunelD3.undoTransform(elementGroup.append('g').attr('class', 'labels').attr('aria-hidden', 'true'), elementGroup);\n\n      function makeData() {\n        original = datasets[0];\n        if (filterRows) original = original.retainRows(filterRows);\n        processed = pre(original, 0)\n          .sort('pd_count:ascending');\n        processed = post(processed, 0);\n        var f0 = processed.field('FirstName'),\n          f1 = processed.field('pd_count'),\n          f2 = processed.field('CompanyName'),\n          f3 = processed.field('#row'),\n          f4 = processed.field('#selection');\n        var keyFunc = function(d) { return f0.value(d)+ '|' + f2.value(d) };\n        data = {\n          FirstName:    function(d) { return f0.value(d.row) },\n          pd_count:     function(d) { return f1.value(d.row) },\n          CompanyName:  function(d) { return f2.value(d.row) },\n          $row:         function(d) { return f3.value(d.row) },\n          $selection:   function(d) { return f4.value(d.row) },\n          FirstName_f:  function(d) { return f0.valueFormatted(d.row) },\n          pd_count_f:   function(d) { return f1.valueFormatted(d.row) },\n          CompanyName_f:function(d) { return f2.valueFormatted(d.row) },\n          $row_f:       function(d) { return f3.valueFormatted(d.row) },\n          $selection_f: function(d) { return f4.valueFormatted(d.row) },\n          _split:       function(d) { return f2.value(d.row) },\n          _key:         keyFunc,\n          _rows:        BrunelD3.makeRowsWithKeys(keyFunc, processed.rowCount())\n        };\n      }\n      // Aesthetic Functions\n      var scale_color = d3.scaleOrdinal()\n        .domain(['Berglunds snabbk\u00f6p', 'Blauer See Delikatessen', 'B\u00f3lido Comidas preparadas', 'LILA-Supermercado', 'LINO-Delicateses', \"La corne d'abondance\", \"La maison d'Asie\", 'Lehmanns Marktstand', 'Maison Dewey', 'Pericles Comidas cl\u00e1sicas', 'Queen Cozinha', 'Rattlesnake Canyon Grocery', 'Ricardo Adocicados', 'Simons bistro', 'Wolski  Zajazd', 'Ernst Handel', 'Rancho grande', 'Great Lakes Food Market'])\n        .range([ '#347DAD', '#D43F58', '#F7D84A', '#31A461', '#A66A9C', '#FF954D', \n          '#A7978E', '#FFCA4D', '#F99EAF', '#B1C43B', '#7E64A2', '#FFB04D', '#CA5C7C', \n          '#DDBC8C', '#FFA28D', '#A5473D', '#8B6141', '#F57357', '#5C6B46']);\n      var color = function(d) { return scale_color(data.CompanyName(d)) };\n      legends._legend = legends._legend || { title: ['Company Name'], \n        ticks: scale_color.domain()};\n      legends._legend.color = scale_color;\n\n      // Build element from data ///////////////////////////////////////////////////////////////////\n\n      function build(transitionMillis) {\n        element = elements[0];\n        var clusterWidth = 0.8 * Math.abs(scale_x(scale_x.domain()[1]) - scale_x(scale_x.domain()[0]) );\n        var w = Math.abs( scale_inner(scale_inner.domain()[0] + 2.0) - scale_inner.range()[0] ) * clusterWidth;\n        var x = function(d) { return scale_x(data.FirstName(d)) + clusterWidth * scale_inner(data.pd_count(d))};\n        var h = Math.abs( scale_y(scale_y.domain()[0] + 2.0) - scale_y.range()[0] );\n        var y1 = scale_y.range()[0];\n        var y2 = function(d) { return scale_y(data.pd_count(d))};\n\n        // Define selection entry operations\n        function initialState(selection) {\n          selection\n            .attr('class', 'element bar filled')\n            .style('pointer-events', 'none')\n        }\n\n        // Define selection update operations on merged data\n        function updateState(selection) {\n          selection\n            .each(function(d) {\n              var width = w, left = x(d) - width/2, \n              c = y1, d = y2(d), top = Math.min(c,d), height = Math.max(1e-6, Math.abs(c-d));\n              this.r = {x:left, y:top, w:width, h:height};\n            })\n            .attr('x', function(d) { return this.r.x })\n            .attr('y', function(d) { return this.r.y })\n            .attr('width', function(d) { return this.r.w })\n            .attr('height', function(d) { return this.r.h })\n            .filter(BrunelD3.hasData)                     // following only performed for data items\n            .style('fill', color);\n        }\n\n        // Define labeling for the selection\n        function label(selection, transitionMillis) {\n        }\n        // Create selections, set the initial state and transition updates\n        selection = main.selectAll('.element').data(data._rows, function(d) { return d.key });\n        var added = selection.enter().append('rect');\n        merged = selection.merge(added);\n        initialState(added);\n        selection.filter(BrunelD3.hasData)\n          .classed('selected', BrunelD3.isSelected(data))\n          .filter(BrunelD3.isSelected(data)).raise();\n        updateState(BrunelD3.transition(merged, transitionMillis));\n\n        BrunelD3.transition(selection.exit(), transitionMillis/3)\n          .style('opacity', 0.5).each( function() {\n            this.remove(); BrunelD3.removeLabels(this); \n        });\n      }\n\n      return {\n        data:           function() { return processed },\n        original:       function() { return original },\n        internal:       function() { return data },\n        selection:      function() { return merged },\n        makeData:       makeData,\n        build:          build,\n        chart:          function() { return charts[0] },\n        group:          function() { return elementGroup },\n        fields: {\n          x:            ['FirstName', 'pd_count'],\n          y:            ['pd_count'],\n          key:          ['FirstName', 'CompanyName'],\n          color:        ['CompanyName']\n        }\n      };\n    }();\n\n    function build(time, noData) {\n      var first = elements[0].data() == null;\n      if (first) time = 0;                                           // no transition for first call\n      buildAxes(time);\n      if ((first || time > -1) && !noData) {\n        elements[0].makeData();\n        BrunelD3.addLegend(legends, legends._legend);\n      }\n      elements[0].build(time);\n    }\n\n    // Expose the following components of the chart\n    return {\n      elements : elements,\n      interior : interior,\n      scales: {x:scale_x, y:scale_y},\n      zoom: function(params, time) {\n          if (params) zoom.on('zoom').call(zoomNode, params, time);\n          return d3.zoomTransform(zoomNode);\n      },\n      build : build\n    };\n    }();\n\n  function setData(rowData, i) { datasets[i||0] = BrunelD3.makeData(rowData) }\n  function updateAll(time) { charts.forEach(function(x) {x.build(time || 0)}) }\n  function buildAll() {\n    for (var i=0;i<arguments.length;i++) setData(arguments[i], i);\n    updateAll(transitionTime);\n  }\n\n  return {\n    dataPreProcess:     function(f) { if (f) pre = f; return pre },\n    dataPostProcess:    function(f) { if (f) post = f; return post },\n    data:               function(d,i) { if (d) setData(d,i); return datasets[i||0] },\n    visId:              visId,\n    build:              buildAll,\n    rebuild:            updateAll,\n    charts:             charts\n  }\n}\n\n// Data Tables /////////////////////////////////////////////////////////////////////////////////////\n\nvar table1 = {\n   summarized: false,\n   names: ['FirstName', 'pd_count', 'CompanyName'], \n   options: ['string', 'numeric', 'string'], \n   rows: [['Janet', 1, 'Berglunds snabbk\u00f6p'], ['Andrew', 1, 'Ricardo Adocicados'],\n  ['Nancy', 1, 'Rattlesnake Canyon Grocery'], ['Michael', 1, 'Rancho grande'],\n  ['Anne', 1, 'Rancho grande'], ['Laura', 1, 'Queen Cozinha'],\n  ['Andrew', 1, 'Pericles Comidas cl\u00e1sicas'], ['Anne', 1, 'Maison Dewey'],\n  ['Andrew', 1, 'Lehmanns Marktstand'], ['Robert', 1, \"La maison d'Asie\"],\n  ['Margaret', 1, \"La corne d'abondance\"], ['Nancy', 1, 'LINO-Delicateses'],\n  ['Laura', 1, 'LILA-Supermercado'], ['Robert', 1, 'Ernst Handel'], ['Margaret', 1, 'Ernst Handel'],\n  ['Anne', 1, 'B\u00f3lido Comidas preparadas'], ['Anne', 1, 'Blauer See Delikatessen'],\n  ['Robert', 1, 'Simons bistro'], ['Laura', 1, 'Wolski  Zajazd'],\n  ['Margaret', 3, 'Great Lakes Food Market']]\n};\n\n// Call Code to Build the system ///////////////////////////////////////////////////////////////////\n\nvar v  = new BrunelVis('visid1db56162-4cf1-11e8-be5d-86c880cdd486');\nv.build(table1);\n\n    });\n});</script>\n        </div>\n    ", 
                        "text/plain": "<IPython.core.display.HTML object>"
                    }, 
                    "metadata": {}
                }
            ], 
            "source": "from pixiedust.display import *\ndisplay(result_df)"
        }, 
        {
            "source": "#### <a id=\"lowlevel\"></a> 4.1 Low level SQL job submission\nLet's run the same SQL again, but this time using the asynchronous submission mechanism and the status check method.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 10, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "SQL query submitted and running in the background. jobId = a94962de-f901-4a8a-bfe9-eeb73d81aa98\n"
                }
            ], 
            "source": "sqlClient.logon()\njobId = sqlClient.submit_sql(sql)\nprint(\"SQL query submitted and running in the background. jobId = \" + jobId)"
        }, 
        {
            "execution_count": 11, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Job status for a94962de-f901-4a8a-bfe9-eeb73d81aa98: running\n"
                }
            ], 
            "source": "print(\"Job status for \" + jobId + \": \" + sqlClient.get_job(jobId)['status'])"
        }, 
        {
            "source": "Use the `wait_for_job()` method as a blocking call until your job has finished:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 12, 
            "cell_type": "code", 
            "metadata": {
                "scrolled": true
            }, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Job a94962de-f901-4a8a-bfe9-eeb73d81aa98 terminated with status: completed\n"
                }
            ], 
            "source": "job_status = sqlClient.wait_for_job(jobId)\nprint(\"Job \" + jobId + \" terminated with status: \" + job_status)\nif job_status == 'failed':\n    details = sqlClient.get_job(jobId)\n    print(\"Error: {}\\nError Message: {}\".format(details['error'], details['error_message']))"
        }, 
        {
            "source": "Use the `get_result()` method to retrieve a dataframe for the SQL result set:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 13, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "OK, we have a dataframe for the SQL result that has been stored by SQL Query in cos://s3-api.us-geo.objectstorage.softlayer.net/projectparkhsu02d1be16ce7e164fc694f1c69332955f0b/jobid=a94962de-f901-4a8a-bfe9-eeb73d81aa98\n"
                }
            ], 
            "source": "result_df = sqlClient.get_result(jobId)\nprint(\"OK, we have a dataframe for the SQL result that has been stored by SQL Query in \" + sqlClient.get_job(jobId)['resultset_location'])"
        }, 
        {
            "source": "You can delete the result set from Cloud Object Storage using the `delete_result()` method:", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 14, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 14, 
                    "metadata": {}, 
                    "data": {
                        "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>Deleted Object</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>jobid=a94962de-f901-4a8a-bfe9-eeb73d81aa98/_SU...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>jobid=a94962de-f901-4a8a-bfe9-eeb73d81aa98</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>jobid=a94962de-f901-4a8a-bfe9-eeb73d81aa98/par...</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                                      Deleted Object\n0  jobid=a94962de-f901-4a8a-bfe9-eeb73d81aa98/_SU...\n1         jobid=a94962de-f901-4a8a-bfe9-eeb73d81aa98\n2  jobid=a94962de-f901-4a8a-bfe9-eeb73d81aa98/par..."
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "sqlClient.delete_result(jobId)"
        }, 
        {
            "source": "### <a id=\"joblist\"></a> 5. Listing recent SQL submissions\nThe method `get_jobs()` provides you a dataframe with all recent SQL submissions with all details. You can change the value `-1`for `display.max_colwidth` to a positive integer if you want to truncate the cell content to shrink the overall table display size.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 15, 
            "cell_type": "code", 
            "metadata": {
                "scrolled": false
            }, 
            "outputs": [
                {
                    "execution_count": 15, 
                    "metadata": {}, 
                    "data": {
                        "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>job_id</th>\n      <th>status</th>\n      <th>user_id</th>\n      <th>statement</th>\n      <th>resultset_location</th>\n      <th>submit_time</th>\n      <th>end_time</th>\n      <th>error</th>\n      <th>error_message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a94962de-f901-4a8a-bfe9-eeb73d81aa98</td>\n      <td>completed</td>\n      <td>parkhsu@kr.ibm.com</td>\n      <td>SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName FROM cos://us-geo/sql/orders.parquet STORED AS PARQUET o, cos://us-geo/sql/employees.parquet STORED AS PARQUET e, cos://us-geo/sql/customers.parquet STORED AS PARQUET c WHERE e.EmployeeID = o.EmployeeID AND c.CustomerID = o.CustomerID AND o.ShippedDate &gt; o.RequiredDate AND o.OrderDate &gt; \"1998-01-01\" ORDER BY c.CompanyName</td>\n      <td>cos://s3-api.us-geo.objectstorage.softlayer.net/projectparkhsu02d1be16ce7e164fc694f1c69332955f0b/jobid=a94962de-f901-4a8a-bfe9-eeb73d81aa98</td>\n      <td>2018-05-01T02:28:42.440Z</td>\n      <td>2018-05-01T02:29:09.630Z</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>bcc6a50d-2fca-4ad1-8e08-05eacf9553bb</td>\n      <td>completed</td>\n      <td>parkhsu@kr.ibm.com</td>\n      <td>SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName FROM cos://us-geo/sql/orders.parquet STORED AS PARQUET o, cos://us-geo/sql/employees.parquet STORED AS PARQUET e, cos://us-geo/sql/customers.parquet STORED AS PARQUET c WHERE e.EmployeeID = o.EmployeeID AND c.CustomerID = o.CustomerID AND o.ShippedDate &gt; o.RequiredDate AND o.OrderDate &gt; \"1998-01-01\" ORDER BY c.CompanyName</td>\n      <td>cos://s3-api.us-geo.objectstorage.softlayer.net/projectparkhsu02d1be16ce7e164fc694f1c69332955f0b/jobid=bcc6a50d-2fca-4ad1-8e08-05eacf9553bb</td>\n      <td>2018-05-01T02:27:05.181Z</td>\n      <td>2018-05-01T02:27:32.212Z</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                                 job_id     status             user_id  \\\n0  a94962de-f901-4a8a-bfe9-eeb73d81aa98  completed  parkhsu@kr.ibm.com   \n1  bcc6a50d-2fca-4ad1-8e08-05eacf9553bb  completed  parkhsu@kr.ibm.com   \n\n                                                                                                                                                                                                                                                                                                                                                                                      statement  \\\n0  SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName FROM cos://us-geo/sql/orders.parquet STORED AS PARQUET o, cos://us-geo/sql/employees.parquet STORED AS PARQUET e, cos://us-geo/sql/customers.parquet STORED AS PARQUET c WHERE e.EmployeeID = o.EmployeeID AND c.CustomerID = o.CustomerID AND o.ShippedDate > o.RequiredDate AND o.OrderDate > \"1998-01-01\" ORDER BY c.CompanyName   \n1  SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName FROM cos://us-geo/sql/orders.parquet STORED AS PARQUET o, cos://us-geo/sql/employees.parquet STORED AS PARQUET e, cos://us-geo/sql/customers.parquet STORED AS PARQUET c WHERE e.EmployeeID = o.EmployeeID AND c.CustomerID = o.CustomerID AND o.ShippedDate > o.RequiredDate AND o.OrderDate > \"1998-01-01\" ORDER BY c.CompanyName   \n\n                                                                                                                            resultset_location  \\\n0  cos://s3-api.us-geo.objectstorage.softlayer.net/projectparkhsu02d1be16ce7e164fc694f1c69332955f0b/jobid=a94962de-f901-4a8a-bfe9-eeb73d81aa98   \n1  cos://s3-api.us-geo.objectstorage.softlayer.net/projectparkhsu02d1be16ce7e164fc694f1c69332955f0b/jobid=bcc6a50d-2fca-4ad1-8e08-05eacf9553bb   \n\n                submit_time                  end_time error error_message  \n0  2018-05-01T02:28:42.440Z  2018-05-01T02:29:09.630Z  None  None          \n1  2018-05-01T02:27:05.181Z  2018-05-01T02:27:32.212Z  None  None          "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "pd.set_option('display.max_colwidth', -1)\nsqlClient.get_jobs().head(100)"
        }, 
        {
            "source": "### <a id=\"other\"></a> 6. Useful things\n`ibmcloudsql` provides some other methods that can be quite handy when working with data on IBM Cloud Object Storage. One is `get_cos_summary()` that provides statistics for a given **URL** to object storage. So you can for instance check the amount of objects and data volume that will be processed by your SQL query when you use the URL in it. For demonstration purposes we will now simply get statistics for the `targeturl` that we used above as target for our query results.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 16, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 16, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "{'largest_object': 'notebook/03-unsupervised-learning_Ny5xlr35V.ipynb',\n 'largest_object_size': '25.3 MB',\n 'newest_object_timestamp': 'May 01, 2018, 02H:28M:43S',\n 'oldest_object_timestamp': 'April 02, 2018, 09H:27M:38S',\n 'smallest_object': 'jobid=bcc6a50d-2fca-4ad1-8e08-05eacf9553bb',\n 'smallest_object_size': '0.0 B',\n 'total_objects': 69,\n 'total_volume': '34.1 MB',\n 'url': 'cos://us-geo/projectparkhsu02d1be16ce7e164fc694f1c69332955f0b/'}"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "sqlClient.get_cos_summary(targeturl)"
        }, 
        {
            "source": "### <a id=\"next\"></a> 7. Next steps\nIn this notebook you learned how you can use the `ibmcloudsql` library in a Python notebook to submit SQL queries on data in IBM Cloud Object Storage and how you can interact with the query results. If you want to automate such an SQL query execution as part of your cloud solution, you can use the <a href=\"https://console.bluemix.net/openwhisk/\" target=\"_blank\">IBM Cloud Functions</a> framework. There is a dedicated SQL function available that lets you set up a cloud function to run SQL statements with IBM Cloud SQL Query. You can find the documentation for doing this <a href=\"https://hub.docker.com/r/ibmfunctions/sqlquery/\" target=\"_blank\" rel=\"noopener noreferrer\">here</a>.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "### <a id=\"authors\"></a>Authors\n\n**Torsten Steinbach**, Torsten is the lead architect for IBM Cloud SQL Query. Previously he has worked as IBM architect for a series of data management products and services, including DB2, PureData for Analytics and Db2 on Cloud.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "<hr>\nCopyright &copy; IBM Corp. 2018. This notebook and its source code are released under the terms of the MIT License.", 
            "cell_type": "markdown", 
            "metadata": {}
        }
    ], 
    "metadata": {
        "kernelspec": {
            "display_name": "Python 3.5", 
            "name": "python3", 
            "language": "python"
        }, 
        "language_info": {
            "mimetype": "text/x-python", 
            "nbconvert_exporter": "python", 
            "version": "3.5.4", 
            "name": "python", 
            "file_extension": ".py", 
            "pygments_lexer": "ipython3", 
            "codemirror_mode": {
                "version": 3, 
                "name": "ipython"
            }
        }
    }, 
    "nbformat": 4
}