7z archives error unpacking

[Delphin1]

Consumption of data packed in 7z does not work on s3 and locally.

select *, _file as file
FROM file('tr_cyp_kosmos2023-12-25.7z', 'CSV'
)

limit 100
SETTINGS
format_csv_delimiter=' ',
input_format_csv_detect_header=false,
input_format_csv_allow_whitespace_or_tab_as_delimiter=true
;



Query id: ac28d786-85c0-4128-b67b-62c50d09a318


Elapsed: 0.163 sec.

Received exception from server (version 24.9.1):
Code: 27. DB::Exception: Received from host:9000. DB::Exception: Cannot parse input: expected ' ' before: 'q#e.\0\0\0\0\0�\0\0\0\0\0\0\0�\'j�����\r]\0\f�i�t[��]�Ƥ=�ko�)k�/T۟��G��L�N\fo�\baʿBn�w����{�����\0�mkr��Ps����?�ĸ���kZ:!���G�Ն�E,~ݽ�����v��\'�mzx[': (at row 1)
:
Row 1:
Column 0,   name: ticker_quote_time,         type: DateTime64(3),  parsed text: "7z��<SINGLE QUOTE><0x1C><ASCII NUL><0x04>w�"
ERROR: garbage after DateTime64(3): "q#e.<0x01><ASCII NUL><ASCII NUL><ASCII NUL><ASCII NUL><ASCII NUL>"

: (in file/uri /var/lib/clickhouse/user_files/ticks/tr_cyp_kosmos2023-12-25.7z): While executing ParallelParsingBlockInputFormat: While executing File. (CANNOT_PARSE_INPUT_ASSERTION_FAILED)

A clear and concise description of what works not as it is supposed to.

How to reproduce
cd /var/lib/clickhouse/user_files
wget https://github.com/Delphin1/clickhouse-7z-issue/blob/main/tr_cyp_kosmos2023-12-25.7z
query select above

• Which ClickHouse server version to use
  CH version: 24.9.1.3278

Expected behavior
Parsed data table

Looks like some codecs do not support.

7z t tr_cyp_kosmos2023-12-25.7z

7-Zip [64] 16.02 : Copyright (c) 1999-2016 Igor Pavlov : 2016-05-21
p7zip Version 16.02 (locale=C.UTF-8,Utf16=on,HugeFiles=on,64 bits,8 CPUs LE)

Scanning the drive for archives:
1 file, 77575 bytes (76 KiB)

Testing archive: tr_cyp_kosmos2023-12-25.7z
--
Path = tr_cyp_kosmos2023-12-25.7z
Type = 7z
Physical Size = 77575
Headers Size = 162
Method = LZMA2:1536k
Solid = -
Blocks = 1

Everything is Ok

Size:       1284855
Compressed: 77575

[alexey-milovidov]

In your example, you have interpreted the whole 7z file as CSV, which is incorrect.
Instead, you want to extract a file or multiple files from 7z and work with them.

It will look like follows:

select *, _file as file
FROM file('tr_cyp_kosmos2023-12-25.7z :: *.csv')

limit 100
SETTINGS
format_csv_delimiter=' ',
input_format_csv_detect_header=false,
input_format_csv_allow_whitespace_or_tab_as_delimiter=true
;

[Delphin1]

Thx Alexey!
This combination worked for me with file() function

select *
FROM file('ticks/tr2024-10-17.7z :: *.txt', 'CSV',
     'ticker_quote_time DateTime64(3), quote_id UInt64, quote_symbol String, bid Decimal(19,5), ask Decimal(19,5), mid Decimal(19,5), is_trading_hours Boolean, quote_time DateTime, mask String, bid_rmng_markup Decimal(19,5), ask_rmng_markup Decimal(19,5), mid_rmng_markup Decimal(19,5), incoming_quote_ask_markup Decimal(19,5), raw_quote_spread Decimal(19,5)'
)
limit 100
SETTINGS
format_csv_delimiter=' ',
input_format_csv_detect_header=false,
input_format_csv_allow_whitespace_or_tab_as_delimiter=true
;

but unfortunately, I couldn't get the same approach to work with s3

select *
FROM s3('https://s3.eu-west-1.amazonaws.com/bucket/tr_cyp_kosmos2023-12-25.7z :: *.txt',
     'CSV',
     'ticker_quote_time DateTime64(3), quote_id UInt64, quote_symbol String, bid Decimal(19,5), ask Decimal(19,5), mid Decimal(19,5), is_trading_hours Boolean, quote_time DateTime, mask String, bid_rmng_markup Decimal(19,5), ask_rmng_markup Decimal(19,5), mid_rmng_markup Decimal(19,5), incoming_quote_ask_markup Decimal(19,5), raw_quote_spread Decimal(19,5)'
)
limit 10
SETTINGS
format_csv_delimiter=' ',
input_format_csv_detect_header=false,
input_format_csv_allow_whitespace_or_tab_as_delimiter=true
;

or

select *
FROM s3(
      'https://s3.eu-west-1.amazonaws.com/bucket/tr_cyp_kosmos2023-12-25.7z :: *.txt'
,      'CustomSeparated'
,      'ticker_quote_time DateTime64(3), quote_id UInt64, quote_symbol String, bid Decimal(19,5), ask Decimal(19,5), mid Decimal(19,5), is_trading_hours Boolean, quote_time DateTime, mask String, bid_rmng_markup Decimal(19,5), ask_rmng_markup Decimal(19,5), mid_rmng_markup Decimal(19,5), incoming_quote_ask_markup Decimal(19,5), raw_quote_spread Decimal(19,5)'
     )

limit 100
SETTINGS
format_custom_field_delimiter=' ',
format_custom_escaping_rule='None'
;

Code: 643. DB::Exception: Couldn't open archive 'tr_cyp_kosmos2023-12-25.7z': Failed to opentr_cyp_kosmos2023-12-25.7z: While executing S3Source. (CANNOT_UNPACK_ARCHIVE) (version 24.9.1.3278 (official build))

Each 7z archive contains a txt file with values ​​separated by spaces.

2023-10-17T00:00:45.583 1234 EURGBP 0.1 0.83596 0.83594 1 2023-10-17T00:00:45 0x35 0 0 0 0 0
2023-10-17T00:00:45.583 1233 EURCHF 0.93972 0.2 0.93976 1 2023-10-17T00:00:45 0x35 0 0 0 0 0

[divanik]

Each 7z archive contains a txt file with values ​​separated by spaces.

Do you have any headers in your file?

[Delphin1]

No headers at all

(base) ❯ head tr_cyp_kosmos2024-10-13.txt
2024-10-13T21:00:00.223 3343971779 USDCHF 0.85547 0.85893 0.8572 1 2024-10-13T21:00:00 0x35 0 0 0 0 0
2024-10-13T21:00:00.223 3343971780 USDCHF 0.85547 0.85923 0.85735 1 2024-10-13T21:00:00 0x35 0 0 0 0 0
2024-10-13T21:00:00.317 3343971781 EURGBP 0.83542 0.83757 0.8365 1 2024-10-13T21:00:00 0x35 0 0 0 0 0
2024-10-13T21:00:00.317 3343971782 EURGBP 0.83542 0.83803 0.83673 1 2024-10-13T21:00:00 0x35 0 0 0 0 0
2024-10-13T21:00:00.317 3343971783 EURUSD 1.09269 1.09377 1.09323 1 2024-10-13T21:00:00 0x35 0 0 0 0 0
2024-10-13T21:00:00.317 3343971784 EURUSD 1.09269 1.09367 1.09318 1 2024-10-13T21:00:00 0x35 0 0 0 0 0
2024-10-13T21:00:00.317 3343971785 EURCHF 0.93534 0.94015 0.93775 1 2024-10-13T21:00:00 0x35 0 0 0 0 0
2024-10-13T21:00:00.317 3343971786 EURCHF 0.93534 0.94055 0.93795 1 2024-10-13T21:00:00 0x35 0 0 0 0 0
2024-10-13T21:00:00.597 3343971787 EURUSD 1.09269 1.09375 1.09322 1 2024-10-13T21:00:00 0x35 0 0 0 0 0
2024-10-13T21:00:00.691 3343971788 USDCHF 0.85531 0.85923 0.85727 1 2024-10-13T21:00:00 0x35 0 0 0 0 0

[divanik]

Hello! I've investigated the issue and here's what I found:
The root cause is that we currently only support local processing of 7z archives. I believe the current logic is suboptimal - we simply ignore the buffer reading function. I'd like to propose modifying its interface and implementing more descriptive error messages for your case.
I attempted to naively implement buffer support in my branch, but it was unsuccessful. Based on my research, libarchive appears to lack support for 7z custom buffers, though I welcome correction if anyone knows otherwise. Adding this feature would likely require integrating a new library, which would be a significant undertaking.

I'm happy to work on this if management considers it a priority. The potential solution would need discussion, particularly regarding the necessity of adding another submodule as a dependency.

[alexey-milovidov]

@divanik, the feature is not needed for now, but we need to clarify these limitations in our docs.