Can anyone please help me with Clickhouse DB opera...
# supporta
Ankita Salunkhe
11/30/2022, 5:04 AMCan anyone please help me with Clickhouse DB operations?
p
Pranay
11/30/2022, 5:06 AM
@Ankita Salunkhe Would be helpful if you can share more details on the questions when you are asking something - would help members in the community to understand what exactly you are asking
a
Ankita Salunkhe
11/30/2022, 5:07 AMI have to delete the records from the clickhouse DB, Ankit has given me the query to delete the records
Ankita Salunkhe
11/30/2022, 5:07 AMbut its not working
Ankita Salunkhe
11/30/2022, 5:07 AMI wants to delete the resource attribute value which is showing into signoz..
Ankita Salunkhe
11/30/2022, 5:07 AMHow I can delete that..
Ankita Salunkhe
11/30/2022, 5:08 AM Copy code
alter table signoz_metrics.time_series_v2 delete where metric_name='signoz_calls_total' and JSONExtractString(labels, 'resource_deployment_environment')='PROD-SIG';Ankita Salunkhe
11/30/2022, 5:08 AMthis is what Ankit has given, But its not working. Its not deleting the records..Its showing query OK but no delete
s
Srikanth Chekuri
11/30/2022, 7:28 AMIf it didn’t delete anything, you probably don’t have the data?
Srikanth Chekuri
11/30/2022, 7:29 AMWhat’s the output of this?
Copy code
SELECT count()
FROM signoz_metrics.time_series_v2
WHERE (metric_name = 'signoz_calls_total') AND (JSONExtractString(labels, 'resource_deployment_environment') = 'PROD-SIG')a
Ankita Salunkhe
11/30/2022, 7:29 AMData is there
Ankita Salunkhe
11/30/2022, 7:29 AMI checked that entries are there
Ankita Salunkhe
11/30/2022, 7:29 AMusing above query
s
Srikanth Chekuri
11/30/2022, 7:30 AMWhat’s the output for
Copy code
SELECT count()
FROM signoz_metrics.time_series_v2
WHERE (metric_name = 'signoz_calls_total') AND (JSONExtractString(labels, 'resource_deployment_environment') = 'PROD-SIG')a
Ankita Salunkhe
11/30/2022, 7:31 AMlet me share that with you
Ankita Salunkhe
11/30/2022, 7:32 AM Copy code
Query id: 319298ee-d864-4285-9d2f-b3174cd804aa
┌─count()─┐
│ 41 │
└─────────┘s
Srikanth Chekuri
11/30/2022, 7:35 AMIt should delete data. Can you run the queries one after the other and share the result.
Copy code
alter table signoz_metrics.time_series_v2 delete where metric_name='signoz_calls_total' and JSONExtractString(labels, 'resource_deployment_environment')='PROD-SIG'; Copy code
SELECT count()
FROM signoz_metrics.time_series_v2
WHERE (metric_name = 'signoz_calls_total') AND (JSONExtractString(labels, 'resource_deployment_environment') = 'PROD-SIG')a
Ankita Salunkhe
11/30/2022, 7:36 AMsure
Ankita Salunkhe
11/30/2022, 7:37 AM Copy code
ALTER TABLE signoz_metrics.time_series_v2
DELETE WHERE (metric_name = 'signoz_calls_total') AND (JSONExtractString(labels, 'resource_deployment_environment') = 'PROD-SIG')
Query id: a5bce6ba-e02e-490b-8678-1e4cb5b2b2ce
Ok.
0 rows in set. Elapsed: 0.041 sec.Ankita Salunkhe
11/30/2022, 7:37 AM Copy code
SELECT count()
FROM signoz_metrics.time_series_v2
WHERE (metric_name = 'signoz_calls_total') AND (JSONExtractString(labels, 'resource_deployment_environment') = 'PROD-SIG')
Query id: 8976e3bb-eb34-49c8-988b-707836c01a16
┌─count()─┐
│ 41 │
└─────────┘Ankita Salunkhe
11/30/2022, 7:37 AMif update we can do that is also fine
Ankita Salunkhe
11/30/2022, 7:38 AMcan u please help me with update query lets see if that is working or not
s
Srikanth Chekuri
11/30/2022, 7:40 AMNo, it could be that delete op is aysnc, try this.
Copy code
alter table signoz_metrics.time_series_v2 delete where metric_name='signoz_calls_total' and JSONExtractString(labels, 'resource_deployment_environment')='PROD-SIG' SETTINGS mutations_sync = 2;a
Ankita Salunkhe
11/30/2022, 7:41 AM Copy code
Received exception from server (version 22.4.5):
Code: 341. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: Exception happened during execution of mutations 'mutation_118194.txt, mutation_138065.txt, mutation_138085.txt, mutation_87827.txt, mutation_87889.txt, mutation_87957.txt, mutation_91031.txt' with part '20221122_57917_62900_491_86291' reason: 'Code: 16. DB::Exception: There is no physical column or subcolumn labels_object in table: While executing MergeTreeInOrder. (NO_SUCH_COLUMN_IN_TABLE) (version 22.4.5.9 (official build))'. This error maybe retryable or not. In case of unretryable error, mutation can be killed with KILL MUTATION query. (UNFINISHED)s
Srikanth Chekuri
11/30/2022, 7:43 AMAll your alter mutations were not successful. The update may not change anything. This is an issue from ClickHouse.
Srikanth Chekuri
11/30/2022, 7:45 AMThis is not fixed yet https://github.com/ClickHouse/ClickHouse/issues/37730
Srikanth Chekuri
11/30/2022, 7:45 AMBoth are not exactly same but related
a
Ankita Salunkhe
11/30/2022, 7:46 AMThen how I can delete that records?
Ankita Salunkhe
11/30/2022, 7:46 AMis there any other way to do this?
s
Srikanth Chekuri
11/30/2022, 7:46 AMI don’t think you can alter individual records now until that issue is fixed.
a
Ankita Salunkhe
11/30/2022, 7:47 AMhow I can fix then?
Ankita Salunkhe
11/30/2022, 7:47 AMis there any other configuration we have to do?
s
Srikanth Chekuri
11/30/2022, 7:49 AMI don’t know of any configuration change that gets rid of rows.
a
Ankita Salunkhe
11/30/2022, 7:51 AMOk, is there anybody who can help me with this issue?
s
Srikanth Chekuri
11/30/2022, 7:53 AMI guess you can leave a comment on the issue in the ClickHouse repo so that they will look into it.
Srikanth Chekuri
11/30/2022, 7:56 AMOr not really good alternative is to drop everything and let it fill back again if that’s fine with you.
a
Ankita Salunkhe
11/30/2022, 7:57 AMActually some mismatch is there thats why.
Ankita Salunkhe
11/30/2022, 7:59 AMhow we can remove that value from signoz dashboard?
Ankita Salunkhe
11/30/2022, 8:00 AMor we have to delete that from db itself?
s
Srikanth Chekuri
11/30/2022, 8:04 AMIt needs to be removed from the DB
a
Ankita Salunkhe
11/30/2022, 8:16 AMOhh Ok..
Ankita Salunkhe
11/30/2022, 8:26 AMThank you..
187 Views