HologresでFact Table(単独テーブル)およびPartition Table作成について
HologresでFact Table(単独テーブル)およびPartition Table作成について
本記事では、Hologres で、Fact table(単独テーブル)、およびpartition付きテーブルを作る方法を紹介します。
Hologresとは
Hologres はリアルタイムのインタラクティブ分析サービスです。高い同時実行性と低いレイテンシーでTB、PBクラスのデータの移動や分析を短時間で処理できます。PostgreSQL11と互換性があり、データを多次元で分析し、ビジネスインサイトを素早くキャッチすることができます。
少し前になりますが、Hologresについての資料をSlideShareへアップロードしていますので、こちらも参考になればと思います。
今回はHologresでFact table(単独テーブル)、およびpartition付きテーブルを作る方法を紹介します。構成図で、こんな感じです。
共通作業1(Hologres全体で共通事項)
Hologresのインスタンス購入
RAMリソースグループの設定
もしRAMユーザーでHologresを操作するのであれば、RAM画面より、Hologres操作に対する権限をアタッチします。
Database作成
HologresのDatabaseはコンソール側からHoloWeb側へ遷移し、登録、設定ができます。
HoloWebでDatabaseを作成します。User作成 同じく、HoloWeb側で設定します。
また、RAMユーザーに紐づいたユーザーを作成することも可能です。WhiteList作成 これもHoloWeb側で設定します。
共通作業2(DataWorksの設定)
DataWorksでWorkSpaceを設定、導入します。
まずはECSからFact table(単独テーブル)を作ってみます。
ECSからのFact table(単独テーブル)作成について
STEP1: 実行環境での初期設定
ECSでPostgreSQL接続をするためにPostgreSQLクライアントをインストールします。ECSはCentOS7.8を使用しています。
まずはCentOSのアップデートから。
### CentOS Upgradeyum -y upgradeyum -y install wget### PostgreSQLを入れるyum search postgresqlyum -y install postgresql.x86_64
PostgreSQLのクライアントをインストールします。
[root@proxima ~]# yum -y install postgresql.x86_64Loaded plugins: fastestmirrorLoading mirror speeds from cached hostfileResolving 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 ResolutionDependencies Resolved====================================================================================================================================Package Arch Version Repository Size====================================================================================================================================Installing:postgresql x86_64 9.2.24-6.el7_9 updates 3.0 MInstalling for dependencies:postgresql-libs x86_64 9.2.24-6.el7_9 updates 235 kTransaction Summary====================================================================================================================================Install 1 Package (+1 Dependent package)Total download size: 3.3 MInstalled size: 17 MDownloading 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:00Running transaction checkRunning transaction testTransaction test succeededRunning transactionInstalling : postgresql-libs-9.2.24-6.el7_9.x86_64 1/2Installing : postgresql-9.2.24-6.el7_9.x86_64 2/2Verifying : postgresql-libs-9.2.24-6.el7_9.x86_64 1/2Verifying : postgresql-9.2.24-6.el7_9.x86_64 2/2Installed:postgresql.x86_64 0:9.2.24-6.el7_9Dependency Installed:postgresql-libs.x86_64 0:9.2.24-6.el7_9Complete![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> はコンソールから確認できます。
<AccessID>、<AccessKey>、<Port>、<Endpoint>がわかれば、psqlとして接続します。(retail_db
は事前に作成したDatabase名です)
Hologresへのデータ移動の量やネットワークトラフィックなどを鑑みて、基本的にはVPC Endpointによる接続を推奨します。
これで接続が出来たらOKです。
STEP3-1: ECSインスタンスにあるcsvファイルへの格納
これはPostgreSQLをベースとする作業なので非常に簡単だと思います。
何かに行き詰まったらPostgreSQL11をベースとした他の技術サイト、Webサイトなどで探っても良いと思います。
例えば、list.csvファイルがあり、そのCSVファイルの中身がこれだった場合、
10001,どらえもん,12010002,のび太,1110003,スネ夫,1010004,ジャイアン,1110005,ドラミ,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
結果、すぐに格納、テーブルが見れるようになりました。
STEP3-2: Pythonスクリプトによる格納
今回データはFakerを使います。Fakerはダミーデータを自動出力するライブラリです。
gccコンパイラ、Python38、psycopg2-binary、Fakerをインストールします。Python3.8です。
yum install centos-release-sclyum install rh-python38scl enable rh-python38 bashpip install psycopg2-binarypip install Faker
Pythonコードとして以下入力します。ファイル名はput_fake_data.pyです。
from faker import Fakerfake = 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.py0,長谷川 直子,,岡山県三鷹市中三依38丁目9番10号,090-6881-7943,1987-07-15,English as a foreign language teacher1,John Ford,,Rotonda Pedroni 988 Piano 5Sesto 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 806Burgessfort, UT 56575,(270)227-1670x2954,1999-01-27,Insurance risk surveyor4,Larry Johnson,,青森県東大和市北青山27丁目23番7号 クレスト三ノ輪249,+1-026-496-1361,1986-12-10,Government social research officer5,Kim Murphy,,大阪府横浜市栄区入谷31丁目24番16号 クレスト上高野376,001-051-403-7518x852,2000-02-06,Librarian, public6,Richard Jackson,,Stretto Giustino 74Bartolomeo lido, 47660 Trapani (CR),(417)791-9369x62702,1970-03-23,Producer, radio7,Catherine Gibson,,Via Grisoni 5 Piano 9Rita laziale, 39328 Torino (PV),+39 798 5162343,2011-05-06,Theatre stage manager8,Annetta Albertini,,熊本県中野区北青山40丁目1番8号 クレスト鍛冶ケ沢109,068-025-6953,1981-07-02,エステティシャン9,加藤 くみ子,,Vicolo Bianca 963Sesto 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 psycopg2from 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で確認します。
これで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)
また、テスト用データをデータファイルはこのような構造になっています。
202106,1,partion 202106 with id 1202106,2,partion 202106 with id 2202106,3,partion 202106 with id 3202106,4,partion 202106 with id 4202106,5,partion 202106 with id 5202106,6,partion 202106 with id 6
Hologres パーティションテーブルの準備
Hologresインスタンスを作成します。
インスタンス詳細ページの「Database」メニューをクリックして、HoloWebコンソールに入ります。
システム管理ページでターゲットデータベースを作成します。
メタデータ管理」のページで、対象となるパーティションテーブルを作成します。
DataWorks DI用のDataWorksプロジェクトとリソースグループ
以下の手順で新規にDataWorksプロジェクトを作成します。
パブリックリソースグループは、Hologresの統合ジョブには使用できませんでした。代わりにDedicated Resource Groupを購入する必要があります。
現在、データソースページやジョブ設定ページでの購入処理ができません(2021/7/5時点、現在Alibaba側対応中)。なので、導入の際はリソースグループのメニューから関連するリソースを調達する必要があります。
STEP2: DataWorks DataIntegrationでデータ格納における定期的なジョブ設定(同期処理)
上記STEP1 の事前準備が完了したら、今度はDataWorks DataIntegrationで、HologresをData sourceとして登録しながらデータ統合ジョブ(同期処理)を実行します。
データ統合ジョブはデータソースに基づいて定義されますが、ここではOSS ReaderとHologres Writerを使用します。
OSSデータソースの構成
Add Data Source
ボタンをクリックし、OSSデータソースを設定します。
Hologresデータソースの設定
Hologresデータソースの場合も同じプロセスです。
データソースが接続テストで失敗する場合は、警告メッセージを表示して関連する設定を確認することができます。
DIジョブの設定
DataWorks の DataStdio で BusinessFlow とバッチ同期ノードを作成します。
ウィザードでOSSデータソースをソースとして、Hologresデータソースをターゲットとして設定し、データを正常にプレビューします。
file path
、 partition info
、 arguments
をパラメータ引数として設定します。
STEP3: DataIntegrationジョブ設定(同期処理)の結果確認
パラメータを使って実行し、関連する結果をHolo Webで確認します。
Hologres側で、HoloWebとしてログイン後、新しく作成したパーティションとそのデータを確認します。
注意事項
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台数の調整)をすると良いです。
https://www.alibabacloud.com/cloud-tech/doc-detail/183398.htm
https://www.alibabacloud.com/cloud-tech/doc-detail/203609.htm
最後に
本記事では、HologresでFact Table(単独テーブル)およびPartition Tableを作成・格納する方法を簡単に説明しました。
Hologresでテーブル設計時、参考に頂ければ幸いです。