# 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. |