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)
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 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 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 __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 __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 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 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 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 _create_client(self):
return Client(self.host,
self.port,
self.database,
self.user,
self.password,
compression=self.compression)
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 _create_client(self, **kwargs):
client_kwargs = {
'port': self.port,
'database': self.database,
'user': self.user,
'password': self.password
}
client_kwargs.update(kwargs)
return Client(self.host, **client_kwargs)
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 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 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()
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 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 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 _create_client(self):
settings = None
if self.compression:
# Set server compression method explicitly
# By default server sends blocks compressed by LZ4.
method = self.compression
if self.server_version > (19, ):
method = method.upper()
settings = {'network_compression_method': method}
return Client(self.host,
self.port,
self.database,
self.user,
self.password,
compression=self.compression,
settings=settings)
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))
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_default_compression(self):
client = Client('localhost', compression=True)
self.assertEqual(client.connection.compressor_cls, Compressor)
#!/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]])
import socket
from clickhouse_driver.client import Client
from Parser import parse_tagged_data
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',
#!/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(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
}, {
def create_client(self, **kwargs):
return Client(self.host, self.port, self.database, self.user,
self.password, **kwargs)
# endflow = unpackbufferint(flowpacket, recordpointer + 28, 4)
srcport = unpackbufferint(flowpacket, recordpointer + 32, 2)
dstport = unpackbufferint(flowpacket, recordpointer + 34, 2)
l4proto = unpackbufferint(flowpacket, recordpointer + 38, 1)
ClickHouse.execute("INSERT INTO NETFLOW.FLOWS (EventTimestamp,SrcIp,DstIp,Proto,SrcPort,DstPort) values", \
[{'EventTimestamp': timestamp, \
'SrcIp': srcaddr, \
'DstIp': dstaddr, \
'Proto': l4proto, \
'SrcPort': srcport, \
'DstPort': dstport }])
def main():
server = socketserver.UDPServer((HOST,PORT), SyslogUDPHandler)
server_thread = threading.Thread(target=server.serve_forever(poll_interval=0.5))
server_thread.daemon = True
server_thread.start()
daemon_params = {'app': 'syslog-server',
'pid': pid,
'action': main,
'user': '******' }
ClickHouse = Client(clickhouse_host)
daemon = Daemonize(**daemon_params)
daemon.start()
#!/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')
