I want to delete a row based on a specific date in ClickHouse DB

userUHx21 0
Viewed 30

I want to delete a row where date is '2022-09-08' the date is present but I am not able to delete it connected my clickhouse with python

table name :- repeat_day_by_last_120_active_cohort_v1
columns :- 'date','L120_active_cohort_logins','L120_active_cohort','percentage_L120_active_cohort_logins'

Code is as follows :-

client.execute(f'''ALTER TABLE repeat_day_by_last_120_active_cohort_v1 DELETE WHERE date = "2022-09-08"''')

Error message is as follows :-

clickhouse_driver.errors.ServerException: Code: 47.
    DB::Exception: Missing columns: '2022-09-08' while processing query: 'SELECT percentage_L120_active_cohort_logins, L120_active_cohort, L120_active_cohort_logins, date WHERE isZeroOrNull(date = `2022-09-08`)', required columns: 'percentage_L120_active_cohort_logins' 'L120_active_cohort' 'L120_active_cohort_logins' 'date' '2022-09-08', maybe you meant: ['percentage_L120_active_cohort_logins','L120_active_cohort','L120_active_cohort_logins','date']. Stack trace:
directed-acyclic-graphs clickhouse airflow python
1 Answers

ClickHouse only allows using single quotes to quote strings while double quotes are used to quote identifiers, so the date you provide is considered as a column, and ClickHouse tried to find the rows which have a value in the column date equals to the value in the column 2022-09-08 which doesn't exists:

client.execute(f"""ALTER TABLE repeat_day_by_last_120_active_cohort_v1 DELETE WHERE date = '2022-09-08'""")
Hussein Awala0
Related