Changelog category (leave one):

• New Feature

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):
Implement data schema inference for input formats. Allow to skip structure (or write just auto) in table functions file, url, s3, hdfs and in parameters of clickhouse-local . Allow to skip structure in create query for table engines File, HDFS, S3, URL, Merge, Buffer, Distributed and ReplicatedMergeTree (if we add new replicas).

Closes #14450

Detailed description / Documentation draft:
Now you can read data from a file without specifying the structure almost for all input formats.
Read data from file:

SELECT * FROM file(<file_path>, <file_format>)

Or:

SELECT * FROM file(<file_path>, <file_format>, 'auto')

Create a table from existing file:

CREATE TABLE <table_name> ENGINE=File(<file_format>, <file_path>)

Attach table from file:

ATTACH TABLE <table_name> FROM <file_path> ENGINE = File(<file_format>)

See the structure of any file (if format supports schema inference):

DESC file(<file_path>, <file_format>)

The same for HDFS, S3 and URL table engines/table functions.

For table engines Merge, Buffer and Distributed you can skip table structure in create query, the structure will be determined by target table.
Example of create query:

CREATE TABLE <table_name> ENGINE=Merge(<db_name>, <tables_regexp>)

For ReplicatedMergeTree table engine you can skip table structure in create query if you already have at least one replica. The structure will be determined by existing replicas.

CREATE TABLE <table_name> ENGINE=ReplicatedMergeTree(...)

For clickhouse-local you can skip parameter -S/--structure or set it to 'auto' when it reads data from a file (it doesn't work when data passed by stdin):

clickhouse-local --input-format='<table_format>' --file='<data_file>' --query='select * from table'

Formats that support schema inference:

1. Formats Protobuf and CapnProto.
   In these formats ClickHouse will read an external schema and convert it to ClickHouse schema. It won't read any data to determine table structure (so, data file can be empty). If you want to see how Protobuf or CapnProto schema transforms into ClickHouse schema, you can run the next query:

DESC file('nonexist', 'Protobuf') SETTINGS format_schema='<path_to_schema>:<message_name>'

2. Parquet, ORC, Arrow, ArrowStream, Avro, Native, formats with suffix -WithNamesAndTypes
   These formats contains the schema directly in the data file, in order to determine the schema ClickHouse reads some part of the data.

3. Formats LineAsString, RawBLOB, JSONAsString
   For these formats the schema is always the same - one column with type String

4. Formats Values, CSV(WithNames), TSV(WithNames), TSVRaw(WithNames), CustomSeparated(WithNames), TSKV, Template, JSONEachRow, JSONCompactEachRow(WithNames), Regexp, MsgPack.
   In these formats ClickHouse reads first input_format_max_rows_to_read_for_schema_inference rows of data and tries to determine the types of columns by using some tweaks and heuristics. If format doesn't contain column names, ClickHouse will use names column_1, column_2, ..., column_N.
   Some limitations:

   • All numbers are treated as Float64 (except MsgPack where we can differentiate integers and floats).
   • All types that are output as strings (Date, DateTime, etc.) will be detected as strings.
   • For formats TSV, TSVRaw, TSKV and for escaping rules Escaped and Raw we treat all columns as String (no tweaks are used here yet).
   • Type Map can be detected only for formats MsgPack, JSONEachRow, JSONCompactEachRow(WIthNames) and for JSON escaping rule (will be improved later).
   • MsgPack format is binary and you should specify the number of columns in it for schema inference, use setting input_format_msgpack_number_of_columns.