https://signoz.io logo
#support
Title
# support
a

Ankita Salunkhe

11/30/2022, 5:04 AM
Can 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 AM
I 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 AM
If 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 AM
Data is there
I checked that entries are there
using above query
s

Srikanth Chekuri

11/30/2022, 7:30 AM
What’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 AM
let me share that with you
Copy code
Query id: 319298ee-d864-4285-9d2f-b3174cd804aa

┌─count()─┐
│      41 │
└─────────┘
s

Srikanth Chekuri

11/30/2022, 7:35 AM
It 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 AM
sure
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 AM
No, 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 AM
All your alter mutations were not successful. The update may not change anything. This is an issue from ClickHouse.
Both are not exactly same but related
a

Ankita Salunkhe

11/30/2022, 7:46 AM
Then how I can delete that records?
is there any other way to do this?
s

Srikanth Chekuri

11/30/2022, 7:46 AM
I don’t think you can alter individual records now until that issue is fixed.
a

Ankita Salunkhe

11/30/2022, 7:47 AM
how I can fix then?
is there any other configuration we have to do?
s

Srikanth Chekuri

11/30/2022, 7:49 AM
I don’t know of any configuration change that gets rid of rows.
a

Ankita Salunkhe

11/30/2022, 7:51 AM
Ok, is there anybody who can help me with this issue?
s

Srikanth Chekuri

11/30/2022, 7:53 AM
I 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 AM
Actually 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 AM
It needs to be removed from the DB
a

Ankita Salunkhe

11/30/2022, 8:16 AM
Ohh Ok..
Thank you..
87 Views