adlmagics/00_adlmagics_demo.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "This file demo the `adlmagics` functions for Azure Data Lake job control and data exploration.\n",
    "## 1. Initilaizaion and Login"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AdlMagics initialized\r\n"
     ]
    }
   ],
   "source": [
    "%reload_ext adlmagics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "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 G2HKWX6E3 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": [
    "## 2. Enumerate Azure Data Lake Analytics accounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adl_accounts = %adl listaccounts --page_index 0 --page_account_number 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`%adl listaccounts` return results as Pandas DataFrames, you can call Pandas functions directly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "adl_accounts.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Enumerate Azure Data Lake jobs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Listing azure data lake jobs under account 'devtooltelemetryadla'...\r\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>    <caption>10 datalake analytics job(s) listed</caption>    <thead>        <tr>            <th>Job Name</th>            <th>Submitter</th>            <th>State</th>            <th>Result</th>            <th></th>        </tr>    </thead>    <tbody>        <tr title='Parallelism: 5\r\n",
       "Priority: 100\r\n",
       "Submit Time: 2018-05-07 07:53:07.696919+00:00\r\n",
       "Start Time: 2018-05-07 07:53:37.385134+00:00\r\n",
       "End Time: 2018-05-07 07:54:00.760707+00:00\r\n",
       "'>            <td>query_a_tsv_sample</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>succeeded</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/defe0ecb-a8f8-46e7-a4d4-0121db2bbfeb' target='_blank'>View Job</a></td>        </tr>        <tr title='Parallelism: 5\r\n",
       "Priority: 100\r\n",
       "Submit Time: 2018-06-13 02:28:06.610250+00:00\r\n",
       "Start Time: 2018-06-13 02:28:31.188645+00:00\r\n",
       "End Time: 2018-06-13 02:29:05.939054+00:00\r\n",
       "'>            <td>create_rowset</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>succeeded</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/ad8eca1e-2a4f-4e15-be2a-084be0d96a8e' target='_blank'>View Job</a></td>        </tr>        <tr title='Parallelism: 5\r\n",
       "Priority: 100\r\n",
       "Submit Time: 2018-06-12 07:01:03.964704+00:00\r\n",
       "Start Time: 2018-06-12 07:01:25.652443+00:00\r\n",
       "End Time: 2018-06-12 07:02:11.231078+00:00\r\n",
       "'>            <td>query_a_tsv_sample</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>succeeded</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/2a96e23d-53e4-4b04-bb74-24dad7d914e0' target='_blank'>View Job</a></td>        </tr>        <tr title='Parallelism: 5\r\n",
       "Priority: 1000\r\n",
       "Submit Time: 2018-01-19 09:41:33.735143+00:00\r\n",
       "Start Time: 2018-01-19 09:43:26.751810+00:00\r\n",
       "End Time: 2018-01-19 09:46:28.222266+00:00\r\n",
       "'>            <td>r_cbh</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>failed</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/2b229466-5aba-45cb-afa9-26243675baa7' target='_blank'>View Job</a></td>        </tr>        <tr title='Parallelism: 5\r\n",
       "Priority: 100\r\n",
       "Submit Time: 2018-06-13 02:27:59.953891+00:00\r\n",
       "Start Time: 2018-06-13 02:28:24.219815+00:00\r\n",
       "End Time: 2018-06-13 02:29:02.470257+00:00\r\n",
       "'>            <td>populate_tbl</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>succeeded</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/613c1543-6874-456d-9fdb-336b60c39824' target='_blank'>View Job</a></td>        </tr>        <tr title='Parallelism: 5\r\n",
       "Priority: 100\r\n",
       "Submit Time: 2018-05-07 08:23:01.601465+00:00\r\n",
       "Start Time: 2018-05-07 08:23:33.805361+00:00\r\n",
       "End Time: 2018-05-07 08:23:57.305923+00:00\r\n",
       "'>            <td>query_tbl</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>succeeded</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/71147a3a-6f88-4314-9002-337fae729102' target='_blank'>View Job</a></td>        </tr>        <tr title='Parallelism: 5\r\n",
       "Priority: 100\r\n",
       "Submit Time: 2018-06-13 02:28:02.375797+00:00\r\n",
       "Start Time: 2018-06-13 02:28:24.219815+00:00\r\n",
       "End Time: 2018-06-13 02:29:09.439086+00:00\r\n",
       "'>            <td>query_tbl</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>succeeded</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/bee8f2a5-c1d2-4b12-ac2c-3db4af1aeac4' target='_blank'>View Job</a></td>        </tr>        <tr title='Parallelism: 5\r\n",
       "Priority: 100\r\n",
       "Submit Time: 2018-06-07 10:04:57.074140+00:00\r\n",
       "Start Time: 2018-06-07 10:05:20.386871+00:00\r\n",
       "End Time: 2018-06-07 10:05:52.496589+00:00\r\n",
       "'>            <td>testjob_via_dl_app_20180411_by_dvttelsa</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>succeeded</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/c678feba-1916-4b5d-be03-510739e83285' target='_blank'>View Job</a></td>        </tr>        <tr title='Parallelism: 5\r\n",
       "Priority: 100\r\n",
       "Submit Time: 2018-05-07 08:41:26.034943+00:00\r\n",
       "Start Time: 2018-05-07 08:41:54.394974+00:00\r\n",
       "End Time: 2018-05-07 08:42:18.958061+00:00\r\n",
       "'>            <td>create_rowset</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>succeeded</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/b38dc779-ccaf-4269-92ef-5df91ad765c1' target='_blank'>View Job</a></td>        </tr>        <tr title='Parallelism: 5\r\n",
       "Priority: 100\r\n",
       "Submit Time: 2018-06-12 09:04:46.783978+00:00\r\n",
       "Start Time: 2018-06-12 09:05:05.143577+00:00\r\n",
       "End Time: 2018-06-12 09:05:19.315604+00:00\r\n",
       "'>            <td>create_db_tbl</td>            <td>ruxu@microsoft.com</td>            <td>ended</td>            <td>succeeded</td>            <td><a href='https://devtooltelemetryadla.azuredatalakeanalytics.net/jobLink/804cb781-e9f6-47fc-b26e-73f1bbfb5e41' target='_blank'>View Job</a></td>        </tr>    </tbody></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "jobs = %adl listjobs --account devtooltelemetryadla --page_index 0 --page_job_number 10 --my"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "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>defe0ecb-a8f8-46e7-a4d4-0121db2bbfeb</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-05-07 07:53:07.696919+00:00</td>\n",
       "      <td>2018-05-07 07:53:37.385134+00:00</td>\n",
       "      <td>2018-05-07 07:54:00.760707+00:00</td>\n",
       "      <td>ended</td>\n",
       "      <td>succeeded</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ad8eca1e-2a4f-4e15-be2a-084be0d96a8e</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-06-13 02:28:06.610250+00:00</td>\n",
       "      <td>2018-06-13 02:28:31.188645+00:00</td>\n",
       "      <td>2018-06-13 02:29:05.939054+00:00</td>\n",
       "      <td>ended</td>\n",
       "      <td>succeeded</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2b229466-5aba-45cb-afa9-26243675baa7</td>\n",
       "      <td>r_cbh</td>\n",
       "      <td>usql</td>\n",
       "      <td>ruxu@microsoft.com</td>\n",
       "      <td>5</td>\n",
       "      <td>1000</td>\n",
       "      <td>2018-01-19 09:41:33.735143+00:00</td>\n",
       "      <td>2018-01-19 09:43:26.751810+00:00</td>\n",
       "      <td>2018-01-19 09:46:28.222266+00:00</td>\n",
       "      <td>ended</td>\n",
       "      <td>failed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>613c1543-6874-456d-9fdb-336b60c39824</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-13 02:27:59.953891+00:00</td>\n",
       "      <td>2018-06-13 02:28:24.219815+00:00</td>\n",
       "      <td>2018-06-13 02:29:02.470257+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  defe0ecb-a8f8-46e7-a4d4-0121db2bbfeb  query_a_tsv_sample  usql   \n",
       "1  ad8eca1e-2a4f-4e15-be2a-084be0d96a8e       create_rowset  usql   \n",
       "2  2a96e23d-53e4-4b04-bb74-24dad7d914e0  query_a_tsv_sample  usql   \n",
       "3  2b229466-5aba-45cb-afa9-26243675baa7               r_cbh  usql   \n",
       "4  613c1543-6874-456d-9fdb-336b60c39824        populate_tbl  usql   \n",
       "\n",
       "            submitter  parallelism  priority  \\\n",
       "0  ruxu@microsoft.com            5       100   \n",
       "1  ruxu@microsoft.com            5       100   \n",
       "2  ruxu@microsoft.com            5       100   \n",
       "3  ruxu@microsoft.com            5      1000   \n",
       "4  ruxu@microsoft.com            5       100   \n",
       "\n",
       "                        submit_time                        start_time  \\\n",
       "0  2018-05-07 07:53:07.696919+00:00  2018-05-07 07:53:37.385134+00:00   \n",
       "1  2018-06-13 02:28:06.610250+00:00  2018-06-13 02:28:31.188645+00:00   \n",
       "2  2018-06-12 07:01:03.964704+00:00  2018-06-12 07:01:25.652443+00:00   \n",
       "3  2018-01-19 09:41:33.735143+00:00  2018-01-19 09:43:26.751810+00:00   \n",
       "4  2018-06-13 02:27:59.953891+00:00  2018-06-13 02:28:24.219815+00:00   \n",
       "\n",
       "                           end_time  state     result  \n",
       "0  2018-05-07 07:54:00.760707+00:00  ended  succeeded  \n",
       "1  2018-06-13 02:29:05.939054+00:00  ended  succeeded  \n",
       "2  2018-06-12 07:02:11.231078+00:00  ended  succeeded  \n",
       "3  2018-01-19 09:46:28.222266+00:00  ended     failed  \n",
       "4  2018-06-13 02:29:02.470257+00:00  ended  succeeded  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jobs.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Submit Azure Data Lake job"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submitting azure data lake job to account 'devtooltelemetryadla'...\n",
      "Job submitted.\n",
      "\tId: d393386b-9b4c-47a2-8a10-340aa056bdc1\n",
      "\tName: testjob_via_dl_app_20180411_by_dvttelsa\n",
      "\tType: usql\n",
      "\tSubmitter: ruxu@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-06-13 04:57:42.513894+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 0x3dd4430>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%adl submitjob --account devtooltelemetryadla --name testjob_via_dl_app_20180411_by_dvttelsa --parallelism 5 --priority 100 --runtime default\n",
    "\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": "markdown",
   "metadata": {},
   "source": [
    "## 5. View Azure Data Lake job details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Viewing azure data lake job by id '36a62f78-1881-4895-8a6a-9e37b497582d' under account 'devtooltelemetryadla'...\n",
      "Azure data lake job info:\n",
      "\tName: testjob_via_dl_app_20180411_by_dvttelsa\n",
      "\tType: usql\n",
      "\tSubmitter: dvttelsa@microsoft.com\n",
      "\tParallelism: 5\n",
      "\tPriority: 100\n",
      "\tSubmit time: 2018-04-11 04:42:18.649130+00:00\n",
      "\tStart time: 2018-04-11 04:44:13.493305+00:00\n",
      "\tEnd time: 2018-04-11 04:44:42.837144+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>36a62f78-1881-4895-8a6a-9e37b497582d</td>\n",
       "      <td>testjob_via_dl_app_20180411_by_dvttelsa</td>\n",
       "      <td>usql</td>\n",
       "      <td>dvttelsa@microsoft.com</td>\n",
       "      <td>5</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-04-11 04:42:18.649130+00:00</td>\n",
       "      <td>2018-04-11 04:44:13.493305+00:00</td>\n",
       "      <td>2018-04-11 04:44:42.837144+00:00</td>\n",
       "      <td>ended</td>\n",
       "      <td>succeeded</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     id  \\\n",
       "0  36a62f78-1881-4895-8a6a-9e37b497582d   \n",
       "\n",
       "                                      name  type               submitter  \\\n",
       "0  testjob_via_dl_app_20180411_by_dvttelsa  usql  dvttelsa@microsoft.com   \n",
       "\n",
       "   parallelism  priority                      submit_time  \\\n",
       "0            5       100 2018-04-11 04:42:18.649130+00:00   \n",
       "\n",
       "                        start_time                         end_time  state  \\\n",
       "0 2018-04-11 04:44:13.493305+00:00 2018-04-11 04:44:42.837144+00:00  ended   \n",
       "\n",
       "      result  \n",
       "0  succeeded  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%adl viewjob --account devtooltelemetryadla --job_id 36a62f78-1881-4895-8a6a-9e37b497582d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Enumerate Azure Data Lake Store accounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Listing azure data lake store accounts...\n",
      "(5) azure data lake store account(s) listed.\n",
      "abcderfgadls\n",
      "accounttestaccessadls\n",
      "adfadla01adls\n",
      "adlaperf02\n",
      "adlau18acltest\n"
     ]
    }
   ],
   "source": [
    "adls_accounts = %adl liststoreaccounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "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>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>abcderfgadls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>accounttestaccessadls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>adfadla01adls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>adlaperf02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>adlau18acltest</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    name\n",
       "0           abcderfgadls\n",
       "1  accounttestaccessadls\n",
       "2          adfadla01adls\n",
       "3             adlaperf02\n",
       "4         adlau18acltest"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adls_accounts.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. List folders or files for a given path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Listing azure data lake store folders under folder 'vifeng' of account 'devtooltelemetryadls'...\r\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>    <caption>3 datalake store folder(s) listed</caption>    <thead>        <tr>            <th>Folder Name</th>            <th>Last Access Time</th>            <th>Last Modified Time</th>            <th>Path</th>        </tr>    </thead>    <tbody>        <tr>            <td>FailureVertexDebug</td>            <td>2017-06-27 13:10:48.540000</td>            <td>2017-06-27 13:10:59.696000</td>            <td>vifeng/FailureVertexDebug</td>        </tr>        <tr>            <td>MonthlyUserIds</td>            <td>2017-07-06 14:52:33.990000</td>            <td>2017-07-06 17:04:35.161000</td>            <td>vifeng/MonthlyUserIds</td>        </tr>        <tr>            <td>udo</td>            <td>2018-04-17 15:39:36.051000</td>            <td>2018-04-17 15:40:01.964000</td>            <td>vifeng/udo</td>        </tr>    </tbody></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "folders = %adl liststorefolders --account devtooltelemetryadls --folder_path vifeng"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "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>name</th>\n",
       "      <th>path</th>\n",
       "      <th>last_access_time</th>\n",
       "      <th>last_modified_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FailureVertexDebug</td>\n",
       "      <td>vifeng/FailureVertexDebug</td>\n",
       "      <td>2017-06-27 13:10:48.540</td>\n",
       "      <td>2017-06-27 13:10:59.696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MonthlyUserIds</td>\n",
       "      <td>vifeng/MonthlyUserIds</td>\n",
       "      <td>2017-07-06 14:52:33.990</td>\n",
       "      <td>2017-07-06 17:04:35.161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>udo</td>\n",
       "      <td>vifeng/udo</td>\n",
       "      <td>2018-04-17 15:39:36.051</td>\n",
       "      <td>2018-04-17 15:40:01.964</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 name                       path        last_access_time  \\\n",
       "0  FailureVertexDebug  vifeng/FailureVertexDebug 2017-06-27 13:10:48.540   \n",
       "1      MonthlyUserIds      vifeng/MonthlyUserIds 2017-07-06 14:52:33.990   \n",
       "2                 udo                 vifeng/udo 2018-04-17 15:39:36.051   \n",
       "\n",
       "       last_modified_time  \n",
       "0 2017-06-27 13:10:59.696  \n",
       "1 2017-07-06 17:04:35.161  \n",
       "2 2018-04-17 15:40:01.964  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "folders.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Listing azure data lake store files under folder 'vifeng' of account 'devtooltelemetryadls'...\r\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>    <caption>8 datalake store file(s) listed</caption>    <thead>        <tr>            <th>File Name</th>            <th>Size (Bytes)</th>            <th>Last Access Time</th>            <th>Last Modified Time</th>            <th>Path</th>        </tr>    </thead>    <tbody>        <tr>            <td>ASAUnmappedEventCounts.tsv</td>            <td>3178</td>            <td>2018-03-27 13:23:46.686000</td>            <td>2018-03-27 13:23:46.750000</td>            <td>vifeng/ASAUnmappedEventCounts.tsv</td>        </tr>        <tr>            <td>AdlUnmappedEventCounts.tsv</td>            <td>13023</td>            <td>2018-03-27 00:05:48.446000</td>            <td>2018-03-27 00:05:48.495000</td>            <td>vifeng/AdlUnmappedEventCounts.tsv</td>        </tr>        <tr>            <td>AdlaMonthlyJobCountsVsCode_201708.tsv</td>            <td>76890</td>            <td>2017-08-10 16:22:47.630000</td>            <td>2017-08-10 16:22:47.755000</td>            <td>vifeng/AdlaMonthlyJobCountsVsCode_201708.tsv</td>        </tr>        <tr>            <td>AllEventNames.tsv</td>            <td>50399</td>            <td>2018-03-27 00:03:33.226000</td>            <td>2018-03-27 00:03:33.277000</td>            <td>vifeng/AllEventNames.tsv</td>        </tr>        <tr>            <td>DataLakeConsoleAppForJava.tsv</td>            <td>3505</td>            <td>2017-07-12 17:31:14.111000</td>            <td>2017-07-12 17:31:14.253000</td>            <td>vifeng/DataLakeConsoleAppForJava.tsv</td>        </tr>        <tr>            <td>HdiUnmappedEventCounts.tsv</td>            <td>5571</td>            <td>2018-03-27 13:28:30.830000</td>            <td>2018-03-27 13:28:30.893000</td>            <td>vifeng/HdiUnmappedEventCounts.tsv</td>        </tr>        <tr>            <td>orders_sample.tbl</td>            <td>2014</td>            <td>2017-09-29 14:00:09.638000</td>            <td>2017-09-29 14:00:10.227000</td>            <td>vifeng/orders_sample.tbl</td>        </tr>        <tr>            <td>testudo.py</td>            <td>2950</td>            <td>2017-09-29 14:00:02.223000</td>            <td>2017-09-29 14:00:02.743000</td>            <td>vifeng/testudo.py</td>        </tr>    </tbody></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "files = %adl liststorefiles --account devtooltelemetryadls --folder_path vifeng"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "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>name</th>\n",
       "      <th>path</th>\n",
       "      <th>size_in_bytes</th>\n",
       "      <th>last_access_time</th>\n",
       "      <th>last_modified_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASAUnmappedEventCounts.tsv</td>\n",
       "      <td>vifeng/ASAUnmappedEventCounts.tsv</td>\n",
       "      <td>3178</td>\n",
       "      <td>2018-03-27 13:23:46.686</td>\n",
       "      <td>2018-03-27 13:23:46.750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AdlUnmappedEventCounts.tsv</td>\n",
       "      <td>vifeng/AdlUnmappedEventCounts.tsv</td>\n",
       "      <td>13023</td>\n",
       "      <td>2018-03-27 00:05:48.446</td>\n",
       "      <td>2018-03-27 00:05:48.495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AdlaMonthlyJobCountsVsCode_201708.tsv</td>\n",
       "      <td>vifeng/AdlaMonthlyJobCountsVsCode_201708.tsv</td>\n",
       "      <td>76890</td>\n",
       "      <td>2017-08-10 16:22:47.630</td>\n",
       "      <td>2017-08-10 16:22:47.755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AllEventNames.tsv</td>\n",
       "      <td>vifeng/AllEventNames.tsv</td>\n",
       "      <td>50399</td>\n",
       "      <td>2018-03-27 00:03:33.226</td>\n",
       "      <td>2018-03-27 00:03:33.277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DataLakeConsoleAppForJava.tsv</td>\n",
       "      <td>vifeng/DataLakeConsoleAppForJava.tsv</td>\n",
       "      <td>3505</td>\n",
       "      <td>2017-07-12 17:31:14.111</td>\n",
       "      <td>2017-07-12 17:31:14.253</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    name  \\\n",
       "0             ASAUnmappedEventCounts.tsv   \n",
       "1             AdlUnmappedEventCounts.tsv   \n",
       "2  AdlaMonthlyJobCountsVsCode_201708.tsv   \n",
       "3                      AllEventNames.tsv   \n",
       "4          DataLakeConsoleAppForJava.tsv   \n",
       "\n",
       "                                           path  size_in_bytes  \\\n",
       "0             vifeng/ASAUnmappedEventCounts.tsv           3178   \n",
       "1             vifeng/AdlUnmappedEventCounts.tsv          13023   \n",
       "2  vifeng/AdlaMonthlyJobCountsVsCode_201708.tsv          76890   \n",
       "3                      vifeng/AllEventNames.tsv          50399   \n",
       "4          vifeng/DataLakeConsoleAppForJava.tsv           3505   \n",
       "\n",
       "         last_access_time      last_modified_time  \n",
       "0 2018-03-27 13:23:46.686 2018-03-27 13:23:46.750  \n",
       "1 2018-03-27 00:05:48.446 2018-03-27 00:05:48.495  \n",
       "2 2017-08-10 16:22:47.630 2017-08-10 16:22:47.755  \n",
       "3 2018-03-27 00:03:33.226 2018-03-27 00:03:33.277  \n",
       "4 2017-07-12 17:31:14.111 2017-07-12 17:31:14.253  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Sample data to Pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sampling data lake store file '/Samples/Data/SearchLog.tsv'...\n",
      "(5) row(s) sampled.\n",
      "['\\ufeff399266', '2/15/2012 11:53:16 AM', 'en-us', 'how to make nachos', '73', 'www.nachos.com;www.wikipedia.com', 'NULL']\n",
      "['382045', '2/15/2012 11:53:18 AM', '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', '2/16/2012 11:53:20 AM', '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', '2/16/2012 11:53:50 AM', 'en-ca', 'south park episodes', '24', 'southparkstudios.com;wikipedia.org/wiki/Sout_Park;imdb.com/title/tt0121955;simon.com/mall', 'southparkstudios.com']\n",
      "['906441', '2/16/2012 11:54:01 AM', '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/Data/SearchLog.tsv --file_type tsv --encoding utf-8 --row_number 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>﻿399266</td>\n",
       "      <td>2/15/2012 11:53:16 AM</td>\n",
       "      <td>en-us</td>\n",
       "      <td>how to make nachos</td>\n",
       "      <td>73</td>\n",
       "      <td>www.nachos.com;www.wikipedia.com</td>\n",
       "      <td>NULL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>382045</td>\n",
       "      <td>2/15/2012 11:53:18 AM</td>\n",
       "      <td>en-gb</td>\n",
       "      <td>best ski resorts</td>\n",
       "      <td>614</td>\n",
       "      <td>skiresorts.com;ski-europe.com;www.travelersdig...</td>\n",
       "      <td>ski-europe.com;www.travelersdigest.com/ski_res...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         0                      1      2                   3    4  \\\n",
       "0  ﻿399266  2/15/2012 11:53:16 AM  en-us  how to make nachos   73   \n",
       "1   382045  2/15/2012 11:53:18 AM  en-gb    best ski resorts  614   \n",
       "\n",
       "                                                   5  \\\n",
       "0                   www.nachos.com;www.wikipedia.com   \n",
       "1  skiresorts.com;ski-europe.com;www.travelersdig...   \n",
       "\n",
       "                                                   6  \n",
       "0                                               NULL  \n",
       "1  ski-europe.com;www.travelersdigest.com/ski_res...  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_data.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Logout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User 'ruxu@microsoft.com' logged out of tenant 'microsoft.onmicrosoft.com'\r\n"
     ]
    }
   ],
   "source": [
    "%adl logout"
   ]
  }
 ],
 "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
}