class ClientDB:
def __init__(self, url_server_click_haus='localhost'):
self.client = Client(url_server_click_haus)
self.list_fields = {
'Pressure': 'Float64',
'Humidity': 'Float64',
'Area_temperature': 'Float64',
'Work_temperature': 'Float64',
'pH_level': 'Float64',
'Weight': 'Float64',
'Fluid_flow': 'Float64',
'CO2_flow': 'Float64',
}
def create_bd(self, name_bd: str):
self.client.execute(f'CREATE DATABASE IF NOT EXISTS {name_bd}')
def create_table(self):
tmp = list()
for field in self.list_fields:
tmp.append(" ".join(
i for i in ["'" + field + "'", self.list_fields[field]]))
field_table = ', '.join(i for i in tmp)
sql = f'CREATE TABLE test_table({field_table})'
self.client.execute(sql)
def test(self):
return self.client.execute('SELECT 1')
def main():
"""
connects to clickhouse and calls parser and inserts data
:return: nothing
"""
sock = socket.socket()
port = 2003
sock.bind(("", port))
taglist = []
# connection to clickhouse
client = Client("clickhouse")
for i in range(100):
sock.listen(1)
conn, addr = sock.accept()
data = conn.recv(512)
insert_data, tag_to_add, tags, taglist = parse_tagged_data(
data, taglist)
if tag_to_add != [] and tag_to_add:
for tag in tag_to_add:
taglist.append(tag)
(client.execute("ALTER TABLE events.tmp ADD \
COLUMN IF NOT EXISTS " + tag + " UInt32 AFTER path"))
if insert_data:
(client.execute("INSERT INTO events.tmp (" + tags + ") VALUES",
[insert_data]))
print(client.execute("SELECT * FROM events.tmp"))
def test_alt_hosts(self):
client = Client('wrong_host',
1234,
self.database,
self.user,
self.password,
alt_hosts='{}:{}'.format(self.host, self.port))
self.n_calls = 0
getaddrinfo = socket.getaddrinfo
def side_getaddrinfo(host, *args, **kwargs):
if host == 'wrong_host':
self.n_calls += 1
raise socket.error(-2, 'Name or service not known')
return getaddrinfo(host, *args, **kwargs)
with patch('socket.getaddrinfo') as mocked_getaddrinfo:
mocked_getaddrinfo.side_effect = side_getaddrinfo
rv = client.execute('SELECT 1')
self.assertEqual(rv, [(1, )])
client.disconnect()
rv = client.execute('SELECT 1')
self.assertEqual(rv, [(1, )])
# Last host must be remembered and getaddrinfo must call exactly
# once with host == 'wrong_host'.
self.assertEqual(self.n_calls, 1)
client.disconnect()
def test_network_error(self):
client = Client('bad-address')
with patch('socket.getaddrinfo') as mocked_getaddrinfo:
mocked_getaddrinfo.side_effect = socket.error(
-2, 'Name or service not known')
with self.assertRaises(errors.NetworkError):
client.execute('SHOW TABLES')
def iter_download_from_clickhouse_to_csv(time_str, days_to_train, days_to_test):
clickhouse_hosts = ['db101', 'db102', 'db103', 'db104', 'db105']
cc = Client(random.choice(clickhouse_hosts), compression='lz4')
# дату кликов смотрим дальше, чтобы догнать поздние клики
clickhouse_query_template_clicks = """
SELECT
SessionId
FROM statistic.DistributedSessions
PREWHERE (Date >= '{0}') AND (Date < '{1}') AND (ClicksCount = 1) AND (SessionId != '')
FORMAT CSV
"""
clickhouse_query_template_views = """
SELECT
{0}
FROM statistic.DistributedSessions
PREWHERE (Date >= '{1}') AND (Date < '{2}') AND (ImpressionsCount = 1) AND (SessionId != '')
FORMAT CSV
"""
test_time_str = time_str
train_time_str = time_str
for i in range(0, days_to_train):
if i==0:
train_datetime_from = datetime(*time.strptime(train_time_str, '%Y-%m-%d %H:%M:%S')[:6])
train_datetime_till = train_datetime_from + timedelta(days=1)
train_datetime_till_clicks = train_datetime_till + timedelta(hours=2)
views_train = cc.execute(clickhouse_query_template_views.format(', '.join(ch_fields), train_datetime_from, train_datetime_till))
clicks_train = cc.execute(clickhouse_query_template_clicks.format(train_datetime_from, train_datetime_till_clicks))
raw_train_df = make_dataframe(views_train, clicks_train)
#raw_train_df = df_features(raw_train_df)
if not os.path.isfile('train.csv'):
raw_train_df.to_csv('train.csv', header='column_names')
else: # else it exists so append without writing the header
raw_train_df.to_csv('train.csv', mode='a', header=False)
train_time_str = train_datetime_till
for i in range(0, days_to_test):
if i==0:
test_datetime_from = test_time_str - timedelta(days=days_to_test)
test_datetime_till = test_datetime_from + timedelta(days=1)
test_datetime_till_clicks = test_datetime_till + timedelta(hours=2)
views_test = cc.execute(clickhouse_query_template_views.format(', '.join(ch_fields), test_datetime_from, test_datetime_till))
clicks_test = cc.execute(clickhouse_query_template_clicks.format(test_datetime_from, test_datetime_till_clicks))
raw_test_df = make_dataframe(views_test, clicks_test)
#raw_test_df = df_features(raw_test_df)
if not os.path.isfile('test.csv'):
raw_test_df.to_csv('test.csv', header='column_names')
else: # else it exists so append without writing the header
raw_test_df.to_csv('test.csv', mode='a', header=False)
test_time_str = test_datetime_till
cc.disconnect()
def call_exec_click(self, query, param=None):
conn = Client(**self.get_config())
if param:
result = conn.execute(query, params=param, types_check=True,)
else:
print('try execute...')
result = conn.execute(query)
print(result)
print('try returning...')
conn.disconnect()
return result
def test_exception_on_hello_packet(self):
client = Client(self.host, self.port, self.database, 'wrong_user')
with self.assertRaises(errors.ServerException) as e:
client.execute('SHOW TABLES')
client.disconnect()
# Simple exception formatting checks
exc = e.exception
self.assertIn('Code:', str(exc))
self.assertIn('Stack trace:', str(exc))
def get_activated_sources():
DB_HOST = os.getenv('DB_HOST')
PATH_LOG_FILE = os.getenv('PATH_LOG_FILE')
client = Client(f'{DB_HOST}')
print(client.execute("DROP TABLE IF EXISTS admin_backend_logs.logs"))
os.system(
f'time clickhouse-client --query="INSERT INTO admin_backend_logs.logs_tmp FORMAT CSV" < {PATH_LOG_FILE}'
)
print(
client.execute(
"CREATE TABLE admin_backend_logs.logs ENGINE = ReplacingMergeTree(day, (date), 8192) AS SELECT DISTINCT toDate(date) AS day, date, method, originalUrl, statusCode, contentType, userAgent, ip, userId FROM admin_backend_logs.logs_tmp;"
))
return 1
def call_select_click(self,query,param=None):
print(self.get_config())
conn = Client(**self.get_config())
print('try exec')
result = conn.execute(query)
conn.disconnect()
return result
def clickhouseConnection(self):
config = self.clickhouseConfig()
# 连接数据库
client = Client(config["http"], config["port"], config["database"],
config["user"], config["pwd"])
a = client.execute("show tables")
print(a)
def boevik():
client = Client('127.0.0.1')
settt = {}
genres = ['Боевики', 'Вестерны', "Детективы", "Для детей", 'Комедии', "Мелодрамы", "Мультфильмы", "Приключения", "Спорт", "Триллеры", "Ужасы", "Фантастика"]
for i in genres:
data = client.execute(f"""SELECT assetid, arrayElement(groupArray(title), 1), count() as cnt FROM events WHERE has(splitByChar(',', genretitles), '{i}') = 1 and (eventtype = 31 or eventtype = 15) GROUP BY assetid ORDER BY cnt DESC LIMIT 5""")
settt[i] = data
return {'fuckinAll': settt}
def add_message(likes):
list_like = likes
client = Client('127.0.0.1')
settt = []
hold_me = client.execute(f"""SELECT topK(25)(arrayJoin(sim)) from (select d, arrayFlatten(groupArray(sim)) as sim from (select splitByChar(',', similar_assetids) as sim, today() as d from similarfilms where assetid in ({list_like})) group by d)""")
for i in hold_me[0][0]:
hold_bitch = client.execute(f"""select assetid, arrayElement(groupArray(title), 1) from events where assetid = {i} group by assetid""")
try:
tit_id = hold_bitch[0]
except IndexError:
continue
settt.append(tit_id)
if len(settt) == 25:
break
return {'custoized' : settt}
def similar(id):
client = Client('127.0.0.1')
settt = []
data = client.execute(f"""SELECT similar_assetids FROM similarfilms WHERE assetid = {id}""")[0][0]
splitted = data.split(',')
for i in splitted:
hold_bitch = client.execute(f"""select assetid, arrayElement(groupArray(title), 1) from events where assetid = {i} group by assetid""")
try:
tit_id = hold_bitch[0]
except IndexError:
continue
settt.append(tit_id)
if len(settt) == 5:
break
return {'Fuckinall': settt}
def init(table_name="events"):
"""
:param table_name:name of table for mecrics
:return: ok
to_do: add checks
"""
# connection to clickhous
client = Client("clickhouse")
# creating database and table
print(client.execute("CREATE DATABASE IF NOT EXISTS " + table_name))
print(
client.execute(
"CREATE TABLE IF NOT EXISTS events.tmp(\n \
timestmp UInt32, \n \
last_volume UInt32, \n \
path String \n \
) ENGINE MergeTree() PARTITION BY\
timestmp ORDER BY timestmp SETTINGS index_granularity=8192;"
)
)
return "created: events.tmp"
def execute(query: str, data=None):
if os.environ.get("DEPLOY_MODE") != "PROD":
client = ClickHouseClient('clickhouse')
else:
host = os.environ.get("CLICKHOUSE_HOST")
port = os.environ.get("CLICKHOUSE_PORT")
user = os.environ.get("CLICKHOUSE_USER")
pasw = os.environ.get("CLICKHOUSE_PASSWORD")
client = ClickHouseClient(host,
port=port,
user=user,
password=pasw)
res = client.execute(query, data)
client.disconnect()
return res
def init(table_name='events'):
'''
:param table_name:name of table for mecrics
:return: ok
to_do: add checks
'''
# connection to clickhous
sock = socket.socket()
PORT = 2003
sock.bind(("", PORT))
client = Client('clickhouse')
# creating database and table
print(client.execute("CREATE DATABASE IF NOT EXISTS " + table_name))
print(client.execute(
'CREATE TABLE IF NOT EXISTS events.tmp(\n \
timestmp UInt32, \n \
last_volume UInt32, \n \
path String \n \
) ENGINE MergeTree() PARTITION BY timestmp ORDER BY timestmp SETTINGS index_granularity=8192;'))
return 'created: events.tmp'
def test_alt_hosts(self):
client = Client('wrong_host',
1234,
self.database,
self.user,
self.password,
alt_hosts='{}:{}'.format(self.host, self.port))
getaddrinfo = socket.getaddrinfo
def side_getaddrinfo(host, *args, **kwargs):
if host == 'wrong_host':
raise socket.error(-2, 'Name or service not known')
return getaddrinfo(host, *args, **kwargs)
with patch('socket.getaddrinfo') as mocked_getaddrinfo:
mocked_getaddrinfo.side_effect = side_getaddrinfo
rv = client.execute('SELECT 1')
self.assertEqual(rv, [(1, )])
client.disconnect()
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from clickhouse_driver.client import Client
if __name__ == '__main__':
client = Client(host='localhost', user='******', database='default', password='******')
'''
print(client.execute('show databases'))
client.execute('USE default')
print(client.execute('SHOW TABLES'))
client.execute('DROP TABLE IF EXISTS test')
client.execute('CREATE TABLE test (x Int32, y Int32, str String) ENGINE = Memory')
client.execute('INSERT INTO test (x, y, str) VALUES', [(1, 2, "aaa"), (2, 2, "bbb")])
client.execute('INSERT INTO test (x) VALUES', [[200]])
print(client.execute('SELECT sum(y) FROM test'))
print(type(client.execute('SELECT * FROM test')))
print(client.execute('SELECT * FROM test'))
'''
with open(r'../hotel/result.csv', 'r', encoding='utf8') as data:
csv = data.read()
sql = "INSERT INTO iris FORMAT CSV \n" + str(csv) + "\n;"
client.execute(sql)
#client.execute('INSERT INTO linkedin', r'c:\19.txt')
sock = socket.socket()
PORT = 2003
sock.bind(("", PORT))
Taglist = []
if __name__ == "__main__":
# connection to clickhouse
client = Client('clickhouse')
for i in range(100):
sock.listen(1)
conn, addr = sock.accept()
data = conn.recv(512)
insert_data, Tag_to_add, tags = parse_tagged_data(data)
if Tag_to_add != [] and Tag_to_add != None:
for tag in Tag_to_add:
Taglist.append(tag)
(client.execute(
'ALTER TABLE events.tmp ADD COLUMN IF NOT EXISTS ' + tag +
' UInt32 AFTER path'))
if insert_data != None:
(client.execute('INSERT INTO events.tmp (' + tags + ') VALUES',
[insert_data]))
print(client.execute('SELECT * FROM events.tmp'))
Created on 2018年10月11日
@author: wzt
设计思路:
1：this clickhouse example code
'''
from clickhouse_driver.client import Client
client = Client(host='172.20.10.8',
database='default',
user='******',
password='******',
compression='zstd')
client.execute('SHOW TABLES')
client.execute('DROP TABLE IF EXISTS monchickey.test_py27')
client.execute('CREATE TABLE monchickey.test_py27 (x Int32) ENGINE = Memory')
client.execute('INSERT INTO monchickey.test_py27 (x) VALUES', [{
'x': 1
}, {
'x': 2
}, {
'x': 3
}, {
'x': 100
}])
client.execute('INSERT INTO monchickey.test_py27 (x) VALUES', [[200]])
#!/opt/anaconda2/bin/python2.7
# -*- coding: utf8 -*-
'''
Created on 2018年10月11日
@author: wzt
设计思路:
1：this clickhouse example code
'''
from clickhouse_driver.client import Client
client = Client('172.20.10.8')
client.execute('SHOW TABLES')
client.execute('DROP TABLE IF EXISTS test')
client.execute('CREATE TABLE test (x Int32) ENGINE = Memory')
client.execute('INSERT INTO test (x) VALUES', [{
'x': 1
}, {
'x': 2
}, {
'x': 3
}, {
'x': 100
}])
client.execute('INSERT INTO test (x) VALUES', [[200]])
def top25():
client = Client('127.0.0.1')
data = client.execute('SELECT assetid, arrayElement(groupArray(title), 1) as title, count() AS cnt_watch FROM events WHERE eventtype = 31 GROUP BY assetid ORDER BY cnt_watch DESC LIMIT 25')
return {'top25': data}
def get_cur_time():
client = Client('127.0.0.1')
data = client.execute('select 1')
return {'databasa': data}
class ClickHouse:
def __init__(self):
if os.environ.get("DEPLOY_MODE") != "PROD":
self.client = ClickHouseClient('clickhouse')
else:
host = os.environ.get("CLICKHOUSE_HOST")
port = os.environ.get("CLICKHOUSE_PORT")
user = os.environ.get("CLICKHOUSE_USER")
pasw = os.environ.get("CLICKHOUSE_PASSWORD")
self.client = ClickHouseClient(host, port=port, user=user, password=pasw)
def execute(self, query: str, data=None):
return self.client.execute(query, data)
def disconnect(self):
self.client.disconnect()
@staticmethod
def save_transaction(
tx_uuid: str,
from_login: str, from_base_currency: int,
to_login: str, to_base_currency: int,
original_amount: float, converted_amount: float,
cr_uuid1: str, cr_uuid2: str, dt):
""" Saving transaction for the history and reports """
clickhouse = ClickHouse()
clickhouse.execute(
"INSERT INTO `outgoing_transactions` "
"(`tx_uuid`, `login`, `base_currency`, `amount`, `cr_uuid`, `datetime`) "
"VALUES",
[
{
"tx_uuid": tx_uuid,
"login": from_login,
"base_currency": from_base_currency,
"amount": original_amount,
"cr_uuid": cr_uuid1 or "",
"datetime": dt
}
]
)
clickhouse.execute(
"INSERT INTO `incoming_transactions` "
"(`tx_uuid`, `login`, `base_currency`, `amount`, `cr_uuid`, `datetime`) "
"VALUES",
[
{
"tx_uuid": tx_uuid,
"login": to_login,
"base_currency": to_base_currency,
"amount": converted_amount,
"cr_uuid": cr_uuid2 or "",
"datetime": dt
}
]
)
clickhouse.disconnect()
return True
@staticmethod
def get_transactions(login: str, period_starts=None, period_ends=None):
""" Getting transactions history for reports """
period_starts_bounds, period_ends_bounds = "", ""
if period_starts:
period_starts_bounds = " and `datetime` > '{period_starts.isoformat()}' "
if period_ends:
period_ends_bounds = " and `datetime` < '{period_ends.isoformat()}' "
clickhouse = ClickHouse()
incoming = clickhouse.execute(f"""
SELECT
`tx_uuid`, `login`, `base_currency`, round(`amount`, 4), `cr_uuid`, `datetime`
FROM `incoming_transactions` WHERE login = '******'
{period_starts_bounds} {period_ends_bounds}
""")
outgoing = clickhouse.execute(f"""
SELECT
`tx_uuid`, `login`, `base_currency`, round(`amount`, 4), `cr_uuid`, `datetime`
FROM `outgoing_transactions` WHERE login = '******'
{period_starts_bounds} {period_ends_bounds}
""")
clickhouse.disconnect()
return {
"incoming": incoming,
"outgoing": outgoing
}
def test_network_error(self):
client = Client('bad-address')
with self.assertRaises(errors.NetworkError):
client.execute('SHOW TABLES')