3.Reading Data - Parquet.ipynb

{"cells":[{"cell_type":"markdown","source":["# Reading Data - Parquet Files\n\n**Technical Accomplishments:**\n- Introduce the Parquet file format.\n- Read data from:\n  - Parquet files without a schema.\n  - Parquet files with a schema."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"99487202-1e08-4508-b194-12026f705188"}}},{"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":"756e37ba-9002-42ae-9281-df5366ab2ae9"}}},{"cell_type":"code","source":["%run \"./Includes/Classroom-Setup\""],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"8575e953-d5cb-44ce-ba17-7d20b780fd00"}},"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":"9a3e3903-5202-44f5-af40-955d2f519c4c"}},"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":["-sandbox\n<div style=\"float:right; margin-right:1em\">\n  <img src=\"https://parquet.apache.org/assets/img/parquet_logo.png\"><br>\n  <a href=\"https://parquet.apache.org/\" target=\"_blank\">https&#58;//parquet.apache.org</a>\n</div>\n\n##![Spark Logo Tiny](https://files.training.databricks.com/images/105/logo_spark_tiny.png) Reading from Parquet Files\n\n<strong style=\"font-size:larger\">\"</strong>Apache Parquet is a columnar storage format available to any project in the Hadoop ecosystem, regardless of the choice of data processing framework, data model or programming language.<strong style=\"font-size:larger\">\"</strong><br>"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"b8f6a8e8-727a-4937-b8b8-25217d5072ce"}}},{"cell_type":"markdown","source":["-sandbox\n### About Parquet Files\n* Free & Open Source.\n* Increased query performance over row-based data stores.\n* Provides efficient data compression.\n* Designed for performance on large data sets.\n* Supports limited schema evolution.\n* Is a splittable \"file format\".\n* A <a href=\"https://en.wikipedia.org/wiki/Column-oriented_DBMS\" target=\"_blank\">Column-Oriented</a> data store\n\n&nbsp;&nbsp;&nbsp;&nbsp;** Row Format ** &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; **Column Format**\n\n<table style=\"border:0\">\n\n  <tr>\n    <th>ID</th><th>Name</th><th>Score</th>\n    <th style=\"border-top:0;border-bottom:0\">&nbsp;</th>\n    <th>ID:</th><td>1</td><td>2</td>\n    <td style=\"border-right: 1px solid #DDDDDD\">3</td>\n  </tr>\n\n  <tr>\n    <td>1</td><td>john</td><td>4.1</td>\n    <td style=\"border-top:0;border-bottom:0\">&nbsp;</td>\n    <th>Name:</th><td>john</td><td>mike</td>\n    <td style=\"border-right: 1px solid #DDDDDD\">sally</td>\n  </tr>\n\n  <tr>\n    <td>2</td><td>mike</td><td>3.5</td>\n    <td style=\"border-top:0;border-bottom:0\">&nbsp;</td>\n    <th style=\"border-bottom: 1px solid #DDDDDD\">Score:</th>\n    <td style=\"border-bottom: 1px solid #DDDDDD\">4.1</td>\n    <td style=\"border-bottom: 1px solid #DDDDDD\">3.5</td>\n    <td style=\"border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD\">6.4</td>\n  </tr>\n\n  <tr>\n    <td style=\"border-bottom: 1px solid #DDDDDD\">3</td>\n    <td style=\"border-bottom: 1px solid #DDDDDD\">sally</td>\n    <td style=\"border-bottom: 1px solid #DDDDDD; border-right: 1px solid #DDDDDD\">6.4</td>\n  </tr>\n\n</table>\n\nSee also\n* <a href=\"https://parquet.apache.org/\" target=\"_blank\">https&#58;//parquet.apache.org</a>\n* <a href=\"https://en.wikipedia.org/wiki/Apache_Parquet\" target=\"_blank\">https&#58;//en.wikipedia.org/wiki/Apache_Parquet</a>"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"23b445b9-6da2-4527-b62a-873323ebf718"}}},{"cell_type":"markdown","source":["### Data Source\n\nThe data for this example shows the number of requests to Wikipedia's mobile and desktop websites (<a href=\"https://dumps.wikimedia.org/other/pagecounts-raw\" target=\"_blank\">23 MB</a> from Wikipedia). \n\nThe original file, captured August 5th of 2016 was downloaded, converted to a Parquet file and made available for us at **/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"76b75a2b-dd81-4529-b3ef-dc53c14925d3"}}},{"cell_type":"code","source":["%fs ls /mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"222bba05-c7f4-449b-a330-ea4a56e40318"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"overflow":false,"datasetInfos":[],"data":[["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/_SUCCESS","_SUCCESS",0],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/_committed_6241970109963426653","_committed_6241970109963426653",760],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/_started_6241970109963426653","_started_6241970109963426653",0],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00000-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet","part-00000-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet",2996913],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00001-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet","part-00001-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet",2994285],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00002-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet","part-00002-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet",2994196],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00003-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet","part-00003-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet",2992431],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00004-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet","part-00004-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet",2990093],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00005-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet","part-00005-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet",2989931],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00006-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet","part-00006-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet",2989314],["dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00007-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet","part-00007-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet",2987499]],"plotOptions":{"displayType":"table","customPlotOptions":{},"pivotColumns":null,"pivotAggregation":null,"xColumns":null,"yColumns":null},"columnCustomDisplayInfos":{},"aggType":"","isJsonSchema":true,"removedWidgets":[],"aggSchema":[],"schema":[{"name":"path","type":"\"string\"","metadata":"{}"},{"name":"name","type":"\"string\"","metadata":"{}"},{"name":"size","type":"\"long\"","metadata":"{}"}],"aggError":"","aggData":[],"addedWidgets":{},"metadata":{},"dbfsResultPath":null,"type":"table","aggOverflow":false,"aggSeriesLimitReached":false,"arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>path</th><th>name</th><th>size</th></tr></thead><tbody><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/_SUCCESS</td><td>_SUCCESS</td><td>0</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/_committed_6241970109963426653</td><td>_committed_6241970109963426653</td><td>760</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/_started_6241970109963426653</td><td>_started_6241970109963426653</td><td>0</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00000-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>part-00000-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>2996913</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00001-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>part-00001-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>2994285</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00002-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>part-00002-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>2994196</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00003-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>part-00003-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>2992431</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00004-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>part-00004-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>2990093</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00005-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>part-00005-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>2989931</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00006-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>part-00006-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>2989314</td></tr><tr><td>dbfs:/mnt/training/wikipedia/pagecounts/staging_parquet_en_only_clean/part-00007-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>part-00007-tid-6241970109963426653-cd9cd6ee-cb10-4da2-82b3-ea25a8369cbf-0-c000.gz.parquet</td><td>2987499</td></tr></tbody></table></div>"]}}],"execution_count":0},{"cell_type":"markdown","source":["Unlike our CSV and JSON example, the parquet \"file\" is actually 11 files, 8 of which consist of the bulk of the data and the other three consist of meta-data."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"dbf60ff1-665f-408b-b19a-bc19af2a8cee"}}},{"cell_type":"markdown","source":["### Read in the Parquet Files\n\nTo read in this files, we will specify the location of the parquet directory."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2faf43e8-6284-4cfb-80cb-f49500abdda6"}}},{"cell_type":"code","source":["parquetFile = \"/mnt/training/wikipedia/pageviews/pageviews_by_second.parquet/\"\n\n(spark.read              # The DataFrameReader\n  .parquet(parquetFile)  # Creates a DataFrame from Parquet after reading in the file\n  .printSchema()         # Print the DataFrame's schema\n)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"5dfc3f59-3252-41af-9803-567efaf0b806"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">root\n |-- timestamp: string (nullable = true)\n |-- site: string (nullable = true)\n |-- requests: integer (nullable = true)\n\n</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\">root\n-- timestamp: string (nullable = true)\n-- site: string (nullable = true)\n-- requests: integer (nullable = true)\n\n</div>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### Review: Reading from Parquet Files\n* We do not need to specify the schema - the column names and data types are stored in the parquet files.\n* Only one job is required to **read** that schema from the parquet file's metadata.\n* Unlike the CSV or JSON readers that have to load the entire file and then infer the schema, the parquet reader can \"read\" the schema very quickly because it's reading that schema from the metadata."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"881c296e-082e-495f-ad89-ae3c7b884b4e"}}},{"cell_type":"markdown","source":["### Read in the Parquet Files w/Schema\n\nIf you want to avoid the extra job entirely, we can, again, specify the schema even for parquet files:\n\n** *WARNING* ** *Providing a schema may avoid this one-time hit to determine the `DataFrame's` schema.*  \n*However, if you specify the wrong schema it will conflict with the true schema and will result in an analysis exception at runtime.*"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"88b110b2-bc96-47e5-af23-c9698379191b"}}},{"cell_type":"code","source":["# Required for StructField, StringType, IntegerType, etc.\nfrom pyspark.sql.types import *\n\nparquetSchema = StructType(\n  [\n    StructField(\"timestamp\", StringType(), False),\n    StructField(\"site\", StringType(), False),\n    StructField(\"requests\", IntegerType(), False)\n  ]\n)\n\n(spark.read               # The DataFrameReader\n  .schema(parquetSchema)  # Use the specified schema\n  .parquet(parquetFile)   # Creates a DataFrame from Parquet after reading in the file\n  .printSchema()          # Print the DataFrame's schema\n)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"5265f331-ad0d-4587-837e-dbb03bc84877"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">root\n |-- timestamp: string (nullable = true)\n |-- site: string (nullable = true)\n |-- requests: integer (nullable = true)\n\n</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\">root\n-- timestamp: string (nullable = true)\n-- site: string (nullable = true)\n-- requests: integer (nullable = true)\n\n</div>"]}}],"execution_count":0},{"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":"1e6be18d-4f98-4766-897a-b0b5d19f2503"}}},{"cell_type":"code","source":["parquetDF = spark.read.schema(parquetSchema).parquet(parquetFile)\n\nprint(\"Partitions: \" + str(parquetDF.rdd.getNumPartitions()) )\nprintRecordsPerPartition(parquetDF)\nprint(\"-\"*80)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"ab2106d6-d11f-41d7-ac05-1484bdc15285"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">Partitions: 5\nPer-Partition Counts\n#1: 1,463,276\n#2: 1,462,749\n#3: 1,462,393\n#4: 1,463,679\n#5: 1,347,903\n--------------------------------------------------------------------------------\n</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\">Partitions: 5\nPer-Partition Counts\n#1: 1,463,276\n#2: 1,462,749\n#3: 1,462,393\n#4: 1,463,679\n#5: 1,347,903\n--------------------------------------------------------------------------------\n</div>"]}}],"execution_count":0},{"cell_type":"markdown","source":["In most/many cases, people do not provide the schema for Parquet files because reading in the schema is such a cheap process.\n\nAnd lastly, let's peek at the data:"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"dba48d16-b89a-4564-83a6-7841b6be2f53"}}},{"cell_type":"code","source":["display(parquetDF.limit(3))"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"ddc536d3-d9e2-4161-b5ee-a57aedd5acc8"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"overflow":false,"datasetInfos":[],"data":[["2015-03-22T14:13:34","mobile",1425],["2015-03-22T14:23:18","desktop",2534],["2015-03-22T14:36:47","desktop",2444]],"plotOptions":{"displayType":"table","customPlotOptions":{},"pivotColumns":null,"pivotAggregation":null,"xColumns":null,"yColumns":null},"columnCustomDisplayInfos":{},"aggType":"","isJsonSchema":true,"removedWidgets":[],"aggSchema":[],"schema":[{"name":"timestamp","type":"\"string\"","metadata":"{}"},{"name":"site","type":"\"string\"","metadata":"{}"},{"name":"requests","type":"\"integer\"","metadata":"{}"}],"aggError":"","aggData":[],"addedWidgets":{},"metadata":{},"dbfsResultPath":null,"type":"table","aggOverflow":false,"aggSeriesLimitReached":false,"arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>timestamp</th><th>site</th><th>requests</th></tr></thead><tbody><tr><td>2015-03-22T14:13:34</td><td>mobile</td><td>1425</td></tr><tr><td>2015-03-22T14:23:18</td><td>desktop</td><td>2534</td></tr><tr><td>2015-03-22T14:36:47</td><td>desktop</td><td>2444</td></tr></tbody></table></div>"]}}],"execution_count":0},{"cell_type":"markdown","source":["## Next steps\n\nStart the next lesson, [Reading Data - Tables and Views]($./4.Reading%20Data%20-%20Tables%20and%20Views)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"c5707b8a-7afd-4ed5-aac6-a5db48c8c29e"}}}],"metadata":{"application/vnd.databricks.v1+notebook":{"notebookName":"3.Reading Data - Parquet","dashboards":[],"notebookMetadata":{"pythonIndentUnit":2},"language":"python","widgets":{},"notebookOrigID":74238150092230}},"nbformat":4,"nbformat_minor":0}