topic Re: Error when query event log: Cannot read more than one event logs in the same query in Data Engineering

Error when query event log: Cannot read more than one event logs in the same query

guangyi — Mon, 09 Sep 2024 03:01:24 GMT

I try to follow the instructions of Monitor Delta Live Tables pipelines to query dlt.expection log.

Here is the simple code version I copied from the Querying the event log section:

CREATE TEMPORARY LIVE VIEW event_log_raw AS (SELECT * FROM event_log(TABLE(`catalog`.`schema`.`table`))); CREATE OR REFRESH MATERIALIZED VIEW event_log_raw_1 AS (SELECT * FROM event_log_raw LIMIT 1);

However, after I put the code into a DLT pipeline and running, it tell me something goes wrong:

org.apache.spark.sql.catalyst.ExtendedAnalysisException: Unable to process statement for View 'event_log_raw'.
java.lang.UnsupportedOperationException: Cannot read more than one event logs in the same query
org.apache.spark.SparkException: A pipeline with a different id is already registered in this Credential Scope. The pipeline in a CredentialScope cannot be updated once it is registered.

I cannot find the related search result for this error message anywhere, what’s wrong with my code?

Re: Error when query event log: Cannot read more than one event logs in the same query

szymon_dybczak — Mon, 09 Sep 2024 06:18:53 GMT

Hi @guangyi ,

Yeah, bit weird error. In your case, the error message you’re seeing suggests that there is an issue with querying the event logs because Delta Live Tables (DLT) only allows one instance of the event_log to be read in a single query.
Could you try to remove below part from your pipeline? Just for a test:

CREATE OR REFRESH MATERIALIZED VIEW event_log_raw_1 AS (SELECT * FROM event_log_raw LIMIT 1);

Also, make sure your environment aligns with following requirements of event_log TVF:

- To call the event_log, you must use a shared cluster or a SQL warehouse.

- The event_log TVF can be called only by the pipeline owner and view created over the event_log.

Re: Error when query event log: Cannot read more than one event logs in the same query

guangyi — Tue, 10 Sep 2024 01:55:33 GMT

H @szymon_dybczak , thank you for the advice. After investigating the problem has been solved.

The error message mentioned in the title is not the key, the below error message “A pipeline with a different id is already registered in this Credential Scope. The pipeline in a CredentialScope cannot be updated once it is registered” leads me to a similar question with the answer: https://community.databricks.com/t5/data-engineering/describe-table-extended-on-materialized-views-uc-dlt-and-cluster/m-p/63971/highlight/true#M32421

The reason why this issue happened is because I isolated this SQL task(file) into an independent pipeline. So the way to fix it is just merge the event query task with the pipeline which generates the target table together.

bronze_pipeline: libraries: - file: path: ./bronze/pipeline.py - file: path: ./bronze/data_quality_check.sql

Re: Error when query event log: Cannot read more than one event logs in the same query

szymon_dybczak — Tue, 10 Sep 2024 07:57:39 GMT

Hi @guangyi ,

Thanks for sharing solution with community!