why the parquet file size of same data is bigger than duckdb, and query slower?

[l1t1]

I use clickhouse local

select number::int i FROM numbers_mt(1,1000000000)t into outfile '/data/t.parquet';

SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 1000000000) AS t
INTO OUTFILE '/data/t.parquet'

Query id: 04192cb3-f239-4926-aabe-ecf7903fb618


1000000000 rows in set. Elapsed: 69.345 sec. Processed 999.78 million rows, 8.00 GB (14.42 million rows/s., 115.34 MB/s.)

kylin-gtj :) SELECT avg(i) FROM file('/data/t.parquet') group by round(log10(i));

SELECT avg(i)
FROM file('/data/t.parquet')
GROUP BY round(log10(i))

Query id: bbb7acdc-e48f-45bd-b0f4-9268083e36f3

┌──────avg(i)─┐
│           2 │
│ 658113883.5 │
│    17392527 │
│     17392.5 │
│      1739.5 │
│      173925 │
│        17.5 │
│         174 │
│ 173925271.5 │
│   1739252.5 │
└─────────────┘

10 rows in set. Elapsed: 50.262 sec. Processed 1.00 billion rows, 5.00 GB (19.90 million rows/s., 99.48 MB/s.)

 ll /data/t.parquet
-rw-rw-r-- 1 kylin kylin 6029997281 5月   8 12:58 /data/t.parquet

and duckdb

D copy (select i::int i from range(1,1000000000)t(i)) to 't.parquet';
100% ▕████████████████████████████████████████████████████████████▏ 
Run Time (s): real 37.259 user 31.668000 sys 3.260000
D 
D copy (select i::int i from range(1,1000000000)t(i)) to 'tu.parquet' (codec 'uncompressed');
100% ▕████████████████████████████████████████████████████████████▏ 
Run Time (s): real 35.608 user 30.668000 sys 2.480000

D SELECT avg(i) FROM 't.parquet' group by round(log10(i));
100% ▕████████████████████████████████████████████████████████████▏ 
┌─────────────┐
│   avg(i)    │
│   double    │
├─────────────┤
│   1739252.5 │
│  17392527.0 │
│ 173925271.5 │
│ 658113883.0 │
│    173925.0 │
│         2.0 │
│        17.5 │
│       174.0 │
│      1739.5 │
│     17392.5 │
├─────────────┤
│   10 rows   │
└─────────────┘
Run Time (s): real 9.453 user 64.380000 sys 1.736000
D SELECT avg(i) FROM 'tu.parquet' group by round(log10(i));
100% ▕████████████████████████████████████████████████████████████▏ 
┌─────────────┐
│   avg(i)    │
│   double    │
├─────────────┤
│   1739252.5 │
│  17392527.0 │
│ 173925271.5 │
│ 658113883.0 │
│    173925.0 │
│         2.0 │
│        17.5 │
│       174.0 │
│      1739.5 │
│     17392.5 │
├─────────────┤
│   10 rows   │
└─────────────┘
Run Time (s): real 9.367 user 63.656000 sys 1.816000
ll

-rw-rw-r--  1 kylin kylin 4001080145 5月   8 10:31 t.parquet

[al13n321]

Fixes for all 3 problems already exist, coming out in the next release or two.

Specifically:

• into outfile used to produce very small row groups by default. This makes the files slow to write and slow to read. Fixed in Better control over Parquet row group size #49325 . It'll be in 23.5. Until then, please use insert (which doesn't have this problem): insert into function file('t.parquet') select number::int i FROM numbers_mt(1,1000000000)
• Reading speed is improved in Parallelize query processing right after reading FROM ... #48727 , Allow reordering by default when reading from file/s3/url #49479 (these two will be in 23.5), and in Read Parquet files even faster, take 2 #49539 . This takes the read query to the same speed as duckdb (clickhouse 2.3s, duckdb 2.5s on my machine).
• Writing speed is improved a lot in Partially reimplement Parquet encoder to make it faster and parallelizable #49367 . That makes the write query a few times faster than duckdb (6.7x on this query on my machine: clickhouse 4.5s, duckdb 30s).
• With all of the above, file size is still a tiny bit bigger than with duckdb: 4016034685 vs 4001080145. This is because default compression method is different: duckdb uses snappy, clickhouse uses lz4. Adding settings output_format_parquet_compression_method='snappy' brings the file size down to 4000535805.

Bonus mystery (optional reading):

• With the into outfile fix (Better control over Parquet row group size #49325) but without the custom encoder (Partially reimplement Parquet encoder to make it faster and parallelizable #49367), the file size comes out 4582715031 - still considerably bigger than duckdb's 4001080145. I'm guessing the difference is in dictionary encoding behavior (when switching from dictionary to non-dictionary encoding, it re-encodes the accumulated data instead of writing it out in dictionary-encoded form), but I didn't actually check.

[l1t1]

thank you

[l1t1]

insert is 1 time faster than into outfile in my test

root@localhost:/data/data/com.termux/files/home# ./ck22 local
<jemalloc>: Number of CPUs detected is not deterministic. Per-CPU arena disabled.
ClickHouse local version 23.5.1.2378 (official build).

localhost.localdomain :) select number::int i FROM numbers_mt(1,100000000)t into outfile '/data/t2.parquet';


SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 100000000) AS t
INTO OUTFILE '/data/t2.parquet'

Query id: 8f05a8b9-211e-4f77-b3ab-10eec98f06e5


100000000 rows in set. Elapsed: 15.733 sec. Processed 100.00 million rows, 800.00 MB (6.36 million rows/s., 50.85 MB/s.)

localhost.localdomain :) insert into function file('t1.parquet') select number::int i FROM numbers_mt(1,100000000);

INSERT INTO FUNCTION file('t1.parquet') SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 100000000)

Query id: cf49b757-ede2-4ae8-89a3-50cc56d43cd9

Ok.

0 rows in set. Elapsed: 8.733 sec. Processed 100.01 million rows, 800.08 MB (11.45 million rows/s., 91.62 MB/s.)

[l1t1]

file size of two parquet are equal.

root@localhost:/data/data/com.termux/files/home# ll *par*
-rw-r--r-- 1 root root  458508518 May 31 21:49 t1.parquet
root@localhost:/data/data/com.termux/files/home# ll /data/*par*
-rw-r--r-- 1 root root 458508521 May 31 21:47 /data/t2.parquet

[al13n321]

insert is 1 time faster than into outfile in my test

I'm getting no difference between outfile and insert (with and without #49367 ):

[with custom encoder]

:) select number::int i FROM numbers_mt(1,100000000)t into outfile 't2.parquet'
100000000 rows in set. Elapsed: 0.484 sec. Processed 86.08 million rows, 688.63 MB (177.71 million rows/s., 1.42 GB/s.)

:) insert into function file('t1.parquet') select number::int i FROM numbers_mt(1,100000000)
0 rows in set. Elapsed: 0.458 sec. Processed 100.01 million rows, 800.08 MB (218.59 million rows/s., 1.75 GB/s.)

[without custom encoder]

:) select number::int i FROM numbers_mt(1,100000000)t into outfile 't2.parquet'
100000000 rows in set. Elapsed: 2.232 sec. Processed 98.57 million rows, 788.57 MB (44.16 million rows/s., 353.27 MB/s.)

:) insert into function file('t1.parquet') select number::int i FROM numbers_mt(1,100000000)
0 rows in set. Elapsed: 2.285 sec. Processed 100.01 million rows, 800.08 MB (43.76 million rows/s., 350.09 MB/s.)

Maybe your t1.parquet is on a faster storage device than t2.parquet? I don't really know what com.termux is, but https://github.com/termux/termux-packages/wiki/Termux-file-system-layout says "Usually it is a ramdisk".

[l1t1]

t1.parquet and t2.parquet are on the same storage.
btw, how to let clickhouse not to show the formatted sql in each run?
such as

SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 100000000) AS t
INTO OUTFILE '/data/t2.parquet'

[al13n321]

btw, how to let clickhouse not to show the formatted sql in each run?

I just edited it out, there's no way to disable it afaict.

t1.parquet and t2.parquet are on the same storage.

I'm out of ideas for how to reproduce it, so I'm going to ask you to try things on your side:

• Please try using the same path in the two queries (point both queries to /data/, or both to /data/data/com.termux/files/home/, or somewhere else). Just to rule out any differences related to file location.
• Try running the queries multiple times (deleting the files in between, or using different file names) to see if the time is consistent across runs.

[l1t1]

I test on two machines. one is a aarch64 Linux PC, the other is an Android phone. these two sql runs at same speed on the PC just as you mentioned. in phone, their speed is about 1:2, maybe they use different cores of CPU?

root@localhost:/data/data/com.termux/files/home# ./clickhouse local
ClickHouse local version 23.4.1.1073 (official build).

localhost.localdomain :) select number::int i FROM numbers_mt(1,10000000) into outfile 'a1.parquet';

SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 10000000)
INTO OUTFILE 'a1.parquet'

Query id: 80584a1b-cf33-41df-b2e9-c90ab5fb26f1


10000000 rows in set. Elapsed: 1.022 sec. Processed 9.75 million rows, 77.97 MB (9.54 million rows/s., 76.32 MB/s.)

localhost.localdomain :) select number::int i FROM numbers_mt(1,10000000) into outfile 'a2.parquet';

SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 10000000)
INTO OUTFILE 'a2.parquet'

Query id: e719e2eb-1aad-4801-9c9a-285e531243a9


10000000 rows in set. Elapsed: 0.942 sec. Processed 10.00 million rows, 80.00 MB (10.61 million rows/s., 84.89 MB/s.)

localhost.localdomain :) select count() from file('a1.parquet');

SELECT count()
FROM file('a1.parquet')

Query id: 4a84f391-f238-4e71-8be5-2866fe815392

┌──count()─┐
│ 10000000 │
└──────────┘

1 row in set. Elapsed: 0.206 sec. Processed 5.49 million rows, 27.44 MB (26.63 million rows/s., 133.13 MB/s.)

localhost.localdomain :) insert into function file('a3.parquet')select number::int i FROM numbers_mt(1,10000000);

INSERT INTO FUNCTION file('a3.parquet') SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 10000000)

Query id: d0fae8c6-8c5f-44ea-8789-f559a8387c36

Ok.

0 rows in set. Elapsed: 0.372 sec. Processed 10.48 million rows, 83.88 MB (28.20 million rows/s., 225.61 MB/s.)

localhost.localdomain :) insert into function file('a4.parquet')select number::int i FROM numbers_mt(1,10000000);

INSERT INTO FUNCTION file('a4.parquet') SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 10000000)

Query id: 777c7f22-10b5-4431-85c7-cd28fef8daf1

Ok.

0 rows in set. Elapsed: 0.393 sec. Processed 10.48 million rows, 83.88 MB (26.70 million rows/s., 213.62 MB/s.)

localhost.localdomain :) select count() from file('a3.parquet');

SELECT count()
FROM file('a3.parquet')

Query id: 3e1c64ab-a7d2-4f39-a70f-68ae63c7e5fd

┌──count()─┐
│ 10000000 │
└──────────┘

1 row in set. Elapsed: 0.158 sec. Processed 5.95 million rows, 29.74 MB (37.59 million rows/s., 187.93 MB/s.)

localhost.localdomain :) exit
Bye.
root@localhost:/data/data/com.termux/files/home# ll a*par*
-rw------- 1 root root      344 Mar  6 15:18 a.parquet
-rw-r--r-- 1 root root 60298901 Jun  1 04:57 a1.parquet
-rw-r--r-- 1 root root 60298901 Jun  1 04:57 a2.parquet
-rw-r--r-- 1 root root 46966997 Jun  1 04:58 a3.parquet
-rw-r--r-- 1 root root 46966997 Jun  1 04:59 a4.parquet

[al13n321]

Wait, this is 23.4, it's expected to have slower outfile as described above, the file is much bigger. (But the file is <2x bigger, while the query is >2x slower, so idk.)

---

Ok, I'm also getting a somewhat consistent 5% difference when running the query lots of times in parallel:

$ seq 128 | parallel --gnu -j0 "~/t/clickhouse-readat local -q "'"'"insert into function file('t{}.parquet') select number::int i FROM numbers(1,10000000) settings max_threads=1, max_insert_threads=0"'"'"" 
3.846s

$ seq 128 | parallel --gnu -j0 "~/t/clickhouse-readat local -q "'"'"select number::int i FROM numbers(1,10000000) settings max_block_size = 1048449, preferred_block_size_bytes = 268402944, max_threads=1 into outfile 't{}.parquet'"'"'"" 
4.082s

So there's probably an unnecessary data copying somewhere. It can probably be found by comparing cpu profiles.

I'm confused how one (?) extra copy can cause 2x slowdown - surely parquet encoding and writing a file are much more expensive than a memcpy?

A similar effect was reported in #50102 - avoiding 1x mem copy sped up parquet writing by 30% when doing it in lots of threads. I'm similarly confused there.

---

I'm going to give up on this investigation for now and move on to other things, unless this is important for you.

[l1t1]

test version 23.5.1.2378 on a phone, the results is also 1:2

localhost.localdomain :) insert into function file('t-1.parquet') select number::int i FROM numbers_mt(1,10000000);

INSERT INTO FUNCTION file('t-1.parquet') SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 10000000)

Query id: b86cc86b-408a-4d7e-bc98-e72fe89dcc1d

Ok.

0 rows in set. Elapsed: 1.027 sec. Processed 10.01 million rows, 80.06 MB (9.75 million rows/s., 77.98 MB/s.)

localhost.localdomain :) select number::int i FROM numbers_mt(1,10000000)t into outfile 't-2.parquet';

SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 10000000) AS t
INTO OUTFILE 't-2.parquet'

Query id: c6d6126d-c073-40d8-8b8b-9fd6f2472dab


10000000 rows in set. Elapsed: 1.811 sec. Processed 10.00 million rows, 80.00 MB (5.52 million rows/s., 44.18 MB/s.)

localhost.localdomain :) select version();

SELECT version()

Query id: 44a0849c-f510-4cd6-ba4c-a785e8eb9e69

┌─version()───┐
│ 23.5.1.2378 │
└─────────────┘

1 row in set. Elapsed: 0.016 sec.

localhost.localdomain :) select number::int i FROM numbers_mt(1,10000000)t into outfile 't-3.parquet';

SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 10000000) AS t
INTO OUTFILE 't-3.parquet'

Query id: f12d36a2-9eab-48f9-9c1e-64e15a8f54e7


10000000 rows in set. Elapsed: 1.899 sec. Processed 10.00 million rows, 80.00 MB (5.27 million rows/s., 42.14 MB/s.)

localhost.localdomain :) insert into function file('t-4.parquet') select number::int i FROM numbers_mt(1,10000000);

INSERT INTO FUNCTION file('t-4.parquet') SELECT CAST(number, 'int') AS i
FROM numbers_mt(1, 10000000)

Query id: 03ad3845-541a-4ba4-9bf9-a685ce127a54

Ok.

0 rows in set. Elapsed: 1.017 sec. Processed 10.01 million rows, 80.06 MB (9.84 million rows/s., 78.70 MB/s.)