BDT Transformation Process

BDT is supported with the following databases. The table below lists the versions tested with.

Note

• Before beginning with transformation, download the JDBC connector .jar for the required version of database you are using and place the .jar files under the bdt/lib directory.

• Before performing BDT transformation, you can prepare input databases. Refer to Filtering Input Data for details.

BDT is supported with the following databases. The table below lists the versions tested with.

For the output file, BDT accepts the output files in the following file formats:

• CSV File

• Fixed Length File

Note

Before performing BDT transformation, you can prepare input databases. Refer to Filtering Input Data for details.

Input Data

BDT accepts the input files in the following file formats:

• CSV File

• Fixed Length File

The input files must meet the following data requirements:

• Per-column data size limit: 128K

• The input data file can contain quoted and unquoted values. Both field1_Value and field2_Value are recognized as data values. To use quoted values, you must specify the double quote character as a text qualifier.

• Fields must be separated by the delimiter character specified in the policy file.

• If a field value contains the delimiter character, the value must be enclosed by text qualifier characters as specified in the policy file.

• If a field value contains the text qualifier, the entire value must be enclosed in text qualifier characters.

• Another way to deal with field values having delimiter or qualifier characters is to escape those character using the backslash (\) instead of enclosing the field in text qualifier characters. When using this technique, set the unescapeInput attribute in the policy file to true. For example:

  If the field value has a backslash character, it must also be escaped using an additional backslash character.

• If you are using fixed length file format, you must set up input files accordingly. Refer to Setting up Input Files with Fixed-Length Record Format for details.

Note

Before performing BDT transformation, you can prepare input files. Refer to Filtering Input Data for details.

Output Data

BDT is supported with the following databases. The table below lists the versions tested with.

Input Data

BDT accepts the input files in the following file formats:

• CSV File

• Fixed Length File

The input files must meet the following data requirements:

• Per-column data size limit: 128K

• The input data file can contain quoted and unquoted values. Both field1_Value and field2_Value are recognized as data values. To use quoted values, you must specify the double quote character as a text qualifier.

• Fields must be separated by the delimiter character specified in the policy file.

• If a field value contains the delimiter character, the value must be enclosed by text qualifier characters as specified in the policy file.

• If a field value contains the text qualifier, the entire value must be enclosed in text qualifier characters.

• Another way to deal with field values having delimiter or qualifier characters is to escape those character using the backslash (\) instead of enclosing the field in text qualifier characters. When using this technique, set the unescapeInput attribute in the policy file to true. For example:

  If the field value has a backslash character, it must also be escaped using an additional backslash character.

• If you are using fixed length file format, you must set up input files accordingly. Refer to Setting up Input Files with Fixed-Length Record Format for details.

Note

Before performing BDT transformation, you can prepare input files. Refer to Filtering Input Data for details.

Create bdt.policy based on your input DB and transformation requirement. The Samples directory has bdt.policy samples with explanatory comments. Refer to Setting up BDT Policy for details.

To transform database table columns:

1. Create a bdt.policy file and add source database connection properties to the policy source attribute. Do the same for destination database. Refer to Listing Source and Destination tables and columns for details.

2. In the tables section of the policy, add an entry for table to transform, specify source and destination table, and the columns to transform.

3. Ensure the destination table has the exact same schema as the source table.

4. Specify the transformation operation for each column of the input data.

Note

If you do not specify any of these actions for a column, the contents of the column are copied to the output file without any transformation.

Listing Source Tables and Columns

Source connection properties are picked from the policy file source attribute.

• Run ./bdt.sh -ls table -p policyfilepath to list source tables.

• Run ./bdt.sh -ls column -table test1 -p policyfilepath to list the columns of source table test1

Listing Destination Tables and Columns

Destination connection properties are picked from the policy file destination attribute.

• Run ./bdt.sh -ld table -p policyfilepath to list destination tables.

• Run ./bdt.sh -ld column -table test1 -p policyfilepath to list columns of destination table test1

Performing Inplace Transformations

You can choose to have BDT create a separate database table with the transformed data, or instead perform an in-place transformation where the source database is itself transformed with new data, and no new database created. Specify either behavior with the inPlaceUpdate attribute value in the policy file.

If you specify an in-place transformation (by setting the inPlaceUpdate attribute to true), where the source and destination are the same, the destination attribute is ignored.

Sample DB-to-DB Transformation Policy

Sample DbToDbTransformation.policy for reference.

Create bdt.policy based on your input DB and transformation requirement. The Samples directory has bdt.policy samples with explanatory comments. Refer to Setting up BDT Policy for details.

BDT supports transforming a database table and copying the data to an output file. To do so:

1. Specify the destination type as CSV_FILE or FIXED_LENGTH_FILE in the policy file.

2. Specify needed attributes of the input file, such as delimiter, encoding, and so on.

   • If more than one table is transformed in a single execution, BDT creates a separate output file for each table.

   • The source table name is suffixed in the output file.

3. You can specify the Transformation Operations that needs to be performed.

Note

If you do not specify any of these actions for a column, the contents of the column are copied to the output file without any transformation.

Listing Source Tables and Columns

Source connection properties are picked from the policy file source attribute.

• Run ./bdt.sh -ls table -p policyfilepath to list source tables.

• Run ./bdt.sh -ls column -table test1 -p policyfilepath to list the columns of source table test1

Sample DB-to-File Transformation Policy

Sample DBToFileTransformation.policy for reference.

Create bdt.policy based on your input file and transformation requirement. The Samples directory has bdt.policy samples with explanatory comments. Refer to Setting up BDT Policy for details.

To transform an input file and copy data to a database table:

1. In the policy file, specify the source type as @CSV_FILE or @FIXED_LENGTH_FILE. You can also add text qualifier characters in input files while setting up the BDT policy.

2. Set the destination DB connection properties in the policy destination attribute. The destination table schema should have all columns specified by the input file header row. If a header row is not present in the input file, columns are named as COLUMN0, COLUMN1...COLUMNn. Refer to BDT Policy Attributes for the list of all attributes.

3. You can specify the Transformation Operations that needs to be performed.

Note

If you do not specify any of these actions for a column, the contents of the column are copied to the output file without any transformation.

Listing Destination Tables and Columns

Destination connection properties are picked from the policy file destination attribute.

• Run ./bdt.sh -ld table -p policyfilepath to list destination tables.

• Run ./bdt.sh -ld column -table test1 -p policyfilepath to list columns of destination table test1

Sample File-to-DB Transformation Policy

Sample FileToDBTransformation.policy for reference.

Create bdt.policy based on your input file and transformation requirement. The Samples directory has bdt.policy samples with explanatory comments. Refer to Setting up BDT Policy for details.

• You can also add text qualifier characters in input files while setting up BDT policy for file-to-file transformation.

• You can specify the Transformation Operations that needs to be performed.

Note

If you do not specify any of these actions for a column, the contents of the column are copied to the output file without any transformation.

Sample File-to-File Transformation policy

Refer to sample File Transformation Policy for reference.

Run ./bdt.sh -t [-c configfilepath] -p policyfilepath for transforming files.

Run bdt.bat -t [-c configfilepath] -p policyfilepath for transforming files.

Verify the output in the column mentioned in the policy file.

• The output file is generated in the output directory specified in bdt.config. Check the output file name with the current timestamp appended.

• The same output directory can also contain a file named <policy_name-timestamp>.failed, which records any utility that has failed to transform. (The policy_name is defined with the name attribute in the policy file.)

Verify the output in the column mentioned in the policy file.

• The output file is generated in the output directory specified in bdt.config. Check the output file name with the current timestamp appended.

• The same output directory can also contain a file named <policy_name-timestamp>.failed, which records any utility that has failed to transform. (The policy_name is defined with the name attribute in the policy file.)