【Hologres連載】HologresでFact Table(単独テーブル)およびPartition Table作成について

Hi, データエンジニアの大原です。
今回はAlibaba Cloudの国際サイトで提供している Hologres をご紹介します。

f:id:sbc_ohara:20210316010930p:plain

Hologresとは

Hologres はリアルタイムのインタラクティブ分析サービスです。高い同時実行性と低いレイテンシーでTB、PBクラスのデータの移動や分析を短時間で処理できます。PostgreSQL11と互換性があり、データを多次元で分析し、ビジネスインサイトを素早くキャッチすることができます。

少し前になりますが、Hologresについての資料をSlideShareへアップロードしていますので、こちらも参考になればと思います。

www.slideshare.net

今回はHologresでFact table(単独テーブル)、およびpartition付きテーブルを作る方法を紹介します。構成図で、こんな感じです。

f:id:sbc_ohara:20210630151343p:plain


共通作業1(Hologres全体で共通事項)

  1. Hologresのインスタンス購入
    f:id:sbc_ohara:20210630152847p:plain

  2. RAMリソースグループの設定
    もしRAMユーザーでHologresを操作するのであれば、RAM画面より、Hologres操作に対する権限をアタッチします。
    f:id:sbc_ohara:20210630152805p:plain

  3. Database作成
    HologresのDatabaseはコンソール側からHoloWeb側へ遷移し、登録、設定ができます。
    f:id:sbc_ohara:20210630152507p:plain HoloWebでDatabaseを作成します。 f:id:sbc_ohara:20210630152543p:plain

  4. User作成 同じく、HoloWeb側で設定します。
    また、RAMユーザーに紐づいたユーザーを作成することも可能です。

  5. WhiteList作成 これもHoloWeb側で設定します。

共通作業2(DataWorksの設定)

DataWorksでWorkSpaceを設定、導入します。詳しくはこちらの記事で説明しています。

www.sbcloud.co.jp


まずはECSからFact table(単独テーブル)を作ってみます。

ECSからのFact table(単独テーブル)作成について

STEP1: 実行環境での初期設定

ECSでPostgreSQL接続をするためにPostgreSQLクライアントをインストールします。ECSはCentOS7.8を使用しています。
まずはCentOSのアップデートから。

### CentOS Upgrade
yum -y upgrade
yum -y install wget

### PostgreSQLを入れる
yum search postgresql
yum -y install postgresql.x86_64

PostgreSQLのクライアントをインストールします。

[root@proxima ~]# yum -y install postgresql.x86_64
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package postgresql.x86_64 0:9.2.24-6.el7_9 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-6.el7_9 for package: postgresql-9.2.24-6.el7_9.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-9.2.24-6.el7_9.x86_64
--> Running transaction check
---> Package postgresql-libs.x86_64 0:9.2.24-6.el7_9 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                            Arch                      Version                              Repository                  Size
====================================================================================================================================
Installing:
 postgresql                         x86_64                    9.2.24-6.el7_9                       updates                    3.0 M
Installing for dependencies:
 postgresql-libs                    x86_64                    9.2.24-6.el7_9                       updates                    235 k

Transaction Summary
====================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 3.3 M
Installed size: 17 M
Downloading packages:
(1/2): postgresql-9.2.24-6.el7_9.x86_64.rpm                                                                  | 3.0 MB  00:00:00
(2/2): postgresql-libs-9.2.24-6.el7_9.x86_64.rpm                                                             | 235 kB  00:00:00
------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                10 MB/s | 3.3 MB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql-libs-9.2.24-6.el7_9.x86_64                                                                            1/2
  Installing : postgresql-9.2.24-6.el7_9.x86_64                                                                                 2/2
  Verifying  : postgresql-libs-9.2.24-6.el7_9.x86_64                                                                            1/2
  Verifying  : postgresql-9.2.24-6.el7_9.x86_64                                                                                 2/2

Installed:
  postgresql.x86_64 0:9.2.24-6.el7_9

Dependency Installed:
  postgresql-libs.x86_64 0:9.2.24-6.el7_9

Complete!
[root@proxima ~]#

STEP2: ECSからHologresへpsql接続

ECSからPostgreSQL接続をします。接続方法は次の通りです。
PGUSER= PGPASSWORD= psql -p -h -d postgres

<AccessID>、<AccessKey> はAlibaba Cloud AccessKeyから、
<Port>、<Endpoint> はコンソールから確認できます。
f:id:sbc_ohara:20210630203619p:plain

<AccessID>、<AccessKey>、<Port>、<Endpoint>がわかれば、psqlとして接続します。(retail_dbは事前に作成したDatabase名です)
Hologresへのデータ移動の量やネットワークトラフィックなどを鑑みて、基本的にはVPC Endpointによる接続を推奨します。
f:id:sbc_ohara:20210630203850p:plain

これで接続が出来たらOKです。

STEP3-1: ECSインスタンスにあるcsvファイルへの格納

これはPostgreSQLをベースとする作業なので非常に簡単だと思います。
何かに行き詰まったらPostgreSQL11をベースとした他の技術サイト、Webサイトなどで探っても良いと思います。

例えば、list.csvファイルがあり、そのCSVファイルの中身がこれだった場合、

10001,どらえもん,120
10002,のび太,11
10003,スネ夫,10
10004,ジャイアン,11
10005,ドラミ,106

Hologres側でそれに伴うテーブルを作成し、

CREATE TABLE list_table (
  id char(15) NOT NULL,
  name varchar NOT NULL,
  age INTEGER ,
PRIMARY KEY (id)
);

PostgreSQLのCopyコマンドと同じように格納するだけです。

\copy list_table from './list.csv' with csv

結果、すぐに格納、テーブルが見れるようになりました。
f:id:sbc_ohara:20210630233004p:plain

STEP3-2: Pythonスクリプトによる格納

今回データはFakerを使います。Fakerはダミーデータを自動出力するライブラリです。
github.com

gccコンパイラ、Python38、psycopg2-binary、Fakerをインストールします。Python3.8です。

yum install centos-release-scl
yum install rh-python38
scl enable rh-python38 bash
pip install psycopg2-binary
pip install Faker

Pythonコードとして以下入力します。ファイル名はput_fake_data.pyです。

from faker import Faker
fake = Faker(['ja_JP'])
for i in range(10):
    print(str(i) + ',' + fake.name() + ',' + ',' + fake.address() + ',' + fake.phone_number() + ',' + fake.date() + ',' + fake.job())

出力結果として、以下のようにダミーデータが出力できたらOKです。

[root@proxima ~]# python3 put_fake_data.py
0,長谷川 直子,,岡山県三鷹市中三依38丁目9番10号,090-6881-7943,1987-07-15,English as a foreign language teacher
1,John Ford,,Rotonda Pedroni 988 Piano 5
Sesto Marta terme, 47136 Venezia (RM),+39 18 6202595,1975-11-09,電気工事士
2,村上 京助,,熊本県大島町吾妻橋8丁目11番20号 ハイツ鶴ヶ丘094,070-9928-9404,1997-10-19,映画監督
3,Carlos Clements,,753 Holland Spur Suite 806
Burgessfort, UT 56575,(270)227-1670x2954,1999-01-27,Insurance risk surveyor
4,Larry Johnson,,青森県東大和市北青山27丁目23番7号 クレスト三ノ輪249,+1-026-496-1361,1986-12-10,Government social research officer
5,Kim Murphy,,大阪府横浜市栄区入谷31丁目24番16号 クレスト上高野376,001-051-403-7518x852,2000-02-06,Librarian, public
6,Richard Jackson,,Stretto Giustino 74
Bartolomeo lido, 47660 Trapani (CR),(417)791-9369x62702,1970-03-23,Producer, radio
7,Catherine Gibson,,Via Grisoni 5 Piano 9
Rita laziale, 39328 Torino (PV),+39 798 5162343,2011-05-06,Theatre stage manager
8,Annetta Albertini,,熊本県中野区北青山40丁目1番8号 クレスト鍛冶ケ沢109,068-025-6953,1981-07-02,エステティシャン
9,加藤 くみ子,,Vicolo Bianca 963
Sesto Loretta sardo, 77123 Ascoli Piceno (OR),+39 24 72759550,2014-02-28,エンジニア演奏家
[root@proxima ~]#

Hologres側でこれを受け入れるテーブルを作成します。

CREATE TABLE Customer_list(
  id INTEGER NOT NULL,
  name varchar(50) NOT NULL,
  address varchar(255) NOT NULL,
  phone_number  varchar(50) NOT NULL,
  date date,
  job varchar(50),
  PRIMARY KEY (id)
);

テーブル作成後は、上記、put_fake_data.pyファイルにて psycopg2 によるHologres接続、データの登録SQLを入れます。

import psycopg2 
from faker import Faker

# 接続
connection = psycopg2.connect(\
    host='hgpost-sg-4vl27pie0001-ap-northeast-1-vpc.hologres.aliyuncs.com',\
    port=80,\
    dbname='retail_db',\
    user='LTAI5txxxxxxxxxxxxdG9umV',\
    password='QnlbsxxxxxxxxxqrAGR7PA')

# fakerで日本語設定
fake = Faker(['ja_JP'])

# カーソル取得
cursor = connection.cursor() 

# SQL実行
for i in range(10):
  insert_data_sql = str(i) + ",'" + fake.name() + "','" + fake.address() + "','" + fake.phone_number() + "','" + fake.date() + "','" + fake.job() + "'"
  cursor.execute("INSERT INTO Customer_list VALUES (" + insert_data_sql + ")") 

# もし上記SQLがSelect文で、結果取得したいのであれば、以下コードを追加
# for row in cursor.fetchall() : 
#     print(row)

# カーソル終了
cursor.close() 

# psycopg2終了
connection.close() 

あとはHologres SQLで確認します。
f:id:sbc_ohara:20210630231845p:plain

これでPython実行によるHologresへのデータ格納が出来たことを確認できました。



今度はpartition付きテーブルを作成してみます。

OSSからのpartition付きテーブル作成について

今回はDataWorks DataIntegrationで操作します。
DataWorksに関する詳しい説明はMaxComputeのBlogにて説明していますので、ここは軽く説明します。

www.sbcloud.co.jp

www.sbcloud.co.jp

Step1: 事前準備

全てのプロセスを完了するためには、以下のインスタンスを準備する必要があります。

  • OSSのCSVデータファイル
  • Hologres partition table
  • DataWorks DI用のDataWorksプロジェクトとリソースグループ

OSSのCSVデータファイル

ソースデータファイルはOSSバケットに格納され、以下のようなパーティションキーに基づいたフォルダ構造になっています。

|--bob-demo-oss-jp(OSS bucket) |--|--partition_demo(parent folder of the data files) |--|--|--partition_key=202105(detail partition folder) |--|--|--|--test_data_202105.csv(detail data file) |--|--|--partition_key=202106(detail partition folder) |--|--|--|--test_data_202106.csv(detail data file)

f:id:sbc_ohara:20210706213245p:plain f:id:sbc_ohara:20210706213256p:plain

また、テスト用データをデータファイルはこのような構造になっています。

202106,1,partion 202106 with id 1
202106,2,partion 202106 with id 2
202106,3,partion 202106 with id 3
202106,4,partion 202106 with id 4
202106,5,partion 202106 with id 5
202106,6,partion 202106 with id 6

Hologres パーティションテーブルの準備

Hologresインスタンスを作成します。

f:id:sbc_ohara:20210706213313p:plain f:id:sbc_ohara:20210706213322p:plain f:id:sbc_ohara:20210706213330p:plain

インスタンス詳細ページの「Database」メニューをクリックして、HoloWebコンソールに入ります。

f:id:sbc_ohara:20210706213345p:plain

システム管理ページでターゲットデータベースを作成します。

f:id:sbc_ohara:20210706213356p:plain f:id:sbc_ohara:20210706213405p:plain

メタデータ管理」のページで、対象となるパーティションテーブルを作成します。

f:id:sbc_ohara:20210706213417p:plain f:id:sbc_ohara:20210706213426p:plain f:id:sbc_ohara:20210706213434p:plain f:id:sbc_ohara:20210706213444p:plain

DataWorks DI用のDataWorksプロジェクトとリソースグループ

以下の手順で新規にDataWorksプロジェクトを作成します。

f:id:sbc_ohara:20210706213502p:plain f:id:sbc_ohara:20210706213511p:plain f:id:sbc_ohara:20210706213520p:plain f:id:sbc_ohara:20210706213531p:plain

パブリックリソースグループは、Hologresの統合ジョブには使用できませんでした。代わりにDedicated Resource Groupを購入する必要があります。

現在、データソースページやジョブ設定ページでの購入処理ができません(2021/7/5時点、現在Alibaba側対応中)。なので、導入の際はリソースグループのメニューから関連するリソースを調達する必要があります。

f:id:sbc_ohara:20210706213546p:plain f:id:sbc_ohara:20210706213554p:plain f:id:sbc_ohara:20210706213601p:plain f:id:sbc_ohara:20210706213609p:plain f:id:sbc_ohara:20210706213618p:plain f:id:sbc_ohara:20210706213627p:plain f:id:sbc_ohara:20210706213637p:plain f:id:sbc_ohara:20210706213647p:plain f:id:sbc_ohara:20210706213655p:plain

STEP2: DataWorks DataIntegrationでデータ格納における定期的なジョブ設定(同期処理)

上記STEP1 の事前準備が完了したら、今度はDataWorks DataIntegrationで、HologresをData sourceとして登録しながらデータ統合ジョブ(同期処理)を実行します。

データ統合ジョブはデータソースに基づいて定義されますが、ここではOSS ReaderHologres Writerを使用します。

f:id:sbc_ohara:20210706213728p:plain f:id:sbc_ohara:20210706213736p:plain

OSSデータソースの構成

Add Data Source ボタンをクリックし、OSSデータソースを設定します。

f:id:sbc_ohara:20210706213750p:plain f:id:sbc_ohara:20210706213759p:plain f:id:sbc_ohara:20210706213809p:plain

Hologresデータソースの設定

Hologresデータソースの場合も同じプロセスです。 f:id:sbc_ohara:20210706213827p:plain f:id:sbc_ohara:20210706213835p:plain f:id:sbc_ohara:20210706213844p:plain

データソースが接続テストで失敗する場合は、警告メッセージを表示して関連する設定を確認することができます。

f:id:sbc_ohara:20210706213904p:plain

DIジョブの設定

DataWorks の DataStdio で BusinessFlow とバッチ同期ノードを作成します。

f:id:sbc_ohara:20210706213923p:plain f:id:sbc_ohara:20210706213935p:plain

ウィザードでOSSデータソースをソースとして、Hologresデータソースをターゲットとして設定し、データを正常にプレビューします。

f:id:sbc_ohara:20210706213950p:plain

file pathpartition infoarguments をパラメータ引数として設定します。

f:id:sbc_ohara:20210706214011p:plain

STEP3: DataIntegrationジョブ設定(同期処理)の結果確認

パラメータを使って実行し、関連する結果をHolo Webで確認します。

f:id:sbc_ohara:20210706214042p:plain f:id:sbc_ohara:20210706214050p:plain

Hologres側で、HoloWebとしてログイン後、新しく作成したパーティションとそのデータを確認します。 f:id:sbc_ohara:20210706214124p:plain


注意事項

Hologresは2019年11月11日 独身の日で、毎秒5億9600万件のリアルタイム格納をしつつ、2.5PBを超えるのFact table(単独テーブル)で運用。多くの多次元分析とインタラクティブ分析を同時実行しつつも、クエリの99%は80ms以下で返却という実績 があります。これはこれですごいですが、これと同等レベルのパフォーマンスを実体感するためには、Hologresのアーキテクチャ性質上、注意しなければならないことがあります。
HologresはShardという、1つのテーブルを複数に分割しながらリソース管理する機能があります。

Shardはインスタンスのスペック事に決まっており、
* Shard数が多いほど、Apache Flink、RealtimeCompute、Apache Spark Streamingからのリアルタイム書き込み処理で負荷がかからない。しかし、リソースに空白(無駄)があるため、SQLクエリでは少し負荷がかかりやすい。
* Shard数が少ないほど、SQLクエリによる検索が高速です。しかし、書き込み処理で分散パフォーマンスが取りにくいため、リアルタイム書き込み処理で負荷がかかりやすい。

というトレードオフ問題があります。 そのため、まずはインスタンスでスモールスタート構成からスタートし、メトリクスやパフォーマンスをみながらインスタンススペックの変更(Shard台数の調整)をすると良いです。

f:id:sbc_ohara:20210630194950p:plain

www.alibabacloud.com

www.alibabacloud.com

alibabatech.medium.com


最後に

本記事では、HologresでFact Table(単独テーブル)およびPartition Tableを作成・格納する方法を簡単に説明しました。

最後までお読みいただきありがとうございました。