Hi folks, does anyone know how to drop columns from a Delta Lake schema when the data is stored in Azure Blob Storage? E.g. I have data in storage account like so: abfss://container@storageaccount/path/to/data. path/to/data contains a bunch of folders in yyyy/mm/dd format, as well as a _delta_log folder. I'm trying to interact with this delta schema via scala in Azure synapse, e.g. val deltaTable = DeltaTable.forPath("abfss://..."). But then from there I'm not sure what to do, as all docs I can find mention writing SQL like ALTER TABLE table DROP COLUMNS columns, but I see no way to do that with this deltaTable. If I try spark.sql("SHOW TABLES") just to see what's there, I get an error about a null path, and if I try spark.sql("SELECT * FROM delta.`abfss://...`") that errors out too. Is there just something simple I'm missing? I feel like dropping columns should be a simple matter but I can't find any way to do it

I wrote a blog post on this that you might find useful: https://delta.io/blog/2022-08-29-delta-lake-drop-column/

Hi Matthew, I actually read that already! My confusion is how do I read an existing delta table (I know the _delta_log folder is all json but my actualy data is parquet), and then write the changes I made back to that table? One thing I tried was val table = spark.read.format("delta").load(path), then did table.drop("column"), table.write.format("delta").save(path), but that gave me some errors about parquet files not existing (no idea why that is, delta referring to files that don't exist anymore?). And using the DeltaTable.vacuum command didn't find any files to clean up so not sure what's going on

Here’s the command: `spark.sql(“ALTER TABLE

my_cool_table

DROP COLUMN language”)`

Does that latter command you sent work with abfss paths? I tried

spark.sql("SELECT * FROM delta.`<abfss://container@account/path/to/data>`")

Where path/to/data has the _delta_log folder, but that gave an error:

org.apache.spark.sql.AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.IllegalArgumentException: null path

, am I writing the path wrong?

two things that would come to my mind: • did you upgrade your tables properties?

spark.sql(
    """ALTER TABLE `my_cool_table` SET TBLPROPERTIES (
   'delta.columnMapping.mode' = 'name',
   'delta.minReaderVersion' = '2',
   'delta.minWriterVersion' = '5')"""
)

• could very well also be that this is not yet working on Azure Synapse due to an older version of Delta Lake 🤔

I tried running that command to upgrade the table properties like so:

spark.sql(
    """ALTER TABLE delta.`<abfss://container@account/path/to/data>` SET TBLPROPERTIES (
   'delta.columnMapping.mode' = 'name',
   'delta.minReaderVersion' = '2',
   'delta.minWriterVersion' = '5')"""
)

But that resulted in the same null path error. I'll try reaching out on the Synapse forums/support and see if this is a known issue on Synapse!

so in general this works for me on Synapse: my path is in format

abfss://<container>@<account>.<http://dfs.core.windows.net/MyTable|dfs.core.windows.net/MyTable>

@Gerhard Brueckl Is this a Scala or PySpark notebook? And which Spark runtime are you using? Just want to eliminate as many differences as possible!

hmm, could be - can you try without? you might need to URL-escape the special characters -

=

would be

%3D

then https://www.w3schools.com/tags/ref_urlencode.ASP I am using this configuration

I tried replacing the = with %3D but unfortunately that didn't work either, and my Spark configuration was the same as yours. For some reason or other this just doesn't work for me. On the bright side, my coworker figured out this solution that did work for us, and I'll share here in case this is useful for anyone else:

import org.apache.hadoop.fs.Path
import org.apache.spark.sql.delta.catalog.DeltaTableV2
import org.apache.spark.sql.delta.commands.{AlterTableDropColumnsDeltaCommand, AlterTableSetPropertiesDeltaCommand}

val tablePath = "<abfss://container@storage.dfs.core.windows.net/path/to/data>"
val deltaTableV2 =  DeltaTableV2(
    spark,
    new Path(tablePath)
)
AlterTableSetPropertiesDeltaCommand(
    deltaTableV2,
    Map(
    "delta.columnMapping.mode" -> "name",
    "delta.minReaderVersion" -> "2",
    "delta.minWriterVersion" -> "5"
    )
).run(
    spark
)

AlterTableDropColumnsDeltaCommand(
    deltaTableV2,
    Seq(Seq("columnName"))
).run(spark)

spark.read.format("delta").load(tablePath).printSchema()