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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
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.