How to populate AggregatingMergeTree with pre-aggregated data?

[gryphon]

Thanks for a great job!

We are trying to migrate our old pre-aggregated data from our own solution to clickhouse.

We have data as follows (simplified):

• date, domain, views
• 12.01.2017, ya.ru, 234
• 12.01.2017, google.com, 333
• ....

Views is an aggregated column in AggregatingMergeTree. When inserting this data to table clickhouse shows an error that views values are not an aggregating function type.

I think this also could be useful to backup/restore data from AggregatingMergeTree tables.

Please advise.

[ludv1x]

Hi.
The for such purposes usually the following schema is used: main MergeTree table that contains all non-aggregated data + MaterialiedView with AggregatingMergeTree engine viewing on it.
If such scheme is unacceptable for you (there are no need to maintain non-aggregated data), you can insert data to AggregatingMergeTree directly. I used arrayReduce('agg_func', [agg, reg, ating, elements]) for it.

[gryphon]

Of course we have main MergeTree table with non-aggreagated data.

But as we are migrating old data (and we have aggregated data only) we are looking for a way to insert data directly to aggregated table instead main MergeTree table.

[ludv1x]

If I correctly understand the problem is to generate column with type AggregateFunction(func_name) in INSERT query.

If so, you could make an INSERT with VALUES text format like here: https://github.com/yandex/ClickHouse/blob/master/dbms/tests/queries/0_stateless/00432_aggregate_function_scalars_and_constants.sql#L21

[ludv1x]

In your case it should look like:

INSERT INTO agg_table (date, domain, views) VALUES ('2017-12-01', 'ya.ru' arrayReduce('avgSum', [234]));

[ludv1x]

If your data is stored in external storage you can do the following magic via clickhouse-local:

external_storage_get_table_csv_data | clickhouse-local --structure "date Date, domain String, views_ UInt64" --input-format CSV --output-format Native --query "SELECT date, domain, arrayReduce('avgSum', [views_]) AS views" | clickhouse-client -q "INSERT INTO agg_table (date, domain, views) FORMAT Native"

[kkuprikov]

What if we have

│ Visits     │ AggregateFunction(count, UInt64, String, Date, String, UInt64, String, String, String, String) │              │                    │
│ UniqueIP   │ AggregateFunction(uniq, String)

fields?

Trying

INSERT INTO aggregate_domains (record_id, date, account_id, embed_url, Visits, UniqueIP) VALUES (1231234, '2017-12-01', 545455, 'ya.ru', arrayReduce('countState', [7744, 7744]));

INSERT INTO aggregate_domains (record_id, date, account_id, embed_url, Visits, UniqueIP) VALUES


Exception on client:
Code: 36. DB::Exception: Using aggregate function with -State modifier in function arrayReduce is not supported

[ludv1x]

Trying

INSERT INTO aggregate_domains (record_id, date, account_id, embed_url, Visits, UniqueIP) VALUES (1231234, '2017-12-01', 545455, 'ya.ru', arrayReduce('countState', [7744, 7744]));

INSERT INTO aggregate_domains (record_id, date, account_id, embed_url, Visits, UniqueIP) VALUES

Exception on client:
Code: 36. DB::Exception: Using aggregate function with -State modifier in function arrayReduce is not supported

It seems that you use outdated version.
You need at least ClickHouse 1.1.54165.

SELECT arrayReduce('countMerge', [arrayReduce('countState', [1, 10])])

┌─arrayReduce(\'countMerge\', array(arrayReduce(\'countState\', [1, 10])))─┐
│                                                                        2 │
└──────────────────────────────────────────────────────────────────────────┘

[kkuprikov]

Thanks for your feedback!

However, after update I still can't proceed:

Connected to ClickHouse server version 1.1.54213.

:) INSERT INTO aggregate_domains (record_id, date, account_id, embed_url, Visits, UniqueIP) VALUES (1231234, '2017-12-01', 545455, 'ya.ru', arrayReduce('countState', [1]), arrayReduce('uniqState', [2]));

INSERT INTO aggregate_domains (record_id, date, account_id, embed_url, Visits, UniqueIP) VALUES


Exception on client:
Code: 53. DB::Exception: Type mismatch in IN or VALUES section. Expected: AggregateFunction(count, UInt64, String, Date, String, UInt64, String, String, String, String). Got: String

[kkuprikov]

What determines the content of arrayReduce arguments?

[ludv1x]

arrayReduce('countState', [1]) can return only scalar of AggregateFunction(count) type.

So you can just change type of column `Visits` to `AggregateFunction(count)`.

[ludv1x]

ClickHouse's types AggregateFunction(count) and AggregateFunction(count, smth1, smth2, ...) do the same (it is not SQL compatible).

It is not actual, now ClickHouse's count() works the same as SQL count() (i.e. counts not NULL rows).

You could call arrayReduce('countState', [toUInt64(1), toUInt64(1)] , ['a', 'a'], [today(), today()]) with to get more sophisticated types like AggregateFunction(count, UInt64, String, Date).

But in your case it is better to use sumState (with type AggregateFunction(sum)) instead of countState.
countState will be cumbersome: you need to pass 100-elem array to generate value 100 instead of [100] in case of sum.

[kkuprikov]

Definitely, this is the right way. Thanks a lot!

So the last question is, what's wrong with clickhouse-local magic:

web@stat-test1:~$ cat example_stat.csv
account_id,date,record_id,embed_url,visits_,uniq_
123,"2017-12-01",265717,"eagle.ya.ru",337,22
221,"2017-04-03",221221,"ololo.ru",228,818

web@stat-test1:~$ cat example_stat.csv | clickhouse-local --structure="account_id UInt64, date Date, record_id UInt64, embed_url String, visits_ UInt64, uniq_ UInt64" --input-format CSV --output-format Native --query "SELECT *"
Read 1 rows, 1.00 B in 0.001 sec., 800 rows/sec., 800.38 B/sec.

dummyUInt8web@stat-test1:~$ cat example_stat.csv | clickhouse-local --structure="account_id UInt64, date Date, record_id UInt64, embed_url String, visits_ UIn UInt64" --input-format CSV --output-format Native --query "SELECT account_id"
Code: 47. DB::Exception: Unknown identifier: account_id

It seems like any identifier in --query "SELECT ..." can't be setup

[ludv1x]

So the last question is, what's wrong with clickhouse-local magic:
dummyUInt8web@stat-test1:~$ cat example_stat.csv | clickhouse-local --structure="account_id UInt64, date Date, record_id UInt64, embed_url String, visits_ UIn UInt64" --input-format CSV --output-format Native --query "SELECT account_id"
Code: 47. DB::Exception: Unknown identifier: account_id

Use --query "SELECT account_id FROM table"

[kkuprikov]

Yeah, this one is working now. TY