Product Documentation

Appearance

ybload Options ​

This section contains detailed descriptions of the bulk load options. Note the following points about the format of these options and their values:

  • Options are listed in alphabetical order for quick reference.

  • Option names are shown in lowercase; they are case-sensitive.

  • Specific valid option values (such as true and false) are shown in lowercase. Variables for option values, such as STRING, are shown in uppercase. Option values are not case-sensitive.

  • The requirements for quoting option strings vary by client platform. Values are shown without quotes, but quotes are sometimes required. For example, if you specify the # character in a Linux shell, it must be enclosed by single or double quotes. If you are using a Windows client, see also Escaping Quotes in Windows Clients.

  • See also Common Options in ybtools.

  • (stdin) : Load from stdin (standard input). To load from stdin instead of named source files, enter the single-dash character (-) at the end of the ybload command. This must be the last character on the command line, and it must be prefixed with -- to indicate that option parsing is complete. For example: ./ybload -d premdb --username bobr -t match -- -

@file

Specify a file that includes a set of options and values to use for the operation. See Saving Load Options to a File and Saving Unload Options to a File.

You can use this option and the --select-file option in the same ybunload command.

--bad-row-file STRING

Define the location and name of a file where rejected rows will be logged. If you do not specify this option, the file defaults to the name SOURCE_FILENAME.TIMESTAMP.bad and is written to a location that is reported early in the console or log file output. If the file already exists, it is truncated.

Note: When object storage is used for loading data, bad rows must be written to the local file system. Specifying a bad row file in an object storage location, such as an S3 bucket, is not supported.

--bigint-field-options

See ybload Field Options.

--boolean-field-options

See ybload Field Options.

--bytes-per-transaction

Set the number of bytes to load per commit. The default is 1TB (1099511627776 bytes). You can set this option to modify the frequency of commits when bulk loads are running. This option works in conjunction with --rows-per-transaction. The threshold that is met first is applied.

--char-field-options

See ybload Field Options.

--comment-char ASCII_CHARACTER

Define the comment character that is used in source files. The default value is the pound sign (#). The value must be a single ASCII character or a valid escape sequence. If --skip-comment-lines is set, commented rows in the source file are skipped, not rejected, and do not appear in the bad rows file.

When the --format option is set, the comment character may be a single-byte or multi-byte character.

--compression-policy

Define the compression policy for data buffers before they are sent from the client to the worker nodes. See ybload Advanced Processing Options.

--convert-ascii-control, --no-convert-ascii-control

Allow the caret control ASCII character ^@ to be parsed as a single-byte representation of null within a character string. The default is --no-convert-ascii-control. See also ybload Field Options.

--convert-c-escape, --no-convert-c-escape

Convert (or do not convert) C-style escape sequences when they appear in CHAR and VARCHAR fields. For example, the two-character sequence \t can be converted into a single tab character (0x09) or it can be loaded unchanged.

This option does not apply to fields with data types other than CHAR and VARCHAR. For example, in an INTEGER field, the value 4\x35, where \x3 is the C-style escape sequence for the number 5, returns an error: '\' is invalid digit. (However, the ybsql \copy command will convert C-style escape sequences in all fields.)

This option is only supported when the --format option is specified. If --format text is used, --convert-c-escape is the default behavior. If --format csv or --format bcp is used, --no-convert-c-escape is the default.

--date-field-options

See ybload Date Formats.

--date-style YMD | DMY | MDY | MONDY | DMONY | Y2MD | DMY2 | MDY2 | MONDY2 | DMONY2

Define the date format in terms of the order and style of the date parts, to avoid any ambiguity in parsing dates. For example, --date-style MDY means accept date values such as 08-13-2016.

If you specify one of the Y2 values, such as DMY2, you must also specify a --y2base value.

You can specify the --date-style option more than once in a single ybload command. For example:

--y2base 1990 --date-style MDY --date-style MONDY --date-style DMY2

Note that you can use JSON-style formatting to abbreviate this syntax.

If --date-style is not specified, the defaults derive from --date-field-options.

--decimal-field-options

See ybload Field Options.

Note that ybload accepts both the dot character (.) and the comma character (,) as the separator for decimal values.

--default-field-options {JSON formatted <FieldOptions>}

Specify the field options to use for fields that do not have their own per-field options or per-type options. See ybload Field Options. The default value is {}.

--delimiter SPECIAL_CHARACTER

Define the special character that the source file uses as its field delimiter. All of the following are supported:

  • A single Unicode character
  • A hex value that corresponds to any ASCII control code (such as 0x1f)
  • A valid escape sequence

When the --format option is set, the delimiter may be a multi-byte character.

If you do not specify a field delimiter, ybload auto-detects it from among the following characters:

  • ,
  • |
  • \t
  • \us
  • \uFFFA

See also Setting --format for Flat Files.

--double-field-options

See ybload Field Options.

--dryrun

Do a dry run of the load operation without committing any data to the table. See Using the Dry Run Option.

--duplicate-handler NONE | RANDOM | ORDER BY <sql clause>

Specify how to handle duplicate source rows when the --write-op option is set to insert, update, or upsert. (--duplicate-handler is ignored for --write-op delete loads.)

  • NONE (the default): source rows are assumed to be unique. The ybload behavior is undefined if duplicate source rows exist; UPDATE and UPSERT operations will sometimes fail.

  • RANDOM: a random matching source row is used to update each row in the target table.

  • "ORDER BY <sql clause>": source rows are sorted, and the first matching row that is found is used to update each row in the target table. For example: "ORDER BY order_date DESC, order_time DESC". Enclose the ORDER BY clause in double quotes.

    See ORDER BY Clause for the complete syntax that is supported, with the exception that the --duplicate-handler option does not support ordinal numbers to identify columns.

    You cannot use a declared key column in the --duplicate-handler order by clause.

    See also Handling Duplicate Rows and Examples with Duplicate Rows.

--emptymarker STRING_WITH_ESCAPES

Define a marker that matches the character used to represent an empty string in your source file (an empty CHAR or VARCHAR field). The value may be a string, a character, or a valid escape sequence.

You may need to quote the emptymarker character. For example, you can use --emptymarker '\t' on the command line, but not --emptymarker \t.

Note: To use " as the emptymarker character, you have to quote strings with a different character (" is the default). For example:

--emptymarker '\"'  --quote-char '|'

If null bytes exist within strings, use the --on-zero-char REMOVE option.

See also NULL and Empty Markers.

--encoding ENCODING_NAME | -E ENCODING_NAME

Specify the source file's encoding (character set: UTF8, LATIN9, ISO-8859-1, UTF16). If no encoding is specified, the encoding of the source file is assumed to match the database encoding. If the encoding of the source file does not match the encoding of the destination database, ybload will use Java transcoding to "translate" the source data to the encoding of the destination database.

Check the documentation for the version of Java you are using on the ybload client system. For example, if you are using the Oracle JVM, check this list of supported character sets. Names from either of the first two columns can be used with the --encoding option. See also the discussion of encodings in Creating Databases.

For the fastest load performance under ybload, export the data from the source system in the character set of the destination database. For example, load exported UTF8 data into a UTF8 database. Any required transcoding is likely to have an impact on load performance.

Note: To load UTF-16 data, create a UTF8 database and set the --encoding option to UTF16.

--escape-char SPECIAL_CHARACTER

Specify an escape character: any single Unicode character. The behavior of this option depends on the --format choice:

  • --format CSV: used to escape embedded quote characters inside quoted fields. The default escape character is ".
  • --format TEXT: used to escape embedded field delimiters and line separators. The default escape character is \.
  • --format BCP: disallowed with BCP format, which does not use escape characters.

When the --format option is set, the escape character may be a multi-byte character.

--field-defs SQL_NAME | SQL_COLUMN_DEF,...

Define a comma-separated list of source field names or field definitions. A field name simply represents the name of the corresponding column in the table. A field definition also includes the data type and any constraints on the column. Column definitions are used to create temporary table columns for source-only fields, as needed by the --duplicate-handler option for UPDATE and UPSERT loads.

You do not need to specify a list if the source fields match the order, number, and data type of the destination table columns. The --parse-header-line option, which only supports field names, overrides the --field-defs option.

Field names are case-insensitive unless they are quoted (same behavior as standard SQL).

For example:

--field-defs id,name,"Balance",date
--field-defs id BIGINT PRIMARY KEY,update_order INT NOT NULL,balance,date

If the target table has default values assigned for certain columns, you can specify a partial column list with this option and the default values will be loaded for the other columns. For example, if a table t1 has three columns, c1, c2, and c3, and c1 was created with a DEFAULT constraint, the --field-defs list could be c2, c3.

See also Loading Generated Key Values.

--format CSV | TEXT | BCP | PARQUET

Specify the formatting style of the incoming data (how the source files were formatted by the export or unload tool that produced them).

For flat files, this option refers to how field delimiters are protected in the data:

  • CSV: Delimiters in field values were protected by wrapping the field values in quotes. For example: "2012, Mini Cooper S, ALL4"
  • TEXT: Delimiters in field values were protected by preceding them with a backslash escape character. For example: 2012\, Mini Cooper S\, ALL4
  • BCP: Delimiters in fields were not protected (for compatibility with the Microsoft SQL Server bcp tool). For example: 2012, Mini Cooper S, ALL4

See also Setting --format for Flat Files and Loading Tables from Parquet Files.

--ignore-emptymarker-case, --no-ignore-emptymarker-case

The first option supports case-insensitive empty-marker comparisons. If you use --ignore-emptymarker-case, values of EMPTY, Empty, and empty are all recognized as empty values when --emptymarker is set to NONE. These options apply globally; you cannot specify them per data type or per field. They are applied globally regardless of how the --emptymarker option was specified.

--ignore-nullmarker-case, --no-ignore-nullmarker-case

The first option supports case-insensitive null-marker comparisons. If you use --ignore-nullmarker-case, values of NULL, Null, and null are all recognized as null values when --nullmarker is set to NULL. These options apply globally; you cannot specify them per data type or per field. They are applied globally regardless of how the --nullmarker option was specified.

--ignore-unsupported-schema, --no-ignore-unsupported-schema

If --ignore-unsupported-schema is specified, ybload proceeds with a parquet load when source fields contain unsupported data types or a nested structure. See Parquet Load Examples. Note that the load may still fail when a target column for unsupported data is declared NOT NULL or has no DEFAULT value. The failure occurs because ybload proceeds as if the unsupported fields are not in the file.

When --no-ignore-unsupported-schema is specified (or neither option is specified, which is the default behavior), ybload returns an error when source fields contain unsupported data types or a nested structure. See also Loading Tables from Parquet Files and Parquet Load Examples.

These options do not apply to loads of flat files.

--integer-field-options

See ybload Field Options.

--ip-field-options

See ybload Field Options.

--key-field-names SQL_NAME,...

Comma separated list of source field names to be used as key fields for --write-op operations. Key field names must be declared as NOT NULL in the CREATE TABLE statement.

If this option is not specified, primary keys either specified with the --field-defs option or declared by the target table are used. If no key fields are specified and no primary key exists, an attempt to load a table with --write-op delete, update, or upsert will result in an error.

Field names are case-insensitive unless they are quoted (same behavior as standard SQL). For example:

--key-field-names user_name,"deptId"

The --parse-header-line option cannot be used in conjunction with the --key-field-names option.

--linesep LINE_SEPARATOR

Define the line separator (or row separator) that is used in source files: any single Unicode character or the \r\n escape sequence.

If you do not specify a line separator, ybload auto-detects it from among the following characters:

  • \n
  • \r\n
  • \rs
  • \uFFFB

When the --format option is set, the line separator may be a multi-byte character.

--locale LOCALE_NAME | -L LOCALE_NAME

The name of the locale to use for parsing dates, timestamps, and so on. If the locale is not specified, the database locale is assumed to be C.

Locale names must be of the following form:

<language code>[_<country code>[_<variant code>]]

For example:

--locale en
--locale en_US
--locale zh_CN

Variant codes are rarely used; for details, see the Java documentation.

--mac-field-options

Specify field options for MACADDR and MACADDR8 fields. See ybload Field Options.

--max-bad-rows NUMBER

Set the maximum number of rejected rows that ybload will tolerate before aborting and starting to roll back the transaction. (Additional bad rows may be reported before the transaction has finished aborting.) The default is -1, which means do not abort and roll back, regardless of the number of bad rows.

Note: --max-bad-rows 32 means 32 bad rows are allowed; the load will fail on the 33rd bad row.

Rejected rows are written to the location specified with the --bad-row-file option.

--nullmarker STRING

Define a string that matches the string used to represent null in your source file. If this option is unspecified or set to an empty string, adjacent delimiters without text between them are parsed as NULL values. This option supports valid escape sequences.

You cannot load data in which more than one value in the same column is intended to be parsed as NULL. However, you can load data in which different columns have different values for NULL. See Specifying NULL Behavior for Different Columns and NULL and Empty Markers.

--num-cores NUMBER_MIN_1

Set the number of CPU cores ybload will attempt to use to 1 or greater. By default, ybload tries to saturate all of the CPU cores on the host computer. For example, the default values for --num-readers and the number of actual concurrent readers used by --read-sources-concurrently ALWAYS are both based on the number of cores on the host computer. The primary purpose of this setting is to restrict ybload to the use of fewer resources when it shares the host computer with other programs.

--num-header-lines [ NUMBER ]

Ignore one or more header lines at the top of the source file. (The first or only header line in a file is typically a list of field names.) The default is 0, or 1 if --parse-header-line is specified. The maximum number is 5. If you specify multiple source files, the first line is skipped in all of them.

--num-readers

Define the behavior for reading input files in parallel. See ybload Advanced Processing Options.

Note: When you are loading from named pipes, setting this option equal to the number of pipes is recommended. Not loading from pipes concurrently may cause a deadlock with the program that the pipes communicate with.

--num-parsers-per-reader

Define multiple parsers per reader. See ybload Advanced Processing Options.

--object-storage-*

See Object Storage Options. These options apply to loads and unloads from and to Azure, AWS S3, and S3-compatible systems.

--on-extra-field [ REMOVE | ERROR ]

Specify REMOVE to allow rows to be loaded when the source file (CSV, TEXT, or BCP) contains either more fields than the columns defined in the header, as detected when the --parse-header-line option is used, or more fields than the columns defined with the --field-defs option.

If neither --parse-header-line nor --field-defs is specified, --on-extra-field has no effect; ybload expects the source file to have the same number of fields as the number of columns in the target table. The --parse-header-line option, if specified, overrides the --field-defs option.

This option takes effect only when the extra fields are at the end of the line in the source file. For example:

colA, colB, colC            <- header
data1, data2, data3, data4  <- on-extra-field

The default behavior (ERROR) is to reject rows with extra fields at the end of the line.

--on-invalid-char [ REPLACE | ERROR ]

Specify the action to take when the source file contains characters that cannot be represented in the database (or characters that are invalid in the source file itself):

  • The replacement character for a LATIN9 database is the question mark: 0x3F (?).
  • The replacement character for a UTF8 database is the Unicode replacement character: U+FFFD (a question mark in a diamond).

The default is REPLACE.

--on-missing-field [ SUPPLYNULL | ERROR ]

Specify SUPPLYNULL to allow rows to be loaded when the source file (CSV, TEXT, or BCP) contains either fewer fields than the columns defined in the header, as detected when the --parse-header-line option is used, or fewer fields than the columns defined with the --field-defs option.

If neither --parse-header-line nor --field-defs is specified, --on-missing-field has no effect; ybload expects the source file to have the same number of fields as the number of columns in the target table. The --parse-header-line option, if specified, overrides the --field-defs option.

This option takes effect only when the missing fields are at the end of the line in the source file. For example:

colA, colB, colC    <- header
data1, data2        <- on-missing-field

The default behavior (ERROR) is to reject rows with missing fields at the end of the line.

--on-string-too-long TRUNCATE | ERROR

Truncate character strings that are longer than the specified column width (or return an error). The default is ERROR.

--on-unescaped-embedded-quote [ PRESERVE | ERROR ]

Preserve or return an error when unescaped quotes are found inside quoted strings. The default is ERROR.

--on-zero-char [ REMOVE | ERROR ]

Remove null bytes (0x00 characters) that appear within strings in CHAR and VARCHAR fields (or return an error). The default is ERROR.

--parse-header-line

Use the header line at the top of the source file (a list of field names) to determine the column names in the target table. This option overrides the --field-defs option.

--per-field-options {JSON Object}

Specify parsing options for individual fields. See ybload Field Options.

--pre-parse-buf-size NUMBER_MIN_1

Specify the size of the pre-parse buffers, which should be larger than the size of any single row in the source data. The default value is 192744 bytes. (The maximum width of a row in a table is 64231 bytes.)

--quote-char SPECIAL_CHARACTER

Define the character that is used in source files to quote field values that contain embedded delimiters. Specify any single Unicode character. The default is ". This option only applies when you are using --format CSV.

When --format CSV is set, the delimiter may be a multi-byte character.

--read-sources-concurrently ALWAYS | NEVER | ALLOW | <NUMBER>

Define the behavior for reading source files in parallel: ALWAYS, NEVER, ALLOW (the default), or a specific number of source files. See ybload Advanced Processing Options.

Note: If you are loading from multiple pipes and this option is not set to ALWAYS, ybload sets it to ALWAYS and returns an INFO message stating that change. (Not reading from pipes concurrently could cause a deadlock with the program that they communicate with.)

--real-field-options

See ybload Field Options.

--resume-partial-load-from-offset NUMBER

Skip the specified number of bytes in the source file in order to resume a failed bulk load. The default value is 0. See Resuming a Partial Load.

--rows-per-transaction

Set the number of rows to load per commit. The default is set to the maximum number of rows that can be loaded (263 – 1). You can reduce this number to increase the frequency of commits when bulk loads are running. This option works in conjunction with --bytes-per-transaction. The threshold that is met first is applied.

--serialize-nested-as-json, --no-serialize-nested-as-json

If --serialize-nested-as-json is specified, ybload processes nested fields in complex parquet files as JSON. If --no-serialize-nested-as-json is specified (or neither option is specified), ybload returns an error when attempting to map complex nested fields to the columns in the target table. These options do not apply to loads of flat files. See Loading Tables from Parquet Files and Parquet Load Examples.

--skip-blank-lines, --no-skip-blank-lines

Skip blank lines in the source file (true) or detect blank rows as bad rows (false). The default value is true.

--skip-comment-lines, --no-skip-comment-lines

Skip lines that are commented out in the source file or detect them as bad rows. The default value is false (do not skip).

Note: If you use --skip-comment-lines, make sure the data does not contain any lines that begin with the comment character (which defaults to # but may bet set to a different character with the --comment-char option).

--skip-missing-files, --no-skip-missing-files

If a file is removed after ybload has expanded the path to the source files into a list, skip the file and continue the load operation. This option is useful when millions of source files (or a number of very large files) are being loaded over an extended period of time. As the load progresses, it is possible that an administrator may delete one or more files from the source location. You may or may not want to stop the load in this situation. The default behavior is to not skip missing files.

When a load proceeds because a missing file is skipped, you will see a message like this:

Skipped [filepath] because the file is removed after listing
--skip-open-failed-files, --no-skip-open-failed-files

If a file fails to open after ybload has expanded the path to the source files into a list, skip the file and continue the load operation. It is possible that the original file listing contains one or more files that have been modified and cannot be opened (for example, if permissions on the file were changed after the load started). You may or may not want to stop the load in this situation. The default behavior is to not skip files that fail to open.

When a load proceeds because a file that fails to open is skipped, you will see a message like this:

Skipped [filepath] because the file can’t be opened

This option only works when no bytes have already been read from the file in question. Files read from object storage may be loaded in parts, but a load will fail rather than skip a file that has been partially read.

--smallint-field-options

See ybload Field Options.

--source-compression NONE | AUTO | GZ | BZIP2 | XZ | PACK200 | LZ4

This option explicitly defines the type of compression used by source data and is primarily intended for data sources that do not have file names (such as STDIN).

The default value is AUTO. When specified, this option overrides other compression detection methods. If this option is not specified, for flat files ybload auto-detects the compression type based on their file name extension.

For parquet files, ybload auto-detects the compression type based on metadata in the files and supports the following compression types: SNAPPY, ZSTD, and LZO. You do not need to specify the --source-compression option for parquet files.

--table (or -t)

Name the target table to load and optionally its schema: schema_name.table_name. If you do not specify the schema name, the table is assumed to be in the public schema. The schema of the target table is not based on the user's search_path, regardless of how it is set. The table must exist in the database that you are connecting to for the load. (Do not try to specify the database name as part of the table name. Use the YBDATABASE environment variable, -d, or --dbname.)

Note: If you used a quoted case-sensitive identifier to create the target table, you must quote the table name and escape the quotes in the ybload command line. For example, if your table is named PremLeagueStats:

-t \"PremLeagueStats\"
--time-field-options

See ybload Field Options.

--timestamp-field-options

See ybload Field Options.

--timestamptz-field-options

See ybload Field Options.

--trim-white, --no-trim-white

Trim or retain leading and trailing whitespace characters in each field. With --format csv, whitespace characters inside of quotes are always preserved. The default value is false (preserved).

--truncate-before-insert, --no-truncate-before-insert

Truncate the target table before inserting new rows. Use this option if you want to ensure that the load runs against an empty table. The TRUNCATE statement is executed in the first transaction of the bulk load session. If the bulk load fails before that transaction is committed, the TRUNCATE is rolled back.

To use this option, you must have DELETE, TRUNCATE, and INSERT privileges on the target table (in addition to BULK LOAD privilege on the database).

--uuid-field-options

See ybload Field Options.

--write-op INSERT | UPDATE | DELETE | UPSERT

How the source rows should be written to the target table.

  • INSERT (the default): insert source rows as new rows (append them to the table).
  • UPDATE: update rows that match source rows.
  • DELETE: delete rows that match source rows.
  • UPSERT: update rows that match source rows, and insert new rows.

Updates, deletes, and upserts require primary keys or declared key fields to match against the target table. See --key-field-names.

By default, source rows are inserted (appended to the table) and duplicate rows are not discarded. Incoming rows are assumed to be unique. Updates and upserts may require duplicate handling. See the --duplicate-handler option.

Note: Make sure the user running ybload has BULK LOAD privileges on the database and appropriate additional privileges on the target table:

  • INSERT for default loads
  • UPDATE and SELECT for --write-op update and --write-op upsert loads
  • DELETE and SELECT for --write-op delete loads

See also Deletes, Updates, and Upserts and Delete, Update, and Upsert Examples.

--y2base YEAR

Define the pivot year (such as 1970) for two-digit year values (such as 97 and 16). For example, if --y2base is set to 1970, two-digit years of 70 and later are assumed to be in the 1900s. Values of 69 and earlier are assumed to be in the 2000s.

If you want to specify one of the Y2 values for --date-style, such as DMY2, you must also specify a --y2base value.

Parent topic:ybload Command