Teradata SQL

First time creating a connector? Read this first.

Ingest your files into Unstructured from Teradata SQL.

Requirements

You will need:

• A Teradata system that can be accessed by its host name or IP address.

  For example, a Teradata system in Teradata Trial includes:

  • A Teradata Trial account.
  • An environment in the account.
  • A Teradata instance in the environment.
  • The name and password for a Teradata user who has the appropriate access to the database.

  Learn how to create these in Teradata Trial.

• The system’s corresponding host name or IP address.

  For example, you can get these values from Teradata Trial as follows:

  1. Sign in to your Teradata Trial account.
     
  2. On the sidebar, under Environments, click the name of the database’s corresponding environment.
     
  3. Use the Host value.
     

• The name of the target database in the system. To get a list of available databases in the system, you can run a Teradata SQL query such as the following:

  SELECT DatabaseName 
  FROM DBC.DatabasesV 
  ORDER BY DatabaseName;
  

• The name of the target table in the database. To get a list of available tables in a database, you can run a Teradata SQL query such as the following, replacing <database-name> with the name of the target database:

  SELECT TableName
  FROM DBC.TablesV
  WHERE DatabaseName = '<database-name>' AND TableKind = 'T'
  ORDER BY TableName;
  

  When Unstructured writes rows to a table, the table’s columns must have a schema that is compatible with Unstructured. Unstructured cannot provide a schema that is guaranteed to work for everyone in all circumstances. This is because these schemas will vary based on your source files’ types; how you want Unstructured to partition, chunk, and generate embeddings; any custom post-processing code that you run; and other factors.

  In any case, note the following about table schemas:

  • The following columns are always required by Unstructured: record_id and element_id.

  • The following columns are optional for Unstructured, but highly recommended: text and type.

  • The rest of the columns are optional and typically will be output by Unstructured as part of the metadata field.

  • If Unstructured is generating vector embeddings, the embeddings column is also required.

    The destination connector outputs Unstructured-generated embeddings that are not directly compatible with Teradata Enterprise Vector Store (EVS). To use embeddings with Teradata EVS, Unstructured recommends that you choose from among the following options:

    • Define a column in your target table named embeddings that is of type VARCHAR(64000), to store the Unstructured-generated embeddings. After Unstructured adds its embeddings to your embeddings column, choose from among Teradata’s options to convert the embeddings column’s VARCHAR values to the Teradata VECTOR Data Type yourself.
    • Omit any columns named embedding, message, or num_tokens from your target table. Then choose from among Teradata’s options (such as AI_TextEmbeddings) to have Teradata generate the embeddings for you, instead of having Unstructured generate them.

  Here is an example table schema that is compatible with Unstructured. It includes all of the required and recommended columns, as well as a few additional columns that are typically output by Unstructured as part of the metadata field. Be sure to replace <database-name> with the name of the target database and <table-name> with the name of the target table (by Unstructured convention, the table name is typically elements, but this is not a requirement).

  CREATE SET TABLE "<database-name>"."<table-name>" (
    "id" VARCHAR(64) NOT NULL,
    PRIMARY KEY ("id"),
    "record_id" VARCHAR(64),
    "element_id" VARCHAR(64),
    "text" VARCHAR(32000) CHARACTER SET UNICODE,
    "type" VARCHAR(50),
    "embeddings" VARCHAR(64000), -- Add this column only if Unstructured is generating vector embeddings.
    "last_modified" VARCHAR(50),
    "languages" VARCHAR(200),
    "file_directory" VARCHAR(500),
    "filename" VARCHAR(255),
    "filetype" VARCHAR(50),
    "record_locator" VARCHAR(1000),
    "date_created" VARCHAR(50),
    "date_modified" VARCHAR(50),
    "date_processed" VARCHAR(50),
    "permissions_data" VARCHAR(1000),
    "filesize_bytes" INTEGER,
    "parent_id" VARCHAR(64)
  )
  

• For the source connector, the name of the primary key column in the table (for example, a column named id, typically defined as "id" VARCHAR(64) NOT NULL, PRIMARY KEY ("id")).

• For the source connector, the names of any specific columns to fetch from the table. By default, all columns are fetched unless otherwise specified.

• For the destination connector, the name of the column in the table that uniquely identifies each record for Unstructured to perform any necessary record updates. By default convention, Unstructured expects this field to be named record_id.

• The name of the Teradata user who has the appropriate access to the target database.

  For example, you can get this from Teradata Trial as follows:

  1. Sign in to your Teradata Trial account.
     
  2. On the sidebar, under Environments, click the name of the database’s corresponding environment.
     
  3. Use the Username value.
     

  The Teradata SQL command to get a list of available users is as follows:

  SELECT UserName 
  FROM DBC.UsersV 
  ORDER BY UserName;
  

• The password for the user, which was set up when the user was created.

  If the user has forgotten their password, the Teradata SQL command to change a user’s password is as follows, replacing <user-name> with the name of the user and <new-password> with the new password:

  MODIFY USER <user-name> SET PASSWORD = '<new-password>';
  

  To change a user’s password, you must be an administrator (such as the DBC user or another user with DROP USER privileges).

The record_id, element_id, and id fields are closely related, but each has a distinct purpose. For more information, see How connectors use record IDs, element IDs, and IDs.

Examples

To create a Teradata SQL source connector, see the following examples.

For more information on working with source connectors using the Unstructured API, see Source endpoints.

Python SDK

import os

from unstructured_client import UnstructuredClient
from unstructured_client.models.operations import CreateSourceRequest
from unstructured_client.models.shared import CreateSourceConnector

with UnstructuredClient(api_key_auth=os.getenv("UNSTRUCTURED_API_KEY")) as client:
    response = client.sources.create_source(
        request=CreateSourceRequest(
            create_source_connector=CreateSourceConnector(
                name="<name>",
                type="teradata",
                config={
                    "host": "<host>",
                    "database": "<database>",
                    "table_name": "<table-name>",
                    "batch_size": <batch-size>,
                    "id_column": "<id-column>",
                    "fields": [
                        "<column-name>",
                        "<column-name>"
                    ],
                    "user": "<user>",
                    "password": "<password>"
                }
            )
        )
    )

    print(response.source_connector_information)

curl --request 'POST' --location \
"$UNSTRUCTURED_API_URL/sources" \
--header 'accept: application/json' \
--header "unstructured-api-key: $UNSTRUCTURED_API_KEY" \
--header 'content-type: application/json' \
--data \
'{
    "name": "<name>",
    "type": "teradata",
    "config": {
        "host": "<host>",
        "database": "<database>",
        "table_name": "<table-name>",
        "batch_size": <batch-size>,
        "id_column": "<id-column>",
        "fields": [
            "<column-name>",
            "<column-name>"
        ],
        "user": "<user>",
        "password": "<password>"
    }
}'

Configuration settings

Replace the preceding placeholders as follows:

name · string · required

A unique name for this connector.

host · string · required

The hostname or IP address associated with the target Teradata instance.

database · string

The name of the target database. By default, the default database name is used if not otherwise specified. To get the name of the default database, you can run the Teradata SQL command SELECT DATABASE;.

table_name · string · required

The name of the target table in the database.

batch_size · integer · default: "100"

The maximum number of rows per batch.

id_column · string

Source connector only. Required. The name of the primary key column that Teradata uses to uniquely identify each record in the table.

record_id_key · string · default: "record_id"

Destination connector only. The name of the column that Unstructured uses to uniquely identify each record in the table for record update purposes.

fields · string

Source connector only. An array of column names to fetch from the table. By default, all columns are fetched unless otherwise specified.

user · string · required

The name of the user who has the appropriate access to the database.

password · string · required

The password for the user.