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

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.10
Published
June 2021
Language
English (United States)
Last Update
2021-06-16
dita:mapPath
fcu1619423186706.ditamap
dita:ditavalPath
mpm1591127278842.ditaval
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem
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
 
);