Loading JSON Data Using Load Utilities - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
gzn1554761068186.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantage™

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 Vantage 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.

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