JSON External Files | CREATE FOREIGN TABLE SQL Statement | Teradata Vantage - 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
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zws1595641486108.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1214
lifecycle
previous
Product Category
Software
Teradata Vantage

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.