Create a UDF Table Function with DLT in UC

alexiswl
Contributor

‎11-08-2023 06:19 PM

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
```



0 Kudos