{"id":1002,"date":"2023-06-27T13:31:55","date_gmt":"2023-06-27T13:31:55","guid":{"rendered":"https:\/\/bandittracker.com\/?p=1002"},"modified":"2023-06-27T13:34:50","modified_gmt":"2023-06-27T13:34:50","slug":"delta-tables-in-databricks","status":"publish","type":"post","link":"https:\/\/bandittracker.com\/delta-tables-in-databricks\/","title":{"rendered":"Delta Tables In Databricks (For Beginners)"},"content":{"rendered":"\n<p>Delta tables are a core feature of Databricks. This article assumes you are completely new to the platform but have some familiarity with SQL or Spark basics.<\/p>\n\n\n\n<p>Read on to get an understanding of what they are, how they differ from other storage formats, and how to create and use them.<\/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-660a5c98ea705\" 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-660a5c98ea705\" 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\/delta-tables-in-databricks\/#What_Is_A_Delta_Table_in_Databricks\" title=\"What Is A Delta Table in Databricks?\">What Is A Delta Table in 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\/delta-tables-in-databricks\/#Differences_Between_Parquet_And_Delta_Tables\" title=\"Differences Between Parquet And Delta Tables\">Differences Between Parquet And Delta Tables<\/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\/delta-tables-in-databricks\/#Difference_Between_Tables_And_Delta_Tables\" title=\"Difference Between Tables And Delta Tables\">Difference Between Tables And Delta Tables<\/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\/delta-tables-in-databricks\/#How_To_Create_A_Delta_Table_In_Databricks\" title=\"How To Create A Delta Table In Databricks\">How To Create A Delta Table In 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\/delta-tables-in-databricks\/#How_To_Convert_A_Parquet_File_To_A_Delta_Table\" title=\"How To Convert A Parquet File To A Delta Table\">How To Convert A Parquet File To A Delta Table<\/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\/delta-tables-in-databricks\/#How_To_Read_A_Delta_Table\" title=\"How To Read A Delta Table\">How To Read A Delta Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/bandittracker.com\/delta-tables-in-databricks\/#How_To_Get_The_Path_Of_A_Delta_Table\" title=\"How To Get The Path Of A Delta Table\">How To Get The Path Of A Delta Table<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_Is_A_Delta_Table_in_Databricks\"><\/span>What Is A Delta Table in Databricks?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>A delta table is the default format for a table in Databricks. When you create a table on the platform, it is stored as a delta table unless you specify otherwise.<\/p>\n\n\n\n<p>For example, this SQL command creates a delta table called &#8220;sales&#8221; with two columns:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>spark.sql(\"\"\"CREATE TABLE sales (product STRING, orders INT)\"\"\")<\/code><\/pre>\n\n\n\n<p>The storage format is based on Parquet, which makes it a columnar-based file format optimized for data analytics. The main difference is that data tables have additional transaction logs that track data changes.<\/p>\n\n\n\n<p>This means that you can update delta tables directly. In contrast, the data in Parquet files cannot be updated. Instead, the entire file must be replaced.<\/p>\n\n\n\n<p>In practice, delta tables bring the big data capabilities of Spark and Parquet to the traditional needs of the data warehouse.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"400\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/databricks.jpg\" alt=\"\" class=\"wp-image-991\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/databricks.jpg 600w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/databricks-300x200.jpg 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Differences_Between_Parquet_And_Delta_Tables\"><\/span>Differences Between Parquet And Delta Tables<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>As I mentioned, Parquet files are immutable i.e. the data within cannot be updated. The most obvious difference with Parquet is that you can perform CRUD operations (update, merge, delete) directly against delta tables.<\/p>\n\n\n\n<p>But that&#8217;s not the only difference. Let&#8217;s look at some more:<\/p>\n\n\n\n<ul>\n<li>Time travel with transaction logs<\/li>\n\n\n\n<li>Query performance with z-ordering and data skipping<\/li>\n\n\n\n<li>Caching on local worker nodes<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Time Travel<\/h3>\n\n\n\n<p>With some database technologies, you have to install and deploy change tracking features as an optional extra. Microsoft SQL Server is one example.<\/p>\n\n\n\n<p>But change tracking comes in-built with delta tables. Again, the functionality is provided through the transaction logs that accompany each table.<\/p>\n\n\n\n<p>In Databricks parlance, this is known as Time Travel. The feature lets you query the data as it was at a point in time in recent history.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Query Performance<\/h3>\n\n\n\n<p>You may be familiar with using partitioning to increase the performance of data retrieval from Parquet files. As delta tables are built on top of Parquet, they also get the benefits.<\/p>\n\n\n\n<p>But they come with the added benefits of what&#8217;s known as z-ordering and data skipping.<\/p>\n\n\n\n<p>Z-ordering is a feature that groups related information in the same set of files. If that sounds a bit like partitioning &#8211; well, it is. <\/p>\n\n\n\n<p>But the key point about z-ordering is that it works really well with high-cardinality values e.g. increasing numeric IDs. Partitioning doesn&#8217;t cope well with high cardinality (you get the &#8220;lots of small files&#8221; problem).<\/p>\n\n\n\n<p>By default, delta tables apply z-ordering to the first thirty-two columns of your table. You should bear that in mind when designing your data model.<\/p>\n\n\n\n<p>Crucially, the delta tables also maintain hi\/lo statistics on the z-order columns. This means that they track the max and min values at the file level.<\/p>\n\n\n\n<p>These statistics allow filtered queries to take advantage of <strong>data skipping<\/strong>. The query processor can check the max\/min values within a file to determine if its values fall outside of the filter. If they do, the file can be skipped.<\/p>\n\n\n\n<p>This can save a lot of query processing overhead.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"400\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/speed.jpg\" alt=\"\" class=\"wp-image-909\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/speed.jpg 600w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/speed-300x200.jpg 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure><\/div>\n\n\n<h3 class=\"wp-block-heading\">Caching For Performance<\/h3>\n\n\n\n<p>You may be thinking that Spark already has caching features. That&#8217;s true.<\/p>\n\n\n\n<p>But delta tables come with a special cache known as the Delta Cache that can store copies for remote data locally on worker nodes.<\/p>\n\n\n\n<p><em>I like the name Delta Cache because it&#8217;s so obvious. I hate the name Time Travel. It took me a while to realize that this was simply change tracking. But I digress&#8230;<\/em><\/p>\n\n\n\n<p>Suppose you have queries that run repeatedly against the same result set. You can use the CACHE keyword to copy the result set to the Delta Cache. Subsequent queries will use the locally cached data instead of reaching out to the underlying Parquet fields.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Difference_Between_Tables_And_Delta_Tables\"><\/span>Difference Between Tables And Delta Tables<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>I mentioned that Databricks tables are delta by default. But you can create other types of tables on the platform.<\/p>\n\n\n\n<p>For example, you can create a table that is bound to data in an external system. This means that the table name is really a pointer to the external storage.<\/p>\n\n\n\n<p>You can also create a table that specifically uses an alternative file format, such as JSON or Avro. The USING keyword after the CREATE TABLE syntax provides this functionality.<\/p>\n\n\n\n<p>Here&#8217;s a statement that creates a CSV-format table based on an external directory.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE agencies USING CSV LOCATION '\/mnt\/external\/csv_file';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">How to check if a table is delta or not<\/h3>\n\n\n\n<p>If you want to check if you&#8217;re dealing with a delta table or otherwise, you can use a Boolean indicator provided by the DeltaTable module.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DeltaTable.isDeltaTable(spark, \"tmp\/sales\")<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Create_A_Delta_Table_In_Databricks\"><\/span>How To Create A Delta Table In Databricks<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>There are several ways to create a delta table. We&#8217;ll look at two: <\/p>\n\n\n\n<ul>\n<li>using PySpark<\/li>\n\n\n\n<li>Using SQL<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Using PySpark and DataFrames<\/h3>\n\n\n\n<p>First, create your DataFrame. Here is some sample code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>columns = &#91;\"product\", \"orders\"]\ndata = &#91;(\"football\", \"529\"), (\"helmet\", \"293\")]\n\nrdd = spark.sparkContex.parallelize(data)\ndf = rdd.toDF(columns)<\/code><\/pre>\n\n\n\n<p>Now that you have your DataFrame, you can write it to a new delta table.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>df.write.format(\"delta\").saveAsTable(\"sales\")<\/code><\/pre>\n\n\n\n<p>You can also use the df.write method to write a DataFrame created from reading data from a CSV file.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Using SQL<\/h3>\n\n\n\n<p>Let&#8217;s create our sales table using a SQL command and populate it with some data:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>spark.sql(\"\"CREATE TABLE sales (product STRING, orders INT) USING delta\"\"\")\n\nspark.sql(\"\"\"INSERT INTO sales VALUES ('football', '529'), ('helmet', '293')\"\"\")<\/code><\/pre>\n\n\n\n<p>Note that &#8220;USING delta&#8221; is optional as delta is the default file format.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Which should you use?<\/h3>\n\n\n\n<p>You may be wondering if you should use PySpark or SQL? You should use the language you&#8217;re most familiar with.<\/p>\n\n\n\n<p>However, if you&#8217;re going to use PySpark, be aware that <a href=\"https:\/\/bandittracker.com\/databricks-sql-standard\/\" data-type=\"post\" data-id=\"994\">ANSI SQL is Databrick&#8217;s standard<\/a>. There are some differences in the syntax and behavior between the two dialects.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Convert_A_Parquet_File_To_A_Delta_Table\"><\/span>How To Convert A Parquet File To A Delta Table<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You could read the Parquet file into a DataFrame and save it back to the platform as a delta table (see the previous section). However, there&#8217;s a single-step process to do this transformation.<\/p>\n\n\n\n<p>The DeltaTable.convertToDelta API lets you do this as an in-place operation.<\/p>\n\n\n\n<p>Suppose you have a parquet file at &#8220;\/tmp\/sales1&#8221; that you want to convert to a delta table. Here is the code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>From delta.tables import *\n\ntbl = DeltaTable.convertToDelta(spark, \"parquet.tmp\/sales1\")<\/code><\/pre>\n\n\n\n<p>This operation adds transaction logs to the original Parquet file. That&#8217;s what turns it into a delta table!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Read_A_Delta_Table\"><\/span>How To Read A Delta Table<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can use the table name of the file path to read data from a delta table.<\/p>\n\n\n\n<p>If you&#8217;re using SQL, then the select statement with the table name is the same as any other DBMS:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM sales;<\/code><\/pre>\n\n\n\n<p>There&#8217;s a bit more to remember if you&#8217;re referencing a path. The syntax will look like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM delta.'\/tmp\/sales';<\/code><\/pre>\n\n\n\n<p>If you&#8217;re using PySpark then this syntax will pull the data from the sales table into a DataFrame:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>df = spark.read.table(\"sales\")<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Get_The_Path_Of_A_Delta_Table\"><\/span>How To Get The Path Of A Delta Table<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Delta tables are stored as collection of files in cloud storage. If you&#8217;re not sure where a specific table is stored ,you can use the DESC FORMATTED command. Here&#8217;s an example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>spark.sql(\"DESC FORMATTED chicago.sales\").show(truncate = false)<\/code><\/pre>\n\n\n\n<p>This outputs quite a lot of detail: scroll and look for the location description.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Delta tables are a core feature of Databricks. This article assumes you are completely new to the platform but have some familiarity with SQL or Spark basics. Read on to get an understanding of what they are, how they differ from other storage formats, and how to create and use them. What Is A Delta &#8230; <a title=\"Delta Tables In Databricks (For Beginners)\" class=\"read-more\" href=\"https:\/\/bandittracker.com\/delta-tables-in-databricks\/\" aria-label=\"More on Delta Tables In Databricks (For Beginners)\">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\/1002"}],"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=1002"}],"version-history":[{"count":3,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/1002\/revisions"}],"predecessor-version":[{"id":1006,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/1002\/revisions\/1006"}],"wp:attachment":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/media?parent=1002"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/categories?post=1002"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/tags?post=1002"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}