Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.databend.com/sql/sql-reference/file-format-options below:

Input & Output File Formats

Introduced or updated: v1.2.713

Databend accepts a variety of file formats both as a source and as a target for data loading or unloading. This page explains the supported file formats and their available options.

To specify a file format in a statement, use the following syntax:

... FILE_FORMAT = ( TYPE = { CSV | TSV | NDJSON | PARQUET | ORC | AVRO } [ formatTypeOptions ] )


... FILE_FORMAT = ( FORMAT_NAME = '<your-custom-format>' )

• Databend currently supports ORC and AVRO as a source ONLY. Unloading data into an ORC or AVRO file is not supported yet.
• If you don't specify the FILE_FORMAT when performing a COPY INTO or SELECT operation from a stage, Databend will use the file format that you initially defined for the stage when you created it. In cases where you didn't explicitly specify a file format during the stage creation, Databend defaults to using the PARQUET format. If you specify a different FILE_FORMAT from the one you defined when creating the stage, Databend will prioritize the FILE_FORMAT specified during the operation.
• For managing custom file formats in Databend, see File Format.

formatTypeOptions includes one or more options to describe other format details about the file. The options vary depending on the file format. See the sections below to find out the available options for each supported file format.

formatTypeOptions ::=
  RECORD_DELIMITER = '<character>'
  FIELD_DELIMITER = '<character>'
  SKIP_HEADER = <integer>
  QUOTE = '<character>'
  ESCAPE = '<character>'
  NAN_DISPLAY = '<string>'
  ROW_TAG = '<string>'
  COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | XZ | NONE

Databend accepts CVS files that are compliant with RFC 4180 and is subject to the following conditions:

• A string must be quoted if it contains the character of a QUOTE, ESCAPE, RECORD_DELIMITER, or FIELD_DELIMITER.
• No character will be escaped in a quoted string except QUOTE.
• No space should be left between a FIELD_DELIMITER and a QUOTE.
• A string will be quoted in CSV if it comes from a serialized Array or Struct field.
• If you develop a program and generate the CSV files from it, Databend recommends using the CSV library from the programing language.

Separates records in an input file.

Available Values:

• \r\n
• A one-byte, non-alphanumeric character, such as # and |.
• A character with the escape char: \b, \f, \r, \n, \t, \0, \xHH

Default: \n

Separates fields in a record.

Available Values:

• A one-byte, non-alphanumeric character, such as # and |.
• A character with the escape char: \b, \f, \r, \n, \t, \0, \xHH

Default: , (comma)

Quotes strings in a CSV file. For data loading, the quote is not necessary unless a string contains the character of a QUOTE, ESCAPE, RECORD_DELIMITER, or FIELD_DELIMITER.

Available Values: ', ", or `(backtick)

Default: "

Escapes a quote in a quoted string.

Available Values: '\\' or ''

Default: ''

Specifies how many lines to be skipped from the beginning of the file.

Default: 0

Specifies how "NaN" (Not-a-Number) values are displayed in query results.

Available Values: Must be literal 'nan' or 'null' (case-insensitive)

Default: 'NaN'

Specifies how NULL values are displayed in query results.

Default: '\N'

ERROR_ON_COLUMN_COUNT_MISMATCH is a boolean option that, when set to true, specifies that an error should be raised if the number of columns in the data file doesn't match the number of columns in the destination table. Setting it to true helps ensure data integrity and consistency during the loading process.

Default: true

Specifies the value that should be used when encountering empty fields, including both ,, and ,"",, in the CSV data being loaded into the table.

Specifies whether to include a header row in the CSV file when exporting data with the COPY INTO <location> command. Defaults to false.

Controls the binary encoding format during both data export and import operations, with available values HEX (default) and BASE64.

Specifies the compression algorithm.

Databend is subject to the following conditions when dealing with a TSV file:

• These characters in a TSV file will be escaped: \b, \f, \r, \n, \t, \0, \\, \', RECORD_DELIMITER, FIELD_DELIMITER.
• Neither quoting nor enclosing is currently supported.
• A string will be quoted in CSV if it comes from a serialized Array or Struct field.
• Null is serialized as \N.

note

1. In Databend, the main difference between TSV and CSV is NOT using a tab instead of a comma as a field delemiter (which can be changed by options), but using escaping instead of quoting for delimter collision
2. We recommend CSV over TSV as a storage format since it has a formal standard.
3. TSV can be used to load files generated by
   1. Clickhouse TSV file format.
   2. MySQL mysqldump command with option --tab without --fields-enclosed-by or --fields-optinally-enclosed-by, if the later two is specified, use CSV instead.
   3. Snowflake CSV without ESCAPE_UNENCLOSED_FIELD. if ESCAPE_UNENCLOSED_FIELD is specified, use CSV instead.

Separates records in an input file.

Available Values:

• \r\n
• An arbitrary character, such as # and |.
• A character with the escape char: \b, \f, \r, \n, \t, \0, \xHH

Default: \n

Separates fields in a record.

Available Values:

• A non-alphanumeric character, such as # and |.
• A character with the escape char: \b, \f, \r, \n, \t, \0, \xHH

Default: \t (TAB)

Same as the COMPRESSION option for CSV.

Specifies how to handle null values during data loading. Refer to the options in the table below for possible configurations.

Determines the behavior when encountering missing fields during data loading. Refer to the options in the table below for possible configurations.

Same as the COMPRESSION option for CSV.

Determines the behavior when encountering missing fields during data loading. Refer to the options in the table below for possible configurations.

Specifies the compression algorithm, which is used for compressing internal blocks of the file rather than the entire file, so the output remains in Parquet format.

Determines the behavior when encountering missing fields during data loading. Refer to the options in the table below for possible configurations.

Determines the behavior when encountering missing fields during data loading. Refer to the options in the table below for possible configurations.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4