{"id":974,"date":"2023-06-20T13:57:32","date_gmt":"2023-06-20T13:57:32","guid":{"rendered":"https:\/\/bandittracker.com\/?p=974"},"modified":"2023-06-20T13:57:34","modified_gmt":"2023-06-20T13:57:34","slug":"reading-and-writing-to-s3-with-databricks","status":"publish","type":"post","link":"https:\/\/bandittracker.com\/reading-and-writing-to-s3-with-databricks\/","title":{"rendered":"Reading And Writing To S3 With Databricks (Examples)"},"content":{"rendered":"\n<p>Before you start exchanging data between Databricks and S3, you need to have the necessary permissions in place. We have a separate article that takes you through <a href=\"https:\/\/bandittracker.com\/configure-permissions-and-roles-for-databricks-and-s3\/\" data-type=\"post\" data-id=\"970\">configuring S3 permissions for Databricks access<\/a>.<\/p>\n\n\n\n<p>The rest of this article provides code examples for common use cases when reading and writing data with Databricks and S3. Create a <a href=\"https:\/\/bandittracker.com\/what-is-a-databricks-notebook\/\" data-type=\"post\" data-id=\"926\">notebook<\/a> to follow along!<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_63 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-660a617003361\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-660a617003361\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/bandittracker.com\/reading-and-writing-to-s3-with-databricks\/#How_To_Read_A_CSV_File_From_S3_With_Databricks\" title=\"How To Read A CSV File From S3 With Databricks\">How To Read A CSV File From S3 With Databricks<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/bandittracker.com\/reading-and-writing-to-s3-with-databricks\/#How_To_Read_Parquet_From_S3_With_Databrick\" title=\"How To Read Parquet From S3 With Databrick\">How To Read Parquet From S3 With Databrick<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/bandittracker.com\/reading-and-writing-to-s3-with-databricks\/#How_To_Write_A_Dataframe_To_A_CSV_File_In_S3_From_Databricks\" title=\"How To Write A Dataframe To A CSV File In S3 From Databricks\">How To Write A Dataframe To A CSV File In S3 From Databricks<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/bandittracker.com\/reading-and-writing-to-s3-with-databricks\/#How_To_Write_A_Dataframe_To_A_JSON_File_In_S3_From_Databricks\" title=\"How To Write A Dataframe To A JSON File In S3 From Databricks\">How To Write A Dataframe To A JSON File In S3 From Databricks<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/bandittracker.com\/reading-and-writing-to-s3-with-databricks\/#Troubleshooting_%E2%80%9CAccess_Denied%E2%80%9D\" title=\"Troubleshooting \u201cAccess Denied\u201d\">Troubleshooting \u201cAccess Denied\u201d<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/bandittracker.com\/reading-and-writing-to-s3-with-databricks\/#Troubleshooting_%E2%80%9CUpload_Exceeds_Maximum_Size%E2%80%9D\" title=\"Troubleshooting \u201cUpload Exceeds Maximum Size\u201d\">Troubleshooting \u201cUpload Exceeds Maximum Size\u201d<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Read_A_CSV_File_From_S3_With_Databricks\"><\/span>How To Read A CSV File From S3 With Databricks<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Here is how to read a CSV file using Python in your Databricks notebook.<\/p>\n\n\n\n<p>Let\u2019s say that your CSV file is \u201csalesdata.csv\u201d and it\u2019s in a bucket called \/europe<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>filename = \u201cs3:\/\/europe\/salesdata.csv\u201d\n\ndata = spark.read.format('csv').options(header='true', inferSchema='true').load(filename)\n\ndisplay(data)<\/code><\/pre>\n\n\n\n<p>The key part of this code is using spark.read() to read the file. These are options used:<\/p>\n\n\n\n<ul>\n<li>Header: specifiy if your data has a header row<\/li>\n\n\n\n<li>inferSchema: this means you don\u2019t have to provide the schema yourself<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Read_Parquet_From_S3_With_Databrick\"><\/span>How To Read Parquet From S3 With Databrick<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Parquet is a columnar file format created by Apache that is optimized for big data in Spark.<\/p>\n\n\n\n<p>Let\u2019s say that your Parquet file is \u201csalesdata\u201d and it\u2019s in a bucket called \/europe<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>filename = \u201c\/europe\/salesdata\u201d\n\ndata = spark.read.parquet(filename)\n\ndisplay(data)<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Write_A_Dataframe_To_A_CSV_File_In_S3_From_Databricks\"><\/span>How To Write A Dataframe To A CSV File In S3 From Databricks<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can use the functions associated with the dataframe object to export the data in a CSV format. Here is some sample code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>filename = \u201c\/europe\/salesdata.csv\u201d\n\ndf\n.write\\\n.format(\"com.databricks.spark.csv\") \\\n.option(\"header\", \"true\") \\\n.save(filename)\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Write_A_Dataframe_To_A_JSON_File_In_S3_From_Databricks\"><\/span>How To Write A Dataframe To A JSON File In S3 From Databricks<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can use the functions associated with the dataframe object to export the data in JSON format. Here is some sample code: <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>filename = \u201c\/europe\/salesdata.json\u201d\n\ndf\n.write \\\n.format(\"json\") \\\n.save(filename)\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Troubleshooting_%E2%80%9CAccess_Denied%E2%80%9D\"><\/span>Troubleshooting \u201cAccess Denied\u201d<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>One of the most common errors you\u2019ll encounter when working with S3 is \u201caccess denied\u201d.<\/p>\n\n\n\n<p>You will get this error if you\u2019re mixing using instance profiles for permissioning alongside using secrete keys in your SPARK environment variables.<\/p>\n\n\n\n<p>You can run \u201c<em>env | grep -i aws<\/em>\u201d on your cluster. If you see AWS_ACCESS_KEY_ID but you shouldn\u2019t be using keys, then you will want to get rid of this variable.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Troubleshooting_%E2%80%9CUpload_Exceeds_Maximum_Size%E2%80%9D\"><\/span>Troubleshooting \u201cUpload Exceeds Maximum Size\u201d<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>If you\u2019re trying to save a large DataFrame to a single CSV or JSON file in S3, you may get this error message:<\/p>\n\n\n\n<p>\u201cYour proposed upload exceeds the maximum allowed size.&#8221;<\/p>\n\n\n\n<p>The preferred solution is to save the file in multiple parts. But what if you have a client who insists that they receive the file as a single object?<\/p>\n\n\n\n<p>You have the option of splitting the DataFrame into multiple smaller ones. You can loop through a list and append the data to the same file like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>df.write.mode('append').json(yourbigfile)<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Before you start exchanging data between Databricks and S3, you need to have the necessary permissions in place. We have a separate article that takes you through configuring S3 permissions for Databricks access. The rest of this article provides code examples for common use cases when reading and writing data with Databricks and S3. Create &#8230; <a title=\"Reading And Writing To S3 With Databricks (Examples)\" class=\"read-more\" href=\"https:\/\/bandittracker.com\/reading-and-writing-to-s3-with-databricks\/\" aria-label=\"More on Reading And Writing To S3 With Databricks (Examples)\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[29],"tags":[],"_links":{"self":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/974"}],"collection":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/comments?post=974"}],"version-history":[{"count":1,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/974\/revisions"}],"predecessor-version":[{"id":975,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/974\/revisions\/975"}],"wp:attachment":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/media?parent=974"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/categories?post=974"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/tags?post=974"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}