tutorials/BigQuery/Importing and Exporting Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing and Exporting Data\n",
    "\n",
    "Data can be imported into Google BigQuery from a CSV file stored within Google Cloud Storage, or it can be streamed directly into BigQuery from Python code.\n",
    "\n",
    "Similarly, the results of a query can be exported to Google Cloud Storage as a set of shards, or they can be streamed directly into a file within Datalab. Note that for larger data sizes, it is recommended to choose the sharded method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from google.datalab import Context\n",
    "import google.datalab.bigquery as bq\n",
    "import google.datalab.storage as storage\n",
    "import pandas as pd\n",
    "try:\n",
    "  from StringIO import StringIO\n",
    "except ImportError:\n",
    "  from io import BytesIO as StringIO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing Data\n",
    "\n",
    "The first step to analyzing and querying your data is importing it. For this demo, we'll create a temporary table in a temporary dataset within BigQuery, using a small data file within Cloud Storage."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing Data from Cloud Storage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To interact with Google Cloud Storage, Datalab includes the `%%gcs` command. First, see the available options on `%%gcs`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "usage: gcs [-h] {copy,create,delete,list,read,view,write} ...\n",
      "\n",
      "Execute various Google Cloud Storage related operations. Use \"%gcs <command>\n",
      "-h\" for help on a specific command.\n",
      "\n",
      "positional arguments:\n",
      "  {copy,create,delete,list,read,view,write}\n",
      "                        commands\n",
      "    copy                Copy one or more Google Cloud Storage objects to a\n",
      "                        different location.\n",
      "    create              Create one or more Google Cloud Storage buckets.\n",
      "    delete              Delete one or more Google Cloud Storage buckets or\n",
      "                        objects.\n",
      "    list                List buckets in a project, or contents of a bucket.\n",
      "    read                Read the contents of a Google Cloud Storage object\n",
      "                        into a Python variable.\n",
      "    view                View the contents of a Google Cloud Storage object.\n",
      "    write               Write the value of a Python variable to a Google Cloud\n",
      "                        Storage object.\n",
      "\n",
      "optional arguments:\n",
      "  -h, --help            show this help message and exit\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "%gcs -h"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's use the `read` option to read a storage object into a local Python variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%gcs read --object gs://cloud-datalab-samples/cars.csv --variable cars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Year,Make,Model,Description,Price\n",
      "1997,Ford,E350,\"ac, abs, moon\",3000.00\n",
      "1999,Chevy,\"Venture Extended Edition\",\"\",4900.00\n",
      "1999,Chevy,\"Venture Extended Edition\",Very Large,5000.00\n",
      "1996,Jeep,Grand Cherokee,\"MUST SELL! air, moon roof, loaded\",4799.00\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(cars)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create the schema, conveniently using a DataFrame example.\n",
    "df = pd.read_csv(StringIO(cars))\n",
    "schema = bq.Schema.from_data(df)\n",
    "\n",
    "# Create the dataset\n",
    "bq.Dataset('importingsample').create()\n",
    "\n",
    "# Create the table\n",
    "sample_table = bq.Table('importingsample.cars').create(schema = schema, overwrite = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Job mysampleproject/job_RInz5SUINexdU5RUrA2CBgDBsUo completed\n"
     ]
    }
   ],
   "source": [
    "sample_table.load('gs://cloud-datalab-samples/cars.csv', mode='append',\n",
    "                  source_format = 'csv', csv_options=bq.CSVOptions(skip_leading_rows = 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%bq query -n importingSample\n",
    "SELECT * FROM importingsample.cars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <div class=\"bqtv\" id=\"1_148887913704\"><table><tr><th>Year</th><th>Make</th><th>Model</th><th>Description</th><th>Price</th></tr><tr><td>1996</td><td>Jeep</td><td>Grand Cherokee</td><td>MUST SELL! air, moon roof, loaded</td><td>4799.0</td></tr><tr><td>1997</td><td>Ford</td><td>E350</td><td>ac, abs, moon</td><td>3000.0</td></tr><tr><td>1999</td><td>Chevy</td><td>Venture Extended Edition</td><td>Very Large</td><td>5000.0</td></tr><tr><td>1999</td><td>Chevy</td><td>Venture Extended Edition</td><td></td><td>4900.0</td></tr></table></div>\n",
       "    <br />(rows: 4, time: 1.7s,   228B processed, job: job_v9Pm_m1RXKg5vkpxPmKI0VAnX2s)<br />\n",
       "    <script>\n",
       "\n",
       "      require.config({\n",
       "        paths: {\n",
       "          d3: '//cdnjs.cloudflare.com/ajax/libs/d3/3.4.13/d3',\n",
       "          plotly: 'https://cdn.plot.ly/plotly-1.5.1.min.js?noext',\n",
       "          jquery: '//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min'\n",
       "        },\n",
       "        map: {\n",
       "          '*': {\n",
       "            datalab: 'nbextensions/gcpdatalab'\n",
       "          }\n",
       "        },\n",
       "        shim: {\n",
       "          plotly: {\n",
       "            deps: ['d3', 'jquery'],\n",
       "            exports: 'plotly'\n",
       "          }\n",
       "        }\n",
       "      });\n",
       "\n",
       "      require(['datalab/charting', 'datalab/element!1_148887913704', 'base/js/events',\n",
       "          'datalab/style!/nbextensions/gcpdatalab/charting.css'],\n",
       "        function(charts, dom, events) {\n",
       "          charts.render('gcharts', dom, events, 'table', [], {\"rows\": [{\"c\": [{\"v\": 1996}, {\"v\": \"Jeep\"}, {\"v\": \"Grand Cherokee\"}, {\"v\": \"MUST SELL! air, moon roof, loaded\"}, {\"v\": 4799.0}]}, {\"c\": [{\"v\": 1997}, {\"v\": \"Ford\"}, {\"v\": \"E350\"}, {\"v\": \"ac, abs, moon\"}, {\"v\": 3000.0}]}, {\"c\": [{\"v\": 1999}, {\"v\": \"Chevy\"}, {\"v\": \"Venture Extended Edition\"}, {\"v\": \"Very Large\"}, {\"v\": 5000.0}]}, {\"c\": [{\"v\": 1999}, {\"v\": \"Chevy\"}, {\"v\": \"Venture Extended Edition\"}, {\"v\": \"\"}, {\"v\": 4900.0}]}], \"cols\": [{\"type\": \"number\", \"id\": \"Year\", \"label\": \"Year\"}, {\"type\": \"string\", \"id\": \"Make\", \"label\": \"Make\"}, {\"type\": \"string\", \"id\": \"Model\", \"label\": \"Model\"}, {\"type\": \"string\", \"id\": \"Description\", \"label\": \"Description\"}, {\"type\": \"number\", \"id\": \"Price\", \"label\": \"Price\"}]},\n",
       "            {\n",
       "              pageSize: 25,\n",
       "              cssClassNames:  {\n",
       "                tableRow: 'gchart-table-row',\n",
       "                headerRow: 'gchart-table-headerrow',\n",
       "                oddTableRow: 'gchart-table-oddrow',\n",
       "                selectedTableRow: 'gchart-table-selectedrow',\n",
       "                hoverTableRow: 'gchart-table-hoverrow',\n",
       "                tableCell: 'gchart-table-cell',\n",
       "                headerCell: 'gchart-table-headercell',\n",
       "                rowNumberCell: 'gchart-table-rownumcell'\n",
       "              }\n",
       "            },\n",
       "            {source_index: 0, fields: 'Year,Make,Model,Description,Price'},\n",
       "            0,\n",
       "            4);\n",
       "        }\n",
       "      );\n",
       "    </script>\n",
       "  "
      ],
      "text/plain": [
       "QueryResultsTable job_v9Pm_m1RXKg5vkpxPmKI0VAnX2s"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%bq execute -q importingSample"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing Data from a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Make</th>\n",
       "      <th>Model</th>\n",
       "      <th>Description</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010</td>\n",
       "      <td>Honda</td>\n",
       "      <td>Civic</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>Tesla</td>\n",
       "      <td>Model S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year   Make    Model  Description    Price\n",
       "0  2010  Honda    Civic          NaN  15000.0\n",
       "1  2015  Tesla  Model S          NaN  64900.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cars2 = storage.Object('cloud-datalab-samples', 'cars2.csv').read_stream()\n",
    "df2 = pd.read_csv(StringIO(cars2))\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Make</th>\n",
       "      <th>Model</th>\n",
       "      <th>Description</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010</td>\n",
       "      <td>Honda</td>\n",
       "      <td>Civic</td>\n",
       "      <td></td>\n",
       "      <td>15000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>Tesla</td>\n",
       "      <td>Model S</td>\n",
       "      <td></td>\n",
       "      <td>64900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year   Make    Model Description    Price\n",
       "0  2010  Honda    Civic              15000.0\n",
       "1  2015  Tesla  Model S              64900.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.fillna(value='', inplace=True)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Make</th>\n",
       "      <th>Model</th>\n",
       "      <th>Description</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1997</td>\n",
       "      <td>Ford</td>\n",
       "      <td>E350</td>\n",
       "      <td>ac, abs, moon</td>\n",
       "      <td>3000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1999</td>\n",
       "      <td>Chevy</td>\n",
       "      <td>Venture Extended Edition</td>\n",
       "      <td></td>\n",
       "      <td>4900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999</td>\n",
       "      <td>Chevy</td>\n",
       "      <td>Venture Extended Edition</td>\n",
       "      <td>Very Large</td>\n",
       "      <td>5000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1996</td>\n",
       "      <td>Jeep</td>\n",
       "      <td>Grand Cherokee</td>\n",
       "      <td>MUST SELL! air, moon roof, loaded</td>\n",
       "      <td>4799.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010</td>\n",
       "      <td>Honda</td>\n",
       "      <td>Civic</td>\n",
       "      <td></td>\n",
       "      <td>15000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2015</td>\n",
       "      <td>Tesla</td>\n",
       "      <td>Model S</td>\n",
       "      <td></td>\n",
       "      <td>64900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year   Make                     Model                        Description  \\\n",
       "0  1997   Ford                      E350                      ac, abs, moon   \n",
       "1  1999  Chevy  Venture Extended Edition                                      \n",
       "2  1999  Chevy  Venture Extended Edition                         Very Large   \n",
       "3  1996   Jeep            Grand Cherokee  MUST SELL! air, moon roof, loaded   \n",
       "4  2010  Honda                     Civic                                      \n",
       "5  2015  Tesla                   Model S                                      \n",
       "\n",
       "     Price  \n",
       "0   3000.0  \n",
       "1   4900.0  \n",
       "2   5000.0  \n",
       "3   4799.0  \n",
       "4  15000.0  \n",
       "5  64900.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_table.insert(df2)\n",
    "sample_table.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exporting Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting Data to Cloud Storage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bucket: mysampleproject-datalab-samples\n",
      "Object: gs://mysampleproject-datalab-samples/tmp/cars.csv\n"
     ]
    }
   ],
   "source": [
    "project = Context.default().project_id\n",
    "sample_bucket_name = project + '-datalab-samples'\n",
    "sample_bucket_path = 'gs://' + sample_bucket_name\n",
    "sample_bucket_object = sample_bucket_path + '/tmp/cars.csv'\n",
    "print('Bucket: ' + sample_bucket_name)\n",
    "print('Object: ' + sample_bucket_object)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_bucket = storage.Bucket(sample_bucket_name)\n",
    "sample_bucket.create()\n",
    "sample_bucket.exists()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Job mysampleproject/job_b6TqQiGuFu5d-8UiB5U5osV0cf8 completed\n"
     ]
    }
   ],
   "source": [
    "table = bq.Table('importingsample.cars')\n",
    "table.extract(destination = sample_bucket_object)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>Name</th><th>Type</th><th>Size</th><th>Updated</th></tr><tr><td>tmp/cars.csv</td><td>application/octet-stream</td><td>232</td><td>2017-03-07 09:34:30.347000+00:00</td></tr></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%gcs list --objects gs://$sample_bucket_name/*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bucket = storage.Bucket(sample_bucket_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "obj = list(bucket.objects())[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = obj.read_stream()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Year,Make,Model,Description,Price\n",
      "1997,Ford,E350,\"ac, abs, moon\",3000\n",
      "1999,Chevy,Venture Extended Edition,\"\",4900\n",
      "1999,Chevy,Venture Extended Edition,Very Large,5000\n",
      "1996,Jeep,Grand Cherokee,\"MUST SELL! air, moon roof, loaded\",4799\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting Data to a Local File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "table.to_file('/tmp/cars.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-rw-r--r-- 1 root root 299 Mar  7 09:34 /tmp/cars.csv\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "ls -l /tmp/cars.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Year,Make,Model,Description,Price\r\n",
      "1997,Ford,E350,\"ac, abs, moon\",3000.0\r\n",
      "1999,Chevy,Venture Extended Edition,,4900.0\r\n",
      "1999,Chevy,Venture Extended Edition,Very Large,5000.0\r\n",
      "1996,Jeep,Grand Cherokee,\"MUST SELL! air, moon roof, loaded\",4799.0\r\n",
      "2010,Honda,Civic,,15000.0\r\n",
      "2015,Tesla,Model S,,64900.0\r\n",
      "\n"
     ]
    }
   ],
   "source": [
    "lines = None\n",
    "with open('/tmp/cars.csv') as datafile:\n",
    "  lines = datafile.readlines()\n",
    "print(''.join(lines))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleanup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sample_bucket.object('tmp/cars.csv').delete()\n",
    "sample_bucket.delete()\n",
    "bq.Dataset('importingsample').delete(delete_contents = True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}