Why does ClickHouse client return multiple tables?

Question

I'm very new to Clickhouse and my first attempts seem to always generate this kind of output for SELECT:

:) select * from test

SELECT *
FROM test 

┌─s───┬───i─┐
│ foo │ 123 │
└─────┴─────┘
┌─s───┬───i─┐
│ bar │ 567 │
└─────┴─────┘

I would expect something like:

┌─s───┬───i─┐
│ foo │ 123 │
│ bar │ 567 │
└─────┴─────┘

Why do I get separate rows ? Is it caused by the table structure ? Here's the structure of the test table:

CREATE TABLE test
(
    s String,
    i UInt64
) ENGINE = Memory

Descry

Related https://mcmap.net/q/2030384/-clickhouse-split-output-on-select/72178 – Chimkent

Answer 1

Background

ClickHouse has a vectorized query execution engine which means when interpreting queries, it consumes data in batches. The definition of a batch is heavily related to the underlying storage engines. Since you are using the Memory engine, it formulates batches per insertion (or split large insertions into separate batches w.r.t the max_insert_block_size setting).

Answer

The reasons that you got separate table rows in the query output are as follows:

• You've inserted two blocks (two INSERT statements) into the test table, thus scanning from it generates two blocks.

• The query you are using doesn't contain any blocking operators (group by, order by, etc) which merge blocks. So the generated blocks are returned one by one.

• The commandline client prints blocks immediately when receiving them.

Concertize

Seems like adding an ORDER BY clause forces the server to collect all results and form a single table result. – Kamalakamaria

Answer 2

By default clickhouse-client uses PrettyCompact output format:

PrettyCompact

Differs from Pretty in that the grid is drawn between rows and the result is more compact. This format is used by default in the command-line client in interactive mode.

If you want to join all output blocks you can use slightly different output format PrettyCompactMonoBlock:

PrettyCompactMonoBlock

Differs from PrettyCompact in that up to 10,000 rows are buffered, then output as a single table, not by blocks.

Chimkent