Copy Datasets and Schemas | Teradata Data Mover - 17.10 - About Copying Datasets and Schemas - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.10
Release Date
June 2021
Content Type
User Guide
Publication ID
B035-4101-061K
Language
English (United States)
Data Mover supports copying objects associated with the Teradata Database DATASET type. The Teradata DATASET type is a complex data type (CDT) representing self-describing files that are interpreted based on a schema. DATASET data types where the underlying storage format is AVRO or CSV are supported. Moving AVRO and CSV schemas is also supported. A schema could be created with the following statements:
CREATE AVRO SCHEMA SYSUDTLIB.testSchema AS '{
      "namespace": "example.avro",
      "type": "record",
      "name": "User",
      "fields": [
            {"name": "name", "type": "string"},
            {"name": "favorite_number",  "type": ["int", "null"]},
            {"name": "favorite_color", "type": ["string", "null"]}
       ]

      }';
After creation, the schema definition can be retrieved by executing a show schema-type query on the schema.
SHOW AVRO SCHEMA testSchema

Copying Schemas Using Data Mover

Data Mover copies schemas as follows:
  • Data Mover retrieves the schema creation DDL by executing a show schema-type sysdtlib.schemaname SQL statement.
  • Data Mover does not parse a create schema DDL; the exact same DDL is run on the target system. Data Mover does not relocate or rename schema objects.
  • If the object being copied does not exist on the target database, it is created on the target database. If the object does exist on the target database, the object is overwritten – unless the overwrite_existing_objects property is set to false. In that case, a create time error is generated.
Schemas are not overwritten if columns on the target system reference the schema.
  • If copying a table with a DATASET column that references a schema, the schema must already exist on the target system or be copied with the job. Schemas are created in the SYSUDTLIB database.

Copying Datasets Using Data Mover

Query the column values to determine if the necessary information is provided to move the column as a DATASET column:
  • The maximum length and DT type code are included in dataset type columns.
  • The storage format column contains the following value when the column is a DATASET data type:
    "Avro": DATASET stored as Avro
  • A column entitled datasetSchemaName containing the name of the schema is associated with a particular column. If no schema is associated with a particular column, this field has a NULL value.
A dataset could be copied using the following command:
CREATE TABLE hcExampleTable(
      id INTEGER,
      avroFile DATASET(100) STORAGE FORMAT Avro WITH SCHEMA chemDatasetSchema
 
);