Skip to main content

Teradata Bulk Connections

The Teradata Bulk Connection is used for reading and writing large volumes of data at a high speed to an empty table on a Teradata Database via the Input Data tool and Output Data tool. Besides being empty, the target table cannot have defined any secondary indexes. The user must have SELECT and INSERT privileges on the target table, SELECT and INSERT privileges on the error tables, and DROP privileges on the database containing the error tables.

The following Teradata Tools and Utilities must be installed on the computer (the latest version is 14.0, which is the recommended version of these tools).

  • Shared ICU Libraries

  • ODBC Driver for Teradata

  • Teradata GSS Client

  • Teradata Parallel Transporter Base

For more information about Teradata, see their website at: www.teradata.com

Use the Output Data tool to write to a Teradata bulk connection

  1. Output Data Source: Use the drop-down to select Other Databases > Teradata Bulk.

  2. Teradata Data Source Name: Use the drop-down list of the available ODBC connections set up on the computer.

  3. User Name: Specify the User name credential for connection to Teradata

  4. Password: Specify the Password credential for connection to Teradata

  5. Configure connection options:

    • LDAP Authentication: If the Teradata server uses LDAP for authentication, you must select this option. You must also select LDAP as the DSN authentication mechanism in the ODBC driver configuration. (LDAP must either be selected in both locations or de-selected in both locations).

    • Enable Data Encryption: Select this option to encrypt data during transfer. You must separately enable DSN encryption within the ODBC driver options (i.e., for creating and dropping tables).

  6. Configure Output Data tool Options:

    1. Max Records per file: specify the limit of records to write. Leave blank for unlimited.

    2. File Format: Teradata Bulk Loader format.

    3. Passwords: Use the drop-down list to specify how to handle password display in the Configuration window. Choices include:

      • Hide (Default)

      • Encrypt for Machine

      • Encrypt for user

    4. Output Options: Use the dropdown to specify how the data should be written to Teradata. Choices include:

      • Create New Table: Writes data to a new Table. Will not overwrite an existing table.

      • Overwrite Table (Drop): Completely drops the existing table, and creates a new one.

      • Append (Ignore Duplicate Rows): Adds data into an existing table while ignoring duplicate records.

      • Append (Mark duplicate Rows): duplicate rows go into TableName_e2

      • Delete & Append: Deletes all the original records from the table and then appends the data into the existing table.

    5. Error Limit: This setting will terminate the writing of records should the number of errors exceed this limit. Any records already written will remain in the table.

    6. Alternate Name for Log and Error Tables: The Teradata Bulk Loader uses the Teradata Parallel Transporter API to write the data. The API automatically creates 3 tables. These tables will default to the Table Name if the number of characters in the name is less than 27. If the number of characters is greater than 27, you will need to enter an alternate table name for these tables.

    7. Pre Create SQL Statement: a SQL statement that will be executed via OleDb BEFORE the output table is created. Advanced knowledge of SQL language required.

    8. Post Create SQL Statement: a SQL statement that will be executed via OleDb AFTER the output table is created. Advanced knowledge of SQL language required.

    9. Table/Field Name Style: Choices include Quoted or None. Quoted will look up Quote Identifier for the database type.

The tables created by the Teradata Bulk Loader are “tablename_lg”, “tablename_e1” and “tablename_e2”. (where tablename is the name you entered in 6 or 7) Should errors occur during the writing of records these tables will have the information about these failures. Alteryx will automatically remove these working tables, on Delete Data & Append, and Overwrite Table (Drop).

Use the Input Data tool to read files through a Teradata bulk connection

  1. Input Data Source: Use the dropdown to select Other Databases > Teradata Bulk.

  2. Teradata Data Source Name: Use the dropdown list of the available ODBC connections set up on the computer.

  3. User Name: Specify the User name credential for connection to Teradata

  4. Password: Specify the Password credential for connection to Teradata

  5. Configure connection options:

    • LDAP Authentication: If the Teradata server uses LDAP for authentication, you must select this option. You must also select LDAP as the DSN authentication mechanism in the ODBC driver configuration. (LDAP must either be selected in both locations or de-selected in both locations).

    • Enable Data Encryption: Select this option to encrypt data during transfer. You must separately enable DSN encryption within the ODBC driver options (i.e., for creating and dropping tables).

  6. Configure Input Data Tool Options:

    1. Record Limit: enter the number to limit the records read from the input data source. This can be useful for testing purposes and trial runs. Leave this blank to read all records.

    2. File Format: Teradata Bulk Loader format.

    3. Passwords: use the drop down list to specify how to handle password display in the Configuration window. Choices include:

      • Hide (Default)

      • Encrypt for Machine

      • Encrypt for user

    4. Table or Query: Specify the target table name into which records will be read from. Click

      for a list of tables.

    5. Read Unspooled: Teradata setting - Specifies whether to use spool or not while running the current Export job.

    6. Do not show % Complete: When checked, Alteryx will not try to report the status of reading in the file, thus speeding up the read time.

    7. Pre Create SQL Statement: a SQL statement that will be executed via OleDb BEFORE the table is read. Advanced knowledge of SQL language required.

    8. Post Create SQL Statement: a SQL statement that will be executed via OleDb AFTER the table is read. Advanced knowledge of SQL language required.

    9. Table/Field Name Style: Choices include Quoted or None. Quoted will look up Quote Identifier for the database type.

No spool mode

Specifies whether to use spool or not while running the current Export job.

Valid values

  • “Spool” = Use Spool. This is the default setting.

  • “NoSpool” = Do not use Spool. This value is valid only if DBS supports NoSpool. If DBS does not support NoSpool, it uses Spool instead.

  • “NoSpoolOnly” = Do not use Spool in any case. If the DBS does not support NoSpool, it terminates the job with an error.

Spooling options

The NoSpool mode exports the contents of a table as fast as possible without reading the table into a spool file or distributing the file to all AMPs before extracting it.

  • (Default) SPOOL the data.

  • Use the NOSPOOLONLY mode, but return an error if NOSPOOL is not supported.

  • Use the NOSPOOL mode when possible; otherwise spool the data in Teradata Database.

Limitations and functionality

  • NOSPOOL mode applies only to simple SELECT statements. The following are not supported:

  • Access to nondata tables, such as SELECT DATE or SELECT USER

  • USING modifier; instead, define restraint parameters by using a FastExport IMPORT command with supporting FIELD and FILLER commands

  • Contains a SORT (ORDER BY), HAVING, or WITH clauses

  • Joins

  • Aggregations (Explain shows SUM step)

  • TABLE functions

  • Ordered-analytic (OLAP) functions

  • Multiple SELECT statements or multistatement requests

  • Statements with zero or more than one, retrieve or sampling step

  • NOSPOOL mode only retrieves data from a single table, but the SELECT statement can be selective about which columns are exported and can constrain the job to a subset of rows.

  • Scalar expressions/functions are allowed.

  • The Sample and partition eliminating constraints are supported.

  • The Activity Count returned for a regular spooled job indicates the number of affected blocks; however, for non-spooled jobs, the number of blocks is unknown, so the response message contains ActivityType (instead of Activity Count) to indicate the NOSPOOL process.

Disadvantages of the NOSPOOL mode

  • Locks are maintained during the entire export process.

  • Data conversion errors previously detected during the spooling phase will not be detected until the block is read, which could occur during any time during the export.

  • Row order (because of the absence of the ORDER BY clause) may or may not be consistent between runs; therefore, NOSPOOL mode offers no guarantee of consistency.

See information on Spool/NoSpool mode, Teradata FastExport Reference.

In this section: