topic Create a UDF Table Function with DLT in UC in Data Engineering

Create a UDF Table Function with DLT in UC

alexiswl — Thu, 09 Nov 2023 02:19:15 GMT

Hello,

I am trying to generate a DLT but need to use a UDF Table Function in the process.

This is what I have so far, everything works (without e CREATE OR REFRESH LIVE TABLE wrapper)

```sql

CREATE OR REPLACE FUNCTION silver.portal.get_workflows_from_workflow_id(workflow_id INT)

RETURNS TABLE(libraryrun_id INT)

RETURN

SELECT libraryrun_id

FROM bronze.portal.libraryrun_workflows

WHERE workflow_id == get_workflows_from_workflow_id.workflow_id

;

CREATE OR REFRESH LIVE TABLE workflow_run_ids_by_bclconvert

COMMENT "View all workflows triggered downstream of a bclconvert run"

AS (

WITH

bclconvert_workflow_ids AS (

SELECT id AS bclconvert_workflow_id, split(wfr_name, '__')[3] AS bclconvert_run_name

FROM bronze.portal.workflow

WHERE type_name == 'bcl_convert'

workflow_runs_with_bclconvert_run_name AS (

SELECT bclconvert_workflow_ids.bclconvert_workflow_id, bclconvert_workflow_ids.bclconvert_run_name, get_workflows_from_workflow_id.libraryrun_id

FROM bclconvert_workflow_ids,

LATERAL get_workflows_from_workflow_id(bclconvert_workflow_ids.bclconvert_workflow_id)

)

SELECT

workflow_runs_with_bclconvert_run_name.bclconvert_workflow_id,

workflow_runs_with_bclconvert_run_name.bclconvert_run_name,

workflow_runs_with_bclconvert_run_name.libraryrun_id,

workflow.type_name,

workflow.portal_run_id,

workflow.`start`,

workflow.`end`

FROM workflow_runs_with_bclconvert_run_name

INNER JOIN bronze.portal.libraryrun AS libraryrun

INNER JOIN bronze.portal.libraryrun_workflows AS libraryrun_workflows

INNER JOIN bronze.portal.workflow AS workflow

WHERE

workflow_runs_with_bclconvert_run_name.libraryrun_id = libraryrun.id

AND

workflow_runs_with_bclconvert_run_name.libraryrun_id = libraryrun_workflows.libraryrun_id

AND

libraryrun_workflows.workflow_id = workflow.id

)

```

However, this results in the following error:

```

[UC_COMMAND_NOT_SUPPORTED.WITHOUT_RECOMMENDATION] The command(s): Delta Live Table operations are not supported in Unity Catalog.

error
```

Re: Create a UDF Table Function with DLT in UC

shan_chandra — Tue, 14 Nov 2023 19:34:31 GMT

@alexiswl - could you please use CREATE OR REPLACE function instead of CREATE OR REFRESH LIVE table?

Re: Create a UDF Table Function with DLT in UC

alexiswl — Wed, 15 Nov 2023 02:58:55 GMT

Hi @shan_chandra, unfortunately this is in the context of creating a DLT, so if I update as suggested, I get the following error:

```

org.apache.spark.sql.AnalysisException: The following code at '/Repos/services+databricks@umccr.org/data-portal-analytics/delta_live_tables/silver/silver_portal_dlt' is not supported in a DLT pipeline: CREATE OR REPLACE FUNCTION silver.portal.get_workf... Please correct your code and make sure you only have DLT statements. For reference: DLT currently accepts 'CREATE MATERIALIZED VIEW', 'CREATE STREAMING TABLE', 'APPLY CHANGES INTO', and 'SET' statements.

```

Re: Create a UDF Table Function with DLT in UC

alexiswl — Wed, 15 Nov 2023 23:56:23 GMT

Hi all,

I have figured out my error.

The function creation should NOT be in the same notebook as the one used by the DLT pipeline.

Instead, create the function separately and register it in Unity Catalog, then reference the function in the DLT pipeline.

As a side note, UDF support for DLT is only available in DBR 13.3 and above. At the time of writing this, you will need to configure the runtime to 'preview' over 'current' to enable UDFs in DLT.