15.10 - Loading JSON Data Using Load Utilities - Teradata Database

Loading JSON data is the same as loading LOB or XML data, and you use the "JSON" keyword in the load script to define the column type. Like LOB data, you can load in inline mode if the data is less than 64 KB in size (64000 LATIN characters or 32000 UNICODE characters). Otherwise, you must specify the column as "JSON AS DEFERRED BY NAME" (with the data residing in external files), where the client and Teradata Database make multiple calls to move the data.

You can use the following load utilities for loading and unloading JSON data:

• The Teradata Parallel Transporter (TPT) SQL Inserter operator supports the loading of JSON data into a table.
• The TPT SQL Selector operator supports the unloading of JSON data from a table.
• The TPT Data Connector operator supports the writing and reading of JSON data to and from a file.

DEFINE SCHEMA TEST_SCHEMA
DESCRIPTION 'PRODUCT INFORMATION SCHEMA'
(
   COL1_INT      INTEGER,
   COL2_CHAR     CHAR(10),
   COL3_JSON     JSON(1000),
   COL4_BIGINT   BIGINT,
   COL5_JSON     JSON(16776192) AS DEFERRED BY NAME,
   COL6_VARCHAR  VARCHAR(20)
);

USING COL1_INT(INTEGER),
   COL2_CHAR(CHAR(10)),
   COL3_JSON(CLOB(1000)),
   COL4_BIGINT(BIGINT),
   COL5_JSON(CLOB(16776192) AS DEFERRED),
   COL6_VARCHAR(VARCHAR(20))
INSERT INTO target_table
VALUES (:COL1_INT, :COL2_CHAR, :COL3_JSON, :COL4_BIGINT,
        :COL5_JSON, :COL6_VARCHAR);

You cannot load JSON data using FastLoad, MultiLoad, or FastExport protocols using either the legacy stand-alone load tools or Parallel Transporter. However, if the JSON data is less than 64 KB, and if the target table defines the column as CHAR or VARCHAR, then you can load the JSON data using these utilities. Similarly, you can load the JSON data into a staging table with the columns defined as CHAR or VARCHAR, and then use INSERT ... SELECT to load the data into JSON columns of a target table.

For more information about the TPT load utility, see the following books:

• Teradata Parallel Transporter Reference, B035-2436
• Teradata Parallel Transporter User Guide, B035-2445