ECSからApsaraDB for ClickHouseへデータ連携する方法

本記事では、ApsaraDB for ClickHouse で 早速使ってみたい方向けに、クイックスタートとして ECSからデータ連携する方法をご紹介します。

ECS - ClickHouseのデータ連携方法を記載します。構成図は次の通りです。

1.ClickHouseとは

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

https://clickhouse.tech/docs/en/introduction/adopters/

1.ClickHouseを準備する

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

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

https://sbopsv.github.io/cloud-tech/usecase-ClickHouse/ACH_002_clickhouse-quick-start

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

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

ClickHouse version:20.8.7.15 Edition：Single-replica Edition

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

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

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

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

1-2. 接続、踏み台となるECSを用意

①ECSを作成します

②作成したばかりのECSへログインします

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

1）ClickHouse Clientのインストールファイルをダウンロードします。AlibabaCloudのHelpページからダウンロードしてインストールします。

①下記AlibabaCloud Helpページのリンクを開きます
Use the CLI of ApsaraDB for ClickHouse to connect to a cluster

②clickhouse-clientのダウンロードファイルリンクを確認します
clickhouse-client download url

Click House version :20.8.7.15

②ECSで19.15.2.2のclickhouse-clientファイルをダウンロードします。

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

2）clickhouse-client.zipを解凍します

3）Clientでclickhouseへ接続
①接続先となるApsaraDB for ClickHouseの接続情報を確認します。コンソール側から確認することができます。

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

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

③clickhouse-clientでclickhouseへ接続します（VPC connection）

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

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

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

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

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

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

Copy
yum install -y zip unzip

③データを解凍します

Copy
unzip ontime-data.zip;

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

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

Copy
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 ;

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

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

③テーブルを表示

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

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

Copy
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のカラムと上記ターゲットテーブルのパラメータが一致していること

②Clickhouseに接続し、データを検索してみる

Copy
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;

③データを検索

Copy
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;

④データを検索

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

3.DMSでClickHouseデータを確認

1）DMSでClickHouseデータを確認します
①ローカルテーブルで検索

Copy
SELECT
    *
FROM `ontime_local`
LIMIT 20;

②分散テーブルで検索

Copy
SELECT
    *
FROM `ontime_distributed`
LIMIT 20;

最後に

ここまで、ECS（orローカル） - ClickHouseのデータ連携方法を紹介しました。
ClickHouseはMySQLベースのSQL構文でCSVファイルのインポートや分析処理が出来ますので、MySQLを参考に色々試すと良いです。

Alibaba Cloudを始めてみましょう

ソフトバンクは、Alibaba Cloudのアカウント開設から、サービス展開までをお手伝いします。

お問い合わせ窓口