adlmagics/01_usql_samples.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# U-SQL samples\n",
    "These samples cover some very common U-SQL scenarios, e.g. query a TSV file, create a database, populate table, query table and create rowset in script. \n",
    "All the samples here are also avaliable in the Azure Portal. \n",
    "## 0. Initialize ADL Magics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AdlMagics initialized\r\n"
     ]
    }
   ],
   "source": [
    "%reload_ext adlmagics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting for user login...\n",
      "To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code GHHCBVD7H to authenticate.\n",
      "User 'ruxu@microsoft.com' logged in to tenant 'microsoft.onmicrosoft.com'\n"
     ]
    }
   ],
   "source": [
    "%adl login --tenant microsoft.onmicrosoft.com"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Query a TSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submitting azure data lake job to account 'devtooltelemetryadla'...\n",
      "Job submitted.\n",
      "\tId: 290fe596-64c1-4e44-a8c3-98aa70264503\n",
      "\tName: query_a_tsv_sample\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-13 02:27:52.219458+00:00\n",
      "\tStart time: None\n",
      "\tEnd time: None\n",
      "\tState: compiling\n",
      "\tResult: none\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<adlmagics.models.adla_job.AdlaJob at 0x457a210>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%adl submitjob --account devtooltelemetryadla --name query_a_tsv_sample --parallelism 5 --priority 100 --runtime default\n",
    "\n",
    "//Define schema of file, must map all columns\n",
    "@searchlog =\n",
    "    EXTRACT UserId int,\n",
    "            Start DateTime,\n",
    "            Region string,\n",
    "            Query string,\n",
    "            Duration int?,\n",
    "            Urls string,\n",
    "            ClickedUrls string\n",
    "    FROM \"/Samples/Data/SearchLog.tsv\"\n",
    "    USING Extractors.Tsv();\n",
    "\n",
    "OUTPUT @searchlog\n",
    "TO \"/Samples/AdlMagicsDemoSearchLogResult.csv\"\n",
    "USING Outputters.Csv();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Viewing azure data lake job by id '2a96e23d-53e4-4b04-bb74-24dad7d914e0' under account 'devtooltelemetryadla'...\n",
      "Azure data lake job info:\n",
      "\tName: query_a_tsv_sample\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-12 07:01:03.964704+00:00\n",
      "\tStart time: 2018-06-12 07:01:25.652443+00:00\n",
      "\tEnd time: 2018-06-12 07:02:11.231078+00:00\n",
      "\tState: ended\n",
      "\tResult: succeeded\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>submitter</th>\n",
       "      <th>parallelism</th>\n",
       "      <th>priority</th>\n",
       "      <th>submit_time</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>state</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2a96e23d-53e4-4b04-bb74-24dad7d914e0</td>\n",
       "      <td>query_a_tsv_sample</td>\n",
       "      <td>usql</td>\n",
       "      <td>ruxu@microsoft.com</td>\n",
       "      <td>5</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-06-12 07:01:03.964704+00:00</td>\n",
       "      <td>2018-06-12 07:01:25.652443+00:00</td>\n",
       "      <td>2018-06-12 07:02:11.231078+00:00</td>\n",
       "      <td>ended</td>\n",
       "      <td>succeeded</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id                name  type  \\\n",
       "0  2a96e23d-53e4-4b04-bb74-24dad7d914e0  query_a_tsv_sample  usql   \n",
       "\n",
       "            submitter  parallelism  priority                      submit_time  \\\n",
       "0  ruxu@microsoft.com            5       100 2018-06-12 07:01:03.964704+00:00   \n",
       "\n",
       "                        start_time                         end_time  state  \\\n",
       "0 2018-06-12 07:01:25.652443+00:00 2018-06-12 07:02:11.231078+00:00  ended   \n",
       "\n",
       "      result  \n",
       "0  succeeded  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%adl viewjob --account devtooltelemetryadla --job_id 2a96e23d-53e4-4b04-bb74-24dad7d914e0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sampling data lake store file '/Samples/AdlMagicsDemoSearchLogResult.csv'...\n",
      "(5) row(s) sampled.\n",
      "['399266', '2012-02-15T11:53:16.0000000', '\"en-us\"', '\"how to make nachos\"', '73', '\"www.nachos.com;www.wikipedia.com\"', '\"NULL\"']\n",
      "['382045', '2012-02-15T11:53:18.0000000', '\"en-gb\"', '\"best ski resorts\"', '614', '\"skiresorts.com;ski-europe.com;www.travelersdigest.com/ski_resorts.htm\"', '\"ski-europe.com;www.travelersdigest.com/ski_resorts.htm\"']\n",
      "['382045', '2012-02-16T11:53:20.0000000', '\"en-gb\"', '\"broken leg\"', '74', '\"mayoclinic.com/health;webmd.com/a-to-z-guides;mybrokenleg.com;wikipedia.com/Bone_fracture\"', '\"mayoclinic.com/health;webmd.com/a-to-z-guides;mybrokenleg.com;wikipedia.com/Bone_fracture\"']\n",
      "['106479', '2012-02-16T11:53:50.0000000', '\"en-ca\"', '\"south park episodes\"', '24', '\"southparkstudios.com;wikipedia.org/wiki/Sout_Park;imdb.com/title/tt0121955;simon.com/mall\"', '\"southparkstudios.com\"']\n",
      "['906441', '2012-02-16T11:54:01.0000000', '\"en-us\"', '\"cosmos\"', '1213', '\"cosmos.com;wikipedia.org/wiki/Cosmos:_A_Personal_Voyage;hulu.com/cosmos\"', '\"NULL\"']\n"
     ]
    }
   ],
   "source": [
    "sample_data = %adl sample --account devtooltelemetryadls --file_path /Samples/AdlMagicsDemoSearchLogResult.csv --file_type csv --encoding utf-8 --row_number 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Create Database and Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submitting azure data lake job to account 'devtooltelemetryadla'...\n",
      "Job submitted.\n",
      "\tId: 9c25487e-4214-449a-9b0b-c2cde334fbcb\n",
      "\tName: create_db_tbl\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-13 02:27:57.610157+00:00\n",
      "\tStart time: None\n",
      "\tEnd time: None\n",
      "\tState: compiling\n",
      "\tResult: none\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<adlmagics.models.adla_job.AdlaJob at 0x114a9fb0>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%adl submitjob --account devtooltelemetryadla --name create_db_tbl --parallelism 5 --priority 100 --runtime default\n",
    "\n",
    "//Create Database SampleDBTutorials\n",
    "CREATE DATABASE IF NOT EXISTS SampleDBTutorials;\n",
    "\n",
    "//Create Table OlympicAthletes\n",
    "CREATE TABLE IF NOT EXISTS SampleDBTutorials.dbo.SearchLog\n",
    "(\n",
    "        //Define schema of table\n",
    "        UserId          int, \n",
    "        Start           DateTime, \n",
    "        Region          string, \n",
    "        Query           string, \n",
    "        Duration        int, \n",
    "        Urls            string, \n",
    "        ClickedUrls     string,\n",
    "    INDEX idx1 //Name of index\n",
    "    CLUSTERED (Region ASC) //Column to cluster by\n",
    "    DISTRIBUTED BY HASH (Region) //Column to partition by\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Viewing azure data lake job by id '804cb781-e9f6-47fc-b26e-73f1bbfb5e41' under account 'devtooltelemetryadla'...\n",
      "Azure data lake job info:\n",
      "\tName: create_db_tbl\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-12 09:04:46.783978+00:00\n",
      "\tStart time: 2018-06-12 09:05:05.143577+00:00\n",
      "\tEnd time: 2018-06-12 09:05:19.315604+00:00\n",
      "\tState: ended\n",
      "\tResult: succeeded\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>submitter</th>\n",
       "      <th>parallelism</th>\n",
       "      <th>priority</th>\n",
       "      <th>submit_time</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>state</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>804cb781-e9f6-47fc-b26e-73f1bbfb5e41</td>\n",
       "      <td>create_db_tbl</td>\n",
       "      <td>usql</td>\n",
       "      <td>ruxu@microsoft.com</td>\n",
       "      <td>5</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-06-12 09:04:46.783978+00:00</td>\n",
       "      <td>2018-06-12 09:05:05.143577+00:00</td>\n",
       "      <td>2018-06-12 09:05:19.315604+00:00</td>\n",
       "      <td>ended</td>\n",
       "      <td>succeeded</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id           name  type  \\\n",
       "0  804cb781-e9f6-47fc-b26e-73f1bbfb5e41  create_db_tbl  usql   \n",
       "\n",
       "            submitter  parallelism  priority                      submit_time  \\\n",
       "0  ruxu@microsoft.com            5       100 2018-06-12 09:04:46.783978+00:00   \n",
       "\n",
       "                        start_time                         end_time  state  \\\n",
       "0 2018-06-12 09:05:05.143577+00:00 2018-06-12 09:05:19.315604+00:00  ended   \n",
       "\n",
       "      result  \n",
       "0  succeeded  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%adl viewjob --account devtooltelemetryadla --job_id 804cb781-e9f6-47fc-b26e-73f1bbfb5e41"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Populate Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submitting azure data lake job to account 'devtooltelemetryadla'...\n",
      "Job submitted.\n",
      "\tId: 613c1543-6874-456d-9fdb-336b60c39824\n",
      "\tName: populate_tbl\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-13 02:27:59.953891+00:00\n",
      "\tStart time: None\n",
      "\tEnd time: None\n",
      "\tState: compiling\n",
      "\tResult: none\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<adlmagics.models.adla_job.AdlaJob at 0xa4f4b0>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%adl submitjob --account devtooltelemetryadla --name populate_tbl --parallelism 5 --priority 100 --runtime default\n",
    "\n",
    "//Read some data\n",
    "@searchlog = \n",
    "    EXTRACT UserId          int, \n",
    "            Start           DateTime, \n",
    "            Region          string, \n",
    "            Query           string, \n",
    "            Duration        int, \n",
    "            Urls            string, \n",
    "            ClickedUrls     string\n",
    "    FROM @\"/Samples/Data/SearchLog.tsv\"\n",
    "    USING Extractors.Tsv();\n",
    "\n",
    "//Insert it into a previously created table\n",
    "INSERT INTO SampleDBTutorials.dbo.SearchLog\n",
    "SELECT *\n",
    "FROM @searchlog;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Viewing azure data lake job by id 'efe86318-2b5c-4726-af24-b1b686f0a270' under account 'devtooltelemetryadla'...\n",
      "Azure data lake job info:\n",
      "\tName: populate_tbl\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-12 09:10:58.272501+00:00\n",
      "\tStart time: 2018-06-12 09:11:22.444598+00:00\n",
      "\tEnd time: 2018-06-12 09:12:11.617046+00:00\n",
      "\tState: ended\n",
      "\tResult: succeeded\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>submitter</th>\n",
       "      <th>parallelism</th>\n",
       "      <th>priority</th>\n",
       "      <th>submit_time</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>state</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>efe86318-2b5c-4726-af24-b1b686f0a270</td>\n",
       "      <td>populate_tbl</td>\n",
       "      <td>usql</td>\n",
       "      <td>ruxu@microsoft.com</td>\n",
       "      <td>5</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-06-12 09:10:58.272501+00:00</td>\n",
       "      <td>2018-06-12 09:11:22.444598+00:00</td>\n",
       "      <td>2018-06-12 09:12:11.617046+00:00</td>\n",
       "      <td>ended</td>\n",
       "      <td>succeeded</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id          name  type  \\\n",
       "0  efe86318-2b5c-4726-af24-b1b686f0a270  populate_tbl  usql   \n",
       "\n",
       "            submitter  parallelism  priority                      submit_time  \\\n",
       "0  ruxu@microsoft.com            5       100 2018-06-12 09:10:58.272501+00:00   \n",
       "\n",
       "                        start_time                         end_time  state  \\\n",
       "0 2018-06-12 09:11:22.444598+00:00 2018-06-12 09:12:11.617046+00:00  ended   \n",
       "\n",
       "      result  \n",
       "0  succeeded  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%adl viewjob --account devtooltelemetryadla --job_id efe86318-2b5c-4726-af24-b1b686f0a270"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Query Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submitting azure data lake job to account 'devtooltelemetryadla'...\n",
      "Job submitted.\n",
      "\tId: bee8f2a5-c1d2-4b12-ac2c-3db4af1aeac4\n",
      "\tName: query_tbl\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-13 02:28:02.375797+00:00\n",
      "\tStart time: None\n",
      "\tEnd time: None\n",
      "\tState: compiling\n",
      "\tResult: none\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<adlmagics.models.adla_job.AdlaJob at 0x55a6390>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%adl submitjob --account devtooltelemetryadla --name query_tbl --parallelism 5 --priority 100 --runtime default\n",
    "\n",
    "//Read from SearchLog table\n",
    "@athletes =\n",
    "    SELECT *\n",
    "    FROM SampleDBTutorials.dbo.SearchLog;\n",
    "\n",
    "//Write it to a file so we can look at it\n",
    "OUTPUT @athletes\n",
    "TO @\"/Samples/Output/SearchLog_output.tsv\"\n",
    "USING Outputters.Tsv();\n",
    "\n",
    "//Alternatively, we can output the whole table to a file without using SELECT\n",
    "OUTPUT SampleDBTutorials.dbo.SearchLog\n",
    "TO @\"/Samples/Output/SearchLog_output_direct.tsv\"\n",
    "USING Outputters.Tsv();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Viewing azure data lake job by id '065043f7-a80e-449a-910a-d7b94fe0a4c8' under account 'devtooltelemetryadla'...\n",
      "Azure data lake job info:\n",
      "\tName: query_tbl\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-12 09:11:25.741512+00:00\n",
      "\tStart time: 2018-06-12 09:12:01.023151+00:00\n",
      "\tEnd time: 2018-06-12 09:12:36.773566+00:00\n",
      "\tState: ended\n",
      "\tResult: succeeded\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>submitter</th>\n",
       "      <th>parallelism</th>\n",
       "      <th>priority</th>\n",
       "      <th>submit_time</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>state</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>065043f7-a80e-449a-910a-d7b94fe0a4c8</td>\n",
       "      <td>query_tbl</td>\n",
       "      <td>usql</td>\n",
       "      <td>ruxu@microsoft.com</td>\n",
       "      <td>5</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-06-12 09:11:25.741512+00:00</td>\n",
       "      <td>2018-06-12 09:12:01.023151+00:00</td>\n",
       "      <td>2018-06-12 09:12:36.773566+00:00</td>\n",
       "      <td>ended</td>\n",
       "      <td>succeeded</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id       name  type           submitter  \\\n",
       "0  065043f7-a80e-449a-910a-d7b94fe0a4c8  query_tbl  usql  ruxu@microsoft.com   \n",
       "\n",
       "   parallelism  priority                      submit_time  \\\n",
       "0            5       100 2018-06-12 09:11:25.741512+00:00   \n",
       "\n",
       "                        start_time                         end_time  state  \\\n",
       "0 2018-06-12 09:12:01.023151+00:00 2018-06-12 09:12:36.773566+00:00  ended   \n",
       "\n",
       "      result  \n",
       "0  succeeded  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%adl viewjob --account devtooltelemetryadla --job_id 065043f7-a80e-449a-910a-d7b94fe0a4c8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sampling data lake store file '/Samples/Output/SearchLog_output.tsv'...\n",
      "(5) row(s) sampled.\n",
      "['106479', '2012-02-16T11:53:50.0000000', '\"en-ca\"', '\"south park episodes\"', '24', '\"southparkstudios.com;wikipedia.org/wiki/Sout_Park;imdb.com/title/tt0121955;simon.com/mall\"', '\"southparkstudios.com\"']\n",
      "['848434', '2012-02-16T12:12:35.0000000', '\"en-ch\"', '\"facebook\"', '10', '\"facebook.com;facebook.com/login;wikipedia.org/wiki/Facebook\"', '\"facebook.com\"']\n",
      "['354068', '2012-02-16T12:00:33.0000000', '\"en-mx\"', '\"what is sql\"', '422', '\"wikipedia.org/wiki/SQL;sqlcourse.com/intro.html;wikipedia.org/wiki/Microsoft_SQL\"', '\"wikipedia.org/wiki/SQL\"']\n",
      "['351530', '2012-02-16T11:54:01.0000000', '\"en-fr\"', '\"microsoft\"', '241', '\"microsoft.com;wikipedia.org/wiki/Microsoft;xbox.com\"', '\"NULL\"']\n",
      "['382045', '2012-02-16T11:53:20.0000000', '\"en-gb\"', '\"broken leg\"', '74', '\"mayoclinic.com/health;webmd.com/a-to-z-guides;mybrokenleg.com;wikipedia.com/Bone_fracture\"', '\"mayoclinic.com/health;webmd.com/a-to-z-guides;mybrokenleg.com;wikipedia.com/Bone_fracture\"']\n"
     ]
    }
   ],
   "source": [
    "sample_athletes = %adl sample --account devtooltelemetryadls --file_path /Samples/Output/SearchLog_output.tsv --file_type tsv --encoding utf-8 --row_number 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "wishlist:\n",
    "1. method to read data rather than sample data, read top xxx rows, or read the first xxx size?\n",
    "2. UI on the output data, table Guid or quick graph buttons."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Create Rowset in Script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submitting azure data lake job to account 'devtooltelemetryadla'...\n",
      "Job submitted.\n",
      "\tId: ad8eca1e-2a4f-4e15-be2a-084be0d96a8e\n",
      "\tName: create_rowset\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-13 02:28:06.610250+00:00\n",
      "\tStart time: None\n",
      "\tEnd time: None\n",
      "\tState: compiling\n",
      "\tResult: none\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<adlmagics.models.adla_job.AdlaJob at 0x37e52d0>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%adl submitjob --account devtooltelemetryadla --name create_rowset --parallelism 5 --priority 100 --runtime default\n",
    "\n",
    "@a  = \n",
    "    SELECT * FROM \n",
    "        (VALUES\n",
    "            (\"Contoso\",   123, 1500.0, new DateTime(1979,3,31) ),\n",
    "            (\"Woodgrove\", 456, 2700.0, new DateTime(1979,4,10) )\n",
    "        ) AS \n",
    "            D( customer, id, amount, date );\n",
    "\n",
    "OUTPUT @a \n",
    "    TO @\"/Samples/Output/Define_rowset_in_script_output.tsv\"\n",
    "    USING Outputters.Tsv();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Viewing azure data lake job by id 'b38dc779-ccaf-4269-92ef-5df91ad765c1' under account 'devtooltelemetryadla'...\n",
      "Azure data lake job info:\n",
      "\tName: create_rowset\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-05-07 08:41:26.034943+00:00\n",
      "\tStart time: 2018-05-07 08:41:54.394974+00:00\n",
      "\tEnd time: 2018-05-07 08:42:18.958061+00:00\n",
      "\tState: ended\n",
      "\tResult: succeeded\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>submitter</th>\n",
       "      <th>parallelism</th>\n",
       "      <th>priority</th>\n",
       "      <th>submit_time</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>state</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b38dc779-ccaf-4269-92ef-5df91ad765c1</td>\n",
       "      <td>create_rowset</td>\n",
       "      <td>usql</td>\n",
       "      <td>ruxu@microsoft.com</td>\n",
       "      <td>5</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-05-07 08:41:26.034943+00:00</td>\n",
       "      <td>2018-05-07 08:41:54.394974+00:00</td>\n",
       "      <td>2018-05-07 08:42:18.958061+00:00</td>\n",
       "      <td>ended</td>\n",
       "      <td>succeeded</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id           name  type  \\\n",
       "0  b38dc779-ccaf-4269-92ef-5df91ad765c1  create_rowset  usql   \n",
       "\n",
       "            submitter  parallelism  priority                      submit_time  \\\n",
       "0  ruxu@microsoft.com            5       100 2018-05-07 08:41:26.034943+00:00   \n",
       "\n",
       "                        start_time                         end_time  state  \\\n",
       "0 2018-05-07 08:41:54.394974+00:00 2018-05-07 08:42:18.958061+00:00  ended   \n",
       "\n",
       "      result  \n",
       "0  succeeded  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%adl viewjob --account devtooltelemetryadla --job_id b38dc779-ccaf-4269-92ef-5df91ad765c1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sampling data lake store file '/Samples/Output/Define_rowset_in_script_output.tsv'...\n",
      "(2) row(s) sampled.\n",
      "['\"Contoso\"', '123', '1500', '1979-03-31T00:00:00.0000000']\n",
      "['\"Woodgrove\"', '456', '2700', '1979-04-10T00:00:00.0000000']\n"
     ]
    }
   ],
   "source": [
    "sample_define_rowset_in_script = %adl sample --account devtooltelemetryadls --file_path /Samples/Output/Define_rowset_in_script_output.tsv --file_type tsv --encoding utf-8 --row_number 30"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}