Hi everyone, I am writing some code that needs to run a `DELETE`statement on a delta table, and the delete condition relies on a

row_number()

window function. When using the

row_number() > 1

condition, I get the error that windows and aggregations are not supported by DeltaTable’s delete. I tried rewriting that as a subquery, but I get the error that subqueries are not supported by delete. Is collecting the unwanted values and use

Column.isin(unwantedValues)

my only option? Are there any alternatives I might consider?

Hi Everyone, I’m trying to look around to see if delta sharing will support views in future. I know it doesn’t currently, but is there a plan to support view sharing?

Changing physical LOCATION of a Delta Table (without breaking metastore ref) Hi everyone, Is there a simple way to move the physical location of a Delta Table that is registered in Hive metastore? I know that the Delta Table itself is "portable": I can move the Delta Lake table folder using operating system capabilities. However, this breaks the Hive metastore reference. There is a

ALTER TABLE x SET LOCATION

statement (https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-ddl-alter-table.html#set-location-and-set-file-format). But if I run this after I changed the physical location, it fails since at this point the table in Hive metastore is already corrupt since there is no Delta Table to be found any more are the stored location. I can work around this issue by creating a dummy delta table (without registering it in Hive metastore) at the original location after I moved the Delta Lake table folder to its destination, run the the

ALTER TABLE x SET LOCATION

statement and then simply delete the dummy delta table at the original location. Another way for moving unmanaged Tables would be to move Delta Lake table folder using OS, run

DROP TABLE x

and then

CREATE TABLE x USING DELTA LOCATION

(https://docs.delta.io/latest/delta-batch.html#control-data-location). Downside of this approach is that we lose table properties and metadata. Both approaches feel very clumsy to my. In my point of view, this should be a build-in feature. Am I missing something?

Quick question, after apply the ALTER TABLE SET LOCATION mentioned above , should I run something else before query the table? after the alter I'm unable to fetch data from the table

Hi All, i have a readStream job to delta, but the job failed due to some anomaly in one partition, the partition contains many files (around 5000 files). I run the compaction for that partition, when I check with

describe detail

the total of files has been reduced. but, when I run the readStream job, the job still read the old files (scan more than 5000 files), then I try to run the

vacuum(0)

, but the readStream still read the file that has been deleted by vacuum command. But, when I try to run the

select

command, there is no error. Is there any way to invalidate the delta table? why the readStream still read the file that has been deleted? note: I use delta version 1.0.0 Thanks

Question about Delta table versioning and time travel: If a Delta table has two versions v1 and v2, does v2 contain everything that v1 had plus the difference between d1 and d2 OR does v2 only contain the difference between v1 and v2 ?

Question about auto loader, is it possible to keep track of file upload time with the auto loader? I am able to see the file name and processing time but not file upload time. The current use case requires to know the upload time to process the data in the right order.

Folks who are using delta table and connector with Flink, how do you checkpoint on delta connector for resuming processing from that point later on?

Hi everyone, i'm trying to get the rangeId so that i can reproduce this test on the image with a small coordinates set, is there a way to have the range Ids back on the databricks runtime (source: delta lake, test of multidimClusteringSuite.scala)

Hello everyone. I'm trying to get an estimate of space that Vacuum can free up, Dry run on Databricks only returns the first 1k files and only name doesn't have the file size. I'm trying to understand how Vacuum lists these files, using the last checkpoint file + last commit Jsons is not working so i'm trying to understand the source code of Vacuum and LogStore, it's a bit difficult for me cause i don't know Scala. Does anyone know how Vacuum lists the files to be remove? Reading Delta log or read all files in table storage and subtracting files from current version? @Denny Lee can you give a tip?

Hi team! Can someone please help me understand how to delete a column from delta lake tables using the Delta Standalone library (without spark) in Java? I am getting below error on trying to update the metadata of a table with old schema having columns (id, name, new_col) to new schema (id, name)

Exception in thread "main" java.lang.IllegalStateException: Detected incompatible schema change:
old schema: root
-- id: integer (nullable = true) (metadata ={})
-- name: string (nullable = true) (metadata ={})
-- new_col: string (nullable = true) (metadata ={})


new schema: root
-- id: integer (nullable = true) (metadata ={})
-- name: string (nullable = true) (metadata ={})

Thank you in advance!

Hey team, Where does the metadata of a delta table created using

delta-standalone

library gets stored in ADLS? Is it possible to query those tables using apache spark pool in SQL?

Good morning team! I have a question related to the z-order command. I execute Optimize once a day should I execute z-order once per day too? or z-order is only executed one time?

Hey team, I am executing

dt.optimize().where("partition == 'asd'").executeCompaction()

on a partition of about 200GB, but my jobs often run out of memory. I've allocated

"spark.executor.memory": "45G"

and

"spark.executor.memoryOverhead": "5G"

which I feel should be enough? Any ideas on how to optimize this operation for memory efficiency? I am not in a position to allocate more memory. I'm on Delta OSS 2.0.0 and Spark 3.2.2.

Hello! Where do I find the correct versions of various modules to pass to

--packages

when running pyspark locally? E.g.

delta-core_2.12:2.2.0

works fine:

pyspark --packages io.delta:delta-core_2.12:2.2.0,org.apache.hadoop:hadoop-aws:3.3.4 \
        --conf "spark.sql.extensions=io.delta.sql.DeltaSparkSessionExtension" \
        --conf "spark.sql.catalog.spark_catalog=org.apache.spark.sql.delta.catalog.DeltaCatalog"

But

delta-core_2.12:2.1.0

throws a

NullPointerException

at start, which I assume is due to some version incompatibility:

pyspark --packages io.delta:delta-core_2.12:2.1.0,org.apache.hadoop:hadoop-aws:3.3.4 \
        --conf "spark.sql.extensions=io.delta.sql.DeltaSparkSessionExtension" \
        --conf "spark.sql.catalog.spark_catalog=org.apache.spark.sql.delta.catalog.DeltaCatalog"

Question is where do I find the compatible versions of

delta-core

,

io.delta

and

hadoop

? My source was: https://docs.databricks.com/release-notes/runtime/12.1.html ( I use Databricks Runtime), but I do not see delta-core there, so when I change from 2.2.0 to 2.1.0 I don't know what to change 2.12 to?

Does Delta connector provide a checkpointing mechanism? I’ve a Flink job that reads from a Delta connector and if the Flink job has to be restarted, I want to specify the time stamp or version that I should use to resume processing data from the Delta connector. Can anyone provide any insights into this?

Hello everyone, im experimenting with deltalake and found a little problem when querying the delta table with apache drill. Drill does not interpret INT96 data as a timestamp by default, so i had to change this drill setting to read deltalake timestamps properly (not a big deal). I´ve tried to set

spark.sql.parquet.int96AsTimestamp

to false, so i wouldnt have to set this drill setting, but it seems that the delta-table writes the data always as INT96. Is it possible to configure this via a special delta config or should this setting work and i did something wrong while setting up my spark session?

Hi Folks having trouble creating delta tables on a local minio bucket running on 9001

delta_table_loc = "<s3a://test-bucket/test-table>
storage_options = {"AWS_ACCESS_KEY_ID": "<accesskey>", "AWS_SECRET_ACCESS_KEY": "<secretkey>",
                   "AWS_ENDPOINT_URL": "<https://0.0.0.0:9001>"}
write_deltalake(table_or_uri=delta_table_loc, data=df, mode='append', schema=SA_data_schema, partition_by=["asset_id"],
                storage_options=storage_options)

when not using AWS_DEFAULT_REGION throws region missing but when used it throws “Failed to load checkpoint: Failed to read checkpoint content: Generic S3 error: Error performing get request”

hi guy. I have a table with 60 column, i knew that delta gather statistic on first 32 column(default). So i use this code:

spark.sql("ALTER TABLE delta.`/user/fplay/temp/testraw/raw_logs.delta` CHANGE COLUMN user_id user_id STRING FIRST")

to change the column index of the column i need to perform z-order to 0(the first column). After all the newest delta log version still have not any statistic about the column i need. Any help please!!!

Hi everybody! I'm looking for some help in a specific scenario that I have to delete physically parquet files and replace the previous ones in a Delta Table. But the issue is related to the Delta Log, Once I remove just one parquet I get an inconsistency error between Delta Log and the actually Parquet files I have in certain partition. There is any way to rebuild/refresh the Delta metastore/log with new files externally added/removed like we do in Hive with just "REFRESH TABLE" command? I already tried this command with Delta, but with no success 😕 Thank you in advance!

Hi everybody, I have delta tables in my azure data lake, created by databricks spark jobs. Now I want to load the data into power bi, what is the reccomended way, do I need to start my cluster/endpoint and allow PowerBi to connect that, or can power bi read the delta format from the lake. many thanks.

Hi All,

Is there any limitation OSS Delta for usage on GCS?

Hi, is a rename/drop of a field in a nested column supported in delta io?

And also, is a renaming of column supported also in symlink tables (access delta lake table from Athena)?

Hey when running a daily Optimize job on a Delta table without adding a where clause (meaning on the entire table) i would think it will optimize only the non optimized files, but it seems like it go over all the partitions again even if they weren’t touched at all from the last run is that correct?

Hi all I'm getting a job failure due to

Total size of serialized results of 462 tasks (4.0 GiB) is bigger than spark.driver.maxResultSize 4.0 GiB

. I have checked the usual suspects for this issue such as collect statements, really high numbers of partitions and large broadcast joins, none of which occur in the job. Digging further I believe this could be caused by the Delta writer as when looking at the stage that returns this it shows this line

com.databricks.sql.transaction.tahoe.commands.WriteIntoDeltaCommand.run(WriteIntoDeltaCommand.scala:70)

. I do not have access to the source code but wondered if anybody had any insight as to why this line could be returning fairly large amounts of data to the driver (I assume metrics and information on file locations for updating the delta log), but it seems that each task is returning a lot of information and for the total number of tasks for this stage (2787, which is equal to the current number of partitions) it would require a much increased result size than currently configured. I would rather work out if this result size can be reduced than keep increasing the Spark configuration and driver size. Can anybody please give any insights into what information is returned to the driver in the write function mentioned above and if there are any particular types of data that could cause each result set to be so large?

Hi folks, Bloom filter indexes are supposed to be a data skipping method like column-level statistics embedded into transaction log files. When I issue a query that can benefit from column-level stats, Spark SQL UI for the query will show some files being pruned and not read at all hence making the whole query faster. This is not the case I have seen with bloom filter index at least with the notebook provided with the docs page https://docs.databricks.com/optimizations/bloom-filters.html. That notebook does not have the classic benchmarking method of disabling disk cache hence the results could be misleading. It also uses

SELECT count(*)

not

SELECT *

. Anyway, bloom filters did not produce the same file pruning behaviour like column-level stats. I wonder if anyone has used them effectively before and can explain how to verify they are really making a difference.

Hi Everyone, Just want to confirm my understanding, does table level gets stored anywhere for each or current version of Delta Table? Or is it derived from file level stats that's gets stored in transaction logs?

Hi All, By mistakenly deleted the entire checkpoint folder in production. Is there any way to recover the checkpoint folder back.