Introducing SQL Scripting in Databricks, Half 2

Partly two of the SQL Scripting announcement weblog collection, we’ll study the executive job we mentioned in half one—methods to apply a case-insensitive rule to each STRING column in a desk. We’ll stroll by means of that instance step-by-step, clarify the options used, and develop it past a single desk to cowl a complete schema.

You can even comply with alongside on this pocket book.

Altering the collation of all textual content fields in all tables in a schema

Databricks helps a variety of language-aware, case-insensitive, and accent-insensitive snacks. It is easy to make use of this function for brand new tables and columns. However what when you’ve got an current system utilizing higher() or decrease() in predicates in all places and need to decide up the efficiency enhancements related to a local case-insensitive collation whereas simplifying your queries? That can require some programming; now you are able to do all of it in SQL.

Let’s use the next take a look at schema:

The order is predicated on the ASCII codepoints, the place all uppercase letters precede all lowercase letters. Are you able to repair this with out including higher() or decrease()?

Dynamic SQL statements and setting variables

Our first step is to inform the desk to vary its default collation for newly added columns. You’ll be able to feed your native variables with parameter markers, which the pocket book will mechanically detect and add widgets. You can even use EXECUTE IMMEDIATE to run a dynamically composed ALTER TABLE assertion.

Each SQL script consists of a BEGIN .. END (compound) assertion. Native variables are outlined first inside a compound assertion, adopted by the logic.

That is all only a set of linear statements. Up to now, you could possibly write all this with SQL Session variables with out the compound assertion. You additionally haven’t achieved a lot. In any case, you needed to vary the collation for current columns. To do that, it’s good to:

• Uncover all current string columns within the desk
• Change the collation for every column

In brief, it’s good to loop over the INFORMATION_SCHEMA.COLUMNS desk.

Loops

SQL Scripting gives 4 methods of looping and methods to manage loop iterations.

1. LOOP … END LOOP;
   It is a “endlessly” loop.
   This loop will proceed till an exception or an express ITERATE or LEAVE command breaks out of the loop.
   We are going to talk about exception dealing with later and level to the ITERATE and LEAVE documentation explaining methods to management loops.
2. WHILE predicate DO … END WHILE;
   This loop shall be entered and re-entered so long as the predicate expression evaluates to true or the loop is damaged out by an exception, ITERATE or LEAVE.
3. REPEAT … UNTIL predicate END REPEAT;
   In contrast to WHILE, this loop is entered at the least as soon as and re-executes till the predicate expression evaluates to false or the loop is damaged by an exception, LEAVE, or ITERATE command.
4. FOR question DO …. END FOR;
   This loop executes as soon as per row the question returns except it’s left early with an exception, LEAVE, or ITERATE assertion.

Now, apply the FOR loop to our collation script. The question will get the column names of all string columns of the desk. The loop physique alters every column collation in flip:

Let’s confirm that the desk has been correctly up to date:

Up to now, so good. Our code is functionally full, however it is best to inform Delta to investigate the columns you modified to learn from file skipping. You do not need to do that per column. However collect all of them collectively and do the work provided that there was, in reality, a string column for which the collation was altered. Selections, choices ….

Conditional logic

SQL Scripting gives 3 ways to carry out conditional execution of SQL statements.

1. If-then-else logic. The syntax for that is simple:
   IF predicate THEN … ELSEIF predicate THEN … ELSE …. END IF;
   Naturally, you possibly can have any variety of non-compulsory ELSEIF blocks, and the ultimate ELSE can also be non-compulsory.
2. A easy CASE assertion
   This assertion is the SQL Scripting model of the easy case expression.
   CASE expression WHEN possibility THEN … ELSE … END CASE;
   A single execution of an expression is in comparison with a number of choices, and the primary match decides which set of SQL statements ought to be executed. If none match, the non-compulsory ELSE block shall be executed.
3. A searched CASE assertion
   This assertion is the SQL Scripting model of the searched case expression.
   CASE WHEN predicate THEN …. ELSE … END CASE;
   The THEN block is executed for the primary of any predicates that consider to true. If none match, the non-compulsory  ELSE block is executed.

For our collation script, a easy IF THEN END IF will suffice. You additionally want to gather the set of columns to use ANALYZE to and a few higher-order perform magic to provide the column listing:

Nesting

What you have got written up to now works for particular person tables. What if you wish to function on all tables in a schema? SQL Scripting is totally composable. You’ll be able to nest compound statements, conditional statements, and loops inside different SQL scripting statements.

So what you’ll do right here is twofold:

1. Add an outer FOR loop to search out all tables inside a schema utilizing INFORMATION_SCHEMA.TABLES. As a part of this, it’s good to exchange the references to the desk title variable with references to the outcomes of the FOR loop question.
2. Add a nested compound to maneuver the column listing variable down into the outer FOR loop. You can’t declare a variable straight within the FOR loop physique; it doesn’t add a brand new scope. That is primarily a call associated to coding type, however you’ll have a extra severe motive for a brand new scope..

This error is sensible. You may have a number of methods to proceed:

1. Filter out unsupported desk sorts, similar to views, within the info schema question. The issue is that there are quite a few desk sorts, and new ones are sometimes added.
2. Deal with views. That is an awesome thought. Let’s name that your homework project.
3. Tolerating the error situation

Exception dealing with

A key functionality of SQL Scripting is the flexibility to intercept and deal with exceptions. Situation handlers are outlined within the declaration part of a compound assertion, and so they apply to any assertion inside that compound, together with nested statements. You’ll be able to deal with particular error situations by title, particular SQLSTATEs dealing with a number of error situations, or all error situations. Throughout the physique of the situation handler, you need to use the GET DIAGNOSTICS assertion to retrieve details about the exception being dealt with and execute any SQL scripting you deem acceptable, similar to recording the error in a log or operating an alternate logic to the one which failed. You’ll be able to then SIGNAL a brand new error situation, RESIGNAL the unique situation, or just exit the compound assertion the place the handler is outlined and proceed with the next assertion.

In our script, you need to skip any assertion for which the ALTER TABLE DEFAULT COLLATION assertion didn’t apply and log the thing’s title.

Above, you have got developed an administrative script purely in SQL. You can even write ELT scripts and switch them into Jobs. SQL Scripting is a very highly effective software it is best to exploit.

What to do subsequent

Whether or not you might be an current Databricks person or migrating from one other product, SQL Scripting is a functionality it is best to use. SQL Scripting follows the ANSI commonplace and is totally appropriate with OSS Apache Spark™. SQL Scripting is described intimately in SQL Scripting | Databricks Documentation.

You can even use this pocket book to see for your self.