How to update columns in clickhouse multiple times in a session?

Question

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 the code is this:

for domain_data in list_domain:
            if not domain_data[6]: 
                x =  0
                while x < 10:
                    update : dict = {
                            'status': "1",
                        }
                    db.update_db(update, 'domain', string_db(domain_data[0]))

and the db.update_db (query is this):

    def update_db(self, values_dict, column_name, column_value):
        column_list_string: str = ''
        existance_var = f"select EXISTS(select {column_name} from {self.database_name}.{self.table_name} where {column_name} = {column_value})"
        existance_var = self.client.execute(existance_var)
        
        if existance_var[0][0]:        
            if values_dict.get('host_name'):
                del values_dict['host_name']
                
            for name, value in values_dict.items():
                column_list_string += f"{name} = {value},"
            
            column_list_string = column_list_string.rstrip(column_list_string[-1])
            
            final_update_query: str = f"ALTER TABLE {self.database_name}.{self.table_name} UPDATE {column_list_string} WHERE {column_name} = {column_value}"
                
            try:
                self.client.execute(final_update_query)
                print("Item updated to database")
            except Exception as e:
                logging.error(f"Update has failed: {str(e)}")
        else:
            logging.error("Update has failed")

I want to update status code to 1 on multiple columns in one session.

score 0 · Answer 1 · answered Feb 28 '23 at 15:57

0

ALTER TABLE statements are asynchronous by default. That means when you run your "final_update_query", ClickHouse starts to execute the update in the background and immediately returns to your Python script. For your use case it would probably work best if you force your Python script to wait until each UPDATE is finished before attempting the next one.

To do that you should send the setting mutation_sync=2 with your execute statement. Then your Python code won't resume until the update is complete on all replicas of your data, and the next update should run as expected.

Documentation on the mutation sync setting is here: https://clickhouse.com/docs/en/operations/settings/settings/#mutations_sync

answered Feb 28 '23 at 15:57

Geoff Genz

2,006
17
20

I also had this problem before and it was not solved, I even included time.sleep(10) in my Python script, but the problem was not solved, I don't think waiting will solve this case. – Sardar Feb 28 '23 at 22:08
At the end of the ALTER TABLE command, I put `SETTINGS mutations_sync = 2` and it had no effect. – Sardar Feb 28 '23 at 22:09
You need to be more specific about "this problem" and "not solved". What happens if you run the same query through clickhouse client? What happens if you actually send the setting in the `settings` dictionary argument to the exec function? – Geoff Genz Mar 01 '23 at 00:50
1

I solved the problem with this query: `final_update_query: str = f"ALTER TABLE db.table UPDATE {column_list_string} WHERE {column_name} IN ({column_value})"` Thank you guys. – Ali Esmaeili Mar 01 '23 at 07:44

score 0 · Answer 2 · answered Mar 01 '23 at 16:35

Mutations is not UPDATE ClickHouse doesn't support realtime updates

Read about ReplacingMergeTree https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree/

and apply it with SELECT ... FINAL ... https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier

How to update columns in clickhouse multiple times in a session?

2 Answers2

Linked