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))
