Hello Team ,we Found a Bug in our production we have a CDC platform where we are performing UPSERT into refine table using rank function but let’s say if records come with same time insert and then update then my rank function will only take updated item and perform upsert but this records doesn’t match to refine table bcz this records hasn’t be inserted so can anyone guide us how to create a plan to first insert the record and then update it

spark_session_micro_batch.sql(f"""
				SELECT * 
				FROM (
					select *
					,row_number() over(partition by {<http://self.pk|self.pk>} order by  __source_ts_ms desc ) as dedupe
					from {self.micro_batch_view}
					)
				WHERE 
					dedupe =1 
			""").drop("dedupe").createOrReplaceTempView(self.micro_batch_dedup_view)
				
and this is our merge query

			SqlQuery = """
				MERGE INTO {db_name}.{refine_table_name} v
				USING dedup_temp_view u
				ON v.{pk}=u.{pk}
				WHEN MATCHED AND u.__op = "u"
				THEN UPDATE SET *
				WHEN MATCHED AND u.__op = "d"
				THEN DELETE
				WHEN NOT MATCHED AND (u.__op = "c" or u.__op = "r")
				THEN INSERT *
			""".format(refine_table_name=refine_table_name,db_name=db_name,pk=pk)

is there somewhere where we can see which reader/writer versions are supported in each delta lake release? i was hoping to find that here i saw the following log and wanted to verify that changing the version wouldn't break for existing spark applications before updating the min version requirements

Please upgrade your Delta table to reader version 2 and writer version 5
 and change the column mapping mode to 'name' mapping. You can use the following command:

 ALTER TABLE <table_name> SET TBLPROPERTIES (
   'delta.columnMapping.mode' = 'name',
   'delta.minReaderVersion' = '2',
   'delta.minWriterVersion' = '5')

we recently wrote about the MERGE command at the delta.io blog (https://delta.io/blog/2023-02-14-delta-lake-merge/) - it includes a section on handling `null`s in your changeset by leveraging

case/when

expressions - there's been questions here specifically on handling this so i'm sharing here too... it's hard to fit everything in one post so we do plan to build on this - please leave a comment if there's something you'd like to see!

Hi alI, I have tried to split the checkpoint file on several parts without sucess .I attempted to adjust configuration options such as spark.databricks.delta.checkpoint.partSize and spark.databricks.delta.snapshotPartitions, however I have been unsuccessful in achieving parallelization.Would you be able to offer me some guidance on how to set up the value of these options? I found the documentation to be somewhat limited. I use pyspark on Azure Snypase

Using the Delta standalone library is there a way to get the transactions of a single and range of versions? I have a parquet file that I would like to find the Delta version that the file was added without scanning all the versions.

Hii Team , can we set max file size during performing z-order optimization bcz i am having issue to getting 30mb file size so it is creating issue during update the data, i have set below property but not working this property . any idea ?

targetfilesize_refine='5000000' #byte

  
          alter table delta.`{table_loc}` 
            SET TBLPROPERTIES (
                'delta.targetFileSize'='{self.targetfilesize_refine}',
                'delta.tuneFileSizesForRewrites'='{self.tunefilesizesforrewrites_refine}'
                )
             """)

Hello Team, Are

readerVersion

and

writeVersion

new protocol which is replacing

minReaderVersion

and

minWriterVersion

respectively? https://github.com/delta-io/delta/pull/1450/files#diff-62d56fa1c25dda8d49ccdbf9ea31df4576f84625706d54613dff0acc8f974d97R509-R510

Hi, Does anybody face the such issue?

AmazonS3Exception: Slow Down (Service: Amazon S3; Status Code: 503; Error Code: 503 Slow Down

during the delta lake merge? We have a delta table which is partitioned by columns.

Hi All, if vacuum and merge are ran in parallel will vacuum delete files that are being committed ?

Hii , i can see table property targetFileSize is set to 10 mb but getting data size 40 mb and so on. please suggest me where i am doing wrong

{"commitInfo":{"timestamp":1676611727893,"operation":"SET TBLPROPERTIES","operationParameters":{"properties":"{\"delta.compatibility.symlinkFormatManifest.enabled\":\"true\",\"delta.targetFileSize\":\"10mb\",\"delta.tuneFileSizesForRewrites\":\"10mb\"}"}

Hi, Is there someone working for https://github.com/delta-io/connectors/issues/484

Hi all, I have a question regarding running bin-packing operation, documented here. Is this operation available using AWS Glue 4? Has anyone been able to run it using Glue 4? Cheers,

Hi. I have a delta table which automatically generates symlink format manifests via

delta.compatibility.symlinkFormatManifest.enabled=true.

Occasionally I am finding that I receive errors if the write that triggers the manifest generation results in a checkpoint file being created and pre-checkpoint transaction log files being deleted. If I run the job again it will work just fine. Is the best way to avoid this to disable the automatic generation and instead do it manually after the data write with

DeltaTable.forPath(...).generate()

? Or am I overlooking something?

org.apache.spark.sql.delta.DeltaRuntimeException: Committing to the Delta table version 210 succeeded but error while executing post-commit hook Generate Symlink Format Manifest: Generate Symlink Format Manifest

Encountered error while reading file <s3://my-bucket/my-table/_delta_log/00000000000000000201.json>

Caused by: java.io.FileNotFoundException: File not present on S3

Hi All, I am new to delta tables and Databricks and I would like to ask if some can help with some performance tuning tips. Currently some of our tables present very complex nested data. And the queries run really slow. Any suggestion about working with very huge nested columns (struct and array) is welcome. The data is originally in one big string but I convert them to several Json columns using from_json. Thanks in advance

Hi team, we are in the process to upgrade delta 2.2.0, With our current environment in delta 1.0.0, here is our scenario 1). set a default 'deletedFileRetentionDuration' on the delta table of 7 days 2) append a delta file and lets say it's version 5, 3) file deleted on day three from the time it was appended. 4) run vacuum on day ten. Observation: On day 11, we observe that the spark action 'count; on version 5 of the delta table used to fail with delta 1.0.0, But with version 2.2.0 it has been succeeding. In delta 2.2.0, only when we use other spark actions such as "collect" or "show" we are seeing the file not found spark exception. Anyone else seen this behavior as a side-effect in upgrading to delta 2.2.0?

Hi all, how's everyone doing? Topic: Databricks Metastore Cluster Details: Databricks runtime 10.4 ML LTS Apache Spark 3.2.1, Scala 2.12 Problem description: We have an issue with our metastore slowing down read and write to our delta tables. We initially attempted to communicate with databricks support to understand the Metastore is Down Issue. We were told it's due to BoneCP trying to established failed connections from the connection pool etc. Attempt 1: The resolution was to try using HikariCP instead of BoneCP, after adding HikariCP datanucleas jar files to /databricks/hive.

spark.hadoop.datanucleus.connectionPoolingType hikari

We got the same error as below. Attempt 2: Docs : • https://learn.microsoft.com/en-us/azure/databricks/release-notes/runtime/10.4ml • https://learn.microsoft.com/en-us/azure/databricks/release-notes/runtime/10.4#hikaricp-is-now-the-default-hive-metastore-connection-pool installed packages: x So 10.4 ML is different to 10.4 in that 10.4 ML is not using hikari (and has the below libraries missing from the databricks Libraries UI) • We installed the following (because this is the standard from 10.4 non-ml version):

org.datanucleus:datanucleus-api-jdo:4.2.4
org.datanucleus:datanucleus-core:4.1.17
org.datanucleus:datanucleus-rdbms:4.1.19
org.datanucleus:javax.jdo:3.2.0-m3

configuration that we've tried:

spark.hadoop.datanucleus.connectionPoolingType hikari

spark.hadoop.datanucleus.connectionPoolingType HikariCP

spark.databricks.hive.metastore.client.pool.type hikari

spark.databricks.hive.metastore.client.pool.type HikariCP

All jar files were added and we are getting the following error after restarting. ``org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:775)`

... 128 more

`Caused by: java.lang.Throwable: Attempt to invoke the "hikari" plugin to create a ConnectionPool gave an error : The connection pool plugin of type "hikari" was not found in the CLASSPATH!`` Any one with knowledge on how to resolve this issue, we would appreciate your help. Thanks

Can transaction log files be safely removed from a delta table? https://github.com/delta-io/delta/blob/master/PROTOCOL.md#last-checkpoint-file

The Delta transaction log will often contain many (e.g. 10,000+) files.

@Dominique Brezinski I had a question, can we use delta lake on top of MinIO data lake? If yes is there a documentation that i can follow? Or should i try to connect delta lake to MinIO in a aws S3 fashion

Hi guys , is it possible to upsert when a table is not delta, for example, saved in parquet format ?

Hii Team , this is question not related to DeltaLake it’s related to CDC(Debezium) i am fetching the data from sql server via debezium connector but i have a scnerio where __source_ts_ms is max for insert but should be for update .bcz i have applied rank function on refine table partition by id order by __source_ts_ms desc can someone confirm what should the best way to find last insrted record for same id

+----+--------+-----------------------+------+----+------------------------+
|rank|amount  |lastUpdateTime         |status|__op|__source_ts_ms          |
+----+--------+-----------------------+------+----+------------------------+
|1   |null    |2023-02-08 12:38:55.407|0     |r   |08-Feb-2023 10:53:52.000|
|2   |100.0000|2023-02-08 15:02:06.7  |2     |u   |08-Feb-2023 09:32:06.000|
|3   |100.0000|2023-02-08 15:01:36.95 |0     |u   |08-Feb-2023 09:31:36.000|
|4   |100.0000|2023-02-08 15:01:34.147|0     |u   |08-Feb-2023 09:31:34.000|
+----+--------+-----------------------+------+----+------------------------+

Anyone created an integration to deltasharing through e.g. custom node.js-application or such? Looking for a way to seamlessly share through deltasharing to a Data Factory pipeline. Any other suggestions is well appreciated. Thanks.

Guys, when I want union two dfs, and sometimes their schema get changed what is the best practice to do it ?

Hi everyone, I am seeking help finding the solution for a specific requirement. There is a requirement to process parquet files and save them to the delta table. These files always belong to a particular partition, so we processed them concurrently. But writing to the delta table becomes slow with many concurrent writes. So we want to batch the files and write them together. I have tried a union operation on all the data frames before writing to the delta table. But this seems to be not an optimal way as this time is more than the time taken to write individually to a plain parquet table. Writing 25 files into the delta table using the union approach took around 125s, whereas parallel writes to the parquet table took around 97s. The same test for 50 files, delta took 234s whereas plain parquet writes took 171s. Is there a better way to write multiple data frames together in one transaction/commit?

Is there a function in delta-lake OSS corresponding to

table_changes

(for reading from the change data feed) ? https://docs.databricks.com/sql/language-manual/functions/table_changes.html

Hello, I have the following Pyspark streaming code in my forEachBatch function when writing micro-dataframe to delta lake:

# create or delete operation

delta_lake_table.alias("main_table").merge(

latest_changes_delete_create_df.alias(

"update_table"), merge_condition

).whenMatchedDelete(condition="update_table.op = 'd'").whenNotMatchedInsertAll(

condition="update_table.op = 'c' OR update_table.op = 'r'"

).execute()

# update operation

delta_lake_table.alias("main_table").merge(

latest_changes_update_df.alias("update_table"), merge_condition

).whenMatchedUpdateAll(

condition="update_table.op = 'u' OR update_table.op = 'r'"

).execute()

Now, the thing is the data schema can evolve, therefore I am using whenMatchedUpdateAll and whenNotMatchedInsertAll while also setting the following config in spark configurations: 'spark.databricks.delta.schema.autoMerge.enabled = true'. However, when a new column comes in I am facing the following error:

pyspark.sql.utils.AnalysisException: The schema of your Delta table has changed in an incompatible way since your DataFrame

or DeltaTable object was created. Please redefine your DataFrame or DeltaTable object.

Changes:

Latest schema has additional field(s): op

Can someone please help and tell me what I am doing wrong. I am using delta lake 2.2.0 (Open-Source) on top of Minio. And have the followed the following documentation: https://docs.delta.io/latest/delta-update.html#automatic-schema-evolution . Thank you.

Hello, I have a delta log folder ( _delta_log) having millions of files. It is retaining all old log files. Is there a way to remove/archive/ consolidate the _delta_log log files?

Can someone please help me as well ^^. Will be grateful.

I am trying test converting an Iceberg table to Delta as documented (https://docs.databricks.com/ingestion/convert-to-delta.html) but I am getting a NullPointerException?

Hello Team, Why distincts are not part stats in delta table ? Is there any way we can populate distinct stats in open source delta versions?

👋 Hello, team! I have a CRON spark job that run

compaction

and

vacuum

on my delta tables. I'm using the scala API to perform those operations. I run this for each table in parallel. I have often an OOM and I'm suspecting that the jobs run on the driver. Does anyone have an idea if this is true and if it's the case it's possible to run them on the executors. Thank you!