Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Error inserting data with window view: Not found column in block #63575

Open
ivesshao-lb opened this issue May 9, 2024 · 3 comments
Open

Error inserting data with window view: Not found column in block #63575

ivesshao-lb opened this issue May 9, 2024 · 3 comments
Labels
experimental feature Bug in the feature that should not be used in production question Question?

Comments

@ivesshao-lb
Copy link

ivesshao-lb commented May 9, 2024 •
edited

-- meta_trade_20.tb_tick definition Source data table
CREATE TABLE meta_trade_20.tb_tick
(
symbol String,
ask Decimal(20,10),
bid Decimal(20,10),
ctm DateTime
)
ENGINE = MergeTree
ORDER BY (symbol,ctm);

-- meta_trade_20.kline_test definition Target data table
CREATE TABLE meta_trade_20.kline_test
(
symbol String,
open_price Decimal(20,10) DEFAULT 0.,
high_price Decimal(20,10) DEFAULT 0.,
low_price Decimal(20,10) DEFAULT 0.,
close_price Decimal(20,10) DEFAULT 0.,
trade_time DateTime
)
ENGINE = AggregatingMergeTree
ORDER BY (symbol,trade_time)
SETTINGS index_granularity = 8192;

-- meta_trade_20.kline_m1_view View Definition
CREATE WINDOW VIEW meta_trade_20.kline_m1_view TO meta_trade_20.kline_test
(
symbol String,
open_price Decimal(20,10),
high_price Decimal(20,10),
low_price Decimal(20,10),
close_price Decimal(20,10),
trade_time DateTime
) WATERMARK toIntervalSecond(2) AS
SELECT
symbol,
any(t.bid) AS open_price,
max(t.bid) AS high_price,
min(t.bid) AS low_price,
anyLast(t.bid) AS close_price,
tumbleStart(t_window) AS trade_time
FROM meta_trade_20.tb_tick AS t
GROUP BY
symbol,
tumble(toDateTime(t.ctm),toIntervalMinute(1)) AS t_window
ORDER BY
symbol ASC,
trade_time ASC;

Error message when inserting data into the source data table：
0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c6d5d7b in /usr/bin/clickhouse

  1. DB::Exception::Exception<String const&, String>(int, FormatStringHelperImpl<std::type_identity<String const&>::type, std::type_identity::type>, String const&, String&&) @ 0x0000000007b70607 in /usr/bin/clickhouse
  2. DB::Block::getByName(String const&, bool) const @ 0x000000001035504e in /usr/bin/clickhouse
  3. DB::StorageWindowView::writeIntoWindowView(DB::StorageWindowView&, DB::Block const&, std::shared_ptr<DB::Context const>) @ 0x00000000124bfe3a in /usr/bin/clickhouse
  4. DB::PushingToWindowViewSink::consume(DB::Chunk) @ 0x0000000012a0c184 in /usr/bin/clickhouse
  5. DB::SinkToStorage::onConsume(DB::Chunk) @ 0x0000000012a34242 in /usr/bin/clickhouse
  6. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<DB::ExceptionKeepingTransform::work()::$_1, void ()>>(std::__function::__policy_storage const*) @ 0x000000001296a0cb in /usr/bin/clickhouse
  7. DB::runStep(std::function<void ()>, DB::ThreadStatus*, std::atomic*) @ 0x0000000012969ddc in /usr/bin/clickhouse
  8. DB::ExceptionKeepingTransform::work() @ 0x00000000129694b3 in /usr/bin/clickhouse
  9. DB::ExecutionThreadContext::executeTask() @ 0x00000000126ebd7a in /usr/bin/clickhouse
  10. DB::PipelineExecutor::executeStepImpl(unsigned long, std::atomic*) @ 0x00000000126e27b0 in /usr/bin/clickhouse
  11. DB::PipelineExecutor::execute(unsigned long, bool) @ 0x00000000126e19c0 in /usr/bin/clickhouse
  12. DB::CompletedPipelineExecutor::execute() @ 0x00000000126e01b2 in /usr/bin/clickhouse
  13. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::shared_ptrDB::Context, std::function<void (DB::QueryResultDetails const&)>, DB::QueryFlags, std::optionalDB::FormatSettings const&, std::function<void (DB::IOutputFormat&)>) @ 0x000000001172a55b in /usr/bin/clickhouse
  14. DB::HTTPHandler::processQuery(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::optionalDB::CurrentThread::QueryScope&) @ 0x0000000012616f8d in /usr/bin/clickhouse
  15. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0x000000001261bdb6 in /usr/bin/clickhouse
  16. DB::HTTPServerConnection::run() @ 0x0000000012696c12 in /usr/bin/clickhouse
  17. Poco::Net::TCPServerConnection::start() @ 0x00000000150f4e52 in /usr/bin/clickhouse
  18. Poco::Net::TCPServerDispatcher::run() @ 0x00000000150f5c51 in /usr/bin/clickhouse
  19. Poco::PooledThread::run() @ 0x00000000151ece67 in /usr/bin/clickhouse
  20. Poco::ThreadImpl::runnableEntry(void*) @ 0x00000000151eb45c in /usr/bin/clickhouse
  21. ? @ 0x00007ff021694ac3 in ?
  22. ? @ 0x00007ff021726850 in ?
    (version 23.12.2.59 (official build))
@ivesshao-lb ivesshao-lb added the question Question? label May 9, 2024
@ivesshao-lb ivesshao-lb changed the title When I create a window view, an error occurs when inserting data into the source data table. When I create a window view, an error occurs when inserting data into the source data table.DB::Exception: Not found column in block. There are only columns: symbol, ask, bid, ctm: while pushing to view meta_trade_20.kline_m1_view (e3587bad-9e45-45cd-b5ce-4e7edb25feff). (NOT_FOUND_COLUMN_IN_BLOCK) (version 23.12.2.59 (official build)) May 9, 2024
@Algunenano Algunenano added the experimental feature Bug in the feature that should not be used in production label May 9, 2024
@Algunenano Algunenano changed the title When I create a window view, an error occurs when inserting data into the source data table.DB::Exception: Not found column in block. There are only columns: symbol, ask, bid, ctm: while pushing to view meta_trade_20.kline_m1_view (e3587bad-9e45-45cd-b5ce-4e7edb25feff). (NOT_FOUND_COLUMN_IN_BLOCK) (version 23.12.2.59 (official build)) Error inserting data with window view: Not found column in block May 9, 2024
@ivesshao-lb
Copy link
Author

Supplementary error information：
2024.05.09 16:03:15.285177 [ 107900 ] {fcf1e2b1-fe66-423d-a301-69e18fe5d985} DynamicQueryHandler: Code: 10. DB::Exception: Not found column in block. There are only columns: symbol, ask, bid, ctm: while pushing to view meta_trade_20.kline_m1_view (e3587bad-9e45-45cd-b5ce-4e7edb25feff). (NOT_FOUND_COLUMN_IN_BLOCK), Stack trace (when copying this message, always include the lines below):

  1. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c6d5d7b in /usr/bin/clickhouse
  2. DB::Exception::Exception<String const&, String>(int, FormatStringHelperImpl<std::type_identity<String const&>::type, std::type_identity::type>, String const&, String&&) @ 0x0000000007b70607 in /usr/bin/clickhouse
  3. DB::Block::getByName(String const&, bool) const @ 0x000000001035504e in /usr/bin/clickhouse
  4. DB::StorageWindowView::writeIntoWindowView(DB::StorageWindowView&, DB::Block const&, std::shared_ptr<DB::Context const>) @ 0x00000000124bfe3a in /usr/bin/clickhouse
  5. DB::PushingToWindowViewSink::consume(DB::Chunk) @ 0x0000000012a0c184 in /usr/bin/clickhouse
  6. DB::SinkToStorage::onConsume(DB::Chunk) @ 0x0000000012a34242 in /usr/bin/clickhouse
  7. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<DB::ExceptionKeepingTransform::work()::$_1, void ()>>(std::__function::__policy_storage const*) @ 0x000000001296a0cb in /usr/bin/clickhouse
  8. DB::runStep(std::function<void ()>, DB::ThreadStatus*, std::atomic*) @ 0x0000000012969ddc in /usr/bin/clickhouse
  9. DB::ExceptionKeepingTransform::work() @ 0x00000000129694b3 in /usr/bin/clickhouse
  10. DB::ExecutionThreadContext::executeTask() @ 0x00000000126ebd7a in /usr/bin/clickhouse
  11. DB::PipelineExecutor::executeStepImpl(unsigned long, std::atomic*) @ 0x00000000126e27b0 in /usr/bin/clickhouse
  12. DB::PipelineExecutor::execute(unsigned long, bool) @ 0x00000000126e19c0 in /usr/bin/clickhouse
  13. DB::CompletedPipelineExecutor::execute() @ 0x00000000126e01b2 in /usr/bin/clickhouse
  14. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::shared_ptrDB::Context, std::function<void (DB::QueryResultDetails const&)>, DB::QueryFlags, std::optionalDB::FormatSettings const&, std::function<void (DB::IOutputFormat&)>) @ 0x000000001172a55b in /usr/bin/clickhouse
  15. DB::HTTPHandler::processQuery(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::optionalDB::CurrentThread::QueryScope&) @ 0x0000000012616f8d in /usr/bin/clickhouse
  16. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0x000000001261bdb6 in /usr/bin/clickhouse
  17. DB::HTTPServerConnection::run() @ 0x0000000012696c12 in /usr/bin/clickhouse
  18. Poco::Net::TCPServerConnection::start() @ 0x00000000150f4e52 in /usr/bin/clickhouse
  19. Poco::Net::TCPServerDispatcher::run() @ 0x00000000150f5c51 in /usr/bin/clickhouse
  20. Poco::PooledThread::run() @ 0x00000000151ece67 in /usr/bin/clickhouse
  21. Poco::ThreadImpl::runnableEntry(void*) @ 0x00000000151eb45c in /usr/bin/clickhouse
  22. ? @ 0x00007ff021694ac3 in ?
  23. ? @ 0x00007ff021726850 in ?
    (version 23.12.2.59 (official build))

@DerekChia
Copy link
Contributor

reproduced it here - https://fiddle.clickhouse.com/ad3c2e1b-0d7e-4ebc-899d-ba422a26c082

cc: @avinzhang

@ivesshao-lb
Copy link
Author

@avinzhang

set allow_experimental_window_view = 1;
set allow_experimental_analyzer = 0;

-- default.tb_tick definition Source data table
CREATE OR REPLACE TABLE default.tb_tick
(
symbol String,
ask Decimal(20,10),
bid Decimal(20,10),
ctm DateTime
)
ENGINE = MergeTree
ORDER BY (symbol,ctm);

-- default.kline_test definition Target data table
CREATE OR REPLACE TABLE default.kline_test
(
symbol String,
open_price Decimal(20,10) DEFAULT 0.,
high_price Decimal(20,10) DEFAULT 0.,
low_price Decimal(20,10) DEFAULT 0.,
close_price Decimal(20,10) DEFAULT 0.,
trade_time DateTime
)
ENGINE = AggregatingMergeTree
ORDER BY (symbol,trade_time)
SETTINGS index_granularity = 8192;

-- default.kline_m1_view View Definition
CREATE WINDOW VIEW default.kline_m1_view TO default.kline_test
(
symbol String,
open_price Decimal(20,10),
high_price Decimal(20,10),
low_price Decimal(20,10),
close_price Decimal(20,10),
trade_time DateTime
)
WATERMARK toIntervalSecond(2) AS
SELECT
symbol,
any(t.bid) AS open_price,
max(t.bid) AS high_price,
min(t.bid) AS low_price,
anyLast(t.bid) AS close_price,
tumbleStart(t_window) AS trade_time
FROM default.tb_tick AS t
GROUP BY
symbol,
tumble(toDateTime(t.ctm),toIntervalMinute(1)) AS t_window
ORDER BY
symbol ASC,
trade_time ASC;

insert into default.tb_tick VALUES('ADAUSD', 0.4655000000, 0.4643000000, '2024-05-10 09:09:09');

Error:
Received exception from server (version 24.3.3):
Code: 10. DB::Exception: Received from localhost:9000. DB::Exception: Not found column in block. There are only columns: symbol, ask, bid, ctm: while pushing to view default.kline_m1_view (8dbcc34a-6739-49e3-bdfe-5970c7da8366). (NOT_FOUND_COLUMN_IN_BLOCK)
(query: insert into default.tb_tick VALUES('ADAUSD', 0.4655000000, 0.4643000000, '2024-05-10 09:09:09');)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
experimental feature Bug in the feature that should not be used in production question Question?
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@Algunenano @DerekChia @ivesshao-lb