Comments (6)
den-crane
commented on June 5, 2024
Merges happen only within partition. toYYYYMM(created_date) - if your rows are in different months then they never be collapsed.
https://fiddle.clickhouse.com/65cff341-5773-487a-9b3c-ac72ecc49dee
CREATE TABLE users (uid Int16, create_date DateTime, s String)
ENGINE=ReplacingMergeTree
Partition by toYYYYMM(create_date)
order by uid;
INSERT INTO users VALUES (1231, '2020-01-01', 's1');
INSERT INTO users VALUES (1231, '2022-01-01', 's2');
SELECT * FROM users final format PrettyCompactMonoBlock;
+--uid-+---------create_date-+-s--+
1. | 1231 | 2022-01-01 00:00:00 | s2 | -- SELECT FINAL merges rows across partitions
+------+---------------------+----+
optimize table users final;
SELECT * FROM users format PrettyCompactMonoBlock;
+--uid-+---------create_date-+-s--+
1. | 1231 | 2020-01-01 00:00:00 | s1 | -- optimize FINAL does not merge rows across partitions
2. | 1231 | 2022-01-01 00:00:00 | s2 | -- optimize FINAL does not merge rows across partitions
+------+---------------------+----+from clickhouse.
traceless
commented on June 5, 2024
Merges happen only within partition.
toYYYYMM(created_date)- if your rows are in different months then they never be collapsed.https://fiddle.clickhouse.com/65cff341-5773-487a-9b3c-ac72ecc49dee
CREATE TABLE users (uid Int16, create_date DateTime, s String) ENGINE=ReplacingMergeTree Partition by toYYYYMM(create_date) order by uid; INSERT INTO users VALUES (1231, '2020-01-01', 's1'); INSERT INTO users VALUES (1231, '2022-01-01', 's2'); SELECT * FROM users final format PrettyCompactMonoBlock; +--uid-+---------create_date-+-s--+ 1. | 1231 | 2022-01-01 00:00:00 | s2 | -- SELECT FINAL merges rows across partitions +------+---------------------+----+ optimize table users final; SELECT * FROM users format PrettyCompactMonoBlock; +--uid-+---------create_date-+-s--+ 1. | 1231 | 2020-01-01 00:00:00 | s1 | -- optimize FINAL does not merge rows across partitions 2. | 1231 | 2022-01-01 00:00:00 | s2 | -- optimize FINAL does not merge rows across partitions +------+---------------------+----+
i’m sorry, I didn't take a closer look at your year (create_date) , here is my order data:
My doubt
The year, month, and day of creation are all the same, event the time, minute, and second are the same, but they are still not merged.from clickhouse.
traceless
commented on June 5, 2024
Merges happen only within partition.
toYYYYMM(created_date)- if your rows are in different months then they never be collapsed.https://fiddle.clickhouse.com/65cff341-5773-487a-9b3c-ac72ecc49dee
CREATE TABLE users (uid Int16, create_date DateTime, s String) ENGINE=ReplacingMergeTree Partition by toYYYYMM(create_date) order by uid; INSERT INTO users VALUES (1231, '2020-01-01', 's1'); INSERT INTO users VALUES (1231, '2022-01-01', 's2'); SELECT * FROM users final format PrettyCompactMonoBlock; +--uid-+---------create_date-+-s--+ 1. | 1231 | 2022-01-01 00:00:00 | s2 | -- SELECT FINAL merges rows across partitions +------+---------------------+----+ optimize table users final; SELECT * FROM users format PrettyCompactMonoBlock; +--uid-+---------create_date-+-s--+ 1. | 1231 | 2020-01-01 00:00:00 | s1 | -- optimize FINAL does not merge rows across partitions 2. | 1231 | 2022-01-01 00:00:00 | s2 | -- optimize FINAL does not merge rows across partitions +------+---------------------+----+
Hello ,i get the error log.
(version 23.8.10.43 (official build)) │ {'FileOpen':20,'Seek':2,'ReadBufferFromFileDescriptorRead':25,'ReadBufferFromFileDescriptorReadBytes':2443738,'WriteBufferFromFileDescriptorWrite':1,'WriteBufferFromFileDescriptorWriteBytes':1049,'ReadCompressedBytes':2443687,'CompressedReadBufferBlocks':89,'CompressedReadBufferBytes':9365655,'OpenedFileCacheMisses':6,'OpenedFileCacheMicroseconds':13,'IOBufferAllocs':66,'IOBufferAllocBytes':17263188,'CreatedReadBufferOrdinary':6,'DiskReadElapsedMicroseconds':575,'DiskWriteElapsedMicroseconds':31,'ExternalProcessingFilesTotal':1,'WaitMarksLoadMicroseconds':1,'Merge':33,'MergedRows':228091,'MergedUncompressedBytes':13015111,'MergesTimeMilliseconds':80,'ContextLock':20,'RWLockAcquiredReadLocks':1,'PartsLockHoldMicroseconds':17,'CannotWriteToWriteBufferDiscard':9,'QueryProfilerRuns':1,'LogTrace':3,'LogDebug':4} │
│ │ MergeParts │ RegularMerge │ Vertical │ 2024-03-24 │ 2024-03-24 11:23:22 │ 2024-03-24 11:23:22.958153 │ 130 │ real_renpho_dc │ dc_amazon_orders │ 5039ee91-9a3d-4860-b8a6-0c94c40e906a │ 202403_0_737533_10509 │ 202403 │ │ Unknown │ │ │ 228033 │ 0 │ ['202403_0_737475_10508','202403_737476_737533_11'] │ 17909760 │ 228091 │ 13015111 │ 22700598 │ 49 │ Code: 49. DB::Exception: Number of rows in source parts (228091) excluding filtered rows (0) differs from number of bytes written to rows_sources file (228077). It is a bug. (LOGICAL_ERROR), Stack trace (when copying this message, always include the lines below):
0. ./build_docker/./src/Common/Exception.cpp:98: DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c621337 in /usr/lib/debug/usr/bin/clickhouse.debug
1. ./build_docker/./contrib/llvm-project/libcxx/include/string:1499: DB::Exception::Exception<unsigned long&, unsigned long&, unsigned long&>(int, FormatStringHelperImpl<std::type_identity<unsigned long&>::type, std::type_identity<unsigned long&>::type, std::type_identity<unsigned long&>::type>, unsigned long&, unsigned long&, unsigned long&) @ 0x000000000f392454 in /usr/lib/debug/usr/bin/clickhouse.debug
2. ./build_docker/./src/Storages/MergeTree/MergeTask.cpp:502: DB::MergeTask::VerticalMergeStage::prepareVerticalMergeForAllColumns() const @ 0x0000000012bb566f in /usr/lib/debug/usr/bin/clickhouse.debug
3. ./build_docker/./contrib/llvm-project/libcxx/include/__functional/function.h:716: bool std::__function::__policy_invoker<bool ()>::__call_impl<std::__function::__default_alloc_func<DB::MergeTask::VerticalMergeStage::subtasks::'lambda'(), bool ()>>(std::__function::__policy_storage const*) @ 0x0000000012bbe169 in /usr/lib/debug/usr/bin/clickhouse.debug
4. ./build_docker/./contrib/llvm-project/libcxx/include/__functional/function.h:848: DB::MergeTask::VerticalMergeStage::execute() @ 0x0000000012bba34b in /usr/lib/debug/usr/bin/clickhouse.debug
5. ./build_docker/./src/Storages/MergeTree/MergeTask.cpp:0: DB::MergeTask::execute() @ 0x0000000012bb9d79 in /usr/lib/debug/usr/bin/clickhouse.debug
6. ./build_docker/./src/Storages/MergeTree/ReplicatedMergeMutateTaskBase.cpp:0: DB::ReplicatedMergeMutateTaskBase::executeStep() @ 0x0000000012e7692e in /usr/lib/debug/usr/bin/clickhouse.debug
7. ./build_docker/./src/Storages/MergeTree/MergeTreeBackgroundExecutor.cpp:273: DB::MergeTreeBackgroundExecutor<DB::DynamicRuntimeQueue>::threadFunction() @ 0x0000000012bcb679 in /usr/lib/debug/usr/bin/clickhouse.debug
8. ./build_docker/./base/base/../base/wide_integer_impl.h:809: ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>) @ 0x000000000c6ffe9e in /usr/lib/debug/usr/bin/clickhouse.debug
9. ./build_docker/./src/Common/ThreadPool.cpp:0: void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::function<void ()>, Priority, std::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'(), void ()>>(std::__function::__policy_storage const*) @ 0x000000000c70395c in /usr/lib/debug/usr/bin/clickhouse.debug
10. ./build_docker/./base/base/../base/wide_integer_impl.h:809: void* std::__thread_proxy[abi:v15000]<std::tuple<std::unique_ptr<std::__thread_struct, std::default_delete<std::__thread_struct>>, void ThreadPoolImpl<std::thread>::scheduleImpl<void>(std::function<void ()>, Priority, std::optional<unsigned long>, bool)::'lambda0'()>>(void*) @ 0x000000000c7021a4 in /usr/lib/debug/usr/bin/clickhouse.debug
11. start_thread @ 0x000000000000744b in /usr/lib64/libpthread-2.26.so
12. __clone @ 0x00000000000ef52f in /usr/lib64/libc-2.26.so
I can't figure out the reason for this exception.
from clickhouse.
nickitat
commented on June 5, 2024
usage of final cleanup is discouraged. to remove duplicates simple final is enough.
from clickhouse.
traceless
commented on June 5, 2024
usage of
final cleanupis discouraged. to remove duplicates simplefinalis enough.
But I have tried many times as you said, but it not collapse duplicates. Do you have any opinion on these exception logs.
from clickhouse.
traceless
commented on June 5, 2024
I have transferred this issue here, this issue is a bug.
from clickhouse.
Related Issues (20)
- Unexpected return type from equals: Grouping sets and predicate pushdown HOT 1
- OPTIMIZE TABLE: When to use it? HOT 6
- session_timezone is not reflected in X-ClickHouse-Timezone HOT 8
- What options exist for storing metadata when using S3 as a primary disk? HOT 2
- Docker container with image of clickhouse/clickhouse-server immediately exits on WSL2 with Ubuntu 24.04 HOT 3
- Add UUID to the system.columns HOT 1
- INSERT INTO from cli client loading data from large local file on fast host has socket connection problem/errors out HOT 1
- Casting a Map to an Array of Tuple
- GPU support HOT 2
- how to download clickhouse a single stable binary HOT 3
- Error with enable new analyzer: "Not found column __table1.name in block" HOT 7
- Missing past releases Documentation HOT 1
- Keep a legend inside a graph pane on dashboards
- Add 64 bit version of parseDateTime (parseDateTime64) HOT 4
- Function initCap treats an apostrophe as a word separator HOT 1
- JSON read/import fails when a value is "-" and schema reflect datetime, not a string as expected
- Cancellable tasks in ThreadPools
- Replacing-AggregatingMergeTree? HOT 4
- `os_user` and `client_hostname` are not set in the Suggest query in clickhouse-client
- unification of is_deleted for RMT with LWD-based _row_exists HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from clickhouse.