【ClickHouse連載】GrafanaでClickHouseデータを可視化してみる

Hi, データエンジニアの大原です。
今回はAlibaba Cloudの国際サイトで提供している ClickHouse のデータをGrafanaで可視化する方法をご紹介します。
Grafanaは、Grafana Labs が公開しているログ・データ可視化のためのツールです。

f:id:sbc_ohara:20210714233458p:plain

ClickHouseとは

ClickHouseは非集計データを含む大量のデータを安定的かつ継続しながら集計といったリアルタイム分析を支える列指向の分散型データベースサービスです。 トラフィック分析、広告およびマーケティング分析、行動分析、リアルタイム監視などのビジネスシナリオで幅広く 使用されています。
ApsaraDB for ClickHouseの概要として、詳しいことはこの記事にてまとめています。

www.sbcloud.co.jp

clickhouse.tech

Grafanaとは

オープンソースのログ・データ可視化ツールです。 類似サービスとしてはElastic の kibana とほぼ同じようなものです。

grafana.com

qiita.com

1.ECSでClickHouseClientの準備

1-1.ClickHouseインスタンスを準備します

この手法は過去の記事でも記載していますが、再掲として記載します。

www.sbcloud.co.jp

1)まずはApsaraDB for ClickHouseインスタンスを作成します。
①VPCを作成

f:id:sbc_ohara:20210716155036p:plain

f:id:sbc_ohara:20210716155113p:plain

②ClickHouseインスタンスを作成
著者は以下のインスタンススペックでインスタンスを作成しています。

ClickHouse version:20.8.7.15
Edition:Single-replica Edition

f:id:sbc_ohara:20210716155212p:plain

f:id:sbc_ohara:20210716155220p:plain

f:id:sbc_ohara:20210716155231p:plain

2)ClickHouseの登録アカウントを作成 インスタンスをクリックし、左側にアカウント管理画面で、アカウントを作成します

f:id:sbc_ohara:20210716155256p:plain

3)ClickHouseクラスターにDMSで接続 ①ClickHouseのインスタンスをクリックし、トップメニューの「Log On to Database」をクリックします

f:id:sbc_ohara:20210716155315p:plain

② DBアカウントとパスワードを入力し、ClickHouseへログイン

f:id:sbc_ohara:20210716155334p:plain

③DMS画面でClickHouseのインスタンスが表示されます

f:id:sbc_ohara:20210716155401p:plain

1-2.ECSを用意

①ECSを作成します

f:id:sbc_ohara:20210729135942p:plain

②ECSをログインします

f:id:sbc_ohara:20210729135923p:plain

1-3.ECSでClickHouse Client環境を設定

1)ClickHouse Clientのインストールファイルをダウンロードします
①下記のリンクを開きます
Click house version :20.8.7.15 Click house download

f:id:sbc_ohara:20210729140007p:plain

②20.8.3.18のインストールファイルをダウンロードします

wget --content-disposition https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-server-common-20.8.3.18-1.el7.x86_64.rpm/download.rpm
wget --content-disposition https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-server-20.8.3.18-1.el7.x86_64.rpm/download.rpm
wget --content-disposition https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-common-static-20.8.3.18-1.el7.x86_64.rpm/download.rpm
wget --content-disposition https://packagecloud.io/Altinity/clickhouse/packages/el/7/clickhouse-client-20.8.3.18-1.el7.x86_64.rpm/download.rpm

f:id:sbc_ohara:20210729140030p:plain

f:id:sbc_ohara:20210729140037p:plain

f:id:sbc_ohara:20210729140045p:plain

f:id:sbc_ohara:20210729140053p:plain

f:id:sbc_ohara:20210729140101p:plain

2)下記のコマンドでクライアントをインストールします

rpm -ivh *.rpm

f:id:sbc_ohara:20210729140121p:plain

3)clickhouseをclient接続
①clickhouse接続情報を確認します

Cluster ID: cc-0iw4v4hezq9lw9333
VPC Endpoint:cc-0iw4v4hezq9lw9333.ads.aliyuncs.com

f:id:sbc_ohara:20210729140134p:plain

②clickhouseコンソール画面にECSのPrivateIPをホワイトリストに追加します

f:id:sbc_ohara:20210729140146p:plain

③clickhouse-clientでclickhouseを接続します(VPC connection)

clickhouse-client --multiline --host=cc-0iw4v4hezq9lw9333.ads.aliyuncs.com --port=3306 --user=sbtest --password=********

f:id:sbc_ohara:20210729140202p:plain

2.ローカルファイルをClickHouseにインポート

2-1.ローカルファイルを用意

①下記コマンドでデータをダウンロードします

wget https://clickhouse-release.oss-cn-shanghai.aliyuncs.com/doc-data/ontime-data.zip

f:id:sbc_ohara:20210729140217p:plain

②unzipコマンドをインストール

yum install -y zip unzip

f:id:sbc_ohara:20210729140229p:plain

③データを解凍

unzip ontime-data.zip;

f:id:sbc_ohara:20210729140241p:plain

2-2.ClickHouseにデーブルを作成

①下記コマンドでローカルテーブルを作成

CREATE TABLE ontime_local ON CLUSTER default
(
    Year UInt16,
    Quarter UInt8,
    Month UInt8,
    DayofMonth UInt8,
    DayOfWeek UInt8,
    FlightDate Date,
    UniqueCarrier FixedString(7),
    AirlineID Int32,
    Carrier FixedString(2),
    TailNum String,
    FlightNum String,
    OriginAirportID Int32,
    OriginAirportSeqID Int32,
    OriginCityMarketID Int32,
    Origin FixedString(5),
    OriginCityName String,
    OriginState FixedString(2),
    OriginStateFips String,
    OriginStateName String,
    OriginWac Int32,
    DestAirportID Int32,
    DestAirportSeqID Int32,
    DestCityMarketID Int32,
    Dest FixedString(5),
    DestCityName String,
    DestState FixedString(2),
    DestStateFips String,
    DestStateName String,
    DestWac Int32,
    CRSDepTime Int32,
    DepTime Int32,
    DepDelay Int32,
    DepDelayMinutes Int32,
    DepDel15 Int32,
    DepartureDelayGroups String,
    DepTimeBlk String,
    TaxiOut Int32,
    WheelsOff Int32,
    WheelsOn Int32,
    TaxiIn Int32,
    CRSArrTime Int32,
    ArrTime Int32,
    ArrDelay Int32,
    ArrDelayMinutes Int32,
    ArrDel15 Int32,
    ArrivalDelayGroups Int32,
    ArrTimeBlk String,
    Cancelled UInt8,
    CancellationCode FixedString(1),
    Diverted UInt8,
    CRSElapsedTime Int32,
    ActualElapsedTime Int32,
    AirTime Int32,
    Flights Int32,
    Distance Int32,
    DistanceGroup UInt8,
    CarrierDelay Int32,
    WeatherDelay Int32,
    NASDelay Int32,
    SecurityDelay Int32,
    LateAircraftDelay Int32,
    FirstDepTime String,
    TotalAddGTime String,
    LongestAddGTime String,
    DivAirportLandings String,
    DivReachedDest String,
    DivActualElapsedTime String,
    DivArrDelay String,
    DivDistance String,
    Div1Airport String,
    Div1AirportID Int32,
    Div1AirportSeqID Int32,
    Div1WheelsOn String,
    Div1TotalGTime String,
    Div1LongestGTime String,
    Div1WheelsOff String,
    Div1TailNum String,
    Div2Airport String,
    Div2AirportID Int32,
    Div2AirportSeqID Int32,
    Div2WheelsOn String,
    Div2TotalGTime String,
    Div2LongestGTime String,
    Div2WheelsOff String,
    Div2TailNum String,
    Div3Airport String,
    Div3AirportID Int32,
    Div3AirportSeqID Int32,
    Div3WheelsOn String,
    Div3TotalGTime String,
    Div3LongestGTime String,
    Div3WheelsOff String,
    Div3TailNum String,
    Div4Airport String,
    Div4AirportID Int32,
    Div4AirportSeqID Int32,
    Div4WheelsOn String,
    Div4TotalGTime String,
    Div4LongestGTime String,
    Div4WheelsOff String,
    Div4TailNum String,
    Div5Airport String,
    Div5AirportID Int32,
    Div5AirportSeqID Int32,
    Div5WheelsOn String,
    Div5TotalGTime String,
    Div5LongestGTime String,
    Div5WheelsOff String,
    Div5TailNum String
)ENGINE = MergeTree()
 PARTITION BY toYYYYMM(FlightDate)
 PRIMARY KEY (intHash32(FlightDate))
 ORDER BY (intHash32(FlightDate),FlightNum)
 SAMPLE BY intHash32(FlightDate)
SETTINGS index_granularity= 8192 ;

f:id:sbc_ohara:20210729140300p:plain

f:id:sbc_ohara:20210729140309p:plain

f:id:sbc_ohara:20210729140319p:plain

②下記コマンドで分散テーブルを作成

CREATE TABLE ontime_distributed ON CLUSTER default AS ontime_local
ENGINE = Distributed(default, default, ontime_local, rand());

f:id:sbc_ohara:20210729140334p:plain

③テーブルを表示

f:id:sbc_ohara:20210729140434p:plain

2-3.ローカルファイルをClickHouseにインポート

①下記コマンドでローカルファイルontime-data.csvをClickHouseにインポート

clickhouse-client --host=cc-0iw4v4hezq9lw9333.ads.aliyuncs.com --port=3306 --user=sbtest --password=****** --query="INSERT INTO ontime_distributed FORMAT CSVWithNames" < ontime-data.csv;

備考:
・パスワードを正しく設定すること
・ダウンロードファイルontime-data.csvが変わる可能性があるので、ontime-data.csvのカラムと上記ターゲットテーブルのパラメータが一致していること

f:id:sbc_ohara:20210729140452p:plain

②Clickhouseに接続し、データを検索します

SELECT
    OriginCityName, 
    DestCityName, 
    count(*) AS flights,
    bar(flights, 0, 20000, 40) 
FROM ontime_distributed 
WHERE Year = 1988 
GROUP BY OriginCityName, DestCityName 
ORDER BY flights 
DESC LIMIT 20;

f:id:sbc_ohara:20210729140535p:plain

③データを検索します

SELECT
    OriginCityName < DestCityName ? OriginCityName : DestCityName AS a, 
    OriginCityName < DestCityName ? DestCityName : OriginCityName AS b,
    count(*) AS flights,
    bar(flights, 0, 40000, 40) 
FROM ontime_distributed 
WHERE Year = 1988 
GROUP BY a, b 
ORDER BY flights 
DESC LIMIT 20;

f:id:sbc_ohara:20210729140551p:plain

④データを検索します

SELECT
    OriginCityName,
    count(*) AS flights
FROM ontime_distributed 
GROUP BY OriginCityName 
ORDER BY flights 
DESC LIMIT 20;

f:id:sbc_ohara:20210729140606p:plain

3.Grafana環境の準備

1)ECSにてGrafanaをインストールします

# mkdir grafana
# cd grafana
# wget https://dl.grafana.com/oss/release/grafana-6.5.2-1.x86_64.rpm
# yum install grafana-6.5.2-1.x86_64.rpm

f:id:sbc_ohara:20210729140621p:plain

f:id:sbc_ohara:20210729140632p:plain

2)ClickHouse Grafanaプラグインのインストール
grafana-cliでClickHouse Grafanaプラグインをインストールします

grafana-cli plugins install vertamedia-clickhouse-datasource

f:id:sbc_ohara:20210729140649p:plain

3)Grafanaを起動し、Grafanaのステータスを確認します

# systemctl start grafana-server.service
# systemctl status grafana-server.service

f:id:sbc_ohara:20210729140700p:plain

4.GrafanaでClickHouseデータ可視化

4-1.ClickHouseデータソースを設定します

1)Grafana登録
①ECSセキュリティグループにポート3000を追加します

f:id:sbc_ohara:20210729140713p:plain

フォーマット:

url:IP:3000
ID/password:admin/admin

②登録URL:該当ECSのIPは47.245.11.176です。
そのため、GrafanaのURLは47.245.11.176:3000 です。

f:id:sbc_ohara:20210729140725p:plain

③IDとPasswordを入力し、ログインします

f:id:sbc_ohara:20210729140736p:plain

④初回ログインする場合、パスワードをリセットする必要があります。
まずはGrafana画面が表示されますので、対応します。

f:id:sbc_ohara:20210729140748p:plain

2)GrafanaでClickHouseデータソースを設定します 。
①「add data source」をクリック

f:id:sbc_ohara:20210729140800p:plain

②Clickhouseを検索

f:id:sbc_ohara:20210729140812p:plain

③Clickhouseのselectメニューをクリック

f:id:sbc_ohara:20210729140825p:plain

④Clickhouseデータソースの設定画面が表示されます

f:id:sbc_ohara:20210729140839p:plain

⑤Clickhouseデータソースを設定します
* ECSとClickhouseは同じVPCにあるため、VPC Endpointで接続します
* 事前に、ClickhouseのWhitelist に ECS の PrivateIP を設定する必要があります

url:http://cc-0iw4v4hezq9lw9333.ads.aliyuncs.com:8123
Basic auth:clickhouse account id and password
Default database:default

f:id:sbc_ohara:20210729140851p:plain

f:id:sbc_ohara:20210729140912p:plain

4-2.dashboardを設定

1)dashboardを設定します
①DMSでClickhouseのdefaultデータベースに下記コマンドを実行します

SELECT count(*) FROM `ontime_distributed`;

f:id:sbc_ohara:20210729141044p:plain

②dashboard homeをクリック
f:id:sbc_ohara:20210729141054p:plain

③dashboardを作成
f:id:sbc_ohara:20210729141105p:plain

④「Add query」をクリック
f:id:sbc_ohara:20210729141116p:plain

⑤dashboardを設定します
備考:「;」マークは入力しません。

Query:ClickHouse
A:SELECT count(*) FROM `ontime_distributed`
Format as: Table

f:id:sbc_ohara:20210729141129p:plain

⑥Visualizationを設定します

Visualization:Singlestat
Show:First

f:id:sbc_ohara:20210729141142p:plain

⑦generalを設定
TitleとDescriptionを設定します
f:id:sbc_ohara:20210729142622p:plain

⑧Dashboardを確認します
f:id:sbc_ohara:20210729142639p:plain

2)その他Dashboardを設定します
①DMSでClickhouseのdefaultデータベースに下記コマンドを実行

SELECT 
    OriginCityName, 
    DestCityName, 
    count(*) AS flights,
    bar(flights, 0, 20000, 40) 
FROM ontime_distributed 
WHERE Year = 1988 
GROUP BY OriginCityName, DestCityName 
ORDER BY flights 
DESC LIMIT 20;

f:id:sbc_ohara:20210729142653p:plain

②dashboard Panelを追加します

f:id:sbc_ohara:20210729142703p:plain

③下記コマンドをQueryに入力します

Query:ClickHouse
A:SELECT OriginCityName, DestCityName, count(*) AS flights,bar(flights, 0, 20000, 40) FROM ontime_distributed WHERE Year = 1988 GROUP BY OriginCityName, DestCityName ORDER BY flights DESC LIMIT 20
Format as: Table

f:id:sbc_ohara:20210729142713p:plain

④Visualizationの設定
Tableを選択します

Visualization:Table

f:id:sbc_ohara:20210729142723p:plain

⑤Generalの設定
TitleとDescriptionを設定します f:id:sbc_ohara:20210729142733p:plain

⑥編集したDashboardを確認
f:id:sbc_ohara:20210729142743p:plain


最後に

ここまで、GrafanaでClickHouseデータを可視化する方法を紹介しました。
ApsaraDB for ClickHouseはGrafanaとスムーズに連携できるので、Elasticsearch - kibanaのような可視化ソリューションとして仕上げることもできます。

Special Thanks, Nancy