At this time, Databricks publicizes help for the ANSI SQL/PSM scripting language!
SQL Scripting is now accessible in Databricks, bringing procedural logic like looping and control-flow immediately into the SQL you already know. Scripting in Databricks is predicated on open requirements and absolutely appropriate with Apache Spark™.
For SQL-first customers, this makes it simpler to work immediately on the Lakehouse whereas making the most of Databricks’ scalability and AI capabilities.
In case you already use Databricks, you’ll discover SQL scripting particularly helpful for constructing administrative logic and ELT duties. Key options embrace:
- Scoped native variables
- Native exception dealing with based mostly on symbolic error situations
- IF-THEN-ELSE and CASE help
- A number of loop constructs, together with FOR loops over queries
- Loop management with ITERATE and LEAVE
- Dynamic SQL execution via EXECUTE IMMEDIATE
Sufficient with the characteristic checklist — let’s stroll via some actual examples. You possibly can use this pocket book to comply with alongside.
Information administration
Administrative duties and information cleanup are a continuing in enterprise information administration — needed, routine, and unattainable to keep away from. You’ll want to scrub up historic data, standardize blended codecs, apply new naming conventions, rename columns, widen information varieties, and add column masks. The extra you may automate these duties, the extra dependable and manageable your techniques will probably be over time. One widespread instance: implementing case-insensitive conduct for all STRING columns in a desk.
Let’s stroll via how SQL scripting could make this type of schema administration repeatable and easy.
Schema administration: make all STRING columns in a desk case-insensitive
On this instance, we need to apply a brand new coverage for string sorting and comparability for each relevant column within the desk known as staff. We are going to use a typical collation kind, UTF8_LCASE, to make sure that sorting and evaluating the values on this desk will all the time be case-insensitive. Making use of this customary permits customers to profit from the efficiency advantages of utilizing collations, and simplifies the code as customers not have to use LOWER() of their queries.
We are going to use widgets to specify which desk and collation kind to change. Utilizing the knowledge schema, we’ll then discover all current columns of kind STRING in that desk and alter their collation. We are going to accumulate the column names into an array. Lastly, we’ll accumulate new statistics for the altered columns, multi functional script.
A pure extension of the above script is to increase it to all tables in a schema, and refresh views to select up the collation change.
Information cleaning: repair grammar in free-form textual content fields
Is there any difficulty extra widespread on the planet of knowledge than ‘soiled information’? Information from completely different techniques, gadgets, and people, will inevitably have variations or errors that should be corrected. If information shouldn’t be cleaned up, you could have unsuitable outcomes and miss an essential perception. You possibly can anticipate a rubbish response if you happen to feed rubbish into an LLM.
Let’s have a look at an instance that features the bane of each publication, together with this weblog: typos. We have now a desk that features free-text entries in a column known as description. The problems within the textual content, which embrace spelling and grammar errors, could be obvious to anybody who is aware of English. Leaving the information on this state will undoubtedly result in points later if making an attempt to research or examine the textual content. Let’s repair it with SQL Scripting! First, we extract tables holding this column title from the knowledge schema. Then repair any spelling errors utilizing ai_fix_grammar(). This perform is non-deterministic. So we use MERGE to attain our aim.
An fascinating enchancment may very well be to let ai_classify() deduce whether or not a column incorporates free-form textual content from the column title or pattern information. SQL Scripting makes administrative duties and cleansing up messy information environment friendly and easy.
ETL
Clients use SQL for ETL in the present day. Why? As a result of SQL helps a strong set of knowledge transformation capabilities, together with joins, aggregations, filtering, with intuitive syntax, making pipeline code simple for any Information Engineer to increase, replace, and keep. Now, with SQL Scripting, clients can simplify beforehand complicated approaches or deal with extra complicated logic with pure SQL.
Updating a number of tables
Anybody who sells bodily merchandise can have a course of for monitoring gross sales and monitoring shipments. A typical information administration sample is to mannequin a number of tables to trace transactions, shipments, deliveries, and returns. Transaction monitoring is enterprise essential, and like all essential course of, it requires the dealing with of sudden values. With SQL Scripting, it’s simple to leverage a conditional CASE assertion to parse transactions into their applicable desk, and if an error is encountered, to catch the exception.
On this instance, we contemplate a uncooked transactions desk for which rows should be routed right into a identified set of goal tables based mostly on the occasion kind. If the script encounters an unknown occasion, a user-defined exception is raised. A session variable tracks how far the script received earlier than it completed or encountered an exception.
This instance script may very well be prolonged with an outer loop that retains polling for extra information. With SQL Scripting, you have got each the facility and adaptability to handle and replace information throughout your information property. SQL Scripting provides you energy to deal with any information administration job and effectively management the move of knowledge processing.
Keep tuned to the Databricks weblog and the SQL periods on the upcoming Information + AI Summit, as we put together to launch help for Temp Tables, SQL Saved Procedures, and extra!
What to do subsequent
Whether or not you might be an current Databricks person doing routine upkeep or orchestrating a large-scale migration, SQL Scripting is a functionality you need to exploit. SQL Scripting is described intimately in SQL Scripting | Databricks Documentation.
You possibly can strive these examples immediately on this SQL Scripting Pocket book. For extra particulars, keep tuned for Half 2 of this collection, which dives into SQL Scripting constructs and how you can use them.