85 questions
0
votes
0
answers
27
views
Clickhouse server unreachable when using the INET IP to connect
I am able to install clickhouse-server (version 24.11.1.2557) on my Ubuntu machine (version 22.04.5). I confirm that the service started successfully by:
a. Checking the log
2024.12.03 21:26:40.561958 ...
- 1,488
0
votes
0
answers
48
views
Avoid conversion of bytes to str in clickhouse-connect
I want to read bytes from ClickHouse using clickhouse-connect driver. I don't provide any encodings when I send a query. Under the hood it tries to decode these bytes to string using utf8 encoding, ...
- 713
2
votes
1
answer
312
views
How to execute a script in Clickhouse from Linux CLI?
This may seem to be an easy question, but I did not find an answer, despite searching for it for a while and my attempts did not succeed.
I'm running Sentry via Docker and it uses Clickhouse. I can ...
- 75.6k
1
vote
0
answers
46
views
Convert Timescaledb query to Clickhouse query
Following are the queries in timescaledb which i need to convert into clickhouse as clickhouse supports timeseries data as well
Following are the timescaledb queries:
CREATE MATERIALIZED VIEW
...
0
votes
1
answer
59
views
Store async insert pending .bin files in the external SSD storage instead of boot disk
I have a clickhouse cluster with 3 VMs, each being a shard. I have some distributed engine tables, there are high number of async insert happening on this cluster, the issue I face is that the boot ...
- 41
0
votes
0
answers
252
views
ClickHouse cluster connection timeout and address errors using spring JDBC
We are trying to connect to a ClickHouse cluster (containing 4 nodes and 3 ClickHouse-keepers) using the JDBC connector. When I test the performance with 100, 200, or 300 threads (users), after some ...
- 1
0
votes
0
answers
68
views
How to read clickhouse bitmap with C# code
I am building a c# service which need to query Clickhouse, we're using ClickHouse.Client to access Clickhouse, while seems the library does not support to read roaring bitmap directly, is there any ...
- 63
0
votes
1
answer
312
views
How do i increase clickhouse timeout in Clickhouse
Trying to do massive large inserts, I get exceptions like this:
Code: 159. DB::Exception: Lock timeout exceeded.
I tried to increase timeouts like this, but it doesn't help:
ClickHouseProperties ...
- 9,186
0
votes
1
answer
547
views
Backup ClickHouse cluster database without using clickhouse-backup utility
I have a three node clickhouse cluster database that I need backup using Clickhouse backup command.
replica = 1
shard = 3
I tried local storage available as shared on all nodes and s3 storage.
When I ...
-1
votes
1
answer
282
views
Need variables in parameterized view in clikhouse
I need variable in parametrized view.
CREATE VIEW my_new_view AS
SELECT
CampaignName,
sentiment, -- **CASE WHEN variable** = sentiment THEN sentiment ELSE NULL END AS sentiment_match
COUNT(CASE ...
0
votes
0
answers
93
views
Insertion in distributed data tables for multiregion click house deployment
Say I have two regions - DC1 and DC2 . Let's consider that I have 9 nodes in DC1 and DC2 with 3 replicas in each DC for 5 shards. In this case , if I trigger an insert statement to a distributed table ...
- 991
0
votes
2
answers
821
views
Timezone Issue with Clickhouse - Asia/Tehran Timezone
Upon executing the following query in my ClickHouse database:
SELECT formatDateTime(now(), '%Y-%m-%d %H:%M:%S', 'Asia/Tehran');
I received an unexpected result:
2024-03-29 00:34:35
However, the ...
- 129
0
votes
0
answers
214
views
Keeper Clickhouse Replication DDL on cluster, but no replication data, error "Table was in readonly mode"
In the test, the Keeper process replicates the DDL in the cluster, but a table of type ReplicatedMergeTree, it creates it in all the nodes, but when I insert information into the table, the data only ...
0
votes
0
answers
138
views
ClickHouse Server Exception: Code: 210.DB::Exception: Fail to read from HDFS:
I'm trying to migrate data from hdfs to clickhouse from hdfs. Sometimes the showcase is assembled without problems, but most often it falls with such an error. I tried to wrap it in try except so that ...
- 23
0
votes
0
answers
224
views
migrations using clickhouse and testcontainers
everyone!
I have some problems to up my migration files inside clickhouse container
package tests
import (
"context"
"fmt"
_ "github.com/ClickHouse/clickhouse-go&...
- 11
0
votes
1
answer
115
views
runningDifference() not working in clickhouse for this serinario
In clickhouse lag/lead functions are not supporting, i used runningDifference(), for this scenario it's not working.
select endtime, runningDifference(endtime) as time_diff
from
(select ...
0
votes
0
answers
79
views
How I can do join in ClickHouse with IpV4 and Ipv4 Range
I trying do join with ipv4 in table1 and range ipv4 table2:
table1:
user_id(int), ipv4(type ipv4)
Column A
Column B
122222
1.0.0.1
323232
1.0.0.2
table2:
range_start(type ipv4), range_end(type ...
- 1
0
votes
2
answers
537
views
ClickHouse view - AggregateFunction column vs usual column
I have declared my aggregation table as:
CREATE TABLE test.system_aggr
(
system_id String,
aggr_val AggregateFunction(sum, Int64)
)
ENGINE = AggregatingMergeTree
ORDER BY (system_id)...
- 3,546
0
votes
1
answer
911
views
How to list all schemas and switch to one in ClickHouse
I'm new to ClickHouse and I'm trying to use the CLI client.
What's the Clickhouse way to select all schemas and then select one?
I mean the equivalent of Oracle's
select username from sys.all_users
...
- 5,446
0
votes
0
answers
60
views
Clickhouse mysql() Table function timesout
I am migrating a few tables from MySQL to Clickhouse in batches.
while Running:
insert into `<ch_table_name>` select * from mysql('<host>', '<db>', '<mysql_table>', '<...
- 5,195
0
votes
0
answers
195
views
Clickhouse JDBC version > 0.4.x fails in Cloud
I have to connect a java cloud application to clickhouse cloud database. The restriction is that the certificate cannot be uploaded to the cloud application.
clickhouse-jdbc version 0.3.2 works ...
- 449
1
vote
1
answer
293
views
Clickhouse creating view on cluster on non default database fails
I am trying to create a view on a clickhouse server that is ran in docker based on image clickhouse/clickhouse-server:22.8.13.20
I create a table in a database named X (not default), the connection ...
- 39
4
votes
1
answer
6k
views
Clickhouse CLI: Multi-statements are not allowed exception
I am trying to execute this query using the Clickhouse CLI:
INSERT INTO t VALUES (1, 'Hello, world'), (2, 'abc'), (3, 'def')
which I found here: https://clickhouse.com/docs/en/sql-reference/syntax
but ...
- 89
0
votes
1
answer
423
views
Clickhouse MergeTree not aggregating inserts
I want to create a clickhouse table that stores the max value of stats over a period of every one hour.
My base table looks like below
CREATE TABLE base_table
(
`time` DateTime,
`stats` ...
- 372
1
vote
1
answer
159
views
Execution timeout when loading csv file to ClickHouseContainer
When attempting to upload a CSV file (~100 lines and < 100KB) to ClickHouseContainer running in junit, the client is timing out with the following error:
An exception or error caused a run to abort:...
- 480
0
votes
1
answer
197
views
Clickhouse JSON Ephemeral Column
Can't get the example from the clickhouse docs to work:
DROP TABLE IF EXISTS github_json;
SET allow_experimental_object_type = 1;
CREATE table github_json
(
event_type LowCardinality(String) ...
- 2,263
0
votes
1
answer
762
views
how insert data into clickhouse table with permission denied?
Got error while inserting date into Clickhouse table:
clickhouse_connect.driver.exceptions.DatabaseError: :HTTPDriver for http://localhost:8123 returned response code 500)
std::exception. Code: 1001, ...
- 1
2
votes
0
answers
1k
views
How to transpose a query result in ClickHouse ? ( Rows to Columns )
I am looking for an easy way to Transpose the Query Output in rows & columns (not one row in text by using Format Vertical ) .
My Query gives me an output like this :-
And I want the output in ...
- 37
0
votes
2
answers
2k
views
How to get current queries to max_concurrent_queries
I encountered this error：
clickhouse_driver.errors.ServerException: Code: 202. DB::Exception: Too many simultaneous queries. Maximum: 200. Stack trace:
but I have adjusted the max_concurrent_queries ...
- 1
1
vote
2
answers
3k
views
How to secure Clickhouse via the default user?
I have a clickhouse database someone else has installed. I am able to connect to it via the
clickhouse-client
or the
clickhouse-client -u default
command and that connects the default user to ...
- 75.6k
0
votes
0
answers
215
views
Clickhouse backup failure - Table has its shared ID to be different from one from the create query
In a clickhouse 3 shard, 3 replica setup, I am trying to take a schema backup.
But it fails with following error :
BACKUP DATABASE default
TO S3( 'https://bucket-id.s3.amazonaws.com/backup_folder/...
- 11.6k
0
votes
1
answer
109
views
How to reduce the memory usage of a multiple ASOF JOIN queries?
I have one query that results in ~100 rows. Not really much. This table is called FillsTable.
Then I have mulitple tables SignalTable1,2,3,4,5,6 that have millions of rows.
I want to ASOF JOIN the ...
- 140k
1
vote
2
answers
1k
views
Steps to migrate my clickhouse data from single node to replicated cluster
I am trying to find a way to move my data from single node standalone clickhouse to new replicated cluster. But there are no solid documents available to perform that.
I tried to take backup from ...
0
votes
1
answer
260
views
How to read click house tables from Python Azure function?
I need to read the tables from click house via Python Azure function, I am able to read the tables from my local system but not able to read from Python Azure Function.
What I tried:
import ...
- 55
0
votes
0
answers
1k
views
How to perform batch inserts to clickhouse using JDBC client?
I am trying to write into clickhouse DB using Clickhouse JDBC client (0.4.0)
<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc -->
<dependency>
<groupId>com....
- 1
0
votes
2
answers
533
views
Clickhouse row policy and default user behavior
Problem: Created User with role and row policies do not see data from all shards unless default user is granted the same row policies.
Setup is the following: I have clickhouse cluster, 3 shards(2 ...
0
votes
0
answers
527
views
Divide ClickHouse table into distinct logical chunks for parallel reading
I want to read a ClickHouse table in parallel from multiple clients. The table is unpartitioned and uses the MergeTree engine. My application is in C++ and is communicating with ClickHouse over its C++...
- 53
0
votes
1
answer
4k
views
Clickhouse Memory Limit Exceeded on Joins
I am trying to execute the following query with Clickhouse which joins 4 tables:
select
s.branch_id AS branch_id,
pa.sharer_id as sharer_id,
si.commission_type_id as ...
0
votes
1
answer
137
views
In ClickHouse, catboostEvaluate method for catboost classification model returning values like regression models
I'm using catboostEvaluate method to predict a classification problem.
In Catboost, the evaluation result is like this:
model.predict(X_validation)
array([0, 0, 0, ..., 1, 0, 0])
However, in ...
0
votes
1
answer
493
views
A setting's value string has to be an exactly one character long
I recently installed Clickhouse on my pc to work with a huge .csv (9Gb)
I know the columns names in this .csv file, so I easily created the table in the Clickhouse console
CREATE DATABASE IF NOT ...
- 11
0
votes
2
answers
3k
views
How to update columns in clickhouse multiple times in a session?
I want to update values of diffrent columns multiple time in a session but just first query is working and other queries that they are in the loop is not update.
I use a loop for my multiple query and ...
- 302
0
votes
0
answers
378
views
Got a TypeError("unhashable type: 'dict'") using insert_dataframe method clickhouse-driver.Client
I start to use clickhouse-driver Client in my data pipelines and every time I faced an issue:
TypeError("unhashable type: 'dict'")
I know this kind of error that dict is not hashable type, ...
-1
votes
2
answers
3k
views
clickhouse table creation error in ubuntu
I installed the clickhouse program in the ubuntu operating system and connected the SQLyog program. I can create a database, but I cannot create a table in it. It gives the following CODE:119 error.
...
1
vote
0
answers
391
views
Understanding Background merges in ClickHouse
I wanted to understand background merges better. I understand that background merges keep happening until it reaches 150GB as defined by max_bytes_to_merge_at_max_space_in_pool.
I would like to ...
- 11
0
votes
1
answer
688
views
Insert data into a ClickHouse table from a file with deduplication
I'm importing data into a ClickHouse table from CSV files.
cat data.csv | clickhouse-client --config-file=config.xml --query="INSERT INTO data_pool FORMAT CSVWithNames"
Often CSV files ...
- 167
0
votes
1
answer
2k
views
Clickhouse: Want to extract data from Array(Nested) column in Clickhouse
Query used to create the table:
CREATE TABLE default.ntest2(
job_name String,
list_data Array(Nested(s UInt64, e UInt64, name String))
)
ENGINE = MergeTree
ORDER BY (job_name)...
- 356
0
votes
1
answer
911
views
Clickhouse: Want to extract data from Array(Tupple) column in Clickhouse
Query used to create the table:
CREATE TABLE default.ntest2(job_name String, list_data Array(Tuple(s UInt64, e UInt64, name String))) ENGINE = MergeTree ORDER BY (job_name) SETTINGS index_granularity =...
- 356
0
votes
1
answer
962
views
Clickhouse protobuf output format
I use clickhouse server in docker with just 1 table and several rows in it. I can request all the data in default format with clickhouse client (over TCP) or with some GUI tool like DBeaver (over HTTP)...
- 1,303
0
votes
1
answer
1k
views
Clickhouse 'lightweight deletes' are not filtered from subsequent queries as expected, only filtered once asynchronous call is complete
I am trying out the new clickhouse lightweight deletes, which deletes rows on the next merge but asynchronously 'marks' them immediately so that they are not shown in subsequent queries.
The guide i ...
1
vote
0
answers
318
views
How to conditionally rename or exchange a table?
Is there an equivalent to the T-SQL IF...ELSE keywords for control flow?
I'm trying to conditionally EXCHANGE tables A and B if A exists, otherwise just rename B to A. From documentation, all I'm ...
- 470