External table option summary
When you create an external table definition, you can specify options that control processing for records or rows, for fields, and for the load operation itself. Use these options when loading from an external table or when using the external table directly in an SQL query.
Tip: The best way to verify that load processing is successful is to check for errors in
the nzlog and nzbad files. Check these files occasionally
during and after the load operation.
The following table lists the external table options, their values, and the data types. The
topics after the table describe each option. In the "Valid formats" column, "Text" indicates the
text-delimited format, and "Fixed" indicates the fixed-length format. In the "Data type" column,
"enumeration" indicates that the system accepts a specified set of quoted or unquoted string values.
Options and their values are not case sensitive.
| Option | Valid formats | Values | Default | Supported for unload | Data type |
|---|---|---|---|---|---|
| BoolStyle | Text, Fixed | 1_0, T_F, Y_N, YES_NO, TRUE_FALSE | 1_0 | Y | enumeration |
| Compress | Text, Fixed |
|
False | Y |
|
| CRinString | Text, Fixed | True, False, On, Off | False | Y | boolean |
| CtrlChars | Text, Fixed | True, False, On, Off | False | N | boolean |
| DataObject | Text, Fixed | Existing file path | No default | Y | file name |
| DateDelim | Text, Fixed | 1 byte string | '-', ' ' | Y | string |
| DateStyle | Text, Fixed | YMD, DMY, MDY, MONDY, DMONY, Y2MD, DMY2, MDY2, MONDY2, DMONY2 | YMD | Y | enumeration |
| DateTimeDelim | Text, Fixed | 1 byte string | ' ' | Y | string |
| DecimalDelim | Text, Fixed | ',' or '.' | '.' | Y | string |
| Delimiter | Text | 1 byte | '|', '\t' | Y | string |
| Encoding | Text | 'Internal', 'Latin9', 'Utf8', 'Utf-8' | 'Internal' | Y | enumeration |
| EscapeChar | Text | '\' | NULL | Y | string |
| FillRecord | Text | True if you specify the option with no value; false if you do not specify the option | False | N | boolean |
| Format | Text, Fixed | 'Text', 'Internal', 'Fixed' | 'Text' | Y | enumeration |
| IgnoreZero | Text | True, False | False | N | boolean |
| IncludeHeader | Text | True, False, NULL | False if you do not specify the option; True if you specify the option with no value | N | boolean |
| IncludeZeroSeconds | Text | True, False, NULL | False if you do not specify the option; True if you specify the option with no value | Y | boolean |
| Layout | Text, Fixed | Zone definitions | No default | N | none |
| LFinString | Text | True, False | False | N | boolean |
| LogDir | Text, Fixed | existing directory path | '/tmp' | N | string |
| MaxErrors | Text, Fixed | 0 - 2,147,483,647 | 1 | N | integer |
| MaxRows | Text, Fixed | >=0 | 0 | N | integer |
| MeridianDelim | Text, Fixed | 1 byte string | ' ' | Y | string |
| NullValue | Text, Fixed | 4-byte string | 'NULL' | Y | string |
| QuotedValue | Text | No, Yes, Single, Double | No | N | enumeration |
| RecordDelim | Text, Fixed | Maximum 8-byte string |
UNIX OS: '\n' Windows OS: '\r\n' or '\n\r' |
N | string |
| RecordLength | Fixed | Integer constant | NULL | N | integer |
| RemoteSource | Text, Fixed | ODBC, JDBC, OLE-DB, S3, AZURE, NZSQL, YES | NULL | Y | enumeration |
| RequireQuotes | Text | True, False, NULL | False if you do not specify the option; True if you specify the option with no value | N | boolean |
| SkipRows | Text, Fixed | >=0 | 0 | N | bigint |
| SocketBufSize | Text, Fixed | 64 KB - 2 GB | 8 MB | Y | integer |
| TimeDelim | Text, Fixed | 1 byte character | ':' | Y | string |
| TimeRoundNanos or TimeExtraZeros | Text | True, False, NULL | False if you do not specify the option; True if you specify the option with no value | N | boolean |
| TimeStyle | Text, Fixed | '24hour', '12hour' | '24hour' | Y | enumeration |
| TruncString | Text | True, False, NULL | False if you do not specify the option; True if you specify the option with no value | N | boolean |
| Y2Base | Text, Fixed | >=0 | N | integer | |
| UniqueId | Text | Namespace used to group data | No default | Y | string |
| AccessKeyId | Text | Key generated on AWS/IBM COS | No default | Y | string |
| SecretAccessKey | Text | Secret access key generated on AWS/IBM COS | No default | Y | string |
| DefaultRegion | Text | Name of the bucket | No default | Y | string |
| BucketUrl | Text | Name of the bucket | No default | Y | string |
| MultipartSizeMb | Text, Fixed | 8MB - 5GB | 105MB | Y | string |
| Endpoint | Text | Region URL | No default | Y | string |
| AzAccount | Text | Azure storage account name | No default | Y | string |
| AzKey | Text | Azure access key | No default | Y | string |
| AzContainer | Text | Name of the Azure container | No default | Y | string |
| AzMaxBlocks | Text | Maximum number of blocks in a block blob | 10000 | Y | integer |
| AzBlockSizeMb | Text | < 100MB | 25MB | Y | integer |
| AzLogLevel | Text | Log level to control logging | INFO | Y | string |