- For Data Migration Tool introduction, please refer to /README.md
- For Installation user guide, please refer to /docs/installation/README.md
- Initial release will support bulk load data transfer only.
- Initial release will support Teradata to BigQuery translation using BQ DTS, row and column validation of the data in source and target.
- If tables from multiple schemas are to be migrated, create separate config files for each source schema.
- Current tool version supports only bulk load which is ideally done only once, note that the transfer shall take place by the process following upload of the config files.
- If there are new table additions to a batch/config file, ideally create a new config file with a new table list even if rest details remain the same. If not followed, there might be multiple data transfer configs responsible for migrating the same tables.
- Note that reuploading the same configuration file or using a different config file for the same dataset and tables, will create data duplication in the tables for the target dataset.
- Please be cautious of the number of parallel processes being triggered for Data Migration. You may need to scale the AGENT VM infrastructure (vCPUs, Memory), Composer Infrastructure based on expected parallel data migration jobs.
The user uploads a configuration json file to dmt-config-<project-id-or-customer-name-given-in-deployment> bucket data folder which initiates data migration.
As the configuration is uploaded, a new file create/update trigger is sent to pub/sub which triggers the DAG controller_dag.
- Target dataset creation to be done by the user/concerned team before uploading the configuration file.
Data Migration deployment will take care of the workflow needed to perform
- Initial data load
- Data Validation using DVT tool
- Options to run DVT on cloud run and GKE airflow pod operator depending on scaling requirements.
Ensure that you have followed the steps to deploy Data Migration Tool architecture as mentioned in the readme with the option of _DATA_SOURCE=teradata
If you had previously only deployed translation architecture - you could run a data architecture deployment for teradata migration with the below command provided the Terraform State files in remote GCS bucket from translation deployment are intact.
gcloud builds submit . --project ${SOURCE_PROJECT} \
--config cloudbuild_deploy.yaml \
--substitutions \
_DATA_SOURCE=teradata
-
Check for success messages in your Cloud Build Logs for deployment of
- Google Cloud Storage
- Cloud Pub/Sub
- Google Compute Engine
- Cloud Composer
- Cloud Run
- Big Query
-
Install teradata utilities on Agent VM (dm-vm-teradata-bq)
- Download utility package from teradata downloads https://downloads.teradata.com (Linux Package: https://downloads.teradata.com/download/tools/teradata-tools-and-utilities-linux-installation-package-0 ).
Note: Ubuntu OS has a separate software package compared to other Linux OS. Please make sure you download the Ubuntu-specific (DMT's default Terraform deployment uses Ubuntu for the Teradata Agent VM). - Upload the package to a bucket (<temp-bucket>)
- SSH into the Agent VM and switch user to root
- Copy the utility from <temp-bucket> to Agent VM
(Can also be done via web ssh console upload feature or sftp client)
sudo gcloud storage cp gs://<temp-bucket>/TeradataToolsAndUtilitiesBase__ubuntu_<version>.tar.gz /opt/migration_project_teradata_bq/ - Go to the
migration_project_teradata_bqdirectorycd /opt/migration_project_teradata_bq/ - Extract the new package
sudo tar -xf TeradataToolsAndUtilitiesBase__ubuntu_<version>.tar.gz - Install the utilities via setup.sh (with root) : bteq, fastexp, mload, tptbase
sudo ./TeradataToolsAndUtilitiesBase/setup.sh 1 2 5 15
- Download utility package from teradata downloads https://downloads.teradata.com (Linux Package: https://downloads.teradata.com/download/tools/teradata-tools-and-utilities-linux-installation-package-0 ).
-
Copy Teradata JDBC JAR to Agent VM (dm-vm-teradata-bq) at path:
/opt/migration_project_teradata_bq/- If you have not already done this during the main ReadMe setup instructions, download the JAR from Teradata downloads: https://downloads.teradata.com/download/connectivity/jdbc-driver
- Upload the package to a bucket (<temp-bucket>)
- SSH into the Agent VM and switch user to root
- Copy the Teradata JDBC JAR from <temp-bucket> to Agent VM (ensure it is named
terajdbc4.jar)gcloud storage cp gs://<temp-bucket>/terajdbc4.jar /opt/migration_project_teradata_bq/
-
Ensure the Teradata Agent VM (Google Compute Engine) has the following folder structure which signifies successful deployment of executables in the Agent VM
/opt/migration_project_teradata_bq/
> pwd
/opt/migration_project_teradata_bq
> ls -ltr
total 115608
-rw-r--r-- 1 root root 60156986 May 11 06:07 mirroring-agent.jar
-rw-r--r-- 1 root root 3086 May 17 07:04 vm_start_script.sh
drwxr-xr-x 4 root root 4096 May 17 07:05 agent_controller
-rwxr--r-- 1 root root 192307 May 17 07:05 startup.log
-rw-r--r-- 1 root root 56843209 May 19 11:50 TeradataToolsAndUtilitiesBase__ubuntu_x8664.17.20.11.00.tar.gz
-rw-r--r-- 1 root root 1159293 May 19 11:50 terajdbc4.jar
drwxrwxrwx 4 root root 4096 May 19 12:06 local_processing_space
The below list of logging tables are created by terraform templates and record activities for Data Migration Tool
| Dataset.Table Name | Migration Phase (Translation, Schema Mig, Data Mig, Validation) | Description |
| dmt_logs.dmt_teradata_transfer_tracking | Data Migration | Containing mapping for transfer_id, agent_id and config file |
| dmt_logs.dmt_teradata_transfer_run_summary | Data Migration | Contains summary for DTS run |
| dmt_logs.dmt_teradata_transfer_run_jobwise_details | Data Migration | Contains DTS job wise details |
- Data Migration
- Data Validations
- SQL Validations
{
"unique_id":"unique_id_to_filter_bq_result_table",
"batchDistribution":1,
"type":"data",
"source":"teradata",
"table_list_file": "gs://dmt-teradata-data-<project-id-or-customer-name-given-in-deployment>/tables_list.csv",
"transfer_config":{
"dataSourceId":"on_premises",
"displayName":"unique_id_to_filter_bq_result_table",
"params":{
"database_type":"Teradata",
"bucket":"dmt-teradata-data-<project-id-or-customer-name-given-in-deployment>",
"database_name":"<database_name>",
"agent_service_account":"dmt-teradata-agent-vm@<project-id>.iam.gserviceaccount.com"
},
"emailPreferences":{
"enableFailureEmail":false
},
"notificationPubsubTopic": "projects/<project-id>/topics/dmt-teradata-dts-notification-topic-<project-id-or-customer-name-given-in-deployment>",
"destinationDatasetId":"<bq_target_dataset>"
},
"agent_config":{
"transfer-configuration":{
"project-id":"<project-id>",
"location":"us"
},
"source-type":"teradata",
"console-log":false,
"silent":false,
"teradata-config":{
"connection":{
"host":"<source_db_host_or_ip>",
"username": "<source_db_username>",
"secret_resource_id": "projects/<project-id>/secrets/secret-edw_credentials/versions/<version_no>"
},
"local-processing-space":"/opt/migration_project_teradata_bq/local_processing_space",
"max-local-storage":"200GB",
"gcs-upload-chunk-size":"32MB",
"use-tpt":true,
"retain-tpt-files":false,
"max-sessions":0,
"spool-mode":"NoSpool",
"max-parallel-upload":2,
"max-parallel-extract-threads":2,
"session-charset":"UTF8",
"max-unload-file-size":"2GB"
}
},
"validation_config": {
"source_config": {
"source_type": "Teradata",
"host": "<source_db_host_or_ip>",
"port": 1025,
"user-name": "<source_db_username>",
"password": "secret-<teradata_secret_name>"
},
"target_config": {
"target_type": "BigQuery",
"project-id": "<project-id>"
},
"validation_params_file_path" : "gs://<dmt-teradata-config-<project-id-or-customer-name-given-in-deployment>/validation/teradata/validation_params.csv",
"validation_type":"<row/column>",
"validation_mode":"<gke/cloudrun>",
"pod_operator_mem" :"4000M",
"pod_operator_cpu" :"800m"
}
}
Data Transfer sample config file location
| JSON attribute | Description |
| unique_id | Provide an unique name for identifying the data migration
– Note: If the user opted for data migration, along with schema migration through the tool, this unique id should be the same as the one used in the schema migration config file. |
| type | Type of migration : data |
| source | Source datawarehouse : teradata |
| table_list_file | File uploaded in GCS bucket same as the one used for uploading config file. This file should provide table names to be migrated from a particular database.
Note: table_list_file key only needs to be provided in case the user chooses to opt only for data migration through the tool (without schema translation). Tables in the CSV file should always be in the same case as how they exist in source Teradata and ultimately match with validation_config source_target_table mapping in json config for DVT validations to avoid any failures in testdb. |
| transfer_config | Sub json config to be used to create data transfer config on bigquery
Refer: REST Resource: projects.locations.transferConfigs | BigQuery | Google Cloud for exhaustive keys under transfer_config sub json. |
| transfer_config:dataSourceId | on_premises |
| transfer_config:displayName | Prefix of the bigquery data transfer service config display name |
| transfer_config:params:database_type | Teradata |
| transfer_config:params:bucket | Bucket name for storing data files extracted from Teradata.
Ideally dmt-teradata-data-<project-id-or-customer-name-given-in-deployment> is created using the Foundations deployment. If a user wishes to use another bucket, it needs to be created by the user/concerned team before uploading the file. |
| transfer_config:params:database_name | Teradata database name (from which tables needs to migrated) |
| transfer_config:params:agent_service_account | Service account configured on agent vm. \
Ideally dmt-dm-teradata-bq@<project-id>.iam.gserviceaccount.com is created from terraform. |
| transfer_config:emailPreferences:enableFailureEmail | false |
| transfer_config:notificationPubsubTopic | PUB/SUB topic to send dts run status.
Ideally projects/<project-id>/topics/dmt-teradata-dts-notification-topic-<project-id-or-customer-name-given-in-deployment> is created from terraform. |
| transfer_config:destinationDatasetId | The BigQuery target dataset id. |
| agent_config | Sub json config to be used to run migration agent
Check: Migrate schema and data from Teradata | BigQuery | Google Cloud for more |
| agent_config:transfer-configuration:project-id | Project id to which migration is taking place |
| agent_config:transfer-configuration:location | Same as data location for the dataset and DTS config |
| agent_config:source-type | teradata |
| agent_config:console-log | false |
| agent_config:silent | false |
| agent_config:teradata-config:connection:host | Hostname/ IP address of on-prem Teradata server |
| agent_config:teradata-config:connection:username | Teradata username with permissions to query on source tables |
| agent_config:teradata-config:connection:secret_resource_id | Secret Resource ID containing password for Teradata username For example - projects/my-project/secrets/my-secret-name/versions/1 You can copy Resource ID from Secret Manager secret's version |
| agent_config:local-processing-space | Local Staging directory path in agent VM where data files extracted from teradata will be temporarily stored
/opt/migration_project_teradata_bq/local_processing_space |
| agent_config:max-local-storage | The maximum amount of local storage to use for the extraction in the specified staging directory.
The default value is 200GB. The supported format is: numberKB|MB|GB|TB. This parameter is not enforced for TPT extractions. |
| agent_config:gcs-upload-chunk-size | This parameter along with max-parallel-upload are used to control how much data gets uploaded to Cloud Storage at the same time. |
| agent_config:max-parallel-upload | Maximum number of threads used by the migration agent to upload files to GCS |
| agent_config:use-tpt | Flag to decide whether to use Teradata Parallel Transporter (TPT) as the extraction method.
If given false FastExport will be used as the extraction method. |
| agent_config:retain-tpt-files | false |
| agent_config:max-sessions | Specifies the maximum number of sessions used by the export job.
If set to 0, then the Teradata database will determine the maximum number of sessions for each export job. |
| agent_config:spool-mode | Default value: NoSpool.
You can change this parameter if any of the disadvantages of NoSpool apply to your case. |
| agent_config:max-parallel-extract-threads | Number of parallel threads used for extracting the data from Teradata. |
| agent_config:session-charset | Default value : UTF8 |
| agent_config:max-unload-file-size | Determines the maximum extracted file size.
This parameter is not enforced for TPT extractions. |
| validation_config | Sub json config to be used to run DVT |
| validation_config:source_config:source_type | DVT Validation
Source datawarehouse : Teradata |
| validation_config:source_config:host | hostname/ IP address of on-prem Teradata |
| validation_config:source_config:port | Port number of on-prem Teradata |
| validation_config:source_config:user-name | User-name with permissions to query on source tables |
| validation_config:source_config:password | Secret Manager key name
secret- <secret key name> For example - secret-edw_credentials |
| validation_config:target_config:target_type | BigQuery |
| validation_config:target_config:project-id | BigQuery GCP Project ID |
| validation_config:validation_params_file_path | GCS location of the CSV file or Excel sheet, containing table or file names along with DVT Validation Flags.
Examples: gs://dmt-config-dmt-demo-project/validation/teradata/validation_params.csv gs://dmt-config-dmt-demo-project/validation/teradata/validation_params.xlsx Read Instructions below to understand how to populate and upload this file/sheet |
| validation_config:validation_type | DVT validation type
data |
| validation_config:validation_mode | DVT validation mode
Possible values - gke, cloudrun |
| validation_config:pod_operator_mem | Pod Operator memory configuration when DVT mode is chosen as GKE
Default Value - 4000M |
| validation_config:pod_operator_cpu | Pod Operator cpu configuration when DVT mode is chosen as GKE
Default Value - 800m |
unique_id: name to uniquely identify the batches or DTS for this config file.
Note if the user opted for data migration, along with schema migration through the tool, this unique id should be the same as the one used in the schema migration config file.
displayName: prefix of the bigquery data transfer service config display name
bucket: user provided bucket name for storing data files extracted from Teradata. Ideally dmt-teradata-data-<customer_name provided in TF> is created using the Foundations deployment. If a user wishes to use another bucket, it needs to be created by the user/concerned team before uploading the file.
notificationPubsubTopic: field necessary to continue flow from Data Transfer Run Logging, including data validation. Given in the format _projects/<project-name>/topics/dmt-teradata-dts-notification-topic-<customer_name provided in TF>. Use the same topic as mentioned above, as required mappings are already done for this topic using the Foundations deployment.
destinationDatasetId: Target dataset id for the particular schema migrated from teradata.
table_list_file: file uploaded in GCS bucket same as the one used for uploading config file. This file should provide table names to be migrated from a particular schema in newlines. Eg:
- DEPARTMENT
- EMPLOYEE
- SALARY
- HR_RECORDS
Note that this key only needs to be provided in case the user chooses to opt only for data migration through the tool (without schema translation). As such, tables structure is created by Bigquery DTS itself rather than the CompilerWorks schema migration feature from the tool.
Tables in the CSV file should always be in the same case as how they exist in source Teradata and ultimately match the contents of CSV/Excel file uploaded as validation parameters file in GCS.
project-id: project id to which migration is taking place
location: same as data location for the dataset
teradata-config: contains host, user and password keys for the teradata server. Provide password value as secret-<secret-name-suffix>. This secret should be created in the Secret Manager as secret-<secret-name-suffix>.
Note that target table name case is sensitive due to case sensitive nature of bigquery. So in the case of only Data Migration, it needs to be always be in the same case as how they exist in source Teradata and ultimately match with validation_config source_target_table mapping in config.json for DVT validations
validation_object: validation_object key in Validation_config needs to be _data _for data transfer configs.
validation_type: validation_type key in Validation_config can be column or row depending on the requirement.
validation_mode: Value can be ‘cloudrun’ or ‘gke’ based on where the user wants to conduct data validation.
primary_key: If Validation_type is _row, _please provide another key called primary_key in the same section with the primary key for the particular table.
Refer REST Resource: projects.locations.transferConfigs | BigQuery | Google Cloud for exhaustive keys under transfer_config sub json.
Please do not edit headers, data validation rules and other sheets in the Excel/Google Sheet as it'll alter the data validation checks put in-place.
Fill the columns according to the use-case. Data Validation Rules within the sheet will help to fill the fields correctly.
To know more about which flags are relevant for which translation (ddl,sql,custom-query) and migration (data), please refer to the tab named DVT Guide. This sheet contains link to DVT Github pages which explain about all possible flags in DVT CLI.
You can refer to the tab Example Sheet to get an idea of which flags can be supplied for which validation.
-
To follow along with Excel sheet
- Open this Excel Template File in Microsoft Excel. (not in Google Sheets, as opening .xlsx in Google Sheets will corrupt the existing Data Validation Rules)
- Fill the sheet
Validation. Once filled, save the file in.xlsxformat. (Excel will convert the template file format.xltxto.xlsxfile for you)
-
To follow along with CSV file format
- Open this Google Sheet in browser (Viewer Access).
- Make a copy, to get an editable sheet.
- Once filled, download CSV file using the Menu Option. (File --> Download --> Comma Separated Values (.csv))
Once filled, upload the corresponding Excel (.xlsx) file or CSV file in GCS bucket, (inside relevant folder within dmt-config-dmt-demo-project/validation) and specify the GCS path in configuration file.
Each of the batches are passed on to teradata_data_load_dag which is responsible for creating and running data transfer service configuration, and can be viewed under Data Transfers in Bigquery.
Each bigquery DTS can be identified with a transfer_run_id. The same can be tracked in table <PROJECT_ID>.dmt_logs.dmt_teradata_transfer_tracking with config json bucket and object path.
Below tasks happens with teradata_data_load_dag DAG execution
- Migration agent will run TPT / FastExport extraction from teradata source into agent VM and upload the extracts to configured bucket
- Data Transfer Service will load data from buckets to bigquery.
- Cloud composer will create BQ tables using translated DDL. OR
- DTS will create BQ tables with schema inferred from teradata exports.
As soon as a transfer run is completed in bigquery, a pub sub message will be received at cloud run, which triggers teradata_transfer_run_log DAG. This DAG is responsible for
- Populating data transfer results in below bigquery tables: <PROJECT_ID>.dmt_logs.dmt_teradata_transfer_run_summary - Details information about the transfer run <PROJECT_ID>.dmt_logs.dmt_teradata_transfer_run_jobwise_details - Details information about each job(corresponding to a target table) within a transfer run.
- Creating a log file for a transfer run in dmt-teradata-datamigration-logging- bucket. These logs are also visible in Bigquery Data Transfers. The purpose of this log file is to persist these for analysis or debug purposes. Log files are named as <transfer_run_id>.json under a folder name same as unique_id.
- Filtering matching tables given in Validation config table mapping and table list, and passing these to validation_dag for data validation through DVT
Composer DAG validation_dag will validate the data migrated to BQ. Results for DVT can be viewed in <PROJECT_ID>.dmt_logs.dmt_dvt_results
Below query can be used to see the results:
SELECT * FROM `<project-id>.dmt_logs.dmt_dvt_results` where
-- validation_type='Column'
-- run_id = <transfer_run_id>
target_table_name = <target_table_name>
There are two ways to validate data - column or row. Validation_type given as column verifies count aggregation on the source and target tables. Validation_type given as row verifies hash of the rows based on the primary key. Hence for row validations, providing primary_key in Validation_config json is necessary.
Check the field descriptions section for detailed information on the keys in the configuration file.