Databricks Performance issue

Vineet S 750

Hey, due to multiple merged condition in same query, it is taking lots of time to run in databricks tried partition but did not work . what can be best approach

Vineet S 750

i got below mentioned but how to add these partition https://kb.databricks.com/delta/delta-merge-into

%scalaspark.sql(s"""     |MERGE INTO $targetTableName     |USING $updatesTableName     |ON $targetTableName.par IN (1,0) AND $targetTableName.id = $updatesTableName.id     |WHEN MATCHED THEN     |  UPDATE SET $targetTableName.ts = $updatesTableName.ts     |WHEN NOT MATCHED THEN     |  INSERT (id, par, ts) VALUES ($updatesTableName.id, $updatesTableName.par, $updatesTableName.ts) """.stripMargi

Dillon Silzer 57,231 Reputation points

2024-02-28T00:57:26.0666667+00:00

What is your compute/node resource?
Vineet S 750 Reputation points

2024-02-28T01:21:01.47+00:00

source csv files target hive metastore
Vineet S 750 Reputation points

2024-02-28T03:15:46.9333333+00:00

Any recommendations for above
Dillon Silzer 57,231 Reputation points

2024-02-28T17:30:25.09+00:00

What is your instance you are using?

https://azure.microsoft.com/en-ca/pricing/details/databricks/
Vineet S 750 Reputation points

2024-02-28T17:37:05.0666667+00:00

Question is being ask about portion used in below query and how we can use the same in other queries $targetTableName.par IN (1,0) %scalaspark.sql(s""" |MERGE INTO $targetTableName |USING $updatesTableName |ON $targetTableName.par IN (1,0) AND $targetTableName.id = $updatesTableName.id |WHEN MATCHED THEN | UPDATE SET $targetTableName.ts = $updatesTableName.ts |WHEN NOT MATCHED THEN | INSERT (id, par, ts) VALUES ($updatesTableName.id, $updatesTableName.par, $updatesTableName.ts) """.stripMargi

1 answer

PRADEEPCHEEKATLA-MSFT 88,561 Reputation points Microsoft Employee

2024-02-28T05:57:55.8566667+00:00
@Vineet S - Thanks for the question and using MS Q&A paltform.

It seems that you are experiencing performance issues while running a merge query in Azure Databricks. You have tried partitioning but it did not work.

To add partitions to your Delta table, you can use the PARTITIONED BY clause while creating the table.

Here is an example:

CREATE TABLE events ( date DATE, eventId STRING, eventType STRING ) USING delta PARTITIONED BY (date) LOCATION '/mnt/events/';

In this example, the events table is partitioned by the date column. You can replace the column name with the appropriate column from your table.

Regarding your merge query, the article you shared provides an example of a merge query that uses multiple merge conditions. However, it is difficult to provide specific guidance without more information about your query and data.

One thing you can try is to optimize your merge query by using the OPTIMIZE command. This command reorganizes the Delta table files to improve query performance.

Here is an example:

OPTIMIZE events;

In this example, events is the name of the Delta table. You can replace it with the name of your table.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
Vineet S 750 Reputation points

2024-02-28T06:07:50.2066667+00:00

Can you pls explain this Query %scalaspark.sql(s""" |MERGE INTO $targetTableName |USING $updatesTableName |ON $targetTableName.par IN (1,0) AND $targetTableName.id = $updatesTableName.id |WHEN MATCHED THEN | UPDATE SET $targetTableName.ts = $updatesTableName.ts |WHEN NOT MATCHED THEN | INSERT (id, par, ts) VALUES ($updatesTableName.id, $updatesTableName.par, $updatesTableName.ts) """.stripMargi

Vineet S 750 Reputation points

2024-02-28T10:31:11.34+00:00

can someone explain above query

Vineet S 750 Reputation points

2024-02-28T17:20:19.4566667+00:00

can someone explain above query

Vineet S 750 Reputation points

2024-02-28T23:54:30.3566667+00:00

i got below mentioned query but how to add these partition ???https://kb.databricks.com/delta/delta-merge-into

PRADEEPCHEEKATLA-MSFT 88,561 Reputation points Microsoft Employee

2024-02-29T06:05:47.74+00:00

@Vineet S - This is a MERGE INTO query in Scala that is used to update or insert data into a target table based on the data in a source table. Here is a breakdown of the query:

MERGE INTO is the statement used to merge data from a source table into a target table.

$targetTableName is the name of the target table where the data will be merged.

$updatesTableName is the name of the source table that contains the data to be merged.

ON $targetTableName.id = $updatesTableName.id is the condition that specifies how to match the data in the source and target tables. In this case, the data is matched based on the id column in both tables.

WHEN MATCHED THEN UPDATE SET $targetTableName.ts = $updatesTableName.ts is the statement that specifies what to do when a match is found between the source and target tables. In this case, the ts column in the target table is updated with the value from the ts column in the source table.

WHEN NOT MATCHED THEN INSERT (id, par, ts) VALUES ($updatesTableName.id, $updatesTableName.par, $updatesTableName.ts) is the statement that specifies what to do when a match is not found between the source and target tables. In this case, a new row is inserted into the target table with the values from the id, par, and ts columns in the source table.

Overall, this query is used to synchronize data between two tables by updating existing rows and inserting new rows as needed.

Vineet S 750 Reputation points

2024-02-29T06:19:12.3+00:00

Thanks, the question is how we can optimize the merge multiple condition which is taking time

PRADEEPCHEEKATLA-MSFT 88,561 Reputation points Microsoft Employee

2024-02-29T07:58:32.8633333+00:00

@Vineet S - To optimize a MERGE statement with multiple conditions in Azure Databricks, you can follow these steps:

Use appropriate indexing: Ensure that the columns used in the join condition and the WHERE clause are indexed. This can significantly improve the performance of the MERGE statement.

Use a staging table: Instead of merging data directly into the target table, you can use a staging table to merge the data. This can help to reduce contention on the target table and improve performance.

Use a batch processing approach: If you are merging a large amount of data, you can use a batch processing approach to merge the data in smaller chunks. This can help to reduce the amount of memory required and improve performance.

Optimize the query plan: You can use the EXPLAIN PLAN statement to analyze the query plan and identify any performance bottlenecks. You can then optimize the query plan by adding hints or rewriting the query.

Use parallel processing: If you have a large amount of data to merge, you can use parallel processing to split the data into smaller chunks and merge them in parallel. This can help to improve performance by utilizing multiple CPU cores.

Use appropriate hardware: Ensure that you are using appropriate hardware for your workload. This can include using faster disks, more memory, or more CPU cores.

In addition to these steps, you can also consider using Delta Lake, which is a powerful data lake engine that provides ACID transactions, scalable metadata handling, and data versioning. Delta Lake can help to improve the performance of MERGE statements by providing faster and more efficient data processing.

By implementing these optimization techniques and using Delta Lake, you can significantly improve the performance of a MERGE statement with multiple conditions in Azure Databricks.

Hope this helps. Do let us know if you any further queries.

Vineet S 750 Reputation points

2024-03-01T01:51:45.5066667+00:00

please give an example of below queries

Use a batch processing approach: If you are merging a large amount of data, you can use a batch processing approach to merge the data in smaller chunks. This can help to reduce the amount of memory required and improve performance.

Use parallel processing: If you have a large amount of data to merge, you can use parallel processing to split the data into smaller chunks and merge them in parallel. This can help to improve performance by utilizing multiple CPU cores.

PRADEEPCHEEKATLA-MSFT 88,561 Reputation points Microsoft Employee

2024-03-11T04:14:25.6766667+00:00

@Vineet S - The scope of your question is different compared to the original scope of the question asked.

I would recommend creating a new thread on the same forum with as much details about your issue as possible. That would make sure that your issue has better visibility in the community.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Databricks Performance issue

1 answer

Your answer