Using built-in SQL functions in Delta Live tables

colt — Sat, 09 Jul 2022 19:28:08 GMT

Do Delta Live Tables have different built-in SQL functions than the corresponding Databricks runtime?

I created a cluster with Databricks runtime 10.3 (the current DLT runtime) so I could test my Delta Live Tables code before running it as a pipeline. I was able to use the built-in SQL functions split() and element_at() within my interactive workbook, but then when I tried to use them in a DLT run, the same code produced a null value.

Is there a reference guide for Delta Live Tables that shows the built-in functions (if different from the corresponding Databricks runtime built-in functions)?

The code I ran was to split a file path to extract just the file name. It took a string like this:

"s3://example-bucket/folder/secondfolder/thirdfolder/b4618a0e-ffb8-11ec-9bbe-00163e43d8f1.json"

and extracted this:

"b4618a0e-ffb8-11ec-9bbe-00163e43d8f1"

Here's the code, which works fine in interactive 10.3 runtime, but doesn't work in DLT:

spark.sql("""

SELECT

element_at(split(element_at(split(file_name, '/'), -1), '\\.'), 1) as file_guid,

FROM my_tablename""")

Re: Using built-in SQL functions in Delta Live tables

Anonymous — Tue, 19 Jul 2022 09:19:44 GMT

https://databricks.com/discover/pages/getting-started-with-delta-live-tables

Re: Using built-in SQL functions in Delta Live tables

Vidula — Fri, 02 Sep 2022 06:31:53 GMT

Hi @Colt Kesselring

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help.

We'd love to hear from you.

Thanks!

topic Re: Using built-in SQL functions in Delta Live tables in Data Engineering

Using built-in SQL functions in Delta Live tables

Re: Using built-in SQL functions in Delta Live tables

Re: Using built-in SQL functions in Delta Live tables