Using SQL Statements with Databricks

Introduction

By combining the Databricks format with SQL statements, users can leverage the Data Lakehouse's processing power to bring their data to life with FME. The Databricks format also lets you use SQL statements unique to Delta Lake, such as time travel with Delta Lake table history.

This article provides examples of different ways SQL statements can enhance performance and advance workflows.

Reading Delta Table History

Each operation that modifies a Delta Lake table creates a new table version, making it easy to read past versions before certain changes. Using SQL queries unique to Databricks Delta tables, FME can read different table versions using the SQLCreator or SQLExecutor transformers. Databricks calls this functionality time travel.

For more information, see the Databricks documentation.

Retrieving the Table Version

Using the DESCRIBE HISTORY SQL statement, you can retrieve an overview of the table history. This includes information such as the operations, user, and timestamp for each change to a Delta table. By default, table history retention is 30 days, but you can adjust it in Databricks.

In FME, an SQLCreator or SQLExecutor transformer can be used to perform the DESCRIBE HISTORY statement to discover what versions exist for a Databricks Delta table. We can also add Attributes to Expose using the Populate from SQL Query function. This will return information such as the version, timestamp, userId of who performed the change, clusterId, and more. 

If you are unsure which table you want to read, you can expand the Database Tables drop-down menu on the left side of the window and double-click the table you want to add to the SQL query builder.

Running SQLCreator outputs the delta table versions. Below is an example of the output

AI Assist

New to FME 2023.1, AI Assist is available in the SQL Statement Editor to help new and experienced users build SQL Statements. You can also send schema information from your database to the AI service to get a result with the correct table and column names.

How to use the AI Assist with SQL

In FME 2023.1 and greater, you can access the AI Assist through the SQL Statement Editor. It appears as a button in the bottom-left corner of the window.

Using the sample screenshot below, we will provide an overview of each component of the AI Assist window.

1.  SQL Query Description

This is where you can specify your request in plain English. For example:

"Get all records where neighborhood is fairview from the publicart table"

This would return a SQL statement such as: 

SELECT * FROM "public"."publicart" WHERE "neighborhood" = 'Fairview'

Send the database schema to AI

When enabled, FME scans your database schema to provide a more accurate result from the AI Assist. The resulting SQL is tailored to your database tables and columns instead of using generic names. 

2. Status

Provides an overview of the AI request status and whether it succeeded.

3. AI Results

These are the results from the description after it has been run through the AI data model. This will include a functional SQL query to use in the SQL editor. If Send database schema to AI is enabled, then the query may include accurate table and column names.

4. Explanation

To help users understand the AI results, each object within the SQL query is broken down with an explanation.

Disclaimers

For legal information about AI Assist, see our AI Terms of Use.

If you have any feedback regarding the use of AI Assist in the SQL Editor, please comment on this article or contact us at info@safe.com.

Reading a Table Version with a Version Number

You can use the version number retrieved in SQLCreator to query a specific Delta table version. Once the user knows the table versions, they can query a specific Delta table version. Using the VERSION AS OF version_number statement, users can query a specific version number.

In SQLCreator, specify the version number to read after a SELECT statement. For example, if we want to read version 10 of the table, we use the following query:

SELECT * FROM ‘default’.’publicart’
VERSION AS OF 10

Reading different versions of the same table returns different results. In our example, Version 19 returns 2,180,000 features, whereas Version 10 returns only 663,000 features. Using Delta table functionality, we can get completely different results from the same table stored in Databricks.

Reading a Table Version with a Timestamp

You can also read Databricks Delta table versions using a specific timestamp. This can be a datetime string (2023-03-10T12:42:00) or a date string (2023-03-10).

The timestamp_expression can be any one of the following:

• '2018-10-18T22:15:12.013Z', that is, a string that can be cast to a timestamp
• cast('2018-10-18 13:36:32 CEST' as timestamp)
• '2018-10-18', that is, a date string
• current_timestamp() - interval 12 hours
• date_sub(current_date(), 1)
• Any other expression that is or can be cast to a timestamp

Again, using the SQLCreator/SQLExecutor, we can query our features with a SELECT statement, then read the version we want based on a timestamp. For example, if we wanted to read what the table looked like as of 2023-04-03, we can use the following SQL:

SELECT * FROM ‘default’.’publicart’
TIMESTAMP AS OF ‘2023-04-03’

In the example above, we read the table with the timestamp 2023-04-03 and received 670 features.

Performing a WHERE Clause

Reading in only the data (attributes and features) you need for your workflow can significantly improve overall performance. By restricting the features you read from Databricks with a view, WHERE clause, or SELECT statement, you can streamline the data flow within your workspace and improve speed.

To do so, we can use the SQLCreator or SQLExecutor to specify a WHERE Clause before reading in our data. The Databricks reader also lets you use a WHERE clause in the reader feature type. However, the SQLCreator/SQLExecutor allow for more control over your query, so if you’re familiar with SQL, this is the preferred method.

The SQL Statement looks like the others we’ve used so far, starting with a SELECT statement. Afterward, we can specify the WHERE clause, which dictates how we filter the data before reading it in. 

In this case, we want to filter our public art dataset by the Strathcona Neighborhood

For this example, we used the following:

SELECT * FROM ‘default’.’publicart’
WHERE GeoLocalArea = ‘Strathcona’ AND SiteAddress IS NOT NULL

Performance Comparison

Before setting the WHERE clause, 3,153,000 features are read, taking 1 minute 55.3 seconds.

After setting the WHERE clause, only 204,000 features are read, taking just 13.9 seconds. That’s an 88% increase in performance and processing time using just a simple WHERE Clause.

Performing a Join

In this example, the SQLCreator transformer joins and reads records from two Databricks tables. SQLCreator lets users run complex SQL queries against their data in Databricks, which FME then reads into features. The queries run as they would in a Databricks notebook, giving users the full power of the Databricks environment.

We have a table of address points in a Databricks table and another Databricks table called postal_addresses. Both datasets contain a common identifier field called AddressID. Since the address points dataset contains limited information, we want to join the two tables before reading them into FME. By joining the two, we can enrich the information before integrating it with other systems.

The SQL query is already prepared in our Databricks notebook. You can copy and paste any queries written in Databricks notebooks directly into the SQLCreator SQL Statement builder. This makes it easy to use scripts already created in Databricks in FME.

In the SQLCreator, we took the SQL query from our Databricks notebook and pasted it directly in the SQL Statement builder.

Once set, the parameters look as follows:

With this SQL query, we join our features using the ‘AddressId’ field in both tables. A WHERE clause also narrows the results to features where the PostalCode begins with ‘V6J’. By performing the join using the Databricks environment, we can leverage the database's speed alongside FME's integration capabilities.

Additional Resources

The examples provided above only touch on a few of the many uses for SQL with FME. For more ideas on how to take advantage of SQL with Databricks, take a look at these articles for inspiration and ideas on how to use SQL statements in FME. 

• Let The Database Do The Work
• Database Tips and Tricks In FME
• The SQLCreator and SQLExecutor Transformers