В Clickhouse реализована возможность использования внутренних и внешних словарей, которые могут быть альтернативой JOIN (которые, к сожалению, не всегда здорово работают). Словари хранят информацию в памяти и к ним можно обратиться командой dictGet. Рассмотрим как создать словарь в Clickhouse и как его можно использовать в запросах.
Будем изучать функционал на примере данных из API Untappd. Untappd — социальная сеть любителей крафтового пива. Мы сфокусируемся на чекинах российких крафтовых пивоварен, начнем собирать информацию о них, чтобы в следующих постах проанализировать данные и сделать некоторые выводы. В рамках этого поста разберем получение мета-информации о российских пивоварнях на Untappd, а полученные данные сохраним в словаре Clickhouse.
Собираем данные с Untappd
Для обращений к API нужны client_id и client_secret_key — их можно получить, создав приложение. Для этого переходим в раздел создания приложения в документации и указываем некоторые данные:
После отправления заявки нужно будет подождать некоторое время: от 1 до 3 недель.
import requests
import pandas as pd
import time
Отправлять запросы к API будем через requests, а в pandas посмотрим на результаты и выгрузим в csv, чтобы отправить в словарь Clickhouse. У Untappd строгие ограничения на количество запросов: всего в час можно отправить 100 запросов, поэтому будем библиотекой time ставить скрипт в ожидание на 38 секунд, чтобы число запросов в час не превосходило 100.
client_id = 'ваш_client_id'
client_secret = 'ваш_client_secret'
all_brewery_of_russia = []
Мы хотим собрать всю тысячу российских пивоварен. Один запрос к методу Brewery Search позволяет получить до 50 пивоварен. При поиске вручную на сайте Untappd по слову «Russia» сайт выдаст 3369 пивоварен:
Проверим это: пролистаем страницу до самого низа и откроем код страницы.
Каждая полученная пивоварня в поиске находится в классе beer-item. Значит, можем в поиске посчитать количество упоминаний beer-item:
И выясняем, что на самом деле их здесь ровно 1000, а не 3369. По запросу Russia в выборку попадают и американские пивоварни, а некоторые были удалены. Значит, придётся отправить 20 запросов, будем получать по 50 пивоварен за раз:
for offset in range(0, 1000, 50):
try:
print('offset = ', offset)
print('осталось:', 1000 - offset, '\n')
response = requests.get(f'https://api.untappd.com/v4/search/brewery?client_id={client_id}&client_secret={client_secret}',
params={
'q':'Russia',
'offset':offset,
'limit':50
})
item = response.json()
print(item, '\n')
all_brewery_of_russia.append(item)
time.sleep(37)
except Exception:
print(Exception)
continue
В параметрах метод Brewery Search принимает q — строку, по которой будем осуществлять поиск на сервисе. Укажем в ней «Russia», чтобы получить все пивоварни, связанные с Россией. Другой параметр — offset — отвечает за смещение. Получив первые 50 пивоварен мы смещаемся на 50 строк в поиске, чтобы получить следующие 50 пивоварен. limit отвечает за количество получаемых пивоварен и не может быть больше 50.
Преобразовываем ответ в формат json и добавляем полученные данные в список all_brewery_of_russia. Объект item будет содержать такие данные:
Но в полученных данных могли затесаться и пивоварни других стран. Отфильтруем их: пройдём итератором по всему списку all_brewery_of_russia и добавим в итоговый только те пивоварни, у которых параметр country_name принимает значение Russia.
brew_list = []
for element in all_brewery_of_russia:
brew = element['response']['brewery']
for i in range(brew['count']):
if brew['items'][i]['brewery']['country_name'] == 'Russia':
brew_list.append(brew['items'][i])
Посмотрим на первый элемент списка brew_list:
print(brew_list[0])
Соберём из списка DataFrame с колонками brewery_id, beer_count, brewery_name, brewery_slug, brewery_page_url, brewery_city, lat и lng. Получим в отдельные списки данные из brewery_list:
df = pd.DataFrame()
brewery_id_list = []
beer_count_list = []
brewery_name_list = []
brewery_slug_list = []
brewery_page_url_list = []
brewery_location_city = []
brewery_location_lat = []
brewery_location_lng = []
for brewery in brew_list:
brewery_id_list.append(brewery['brewery']['brewery_id'])
beer_count_list.append(brewery['brewery']['beer_count'])
brewery_name_list.append(brewery['brewery']['brewery_name'])
brewery_slug_list.append(brewery['brewery']['brewery_slug'])
brewery_page_url_list.append(brewery['brewery']['brewery_page_url'])
brewery_location_city.append(brewery['brewery']['location']['brewery_city'])
brewery_location_lat.append(brewery['brewery']['location']['lat'])
brewery_location_lng.append(brewery['brewery']['location']['lng'])
И отправим их в DataFrame:
df['brewery_id'] = brewery_id_list
df['beer_count'] = beer_count_list
df['brewery_name'] = brewery_name_list
df['brewery_slug'] = brewery_slug_list
df['brewery_page_url'] = brewery_page_url_list
df['brewery_city'] = brewery_location_city
df['brewery_lat'] = brewery_location_lat
df['brewery_lng'] = brewery_location_lng
Посмотрим, как выглядит наша таблица:
df.head()
Отсортируем значения по brewery_id и выгрузим таблицу в формате csv без столбца с индексами и заголовков колонок:
df = df.sort_values(by='brewery_id')
df.to_csv('brewery_data.csv', index=False, header=False)
Создаём словарь Clickhouse
Словари для Clickhouse можно создавать по-разному. Мы попробуем задать его структуру в xml-файле, настроить конфигурационные файлы сервера и обращаться к нему через клиент. Наш xml будет иметь следующую структуру:
Со всеми способами создания словарей можно ознакомиться в документации
<yandex>
<dictionary>
<name>breweries</name>
<source>
<file>
<path>/home/ubuntu/brewery_data.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<flat />
</layout>
<structure>
<id>
<name>brewery_id</name>
</id>
<attribute>
<name>beer_count</name>
<type>UInt64</type>
<null_value>Null</null_value>
</attribute>
<attribute>
<name>brewery_name</name>
<type>String</type>
<null_value>Null</null_value>
</attribute>
<attribute>
<name>brewery_slug</name>
<type>String</type>
<null_value>Null</null_value>
</attribute>
<attribute>
<name>brewery_page_url</name>
<type>String</type>
<null_value>Null</null_value>
</attribute>
<attribute>
<name>brewery_city</name>
<type>String</type>
<null_value>Null</null_value>
</attribute>
<attribute>
<name>lat</name>
<type>String</type>
<null_value>Null</null_value>
</attribute>
<attribute>
<name>lng</name>
<type>String</type>
<null_value>Null</null_value>
</attribute>
</structure>
<lifetime>300</lifetime>
</dictionary>
</yandex>
Под идёт имя словаря. В указываем свойства колонок. Под тегом идёт ключевое поле, а под тегом укажем путь и формат файла. Скоро мы положим его в папку /home/ubuntu, поэтому так и укажем.
Загрузим нашу csv-таблицу и xml-файл на сервер, это можно сделать, например, по ftp через FileZilla. В одном из материалов мы учились ставить Clickhouse на бесплатную машину от Amazon, в этот раз будем работать там же. В FileZilla заходим в настройки SFTP и добавляем файл с ключом:
И подключаемся к серверу по адресу, который указан в консоли EC2 машины на AWS. Укажем протокол SFTP, свой Host и в качестве User — Ubuntu:
В случае перезагрузки машины через консоль Public DNS мог измениться
После подсоединения мы попадём в папку /home/ubuntu сервера. Положим файлы туда же. Теперь подключимся по SSH через Termius. Чтобы Clickhouse увидел файл со структурой словаря, его нужно положить в папку /etc/clickhouse-server:
О том, как подключаться в серверу на AWS через SSH-клиент мы рассказывали в материале «Устанавливаем Clickhouse на AWS»
sudo mv breweries_dictionary.xml /etc/clickhouse server/
Переходим в конфигурационный файл:
cd /etc/clickhouse-server
sudo nano config.xml
Нам нужен тег — он указывает путь к файлу, который описывает структуру словарей. Укажем путь к нашему xml:
<dictionaries_config>/etc/clickhouse-server/breweries_dictionary.xml</dictionaries_config>
Сохраняем файл и запускаем клиент Clickhouse:
clickhouse client
Проверим, что наш словарь действительно загрузился:
SELECT * FROM system.dictionaries\G
В случае успеха получим подобное:
Напишем запрос к функции dictGet, чтобы получить название пивоварни под ID 999. Указываем первым аргументом наименование словаря, затем поле, значение которого хотим получить и ID.
SELECT dictGet('breweries', 'brewery_name', toUInt64(999))
Если сделаем всё правильно, то выясним, что под ID 999 находится Балтика:
Аналогичным образом удобно использовать функцию, когда в таблице с фактами хранится только ID измерения для получения понятного наименования.
