4.Reading Data - Tables and Views.ipynb

{"cells":[{"cell_type":"markdown","source":["# Reading Data - Tables and Views\n\n**Technical Accomplishments:**\n* Demonstrate how to pre-register data sources in Azure Databricks.\n* Introduce temporary views over files.\n* Read data from tables/views.\n* Regarding `printRecordsPerPartition(..)`, it \n  * converts the specified `DataFrame` to an RDD\n  * counts the number of records in each partition\n  * prints the results to the console."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"536fe2cc-0fb6-4eea-90b3-4272696fbd12"}}},{"cell_type":"markdown","source":["##![Spark Logo Tiny](https://files.training.databricks.com/images/105/logo_spark_tiny.png) Getting Started\n\nRun the following cell to configure our \"classroom.\""],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"9764e417-8a10-41ac-88e0-fb2f9fe9cab5"}}},{"cell_type":"code","source":["%run \"./Includes/Classroom-Setup\""],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"9c10e0d4-273d-48fc-8ce2-e30392482404"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}},{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"Initialized classroom variables & functions...","textData":null,"removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"htmlSandbox","arguments":{}}},"output_type":"display_data","data":{"text/html":["Initialized classroom variables & functions..."]}},{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}},{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"Datasets are already mounted to <b>/mnt/training</b> from <b>wasbs://training@dbtraineastus.blob.core.windows.net/</b>","textData":null,"removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"htmlSandbox","arguments":{}}},"output_type":"display_data","data":{"text/html":["Datasets are already mounted to <b>/mnt/training</b> from <b>wasbs://training@dbtraineastus.blob.core.windows.net/</b>"]}},{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"Created user-specific database","textData":null,"removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"htmlSandbox","arguments":{}}},"output_type":"display_data","data":{"text/html":["Created user-specific database"]}},{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"Using the database <b style=\"color:green\">aleksei_cherniaev_if_lv_db</b>.","textData":null,"removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"htmlSandbox","arguments":{}}},"output_type":"display_data","data":{"text/html":["Using the database <b style=\"color:green\">aleksei_cherniaev_if_lv_db</b>."]}},{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}},{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"All done!","textData":null,"removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"htmlSandbox","arguments":{}}},"output_type":"display_data","data":{"text/html":["All done!"]}}],"execution_count":0},{"cell_type":"code","source":["%run \"./Includes/Utility-Methods\""],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"da6fbced-0c64-4f60-bc03-755390e8b8bf"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}},{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"\n<div>Declared various utility methods:</div>\n<li>Declared <b style=\"color:green\">printRecordsPerPartition(<i>df:DataFrame</i>)</b> for diagnostics</li>\n<li>Declared <b style=\"color:green\">computeFileStats(<i>path:String</i>)</b> returns <b style=\"color:green\">(count:Long, bytes:Long)</b> for diagnostics</li>\n<li>Declared <b style=\"color:green\">tracker</b> for benchmarking</li>\n<li>Declared <b style=\"color:green\">cacheAs(<i>df:DataFrame, name:String, level:StorageLevel</i>)</b> for better debugging</li>\n<li>Declared <b style=\"color:green\">benchmarkCount(<i>lambda:DataFrame</i>)</b> returns <b style=\"color:green\">(df:DataFrame, total:Long, duration:Long)</b> for diagnostics</li>\n<li>Declared <b style=\"color:green\">untilStreamIsReady(<i>name:String</i>)</b> to control workflow</li>\n<br/>\n<div>All done!</div>\n","textData":null,"removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"htmlSandbox","arguments":{}}},"output_type":"display_data","data":{"text/html":["\n<div>Declared various utility methods:</div>\n<li>Declared <b style=\"color:green\">printRecordsPerPartition(<i>df:DataFrame</i>)</b> for diagnostics</li>\n<li>Declared <b style=\"color:green\">computeFileStats(<i>path:String</i>)</b> returns <b style=\"color:green\">(count:Long, bytes:Long)</b> for diagnostics</li>\n<li>Declared <b style=\"color:green\">tracker</b> for benchmarking</li>\n<li>Declared <b style=\"color:green\">cacheAs(<i>df:DataFrame, name:String, level:StorageLevel</i>)</b> for better debugging</li>\n<li>Declared <b style=\"color:green\">benchmarkCount(<i>lambda:DataFrame</i>)</b> returns <b style=\"color:green\">(df:DataFrame, total:Long, duration:Long)</b> for diagnostics</li>\n<li>Declared <b style=\"color:green\">untilStreamIsReady(<i>name:String</i>)</b> to control workflow</li>\n<br/>\n<div>All done!</div>\n"]}}],"execution_count":0},{"cell_type":"markdown","source":["##![Spark Logo Tiny](https://files.training.databricks.com/images/105/logo_spark_tiny.png) Registering Tables in Databricks\n\nSo far we've seen purely programmatic methods for reading in data.\n\nDatabricks allows us to \"register\" the equivalent of \"tables\" so that they can be easily accessed by all users. \n\nIt also allows us to specify configuration settings such as secret keys, tokens, username & passwords, etc without exposing that information to all users."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"264f6ec0-5b17-4eec-ae03-0964eb545bbf"}}},{"cell_type":"markdown","source":["## Register a Table/View\n* Databrick's UI has built in support for working with a number of different data sources\n* New ones are being added regularly\n* In our case we are going to upload the file <a href=\"http://files.training.databricks.com/static/data/pageviews_by_second_example.tsv\">pageviews_by_second_example.tsv</a>\n* .. and then use the UI to create a table."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"b014c819-04fb-46f6-a0a9-c4fe3acd559b"}}},{"cell_type":"markdown","source":["There are several benefits to this strategy:\n* Once setup, it never has to be done again\n* It is available for any user on the platform (permissions permitting)\n* Minimizes exposure of credentials\n* No real overhead to reading the schema (no infer-schema)\n* Easier to advertise available datasets to other users"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2765165c-fe04-4b9b-926f-0ac9467b9932"}}},{"cell_type":"markdown","source":["## Follow these steps to register a new Table\n\n**NOTE:** *It may be easiest for you to duplicate this browser tab so you can refer back to these steps.*\n\n1. Download the [pageviews_by_second_example.tsv](http://files.training.databricks.com/static/data/pageviews_by_second_example.tsv) file to your computer.\n2. Select **Data** in the left-hand menu.\n3. Select the database with your username.\n4. Select **Add Data** to create a new Table.\n\n  ![The Data menu item and Add Data button are both highlighted.](https://databricksdemostore.blob.core.windows.net/images/03-de-learning-path/data-add-data.png)\n\n5. In the Create New Table form, make sure **Upload File** is selected, then click on browse and select the [pageviews_by_second_example.tsv](http://files.training.databricks.com/static/data/pageviews_by_second_example.tsv) file is highlighted, or drag and drop it into the File box.\n6. Select **Create Table with UI**.\n\n  ![The previously listed form options are shown.](https://databricksdemostore.blob.core.windows.net/images/03-de-learning-path/create-new-table-1.png)\n\n7. Select your cluster, then select **Preview Table**.\n8. Under **Create in Database**, select the database with your username in the list. It is **important** that you do not skip this step. You can find the database name in the output of `cell 3` above.\n9. Select **Create Table**.\n\n  ![The previously listed form options are shown.](https://databricksdemostore.blob.core.windows.net/images/03-de-learning-path/create-new-table-2.png)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"47b92b03-ad40-4b13-84f0-bb010f49c687"}}},{"cell_type":"markdown","source":["##![Spark Logo Tiny](https://files.training.databricks.com/images/105/logo_spark_tiny.png) Reading from a Table/View\n\nWe can now read in the \"table\" **pageviews_by_seconds_example** as a `DataFrame` with one simple command (and then print the schema):"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"57b18134-2210-413b-89ac-87f12bfe287a"}}},{"cell_type":"code","source":["pageviewsBySecondsExampleDF = spark.read.table(\"pageviews_by_second_example_tsv\")\n\npageviewsBySecondsExampleDF.printSchema()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"85121087-d52b-4f38-8b1c-8abbf9b778d0"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"metadata":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}},{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"data":"<div class=\"ansiout\"><span class=\"ansi-red-fg\">---------------------------------------------------------------------------</span>\n<span class=\"ansi-red-fg\">AnalysisException</span>                         Traceback (most recent call last)\n<span class=\"ansi-green-fg\">&lt;command-74238150092320&gt;</span> in <span class=\"ansi-cyan-fg\">&lt;module&gt;</span>\n<span class=\"ansi-green-fg\">----&gt; 1</span><span class=\"ansi-red-fg\"> </span>pageviewsBySecondsExampleDF <span class=\"ansi-blue-fg\">=</span> spark<span class=\"ansi-blue-fg\">.</span>read<span class=\"ansi-blue-fg\">.</span>table<span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#34;pageviews_by_second_example_tsv&#34;</span><span class=\"ansi-blue-fg\">)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">      2</span> \n<span class=\"ansi-green-intense-fg ansi-bold\">      3</span> pageviewsBySecondsExampleDF<span class=\"ansi-blue-fg\">.</span>printSchema<span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">)</span>\n\n<span class=\"ansi-green-fg\">/databricks/spark/python/pyspark/sql/readwriter.py</span> in <span class=\"ansi-cyan-fg\">table</span><span class=\"ansi-blue-fg\">(self, tableName)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    403</span>         <span class=\"ansi-blue-fg\">[</span><span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#39;name&#39;</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">&#39;string&#39;</span><span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#39;year&#39;</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">&#39;int&#39;</span><span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#39;month&#39;</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">&#39;int&#39;</span><span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#39;day&#39;</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">&#39;int&#39;</span><span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">]</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    404</span>         &#34;&#34;&#34;\n<span class=\"ansi-green-fg\">--&gt; 405</span><span class=\"ansi-red-fg\">         </span><span class=\"ansi-green-fg\">return</span> self<span class=\"ansi-blue-fg\">.</span>_df<span class=\"ansi-blue-fg\">(</span>self<span class=\"ansi-blue-fg\">.</span>_jreader<span class=\"ansi-blue-fg\">.</span>table<span class=\"ansi-blue-fg\">(</span>tableName<span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    406</span> \n<span class=\"ansi-green-intense-fg ansi-bold\">    407</span>     <span class=\"ansi-green-fg\">def</span> parquet<span class=\"ansi-blue-fg\">(</span>self<span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">*</span>paths<span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">**</span>options<span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">:</span>\n\n<span class=\"ansi-green-fg\">/databricks/spark/python/lib/py4j-0.10.9-src.zip/py4j/java_gateway.py</span> in <span class=\"ansi-cyan-fg\">__call__</span><span class=\"ansi-blue-fg\">(self, *args)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">   1302</span> \n<span class=\"ansi-green-intense-fg ansi-bold\">   1303</span>         answer <span class=\"ansi-blue-fg\">=</span> self<span class=\"ansi-blue-fg\">.</span>gateway_client<span class=\"ansi-blue-fg\">.</span>send_command<span class=\"ansi-blue-fg\">(</span>command<span class=\"ansi-blue-fg\">)</span>\n<span class=\"ansi-green-fg\">-&gt; 1304</span><span class=\"ansi-red-fg\">         return_value = get_return_value(\n</span><span class=\"ansi-green-intense-fg ansi-bold\">   1305</span>             answer, self.gateway_client, self.target_id, self.name)\n<span class=\"ansi-green-intense-fg ansi-bold\">   1306</span> \n\n<span class=\"ansi-green-fg\">/databricks/spark/python/pyspark/sql/utils.py</span> in <span class=\"ansi-cyan-fg\">deco</span><span class=\"ansi-blue-fg\">(*a, **kw)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    114</span>                 <span class=\"ansi-red-fg\"># Hide where the exception came from that shows a non-Pythonic</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    115</span>                 <span class=\"ansi-red-fg\"># JVM exception message.</span>\n<span class=\"ansi-green-fg\">--&gt; 116</span><span class=\"ansi-red-fg\">                 </span><span class=\"ansi-green-fg\">raise</span> converted <span class=\"ansi-green-fg\">from</span> <span class=\"ansi-green-fg\">None</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    117</span>             <span class=\"ansi-green-fg\">else</span><span class=\"ansi-blue-fg\">:</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    118</span>                 <span class=\"ansi-green-fg\">raise</span>\n\n<span class=\"ansi-red-fg\">AnalysisException</span>: Table or view not found: pageviews_by_second_example_tsv;\n&#39;UnresolvedRelation [pageviews_by_second_example_tsv], [], false\n</div>","errorSummary":"<span class=\"ansi-red-fg\">AnalysisException</span>: Table or view not found: pageviews_by_second_example_tsv;","metadata":{},"errorTraceType":"html","type":"ipynbError","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"><span class=\"ansi-red-fg\">---------------------------------------------------------------------------</span>\n<span class=\"ansi-red-fg\">AnalysisException</span>                         Traceback (most recent call last)\n<span class=\"ansi-green-fg\">&lt;command-74238150092320&gt;</span> in <span class=\"ansi-cyan-fg\">&lt;module&gt;</span>\n<span class=\"ansi-green-fg\">----&gt; 1</span><span class=\"ansi-red-fg\"> </span>pageviewsBySecondsExampleDF <span class=\"ansi-blue-fg\">=</span> spark<span class=\"ansi-blue-fg\">.</span>read<span class=\"ansi-blue-fg\">.</span>table<span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#34;pageviews_by_second_example_tsv&#34;</span><span class=\"ansi-blue-fg\">)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">      2</span> \n<span class=\"ansi-green-intense-fg ansi-bold\">      3</span> pageviewsBySecondsExampleDF<span class=\"ansi-blue-fg\">.</span>printSchema<span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">)</span>\n\n<span class=\"ansi-green-fg\">/databricks/spark/python/pyspark/sql/readwriter.py</span> in <span class=\"ansi-cyan-fg\">table</span><span class=\"ansi-blue-fg\">(self, tableName)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    403</span>         <span class=\"ansi-blue-fg\">[</span><span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#39;name&#39;</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">&#39;string&#39;</span><span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#39;year&#39;</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">&#39;int&#39;</span><span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#39;month&#39;</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">&#39;int&#39;</span><span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">(</span><span class=\"ansi-blue-fg\">&#39;day&#39;</span><span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">&#39;int&#39;</span><span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">]</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    404</span>         &#34;&#34;&#34;\n<span class=\"ansi-green-fg\">--&gt; 405</span><span class=\"ansi-red-fg\">         </span><span class=\"ansi-green-fg\">return</span> self<span class=\"ansi-blue-fg\">.</span>_df<span class=\"ansi-blue-fg\">(</span>self<span class=\"ansi-blue-fg\">.</span>_jreader<span class=\"ansi-blue-fg\">.</span>table<span class=\"ansi-blue-fg\">(</span>tableName<span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    406</span> \n<span class=\"ansi-green-intense-fg ansi-bold\">    407</span>     <span class=\"ansi-green-fg\">def</span> parquet<span class=\"ansi-blue-fg\">(</span>self<span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">*</span>paths<span class=\"ansi-blue-fg\">,</span> <span class=\"ansi-blue-fg\">**</span>options<span class=\"ansi-blue-fg\">)</span><span class=\"ansi-blue-fg\">:</span>\n\n<span class=\"ansi-green-fg\">/databricks/spark/python/lib/py4j-0.10.9-src.zip/py4j/java_gateway.py</span> in <span class=\"ansi-cyan-fg\">__call__</span><span class=\"ansi-blue-fg\">(self, *args)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">   1302</span> \n<span class=\"ansi-green-intense-fg ansi-bold\">   1303</span>         answer <span class=\"ansi-blue-fg\">=</span> self<span class=\"ansi-blue-fg\">.</span>gateway_client<span class=\"ansi-blue-fg\">.</span>send_command<span class=\"ansi-blue-fg\">(</span>command<span class=\"ansi-blue-fg\">)</span>\n<span class=\"ansi-green-fg\">-&gt; 1304</span><span class=\"ansi-red-fg\">         return_value = get_return_value(\n</span><span class=\"ansi-green-intense-fg ansi-bold\">   1305</span>             answer, self.gateway_client, self.target_id, self.name)\n<span class=\"ansi-green-intense-fg ansi-bold\">   1306</span> \n\n<span class=\"ansi-green-fg\">/databricks/spark/python/pyspark/sql/utils.py</span> in <span class=\"ansi-cyan-fg\">deco</span><span class=\"ansi-blue-fg\">(*a, **kw)</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    114</span>                 <span class=\"ansi-red-fg\"># Hide where the exception came from that shows a non-Pythonic</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    115</span>                 <span class=\"ansi-red-fg\"># JVM exception message.</span>\n<span class=\"ansi-green-fg\">--&gt; 116</span><span class=\"ansi-red-fg\">                 </span><span class=\"ansi-green-fg\">raise</span> converted <span class=\"ansi-green-fg\">from</span> <span class=\"ansi-green-fg\">None</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    117</span>             <span class=\"ansi-green-fg\">else</span><span class=\"ansi-blue-fg\">:</span>\n<span class=\"ansi-green-intense-fg ansi-bold\">    118</span>                 <span class=\"ansi-green-fg\">raise</span>\n\n<span class=\"ansi-red-fg\">AnalysisException</span>: Table or view not found: pageviews_by_second_example_tsv;\n&#39;UnresolvedRelation [pageviews_by_second_example_tsv], [], false\n</div>"]}}],"execution_count":0},{"cell_type":"markdown","source":["And of course we can now view that data as well:"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"1649553f-65b3-4331-bfb1-41ae88bb5532"}}},{"cell_type":"code","source":["display(pageviewsBySecondsExampleDF)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"7310aef7-b83c-4a3b-880c-a0e2258e6f51"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"data":"","errorSummary":"","metadata":{},"errorTraceType":null,"type":"ipynbError","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### Review: Reading from Tables\n* No job is executed - the schema is stored in the table definition on Databricks.\n* The data types shown here are those we defined when we registered the table.\n* In our case, the file was uploaded to Databricks and is stored on the DBFS.\n  * If we used JDBC, it would open the connection to the database and read it in.\n  * If we used an object store (like what is backing the DBFS), it would read the data from source.\n* The \"registration\" of the table simply makes future access, or access by multiple users easier.\n* The users of the notebook cannot see username and passwords, secret keys, tokens, etc."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"a1a5cf2f-ab9c-4fed-b962-99933ce9855f"}}},{"cell_type":"markdown","source":["Let's take a look at some of the other details of the `DataFrame` we just created for comparison sake."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"cc2325a9-e1a1-4240-aea9-99802eab7a94"}}},{"cell_type":"code","source":["print(\"Partitions: \" + str(pageviewsBySecondsExampleDF.rdd.getNumPartitions()))\nprintRecordsPerPartition(pageviewsBySecondsExampleDF)\nprint(\"-\"*80)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"b13e0324-c748-4e1f-b616-082b0de4f091"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"data":"","errorSummary":"","metadata":{},"errorTraceType":null,"type":"ipynbError","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["##![Spark Logo Tiny](https://files.training.databricks.com/images/105/logo_spark_tiny.png) Temporary Views\n\nTables that are loadable by the call `spark.read.table(..)` are also accessible through the SQL APIs.\n\nFor example, we already used Databricks to expose **pageviews_by_second_example_tsv** as a table/view."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"947af781-32de-4721-b199-b8d270060939"}}},{"cell_type":"code","source":["%sql\nselect * from pageviews_by_second_example_tsv limit(5)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"12a669ac-ceff-44ab-a4e9-81d123d253a2"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"data":"","errorSummary":"","metadata":{},"errorTraceType":null,"type":"ipynbError","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["You can also take an existing `DataFrame` and register it as a view exposing it as a table to the SQL API.\n\nIf you recall from earlier, we have an instance called `parquetDF`.\n\nWe can create a [temporary] view with this call..."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"853b106d-64be-4291-8023-ff3e5a10bf4b"}}},{"cell_type":"code","source":["# create a DataFrame from a parquet file\nparquetFile = \"/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/\"\nparquetDF = spark.read.parquet(parquetFile)\n\n# create a temporary view from the resulting DataFrame\nparquetDF.createOrReplaceTempView(\"parquet_table\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"65de7ce4-3727-405d-a249-31fc975bc499"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"data":"","errorSummary":"","metadata":{},"errorTraceType":null,"type":"ipynbError","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["And now we can use the SQL API to reference that same `DataFrame` as the table **parquet_table**."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"e1d200a3-cefe-4284-bc71-fb7f3a08be71"}}},{"cell_type":"code","source":["%sql\nselect * from parquet_table order by requests desc limit(5)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"0b81e2b3-27b5-4fd3-be94-bdbcc78c9b49"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"data":"","errorSummary":"","metadata":{},"errorTraceType":null,"type":"ipynbError","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["** *Note #1:* ** *The method createOrReplaceTempView(..) is bound to the SparkSession meaning it will be discarded once the session ends.*\n\n** *Note #2:* ** On the other hand, the method createOrReplaceGlobalTempView(..) is bound to the spark application.*\n\n*Or to put that another way, I can use createOrReplaceTempView(..) in this notebook only. However, I can call createOrReplaceGlobalTempView(..) in this notebook and then access it from another.*"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"e483c887-e900-4127-a9c0-192c23bd6406"}}},{"cell_type":"markdown","source":["## Next steps\n\nStart the next lesson, [Writing Data]($./5.Writing%20Data)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"5ee2bbab-4286-4c12-986c-42954597f84f"}}}],"metadata":{"application/vnd.databricks.v1+notebook":{"notebookName":"4.Reading Data - Tables and Views","dashboards":[],"notebookMetadata":{"pythonIndentUnit":2},"language":"python","widgets":{},"notebookOrigID":74238150092310}},"nbformat":4,"nbformat_minor":0}