Possibilities and Limitations of Delta Live Tables (DLT) with Direct Publish mode

[susanne]

Hi all,

I am implementing a DLT table with the new Direct Publish feature which is still in Private Preview.

Is it a limitation of DLT with Direct Publish that you can not query the event_log of the DLT?
When I use this query:

SELECT *
FROM event_log('pipeline_id');

Then I always get this error message regardless of what type of compute I am using:
[INTERNAL_ERROR] The Spark SQL phase analysis failed with an internal error. You hit a bug in Spark or the Spark plugins you use. Please, report this bug to the corresponding communities or vendors, and provide the full stack trace. SQLSTATE: XX000

[Walter_C]

The error you are encountering when querying the event log in Delta Live Tables (DLT) with Direct Publish mode is a known limitation. Specifically, the event log Table-Valued Function (TVF) does not work in Direct Publishing Mode. This is documented in the private preview guide for DLT Direct Publishing Mode.

As a workaround, you can directly query the delta table that stores the pipeline’s event log. The event log for a Direct Publishing pipeline is stored in a specific format:

1. Default Catalog: The catalog specified in the pipeline settings.
2. Default Schema: The schema specified in the pipeline settings.
3. Underscored Pipeline ID: The pipeline’s UUID with dashes replaced by underscores.

For example, if your pipeline ID is 5e9e3c89-2a4b-4dcd-baaf-48f58c2c54ae, and the default catalog and schema are catalog and test respectively, the event log table would be named catalog.test.__5e9e3c89_2a4b_4dcd_baaf_48f58c2c54ae_event_log.

[susanne]

@Walter_Cthank you so much, that worked perfectly 🙂

[susanne]

Hello everyone,

I am having problems working with this approach and
I am not sure if it is related to the recent changes in DLT Direct Publish (moving it to Public Preview) or if it is related to the changes I have made.

I recently bound my existing DLT to an Asset Bundle deployment and for some reason I cannot find the event_log table anymore when I run the following SQL:

SELECT *

FROM ca_catalog.gold.__<pipeline_id>_event_log

My DLT Setting are shown below as the yml configuration:
resources:
pipelines:
pipeline_dev_<user>_ca_etl_pipeline:
name: "[dev <user>] ca_etl_pipeline"
libraries:
- notebook:
path: /Workspace/Shared/pipeline/src/jobs/dlt_pipeline/generic_dlt_pipeline
schema: gold
development: true
photon: true
channel: PREVIEW
catalog: ca_catalog
serverless: true
deployment:
kind: BUNDLE
metadata_file_path: /Workspace/Users/<user>/.bundle/ca_etl_pipeline_databricks_bundle/dev/state/metadata.json

Could it have something to do with my asset bundle deployment or is it a known problem in Databricks?

[susanne]

If any one is interested how to access the event_log for DLTs that are writing to different schemas:
The approach that was described above was not working anymore - at least for us.
But we discovered this post:
https://stackoverflow.com/questions/79432901/access-dlt-event-logs
And the provided answer did the trick:
"Add to the Pipeline Settings (JSON not UI):

"event_log": {
    "name": "<table_name>",
    "schema": "<schema_name>",
    "catalog": "<catalog_name>",
    "visible": true
},

Then query the resulting table."