Update of an external table in Azure Synapse

Question

Update of an external table in Azure Synapse

Peter Polacek 25

Hello,

we are using external tables which are defined for example like this:

CREATE EXTERNAL TABLE [TEST].[TEST_TABLE] WITH (    
    LOCATION = '

Accepted answer

0 additional answers

Your answer

Answer 1

Azar 29,520 MVP Volunteer Moderator

Hi there Peter Polacek

Thanks for using QandA platform

External tables in Azure Synapse Analytics are read-only, so you cannot directly update them. Instead, try updating the underlying data files in Azure Data Lake that the external table references. To do this, you can use Azure Data Factory, Synapse Pipelines, or Spark notebooks to modify the data in place. Once the underlying data is updated, the external table will automatically reflect these changes.

If you want to manage updates dynamically, you can create a view on top of the external table that combines the original data with new or updated records from another source. For exmple.

CREATE OR ALTER VIEW [TEST].[UPDATED_VIEW] AS
SELECT * 
FROM [TEST].[TEST_TABLE]
UNION ALL
SELECT * 
FROM [UpdatedRecords]; -- This source contains new or updated records

If this helps kindly accept the answer thanks much.

Peter Polacek 25 Reputation points

2024-12-11T18:35:06.62+00:00

Thanks Azar.

The data files behind those external tables are in PARQUET file format and I just read that parquet files are immutable. So I assume it's not possible to modify data inside them. Can you please confirm that, eventually give some example how it could be possible ?

Thank you very much !
Peter Polacek 25 Reputation points

2024-12-11T18:39:20.4466667+00:00

Thank you very much !
Azar 29,520 Reputation points MVP Volunteer Moderator

2024-12-11T19:17:20.4033333+00:00
Hi again Peter Polacek

Yupp, you're correct the Parquet files are immutable and you cannot modify them directly. To update the data in Parquet files, the standard approach is to read the existing data, apply the updates, and write the modified data to a new Parquet file. Once the new file is ready, you can replace the old file in the storage location referenced by your external table.

from pyspark.sql import SparkSession # Initialize Spark session spark = SparkSession.builder.appName("UpdateParquet").getOrCreate() # Read the existing Parquet file df = spark.read.parquet("path_to_existing_file") # Apply transformations (e.g., filtering, updating values, etc.) updated_df = df.withColumn("column_to_update", df["column_to_update"] + 1) # Example update # Write the updated DataFrame to a new Parquet file updated_df.write.mode("overwrite").parquet("path_to_new_file") # Replace the old file with the new one in Azure Data Lake

Once the new Parquet file is in place, the external table in Azure Synapse will automatically reflect the updated data without any additional changes to the table definition.

Dont forget to mark the answer accepted if it helped.

Share via

Update of an external table in Azure Synapse

0 additional answers

Your answer