Onboarding Data from Teradata TPT
Infoworks supports ingesting data from Teradata via Teradata Parallel Transporter (TPT). TPT is a native tool provided by Teradata. Ingestion via TPT helps in reducing load (AmpCPU) on the source Teradata system (5x -10x reduced source load with TPT) and provides better performance over JDBC. TPT is the preferred load/unload tool for Teradata Database. |
Prerequisite
Ensure to install TPT on the master node of the interactive cluster, along with the cloud specific access module using the TPT installer.
Perform the following steps to install TPT on the edge node, interactive cluster, and persistent cluster as applicable.
- Download the Teradata Tools and Utilities (TTU) installer package for the required OS.
- Extract the installer package to a directory.
- Navigate to the extracted directory.
- Execute the following command:
sudo ./setup.bat a
For more information on TPT installation steps, refer to Teradata Tools and Utilities Installation.
Creating a Teradata TPT Source
For creating a Teradata TPT source, see Onboarding a RDBMS Source. Ensure that the Source Type selected is Teradata.
Teradata Configurations
| Field | Description |
|---|---|
| Fetch Data Using | The mechanism through which Infoworks fetches data from the database. |
| Connection URL | The connection URL through which Infoworks connects to the database. The URL must be in the following format: jdbc:teradata://<ip>/TMODE=<tmode>,database=<databasename> |
| Username | The username for the connection to the database. |
| Authentication Type for Password | Select the authentication type from the dropdown. For example, Infoworks Managed or External Secret Store. If you select Infoworks Managed, then provide Authentication Password for Password. If you select External Secret Store, then select the Secret which contains the password. |
| Source Schema | The schema in the database to be crawled. The schema value is case sensitive. |
| Enable Ingestion via Teradata Parallel Transporter (TPT) | Check the box to enable ingestion via TPT. This option cannot be disabled, if any of the tables is configured for TPT(or TPT without IW Processing ). In order to disable this option, you will need to change the "Ingest Using" field setting to JDBC. |
| Cloud Type | The options include Azure Blob Storage (WASB), ADLS Gen2, Amazon S3, and GCS. The cloud type will be displayed based on the environment. If the data environment is Azure, select WASB or ADLS Gen2 from the Cloud Type dropdown. |
| Custom Tags | This dropdown provides the list of tags which you can choose. It can be used to identify/segregate the source based on the selected tags. |
For ADLS Gen2, enter the following:
| Field | Description |
|---|---|
| Access scheme | Scheme used to access ADLS Gen2. The available options are abfs:// and abfss:// |
| Authentication mechanism | The mechanism using which the security information is stored. The options include Access Key and Service Principal. Access key refers to the key to authenticate applications when making requests to this Azure storage account. Service Principal functions as the identity of the application instance. |
| FileSystem | Provide a file system where all the data of an artifact will be stored. |
| Storage account name | Provide the name of Azure storage account. |
| Access Key | Provide the storage access key to connect to ADLS. NOTE This field appears only when the authentication mechanism is selected as Access Key. |
| Application ID | Provide the ID that uniquely identifies the user application. NOTE This field appears only when the authentication mechanism is selected as Service Principal. |
| Directory ID | Provide the ID that uniquely identifies the Azure AD instance. NOTE This field appears only when the authentication mechanism is selected as Service Principal. |
| Service Credential | Provide the credential string value that the application uses to prove its identity. NOTE This field appears only when the authentication mechanism is selected as Service Principal. |
Windows Azure Storage Blob (WASB), also known as Blob Storage, is a file system for storing large amounts of unstructured object data, such as text or binary data. For more details, see Azure Blob Storage Documentation.
For Azure Blob Storage (WASB), enter the following:
| Field | Description |
|---|---|
| Storage Account name | The unique name of the storage account. For more details, see Create a storage account. |
| Authentication Type for Storage Account Access Key | The storage account access key. This option is displayed if the Authentication Mechanism used is Account Key. For more details, see Manage storage account access keys. If you select Infoworks Managed from the Authentication Type for Storage Account Access Key dropdown, then provide Authentication Password for Storage Account Access Key. If you select External Secret Store, select the secret from Secret for Storage Account Access Key dropdown. |
| Storage Account End Point | The account end point of WASB storage.The default value is: blob.core.windows.net. If the staging cloud storage is in one of the Azure Government Cloud regions, use the following end point: blob.core.usgovcloudapi.net |
| Container name | The name of the container in the storage account, in which the files are stored. A container organizes a set of blobs, similar to a directory in a file system. For more details, see Create a container. |
Amazon Simple Storage Service (S3) is storage for the Internet. Amazon S3 has a simple web services interface that you can use to store and retrieve any amount of data, at any time, from anywhere on the web. For more details, see Amazon S3 Documentation.
For Amazon S3, enter the following:
| Field | Description |
|---|---|
| Authentication Mechanism | The mechanism using which the security information is stored. |
| Access ID | The access ID uniquely identifies an AWS account. You can use the access ID to send authenticated requests to Amazon S3. The Access ID is a 20-character, alphanumeric string, for example, AKIAIOSFODNN7EXAMPLE. For the access ID details, contact your AWS Cloud admin. |
| Authentication Type for Secret Key | The secret key to send requests using the AWS account. The secret access key is a 40-character string, for example, wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY. _For the secret key details, contact your AWS Cloud admin. For more details, see AWS Account Access Keys. _ __ If you select Infoworks Managed from the Authentication Type for Secret Key dropdown, then provide Authentication Password for Secret Key. If you select External Secret Store, select the secret from Secret for Secret Key dropdown. __ |
| Bucket Name | The name of the container/bucket in the storage account in which the files are stored. The container/bucket organizes a set of objects, similar to a directory in a file system. For more details, see Creating a bucket. |
Google Cloud Storage (GCS) is an online file storage web service for storing and accessing data on Google Cloud Platform infrastructure. In GCS, buckets are the basic containers that hold the data. For more details, see GCS documentation.
For GCS, enter the following:
| Field | Description |
|---|---|
| Project ID | The ID of the project in the source. For more details, see Project ID. |
| Authentication Mechanism | The mechanism using which the security information is stored. The Service Account Credential File option refers to the access key to authenticate applications when making requests to this Azure storage account. For more details, see Creating and managing service account keys. |
| Service Account Credential File Upload | You can upload or enter the location where the service account JSON credential file is available in the edge node. This option is displayed if the Authentication Mechanism selected is Service Account Credential File. |
| Bucket Name | The name of the container/bucket in the storage account in which the files are stored. The container/bucket organizes a set of objects, similar to a directory in a file system. For more details, see Creating storage buckets. |
Enabling TD Wallet
| Field | Description |
|---|---|
| Enable TD Wallet | The option to enable TD Wallet. |
| TD wallet key used for password | The TD Wallet key for current Teradata connection. |
Once the settings are saved, you can test the connection.
Configuring a Teradata Table
| Field | Description |
|---|---|
| Ingest Using | Fetch mechanism to be used for ingestion. The options include JDBC, TPT and TPT without IW processing. You will not be able to change the fetch mechanism if the option selected is "TPT without IW processing" and any of the table is in ready state. However, you can switch between TPT and JDBC even after the table is in ready state. |
| Ingest Type | The type of synchronization for the table. The options include full refresh and incremental. |
| Natural Keys | The combination of keys to uniquely identify the row. This field is mandatory in incremental ingestion tables. It helps in identifying and merging incremental data with the already existing data on target. |
| Incremental Mode | The option to indicate if the incremental data must be appended or merged to the base table. This field is displayed only for incremental ingestion. The options include append and merge. For TPT without IW processing, only append mode is available. |
| Watermark Column | Select single/multiple watermark columns to identify the incremental records. The selected watermark column(s) should be of the same datatype. |
| Ingest subset of data | The option to configure filter conditions to ingest a subset of data. This option is available for all the RDBMS and Generic JDBC sources. For more details, see Filter Query for RDBMS Sources |
| Sorting Columns | Select columns on which the target table will be sorted. |
Target Configuration
Configure the following fields:
| Field | Description |
|---|---|
| Target Table Name | The name of the target table. |
| Catalog Name | The name of the target catalog. |
| Staging Catalog Name | The staging catalog name for temp tables. |
| Staging Schema Name | The staging schema name for temp tables. |
| Schema Name | The schema name of the target table. |
| Storage Format | The format in which the tables must be stored. The options include Read Optimized (Delta), Read Optimized (Parquet), Read Optimized (ORC), Write Optimized (Avro), Universal Format (UniForm) and Delimited text files. |
| Partition Column | The column used to partition the data in target. Selecting the Create Derived Column option allows you to derive a column from the partition column. This option is enabled only if the partition column datatype is date or timestamp. Provide the Derived Column Function and Derived Column Name. Data will be partitioned based on this derived column. |
Optimization Configuration
Configure the following fields:
| Field | Description |
|---|---|
| Split By Column | The column used to crawl the table in parallel with multiple connections to database. Split-by column can be an existing column in the data. Any column for which minimum and maximum values can be computed, can be a split-by key. Select the Create Derived Split Column option and provide the Derived Split Column Function to derive a column from the Split By column. This option is enabled only if the Split By column datatype is date or timestamp. The data will be split based on the derived value. |
| Is Wide Table | The option to be enabled when any insert, update or delete statement in logs can exceed 4000 characters. |
Advanced Configurations
Following are the steps to set advanced configuration for a table:
- Click the Data Catalog menu and click Ingest for the required source.
- Click the Configure Tables tab, click the Advanced Configuration tab and click Add Configuration.
- Enter Key, Value, and Description. You can also select the configuration from the list displayed.
Configure Multiple TPT Data Writers and Readers
To configure multiple data writers and readers for TPT:
- Click Advanced Configuration -> Add Configuration.
- Set the following configuration parameters to any desired integer value in the Add Configuration screen:
- tpt_reader_instances
- tpt_writer_instances
tpt_writer_instances is set to > 1, then tpt_add_directory_separator_to_object should be set to true.
- Click Save to save the configuration changes.
Configure the Default Data Fetch Mechanism
To configure default data fetch mechanism, you must configure the following parameters at the source level or global level.
Click Advanced Configuration -> Add Configuration.
In the Add Configuration screen, enter data_fetch_ type under the Key field.
Add one of the following values in the Value field.
- jdbc
- tpt
- skip_iw_processing
Click Save to save the configuration changes.
Sync Data to Target
Using this option, you can configure the Target connections and sync data as described in the section Synchronizing Data to External Target
The following are the steps to sync data to target.
- From the Data Sources menu, select one of the tables and click View Source/Ingest button.
- Select the source table to be synchronized to Target.
- Click the Sync Data to Target button.
- Enter the mandatory fields as listed in the table below:
| Field | Description |
|---|---|
| Job Name | The name of the ingestion job. |
| Max Parallel Tables | The maximum number of tables that can be crawled at a given instance. |
| Compute Cluster | The template based on which the cluster will spin up for each table.The compute clusters created by admin and are accessible by the user are listed in the drop down. |
| Overwrite Worker Count | The option to override the maximum and minimum number of worker node values as configured in the compute template |
| Number of Worker Nodes | The number of worker nodes that will spin up in the cluster. |
| Save as a Table Group | The option to save the list of tables as a table group. |
Click Onboarding a RDBMS Source to navigate back to complete the onboarding process.