SingleStoreを試す！ (100万行挑戦編２)

#今回は、100万行のカラムストアに挑戦！
さて、前回は100万行のインメモリテーブルを作成しましたが、その勢いで今回は、ストレージ・デバイス側を使ったカラムストアを作って、簡単なSQLを走らせて性能検証をしてみたいと思います。

##さて、100万行の出所をどうするか・・・
幾つか方法は考えられますが、現実的な運用プロセスを考えた場合に、最初にインメモリで活性の高いデータ処理を行い、月末、期末等に過去参照データとして「更に一定期間（3年とか・・・）保存しておく様なケースを想定し、前回作成したテーブルを使って（コンバージョンして）一気に100万行のテーブルをデバイス側に立ち上げることにします（今回は、SingleStoreを導入した、自作PCのSSD上に作ります）

#謎の呪文の登場・・・
基本的に初期設定では、 SingleStore上に作成するテーブルはインメモリ上に展開されますので、それを意図的にカラムストア側に作る様に環境変数を変えます（SQLベースでカラムストア側にテーブルを作成する！という宣言も有りますが、SingleStoreの中身を知る・・という意味も有り、今回はこの作戦を選択しました）

SingleStoreでは、デフォルトでにdefault_table_typeはrowstoreに設定されていますので、この設定を一時的にcolumnstoreに変更します。

SET GLOBAL default_table_type = 'columnstore';

これで、デフォルトの操作がカラムストア側に実行される様になります。また、この状況でインメモリ側にテーブルを作成したい場合は、

CREATE ROWSTORE TABLE ..., 

で利用する事が出来、通常通りに戻す場合は・・・

SET GLOBAL default_table_type = 'rowstore';

になります。

#では、前回の100万行インメモリテーブルを変換します！
SingleStoreの場合、シンプルに以下のSQLを走らせる事でインメモリからカラムストア上への変換が可能です。（ここでは、標準状態のRowモードを暫定的に変更して処理を行い、終了後にRowモードに戻す事を想定しています）

SET GLOBAL default_table_type = 'columnstore';
CREATE TABLE C_Table_Name(<< テーブル定義を合わせます >>) AS SELECT * FROM R_Table_Name;
DROP TABLE R_Table_Name;
ALTER TABLE C_Table_Name RENAME TO R_Table_Name;
SET GLOBAL default_table_type = 'rowstore';

##そうだ！Pythonで書いてみよう！・・
前回作成したインメモリのテーブルをカラムストアへ変換する処理を、得意のバラック改造作業でサクッと作ってみました。

# coding: utf-8
#
# 日本語版BIG DATA Generator Tool
#
#  メモリ上のテーブルをカラムストアへ複製する
#
# Python 3版
#
#

#  初期設定
import sys
stdout = sys.stdout
sys.stdout = stdout

import pymysql.cursors

# テーブル初期化
Table_Init = "DROP TABLE IF EXISTS ROW2COL_Table"
Set_Col = "SET GLOBAL default_table_type = 'columnstore'"
Set_Row = "SET GLOBAL default_table_type = 'rowstore'"
   
# テーブル定義
DC0 = "id BIGINT, ts TIMESTAMP(6), "
DC1 = "Category VARCHAR(20), Product VARCHAR(20), Price INT, Units INT, Logistics VARCHAR(20), "    
DC2 = "Card VARCHAR(40), Number VARCHAR(30), Payment INT, Tax INT, "    
DC3 = "User VARCHAR(20), Zip VARCHAR(10), Prefecture VARCHAR(10), Address VARCHAR(60), Area VARCHAR(10), Tel VARCHAR(15), Email VARCHAR(40), Point INT, "
DC4 = "KEY() USING CLUSTERED COLUMNSTORE, KEY(Logistics) USING HASH"

try:
   
    from datetime import datetime
    
    print ("インメモリテーブルの変換処理を開始します　：　" +  datetime.now().strftime("%Y/%m/%d %H:%M:%S"))


    # SingleStoreに接続
    db = pymysql.connect(host = 'xxx.xxx.xxx.xxx',
                         port=3306,
                         user='xxxxxxxx',
                         password='zzzzzzzz',
                         db='xxxxxxxx',
                         charset='utf8',
                         cursorclass=pymysql.cursors.DictCursor)
       
   
    # カラムストア側のテーブル生成SQL  
    Table_Create = "CREATE TABLE ROW2COL_Table("+DC0+DC1+DC2+DC3+DC4+") AS SELECT * FROM BIG_DATA_Table"  
                           
    with db.cursor() as cursor:
        
        # モードをカラム側に変更
        cursor.execute(Set_Col)
        db.commit()
       
        # 既存テーブルの初期化
        cursor.execute(Table_Init)
        db.commit()
       
        #　新規にテーブルを作成
        cursor.execute(Table_Create)    
        db.commit()
        
        # 標準設定に戻しておく
        cursor.execute(Set_Row)
        db.commit()

except KeyboardInterrupt:
   
    print('!!!!! 割り込み発生 !!!!!')
           
finally:
   
    # データベースコネクションを閉じる
    db.close()
    print ("インメモリテーブルの変換処理が終了しました　：　" + datetime.now().strftime("%Y/%m/%d %H:%M:%S"))

##無事に変換出来ると・・
今回の検証では、以下の処理時間で無事に変換が終了しました。

インメモリテーブルの変換処理を開始します　：　2021/02/24 11:31:15
インメモリテーブルの変換処理が終了しました　：　2021/02/24 11:31:23

100万行のデータ変換ですが、「入り口」がメモリで「出口」がSSDでしたので、この位の速度が普通に出た可能性も有るので、あくまでも「参考値」としての結果かもしれませんが、上手くタイミングや運用戦略を組み立てれば、HDD環境でも十分処理環境の最適化に活用出来るかと思います。

##処理の実行前
実行前の状況はこんな感じです。

##処理の実行後
先ほどの簡単なSQLでインメモリのテーブルをカラムストア側のテーブルに変換すると、縦方向縞のアイコンを持つテーブルが出来上がります。

このアイコンのテーブル情報を見て行くと、メモリサイズが０（ゼロ）になっている事が判ると同時に、ディスク上のサイズがデータ圧縮された容量で（この場合、圧縮率が64％強）表示されています。従来のMemSQL同様に、カラム方向におけるデータ重複を最適化して（”何が何個”的な形式等に置き換えるなど）、出来るだけ処理に効率的な格納を行う様にSingleStoreでも仕様が拡張されています（OLTPやアップサートをサポート出来る様に進化しています）

#重要なポイントです・・・
カラムストアを活用する際の重要なポイントとしては、変換された各カラム情報を如何に取り扱い易く展開するか？にあります。もちろん上位のSQLアプリ側から見れば普通のRow系DBとして参照出来ますが、その特性を上手く活かす事によりより高い性能を安定して出す事が出来るようになります。

この辺のノウハウや留意点については、
Creating a Columnstore Table Using Standard CREATE TABLE
に書かれていますので、ぜひご一読される事をお勧め致します。

先程変換したテーブルを、SQLクライアントツールで確認してみます。

無事に処理が出来ている様です・・

#では、少しSQLでアクセスしてみます！
先ずはちゃんと100万行変換されているかを確認します。

select count(*)
from ROW2COL_Table;

結果は46msでした。

次に仮想配送センターでの取り扱い情報を抽出してみます。

select * from ROW2COL_Table
where Logistics ='伊丹物流センター';

100万行分のデータ相手の抽出になりますが、今回の結果は79msになりました。
最後に集計処理を入れた、少し重量級の処理を検証します。

select SUM(Units) as Sum_of_Units, AVG(Payment) as Avarage_of_Payment, Category
from ROW2COL_Table
where Category in ('書籍','DVD/CD','酒類') and Prefecture in ('愛知県','宮城県','鹿児島県')
group by Category;

結果としては、カラムストア最適化の恩恵を受けて想定外に速い131msで処理できました。

#今回のまとめ
今回は、前回作成したインメモリ上の100万行テーブルを、簡単なSQL処理でストレージ側のカラムストアへ変換し、その変換後のテーブルに対して前回同様のSQL処理を行ってみました。動作的には、デバイス性能に依存するところは有りますが、ロジック的に性能をサポート出来る部分の最適化が進み、参照系の過去データ利活用には十分使える感触を得る事が出来ました。

今回の検証を通して、SingleStoreのインメモリ・カラムストアを上手く活用することで、非常に戦闘力の高いデータ・コンピューティング・システムを構築する事が出来ると同時に、既存のＢＩやＡＩ系のモダン・トランザクション系アプリに対する、データ供給に起因する性能劣化や各種の問題を、シンプルに解決できる可能性を感じる事が出来ました。

#さて次回は・・・・
次回は、いよいよEqualumのアップデート検証を行ってみたいと思います。BCP等で活用出来そうなCDCストリーミング・レプリケーションが、Oracle環境以外にも利用可能になったとの事なので、その辺を中心に作業を行います。

#謝辞
本検証は、SIngleStore社の公式Freeバージョン（V7.３)を利用して実施しています。
この貴重な機会を提供して頂いたSingleStore社に対して感謝の意を表すると共に、本内容とSingleStore社の公式ホームページで公開されている内容等が異なる場合は、SingleSTore社の情報が優先する事をご了解ください。