I hereby agree to the terms of the CLA available at: https://yandex.ru/legal/cla/?lang=en

Changelog category (leave one):

• Improvement

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):
Add compression for INTO OUTPUT and FROM INFILE with additional optional parameter.

Detailed description / Documentation draft:
Additional version of #27134 in solving #3473. This version differs from previous be special field COMPRESSION that can be used after INTO OUTFILE filename COMPRESSION type.

Also it improves #27655 by adding compression method.

Description.
Functions FROM INFILE and INTO OUTFILE can work with compressed files. By default they will check file name ending to choose correct compression method. But user can also define compression method using COMPRESSION. Compression can be 'gzip', 'deflate', 'br', 'xz', 'zstd', 'lz4', 'bz2'.

Examples

SELECT * FROM (SELECT 'Hello, World!') INTO OUTFILE 'file_with_gz.gz' FORMAT TabSeparated;
SELECT * FROM (SELECT 'Hello, World!') INTO OUTFILE 'file_without_gz' COMPRESSION 'GZ' FORMAT TabSeparated;

These queries are equal (it can be checked by gunzip command in terminal, but file_without_gz should be copied or moved to file with .gz because it can't be parsed by gunzip as gziped file).

Also they can be checked by queries:

INSERT INTO TABLE table_name FROM INFILE 'file_with_gz.gz' FORMAT TabSeparated;
INSERT INTO TABLE table_name FROM INFILE 'file_without_gz' COMPRESSION 'gz' FORMAT TabSeparated;

Both queries will insert "Hello, World!" into table.

Note for docs

It will be great to add documentation for INSERT INTO TABLE FUNCTION. (https://clickhouse.tech/docs/en/sql-reference/table-functions/s3/#usage-examples -- some examples, #28716 is adding documentation for insert into table s3, but we have nothing here https://clickhouse.tech/docs/en/sql-reference/statements/insert-into/#insert_query_insert-select)