USING Options

The following options are available in the USING clause when you run:

SELECT FROM EXTERNAL queries
CREATE EXTERNAL TABLE commands

Some of these options apply only to SELECT FROM EXTERNAL or CREATE EXTERNAL TABLE operations, as stated in the descriptions. See also External Table Examples.

Note: When you select from external tables, a background ybload operation is run to read the data. When you create external tables, a background ybunload operation is run to write the data. You can find more information about some of the USING options by consulting the equivalent ybload and ybunload sections.

Options are listed in alphabetical order for quick reference.
Option names are shown in lowercase, but they are not case-sensitive.
Variables for option values, such as CHARACTER, are shown in uppercase.
Option values that are keywords (such as true and false) are shown in lowercase. These keywords are not case-sensitive.
Option values that are string literals (such as a path to a directory in logdir PATH) are case-sensitive and should be protected with quotes when specified. For example: logdir '/ext_db/QueryLogs'
Quoted option values follow standard SQL quoting rules.


Option name	Accepted values	Default value	Data type	Description
`boolstyle`	`1_0, T_F, Y_N, YES_NO, TRUE_FALSE`	`1_0`	String	Specify which pair of values to use for Boolean data types in unloaded data: `1` or `0`, `T` or `F`, `Y` or `N`, `YES` or `NO`, `TRUE` or `FALSE`. This option is ignored when you select from external tables; `ybload` automatically supports all of these styles.
`compress`	`true, false, TYPE`	Auto-detected (based on file suffix)	Boolean	Define the compression type for input and output data. If this option is specified without a value or with `true`, `GZIP` compression is applied for `SELECT` operations. If this option is not included or is specified with `false`, compression is auto-detected based on the file suffix for `SELECT` operations. You can explicitly specify the compression type, but the supported type values differ for `ybload` and `ybunload`; see the `ybload --source-compression` and `ybunload --compress` options.
`datedelim`	`CHARACTER`	Auto-detected	String	Specify the delimiter for datetime values. Equivalent to `delim` in `ybload --date-field-options`.
`datestyle`	`FORMAT`	`YMD`	String	Specify the format for datetime values. See ybload Date Formats. Any of the supported date styles, such as `'YMD'` or `'DMY'`, are accepted. Only one date style can be specified in the `USING` clause.
`delim` or `delimiter`	`SPECIAL_CHARACTER`	Auto-detected	String	Specify the field delimiter (a column separator, equivalent to the `--delimiter` option in `ybload`). See also Character Escape Sequences.
`encoding`	`ENCODING_NAME`	Server encoding	String	Equivalent to `ybload --encoding`. This option does not apply to `CREATE EXTERNAL TABLE` operations.
`escapechar`	`SPECIAL_CHARACTER`	Depends on the `format` option	String	Use this character as the escape character. Equivalent to the `--escape-char` option in `ybload`.
`fillrecord`	`true`, `false`	`false` (`ERROR`)	Boolean	Define the behavior when fields are missing from source files: supply a `null` value or return an error. `fillrecord true` is equivalent to `ybload --on-missing-field SUPPLYNULL`. This option does not apply to `CREATE EXTERNAL TABLE` operations.
`format`	`csv`, `text`, `bcp`	`csv`	String	Define the format of the data being read or written: `CSV`, `TEXT`, or `BCP` (see the `ybload --format` option for details). If this option is not specified, the default is `CSV`. If this option is specified without a value or with an empty string, the command returns an error. This option is required for `CREATE EXTERNAL TABLE` operations.
`ignorezero`	`true`, `false`	`false` (`ERROR`)	Boolean	Remove null bytes (`0x00` characters) or return an error when they appear within strings in `CHAR` and `VARCHAR` fields. `ignorezero true` is equivalent to `ybload --on-zero-char REMOVE`. This option does not apply to `CREATE EXTERNAL TABLE` operations.
`includeheader`	`true`, `false`	`false`	Boolean	`TRUE`: Write a single header line at the beginning of each output file. `FALSE`: Do not include a header line (the default behavior). This option applies to `CREATE EXTERNAL TABLE` operations only.
`logdir`	`PATH`	Same location as external files	String	Set a path to a writable log directory, relative to an active NFS mount point (see CREATE EXTERNAL MOUNT). For example: `logdir '/qumulo/external_tables/select_from_external_logs/'` If this option is not specified, log files and any bad row files are written to the same directory as the data file. You cannot use a path to a read-only NFS mount for the log directory.
`maxerrors`	`NUMBER`	0	Integer	Set the maximum number of bad rows to tolerate. Equivalent to `ybload --max-bad-rows NUMBER`. Note: `maxerrors 32` means 32 bad rows are allowed; the load will fail on the 33rd bad row. This option does not apply to `CREATE EXTERNAL TABLE` operations.
`notruncate`	`true`, `false`	`false`	Boolean	Preserve or overwrite external table files. `TRUE`: if an external table file exists with the specified name, do not overwrite the file; return an error. `FALSE` is the default, which allows existing files to be overwritten. This option applies to `CREATE EXTERNAL TABLE` operations only.
`nullvalue`	`STRING`	`NULL`	String	Specify the string that will represent `NULL` in the result set (equivalent to the `--nullmarker` option).
`quotedvalue`	`yes`, `no`, `single`, `double`, `SPECIAL_CHARACTER`	"	String	Use this character as the quote character when the format is `CSV`. Equivalent to the `--quote-char` and `--escape-char` options in `ybload`. Note: The values `yes` and `single` specify a single quote (`'`). The values `no` and `double` specify a double quote (`"`), which is the same as the default behavior when this option is not specified.
`recorddelim`	`SPECIAL_CHARACTER`	Auto-detected	String	Define the record delimiter (or row separator) that is used in external source files. Equivalent to `ybload --linesep`. This option does not apply to `CREATE EXTERNAL TABLE` operations.
`skiprows`	`NUMBER`	0	Integer	Skip the specified number of rows when reading data in from the external file. This option is useful when the file contains one or more header lines. This option does not apply to `CREATE EXTERNAL TABLE` operations.
`timedelim`	`CHARACTER`	:	String	Specify the delimiter character for the time components of a datetime value. The default is the colon character (`:`). This option must be specified in combination with `timestyle`. Equivalent to `delim` in `ybload --time-field-options` and `--timestamp-field-options`. (See ybload Field Options.)
`timestyle`	`'24HOUR'` , `'12HOUR'`	`'24HOUR'`	String	Specify the time format used in the data file. The default is`'24HOUR'`. This option is ignored when you select from external tables; `ybload` automatically supports both formats.
`truncstring`	`true`, `false`	`false`	Boolean	Truncate strings that are longer than the specified column width or return an error. Equivalent to `ybload --on-string-too-long`. `true` is equivalent to `truncate`; `false` is equivalent to `error`. This option does not apply to `CREATE EXTERNAL TABLE` operations.
`y2base`	`YEAR` (`0-9999`)	No default	integer	Equivalent to the `ybload --y2base` option. This option does not apply to `CREATE EXTERNAL TABLE` operations.