JSON External Files | CREATE FOREIGN TABLE SQL Statement | Teradata Vantage - 17.05 - JSON External Files - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Native Object Store Getting Started Guide

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1214-175K
Language
English (United States)

Each record in the JSON external data must be formatted on a single line, terminated by a new line symbol (\n).

External JSON files can contain individual records or a single JSON array that uses a comma (,) as the record delimiter. For single-array JSON files, the record can span across multiple lines. Optionally, the JSON array can be named.

Vantage can read the following JSON document in external storage:
 {"field1": true,"field2": "somestring","field3": {"field4": 1}}
Vantage cannot read the external JSON data if it is stored in this format, due to the line breaks:
{
    "field1" : true,
    "field2":
         "somestring",
    "field3":
    {
        "field4":1
    }
}
Because the following external JSON data is in a single array (delimited by the opening and closing square bracket characters), using commas to delimit individual records in the array, Vantage can read the data, even with the embedded line breaks:
[{
                "field1": "string1", "field2": "string2"
}, {
                "field1": "string3", "field2": "string4"
}]

The field names are case sensitive. So, a reference to Field1 will not match the "field1" in the examples above.

Spaces inside quotes for the field names are significant. So, if the record contained "field1 ", then a reference to payload.field1 does not match.

External JSON files can be compressed in GZIP format. Other forms of file compression are not supported.