Projections by amosbird · Pull Request #20202 · ClickHouse/ClickHouse

amosbird · 2021-02-08T10:20:05Z

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

Changelog category (leave one):

New Feature

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

Add projection support for MergeTree* tables.

Detailed description / Documentation draft:

The documentation is almost listed in #14730

Possible TODOs.

Complete query analysis for projections.
Normal projections with different order by clause
~~ProjectionMergeTree to store projection data only~~ can be done via CollapsingMergeTree with default sign = 1
Secondary indices via projection

alexey-milovidov · 2021-02-09T23:14:11Z

Some exception occurred :(

GithubException: 500 null
This is GitHub downtime, https://www.githubstatus.com/history

Need to restart the tests.

zhaojintaozhao · 2021-02-21T12:24:02Z

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

Changelog category (leave one):

New Feature

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

Add projection support for MergeTree* tables.

Detailed description / Documentation draft:

The documentation is almost listed in #14730

Possible TODOs.

Complete query routing for projections. Currently alias in group-by will not work.

Normal projections with different order by clause

ProjectionMergeTree to store projection data only

Secondary indices via projection

@amosbird :
This is a very valuable feature;
I have some questions about projects.

What is the difference between projections and materialized views?
Does projections also use the idea of kylin cube? Pre-aggregate the query results in advance. The aggregated results are directly queried during query. Projections: Are the combinations of all dimensions calculated in advance?

itzikiusa · 2021-02-28T16:54:53Z

Long expected feature!
Waiting for PR to be approved, and the following steps, specially different order by
Thank you very much for your work!

amosbird · 2021-05-06T02:15:31Z

2021-05-05 23:51:05 00992_system_parts_race_condition_zookeeper_long:                       Received exception from server (version 21.6.1):
2021-05-05 23:51:05 Code: 999. DB::Exception: Received from localhost:9000. DB::Exception: Run time inconsistency, path: /clickhouse/task_queue/ddl/query-0000003330. 
2021-05-05 23:51:06 Received exception from server (version 21.6.1):
2021-05-05 23:51:06 Code: 81. DB::Exception: Received from localhost:9000. DB::Exception: There was an error on [localhost:19000]: Code: 81, e.displayText() = DB::Exception: Database test_06rrwt doesn't exist (version 21.6.1.6765). 
2021-05-05 23:51:06 [ FAIL ] 4.53 sec. - return code 81
2021-05-05 23:51:06 Code: 81. DB::Exception: Received from localhost:9000. DB::Exception: Database `default` doesn't exist. 
2021-05-05 23:51:06 
2021-05-05 23:51:06 , result:
2021-05-05 23:51:06 
2021-05-05 23:51:06

amosbird · 2021-05-06T04:45:44Z

Integration tests (asan) — Timeout, fail: 0, passed: 1314, flaky: 1

What does this mean? (stably reproduced in last three commits)

TODO (suggested by Nikolai) 1. Build query plan fro current query (inside storage::read) up to WithMergableState 2. Check, that plan is simple enough: Aggregating - Expression - Filter - ReadFromStorage (or simplier) 3. Check, that filter is the same as filter in projection, and also expression calculates the same aggregation keys as in projection 4. Return WithMergableState if projection applies 3 will be easier to do with ActionsDAG, cause it sees all functions, and dependencies are direct (but it is possible with ExpressionActions also) Also need to figure out how prewhere works for projections, and row_filter_policies. wip

KochetovNicolai · 2021-05-11T15:57:56Z

Currently, most of mentioned in #14730 (comment) is implemented. Exceptions are: WHERE in projection definition and everything from Projection Analysis and Settings.

This feature is experimental. Enable allow_experimental_projection_optimization to use projections in SELECT.
Projection with ORDER BY uses it's own primary key when selected. Read-in-order optimization also supported.
Projection with GROUP BY uses stored pre-aggregated data.

Alter update/delete will rebuild projection if any column used by projection is affected.

There is still not enough tests. Also, not all the code was thoughtfully reviewed. However, I am going to merge this pr, because the feature works in general (and to avoid major conflicts).

amosbird · 2021-05-12T02:57:37Z

https://clickhouse-test-reports.s3.yandex.net/20202/817bc1377c131e83d13bbd90260f8f2d97856bfd/integration_tests_(release)/integration_run_test_cluster_copier_test_py_1.txt

cluster copier test throws code 85: FORMAT_IS_NOT_SUITABLE_FOR_INPUT

amosbird · 2021-05-12T03:05:12Z

https://clickhouse-test-reports.s3.yandex.net/20202/817bc1377c131e83d13bbd90260f8f2d97856bfd/integration_tests_(thread)/integration_run_test_jbod_balancer_test_py_0.txt

insert timeout

tests/queries/skip_list.json

alexey-milovidov · 2021-05-23T23:53:35Z

@KochetovNicolai There is no comment about MergeTreeDataSelectCache.

You've missed the case when PVS-Studio argued about uncomprehensible code:

PVS check — Have 7 new errors, 21 total errors

https://clickhouse-test-reports.s3.yandex.net/20202/817bc1377c131e83d13bbd90260f8f2d97856bfd/pvs_studio_report/pvs-studio-html-report/sources/MergeTreeDataSelectExecutor.cpp_13.html#ln583

The code is correct but it becomes too tangled, which PVS-Studio does not like.

alexey-milovidov · 2021-05-23T23:54:25Z

We need clear explanation - what MergeTreeDataSelectCache is doing, what's going on in the code...

olgarev · 2021-08-15T12:27:23Z

Internal ticket DOCSUP-12894

zhouerlin · 2022-01-11T03:25:39Z

Currently, projection does not support the where clause,
What is the difficulty in implementing it or for what purpose it was designed to do so? @amosbird

In ClickHouse#20202 DROP COLUMN had been made no-op, i.e. it simply hardlinked everything and no columns had been removed. In most of the cases this is OK, however Vertical merge may inject another column instead of current if it does not exists (injectRequiredColumns(LoadedMergeTreeDataPartInfoForReader{}) in MergeTreeSequentialSource ctor), and it may select this already removed column and it will fail with an error: DB::Exception: There is no column legacy_features_Map.count in table. (NO_SUCH_COLUMN_IN_TABLE), Stack trace (when copying this message, always include the lines below): 0. src/Common/Exception.cpp:91: DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe0c67d5 in /usr/lib/debug/usr/bin/clickhouse.debug 2. src/Storages/StorageSnapshot.cpp:111: DB::StorageSnapshot::getColumn(DB::GetColumnsOptions const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&) const @ 0x13ff1cb3 in /usr/lib/debug/usr/bin/clickhouse.debug 3. contrib/llvm-project/libcxx/include/new:246: DB::StorageSnapshot::getColumnsByNames(DB::GetColumnsOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>> const&) const @ 0x13ff19f1 in /usr/lib/debug/usr/bin/clickhouse.debug 4. src/Storages/MergeTree/MergeTreeSequentialSource.cpp:0: DB::MergeTreeSequentialSource::MergeTreeSequentialSource(DB::MergeTreeData const&, std::__1::shared_ptr<DB::StorageSnapshot> const&, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>>, std::__1::optional<DB::MarkRanges>, bool, bool, bool, bool) @ 0x143d108c in /usr/lib/debug/usr/bin/clickhouse.debug 5. contrib/llvm-project/libcxx/include/__memory/construct_at.h:35: DB::createMergeTreeSequentialSource(DB::MergeTreeData const&, std::__1::shared_ptr<DB::StorageSnapshot> const&, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>>, bool, bool, bool, std::__1::shared_ptr<std::__1::atomic<unsigned long>>) @ 0x143d25ee in /usr/lib/debug/usr/bin/clickhouse.debug 6. contrib/llvm-project/libcxx/include/vector:434: DB::MergeTask::VerticalMergeStage::prepareVerticalMergeForOneColumn() const @ 0x1422c525 in /usr/lib/debug/usr/bin/clickhouse.debug Such merges can be distinguished with the following message in logs: Part X doesn't change up to mutation version Y (optimized) ^^^^^^^^^^^ Funny that this has been accidentally fixed in the PR with the title "Remove strange code from MutateTask" (ClickHouse#48666) Signed-off-by: Azat Khuzhin <[email protected]>

robot-clickhouse added doc-alert pr-feature Pull request with new product feature labels Feb 8, 2021

alexey-milovidov mentioned this pull request Feb 8, 2021

Roadmap 2021 (discussion) #17623

Closed

amosbird force-pushed the projection branch from b2cbc92 to d5af8bd Compare February 8, 2021 16:50

amosbird force-pushed the projection branch 3 times, most recently from e26b6f9 to 6754a41 Compare February 13, 2021 10:41

alexey-milovidov assigned KochetovNicolai Feb 17, 2021

amosbird force-pushed the projection branch from 4ec7ec4 to 6080e9f Compare February 17, 2021 17:03

mdianjun mentioned this pull request Feb 23, 2021

clickhouse的问题梳理 cloudnativecube/octopus#20

Open

amosbird mentioned this pull request Mar 15, 2021

【Timing trigger】How to routinely delay statistics 3min before specifying the mean value and quantile value of the aggregated latitude #21660

Closed

amosbird force-pushed the projection branch from 4a33bf8 to c52a828 Compare March 19, 2021 12:22

amosbird force-pushed the projection branch from 6b6dc89 to e9161fc Compare April 22, 2021 14:27

amosbird mentioned this pull request May 2, 2021

Feature: flexible merging of rows via SQL query #4419

Open

amosbird force-pushed the projection branch from b0ab7cc to 1645040 Compare May 3, 2021 07:29

amosbird added the force tests label May 4, 2021

amosbird force-pushed the projection branch 2 times, most recently from 8d7eb80 to b258cbc Compare May 11, 2021 08:47

amosbird and others added 7 commits May 11, 2021 18:12

add metadata_snapshot to getQueryProcessingStage

cd64146

Try to merge projectons faster.

3296c92

Cleanup code.

3d36fb5

Fix prewhere.

427f444

Check if pipeline is simple and add more comments

483bd16

Try to enable normal projections.

9543116

amosbird and others added 10 commits May 11, 2021 18:12

Fix tests

a3fd0b6

More tests.

b736515

Fix tests

ba17acb

Fix more tests

718c284

More fixes

ddd4256

Even more fixes

51d685b

Remove projection type ast

dfa5629

Fix issues in normal projection

5836e2e

Comment debug info out

5e9fab6

Fix

074c2ea

amosbird force-pushed the projection branch from b258cbc to 074c2ea Compare May 11, 2021 10:22

KochetovNicolai added 4 commits May 11, 2021 14:44

fix fetch for projection.

89f1601

Remove unused code.

a7d717d

Try fix test.

0e469f3

Fix part check for projections.

817bc13

KochetovNicolai merged commit a1ba67e into ClickHouse:master May 12, 2021

abyss7 reviewed May 17, 2021

View reviewed changes

tests/queries/skip_list.json Show resolved Hide resolved

olgarev mentioned this pull request Aug 16, 2021

DOCSUP-12894: Projections settings #27725

Merged

den-crane mentioned this pull request Nov 1, 2021

two questions: 1、Which lts version is supported to projection? 2、 Timeout to modify a large table field like(Session expired) #30953

Closed

azat mentioned this pull request Aug 9, 2023

Add a regression test for broken Vertical merge after ADD+DROP COLUMN #53214

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Projections#20202

Projections#20202
KochetovNicolai merged 34 commits intoClickHouse:masterfrom
amosbird:projection

amosbird commented Feb 8, 2021 •

edited

Loading

Uh oh!

alexey-milovidov commented Feb 9, 2021

Uh oh!

zhaojintaozhao commented Feb 21, 2021

Uh oh!

itzikiusa commented Feb 28, 2021

Uh oh!

amosbird commented May 6, 2021

Uh oh!

amosbird commented May 6, 2021 •

edited

Loading

Uh oh!

KochetovNicolai commented May 11, 2021 •

edited

Loading

Uh oh!

amosbird commented May 12, 2021

Uh oh!

amosbird commented May 12, 2021

Uh oh!

Uh oh!

alexey-milovidov commented May 23, 2021

Uh oh!

alexey-milovidov commented May 23, 2021

Uh oh!

olgarev commented Aug 15, 2021

Uh oh!

zhouerlin commented Jan 11, 2022 •

edited

Loading

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

9 participants

Conversation

amosbird commented Feb 8, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

alexey-milovidov commented Feb 9, 2021

Uh oh!

zhaojintaozhao commented Feb 21, 2021

Uh oh!

itzikiusa commented Feb 28, 2021

Uh oh!

amosbird commented May 6, 2021

Uh oh!

amosbird commented May 6, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

KochetovNicolai commented May 11, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

amosbird commented May 12, 2021

Uh oh!

amosbird commented May 12, 2021

Uh oh!

Uh oh!

alexey-milovidov commented May 23, 2021

Uh oh!

alexey-milovidov commented May 23, 2021

Uh oh!

olgarev commented Aug 15, 2021

Uh oh!

zhouerlin commented Jan 11, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

9 participants

amosbird commented Feb 8, 2021 •

edited

Loading

amosbird commented May 6, 2021 •

edited

Loading

KochetovNicolai commented May 11, 2021 •

edited

Loading

zhouerlin commented Jan 11, 2022 •

edited

Loading