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