Product Documentation

Appearance

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 ybloadandybunload 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 nameAccepted valuesDefault valueData typeDescription
boolstyle1_0, T_F, Y_N, YES_NO, TRUE_FALSE1_0StringSpecify 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.
compresstrue, false, TYPEAuto-detected (based on file suffix)BooleanDefine 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](../bulk_loading/ybload_options.md#source-compression) and ybunload [--compress](../unloading/ybunload_options.md#compress) options.

datedelimCHARACTERAuto-detectedStringSpecify the delimiter for datetime values. Equivalent to delim in ybload --date-field-options.
datestyleFORMATYMDStringSpecify 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 delimiterSPECIAL_CHARACTERAuto-detectedStringSpecify the field delimiter (a column separator, equivalent to the --delimiter option in ybload). See also Character Escape Sequences.
encodingENCODING_NAMEServer encodingStringEquivalent to ybload [--encoding](../bulk_loading/ybload_options.md#encoding). This option does not apply to CREATE EXTERNAL TABLE operations.
escapecharSPECIAL_CHARACTERDepends on the format optionStringUse this character as the escape character. Equivalent to the --escape-char option in ybload.
fillrecordtrue, falsefalse (ERROR)BooleanDefine 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](../bulk_loading/ybload_options.md#onmissingfield) SUPPLYNULL. This option does not apply to CREATE EXTERNAL TABLE operations.
formatcsv, text, bcpcsvStringDefine the format of the data being read or written: CSV, TEXT, or BCP (see the ybload [--format](../bulk_loading/ybload_options.md#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.
ignorezerotrue, falsefalse (ERROR)BooleanRemove 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](../bulk_loading/ybload_options.md#onzerochar) REMOVE. This option does not apply to CREATE EXTERNAL TABLE operations.
includeheadertrue, falsefalseBooleanTRUE: 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.
logdirPATHSame location as external filesStringSet 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.
maxerrorsNUMBER0IntegerSet the maximum number of bad rows to tolerate. Equivalent to ybload [--max-bad-rows](../bulk_loading/ybload_options.md#maxbadrows) 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.

notruncatetrue, falsefalseBooleanPreserve 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.
nullvalueSTRINGNULLStringSpecify the string that will represent NULL in the result set (equivalent to the --nullmarker option).
quotedvalueyes, no, single, double, SPECIAL_CHARACTER"StringUse this character as the quote character when the format is CSV. Equivalent to the --quote-charand--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.

recorddelimSPECIAL_CHARACTERAuto-detectedStringDefine the record delimiter (or row separator) that is used in external source files. Equivalent to ybload [--linesep](../bulk_loading/ybload_options.md#linesep). This option does not apply to CREATE EXTERNAL TABLE operations.
skiprowsNUMBER0IntegerSkip 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.
timedelimCHARACTER:StringSpecify 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'StringSpecify 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.
truncstringtrue, falsefalseBooleanTruncate strings that are longer than the specified column width or return an error. Equivalent to ybload [--on-string-too-long](../bulk_loading/ybload_options.md#onstringtoo). true is equivalent to truncate; false is equivalent to error. This option does not apply to CREATE EXTERNAL TABLE operations.

y2baseYEAR (0-9999)No defaultintegerEquivalent to the ybload [--y2base](../bulk_loading/ybload_options.md#y2base) option. This option does not apply to CREATE EXTERNAL TABLE operations.

Parent topic:CREATE EXTERNAL TABLE