async def ch_client():
client = ChClient(app['ch_http_session'],
url=f'http://sai.snad.space:8123',
database='ztf',
user='******')
await client.fetch('SELECT 1')
return client
def __init__(self, stat_provider_config):
super().__init__()
self.stat_provider_config = stat_provider_config
self.clickhouse_session = None
self.clickhouse_client = None
self.download_stats = {}
self.top_missed_stats = []
self.current_task = None
if stat_provider_config['enabled']:
self.clickhouse_session = ClientSession()
self.clickhouse_client = ChClient(
self.clickhouse_session,
url=stat_provider_config['clickhouse']['host'],
user=stat_provider_config['clickhouse']['username'],
password=stat_provider_config['clickhouse']['password'],
)
async def main():
print("dddsd")
async with ClientSession() as s:
client = ChClient(s,url='http://192.168.1.90:8123/',user='******',password='******',database='xxx')
alive= await client.is_alive() # returns True if connection is Ok
print(alive)
print("{{{0}}}:{1}".format("Name",12))
sql='INSERT INTO phalgorithm.pipeHeatAnalyse(id,calcTable) values (4,\'{{"pp":"dsf"}}\')'
await client.execute(sql)
async def put(table, lst):
async with ClientSession() as s:
client = ChClient(s,
database='offers_dims',
url='http://10.199.13.111:8123')
try:
await client.execute(
f'INSERT INTO {table} \
(source, link, price, area, hash, date, diap, storage, operation) \
VALUES', *lst)
except Exception as e:
print(f'{e.__class__.__name__}: {e}')
print('!!!ERROR!!!')
async def main():
async with ClientSession() as s:
client = ChClient(s, url="http://localhost:8123")
# preparing database
await client.execute(
"CREATE TABLE t (a UInt8, b Float32) ENGINE = Memory")
# making queries in parallel
await asyncio.gather(
some_query(client, 1000, 1000),
some_query(client, 2000, 1000),
some_query(client, 3000, 1000),
some_query(client, 4000, 1000),
some_query(client, 5000, 1000),
some_query(client, 6000, 1000),
)
async def bench_selects(*, retries: int, rows: int):
async with ClientSession() as s:
client = ChClient(s, compress_response=True)
# prepare environment
await prepare_db(client)
await insert_rows(client, row_data(), rows)
# actual testing
start_time = time.time()
for _ in range(retries):
await client.fetch("SELECT * FROM benchmark_tbl")
total_time = time.time() - start_time
avg_time = total_time / retries
print(
f"- Average time for selecting {rows} rows (from {retries} runs): {avg_time} seconds. Total: {total_time}"
)
async def bench_inserts(*, retries: int, rows: int):
async with ClientSession() as s:
client = ChClient(s, compress_response=True)
# prepare environment
await prepare_db(client)
# actual testing
one_row = row_data()
start_time = time.time()
for _ in range(retries):
await client.execute(
"INSERT INTO benchmark_tbl VALUES", *(one_row for _ in range(rows))
)
total_time = time.time() - start_time
avg_time = total_time / retries
print(
f"- Average time for inserting {rows} rows (from {retries} runs): {avg_time} seconds. Total: {total_time}"
)
async def bench_selects(*, retries: int, rows: int):
print("AIOCHCLIENT selects")
async with ClientSession() as s:
client = ChClient(s)
# prepare environment
await prepare_db(client)
await insert_rows(client, row_data(), rows)
# actual testing
start_time = time.time()
for _ in range(retries):
await client.fetch("SELECT * FROM benchmark_tbl")
total_time = time.time() - start_time
avg_time = total_time / retries
speed = int(1 / avg_time * rows)
print(
f"- Avg time for selecting {rows} rows from {retries} runs: {avg_time} sec. Total: {total_time}"
)
print(f" Speed: {speed} rows/sec")
async def bench_inserts(*, retries: int, rows: int):
print("AIOCHCLIENT inserts")
async with ClientSession() as s:
client = ChClient(s, compress_response=True)
# prepare environment
await prepare_db(client)
# actual testing
one_row = row_data()
start_time = time.time()
for _ in range(retries):
await client.execute("INSERT INTO benchmark_tbl VALUES",
*(one_row for _ in range(rows)))
total_time = time.time() - start_time
avg_time = total_time / retries
speed = int(1 / avg_time * rows)
print(
f"- Avg time for inserting {rows} rows from {retries} runs: {avg_time} sec. Total: {total_time}"
)
print(f" Speed: {speed} rows/sec")
def __init__(
self,
session: ClientSession,
url: str,
user: str,
password: str,
database: str,
compress_response: bool = True,
) -> NoReturn:
self.client = ChClient(
session=session,
url=url,
user=user,
password=password,
database=database,
compress_response=compress_response,
)
self.database = database
async def bench_selects_with_decoding(*, retries: int, rows: int):
print("AIOCHCLIENT selects with decoding")
async with ClientSession() as s:
client = ChClient(s, compress_response=True)
# prepare environment
await prepare_db(client)
await insert_rows(client, row_data(), rows)
# actual testing
start_time = time.time()
for _ in range(retries):
selected_rows = await client.fetch("SELECT * FROM benchmark_tbl")
# decoding:
selected_rows = [row[0] for row in selected_rows]
total_time = time.time() - start_time
avg_time = total_time / retries
speed = int(1 / avg_time * rows)
print(
f"- Avg time for selecting {rows} rows from {retries} runs: {avg_time} sec (with decoding). Total: {total_time}"
)
print(f" Speed: {speed} rows/sec")
async def query(url, user, passwd, db, sql):
try:
from aiochclient import ChClient
import aiohttp
import traceback
except:
logger.info(
"[Clickhouse] 导入 aiochclient 模块失败, 请输入命令 pip install aiochclient")
return {
"status": 2,
"result": "缺少 aoichclient 模块，请 pip install aiochclient 安装"
}
logger.info("[Clickhouse] APP执行参数为：{url} {database} {user} {passwd} {sql}",
url=url,
user=user,
passwd=passwd,
database=db,
sql=sql)
try:
async with aiohttp.ClientSession() as s:
client = ChClient(s,
url=url,
user=user,
password=passwd,
database=db)
all_rows = await client.fetch(sql)
result = []
for row in all_rows:
tmp = dict()
for i in row.items():
tmp[i[0]] = i[1]
result.append(tmp)
except Exception as e:
return {"status": 2, "result": str(e)}
else:
return {"status": 0, "result": result}
async def init_ch_client(app: web.Application):
app['http_session'] = ClientSession()
app['ch'] = ChClient(app['http_session'], **app['config']['clickhouse'])
async def main():
async with ClientSession() as s:
client = ChClient(s, url="http://localhost:8123")
assert await client.is_alive()
class StatProvider(AioThing):
def __init__(self, stat_provider_config):
super().__init__()
self.stat_provider_config = stat_provider_config
self.clickhouse_session = None
self.clickhouse_client = None
self.download_stats = {}
self.top_missed_stats = []
self.current_task = None
if stat_provider_config['enabled']:
self.clickhouse_session = ClientSession()
self.clickhouse_client = ChClient(
self.clickhouse_session,
url=stat_provider_config['clickhouse']['host'],
user=stat_provider_config['clickhouse']['username'],
password=stat_provider_config['clickhouse']['password'],
)
@asynccontextmanager
async def _safe_execution(self):
try:
yield
except asyncio.CancelledError as e:
logging.getLogger('error').warning(e)
except Exception as e:
logging.getLogger('error').error(e)
raise
async def load_download_stats(self):
async with self._safe_execution():
download_stats = {}
logging.getLogger('statbox').info({
'action': 'start_loading',
'stats': 'download_stats',
})
async for row in self.clickhouse_client.iterate('''
select id, count(distinct user_id) as c
from query_log where mode = 'get' and id != 0
group by id
'''):
download_stats[row['id']] = DocumentStat(
downloads_count=row['c'])
self.download_stats = download_stats
logging.getLogger('statbox').info({
'action': 'loaded',
'stats': 'download_stats',
'items': len(download_stats),
})
await asyncio.sleep(self.stat_provider_config['download_stats']
['refresh_time_secs'])
async def load_top_missed_stats(self):
async with self._safe_execution():
top_missed_stats = []
logging.getLogger('statbox').info({
'action': 'start_loading',
'stats': 'top_missing',
})
async for row in self.clickhouse_client.iterate('''
select
document_id,
count(distinct chat_id) as c from telegram_statbox_log
where action = 'missed' and
(mode = 'start_delivery' or mode = 'delivery') and
schema = 'scimag'
group by document_id
order by count(distinct chat_id) desc, document_id desc limit 1000
'''):
top_missed_stats.append(row['document_id'])
self.top_missed_stats = top_missed_stats
logging.getLogger('statbox').info({
'action': 'loaded',
'stats': 'top_missing',
'items': len(top_missed_stats),
})
await asyncio.sleep(self.stat_provider_config['top_missed_stats']
['refresh_time_secs'])
def get_download_stats(self, document_id, default=None):
return self.download_stats.get(document_id, default)
def get_top_missed_stats(self):
return self.top_missed_stats
async def all_tasks(self):
return await asyncio.gather(
self.load_download_stats(),
self.load_top_missed_stats(),
)
async def start(self):
if self.clickhouse_session:
self.current_task = asyncio.create_task(self.all_tasks())
async def stop(self):
await self.cancel()
if self.clickhouse_session:
await self.clickhouse_session.close()
if self.current_task:
self.current_task.cancel()
await self.current_task
await super().stop()
async def chclient(request, http_client):
async with ChClient(http_client(), **request.param) as chclient:
yield chclient
async def chclient(request):
async with aiohttp.ClientSession() as s:
yield ChClient(s, **request.param)