Forum Discussion
Responsive ResidentSave a spark dataframe in a Lakehouse with Schema Support enabled (Feasible?)
Hi,
We have created a Lakehouse with Schema support enabled. Then we have developed a notebook to save a pyspark dataframe as a delta table
it should be possible but from the error it seems like a partition mismatch issue, not a problem with schema support itself.
- Once a Delta table is created (example: staffestablishmentplan), its partitioning columns are fixed unless the table is dropped and recreated. So, if the table already exists without partitions or with different partitions, then this code: spark_df.write.mode("append").format("delta").option("overwriteSchema", "true").partitionBy("MonthKey").saveAsTable("staffestablishmentplan") will throw an error.
- Check the existing table partitioning: spark.sql("DESCRIBE DETAIL staffestablishmentplan").select("partitionColumns").show(truncate=False)
If the output is [], then the table was created without partitions. In that case:
You cannot append with a new partitioning scheme unless you drop and recreate the table.
You need to either:
Remove .partitionBy("MonthKey") when appending or
Drop and recreate the table with the desired partition.
If you are still early in development and can afford to overwrite the table (if possible)
Finaly recommendation to try:
- Check if the table already exists
- If it exists without MonthKey partition, you have two options:
- drop it
- then run your saveAsTable with partitionBy
- Or append without partitionBy if you cannot afford to drop it
Please 'Kudos' and 'Accept as Solution' if this answered your query.
2 Replies
When you partition a table it dynamically creates folders/directories based on that partition field(s) and the field is not in your data file anymore but is a directory instead. If you want to repartition a table you must first create a new table with your new partition field(s) then run an select * from old table sql statement to insert into and make sure you partition field is your first field in the select or use your pyspark code from above to load your new table.
- Vinodh247
Super User
it should be possible but from the error it seems like a partition mismatch issue, not a problem with schema support itself.
- Once a Delta table is created (example: staffestablishmentplan), its partitioning columns are fixed unless the table is dropped and recreated. So, if the table already exists without partitions or with different partitions, then this code: spark_df.write.mode("append").format("delta").option("overwriteSchema", "true").partitionBy("MonthKey").saveAsTable("staffestablishmentplan") will throw an error.
- Check the existing table partitioning: spark.sql("DESCRIBE DETAIL staffestablishmentplan").select("partitionColumns").show(truncate=False)
If the output is [], then the table was created without partitions. In that case:
You cannot append with a new partitioning scheme unless you drop and recreate the table.
You need to either:
Remove .partitionBy("MonthKey") when appending or
Drop and recreate the table with the desired partition.
If you are still early in development and can afford to overwrite the table (if possible)
Finaly recommendation to try:
- Check if the table already exists
- If it exists without MonthKey partition, you have two options:
- drop it
- then run your saveAsTable with partitionBy
- Or append without partitionBy if you cannot afford to drop it
Please 'Kudos' and 'Accept as Solution' if this answered your query.
