class DBConnection: def __init__(self, clickhouse_host=None, clickhouse_name=None, dbuser=None, dbpass=None, db_query=None): self.db_query = db_query self.clickhouse_client = Client( host=clickhouse_host, database=clickhouse_name, user=dbuser, password=dbpass ) try_conn = self._connection_test() if try_conn: raise try_conn self.connection_status = False def make_connection(self): try: self.clickhouse_client.connection.connect() except Exception as error: logger.error(error) return False self.connection_status = True return True def _connection_test(self): try: self.clickhouse_client.connection.connect() except errors.Error as error: logger.error(exception_to_logger(error)) return "Error of database connection." self.clickhouse_client.disconnect() return None def send_request(self, trace=False): response_status = False try: if isinstance(self.db_query, tuple): response = self.clickhouse_client.execute( self.db_query[0], self.db_query[1] ) else: response = self.clickhouse_client.execute(self.db_query) response_status = True except Exception as error: logger.error(exception_to_logger(error)) response = json_str_error("Error in sql query!") if trace: print_statusbar([(error, 'd',)]) return response_status, response def disconnect(self): self.clickhouse_client.disconnect() self.connection_status = False
def createDatabase(self, dbName): logging.info("Create new database function") # Create database in clickhouse server client = Client(self.ipAddress) query = "CREATE DATABASE {}".format(dbName) client.execute(query) print("Database is created successfully")
def insert_into_db(parsed_data): #connecting to clickhouse database try: login_data = config('Clickhouse_db_login') hostname = getHostname(login_data['hostnames']) user = login_data['user'] database = login_data['database'] password = login_data['password'] port = login_data['port'] client = Client(host=hostname, user=user, database=database, password=password, port=port) print('Connected to clickhouse db') table_name = config('Clickhouse_table_data')[ 'table_name'] #get table name from config file client.execute( 'INSERT INTO {0} (event_date, ip_server, url, ip_nat, ip_abon, protocol_type) VALUES' .format(table_name), parsed_data) #execute command for inserting data print('Command has executed successfully') except (Exception, Client.DatabaseError) as error: print(error)
def updateColumn(self,dbName,tableName,updateColumnName,updateColumnValue,fWhereColumnName,fWhereColumnValue,sWhereColumnName,sWhereColumnValue): logging.info("Update column data function") # Update column value client = Client(self.ipAddress) query = "ALTER TABLE {}.{} UPDATE {}={} WHERE {}={} AND {}={};".format(dbName,tableName,updateColumnName,updateColumnValue,fWhereColumnName,fWhereColumnValue,sWhereColumnName,sWhereColumnValue) client.execute(query) logging.info("Record is modified successfully")
def addColumn(self,dbName,tableName,columnName,dataType): logging.info("Add new column function") # Add column to existing table client = Client(self.ipAddress) query = 'ALTER TABLE {}.{} ADD COLUMN {} {};'.format(dbName,tableName,columnName,dataType) client.execute(query) logging.info("Table column is added successfully")
def dropDatabase(self, dbName): logging.info("Drop database function") # Drop the database client = Client(self.ipAddress) query = "DROP DATABASE IF EXISTS {};".format(dbName) client.execute(query) logging.info("Database is dropped successfully")
def create_clickhouse_database(): """ ### Создать таблицу keywords_volume в Кликхаус """ if database_name == None: database_name = 'seodata' from clickhouse_driver import Client ch = Client(host='clickhouse') ch.execute(f'CREATE DATABASE IF NOT EXISTS {database_name}') # создаст базу данных если ее не существует ch = Client(host='clickhouse', database=f'{database_name}') # Если таблицы нет то создаем ch.execute(""" CREATE TABLE IF NOT EXISTS seodata.keywords_volume ( `ext_taskid` String, `se` String, `location_name` String, `language_name` String, `keyword` String, `competition` Float64, `cpc` Float64, `year` UInt64, `month` UInt64, `search_volume` UInt64, `date` Date, `create_date` DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(date) ORDER BY (date) SETTINGS index_granularity=8192 """) print(ch.execute('SHOW TABLES')) return {}
def delete_data_in_ch(self): client = Client(**CLICKHOUSE_DB_INFO) delete_data_last = {} if not self.delete_data_list: self.time_cost_last_delete = 0 else: logger.info( "begin group data with primary_key_values to each table") for i in self.delete_data_list: delete_data_last.setdefault(i['table_name'], []).append(i['primary_key_values']) logger.info("group each table data for one sql") for table_name, values in delete_data_last.items(): sql_cur = {} for v in values: for k, vv in v.items(): sql_cur.setdefault(k, []).append(vv) last_delete = ' and '.join( ['%s in %s' % (k, tuple(v)) for k, v in sql_cur.items()]) exec_sql = "alter table %s delete where %s ;" % (table_name, last_delete) begin_delete_time = int(time.time()) logger.info("begin execute: exec_sql:%s" % exec_sql) client.execute(exec_sql) logger.info("end execute") client.disconnect() self.time_cost_last_delete = int( time.time()) - begin_delete_time
def insertPhrasesData(self, dbName, phrase, label): logging.info("Insert phrases data function") # Inserting the data into the table client = Client(self.ipAddress) query = 'INSERT INTO {}.Phrases (phrases,label) VALUES'.format(dbName) client.execute(query, [{'phrases': phrase, 'label': label}]) print('Record is added successfully')
def insertData(self, dbName, tableName, listOfDictionaries): # Inserting the data into the table client = Client(self.ipAddress) query = 'INSERT INTO {}.{} (date_time,user_id,user_name,text_id,text,score,magnitude) VALUES'.format( dbName, tableName) client.execute(query, listOfDictionaries) print('Record is added successfully')
def addColumn(self, dbName, tableName, columnName, dataType): # Add column to existing table client = Client(self.ipAddress) query = 'ALTER TABLE {}.{} ADD COLUMN {} {};'.format( dbName, tableName, columnName, dataType) client.execute(query) print("Table column is added successfully")
def moveTable(self, dbName1, dbName2, tableName): # Move a table from one schema to another client = Client(self.ipAddress) query = 'RENAME TABLE {}.{} TO {}.{};'.format(dbName1, tableName, dbName2, tableName) client.execute(query) print("Table is moved")
def insert_to_ch(schema, table, res, start_time=None, end_time=None): if not res: logger.info(f"None Result, pass: {schema}.{table} {start_time}, {end_time}") return ind = random.randint(0, len(CH_SERVERS) - 1) host, port = CH_SERVERS[ind][0], CH_SERVERS[ind][1] if end_time: distributed_table = table.rstrip('_local') client = Client(host=host, port=port, user=CH_USER, password=CH_PASS) s = client.execute(f"select count(*) cnt from " f"{schema}.{distributed_table} where end_time=toDateTime('{end_time}')") s = s[0][0] client.disconnect() else: s = 0 if not s: sql = f'INSERT INTO {schema}.{table}(' \ f'end_time, invertal, gtid, transaction_spend_time, transaction_size, single_statement_affected_rows) VALUES' client = Client(host=host, port=port, user=CH_USER, password=CH_PASS) client.execute(sql, res) logger.info(f"Success: {schema}.{table} {start_time}, {end_time}") client.disconnect() else: logger.info(f'Exists, pass: {schema}.{table} {start_time}, {end_time}')
def dropTable(self, dbName, tableName): logging.info("Drop table function") # Drop the table client = Client(self.ipAddress) query = 'DROP TABLE IF EXISTS {}.{};'.format(dbName, tableName) client.execute(query) logging.info("Table is dropped successfully")
def insertNewData(self, dbName, tableName, listOfDictionaries): logging.info("Insert data function") # Inserting the data into the table client = Client(self.ipAddress) query = 'INSERT INTO {}.{} (date_time,user_id,user_name,text_id,text,label) VALUES'.format( dbName, tableName) client.execute(query, listOfDictionaries) logging.info('Record is added successfully')
def execute_queries(): client = Client('10.0.0.2') print(client.execute('SHOW TABLES')) sql = 'select Year, Month, DayofMonth, Origin from flight limit 3;' for row in client.execute(sql): print(row)
def run_sql_on_clickhouse(self, sql): client = Client(host='localhost') # clickhouse_sql = "clickhouse-client -h 127.0.0.1 --query=\"{}\"".format(sql).replace('`','') # clickhouse_sql = "docker run -it --rm --link clickhouse-server-19-4-3-11:clickhouse-server yandex/clickhouse-client:19.4.3.11 --host clickhouse-server --query=\"{}\"".format(sql).replace('`','') print("------- CLICKHOUSE SQL -------") print(sql) # os.system(clickhouse_sql) client.execute(sql)
def createPhrasesTable(self, dbName): logging.info("Create phrases table function") # Create a new table in clickhouse server client = Client(self.ipAddress) # Create the table query = 'CREATE TABLE {}.Phrases (date Date DEFAULT today(), phrases String, label Float64) ENGINE = MergeTree(date, (date), 8192);'.format( dbName) client.execute(query) logging.info("Table is created successfully")
def transfer_data(): client = Client('clickhouse') logging.info('Start insert data from BUF table to MAIN table') with open(BASE_DIR + 'from_buf.sql', 'r') as file: insert = file.read() client.execute(insert) logging.info('finish insert data to main table') logging.warning('Truncate BUF table') client.execute('truncate table eventdata.events_buf')
def clickhouse(): client = Client( settings.CLICKHOUSE_HOST, user=settings.CLICKHOUSE_USER, password=settings.CLICKHOUSE_PASSWORD, ) client.execute(f'DROP DATABASE IF EXISTS {settings.CLICKHOUSE_NAME}') client.execute(f'CREATE DATABASE {settings.CLICKHOUSE_NAME}') client.disconnect()
def createTable(self,dbName,tableName): # Create a new table in clickhouse server client = Client(self.ipAddress) # Drop the table if table name already exists self.dropTable(dbName,tableName) # Create the table query = 'CREATE TABLE {}.{} (date Date DEFAULT today(), date_time String, user_id UInt64, user_name String, text_id UInt64, text String, score Float32, magnitude Float32) ENGINE = MergeTree(date, (date), 8192);'.format(dbName,tableName) client.execute(query) print("Table is created successfully")
def updateColumn(self, dbName, tableName, updateColumnName, updateColumnValue, whereColumnName, whereColumnValue): # Update column value client = Client(self.ipAddress) query = "ALTER TABLE {}.{} UPDATE {}={} WHERE {}={};".format( dbName, tableName, updateColumnName, updateColumnValue, whereColumnName, whereColumnValue) client.execute(query) print("Record is modified successfully")
def insert_partition(rows): assert isinstance(rows, list), rows assert isinstance(rows[0], tuple), rows[0] assert len(rows[0]) == 2, rows[0] client = Client('localhost') try: client.execute('INSERT INTO hashes (data, hash) VALUES', rows) except Exception: client.disconnect()
def UZD_to_Clickhouse(): # cfg = ConfigParser() # cfg.read(db_config_path) # # db_name = "evo_archive_2019" # host = cfg.get(db_name, "host") # user = cfg.get(db_name, "user") # password = cfg.get(db_name, "password") # database = cfg.get(db_name, "database") # host = 'dev-dwh-pg.mailru.local' # user = '******' # password = '******' # database = 'sandbox' # # connection = pymysql.connect(host=host, # user=user, # password=password, # db=database, # cursorclass=pymysql.cursors.DictCursor) # # # mycursor = connection.cursor() # # mycursor.execute(query) # # query_result = mycursor.fetchall() # connection.close() clickhouse_client = Client('172.30.200.27', user='******', password='******', database='sandbox') RowsFromFile = [] z = zipfile.ZipFile('1.zip') json_data = z.read('fns_trans_data.json').decode('utf-8') r = json.loads(json_data) counter = 0 batchID = 1 for row in r: row['batchID'] = batchID RowsFromFile.append(row) counter += 1 if counter == 5: clickhouse_client.execute( 'INSERT INTO sandbox.datavault_raw_test (batchID,intTransID,intFromTransID,intExiteDocID,varTransDatetime,varDocExchangeGUID,varTransGUID,varDocGUID,varSenderGUID,varRecipientGUID,varSosGUID,varTransType,varDocType,intTransState ) VALUES', RowsFromFile) with open('last_batch.txt', 'w') as last_batch: last_batch.write('Last TransID was:' + str(RowsFromFile[-1]['intTransID'])) batchID += 1 RowsFromFile = [] counter = 0 if counter != 0: clickhouse_client.execute( 'INSERT INTO sandbox.datavault_raw_test (batchID,intTransID,intFromTransID,intExiteDocID,varTransDatetime,varDocExchangeGUID,varTransGUID,varDocGUID,varSenderGUID,varRecipientGUID,varSosGUID,varTransType,varDocType,intTransState ) VALUES', RowsFromFile) with open('last_batch.txt', 'w') as last_batch: last_batch.write('last TransID was:' + str(RowsFromFile[-1]['intTransID']))
def insert_data(ip=None, date=None, perdiction=None, revenue=None, anomaly=None): client = Client(ip) query = "INSERT INTO vgame.anomaly (CreateDate, perdiction, revenue,anomaly) VALUES (toDate('" + str( date) + "'), '" + str(perdiction) + "', '" + str( revenue) + "','" + str(anomaly) + "')" client.execute(query)
def createNewTable(self, dbName, tableName): logging.info("Create new table function") # Create a new table in clickhouse server client = Client(self.ipAddress) # Drop the table if table name already exists self.dropTable(dbName, tableName) # Create the table query = 'CREATE TABLE {}.{} (date Date DEFAULT today(), date_time String, user_id UInt64, user_name String, text_id String, text String, label Float32) ENGINE = MergeTree(date, (date), 8192);'.format( dbName, tableName) client.execute(query) logging.info("Table is created successfully")
def create_table(ip=None): client = Client(ip) query = """CREATE TABLE IF NOT EXISTS vgame.anomaly ( `CreateDate` DateTime('Asia/Ho_Chi_Minh'), `perdiction` Nullable(Float64), `revenue` Nullable(Float64), `anomaly` Nullable(Float64) ) ENGINE = Log();""" client.execute(query)
def dbms_client(db_host, db_port, db_name): client = Client(db_host, db_port) create_db_query = f"CREATE DATABASE IF NOT EXISTS {escape_symbol(db_name)}" client.execute(create_db_query) use_db_query = f"USE {escape_symbol(db_name)}" client.execute(use_db_query) logger.info(f"created clickhouse db connection. db_name={db_name}") return client
def insertData(self, dbName, tableName, userID, userName, textID, text): # Inserting the data into the table client = Client(self.ipAddress) query = 'INSERT INTO {}.{} (user_id,user_name,text_id,text) VALUES'.format( dbName, tableName) client.execute(query, [{ 'user_id': userID, 'user_name': userName, 'text_id': textID, 'text': text }]) print('Record is added successfully')
def clean_clickhouse(domain, database): client = Client(host=domain) tables = client.execute( "SELECT name FROM system.tables WHERE database = %(db)s", {"db": database}) for table in tables: table_name = table[0].encode("utf-8") if table_name == "schema_migrations": continue else: table_name = database + "." + table_name client.execute( "TRUNCATE TABLE {table_name}".format(table_name=table_name))