how to find specific value by using synapse

Question

Hi,

I am using Azure synapse analytics to read the source data from a gen2 container, and the source data is stored in parquet files.

When using copy activity to load the data from parquet files to sql pool, it throw ParquetDateTimeExceedLimit error since there are values of "'0001-01-01 00:00:00'". How can we find all the values of "'0001-01-01 00:00:00'" in the parquet files and replace them? Or is there any other alternative ways that help load the data from parquet files to sql pool with values of "'0001-01-01 00:00:00'"? Thanks

Answer 1

You can achieve your goal like below :

Approach 1: Identify and Replace Specific Values in Parquet Files

Synapse provides an integrated Apache Spark environment. You can use Spark to read your Parquet files, find the problematic datetime values, and replace them before loading into SQL Pool.

Here’s a Spark code snippet to find and replace the specific datetime values:

# Load the parquet file into a Spark DataFrame

df = spark.read.parquet("abfss://<container>@<storage-account>.dfs.core.windows.net/<file-path>")

# Filter rows where the datetime column contains '0001-01-01 00:00:00'

df_filtered = df.filter(df["your_datetime_column"] == "0001-01-01 00:00:00")

# Show rows with the problematic value

df_filtered.show()

# Replace '0001-01-01 00:00:00' with a valid value (e.g., NULL or another date)

df_replaced = df.withColumn("your_datetime_column", when(df["your_datetime_column"] == "0001-01-01 00:00:00", lit(None)).otherwise(df["your_datetime_column"]))

# Save the modified DataFrame back to Parquet or directly load it to SQL Pool

df_replaced.write.parquet("abfss://<container>@<storage-account>.dfs.core.windows.net/<output-file-path>")

This approach allows you to clean up the data before loading it into SQL Pool.

You can use Mapping Data Flows in Azure Synapse Analytics to find and replace the problematic values during the transformation phase before loading the data.

• Use a Data Flow to load the Parquet data.
• Add a Derived Column transformation to check for the invalid datetime values and replace them with a default value or null.
• Load the transformed data into SQL Pool.

Example expression in the Derived Column transformation:

iif(datetimeColumn == '0001-01-01 00:00:00', toDateTime('1970-01-01'), datetimeColumn)

Approach 2: Load Data with Custom DateTime Handling

If you need to load the data into SQL Pool without transforming the Parquet files, you can:

In the Synapse Copy Activity, you can configure the activity to ignore or skip the invalid datetime values and use a default value instead. This way, you can load the data even with those problematic values without failing the pipeline.

If the destination SQL table is expecting specific date ranges, you can adjust the schema by:

• Setting a different default date value that is compatible with the SQL Pool's DATETIME type.
• Using a VARCHAR column to initially load the dates as strings, then converting them to the proper DATETIME format after handling any invalid values.

Approach 3: Use External Tables with Parquet

You can also define an External Table in Synapse that reads directly from the Parquet files without needing to load the data into SQL Pool.

1. Create an external table referencing your Parquet file.
2. Use SQL queries to identify rows with '0001-01-01 00:00:00'.

   
      CREATE EXTERNAL TABLE parquet_table (
   
          your_datetime_column DATETIME
   
      )
   
      WITH (
   
          LOCATION = 'your_parquet_file_location',
   
          DATA_SOURCE = your_data_source,
   
          FILE_FORMAT = parquet_format
   
      );
   
      SELECT * 
   
      FROM parquet_table
   
      WHERE your_datetime_column = '0001-01-01 00:00:00';
   
   

   You can then handle the problematic values directly through the external table before loading them into SQL Pool.

Answer 2

Thanks for your answers. But for Approch 2, how can I skip the invalid datetime values and use a default value since I can only see the two options in the following copy activity settings.

Also, for Approach 3, if I create an external table, it has to be on a dedicated sql pool?

Thanks