今回はデータエンジニアリングよりの話題です。
列指向DBMSである、ClickHouseにデータを格納し、BIであるLooker Studioで描画してみるシーンを試してみます。

ClickHouseとは

以下はChatGPTにまとめてもらった内容です。

• 大量データを高速に集計・分析するためのデータベース
• ログ分析、IoT、広告、金融、BIツール連携などで広く利用
• オープンソース（商用サポートやクラウド版もあり）

主な特徴・強み

とにかく高速

• カラムナ型（列指向）DB
  → 必要な列だけを読み込むため、集計クエリが非常に速い
• 数億〜数十億行のデータでも秒単位で集計可能
• CPU・メモリをフル活用する設計（SIMD最適化など）

分析クエリに最適化

• COUNT, SUM, AVG, GROUP BY などの集計が得意
• 時系列データ（ログ、センサーデータ）との相性が非常に良い
• JOIN やサブクエリも分析用途向けに高性能

高い圧縮率でストレージ効率が良い

• 列単位で圧縮するためディスク使用量が小さい
• ログ・イベント系データを長期間保存しやすい
• 「安く大量に貯めて、速く分析」が可能

スケールしやすい

• 分散構成（クラスタ）に対応
• シャーディング・レプリケーションが標準機能
• サーバーを増やすことで水平スケールが可能

SQLライクで扱いやすい

• 基本は SQLに近い文法
• BIツール（Looker Studio、Metabase、Superset など）と連携しやすい
• CSV / JSON / Parquet / S3 など多様なデータソースに対応

得意・不得意がはっきりしている

得意

• ログ分析
• ダッシュボード用集計
• 大規模時系列データの可視化

不得意

• 銀行口座のようなトランザクション処理（OLTP）
• 行単位の頻繁な UPDATE / DELETE

どんな人・用途に向いている？

• BigQuery / Redshift / Snowflake の代替や補完を検討している
• 自前環境（オンプレ・Docker・VM）で分析基盤を作りたい
• ログ・イベント・IoTデータを低コスト＆高速に分析したい

オープンソースとして、自社ホストができる部分はメリットであり、かつ高速なので、
大規模な自社データ基盤や分析用途ではなく、限られたメンバーでの利用（分析、可視化、監視など）を安く済ませたい場合には良さそうな選択しです。
また、クラスタ設計にも対応していることから、小さく始めたもののスケールさせたい場合にも対応できるのはありがたいところですね。

自社ホストでClickHouse環境を立ち上げる

ClickHouseに取り込んだデータを、BIであるLooker Studioで可視化します。
Looker StudioはGoogleが提供するブラウザベースのBIサービスであるため、今回は自分のローカル上でのClickHouse起動ではなく、AWS EC2上でのinstallで実行します。

(セキュリティ的な面を考えるのであれば、Google Cloud上のGCEなどにinstallし、VPC内に限った中でLooker Studioを接続しにいくのが一番良いと思いますが、サクッと検証のためEC2を使っています)

EC2を以下のサイズで立ち上げました。

• AMI：ubuntu（実際Amazon linuxでもなんでもOK）
• t3.small
• 30GBほどのEBS
  

installは下記の手順に乗っ取って進めます

sshクライアント上などで、ClickHouseクライアントの稼働が確認できれば、インストールは完了です。GUIでの操作も行うのであれば/etc/clickhouse-server/config.d/listen.xmlとしてxmlファイルを以下の内容で作成し、ClickHouseを再起動してみてください。

<clickhouse>
        <listen_host>0.0.0.0</listen_host>
        <mysql_port>9004</mysql_port> #これはこのあと説明するMySQLインタフェース用
</clickhouse>

systemctl restart clickhouse-server

EC2側のSGなども適切なIPを設定し、パブリックIPに8123のポートを付与してアクセスすればOKです。

MySQLインタフェースを有効化する

LookerStudioではMySQL相当としてClickHouseに接続します。
まずはインタフェースを有効化し

その上で、接続のセットアップを進めます

最終的に/etc/clickhouse-server/users.d/mysql_user.xmlの新規xmlに対して下記の内容を記載し、再起動を実施します。

<clickhouse>
    <users>
        <mysql_user>
            <password_double_sha1_hex>ここは手元で作成したsha1を入力</password_double_sha1_hex>
            <networks>
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </mysql_user>
    </users>
    <profiles>
        <default>
            <use_mysql_types_in_show_columns>1</use_mysql_types_in_show_columns>
        </default>
    </profiles>
</clickhouse>

9004のポートを通してLooker Studioもアクセスすることになるので、SGのインバウンド設定で9004ポートは開放する必要があります。

ここまで正常に設定ができていれば、9004ポートに対してmysqlクライアントでの接続が可能となっているはずなので、内外から試してみて確認すると良いです。

データを格納し、Looker Studioで接続する

データのロード

ClickHouse側で指定しているsampleデータをロードしてみます。

各国に配置されたセンサーデータとなり、全量をロードすると200億件以上のレコードとなります。今回のEBSやリソースではキャパオーバーなので、特定のファイル一つをロードしてみましょう。

補足：ClickHouse側で公開しているS3のファイル名・サイズ確認

# AWS CLIがインストールされた環境で
aws s3 ls s3://clickhouse-public-datasets/sensors/monthly/ \ 
--region eu-central-1 \ 
--no-sign-request

CREATE文は上記Docのままを使用し、以下はロードに用いたSQLです。
400万レコードぐらいがサクッとロードされます。

insert_sensor_data.sql

INSERT INTO sensors
    SELECT *
    FROM s3Cluster(
        'default',
        'https://clickhouse-public-datasets.s3.amazonaws.com/sensors/monthly/2023-02_pms5003.csv.zst',
        'CSVWithNames',
        $$ sensor_id UInt16,
        sensor_type String,
        location UInt32,
        lat Float32,
        lon Float32,
        timestamp DateTime,
        P1 Float32,
        P2 Float32,
        P0 Float32,
        durP1 Float32,
        ratioP1 Float32,
        durP2 Float32,
        ratioP2 Float32,
        pressure Float32,
        altitude Float32,
        pressure_sealevel Float32,
        temperature Float32,
        humidity Float32 $$
    )
SETTINGS
    format_csv_delimiter = ';',
    input_format_allow_errors_ratio = '0.5',
    input_format_allow_errors_num = 10000,
    input_format_parallel_parsing = 0,
    date_time_input_format = 'best_effort',
    max_insert_threads = 32,
    parallel_distributed_insert_select = 1;

ロードされたデータに対して、簡単なSQLを発行してみると感じますが、貧弱なEC2でも十分な速度感で抽出や加工を実現できます。

Looker Studioで描画

Looker Studioを開き、MySQLコネクタで接続情報を入力します。
先で設定したMySQL用のユーザー、パスワードを入力し、接続されることを確認してください。
（画像ではカスタムクエリで必要な情報のみLooker Studioに渡しています）

ここまでくれば、あとはLooker Studio上でデータを弄るだけとなります。
データには経度緯度情報と、P2（PM2.5）情報が入っているので、MAPベースでのヒートマップなどを描画してみました。

さいごに

今回はClickHouseを用いて、使い慣れたLooker Studioへの連携・描画を実施してみました。

ClickHouse Cloudなどを使えばより簡単にデータ取り込みから連携までを実現できますが、データやチームの規模感、今後のスケール性なども考慮し、他サービスとの比較も通して検討されるのが良いかと思います。

宣伝

弊社ではデータ基盤策定からLLMまで、お客様にあったプロセスでの提案とご支援が可能です、お気軽にお問合せください。

また、中途採用やインターンの応募もお待ちしています！