class ClickhouseClient: """ clickhouse封装 """ def __init__(self, host: str, port: int, database: str, user: str, password: str) -> None: self.conn = Client(host=host, port=port, database=database, user=user, password=password) def close(self): self.conn.disconnect() def query(self, query: str, params: Any = None) -> List[dict]: """ 查询一条结果 :param query: 查询语句 :param params: 查询参数 :return: List[dict] """ try: data = self.conn.execute_iter(query, params, with_column_types=True) columns = [column[0] for column in next(data)] temp = [] for row in data: temp.append( json.dumps(dict(zip(columns, [value for value in row])), cls=DateEncoder)) except Exception as e: raise e finally: self.close() return temp
def clkhs_artificial_load(): do_logging('Thread ' + str(threading.get_ident()) + ' starting') clk_settings = {'max_threads': 8, 'max_block_size': 5000} client = Client(host=clkhs_instance, port='', settings=clk_settings, connect_timeout=60, send_receive_timeout=900, sync_request_timeout=120) settings = {'max_block_size': 5000} while (not clkhs_stop_threads): try: # Select a query to issue query = clkhs_artificial_queries[random.randint(0, 8)] do_logging('Thread ' + str(threading.get_ident()) + ' starting background query: ' + query) # Issue query and iterate through result set result = client.execute_iter(query, settings) for x in result: if (clkhs_stop_threads): return except Exception as e: # This is generally a serverside memory exception do_logging('Thread ' + str(threading.get_ident()) + ' encountered an exception:') do_logging(e) time.sleep(5) do_logging('Continuing after exception') continue
def create_database(): ''' Функция создаст ClickHouse-базу данных и пополнит каждую её таблицу информацией, обеспечивающей быстрый доступ к элементам соответствующей сжатой исходной таблицы. ''' ind_dir_path = os.path.normpath( input('\nПуть к папке с индексируемыми архивами: ')) trg_dir_path = input('\nПуть к папке для результатов: ') #Имя базы данных сделаем для простоты почти #тем же, что и у папки с индексируемыми файлами. #Соединение с ClickHouse, создание клиент-объекта. db_name = f'DBCH{os.path.basename(ind_dir_path)}' client = Client('localhost') #Проверка на наличие базы, #созданной по тем же данным #при прошлых запусках программы. #Если предыдущая БД обнаружилась, то #выведудся имена хранимых там таблиц #и столбцов, а также типы данных. if (f'{db_name}', ) in client.execute('SHOW DATABASES'): print(f'\nБаза данных {db_name} уже существует') client.execute(f'USE {db_name}') tab_names = [ tup[0] for tup in client.execute('SHOW TABLES') if tup[0] != 'header' ] table_struc = client.execute_iter(f'DESCRIBE TABLE {tab_names[0]}') col_names_n_types = { tup[0]: tup[1] for tup in table_struc if tup[0] != 'line_start' } print('\nТаблицы ранее созданной базы данных:\n', tab_names) print( '\nСтолбцы таблиц и соответствующие типы данных ранее созданной БД:\n', col_names_n_types) #Раз БД, соответствующая таблицам #выбранной папки, ранее была создана, #то можно сразу приступать к её #эксплуатации с помощью фронтенда. #Иной вариант - создать базу заново, #чтобы, например, переиндексировать #эти таблицы по другим столбцам. recreate = input('''\nПересоздать базу данных? [yes(|y)|no(|n|<enter>)]: ''') if recreate in ['yes', 'y']: client.execute(f'DROP DATABASE {db_name}') elif recreate in ['no', 'n', '']: return ind_dir_path, trg_dir_path, db_name, tab_names, col_names_n_types else: print(f'{recreate} - недопустимая опция') sys.exit() ram = int(input('\nОбъём оперативной памяти компьютера, Гбайт: ')) * 1e9 detect_headers = input( '''\nРаспознавать хэдеры VCF (##) и UCSC (track_name=) индексируемых таблиц автоматически, или потом вы укажете количество хэдеров самостоятельно? (Предпросмотрщик больших файлов есть в репозитории https://github.com/PlatonB/bioinformatic-python-scripts) [auto(|a)|manual(|m)]: ''') if detect_headers in ['auto', 'a']: num_of_unind = None elif detect_headers in ['manual', 'm']: num_of_unind = input('''\nКоличество не обрабатываемых строк в начале каждой индексируемой таблицы (Важно! Табулированную шапку к ним не причисляйте) (игнорирование ввода ==> производить работу для всех строк) [0(|<enter>)|1|2|...]: ''') if num_of_unind == '': num_of_unind = 0 else: num_of_unind = int(num_of_unind) else: print(f'{detect_headers} - недопустимая опция') sys.exit() cont, col_info = 'y', {} while cont not in ['no', 'n', '']: col_name = input('''\nИмя индексируемого столбца (Нужно соблюдать регистр) [#Chrom|pos|RSID|...]: ''') col_name = ''.join(col_name.split('#')) data_type = input( '''\nВ выбранном столбце - целые числа, вещественные числа или строки? (примеры вещественного числа: 0.05, 2.5e-12) (примеры строки: X, Y, A/C/G, rs11624464, HLA-DQB1) [integer(|i)|decimal(|d)|string(|s)]: ''') if data_type in ['integer', 'i']: data_type = 'Int64' tale = None elif data_type in ['decimal', 'd']: tale = input('''\nСколько оставлять знаков после точки? (игнорирование ввода ==> 5) [...|5(|<enter>)|...|18): ''') if tale == '': tale = '5' elif 0 > int(tale) > 18: print(f'{tale} - недопустимая опция') sys.exit() data_type = f'Decimal64({tale})' elif data_type in ['string', 's']: data_type = 'String' tale = None else: print(f'{data_type} - недопустимая опция') sys.exit() col_info[col_name] = [data_type, 'cell_index', tale] cont = input('''\nПроиндексировать по ещё одному столбцу? (игнорирование ввода ==> нет) [yes(|y)|no(|n|<enter>)]: ''') if cont not in ['yes', 'y', 'no', 'n', '']: print('{cont} - недопустимая опция') sys.exit() #Доукомплектовываем созданный в рамках #пользовательского диалога словарь с названиями #и характеристиками выбранных пользователем #столбцов парой ключ-значение, описывающей #столбец индексов архивированной таблицы. col_info['line_start'] = ['Int64'] #Получаем названия указанных пользователем #столбцов и столбца с индексами сжатой таблицы. col_names = list(col_info.keys()) #ClickHouse не индексирует, а просто сортирует столбцы. #Выделим для сортировки половину оперативной памяти. #Если этого объёма не хватит, то ClickHouse задействует #внешнюю сортировку - размещение фрагментов столбца на #диске, сортировку каждого из них и поэтапное слияние. client.execute(f'SET max_bytes_before_external_sort = {int(ram) // 2}') #Создание БД, и выбор этой БД для использования #во всех последующих запросах по умолчанию. client.execute(f'CREATE DATABASE {db_name}') client.execute(f'USE {db_name}') print('') #Работа с архивами, каждый из #которых содержит по одной таблице. arc_file_names = os.listdir(ind_dir_path) for arc_file_name in arc_file_names: if arc_file_name.startswith('.~lock.'): continue with gzip.open(os.path.join(ind_dir_path, arc_file_name), mode='rt') as arc_file_opened: #Автоматическое определение и прочтение #вхолостую хэдеров таблиц распространённых #биоинформатических форматов VCF и UCSC BED. #Последний из прочитанных хэдеров (он #же - шапка таблицы) будет сохранён. if num_of_unind == None: while True: header_row = process_line(arc_file_opened) if re.match(r'##|track_name=', header_row[0]) == None: break #Холостое прочтение хэдеров, количество которых #указано пользователем, и сохранение шапки. else: for unind_index in range(num_of_unind): arc_file_opened.readline() header_row = process_line(arc_file_opened) #Обязательное требование программы - #единообразие исходных таблиц. #Доказательством соблюдения этого правила #будет считаться одинаковость шапок. #Шапка первой обрабатываемой таблицы #назначается референсной, а шапки #следующих таблиц будут с ней сопоставляться. if 'common_header_row' not in locals(): common_header_row = copy.deepcopy(header_row) elif header_row != common_header_row: print('Шапки индексируемых таблиц не совпадают') sys.exit() #Элементы шапки, озаглавливающие #выбранные пользователем столбцы, #станут потом именами столбцов БД. #Поскольку в этих именах не должно #быть символа # (таковы требования #со стороны ClickHouse), убираем его. for header_cell_index in range(len(header_row)): if header_row[header_cell_index].find('#') != -1: header_row[header_cell_index] = ''.join( header_row[header_cell_index].split('#')) #На этапе пользовательского диалога был #создан словарь с указанными пользователем #именами будущих столбцов БД и соответствующими #поддерживаемыми ClickHouse типами данных. #Добавляем ко всем ключам словаря, #кроме отвечающего за столбец стартов #строк, индексы имён этих столбцов, #обозначающие их позицию в шапке. #Эти же индексы будут определять #положение соответствующих ячеек в #каждой строке исходной таблицы. for col_name in col_names[:-1]: col_info[col_name][1] = header_row.index(col_name) #Для простоты назовём таблицы БД теми же #именами, что и у исходных, но только без #точек и дефисов, т.к. наличие таковых в #именах таблиц ClickHouse-баз недопустимо. #Таблицам также нельзя присваивать имена, #начинающиеся с цифры, поэтому добавим #каждому имени буквенную приставку. tab_name = 'TBL' + arc_file_name.replace('.', 'DOT').replace( '-', 'DEFIS') #Создаём таблицу БД, которая после #дальнейшего заполнения будет служить #путеводителем по соответствующей #gzip-архивированной крупной таблице. #Имя и тип данных каждого столбца БД #берём из ранее сформированного словаря. #По умолчанию ClickHouse сжимает каждый #столбец очень быстрым, но практически #не уменьшающим размер алгоритмом LZ4. #Применем к столбцам оптимальный по #скорости и степени компрессии Zstandart. client.execute(f'''CREATE TABLE {tab_name} ({", ".join([col_name + " " + col_ann[0] + " CODEC(ZSTD(22))" for col_name, col_ann in col_info.items()])}) ENGINE = MergeTree() ORDER BY ({", ".join(col_names[:-1])})''' ) print(f'Таблица {tab_name} новой базы данных пополняется') #Данные будут поступать в #базу одной или более порциями. #Для контроля работы с порциями #далее будет отмеряться их размер. #Назначаем ноль в качестве #стартового значения этой величины. fragment, fragment_len = [], 0 #Таблица БД будет пополняться #до тех пор, пока не закончится #перебор строк исходной таблицы. while True: #Размер порции в 100000 строк #соответствует рекомендации из #официальной документации ClickHouse. if fragment_len == 100000: client.execute( f'''INSERT INTO {tab_name} ({", ".join(col_names)}) VALUES''', fragment) #После добавления порции список, #её содержащий, очищается, а #счётчик её размера обнуляется. fragment.clear() fragment_len = 0 #Получение байтовой позиции начала #текущей строки исходной таблицы. #Устранение \n и разбиение #этой строки на список. line_start, row = arc_file_opened.tell(), process_line( arc_file_opened) #Чтение исходной таблицы завершено. #Вероятнее всего, количество строк #таблицы не кратно 100000, поэтому #к этому моменту накопилось ещё #некоторое количество данных. #Допропишем тогда их в базу. if row == ['']: if fragment_len > 0: client.execute( f'''INSERT INTO {tab_name} ({", ".join(col_names)}) VALUES''', fragment) break #Отбор ячеек тех столбцов сжатой #таблицы, по которым индексируем. #Сохранение этих ячеек и стартовых #позиций табличных строк в список. cells = fetch_cells(row, col_info, line_start) #Пополнение порции с нуля, в т.ч. #после отправки в БД предыдущей, #либо достройка текущей порции. fragment.append(dict(zip(col_names, cells))) #В любом случае, инкрементируем #счётчик размера порции. fragment_len += 1 #Соберём информацию об устройстве базы данных. #Она будет далее использоваться фронтендами. #Выведем также эти сведения на экран, #чтобы пользователю при запусках фронтендов #было очень легко в базе разобраться. tab_names = [tup[0] for tup in client.execute('SHOW TABLES')] col_names_n_types = { col_name: col_ann[0] for col_name, col_ann in col_info.items() if col_name != 'line_start' } print('\nТаблицы новой базы данных:\n', tab_names) print('\nСтолбцы таблиц и соответствующие типы данных новой БД:\n', col_names_n_types) #Общая для всех исходных таблиц шапка #направится в отдельную таблицу БД. client.execute('''CREATE TABLE header (header_cells String) ENGINE = TinyLog''') client.execute( f'''INSERT INTO header (header_cells) VALUES''', [{ 'header_cells': header_cell } for header_cell in common_header_row]) client.disconnect() return ind_dir_path, trg_dir_path, db_name, tab_names, col_names_n_types
#(жёсткий вариант), либо элементы #с, как минимум, одним правым #NULL (щадящий вариант). where = [f'{right_tab_name}.{col_name} {right_tab_dest}' \ for right_tab_name in right_tab_names if right_tab_name != left_tab_name] print(f'\nРабота с таблицей {left_tab_name} базы данных') #Инструкция, собственно, #пересечения или вычитания. #Результат - остающиеся байтовые #позиции начала строк соответствующей #(левой) архивированной таблицы. res = client.execute_iter( f'''SELECT {left_tab_name}.line_start FROM {left_tab_name} {" ".join(left_join)} WHERE {logical.join(where)}''' ) print(f'Извлечение отобранных строк таблицы {left_arc_file_name}') #Перемещение курсора по сжатой таблице к #началу каждой отвечающей запросу строки. #Очередная новая позиция курсора отсчитывается #не от начала файла, а от последней запомненной #позиции, что в ряде случаев приводит к #достижению колоссальной производительности. #Прописывание отобранных строк в конечный файл. #Присвоение флагу значения, показывающего #наличие в конечном файле нехэдерных строк. cur_pointer = 0
def clickhouse2deps(args): """ Extract dependencies from UASTs in a Clickhouse DB. """ log = logging.getLogger("clickhouse2deps") output_path = path_with_suffix(args.output_path, ".asdf") check_remove_filepath(output_path, log, args.force) log.info("Loading the query template ...") root = Path(__file__).parent template_loader = jinja2.FileSystemLoader(str(root)) env = jinja2.Environment(keep_trailing_newline=False) template = template_loader.load(env, name=QUERY_TEMPLATE) log.info("Loading the query args ...") with (root / QUERY_ARGS).open() as fin: query_args = yaml.load(fin, Loader=yaml.BaseLoader) client = Client( user=args.user, password=args.password, host=args.host, port=args.port, database=args.database, ) files, deps, ind_files, ind_deps = [], [], [], [] ind_to_langs, ind_to_repos, file_to_inds, dep_to_inds = {}, {}, {}, {} for lang in args.langs: log.info("Extracting %s dependencies...", lang) rows = client.execute_iter( template.render(lang=lang, table=args.table, query_args=query_args[lang]), settings={"max_block_size": MAX_BLOCK_SIZE}, ) num_rows, num_files, num_deps = 0, 0, 0 for row in rows: row = [ e.decode("utf-8", errors="ignore") if not isinstance(e, str) else e for e in row ] num_rows += 1 repo, file_path, dep = row lang_file = ":".join([lang, repo, file_path]) lang_dep = ":".join([lang, dep]) if lang_file not in file_to_inds: num_files += 1 file_to_inds[lang_file] = len(file_to_inds) files.append(file_path) ind_file = file_to_inds[lang_file] ind_to_langs[ind_file] = lang ind_to_repos[ind_file] = repo ind_files.append(ind_file) if lang_dep not in dep_to_inds: num_deps += 1 dep_to_inds[lang_dep] = len(dep_to_inds) deps.append(dep) ind_deps.append(dep_to_inds[lang_dep]) log.info( "Finished with %s, retrieved %d rows with %d distinct dependencies in %d files", lang, num_rows, num_deps, num_files, ) log.info( "Done, retrieved %d rows with %d distinct dependencies in %d files and %d repos", len(ind_files), len(deps), len(files), len(set(ind_to_repos.values())), ) log.info("Creating the sparse matrix ...") matrix = coo_matrix(([True] * len(ind_files), (ind_files, ind_deps)), dtype=bool) log.info("Creating the dependencies model ...") model = Dependencies(log_level=args.log_level).construct( matrix, files, deps, ind_to_langs, ind_to_repos ) model.save(output_path, series="deps") log.info("Saved model to %s" % output_path)
""" import time from datetime import datetime from clickhouse_driver import Client if __name__ == '__main__': c = Client(host="192.168.122.5", port=9000, database='default') c2 = Client(host="192.168.122.5", port=9000, database='default') insert_sql = "INSERT INTO flash (a, t) VALUES" # rows = [] # for i in range(100000): # rows.append((i + 1, datetime.now())) # c.execute(insert_sql, rows) # print("insert ok!") settings = {'max_block_size': 1000} rows_gen = c.execute_iter("select * from flash", settings=settings) for row in rows_gen: print(row) # 插入 共用连接 error # c.execute(insert_sql, [(100001, datetime.now())]) # 使用新的连接解决问题 c2.execute(insert_sql, [(100001, datetime.now())]) # sleep time.sleep(1)
#в качестве первого из хэдеров. trg_file_opened.write(f'##{where}\n') #То же самое делаем с #восстановленной ранее шапкой. #Это будет второй хэдер. trg_file_opened.write(header_line + '\n') print(f'\nПоиск по таблице {tab_name} базы данных') #Инструкция, собственно, поиска. #Она позволит извлечь из текущей #таблицы БД байтовые позиции начала #отвечающих поисковым условиям #строк архивированной таблицы. res = client.execute_iter(f'''SELECT line_start FROM {tab_name} WHERE {where}''') print(f'Извлечение отобранных строк таблицы {arc_file_name}') #Перемещение курсора по сжатой таблице к #началу каждой отвечающей запросу строки. #Очередная новая позиция курсора отсчитывается #не от начала файла, а от последней запомненной #позиции, что в ряде случаев приводит к #достижению колоссальной производительности. #Прописывание найденных строк в конечный файл. #Присвоение флагу значения, показывающего #наличие в конечном файле нехэдерных строк. cur_pointer = 0 for line_start in res: new_pointer = line_start[0]
total_query_time = 0.0 total_parse_time = 0.0 rows_returned = 0 do_break = False # Iterate set number of times, for repetitions sake i = 0 while (i < num_repetitions): try: query_time = 0.0 parse_time = 0.0 settings = {'max_block_size': 5000} # Do the query and parsing start_time = time.perf_counter() result = client.execute_iter(clkhs_select_query_prefix + clkhs_get_fuzzy_query(query_num) + str(num_rows) + ';', settings) clkhs_UDR_list = [] for x in result: clkhs_UDR_list.append(x) mid_time = time.perf_counter() clkhs_UDR_df = pd.DataFrame(clkhs_UDR_list) end_time = time.perf_counter() # Get times query_time = query_time + mid_time - start_time parse_time = parse_time + end_time - mid_time # Aggregate total_query_time = total_query_time + query_time total_parse_time = total_parse_time + parse_time rows_returned = int(clkhs_UDR_df.size / num_cols)
#Прописываем восстановленную ранее #шапку в качестве второго хэдера. trg_file_opened.write(header_line + '\n') print(f'\n\tПоиск по таблице {tab_name} базы данных') #Инструкция поиска по столбцу #таблицы БД, созданной быть #источником характеристик #аннотируемого столбца. #Позволит извлечь из этой #таблицы байтовые позиции #начала содержащих запрашиваемые #ячейки строк архивированной таблицы. res = client.execute_iter(f'''SELECT line_start FROM {tab_name} WHERE {col_name} IN ({", ".join(ann_set)})''' ) print(f'\tИзвлечение отобранных строк таблицы {arc_file_name}') #Перемещение курсора по сжатой таблице к #началу каждой отвечающей запросу строки. #Очередная новая позиция курсора отсчитывается #не от начала файла, а от последней запомненной #позиции, что в ряде случаев приводит к #достижению колоссальной производительности. #Прописывание найденных строк в конечный файл. #Присвоение флагу значения, показывающего #наличие в конечном файле нехэдерных строк. cur_pointer = 0 for line_start in res: