【ClickHouse連載】ApsaraDB for ClickHouse - OSSデータ連携方法

Hi, データエンジニアの大原です。
今回はAlibaba Cloudの国際サイトで提供している ClickHouse で 早速使ってみたい方向けに、クイックスタートとして OSSとデータ連携する方法をご紹介します。
OSS(Object Storage Service)は、AlibabaCloudが提供する低コストのオブジェクトストレージサービスです。ClickHouseは、ProtobufとCapnProtoを除く、複数の形式のOSSファイルへのアクセスをサポートしています。

f:id:sbc_ohara:20210714233458p:plain

1.ClickHouseとは

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

www.sbcloud.co.jp

clickhouse.tech


前提条件: ・ClickHouseバックエンドノードとOSS間のスムーズなネットワークを確保するには、テーブル関数またはテーブルエンジンパラメーターに入力されたossエンドポイントがVPCのエンドポイントである必要があります。 また、OSSバケットはClickHouseインスタンスと同じリージョンにある必要があります。
・テーブル関数にacces-key-idとaccess-key-secretはoss-file-pathへの読み取り権限が必要です。
・テーブルエンジンにacces-key-idとaccess-key-secretは、oss-file-pathへの読み取り権限が必要です。 挿入操作を実行する必要がある場合は、書き込み権限も必要です。
・oss-file-pathパラメータのフォーマットは oss://<bucket-name/<path-to-file> です。
・file-format-nameとcolumn-definitionsは、実際のファイル形式と一致している必要があります。なお、ProtobufとCapnProtoは非対応です。対応フォーマット
・OSSに既存のファイルを挿入する場合、OSSには追加可能なオブジェクトしか書き込むことができないため、AppendObjectインターフェイスを介してファイルがOSSにアップロードされていることを確認する必要があります。

clickhouse.tech

1.ClickHouseを準備する

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

2.OSSでデータを準備する

2-1.OSSにCSVファイルをアップロード

1)OSSバケットを新規作成
①AlibabaCloudのサイトをログインし、OSSをクリックします

f:id:sbc_ohara:20210716202823p:plain

②バケットを作成 f:id:sbc_ohara:20210716202840p:plain

f:id:sbc_ohara:20210716202849p:plain

f:id:sbc_ohara:20210716202859p:plain

③下記サンプルデータをCSVファイルとして保存し、OSSにアップロードします。

id, user_name, age, city, access_url
1,tick,32,shanghai,http://xdbdsd.com/xgwgwe
2,wangl,22,beijing,http://ghwbw.com/xgwgwe
3,xiaoh,23,shenzhen,http://holko.com/xgwgwe
4,jess,45,hangzhou,http://jopjop.com/xgwgwe
5,jack,14,shanghai,http://wewsd.com/xgwgwe
6,tomy,25,hangzhou,http://sbedr.com/xgwgwe
7,lucy,45,shanghai,http://ghhwed.com/xgwgwe
8,tengyin,26,shanghai,http://hewhe.com/xgwgwe
9,cuos,27,shenzhen,http://yoiuj.com/xgwgwe
10,wangsh,37,shanghai,http://hhou.com/xgwgwe

f:id:sbc_ohara:20210716202937p:plain

③CSVファイルをOSSバケットにアップロード

f:id:sbc_ohara:20210716202955p:plain

f:id:sbc_ohara:20210716203006p:plain

f:id:sbc_ohara:20210716203019p:plain

3.ClickHouseでOSSデータを操作します。

ApsaraDB for ClickHouseは、OSSに対して2つのデータにアクセス方法を提供しています。

ApsaraDB for ClickHouseのテーブル関数を使ってOSSのデータを直接読み取って処理
テーブル関数:テーブル関数oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<column-definitions>')

ApsaraDB for ClickHouseのテーブルエンジンを使ってOSSのデータを読み取り処理、もしくは書き込み処理
テーブルエンジン:テーブルエンジンoss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>')

どのような挙動かについては次の通りです。

3-1.ApsaraDB for ClickHouseのテーブル関数を使ってOSSのデータを直接読み取って処理

①Clickhouseで下記のコマンドでOSSデータを検索します。

SELECT *
FROM oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/access_log_csv.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

f:id:sbc_ohara:20210716203905p:plain

f:id:sbc_ohara:20210716203919p:plain

②その他検索について

SELECT *
FROM oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/access_log_csv.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String') WHERE id = 9;

f:id:sbc_ohara:20210716203953p:plain

SELECT
    user_name,
    count(*) AS uv
FROM oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/access_log_csv.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String')
GROUP BY user_name;

f:id:sbc_ohara:20210716204007p:plain

備考:
テーブル関数でOSSデータをアクセスする際、ClickHouse側でデータを保存していない状態でのアクセスを推奨します。
また、OSSに保存されている大量データをアクセスする場合、OSS - ApsaraDB for ClickHouse間のNW帯域によるパフォーマンスが低下する可能性があります。加えて、ClickHouseで複雑なSQLクエリによる分析を実施する場合は、OSSのデータをClickHouseへ同期することを推奨します。

3-2.ApsaraDB for ClickHouseのテーブルエンジンを使ってOSSのデータを読み取り処理

①テーブルエンジンでテーブルを作成します

create table oss_test_table on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
engine = OSS('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/access_log_csv.csv', 'CSV');

f:id:sbc_ohara:20210716204320p:plain

②テーブルを検索します

SELECT *
FROM oss_test_table;

f:id:sbc_ohara:20210716204344p:plain

③再度テーブルを集計しながら検索

SELECT
    city,
    count(*) AS pv
FROM oss_test_table
GROUP BY city

f:id:sbc_ohara:20210716204416p:plain

備考:
テーブルエンジンでOSSデータをアクセスする際、ClickHouse側でデータを保存していない状態でのアクセスを推奨します。
また、OSSに保存されている大量データをアクセスする場合、OSS - ApsaraDB for ClickHouse間のNW帯域によるパフォーマンスが低下する可能性があります。加えて、ClickHouseで複雑なSQLクエリによる分析を実施する場合は、OSSのデータをClickHouseへ同期することを推奨します。

3-3.ApsaraDB for ClickHouseのテーブルエンジンを使ってOSSのデータを書き込み処理

①テーブルエンジンでテーブルを作成します

create table oss_test_table_appenable on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
engine = OSS('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/access_log_csv.csv', 'CSV');

f:id:sbc_ohara:20210716204524p:plain

②テーブルを検索します

select * from oss_test_table_appenable;

f:id:sbc_ohara:20210716204654p:plain

③データを挿入してみます
(この方法はデータを既存のCSVファイルに上書き保存となります。)

insert into oss_test_table_appenable values(11, 'test1', 25, 'beijing', 'http://asewg.com/jhlue');

f:id:sbc_ohara:20210716204706p:plain

④再度データを検索します

select * from oss_test_table_appenable where id =11 ;

f:id:sbc_ohara:20210716204718p:plain

4.OSSデータをClickHouseへインポート

4-1.ローカルテーブルを作成します

Single-replica Editionの場合、

create table oss_test_table_local on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
engine = MergeTree()
order by id;

f:id:sbc_ohara:20210716204812p:plain

※Double-replica Editionの場合

create table oss_test_table_local on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
engine = ReplicatedMergeTree('/clickhouse/db/default/tables/oss_test_table_local/{shard}', '{replica}')
order by id;

4-2.分散テーブルを作成します

分散テーブルを作成します

create table oss_test_table_distributed on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
engine = Distributed(default, default, oss_test_table_local, rand());

f:id:sbc_ohara:20210716204852p:plain

4-3.OSSのCSVファイルらデータをClickHouseへインポート

1)OSSのCSVファイルを確認します f:id:sbc_ohara:20210716204904p:plain

f:id:sbc_ohara:20210716204913p:plain

2)テーブルエンジンでoss_test_table1テーブルを作成 ①テーブルエンジンでoss_test_table1テーブルを作成します

create table oss_test_table1 on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
engine = OSS('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/access_log_csv1.csv', 'CSV');

f:id:sbc_ohara:20210716205005p:plain

②テーブルを検索

select * from oss_test_table1;

f:id:sbc_ohara:20210716205021p:plain

4-4.データをインポートする

①oss_test_table1のデータをClickhouseにインポートする

insert into oss_test_table_distributed select * from oss_test_table1;

f:id:sbc_ohara:20210716205038p:plain

④データを検索する

select * from oss_test_table_distributed; 

f:id:sbc_ohara:20210716205056p:plain

4-5.oss-file-pathはワイルドカードをサポートしているので、試してみます

通常、OSSには同じ命名規則を持つ複数の小さなファイルがあります。複数の小さなファイルの分析を簡素化するために、oss-file-pathパラメーターはあいまい一致のために次のワイルドカードをサポートします。
① :任意のファイル名とディレクトリ名に一致します。 たとえば、/dir/ は/dirのすべてのファイルと一致します。
②{x、y、z}:中括弧内の任意の値に一致します。 たとえば、file {x、y、z}はfile_x、file_y、file_zと一致します。
③{num1..num2}:[num1、num2]のすべての数値を展開します。 たとえば、file
{1..3}は、file_1、file_2、file_3と同等です。
④?:任意の1文字に一致します。 たとえば、file_?はfile_a、file_b、file_cなどと一致します。

まずは試してみます。
1)OSSファイルを用意します。

oss-clickhouse/
    txtfiles/
        access_log_csv1.txt
        access_log_csv2.txt
        access_log_csv3.txt

f:id:sbc_ohara:20210716205203p:plain

f:id:sbc_ohara:20210716205214p:plain

f:id:sbc_ohara:20210716205223p:plain

f:id:sbc_ohara:20210716205231p:plain

2)ClickHouseでデータを検索します ①Case1:

select * from oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/txtfiles/*', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String') order by age;

f:id:sbc_ohara:20210716205305p:plain

oss-clickhouse/
    txtfiles/
        access_log_csv1.txt
        access_log_csv2.txt
        access_log_csv3.txt

上記ファイルが検索結果として出力されます

②Case2:

select * from oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/txtfiles/access*', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String') order by age;

f:id:sbc_ohara:20210716205327p:plain

oss-clickhouse/
    txtfiles/
        access_log_csv1.txt
        access_log_csv2.txt
        access_log_csv3.txt

上記ファイルが検索結果として出力されます

③Case3:

select * from oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/txtfiles/access_log_csv{1,2,3}.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String')order by age;

f:id:sbc_ohara:20210716205415p:plain

oss-clickhouse/
    txtfiles/
        access_log_csv1.txt
        access_log_csv2.txt
        access_log_csv3.txt

上記ファイルが検索結果として出力されます

④Case4:

select * from oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/*/access_log_csv{1,3}.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String') order by age;

f:id:sbc_ohara:20210716205450p:plain

oss-clickhouse/
    txtfiles/
        access_log_csv1.txt
        access_log_csv3.txt

上記ファイルが検索結果として出力されます

⑤Case5:

select * from oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/*/*', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String') order by age;

f:id:sbc_ohara:20210716205552p:plain

oss-clickhouse/
    txtfiles/
        access_log_csv1.txt
        access_log_csv2.txt
        access_log_csv3.txt

上記ファイルが検索結果として出力されます

⑥Case6:

select * from oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/*/access_log_csv{1..3}.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String') order by age;

f:id:sbc_ohara:20210716205610p:plain

oss-clickhouse/
    txtfiles/
        access_log_csv1.txt
        access_log_csv2.txt
        access_log_csv3.txt

上記ファイルが検索結果として出力されます

⑦Case7:

select * from oss('oss-ap-northeast-1-internal.aliyuncs.com', '<your-access-key-id>', '<your-access-key-secret>', 'oss://oss-clickhouse/*/access_log_csv?.txt', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String') order by age;                     

f:id:sbc_ohara:20210716205623p:plain

oss-clickhouse/
    txtfiles/
        access_log_csv1.txt
        access_log_csv2.txt
        access_log_csv3.txt

上記ファイルが検索結果として出力されます


最後に

ここまで、OSS - ClickHouseのデータ連携方法を紹介しました。
ClickHouseはOSSにあるCSVファイルの分析や書き込み処理が出来るので、シナリオ次第ではDWHだけでなくDataLakeとして運用できると思います。

Special Thanks, Nancy