Custom DELETE syntax to fix a stuck operation on a large table in Databricks

[raffaelec]

Hello,

I’ve configured a Log Stream task to read from a Microsoft Azure SQL database, along with a replication task that reads from this Log Stream and replicates data to a Databricks Lakehouse (Delta) endpoint.

Among the replicated tables, there is one with about 380 million records and 18 columns, which accumulates between 200,000 and 400,000 changes during the full load, which typically takes several hours.

Over the past few weeks, once the full load of this table completes, the task has been unable to apply the changes to Databricks. Specifically, the task tries to apply the DELETE operations on the table and remains in running state for tens of hours without completing the operation successfully.

The DELETE operation has this form:

DELETE FROM my_database.target_table T
WHERE EXISTS (
    SELECT C.seg1
    FROM my_database.attrep_changes_table C
    WHERE (
        T.primary_key = CAST(C.seg1 AS INT)
        OR (T.primary_key IS NULL AND C.seg1 IS NULL)
    )
    AND C.seq >= 1
    AND C.seq <= 200100
);

We have tried several alternatives to solve the problem, including:

• reducing the change batch size;
• increasing the query timeout of the operations;
• optimizing the large table to make delete operations more efficient;
• replicating only the last 6 months of data for the large table.

Unfortunately, none of these attempts was successful.

However, we verified that the following DELETE operation runs in 30 seconds on Databricks:

DELETE FROM my_database.target_table
WHERE primary_key IN (
    SELECT CAST(seg1 AS INT)
    FROM my_database.attrep_changes_table
    WHERE seq BETWEEN 1 AND 200100
      AND seg1 IS NOT NULL
);

Is it possible to configure the replication task to run DELETE operations using the syntax above?

I understand that Qlik Replicate provides internal parameters to customize the SQL syntax it generates, such as $info.query_syntax.create_table for table creation. Is there an equivalent parameter for DELETE operations? If so, how could we set it so that the task uses the syntax shown above? If no such parameter exists, is there an alternative way to achieve the same result?

Thank you in advance for your support.

Best regards

[john_wang · Accepted solution]

Hello Raffaele, @raffaelec 

Thanks for posting this here.

I believe you have also opened support ticket 00472073 for the same issue. We are currently working with the CF team to investigate the specific error and will keep you posted on any updates.

Regarding improving the performance of the DELETE operation, we have two options:

1.  Use MERGE instead of DELETE

    The Apply changes using SQL MERGE option can be more efficient than separate INSERT/UPDATE/DELETE operations.

    You can enable it by setting:

    Task Settings → Change Processing Tuning → Miscellaneous Tuning → Apply changes using SQL MERGE = TRUE

2. Continue using DELETE with a customized syntax

    If you prefer to keep using the DELETE operation but want to use a specific SQL syntax as your sample, you can configure the following internal parameter:

1. Open the Databricks target endpoint.

2. Go to the Advanced tab.

3. Open Internal Parameters.

4. Add a new parameter named: $info.query_syntax.bulk_delete_syntax

5. Press Enter and set the parameter value to: TUPLES_IN_SQL_IN_CLAUSE

6. Stop and resume the task for the change to take effect.

Hope this helps.

John.

[raffaelec]

Hello @john_wang,

The replication task was already configured to use SQL MERGE.

I have now added the internal parameter you indicated, with the suggested value. I can confirm that this change allowed the replication task to resume applying the changes to the tables on Databricks.

Thank you very much!

Best regards

[john_wang]

Hello Raffaele, @raffaelec 

Thank you for the follow-up.

Please note that SQL MERGE conflicts with the TUPLES_IN_SQL_IN_CLAUSE DELETE operation. If you want to use TUPLES_IN_SQL_IN_CLAUSE DELETE operations, you need to turn SQL MERGE off.

If you’d like to confirm the SQL statements generated with the current settings, you can set TARGET_APPLY to Trace and then review the task log to see the actual SQL statements used for applying the changes.

We’re very glad to have such an experienced expert like you supporting us. Thank you again for your outstanding support!

My Best,

John.