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 }