#support
Title
# support
a
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
but its not working
I wants to delete the resource attribute value which is showing into signoz..
How I can delete that..
Copy code
alter table signoz_metrics.time_series_v2 delete where metric_name='signoz_calls_total' and JSONExtractString(labels, 'resource_deployment_environment')='PROD-SIG';this 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?
What’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
I checked that entries are there
using 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
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
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. 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 │
└─────────┘if update we can do that is also fine
can 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.
This is not fixed yet https://github.com/ClickHouse/ClickHouse/issues/37730
Both are not exactly same but related
a
Ankita Salunkhe
11/30/2022, 7:46 AMThen how I can delete that records?
is 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?
is 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.
Or 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.
how we can remove that value from signoz dashboard?
or 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..
Thank you..
24 Views