https://community.databricks.com/t5/technical-blog/data-engineering-sql-holiday-specials/ba-p/104905 <P><SPAN>December is the most celebrated time of year in the Data Engineering calendar as we embrace the important holiday: change freeze season.&nbsp;</SPAN></P> <P><SPAN>As we come back to the office to start our new projects, I wanted to make you aware of some quietly released features in the data engineering ecosystem in Databricks in the hope they might benefit you. To try and spice up what is essentially a list of features, I’ve categorised them into release stage (GA vs PuPr) as well as the Holly Smith scale of feature usefulness:</SPAN></P> <H5><SPAN><span class="lia-unicode-emoji" title=":spouting_whale:">🐳</span> Extra Large - <span class="lia-unicode-emoji" title=":elephant:">🐘</span> Large&nbsp; - 🦩 Medium - 🦔 Small</SPAN></H5> <H1><SPAN>General Availability</SPAN></H1> <H2><SPAN><span class="lia-unicode-emoji" title=":elephant:">🐘</span> New SQL functions:</SPAN></H2> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN><FONT face="courier new,courier">try_url_decode()</FONT> - instead of coding in try/catch for decoding error prone URLs into ASCII with <FONT face="courier new,courier">url_decode()</FONT>, simplify your code with <FONT face="courier new,courier">try_url_decode()</FONT> to check your URLs are valid before returning them.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN><FONT face="courier new,courier">zeroifnull()</FONT> and <FONT face="courier new,courier">nullifzero()</FONT> - does what it says, think of it as a more specific <FONT face="courier new,courier">coalesce()</FONT> or if then else logic.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN><FONT face="courier new,courier">day()</FONT> - can now be used as a synonym for extract(DAY from valid_date) for cleaner code</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN><FONT face="courier new,courier">uniform()</FONT> - will generate random numbers with a uniform, ie even, distribution. The benefit of this over rand() is the option to specify upper and lower bounds without having to do the maths yourself</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN><FONT face="courier new,courier">randstr()</FONT> - generate a random string of uppercase, lowercase letters and numbers of specified length</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>More functions now have named parameter invocation. Syntactic sugar good for code readability and to match the majority of functions that already have this feature, although given that all the functions updated have only one argument, this is a 🦔 for me. Updated functions: <FONT face="courier new,courier">variant_explode<FONT face="arial,helvetica,sans-serif">,&nbsp;</FONT>variant_explode_outer<FONT face="arial,helvetica,sans-serif">,</FONT> inline<FONT face="arial,helvetica,sans-serif">,</FONT> inline_outer<FONT face="arial,helvetica,sans-serif">,</FONT> posexplode<FONT face="arial,helvetica,sans-serif">,</FONT> posexplode_outer</FONT><FONT face="arial,helvetica,sans-serif">.&nbsp;</FONT></SPAN></LI> </UL> <P><SPAN>If you’d like to see worked examples, check out this notebook <A href="https://notebooks.databricks.com/devrel/nbs/%F0%9F%8E%84Data%20Engineering%20SQL%20Holiday%20Specials.html" target="_blank" rel="noopener">here</A>.</SPAN></P> <H2><SPAN><span class="lia-unicode-emoji" title=":elephant:">🐘</span> New workflows task type - .sql files</SPAN></H2> <P><SPAN>Not to be confused with sql <EM>queries</EM>, a .sql file can now be run in workflows. What’s even better is this can come from a repo and so the file could in theory have been authored in a different tool. For some of you, this will open up a new way of architecting your workflows and make interoperability between tools easier. </SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="holly_0-1736421009114.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/13969iA66FEE1D625D9BDE/image-size/medium?v=v2&amp;px=400" role="button" title="holly_0-1736421009114.png" alt="holly_0-1736421009114.png" /></span></P> <H2><SPAN>🦩Primary Key and Foreign Key relationship constraints</SPAN></H2> <P><SPAN>Tables in Unity Catalog can now be updated or created with definitions of primary keys and foreign key relationships that are persisted in the information_schema and reflected in the UI. </SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="holly_1-1736421024246.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/13970i2D011D167B8F2874/image-size/large?v=v2&amp;px=999" role="button" title="holly_1-1736421024246.png" alt="holly_1-1736421024246.png" /></span></P> <P><SPAN>To see the full syntax of how these tables are created, check out the notebook <A href="https://notebooks.databricks.com/devrel/nbs/%F0%9F%8E%84Data%20Engineering%20SQL%20Holiday%20Specials.html" target="_blank" rel="noopener">here</A>.&nbsp;</SPAN></P> <P><SPAN>For some of you this is a massive deal, however there is a snag; at the moment these are informational and not enforced. If I write a blog about them being enforced, you can guarantee that’s a <span class="lia-unicode-emoji" title=":spouting_whale:">🐳</span> feature.</SPAN></P> <P><SPAN>Side note: 10/10 for the AI trying to make sense of these ridiculous tables. </SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="holly_2-1736421037458.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/13971iBDCE47FB80D8DD4D/image-size/large?v=v2&amp;px=999" role="button" title="holly_2-1736421037458.png" alt="holly_2-1736421037458.png" /></span></P> <H2><SPAN>🦩Structured Streaming and liquid clustering compatibility Improvements</SPAN></H2> <P><SPAN>Up until now you had to define upfront an empty table and the clustering keys before streaming into it, which, yes might be production best practice but a nasty papercut for quickly testing or debugging something.</SPAN></P> <P><SPAN>This is no longer the case with structured streaming writes and the following will now work:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">(spark.readStream.format("rate") # generate some random data .load() .writeStream .clusterBy("timestamp") # &lt;- this bit is new and now possible .option("checkpointLocation", "dbfs:/tmp/checkpoint/random_numbers") .trigger(once=True) # let's not have this run forever .toTable("random_numbers") # new table created as part of this syntax )</LI-CODE> <P>&nbsp;</P> <P><FONT size="1 2 3 4 5 6 7"><SPAN>Yes I know this is a python example in a SQL blog, but I can't make a random number generator easily stream in SQL.</SPAN></FONT></P> <P><SPAN>To validate this has been successful, you can run <FONT face="courier new,courier">DESCRIBE EXTENDED</FONT> against the table name and look for clustering information, or see it in the table properties in the last row of the results.&nbsp;</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Public Previews</SPAN></H1> <H2><SPAN><span class="lia-unicode-emoji" title=":spouting_whale:">🐳</span> Oracle Lakehouse Federation</SPAN></H2> <P><SPAN>Connect to Oracle with Databricks in a one time setup to let users query a table just like they would any in Unity Catalog.</SPAN></P> <P><SPAN>The exact setup steps aren’t something Im going to replicate here as they are&nbsp;</SPAN><A href="https://docs.databricks.com/en/query-federation/oracle.html" target="_blank" rel="noopener"><SPAN>fully documented</SPAN></A><SPAN>, but in short:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Check the requirements for UC, compute, networking and permissions</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Create a Databricks connection for external data. You’ll need hostname, port, user and password details</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Create a Foreign catalog in UC to mirror an Oracle database</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>[Optional] create views that make the best use of supported performance pushdowns like filter and limit</SPAN></LI> </UL> <P><SPAN>Keep in mind the data type mappings, if you don’t have timezones on your timestamps you might quickly end up in timezone hell.&nbsp;</SPAN></P> <P><SPAN>It won’t look particularly exciting in the catalog explorer UI either. To the uninitiated it’ll just show as another standard catalog.</SPAN></P> <P><SPAN>For me, this is an enormous win for breaking down silos and easy movement of data. You can now start plugging this in with other Databricks features like workflows and genie to dramatically simplify your data landscape.</SPAN></P> <H2><SPAN><span class="lia-unicode-emoji" title=":elephant:">🐘</span> Warehouse system tables&nbsp;</SPAN></H2> <P><SPAN>Finally! Programmatic access to warehouse data in system tables. This will allow you to see what’s been created and deleted over the last 30 days. Great for admins wanting to keep an eye on how many 10 cluster 4XL warehouses are being created. You can also use the data to find oversized auto stop times or those not tagged.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="holly_3-1736421071673.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/13972i24863629AC9EBF64/image-size/medium?v=v2&amp;px=400" role="button" title="holly_3-1736421071673.png" alt="holly_3-1736421071673.png" /></span></P> <P><SPAN>To enable this, ask an account admin to run:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">%sh curl -v -X PUT -H "Authorization: Bearer &lt;Developer Access Token&gt;" "https://&lt;your workspace url&gt;.cloud.databricks.com/api/2.0/unity-catalog/metastores/&lt;metastore id found in UC&gt;/systemschemas/compute" </LI-CODE> <P>&nbsp;</P> <P>&nbsp;</P> <H2><SPAN><span class="lia-unicode-emoji" title=":elephant:">🐘</span>Materialised View updates</SPAN></H2> <P><SPAN>MVs have had a few welcome updates recently, notably the ability to monitor runs with the query history UI, masking parity, and an easier way to schedule that doesn’t require deciphering CRON syntax. All of these have working examples in this notebook <A href="https://notebooks.databricks.com/devrel/nbs/%F0%9F%8E%84Data%20Engineering%20SQL%20Holiday%20Specials.html" target="_blank" rel="noopener">here</A>.&nbsp;</SPAN></P> <P><SPAN>I’ll do anything to avoid using the quartz cron </SPAN><A href="https://www.quartz-scheduler.org/documentation/quartz-2.3.0/tutorials/crontrigger.html" target="_blank" rel="noopener"><SPAN>scheduler</SPAN></A><SPAN> (it’s a good site, I just lack the ability to retain any understanding of it). The new schedule is useful for standard intervals ranging from hours to weeks, but if you need something more intricate, it’s back to figuring out * vs ?</SPAN></P> <P><SPAN>Instead of&nbsp;</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">%sql CREATE MATERIALIZED VIEW ham_feasibility SCHEDULE CRON '0 0 * * * * *' --old schedule syntax AS . . .</LI-CODE> <P>&nbsp;</P> <P><SPAN>It’s now</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">%sql CREATE MATERIALIZED VIEW ham_feasibility SCHEDULE EVERY 1 HOUR --new schedule syntax AS . . .</LI-CODE> <P>&nbsp;</P> <P><SPAN>The next big update that MVs have had is the ability to see the performance in the Query History UI. Now it’s a lot easier to understand any drops in performance, especially if they’re sporadic and difficult to recreate. This can be accessed from the left hand menu and filter on compute as ‘DLT’ and statement as ‘REFRESH’ </SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="holly_4-1736421096010.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/13973iCDD6F24B2F37B47C/image-size/medium?v=v2&amp;px=400" role="button" title="holly_4-1736421096010.png" alt="holly_4-1736421096010.png" /></span></P> <P><SPAN>Another update to materialised views (and streaming tables) is the new support for filters and column masks.&nbsp;</SPAN></P> <P><SPAN>For row filtering, create a function like you would for any other table based on group membership, and add the keywords ‘with row filter’ to the creation statement</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">%sql CREATE MATERIALIZED VIEW ham_options (ham_type STRING, is_edible BOOLEAN) WITH ROW FILTER veggie_filter --apply the function we just defined AS SELECT * FROM ham_types --now this view will show results tailored to whether or not the user is part of the omnivore group stored in unity catalog</LI-CODE> <P>&nbsp;</P> <P><SPAN>And for column masking, define the function then apply it in the creation step after the defining the column</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">%sql CREATE OR REPLACE MATERIALIZED VIEW ham_options (ham_type STRING, is_edible BOOLEAN MASK undefined_preference) -- applying the column mask to our materialised view AS SELECT * FROM ham_types;</LI-CODE> <P>&nbsp;</P> <P>&nbsp;</P> <P><SPAN>One final new update for materialised views is the ability to set the compute to run with features only accessible in a private preview. This is done with the channel setting at table creation.</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">%sql CREATE MATERIALIZED VIEW preview_channel_example TBLPROPERTIES(pipelines.channel = "PREVIEW") --set the compute to use features in public preview AS SELECT day('2019-01-07') as result</LI-CODE> <P>&nbsp;</P> <P>&nbsp;</P> <H2><SPAN>🦔 Lite mode VACUUM&nbsp;</SPAN></H2> <P><SPAN>Do you ever run a pipeline and know that you </SPAN><I><SPAN>should</SPAN></I><SPAN> run a vacuum, but decide against it because it’ll take too long (but ultimately make the problem worse for yourself tomorrow)? Lite mode is a balance between the two, offering a faster option to prevent the proliferation of stale files.</SPAN></P> <P><SPAN>The syntax is dead easy, it's just&nbsp;</SPAN><FONT face="courier new,courier"><SPAN>VACUUM</SPAN><SPAN> table_name LITE;</SPAN></FONT></P> <P><SPAN>It doesn’t replace the default full version, it will only scan for stale files in the delta log and won’t do a full clean out checking for empty directories, parquet files from failed transactions or arbitrary files from connectors.&nbsp;</SPAN></P> <P><SPAN>This feature didn’t quite make it to 🦩 sized as the majority of people don’t have data big enough to be running into this problem. This feature is for you if you notice your VACUUM statements are taking over 15 mins.</SPAN></P> <H1><SPAN>Sources and Further Reading</SPAN></H1> <UL> <LI><SPAN>Release notes for <A href="https://docs.databricks.com/en/release-notes/runtime/index.html" target="_blank" rel="noopener">Runtimes</A>, <A href="https://docs.databricks.com/en/sql/release-notes/index.html" target="_blank" rel="noopener">SQL</A> and <A href="https://docs.databricks.com/en/release-notes/product/index.html" target="_blank" rel="noopener">Platform</A></SPAN></LI> <LI><SPAN><A href="https://docs.databricks.com/en/tables/constraints.html#declare-primary-key-and-foreign-key-relationships" target="_blank" rel="noopener">Key constraints</A></SPAN></LI> <LI><A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-create-materialized-view.html" target="_blank" rel="noopener"><SPAN>Materialised views</SPAN></A></LI> <LI><A href="https://docs.databricks.com/en/admin/system-tables/warehouses.html" target="_blank" rel="noopener"><SPAN>Warehouse system tables</SPAN></A></LI> <LI><SPAN>Notebook of <A href="https://notebooks.databricks.com/devrel/nbs/%F0%9F%8E%84Data%20Engineering%20SQL%20Holiday%20Specials.html" target="_blank" rel="noopener">examples</A></SPAN></LI> </UL> Thu, 09 Jan 2025 13:10:26 GMT holly 2025-01-09T13:10:26Z https://community.databricks.com/t5/technical-blog/data-engineering-sql-holiday-specials/ba-p/104905 <P><SPAN>December is the most celebrated time of year in the Data Engineering calendar as we embrace the important holiday: change freeze season.&nbsp;</SPAN></P> <P><SPAN>As we come back to the office to start our new projects, I wanted to make you aware of some quietly released features in the data engineering ecosystem in Databricks in the hope they might benefit you.</SPAN></P> Thu, 09 Jan 2025 13:10:26 GMT https://community.databricks.com/t5/technical-blog/data-engineering-sql-holiday-specials/ba-p/104905 holly 2025-01-09T13:10:26Z https://community.databricks.com/t5/technical-blog/data-engineering-sql-holiday-specials/bc-p/104980#M430 <P>Great insight about the latest DBRS features. Thanks for sharing the information!</P> Thu, 09 Jan 2025 15:51:13 GMT https://community.databricks.com/t5/technical-blog/data-engineering-sql-holiday-specials/bc-p/104980#M430 BamBam 2025-01-09T15:51:13Z