Дашборд ClickHouse: учебное пособие
В этой статье мы рассмотрим, как создать дашборд на основе ClickHouse, быстрой аналитической базы данных с открытым исходным кодом. Мы построим визуализацию данных фондового рынка с помощью свечных графиков, изучим влияние WallStreetBets и посмотрим, насколько быстро работает ClickHouse.
Что такое ClickHouse?
ClickHouse – быстрая open-source аналитическая база данных. В отличие от Postgres или MySQL, она способна формировать аналитические отчеты с использованием SQL-запросов в режиме реального времени.
ClickHouse is принята и используется специалистами Bloomberg, Cloudflare, eBay, Spotify, Uber и даже ядерными физиками из CERN (Европейской организации по ядерным исследованиям).
Считается, что база данных работает экстремально быстро благодаря движку столбчатого формата хранения данных. Звучит правдоподобно, поскольку обычно аналитические операции, такие как AVG, DISTINCT или MIN, быстрее применять к плотно организованным значениям (столбцам), нежели чем к разрозненным данным.
В данной статье мы изучим, как:
- Начать работу с ClickHouse,
- Построить аналитический API при помощи Cube, и
- Запросить этот API из внешнего дашборда, чтобы
- Вы легко могли визуализировать результаты запроса
Вот что мы получим в итоге:
Как запустить ClickHouse
Удивительно, но факт: существует несколько способов начать работу с ClickHouse:
Установите и запустите ClickHouse на Вашем macOS или Linux. Он хорош для целей тестирования, но немного неоптимален, если Вам нужно получить достоверные сведения о производительности ClickHouse.
Установите и запустите ClickHouse на AWS, GCP или другой облачной платформе. Это идеальный вариант для тестирования и производственного использования, особенно если у Вас (или Вашей компании) там уже есть активированные аккаунты. Настройка ClickHouse в AWS EC2; в AWS EKS.
Запустите ClickHouse в Yandex Cloud, еще одной облачной платформе. Это еще оидн отличный вариант для тестирования и производственного использования. Во-первых, ClickHouse был изначально разработан крупной технологической компанией Yandexв июне 2016 года. Во-вторых, установка ClickHouse в Yandex Cloud займет меньше времени и потребует минимальных усилий с Вашей стороны.
И даже это еще далеко не все! Вы также можете...
Использовать ClickHouse Playground, установку, доступную только для чтения, имеющую веб-консоль и API доступ. Хотя она не позволяет выполнять INSERT или запросы на определение данных, такие как CREATE TABLE, установка является отличным инструментом для начала работы с ClickHouse"с нуля".
Уже установили ClickHouse? Здорово! Тогда Вы можете использовать свои учетные данные для того, чтобы продолжить работу с этим учебным пособием. Мы же воспользуемся следующими учетными данными от ClickHouse Playground:
Как запустить аналитический API
Давайте подробнее разберемся с тем, как необходимо работать с ClickHouse.
Как отправить запросы в ClickHouse? У БД есть два интерфейса, HTTP и Native TCP. Однако, вряд ли Вам захочется с работать с низкоуровневыми сырыми данными HTTP или двоичными данными TCP, не так ли?
Доступны ли какие-либо библиотеки? У ClickHouse несколько драйверов (например, for C++) и множество библиотек для различных языков. Вы можете использовать их для того, чтобы отправлять SQL запросы и получать необходимые Вам результаты.
Можно ли остаться на высоком уровне и даже не утруждать себя написанием и сопровождением SQL-запросов? Конечно. Как раз здесь-то и пригодится Cube - open-source аналитическая API платформа, включающая в себя ClickHouse. Вы можете использовать Cube.js для высокоуровневых доменных запросов (например, "Я хочу узнать среднюю зарплату для каждой должности" или "Покажите мне количество покупок для каждой категории товаров"), их эффективного выполнения в Вашей базе данных (при этом Вы получаете предсказуемую производительность с низкой задержкой), а также для получения результата, который можно легко визуализировать, например, построить график на дашборде. Кроме того, Вы получаете Cube.js Developer Playground - визуальный инструмент, позволяющий легко строить запросы и выводить их в виде графиков. Давайте попробуем.
Для начала создадим новый проект Cube. В данном случае предполагается, что у Вас уже есть установленный Node.js. Обратите внимание на то, что Вы также можете использовать Docker для того, чтобы запустить Cube.js:
npx cubejs-cli create clickhouse-dashboard -d clickhouse
Теперь у Вас есть новый проект Cube в папке clickhouse-dashboard, содержащей несколько файлов. Зайдем в эту папку.
Теперь добавим учетные данные ClickHouse в .env file. Из этого файла Cube возьмет необходимые параметры конфигурации. Давайте занесем туда учетные данные из ClickHouse Playground. Убедитесь, что Ваш файл .env имеет следующий вид или укажите свои собственные учетные данные:# Cube environment variables: https://cube.dev/docs/reference/environment-variables
CUBEJS_DB_TYPE=clickhouse CUBEJS_DB_HOST=play-api.clickhouse.tech CUBEJS_DB_PORT=8443 CUBEJS_DB_SSL=true CUBEJS_DB_USER=playground CUBEJS_DB_PASS=clickhouse CUBEJS_DB_NAME=datasets CUBEJS_DB_CLICKHOUSE_READONLY=true CUBEJS_DEV_MODE=true CUBEJS_WEB_SOCKETS=true CUBEJS_API_SECRET=SECRET
Вот значение этих команд:
-
Очевидно, что
CUBEJS_DB_TYPEговорит о том, что мы подключаемся к ClickHouse; -
CUBEJS_DB_HOSTиCUBEJS_DB_PORTобозначают то, на чем запущен наш ClickHouse, аCUBEJS_DB_SSLвключает защищенные соединения по протоколу TLS; -
CUBEJS_DB_USERиCUBEJS_DB_PASSиспользуются для аутентификации пользователя ClickHouse; -
CUBEJS_DB_NAMEнаименование БД (или "схемы"), где собраны все таблицы с данными; -
CUBEJS_DB_CLICKHOUSE_READONLYопция, которую нам необходимо указать специально, поскольку мы подключаемся к ClickHouse Playground, так как она разрешает доступ только для чтения. Обычно для ClickHouse такая опция не требуется. Остальные опции используется для кофигурации Cube и не имеют никакого отношения к ClickHouse.
Теперь запустим Cube:
npm run dev
Бинго! Вот что у Вас должно получиться:
Как работать с данными
Обратимся к localhost:4000 и рассмотрим Cube Developer Playground. Перед Вами откроется множество возможностей, но у нас есть четкий алгоритм действий.
Для начала сгенерируем схему данных. Для этого перейдите на вкладку "Схема", выделите все необходимые таблицы и нажмите кнопку "Сгенерировать схему".
Схема данных - высокоуровневое описание данных, специфичное для конкретной области. Она позволяет отказаться от написания SQL-запросов и положиться на механизм генерации запросов Cube. Вы можете увидеть, как выглядят файлы схемы данных, если перейдете к файлам HitsV1.js или VisitsV1.js в боковой панели
Теперь сформируем запрос. Для этого перейдите на вкладку "Build", нажмите кнопку "+ Measure" и выберите меру. Например, выберите Hits V1 Count. В нашем наборе данных речь идет о веб-трафике: посещениях веб-страниц и визитах пользователей. Как видите, автоматически было выбрано временное измерение "Hits V1 Eventtime", и на графике ниже отображается количество посещений страниц за каждый день с 2014-03-16 по 2014-03-23. Какой старый набор данных! Хотите увидеть больше точек данных? Нажмите кнопку "День" и выберите вместо нее "Час". Теперь стало еще интереснее!
Конечно, не стесняйтесь экспериментировать и пробовать свои собственные запросы, меры, измерения, временные измерения и фильтры.
В-третьих, проверим запрос. Обратите внимание, что над графиком расположено множество элементов управления и опций. Вы можете переключаться между различными представлениями и библиотеками диаграмм, просматривать запрос Cube в формате JSON или просматривать, какой SQL был сгенерирован для этого запроса. Вы же не хотите писать такой SQL с нуля, не правда ли?
Как создать дашборд
У Cube Developer Playground есть еще кое-что очень интересное для изучения.
Перейдем на вкладку "Dashboard App", где можно сгенерировать код для фронтенд-приложения с дашбордом (сюрприз-сюрприз!). Здесь есть множество шаблонов для различных фреймворков (в том числе React и Angular) и библиотек диаграмм, но Вы всегда можете выбрать опцию "создать свой собственный".
Выберем "React", "React Antd Dynamic", "D3" и нажмем кнопку "OK". Уже через несколько секунд в папке dashboard-app будет создано новое front-end приложение. Для его запуска нажмите кнопку "Start dashboard app" или сделайте то же самое, перейдя в папку dashboard-app и выполнив следующую команду:
npm run start
Хотите верьте, хотите нет, но это приложение позволит Вам сделать то, что Вы уже пробовали. На вкладке "Explore" можно создать запрос, выбрать график, а затем нажать кнопку "Add to dashboard". На вкладке "Дашборд" Вы увидите результат.
Рис.10
Как нарисовать оставшуюся часть совы?
Честно говоря, этот стандартный дашборд довольно легко превратить в визуализацию данных о фондовом рынке всего за несколько быстрых шагов.
Для начала подключимся другому источнику данных. Это по-прежнему будет ClickHouse: для нашего удобства я создал специальный экземпляр ClickHouse в Google Cloud. На нем хранится свежая версия этого набора данных фондового рынка, обновленная 17 февраля 2021 года.
Набор данных содержит почти 3 Гбайт и чуть менее 9000 тикеров с ежедневными объемами и ценами: низкими, высокими, открытыми и закрытыми значениями цен. Таким образом, всего 28,2 млн. строк, что не очень много, но вполне приличный объем данных.
Для того, чтобы использовать данный набор данных, обновите Ваш .env файл следующим содержимым:
# Cube environment variables: https://cube.dev/docs/reference/environment-variables CUBEJS_DB_TYPE=clickhouse CUBEJS_DB_HOST=demo-db-clickhouse.cube.dev CUBEJS_DB_PORT=8123 CUBEJS_DB_USER=default CUBEJS_DB_PASS= CUBEJS_DB_NAME=default CUBEJS_DB_CLICKHOUSE_READONLY=true CUBEJS_DEV_MODE=true CUBEJS_WEB_SOCKETS=true CUBEJS_API_SECRET=SECRET
Теперь составим схему данных. Нам необходимо описать наши данные терминами мер и измерений, или, проще говоря, в терминах "что мы хотим знать" о данных (т.е. меры) и "как мы можем разложить" данные (т.е. измерения). В нашем случае цены на акции имеют два очевидных измерения: биржевой тикер и дату.
Однако с мерами не все так просто, поскольку нам придется использовать различные функции агрегирования (т.е. способы вычисления нужных значений). Например, дневные низкие цены должны агрегироваться с типом min, поскольку недельная низкая цена - это самая низкая цена за все дни, верно? Тогда, очевидно, для ежедневных высоких цен следует использовать тип max. Для цен открытия и закрытия мы будем использовать тип avg, а для подсчета общего количества записей данных - тип count.
Теперь убедитесь в том, что только один файл в папке со схемой имеет имя Stocks.js и следующее содержание:
cube(`Stocks`, {
sql: `SELECT * FROM default.stocks`,
measures: {
count: { sql: `${CUBE}.Date`, type: `count` },
open: { sql: `${CUBE}.Open`, type: `avg`, format: `currency` },
close: { sql: `${CUBE}.Close`, type: `avg`, format: `currency` },
high: { sql: `${CUBE}.High`, type: `max`, format: `currency` },
low: { sql: `${CUBE}.Low`, type: `min`, format: `currency` },
volume: { sql: `${CUBE}.Volume`, type: `sum`, format: `currency` },
firstTraded: { sql: `${CUBE}.Date`, type: `min` },
},
dimensions: {
ticker: { sql: `${CUBE}.Ticker`, type: `string` },
date: { sql: `${CUBE}.Date`, type: `time` },
},
});
После этих изменений перезапустите свой Cube и используйте Developer Playground для работы с данными. Посмотрите, как легко найти компании, о которых мы собрали наибольшее количество данных - очевидно, потому, что они уже давным-давно публично торгуются на бирже.
Здесь мы найдем Coca-Cola (KO), Hewlett-Packard (HPQ), Johnson & Johnson (JNJ), Caterpillar (CAT), Walt Disney (DIS) и т.д. На самом деле, Вы можете легко узнать, с какого момента они торгуются, добавив в запрос показатель Stocks.firstTraded. Упс! Теперь Вы знаете, что у нас есть данные только с эпохи Unix, но это не так уж и важно, не правда ли?
Теперь создадим легкое, но симпатичное приложение. Developer Playground – отличное решение, но почему бы не написать код, как мы это обычно делаем? Это поможет изучить торги, которые когда-то были популярными на WallStreetBets .
Будучи гуру в области биржевых торгов, мы безусловно воспользуемся candlestick chart для визуализации данных. Несмотря на всю свою сложность, свечной график является мощным способом отображения ценовых данных, поскольку позволяет объединить четыре значения (цены открытия, закрытия, минимума и максимума) в одну геометрическую фигуру. Подробнее об этом Вы сможете узнать на Investopedia .
После этого обязательно перейдите в папку dashboard-app и установите несколько пакетов npm для ApexCharts. Мы будем использовать готовый компонент свечного графика, чтобы не создавать его самостоятельно:
npm install --save apexcharts react-apexcharts
Затем, создайте новый файл src/components/GameStock.js с содержанием, приведенным ниже. В основном он использует Cube API для запроса набора данных, ApexCharts для его визуализации и несколько компонентов Ant Design для управления отображением. Он не очень длинный, и Вы можете пролистать его позже:
import React, { useState, useEffect } from 'react';
import { Row, Col, Button, Select, Space, Card, Layout } from 'antd';
import { useCubeQuery } from '@cubejs-client/react';
import Chart from 'react-apexcharts';
const GameStock = () => {
const { resultSet } = useCubeQuery({
dimensions: [ 'Stocks.ticker' ],
});
const [ tickers, setTickers ] = useState([]);
const [ selectedTicker, setSelectedTicker ] = useState('GME');
useEffect(() => {
if (resultSet) {
setTickers(resultSet.tablePivot().map(x => x['Stocks.ticker']).map(x => ({ label: x, value: x })));
}
}, [ resultSet ]);
const [ dateRange, setDateRange ] = useState(dateRange2021);
return (
<Layout>
<Layout.Header style={{ backgroundColor: '#43436B' }}>
<Space size='large'>
<a href='https://cube.dev' target='_blank'>
<img src='https://cubejs.s3-us-west-2.amazonaws.com/downloads/logo-full.svg' alt='Cube' />
</a>
<Space>
<Button href='https://github.com/cube-js/cube.js' target='_blank' ghost>GitHub</Button>
<Button href='https://slack.cube.dev' target='_blank' ghost>Slack</Button>
</Space>
</Space>
</Layout.Header>
<div style={{ padding: 50 }}>
<Row gutter={[ 50, 50 ]}>
<Col span={24}>
<Space>
Ticker
<Select
style={{ width: 100 }}
showSearch
options={tickers}
value={selectedTicker}
loading={!selectedTicker}
onChange={setSelectedTicker}
filterOption={(input, option) =>
option.value.toLowerCase().indexOf(input.toLowerCase()) === 0
}
/>
or
{prominentTickers.map(t => (
<Button
key={t}
size='small'
type={t === selectedTicker ? 'primary' : 'default'}
onClick={() => setSelectedTicker(t)}
>{t}</Button>
))}
</Space>
</Col>
</Row>
<Row gutter={[ 50, 50 ]}>
<Col span={24}>
<Space>
Time frame
{dateRanges.map(([ label, range ]) => (
<Button
key={label}
size='small'
value={range}
onClick={() => setDateRange(range)}
type={range === dateRange ? 'primary' : 'default'}
>{label}</Button>
))}
</Space>
</Col>
</Row>
<Row gutter={[ 50, 50 ]}>
<Col span={24}>
<Card style={{ maxWidth: dateRange === dateRange2021 ? '900px' : '100%' }}>
{selectedTicker && (
<CandlestickChart ticker={selectedTicker} dateRange={dateRange} />
)}
</Card>
</Col>
</Row>
</div>
</Layout>
);
};
const CandlestickChart = ({ ticker, dateRange }) => {
const granularity = dateRange !== undefined ? 'day' : 'month';
const { resultSet } = useCubeQuery({
measures: [ 'Stocks.open', 'Stocks.close', 'Stocks.high', 'Stocks.low' ],
timeDimensions: [ {
dimension: 'Stocks.date',
granularity,
dateRange,
} ],
filters: [ {
dimension: 'Stocks.ticker',
operator: 'equals',
values: [ ticker ],
} ],
});
const pivotConfig = {
x: [ `Stocks.date.${granularity}` ],
y: [ 'measures' ],
joinDateRange: false,
fillMissingDates: false,
};
const data = resultSet === null ? [] : resultSet.chartPivot(pivotConfig).map(row => {
const max = Math.max(row['Stocks.open'], row['Stocks.high'], row['Stocks.low'], row['Stocks.close']);
const precision = max >= 100 ? 0 : max >= 10 ? 1 : 2;
return {
x: new Date(row.x),
y: [
row['Stocks.open'].toFixed(precision),
row['Stocks.high'].toFixed(precision),
row['Stocks.low'].toFixed(precision),
row['Stocks.close'].toFixed(precision),
],
};
});
const options = {
title: { text: '', align: 'left' },
chart: { animations: { enabled: false }, toolbar: { show: false } },
xaxis: { type: 'datetime' },
yaxis: { labels: { formatter: v => Math.round(v) }, tooltip: { enabled: true } },
};
return <Chart
options={options}
series={[ { data } ]}
type='candlestick'
height={300} />;
};
const prominentTickers = [ 'BYND', 'GME', 'IRM', 'MAC', 'NOK', 'SPCE' ];
const dateRange202x = [ '2020-01-01', '2021-03-01' ];
const dateRange2021 = [ '2021-01-01', '2021-03-01' ];
const dateRanges = [
[ '2021', dateRange2021 ],
[ '2020 – 2021', dateRange202x ],
[ 'All time', undefined ],
];
export default GameStock;
Для того, чтобы все заработало, откройте src/App.js и измените всего несколько строк чтобы добавить этот новый компонент GameStock к представлению:
+ import GameStock from './components/GameStock';
import './body.css';
import 'antd/dist/antd.css';
// ...
const AppLayout = ({
children
}) => <Layout style={{
height: '100%'
}}>
- <Header />
- <Layout.Content>{children}</Layout.Content>
+ <GameStock />
</Layout>;
// ...
Верите Вы или нет, но мы все установили! Смело запускайте свое dashboard-приложение с помощью npm run start и приготовьтесь к самому настоящему веселью )
Мы сможем видеть не только то, что произошло 28 января 2021 г., когда цена акций GameStop (GME) была экстремально волатильной: минимум составил 112 долл., максимум - 483 долл. Определенно стоит взглянуть и на IRM, MAC или NOK, поскольку они также были затронуты этим движением.
Теперь мы можем изучить цены практически всех публичных компаний или ETF. Просто введите тикер и выберите нужный временной интервал. Может быть, Вы хотите взглянуть на цены акций Google (GOOG и GOOGL) с 2005 года? Вот они:
