Создание визуализации ClickHouse с помощью Altinity и Cube

Создание визуализаций на основе данных из ClickHouse, которая в свою очередь использует данные Altinity.Cloud и Cube Cloud, в качестве метрик.

Я обожаю считать звезды. Особенно звезды на GitHub! Мне всегда было интересно отслеживать рост популярности полезных репозиториев GitHub. Именно поэтому для создания информативных дашбордов ClickHouse я решил использовать набор данных о событиях GitHub.

В этом руководстве я подробно расскажу Вам о том, как создать пользовательскую визуализацию на основе данных, полученных из ClickHouse. В качестве уровня API метрик я буду использовать управляемый экземпляр ClickHouse из Altinity Cloud и Cube Cloud.

Вот как в итоге будет выглядеть приложение, содержащее дашборд.

Как создать визуализацию на основе данных ClickHouse

Я хочу использовать набор данных о событиях на GitHub, содержащий данные обо всех событиях, произошедших на GitHub начиная с 2011 года. Данный набор содержит более 3 млрд записей. Круто, не так ли?

Для обработки всех этих данных я хочу использовать ClickHouse - мощную БД для создания аналитических приложений. ClickHouse также достаточно часто используется для хранения метрик.

Несмотря на то что ClickHouse работает молниеносно, мне все равно нужен API для получения данных и их отображения в дашбордах. в качестве слоя метрик для создания аналитических запросов я буду использовать Cube Cloud- это аналитический API для создания приложений для работы с данными.

Что такое ClickHouse?

ClickHouse – мощная колоночно-ориентированная система управления базами данных с открытым исходным кодом, позволяющая генерировать аналитические отчеты в режиме реального времени с помощью SQL запросов.

В данной СУБД реализован колоночный механизм хранения данных, что обеспечивает высокую производительность аналитических запросов. Кроме того, ClickHouse обеспечивает высокую скорость обработки запросов и эффективное хранение данных.

Существует несколько способов запустить ClickHouse

Локально или bare-metal.
Провайдеры облачных услуг, такие как AWS, Google Cloud Platform и т.д.
Y.Cloud.
Altinity.

Создание кластера ClickHouse

Процесс регистрации более чем прост. Сначала перейдите на страницу тест-драйва Altinity. Затем заполните поля и попросите команду Altinity создать для Вас кластер ClickHouse.

Через несколько минут Вы получите кластер с Вашими данными.

Войдите в систему, используя свои учетные данные - Вы попадете на страницу кластеров.

Следующий шаг - создание кластера. Нажмите на кнопку Launch Cluster, чтобы открыть Мастера запуска кластера (Cluster Launch Wizard).

Выберите желаемую конфигурацию и нажмите кнопку Next.

Не забудьте также настроить параметры соединения.

Последний шаг - запуск кластера. Перед тем как перейти к запуску кластера, Вы получите информацию о предполагаемой стоимости работы с кластером.

После того как Вы нажмете кнопку Launch, Вам придется немного подождать.

Когда кластер будет запущен, Вы увидите следующее:

Супер! Теперь у нас есть работающий кластер. Загрузим в него данные.

Добавление данных из набора данных о событиях на GitHub в свежесозданный кластер ClickHouse

Существует несколько способов импортировать используемый нами набор данных. Я предлагаю загрузить данные непосредственно в ClickHouse.

Для запуска процесса импорта данных отредактируйте свой профиль пользователя. Затем задайте параметру max_http_get_redirects большое значение. Я решил на всякий случай начать с 1000.

Для начала работы с Clickhouse установите clickhouse-client и создайте файл ./clickhouse-client.xml, используя параметры конфигурации, предоставленные Altinity.

Для работы с clickhouse-client Вам понадобятся следующие параметры:

Хост: <team>.<company>.altiinty.cloud
Порт: 9440
Пользователь: admin — или используйте свою конфигурацию.
Пароль: *** — пароль, используемый текущим пользователем.

clickhouse-client.xml будет иметь следующий вид:

cat clickhouse-client.xml

<config>
    <host>your-team.your-company.altinity.cloud</host>
    <user>admin</user>
    <password>xxxxxxxxxxxxx</password>
    <secure>True</secure>
    <port>9440</port>
</config>

В том же каталоге, где Вы сохранили clickhouse-client.xml, выполните следующую команду:

clickhouse-client

После подключения создайте внешнюю таблицу, которая будет считывать данные с URL-адреса.

CREATE TABLE github_events_url

(
    file_time DateTime,
    event_type Enum('CommitCommentEvent' = 1, 'CreateEvent' = 2, 'DeleteEvent' = 3, 'ForkEvent' = 4,
                    'GollumEvent' = 5, 'IssueCommentEvent' = 6, 'IssuesEvent' = 7, 'MemberEvent' = 8,
                    'PublicEvent' = 9, 'PullRequestEvent' = 10, 'PullRequestReviewCommentEvent' = 11,
                    'PushEvent' = 12, 'ReleaseEvent' = 13, 'SponsorshipEvent' = 14, 'WatchEvent' = 15,
                    'GistEvent' = 16, 'FollowEvent' = 17, 'DownloadEvent' = 18, 'PullRequestReviewEvent' = 19,
                    'ForkApplyEvent' = 20, 'Event' = 21, 'TeamAddEvent' = 22),
    actor_login LowCardinality(String),
    repo_name LowCardinality(String),
    created_at DateTime,
    updated_at DateTime,
    action Enum('none' = 0, 'created' = 1, 'added' = 2, 'edited' = 3, 'deleted' = 4, 'opened' = 5, 'closed' = 6, 'reopened' = 7, 'assigned' = 8, 'unassigned' = 9,
                'labeled' = 10, 'unlabeled' = 11, 'review_requested' = 12, 'review_request_removed' = 13, 'synchronize' = 14, 'started' = 15, 'published' = 16, 'update' = 17, 'create' = 18, 'fork' = 19, 'merged' = 20),
    comment_id UInt64,
    body String,
    path String,
    position Int32,
    line Int32,
    ref LowCardinality(String),
    ref_type Enum('none' = 0, 'branch' = 1, 'tag' = 2, 'repository' = 3, 'unknown' = 4),
    creator_user_login LowCardinality(String),
    number UInt32,
    title String,
    labels Array(LowCardinality(String)),
    state Enum('none' = 0, 'open' = 1, 'closed' = 2),
    locked UInt8,
    assignee LowCardinality(String),
    assignees Array(LowCardinality(String)),
    comments UInt32,
    author_association Enum('NONE' = 0, 'CONTRIBUTOR' = 1, 'OWNER' = 2, 'COLLABORATOR' = 3, 'MEMBER' = 4, 'MANNEQUIN' = 5),
    closed_at DateTime,
    merged_at DateTime,
    merge_commit_sha String,
    requested_reviewers Array(LowCardinality(String)),
    requested_teams Array(LowCardinality(String)),
    head_ref LowCardinality(String),
    head_sha String,
    base_ref LowCardinality(String),
    base_sha String,
    merged UInt8,
    mergeable UInt8,
    rebaseable UInt8,
    mergeable_state Enum('unknown' = 0, 'dirty' = 1, 'clean' = 2, 'unstable' = 3, 'draft' = 4),
    merged_by LowCardinality(String),
    review_comments UInt32,
    maintainer_can_modify UInt8,
    commits UInt32,
    additions UInt32,
    deletions UInt32,
    changed_files UInt32,
    diff_hunk String,
    original_position UInt32,
    commit_id String,
    original_commit_id String,
    push_size UInt32,
    push_distinct_size UInt32,
    member_login LowCardinality(String),
    release_tag_name String,
    release_name String,
    review_state Enum('none' = 0, 'approved' = 1, 'changes_requested' = 2, 'commented' = 3, 'dismissed' = 4, 'pending' = 5)
) ENGINE = URL('https://datasets.clickhouse.tech/github_events_v2.native.xz', Native);

Затем создайте целевую таблицу и вставьте в нее данные.

CREATE TABLE github_events ENGINE = MergeTree ORDER BY (event_type, repo_name, created_at) AS SELECT * FROM github_events_url;

Это займет некоторое время. Оно и понятно, ведь нужно импортировать около 200 ГБ… Отвлекитесь и выпейте чашечку кофе.

Для того, чтобы убедиться в том, что Ваши данные полностью импортированы, запустите простой запрос SELECT. Вы должны увидеть около 4 миллиардов строк.

SELECT count()FROM github_events
┌────count()──┐
│ 4014609315  │
└─────────────┘

Отлично, Вы успешно импортировали все данные. Общий объем должен составлять около 225,84 ГБ.

Если Вы хотите потренироваться с выбранным нами набором данных о событиях на Githab и не планируете импортировать свой собственный набор данных, Вы можете использовать демо кластер Altinity. clickhouse-client.xml в этом случае должен выглядеть следующим образом:

<config>
    <host>github.demo.altinity.cloud</host>
    <user>demo</user>
    <password>demo</password>
    <secure>True</secure>
    <port>9440</port>
</config>

Начина я с этого момента мы будем работать исключительно с демо-кластером Altinity.

Создание аналитических запросов ClickHouse в Altinity

Пришло время составить аналитические запросы. Во-первых, я хочу получить 10 самых популярных репозиториев.

SELECT
    repo_name,

    count() AS stars
FROM github_events
WHERE event_type = 'WatchEvent'
GROUP BY repo_name
ORDER BY stars DESC
LIMIT 10

 

Query id: 94dcd3ce-dde2-4a56-a176-167cdfda68ca
 
┌─repo_name────────────────────────────────┬──stars─┐
│ 996icu/996.ICU                           │ 364787  │
│ FreeCodeCamp/FreeCodeCamp                │ 225490  │
│ vuejs/vue                                │ 216744  │
│ facebook/react                           │ 209411  │
│ kamranahmedse/developer-roadmap          │ 188205  │
│ sindresorhus/awesome                     │ 187670  │
│ tensorflow/tensorflow                    │ 185670  │
│ jwasham/coding-interview-university      │ 177806  │
│ getify/You-Dont-Know-JS                  │ 161089  │
│ freeCodeCamp/freeCodeCamp                │ 161046  │
└──────────────────────────────────────────┴─────────┘

 
10 rows in set. Elapsed: 1.800 sec. Processed 276.33 million rows, 2.23 GB (153.52 million rows/s., 1.24 GB/s.)

Написание сложных запросов, подобных этому, - задача непростая, особенно, если Вы такой же разработчик, как и я … В идеале я бы хотел, чтобы используемый мной инструмент выступал в качестве уровня метрик. В этом случае я смогу генерировать графики и диаграммы без необходимости писать SQL самостоятельно. Я бы также не отказался от настройки прав доступа и безопасности на основе ролей.

Cube – к Вашим услугам.

Создание приложения Cube App на Cube Cloud

С Cube Вы получаете централизованный уровень метрик с возможностью генерации SQL запросов, автомасштабированием и многими другими полезностями. Я разработчик, я бы очень хотел, чтобы все мои SQL-файлы генерировались за меня!

Сейчас я покажу Вам, как настроить Cube Cloud. После прохождения регистрации создайте развертывание. Выберите интеграцию базы данных ClickHouse.

Добавьте значения из базы данных ClickHouse. База данных в Altinity.Cloud будет называться default.

Затем сгенерируем схему из таблицы github_events .

Генерировать схему не обязательно, но все же я рекомендую это сделать, поскольку в будущем это очень сильно упростит Вам жизнь. После развертывания Вы увидите обзор всех ресурсов.

Создание Cube Cloud в качестве слоя метрик

Посмотрим на схему, сгенерированную автоматически. Выберите раздел Schema м нажмите на файл GithubEvents.js:

Мне нравится начинать с подобных БД, на их основе можно достаточно легко создавать свой собственный слой метрик.

Для начала я покажу Вам, как добавить новое измерение. Назовем его eventType.

eventType: {
  sql: `event_type`,
  type: `string`
},

После добавления измерения Вы увидите следующее:

После сохранения этого изменения откройте Playground и выполните тот же запрос, что и в Altinity.

Супер! Но проделанная работа сложнее, чем хотелось бы. Поэтому выполним следующее:

segments: {
  watchEvents: {
    sql: `${CUBE}.event_type = 'WatchEvent'`,
  },
},

Вставьте данные чуть ниже joins:

Это даст Вам предварительно определенный фильтр, так что Вам не придется возиться с определением фильтров в запросе Cube:

Используя эту логику, Вы сможете смешивать и сопоставлять любые желаемые показатели и измерения.

Cube также предоставляет Вам весь код, необходимый для внедрения в Ваше собственное внешнее приложение. Для интеграции библиотек Вы можете использовать React, Vue, Angular, Vanilla JavaScript, а также BI. Мы называем эту новую функцию SQL API.

Саму диаграмму или график можно выбрать из Chart.js, Bizcharts, Recharts или D3. Выбор –за Вами.

Чтобы использовать автогенерируемый код графика, перейдите на вкладку «Code», скопируйте и вставьте код, все готово. Чудеса!

Рис .22

Для создания дашбордов используйте функциЮ. Встроенную в Cube. Я взял на себя смелость и создал одну из них специально для Вас. Результат выглядит так:

Теперь Вы знаете, как самостоятельно генерировать метрики. Перейдем к безопасности и доступу на основе ролей.

Добавление Multi-Tenancy в Cube Cloud

Cube поддерживает архитектуру multi-tenancy . Вы можете включить ее как на уровне базы данных, так и на уровне схемы данных.

Чтобы включить доступ на основе ролей на уровне строк, я использую объект контекста. У него есть свойство securityContext, где Вы можете указать все необходимые данные для идентификации того или иного пользователя

По умолчанию securityContext определяется Cube.js API -токеном.

Начните с копирования команды CURL с помощью токена Authorization:

Результат:

curl \
  -H "Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE2Mzg3OTgxMTJ9.LQnd3UacUQAZpLUjxRV_tWnPZheWY4MhxGzrcsSlrbg" \
  -G \
  --data-urlencode 'query={"measures":["GithubEvents.count"]}' \
  https://inland-caratunk.aws-eu-central-1.cubecloudapp.dev/cubejs-api/v1/load

Теперь скопируйте только токен и вставьте его в валидатор веб-токенов JWT.io.

С правой стороны добавьте полезную нагрузку для "role": "stars", а также секрет приложения Cube. Так Вы сможете сгенерировать правильный токен авторизации.

Секрет приложения Cube можно найти в разделе env vars в настройках развертывания.

Теперь у Вас есть токен, содержащий полезную нагрузку с «role»: «stars». Далее давайте воспользуемся securityContext и queryRewrite в cube.js для добавления роли stars. Я хочу, чтобы эта роль могла запрашивать только события типа WatchEvent.

В файл cube.js добавьте следующий код:

module.exports = {
  queryRewrite: (query, { securityContext }) => {
    if (!securityContext.role) {
      throw new Error('No role found in Security Context!');
    }
 
    if (securityContext.role == 'stars') {
      query.filters.push({
        member: 'GithubEvents.eventType',
        operator: 'equals',
        values: ['WatchEvent'],
      });
    }
 
    return query;
  },
};

На Cube Cloud это будет выглядеть следующим образом:

Сохраните, зафиксируйте и отправьте изменения. Запустите команду CURL еще раз, но уже с новым токеном:

curl \
  -H "Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoic3RhcnMiLCJpYXQiOjE2Mzg3OTEyNjN9.bk3WPLNdP_gjC-K1qk6xRLM-56ABzqVG20etZvb5Yvc" \
  -G \
  --data-urlencode 'query={"measures":["GithubEvents.count"]}' \
  https://inland-caratunk.aws-eu-central-1.cubecloudapp.dev/cubejs-api/v1/load

Вывод будет отфильтрован таким образом, чтобы отобразились только события типа WatchEvent.

{
  "query": {
    "measures": [
      "GithubEvents.count"
    ],
    "timezone": "UTC",
    "order": [],
    "filters": [
      {
        "member": "GithubEvents.eventType",
        "operator": "equals",
        "values": [
          "WatchEvent"
        ]
      }
    ],
    "dimensions": [],
    "timeDimensions": []
  },
  "data": [
    {
      "GithubEvents.count": "276214411"
    }
  ],
  "lastRefreshTime": "2021-12-06T14:03:10.885Z",
  "annotation": {
    "measures": {
      "GithubEvents.count": {
        "title": "Github Events Count",
        "shortTitle": "Count",
        "type": "number",
        "drillMembers": [
          "GithubEvents.repoName",
          "GithubEvents.title",
          "GithubEvents.commitId",
          "GithubEvents.originalCommitId",
          "GithubEvents.releaseTagName",
          "GithubEvents.releaseName",
          "GithubEvents.createdAt",
          "GithubEvents.updatedAt"
        ],
        "drillMembersGrouped": {
          "measures": [],
          "dimensions": [
            "GithubEvents.repoName",
            "GithubEvents.title",
            "GithubEvents.commitId",
            "GithubEvents.originalCommitId",
            "GithubEvents.releaseTagName",
            "GithubEvents.releaseName",
            "GithubEvents.createdAt",
            "GithubEvents.updatedAt"
          ]
        }
      }
    },
    "dimensions": {},
    "segments": {},
    "timeDimensions": {}
  },
  "dataSource": "default",
  "dbType": "clickhouse",
  "extDbType": "cubestore",
  "external": false,
  "slowQuery": false
}

Используя securityContext, Вы можете обеспечить права доступа и должную безопасность Вашего приложения, включая безопасность на уровне строк для БД ClickHouse. Чтобы узнать об этом подробнее, ознакомьтесь с нашим фирменным рецептом использования доступа на основе ролей.

Заключение

Цель данной статьи состоит в объяснении одного из самых простых способов использования ClickHouse. Задействование Altinity.Cloud для размещения и управления кластером ClickHouse, Вы можете сосредоточиться на главном и доверить управление инфраструктурой профессионалам.

В Cube Cloud Вы получаете слой метрик, который интегрируется со всеми основными библиотеками визуализации данных, в том числе с SQL-совместимыми инструментами построения графиков, такими как Apache Superset. Кроме того, в комплект входит поддержка многопользовательской сети. Среди различных опций многопользовательского доступа можно включить безопасность на уровне строк, доступ на основе ролей, использование нескольких экземпляров базы данных, нескольких схем и многое другое.

← Предыдущая статья

Интеграция ClickHouse и совместимого с S3 объектного хранилища

Следующая статья →

Углубляемся в тонкости Apache Parquet с помощью ClickHouse - часть 1