Datastream_to_bigquery dataflow template : Invalid date problem

[Medmah]

I am using Datastream_to_bigquery dataflow template to ingest data from GCS to Bigquery. The avro files in GCS are created by Datastream and the data source is MySQL. In the source tables I have columns with date type (ex. birthdate : '2015-03-11').  The problem I am facing is the data with date before '1677-09-21' or after '2262-04-12 ' is not loaded into the staging table and is written in the dlq GCS folder with error message 'Invalid date'.  Anyone can help me to get this data ?

[ms4446]

The "Invalid date" error arises from the interaction between MySQL, Google Cloud Datastream, and BigQuery, particularly around how dates are represented and handled across these systems. Here's a closer look at the underlying issues:

• MySQL's DATE type: MySQL supports a wide range of dates from '1000-01-01' to '9999-12-31'.
• BigQuery's DATE type: BigQuery also has a broad date range, from '0001-01-01' to '9999-12-31'.
• Dataflow/Datastream Handling: The issue occurs during the Datastream process and Dataflow template execution. Dates outside the range '1677-09-21' to '2262-04-11' cause problems, likely due to internal timestamp representations.

Addressing this issue involves adjustments at the data source, during processing, or within the BigQuery schema. Here are refined approaches:

1. Modify Source MySQL Data (If Possible):

• If you can modify the source data, adjust out-of-range MySQL dates to fall within BigQuery's acceptable range or use sentinel values to indicate invalid dates.
• Pros: Direct and straightforward if you can change your source.
• Cons: Original date information is altered or lost.

2. Data Transformation During Dataflow:

• Implement a transformation step in your Dataflow job to detect and modify out-of-range dates, either converting them to sentinel values or string representations.
• Pros: Preserves the essence of the original information in a transformed format.
• Cons: Requires additional development and testing effort.

3. Change Destination to String Column in BigQuery:

• Modify the BigQuery destination schema to accept dates as strings. This bypasses the limitation but impacts date-specific functionality.
• Pros: Simple and ensures all data is loaded without loss.
• Cons: Date operations become more complex and less efficient.

Here's an example in Python for a transformation step that adjusts out-of-range dates:

import apache_beam as beam 
import datetime 

def transform_out_of_range_dates(record): 
  if 'birthdate' in record:
    date_str = record['birthdate'] 
    try: 
      valid_date = datetime.datetime.strptime(date_str, '%Y-%m-%d') 

      if not (datetime.date(1677, 9, 21) <= valid_date.date() <= datetime.date(2262, 4, 11)):
          raise ValueError("Date out of range") 
    except ValueError: 
       record['birthdate'] = '9999-12-31'  
       return record 

 with beam.Pipeline() as p: 
   # ... Your Dataflow code ...

[Medmah]

Thank you for the response ! Unfortunately, I cannot directly modify the source MySQL data due to read-only access. While I'm using Datastream to BigQuery Dataflow Flex template, extensive UDF development in JavaScript is required for each source table, making it a less scalable solution. Additionally, modifying the BigQuery destination schema to accept dates as strings is not an option. I can not understand why the Dataflow template does not support dates outside the range '1677-09-21' to '2262-04-11'  ! Is it possible to modify this template to fix this issue ?

[ms4446 · Accepted solution]

The issue with handling dates outside the range of '1677-09-21' to '2262-04-11' in the Datastream to BigQuery Dataflow template likely stems from the way dates are represented and processed through the pipeline. Here's a refined explanation:

• Underlying Libraries and Timestamp Conversion: The specific date range limitation is often related to the internal representation of dates and timestamps. When dates are converted to timestamps for processing or serialization (e.g., into Avro format), they are typically represented as the number of seconds or microseconds since the Unix epoch (January 1, 1970). The range '1677-09-21' to '2262-04-11' corresponds to the minimum and maximum values that can be represented in microseconds within a 64-bit signed integer. This limitation can arise from the programming languages or libraries used in the Dataflow template, rather than BigQuery or MySQL directly.

• BigQuery and MySQL Date Compatibility: While both BigQuery and MySQL support a broad range of dates, the practical limitations during data processing are often due to the intermediate steps (like serialization/deserialization) and the systems or libraries that impose narrower supported ranges.

Modifying the Dataflow Template: Challenges and Considerations

Direct modification of the Dataflow template to accommodate broader date ranges presents several challenges:

• Complexity: Understanding and modifying Dataflow templates requires in-depth knowledge of Apache Beam, the specific serialization/deserialization mechanisms (such as Avro), and the Dataflow SDK. The process can be complex due to the numerous components and dependencies involved.

• Maintainability: Custom modifications to a core Dataflow template might lead to maintenance challenges, especially when Google updates the template or the Dataflow service. Ensuring compatibility with future updates requires ongoing vigilance and potentially additional modifications.

Proposed Scalable Workarounds

Given the challenges with direct template modification, here are refined scalable workaround strategies:

1. Custom Transformation within Dataflow:

   • Develop a custom Dataflow pipeline, leveraging the template's logic or starting from scratch, and incorporate steps to identify and adjust out-of-range dates before loading the data into BigQuery. This approach allows for precise control over data transformations.
   • Pros: Offers a tailored solution with full control over data processing.
   • Cons: Increases development and maintenance effort.

2. Pre-processing Stage:

   • Implement a pre-processing stage prior to Datastream ingestion. This could involve a script or a small application that reads data from MySQL, identifies and adjusts out-of-range dates (e.g., converting them to sentinel values or string representations), and then writes the processed data to an intermediate storage solution like GCS.
   • Pros: Minimizes the need for changes in the Dataflow/BigQuery pipeline and maintains the integrity of the original data.
   • Cons: Adds an additional processing layer, which could impact performance and requires management.

While the direct modification of the Dataflow template to handle a broader range of dates is theoretically possible, it's often more practical to implement a custom transformation within Dataflow or introduce a pre-processing stage. These approaches provide scalable solutions to the date range limitation while balancing complexity, maintainability, and data integrity.

[Medmah]

Thank you again for your help. I contacted my client to modify the data source, I think this is the best solution for the moment.