Skip to content
Coordinator DB
View as Markdown
View as Markdown

Format settings ​

Format settings control how bytes are read or written for one statement. They are separate from persisted database resource settings and server XML options.

Syntax and scope ​

Use SETTINGS name = value, ... on one statement. A Boolean name without a value enables it. Options use the defaults again on the next statement; SET rejects format options. Names ignore ASCII case. Unknown names and invalid values are rejected before execution.

On INSERT, put SETTINGS before FORMAT because all bytes after an input format name are data. For SELECT it can appear before or after the FORMAT clause.

sql
SELECT 7 AS id,'Ada' AS name SETTINGS csv_separator=';' FORMAT csv;
text
7;"Ada"

Applicable formats ​

Each option affects a specific set of codecs. A valid option for another codec can be accepted without changing the selected format. Use its family page or the selected format's settings table to choose applicable options. In particular, json_each_row does not apply the numeric JSON quoting options.

Available options ​

Setting Type Default Meaning
arrow_compression String lz4_frame Compress record batch buffers with lz4_frame or zstd; none writes uncompressed buffers.
arrow_date_as_number Bool 0 Write Date as UInt16 days since the epoch instead of Date32; Date32 always remains Date32.
arrow_dictionary Bool 0 Write LowCardinality as dictionary indices and values instead of expanding its scalars.
arrow_dictionary_64bit_indices Bool 0 Use 64-bit dictionary indices instead of the smallest width that fits the dictionary.
arrow_dictionary_signed_indices Bool 1 Use signed dictionary indices; when off, use unsigned indices.
arrow_fixed_strings Bool 1 Write FixedString with a fixed byte width instead of variable-length Binary.
arrow_ignore_case Bool 0 Ignore ASCII case when matching input field names to target columns.
arrow_missing_as_default Bool 1 Give target columns absent from the input schema their DEFAULT; when off, reject missing columns.
arrow_strings_as_text Bool 1 Write text strings as Utf8 instead of Binary; byte strings keep their exact bytes.
avro_block_bytes UInt64 16384 Target uncompressed bytes per output block, from 32 through 1073741824; a complete row may exceed the target.
avro_compression String '' Output block compression: null, deflate, snappy, or zstd; empty selects snappy.
avro_confluent_connect_timeout UInt64 1 Connection and TLS handshake timeout in seconds; positive values are capped at 599.
avro_confluent_receive_timeout UInt64 1 Registry response receive timeout in seconds; positive values are capped at 599.
avro_confluent_registry_url String '' HTTP or HTTPS schema registry base URL; URL-encoded credentials select Basic authentication.
avro_confluent_retries UInt64 5 Retry transient registry failures this many times; zero disables retries and values above 20 are capped at 20.
avro_confluent_retry_delay_ms UInt64 100 Initial retry delay in milliseconds, capped at 60000; subsequent delays double up to 10000 milliseconds.
avro_confluent_send_timeout UInt64 1 Registry request send timeout in seconds; positive values are capped at 599.
avro_confluent_subject String '' Subject under which an output record schema is registered; required for avro_confluent output.
avro_missing_as_default Bool 0 Give target fields absent from the input schema their SQL DEFAULT or native type default; otherwise reject them.
avro_null_as_default Bool 0 Replace input NULL in a non-nullable native target with its SQL DEFAULT or native type default; legacy SQL columns retain NULL.
avro_string_columns String '' Regular expression matching output String column paths to encode as UTF-8 string instead of bytes; empty matches none.
bson_strings Bool 0 Write text and fixed-width strings as BSON String instead of Binary.
csv_check_types Bool 1 Compare the input types header with the bound target column types; when off, skip that header.
csv_crlf Bool 0 End each written row with a carriage return and a line feed instead of a line feed.
csv_double_quotes Bool 1 Read a field that starts with a double quote as quoted text; when off, the quote is an ordinary character.
csv_empty_as_default Bool 1 Give an empty unquoted field the column's DEFAULT; when off, read it as empty text.
csv_enum_by_number Bool 0 Read a whole number in an Enum column as the label's number instead of its name.
csv_match_names Bool 1 Match input names to target columns ignoring ASCII case; when off, skip the names header and read in target order.
csv_null_text String \N The unquoted text that stands for NULL when reading and writing; it must stay one untrimmed field under the current CSV settings.
csv_separator Char , The character between fields when reading and writing: one ASCII character that is not a letter, digit, quote, backslash, or line break. Write a tab as \t.
csv_single_quotes Bool 0 Also read a field that starts with a single quote as quoted text.
csv_skip_lines UInt64 0 Ignore this many lines at the start of the input, before any header row.
csv_trim_blanks Bool 1 Drop spaces and tabs around a field when reading; quotes keep the blanks inside them.
csv_uneven_rows Bool 0 Accept rows with too few or too many fields: missing fields take the column's DEFAULT and extra fields are dropped.
custom_separated_detect_header Bool 1 Recognize an input names header and an optional matching types header.
custom_separated_escaping String Escaped Field grammar: Escaped, Quoted, CSV, JSON, XML (output only), or Raw.
custom_separated_match_names Bool 1 Match input names to target columns ignoring ASCII case; when off, skip the names header and read in target order.
custom_separated_result_prefix String '' Text before the entire input or output result.
custom_separated_result_suffix String '' Text after the entire input or output result.
custom_separated_row_prefix String '' Text before the first field of each input or output row.
custom_separated_row_separator String '' Text between input or output rows, excluding their own prefixes and suffixes.
custom_separated_row_suffix String \n Text after the last field of each input or output row.
custom_separated_separator String \t Text between input or output fields; may contain multiple bytes.
custom_separated_skip_empty_lines Bool 0 Skip trailing CR and LF bytes after the result; one final LF is always accepted.
custom_separated_uneven_rows Bool 0 Give omitted input fields their SQL or native defaults and validate then skip extra fields.
geo_json_unsupported_geometry String throw Reject unsupported geometry types on input; null validates them and stores NULL when geometry is requested.
geo_json_validate_geometry Bool 1 Validate line lengths, ring lengths and closure, and nonempty geometry containers on input and output; coordinates must always be finite.
hive_text_collection_separator Char \x02 The byte between outermost Array and Tuple elements or Map entries on output; input uses bracketed values.
hive_text_map_separator Char \x03 The byte between outermost Map keys and values, also used at the next nested level on output.
hive_text_row_separator Char \n The byte ending each output row; input always reads newline-delimited CSV records.
hive_text_separator Char \x01 The byte between top-level fields when reading and writing, with no output quoting or escaping.
hive_text_uneven_rows Bool 1 Give omitted trailing input columns their DEFAULT and skip extra fields; off requires the exact field count.
ignore_unknown_fields Bool 0 Skip input keys and header names that match no target column instead of failing.
json_array_of_rows Bool 0 Wrap all output rows in one JSON array.
json_arrays_as_strings Bool 1 Accept JSON arrays in string input columns and retain their JSON text.
json_bools_as_numbers Bool 1 Accept JSON Boolean input in numeric columns as zero or one.
json_bools_as_strings Bool 1 Accept JSON Boolean input in string columns as true or false text.
json_check_types Bool 1 Compare input metadata or header types with the bound target column types.
json_columns_sql_defaults Bool 1 Evaluate SQL DEFAULT expressions for omitted input columns; off supplies only their native type defaults.
json_compact_columns_sql_defaults Bool 1 Evaluate SQL DEFAULT expressions for omitted trailing input columns; off supplies only their native type defaults.
json_compact_strings_each_row_sql_defaults Bool 1 Evaluate SQL DEFAULT expressions for missing or defaulted positional whole-text JSON fields; off supplies their native type defaults.
json_compact_uneven_rows Bool 0 Give missing positional fields their SQL or native defaults and validate then skip extra fields.
json_empty_as_default Bool 0 Give empty JSON string fields their SQL or native type default.
json_escape_slashes Bool 1 Escape unescaped forward slashes in JSON output strings.
json_match_names Bool 1 Match input header names to target columns ignoring ASCII case; when off, skip the names header and read in target order.
json_named_tuples_as_objects Bool 1 Read and write named Tuple values as objects; otherwise use positional arrays.
json_numbers_as_strings Bool 1 Accept JSON numbers in string input columns and retain their exact spelling.
json_object_each_row_name_column String '' Use this string column for outer row keys and omit it from row objects; empty generates row_1, row_2, and so on.
json_object_each_row_sql_defaults Bool 1 Evaluate SQL DEFAULT expressions for omitted or defaulted row fields; off supplies only their native type defaults.
json_objects_as_strings Bool 1 Accept JSON objects in string input columns and retain their JSON text.
json_quote_decimals Bool 0 Write fixed-point decimal output values as JSON strings.
json_quote_large_floats Bool 0 Write finite Float64 output values as JSON strings.
json_quote_large_integers Bool 0 Write integers of at least 64 bits as JSON strings; retained narrow types stay numeric.
json_quote_special_floats Bool 0 Write non-finite floats as inf, -inf, nan, or -nan strings instead of JSON null.
json_strict_escapes Bool 1 Reject invalid JSON escapes; otherwise preserve their literal backslash spelling.
json_strings_each_row_sql_defaults Bool 1 Evaluate SQL DEFAULT expressions for omitted or defaulted whole-text JSON fields; off supplies their native type defaults.
json_tuple_ignore_unknown_fields Bool 0 Validate then ignore object members outside a named Tuple declaration.
json_tuple_missing_as_default Bool 1 Give missing named Tuple members their declared type defaults.
json_validate_utf8 Bool 0 Replace invalid UTF-8 sequences in whole-text JSON output with replacement characters; off preserves byte-string payloads.
markdown_escape_special_characters Bool 0 Escape ASCII punctuation in text and byte-string values with a backslash; when off, escape controls, backslashes, and single quotes.
msg_pack_uuid_encoding String EXT Write UUID as EXT (extension type 2), BIN (16 binary bytes), or STR (canonical text).
parquet_compression String zstd Compress column pages with snappy, lz4, brotli, zstd, gzip, or none.
parquet_detect_variant Bool 1 Recognize unannotated metadata/value binary structs as Variant values.
parquet_fixed_strings Bool 1 Write FixedString as fixed-width bytes instead of variable-width Binary.
parquet_ignore_case Bool 0 Ignore ASCII case when matching input fields to target column names.
parquet_missing_as_default Bool 1 Give missing input fields their SQL DEFAULT or native type default.
parquet_page_bytes UInt64 1048576 Target uncompressed bytes per output data page.
parquet_page_index Bool 1 Write column and offset indexes for individual data pages.
parquet_parse_json Bool 1 Decode JSON-annotated input fields as native JSON instead of text.
parquet_read_bytes UInt64 16744704 Target decoded batch bytes estimated from uncompressed row-group metadata.
parquet_read_geo Bool 1 Decode WKB or WKT columns identified by GeoParquet metadata; text and binary targets retain raw payloads.
parquet_read_rows UInt64 65409 Maximum rows per decoded input batch.
parquet_row_group_bytes UInt64 536870912 Target output row-group bytes before compression.
parquet_row_group_rows UInt64 1000000 Maximum rows per output row group.
parquet_strings_as_text Bool 1 Annotate text strings as UTF-8; BLOB keeps its exact binary representation.
parquet_wide_as_decimal Bool 0 Write 128/256-bit integers as big-endian DECIMAL instead of little-endian fixed-width bytes.
parquet_write_batch_rows UInt64 1024 Check output data-page size after this many rows.
parquet_write_geo Bool 1 Encode geometry as WKB and include GeoParquet column metadata.
png_height UInt64 1024 Height of the image in pixels, from 1 through 2147483647.
png_time_denominator UInt64 60 Positive denominator of the duration in seconds of one integer t unit.
png_time_numerator UInt64 1 Positive numerator of the duration in seconds of one integer t unit.
png_width UInt64 1024 Width of the image in pixels, from 1 through 2147483647.
pretty_borders String unicode The characters that draw the table: unicode for box-drawing lines, ascii for plus, minus, and bar.
pretty_footer_from_rows UInt64 0 Repeat the column names in the bottom border when the table shows at least this many rows; 0 never repeats them.
pretty_max_cell_width UInt64 0 Cut a value wider than this many terminal columns and mark the cut; 0 never cuts.
pretty_max_padding UInt64 0 Align a column to at most this many terminal columns; wider values extend their own row. 0 aligns to the widest value.
pretty_max_rows UInt64 0 Show at most this many rows and report how many were left out; 0 shows every row.
pretty_row_numbers Bool 0 Add a first column that numbers the rows from 1.
protobuf_auto_schema Bool 1 Derive a message schema from columns when protobuf_schema is empty.
protobuf_keep_default_wrappers Bool 0 Write present Google wrappers for default scalar values; NULL wrappers remain absent.
protobuf_message String '' Fully qualified message name in protobuf_schema; empty selects Row for an automatically derived schema.
protobuf_missing_as_default Bool 1 Apply SQL DEFAULT to target columns outside the message schema; when off use their native type defaults.
protobuf_oneof_tag Bool 0 Read the selected oneof field number into a column named after that oneof; unmatched branches yield zero.
protobuf_schema String '' Literal proto2 or proto3 schema; imports are limited to bundled Google definitions.
protobuf_unwrap_google Bool 0 Read Google scalar wrappers directly into scalar columns; absent nullable wrappers become NULL.
regexp_escaping String Raw Read captured fields as Raw text, Escaped text, Quoted literals, CSV fields, or JSON values.
regexp_pattern String '' Match every complete input line; one capture group supplies each target column. Empty requires a statement override.
regexp_skip_unmatched Bool 0 Skip lines that do not match the expression; otherwise report their physical line number.
row_binary_check_types Bool 1 Compare input header types with target types; when off decode matched fields with target widths.
row_binary_match_names Bool 1 Match input header names ignoring ASCII case; when off read in target column order.
row_binary_max_string_bytes UInt64 1073741824 Maximum bytes in an input variable-length string, including header names and type parameters; 0 disables the limit.
row_binary_read_binary_types Bool 0 Read type tags and their binary parameters instead of textual type names in the input header.
row_binary_read_json_strings Bool 0 Read JSON fields as length-prefixed JSON text; binary JSON paths are unsupported.
row_binary_write_binary_types Bool 0 Write type tags and their binary parameters instead of textual type names in the output header.
row_binary_write_json_strings Bool 0 Write JSON fields as length-prefixed JSON text; binary JSON paths are unsupported.
snappy_mode String basic Read Snappy client files as basic (Hadoop blocks) or framed (standard streaming format).
sql_insert_batch_rows UInt64 65505 Write at most this many rows per INSERT or REPLACE statement; 0 writes one row per statement.
sql_insert_column_names Bool 1 Include the result column names before VALUES in each written statement.
sql_insert_quote_names Bool 1 Surround each written column name with backticks; names and the table text are otherwise written verbatim.
sql_insert_replace Bool 0 Write REPLACE INTO instead of INSERT INTO.
sql_insert_table String table The table text written verbatim after INSERT INTO or REPLACE INTO; include any desired qualification or quoting.
sqlite_read_table String '' Read this table from the input database; empty selects its first user table.
sqlite_write_table String table Name of the single table written to the output database.
tab_separated_check_types Bool 1 Compare the input types header with the bound target column types; when off, skip that header.
tab_separated_crlf Bool 0 End each written row with a carriage return and a line feed instead of a line feed.
tab_separated_detect_header Bool 1 Recognize a raw TSV names header and optional matching types header before reading data.
tab_separated_empty_as_default Bool 0 Give an empty field the column's DEFAULT instead of reading it as empty text.
tab_separated_enum_by_number Bool 0 Read a whole number in an Enum column as the label's number instead of its name.
tab_separated_match_names Bool 1 Match input names to target columns ignoring ASCII case; when off, skip the names header and read in target order.
tab_separated_null_text String \N The field text that stands for NULL when reading and writing; it cannot contain tabs or line breaks.
tab_separated_read_crlf Bool 0 Accept carriage-return/line-feed endings in raw TSV input, removing the carriage return from each record.
tab_separated_skip_empty_lines Bool 0 Ignore empty lines at the end of raw TSV input; empty lines inside the data remain records.
tab_separated_skip_lines UInt64 0 Ignore this many lines at the start of the input, before any header row.
tab_separated_uneven_rows Bool 0 Accept rows with too few or too many fields: missing fields take the column's DEFAULT and extra fields are dropped.
template_quote_large_integers Bool 0 Write integers of at least 64 bits as strings in JSON template fields; retained narrow types stay numeric.
template_result String '' Literal result template; empty means ${data}. Output supports data and rows; input may skip prefix or suffix fields.
template_row String '' Literal row template with named or zero-based column placeholders and per-field escaping; empty requires an override.
template_row_separator String \n Text between template rows, excluding their own literals; never written after the final row.