Fact・Partition Table作成方法

作成日:2021-07-06

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

本記事では、Hologres で、Fact table(単独テーブル)、およびpartition付きテーブルを作る方法を紹介します。

Hologresとは

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

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

https://www.slideshare.net/sbcloud/alibaba-cloud-hologres

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

img


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

  1. Hologresのインスタンス購入
    img

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

  3. Database作成
    HologresのDatabaseはコンソール側からHoloWeb側へ遷移し、登録、設定ができます。
    img HoloWebでDatabaseを作成します。 img

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

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

共通作業2(DataWorksの設定)

DataWorksでWorkSpaceを設定、導入します。


まずは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=<AccessID> PGPASSWORD=<AccessKey> psql -p <Port> -h <Endpoint> -d postgres

<AccessID>、<AccessKey> はAlibaba Cloud AccessKeyから、
<Port>、<Endpoint> はコンソールから確認できます。

img

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

これで接続が出来たら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

結果、すぐに格納、テーブルが見れるようになりました。
img

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

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

https://github.com/joke2k/faker

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丁目910,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丁目1120号 ハイツ鶴ヶ丘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丁目237号 クレスト三ノ輪249,+1-026-496-1361,1986-12-10,Government social research officer
5,Kim Murphy,,大阪府横浜市栄区入谷31丁目2416号 クレスト上高野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丁目18号 クレスト鍛冶ケ沢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で確認します。
img

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



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

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

今回はDataWorks DataIntegrationで操作します。

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)

img img

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

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インスタンスを作成します。

img img img

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

img

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

img img

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

img img img img

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

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

img img img img

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

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

img img img img img img img img img

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

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

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

img img

OSSデータソースの構成

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

img img img

Hologresデータソースの設定

Hologresデータソースの場合も同じプロセスです。 img img img

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

img

DIジョブの設定

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

img img

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

img

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

img

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

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

img img

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


注意事項

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台数の調整)をすると良いです。

img

https://www.alibabacloud.com/help/doc-detail/183398.htm

https://www.alibabacloud.com/help/doc-detail/203609.htm

https://alibabatech.medium.com/evolution-of-the-real-time-data-warehouses-of-the-alibaba-search-and-recommendation-data-platform-e3717bc01b7f


最後に

本記事では、HologresでFact Table(単独テーブル)およびPartition Tableを作成・格納する方法を簡単に説明しました。
Hologresでテーブル設計時、参考に頂ければ幸いです。

Close

Alibaba Cloudを始めてみましょう

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