Loading JSON Data Using Load Utilities

Teradata Vantage™ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

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 (Teradata PT) SQL Inserter operator supports the loading of JSON data into a table.
  • The Teradata PT SQL Selector operator supports the unloading of JSON data from a table.
  • The Teradata PT Data Connector operator supports the writing and reading of JSON data to and from a file.
The following shows how JSON syntax is used in the Teradata PT schema definition:
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)
);
The following shows a sample USING clause generated by the Teradata PT SQL Inserter operator:
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);
For more information about the Teradata PT load utility, see the following documents:
  • Teradata® Parallel Transporter Reference, B035-2436
  • Teradata® Parallel Transporter User Guide, B035-2445
In general, you cannot import or export LOB JSON data using FastLoad, MultiLoad, or FastExport protocols using either the legacy stand-alone load tools or Parallel Transporter. However, you can use these utilities to load or unload JSON data in the following cases:
  • You can load JSON data if it is less than 64 KB, and the target table defines the column as CHAR or VARCHAR.
  • If you use a transform group that converts JSON to/from VARCHAR or VARBYTE.

When loading JSON data using FastLoad or MultiLoad using VARCHAR or VARBYTE transforms, the imported data must fit in the row. If it cannot be stored inline, the input row is put into the error table.

The MLOADX protocol can load LOB JSON data using any transforms without the restriction of the inline length specified for the type.

Example: Loading JSON Data Using a Specified Transform Group

In this example, the transform group settings for the user specify to use the TD_JSON_VARBYTE transform group. The following shows how you can load JSON data into the JSON column of the jsn_byte_bson table.

.logon NODEID/dr171210_vb,dr171210_vb;
 
DROP TABLE Error_11;
DROP TABLE Error_22;
 
CREATE TABLE jsn_byte_bson(
  s1 INTEGER NOT NULL
 ,s2 JSON (1000) STORAGE FORMAT BSON)
UNIQUE PRIMARY INDEX(s1);
 
DEFINE JOB TPT (
  a (INTEGER),
  b (VARBYTE(1000))
FILE=jsn_byte_bson;
 
BEGIN LOADING jsn_byte_bson ErrorFiles Error_11, Error_22 indicators;
 
INSERT INTO jsn_byte_bson VALUES(:a, :b);
END LOADING;
LOGOFF;

For details about the predefined transform groups for the JSON type, see Support for Multiple Transform Groups.