Dataflow datastream to SQL

[akhilesh_arya2]

I am using Google cloud Dataflow to setup a pipeline between MySQL to PostgreSQL using Datastream to SQL template.
First the data is migrated from MySQL DB to Cloud Bucket using Datastream in Avro format.
after this step the Dataflow template Datastream to SQL works and collects the data froom AVRO files and migrate this data to PostgreSQL Database.
Now One Case arises that I need to alter my MySQL table and need to add a column to it I have made same changes to my PostgreSQL table. But My new data is coming to the PostgreSQL database table but the data in the column which i added is not getting added to the target table. What should I do ?

[ms4446 · Accepted solution]

The core issue likely stems from the schema mapping within your data migration pipeline becoming outdated due to the table alteration. This outdated schema mapping means the pipeline does not recognize or know how to handle the newly added column. Here are some steps you can take to try to resolve the issue:

1. Pipeline and Schema Management Understanding

• Datastream Configuration: Verify whether Datastream is set up to automatically detect and propagate schema changes. If it does not support this feature or if automatic detection is not enabled, manual intervention may be required.
• Dataflow Template Examination: Assess how the Datastream to SQL template is configured to manage schema mappings. If the template uses a static configuration approach, you'll need to update it to include the new column.

2. Addressing Schema Mismatch

• Manual Schema Update: If the pipeline tools offer the capability, manually update the schema mapping to incorporate the new column, ensuring that its data transfer is properly defined.
• Pipeline Reconfiguration: In cases where manual updates are not feasible or if the schema change is not automatically detected, consider reinitializing or recreating parts of your pipeline. This may prompt Datastream to generate a new configuration that reflects the updated MySQL source schema.

3. Data Backfill Strategy

Given the potential for existing data to lack values for the new column, a backfill strategy may be necessary:

• Direct SQL Updates: For immediate value population, execute SQL update commands directly on the PostgreSQL database to fill the new column with default or calculated values as needed.
• Utilize Pipeline Tools for Backfill: If your setup allows, use your pipeline tools to facilitate a backfill process, ensuring that all relevant data in PostgreSQL reflects the new column's values.

Important Considerations

• Dive into the documentation for both Datastream and the Datastream to SQL Dataflow template to understand their schema change management capabilities and configuration requirements.
• To reduce the risk of data inconsistencies, consider pausing data ingestion into MySQL during the schema update process.
• Develop a meticulous backfilling strategy, especially if the new column requires specific, non-default values. This may involve recalculating values based on existing data or running additional processes outside the immediate pipeline.