You can optionally specify a payload column for foreign tables that have JSON and CSV data. If you explicitly specify a payload column in your foreign table definition you must also specify a location column.
You cannot specify a payload column for foreign tables that have Parquet data.
If you define a foreign table but do not explicitly specify a payload column, the automatically generated payload column data type depends on the extension of the files in the location path.
| File Extension | Payload Column Type |
|---|---|
| .json or .json.gz | JSON |
| .csv or .csv.gz | DATASET STORAGE FORMAT CSV |
| No file extension or unrecognized file extension | JSON, with unsupported file type warning |
| mixed .json and .csv files at the location path | Based on the type of the first valid file Vantage encounters. Subsequent files from the location that have different extensions are ignored. For this reason, it is best to specify a payload column for a location that has mixed format data, if you cannot arrange to have only one data format at the specified LOCATION. |
- For JSON data:
- Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE
- Payload JSON(16776192) INLINE LENGTH 64000 CHARACTER SET LATIN
- For CSV data:
- Payload DATASET (2097088000) INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET UNICODE
- Payload DATASET (2097088000) INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET LATIN
If the external CSV data files do not include header records specifying column names, you must define a schema object that specifies column names for the CSV field data. (If the CSV file has a header record, and you also associate it with a schema, the header is treated as a regular data row in the foreign table.) For more information about schema objects, see CSV File Headers and Schemas.
In these cases, use a payload column specification that indicates the schema to use for the CSV data:- Payload DATASET (2097088000) INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET UNICODE WITH SCHEMA schema_name
- Payload DATASET (2097088000) INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET LATIN WITH SCHEMA schema_name
- The first record is read as the header.
- Fields are delimited by commas.
- Records are delimited by line feeds (LFs), "\n" or carriage return line feeds (CRLFs), "\r\n".You can only specify line feeds (LFs), "\n", in the schema or the ROWFORMAT clause, not carriage return line feeds (CRLFs), "\r\n".