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

まずは準備から・・・

今回は、Pythonで簡単なデータ・ジェネレータ（的なモノ）を作成し、それを使ってまずは100万行のインメモリテーブルを生成します。取り急ぎ作成になったので、あまり綺麗な造りにはなっていませんが、SingleStore上に空のテーブル（ネット販売的な小売系データをイメージしてみました）を作成するスクリプトを書きました。

SingleStore上へ空のテーブルを作成する

PythonでSingleStoreを取り扱う場合は、通常よく用いられているpymysqlを事前に導入しておきます。（多分、各カラムのサイズ定義等は、もっと最適化出来ると思いますが、先を急ぐ関係上、”極めてエイヤー！”で設定してありますので、気になる方は適宜修正・変更してお使いください・・・・（汗））

# coding: utf-8
#
# 日本語版BIG DATA Generator
#
# Python 3版
#
#

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

import pymysql.cursors

# テーブル初期化
Table_Init = "DROP TABLE IF EXISTS BIG_DATA_Table"

# テーブル定義
DC0 = "id BIGINT AUTO_INCREMENT, ts TIMESTAMP(6) DEFAULT NOW(), "
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 = "SHARD KEY (Area), PRIMARY KEY(id,  Area)"

try:

    print("テーブル作成処理を開始")

    # SingleStoreに接続
    db = pymysql.connect(host = 'zzz.zzz.zzz.zzz',
                         port=3306,
                         user='xxxxxxxx',
                         password='xxxxxxxx',
                         db='xxxxxxx',
                         charset='utf8',
                         cursorclass=pymysql.cursors.DictCursor)


    # デモ用のテーブルの作成  
    Table_Create = "CREATE TABLE IF NOT EXISTS BIG_DATA_Table("+DC0+DC1+DC2+DC3+DC4+")"  

    with db.cursor() as cursor:

        # 既存テーブルの初期化
        cursor.execute(Table_Init)
        db.commit()

        #　新規にテーブルを作成
        cursor.execute(Table_Create)    
        db.commit()

except KeyboardInterrupt:

    print('!!!!! 割り込み発生 !!!!!')

finally:

    # データベースコネクションを閉じる
    db.close()

    print("テーブル作成処理が終了")

作成されたテーブルにデータを挿入する

次に、この事前定義されたテーブルに対して、連続して大量のデータを挿入していく仕組みを書きます。今回は１ファイルで全てを詰め込む形式を取りましたので、前半部分に必要なメタ系データを全て定義してしまいます。（CSVファイルのインポート機能を活用すると、別ファイルを管理する必要が出てきますが、コード的にはシンプルになると思いますが・・）必要があれば、適宜設定を修正・変更してお使いください。

# coding: utf-8
#
# 日本語版BIG DATA Generator
#
# Python 3版
#

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

import time
import pymysql.cursors
import re

# カテゴリ名
Category_Name = ["酒類","家電","書籍","DVD/CD","雑貨"]

# 酒類の商品情報
Product_Name0 = ["日本酒","バーボン","ビール","芋焼酎","赤ワイン","白ワイン","スコッチ","ブランデー","泡盛","テキーラ"]
Product_Price0 = [1980, 2500, 490, 2000, 3000, 2500, 3500, 5000, 1980, 2000]    

# 家電の商品情報
Product_Name1 = ["テレビ","洗濯機","ラジオ","ステレオ","電子レンジ","パソコン","電池","エアコン","乾燥機","掃除機"]
Product_Price1 = [49800, 39800, 2980, 88000, 29800, 64800, 198, 64800, 35800, 24800]    

# 書籍の商品情報
Product_Name2 = ["週刊誌","歴史","写真集","漫画","参考書","フィクション","経済","自己啓発","月刊誌","新刊"]
Product_Price2 = [280, 1500, 2500, 570, 1480, 1400, 1800, 1540, 980, 1980]    

# DVD/CDの商品情報
Product_Name3 = ["洋楽","演歌","Jポップ","洋画","アイドル","クラッシック","邦画","連続ドラマ","企画","アニメ"]
Product_Price3 = [1980, 2200, 2500, 3500, 2980, 1980, 3800, 2690, 1980, 2400]

# 雑貨の商品情報
Product_Name4 = ["洗剤","電球","贈答品","医薬部外品","ペットフード","乾電池","文房具","男性用品","女性用品","季節用品"]
Product_Price4 = [498, 198, 1980, 398, 980, 248, 398, 2980, 3580, 1980]

#　地域名ルックアップ情報（キーは都道府県名）
Area_Data={'北海道':'北海道','青森県':'東北','岩手県':'東北','宮城県':'東北','秋田県':'東北','山形県':'東北','福島県':'東北',
           '茨城県':'関東','栃木県':'関東','群馬県':'関東','埼玉県':'関東','千葉県':'関東','東京都':'関東','神奈川県':'関東',
           '新潟県':'中部','富山県':'中部','石川県':'中部','福井県':'中部','山梨県':'中部','長野県':'中部','岐阜県':'中部','静岡県':'中部','愛知県':'中部',
           '三重県':'近畿','滋賀県':'近畿','京都府':'近畿','大阪府':'近畿','兵庫県':'近畿','奈良県':'近畿','和歌山県':'近畿',
           '鳥取県':'中国','島根県':'中国','岡山県':'中国','広島県':'中国','山口県':'中国',
           '徳島県':'四国','香川県':'四国','愛媛県':'四国','高知県':'四国',
           '福岡県':'九州・沖縄','佐賀県':'九州・沖縄','長崎県':'九州・沖縄','熊本県':'九州・沖縄','大分県':'九州・沖縄','宮崎県':'九州・沖縄','鹿児島県':'九州・沖縄','沖縄県':'九州・沖縄'}

# 物流センタールックアップ情報（キーは地域名）
Logi_Data = {'北海道':'道央物流センター','東北':'東北物流センター','関東':'関東中央物流センター',
             '中部':'甲州物流センター','近畿':'伊丹物流センター','中国':'広島臨港物流センター','四国':'讃岐物流センター','九州・沖縄':'平戸物流センター'}

# 購入ポイント情報（カテゴリ名の順番に設定
Point_Data = [0.02, 0.1, 0.03, 0.02, 0.05]

# 消費税率の設定
Tax_Data = 0.1

# 書き込み用のカラム設定
DL1 = "Category, Product, Price, Units, Logistics, "  # ビジネス情報
DL2 = "Card, Number, Payment, Tax, "  # 支払い情報
DL3 = "User, Zip, Prefecture, Address, Area, Tel, Email, Point"  # 顧客情報

# 生成するデータの数
Generate_Data = 1000000

# 途中経過のチェックポイント設定
Check_Point = 10000

try:

    print("データの生成を開始します。")

    # Fakerの初期化
    from faker import Faker
    fakegen = Faker('ja_JP')    
    Faker.seed(fakegen.random_digit())

    # ループカウンターの初期化
    Loop_Counter = 0

    # SingleStoreとの接続
    db = pymysql.connect(host = 'zzz.zzz.zzz.zzz',
                         port=3306,
                         user='xxxxxxxx',
                         password='xxxxxxxx',
                         db='xxxxxxxx',
                         charset='utf8',
                         cursorclass=pymysql.cursors.DictCursor) 

    with db.cursor() as cursor:

        # 検証データの生成
        while Loop_Counter < Generate_Data:

            # ランダムに書き込む商材の種類と商品IDを選択
            Category_ID = fakegen.random_digit()
            if Category_ID > 4: Category_ID = Category_ID - 5

            # 商品番号を決定    
            Product_ID = fakegen.random_digit()

            # カテゴリ名の設定  
            Category = Category_Name[Category_ID]

            # カラム情報の設定
            if Category_ID == 0:  # 酒類
                Product = Product_Name0[Product_ID]
                Price = Product_Price0[Product_ID]
                Units = fakegen.random_digit() + 1  # リアルっぽく調整しています
                Point = Price * Units * Point_Data[Category_ID]

            elif Category_ID == 1:  # 家電
                Product = Product_Name1[Product_ID]
                Price = Product_Price1[Product_ID]
                Units = 1  # リアルっぽく調整しています
                Point = Price * Units * Point_Data[Category_ID]

            elif Category_ID == 2:  # 書籍
                Product = Product_Name2[Product_ID]
                Price = Product_Price2[Product_ID]
                Units = fakegen.random_digit() + 1
                if Units >3: Units = 3  # リアルっぽく調整しています  
                Point = Price * Units * Point_Data[Category_ID]

            elif Category_ID == 3:  # DVD/CD
                Product = Product_Name3[Product_ID]
                Price = Product_Price3[Product_ID]
                Units = fakegen.random_digit() + 1
                if Units >2: Units = 2  # リアルっぽく調整しています
                Point = Price * Units * Point_Data[Category_ID]

            else:  # 雑貨
                Product = Product_Name4[Product_ID]
                Price = Product_Price4[Product_ID]
                Units = fakegen.random_digit() + 1
                if Units >4: Units = 4  # リアルっぽく調整しています
                Point = Price * Units * Point_Data[Category_ID]

            # 支払い情報の設定
            if str(fakegen.pybool()) == "True":
                Card = "現金"
            else:
                Card = fakegen.credit_card_provider()

            Number = fakegen.credit_card_number()              
            if Card == "現金":    Number = "N/A"

            # 支払い総額と消費税
            Payment = Units * Price
            Tax = Payment * Tax_Data

            # 購入者情報の生成
            User = fakegen.name()
            Zip = fakegen.zipcode()
            Address = fakegen.address()
            Tel = fakegen.phone_number()
            Email = fakegen.ascii_email()

            # 都道府県情報の抽出
            pattern = u"東京都|北海道|(?:京都|大阪)府|.{2,3}県"
            m = re.match(pattern , Address)
            if m:
                Prefecture = m.group()

            # 地域名と物流センター名を取得           
            Area = Area_Data.get(Prefecture)
            Logistics = Logi_Data.get(Area)

            #　此処から先を各データベースの規程テーブルへ書き込む        
            DV1 = Category+"','"+Product+"','"+str(Price)+"','"+str(Units)+"','"+Logistics+"','"
            DV2 = Card+"','"+Number+"','"+str(Payment)+"','"+str(Tax)+"','"
            DV3 = User+"','"+Zip+"','"+Prefecture+"','"+Address+"','"+Area+"','"+Tel+"','"+str(Email)+"','"+str(Point)

            sql_data = "INSERT INTO BIG_DATA_Table("+DL1+DL2+DL3+") VALUES('"+DV1+DV2+DV3+"')"

            # データベースへの書き込み
            cursor.execute(sql_data)    
            db.commit()

            #  コンソールに生成データを表示（不要な場合はコメントアウトする）
            #print (sql_data)

            # ループカウンタの更新
            Loop_Counter = Loop_Counter + 1

            # データの作成状況を表示
            if (Loop_Counter % 10) == 0:     print("途中経過：　" + str(Loop_Counter) + " 個目のデータ作成を終了")

except KeyboardInterrupt:

    print('!!!!! 割り込み発生 !!!!!')

finally:

    # データベースコネクションを閉じる
    db.close()

    print("今回生成したデータの総数 : " + str(Loop_Counter))    
    print("指定されたデータの生成が終了しました。")

今回は、以前にテスト用に作成したモノを改造して、典型的なバラック構造になってしまいましたが、逆に流れのままですので分かり易いかもしれません。最後に途中経過を表示（今回は１０００回毎に通過メッセージを出す様にしました）する仕組みを追加して終了です。

データの生成

作成したスクリプトを走らせると、メモリの上にかなり大きなデータが生成されます。（流石に100万行はかなり時間が掛かりますので、途中経過を時々確認しながら別の作業を進めておいてください（苦笑））

この画像で確認出来る、BIG_DATA_Tableと、負荷用にもう一つ作成したBIG_DATA_R_Tableがインメモリ展開されるテーブルになります（約４６０MB）。また、ここではカラムストア側にも１００万行のデータテーブルが生成されていますが、これはこのあと解説をしたいと思います。

SQLで遊んでみる・・

では、取り急ぎ定番のSQLを仕掛けてみます。

select count(*)
from BIG_DATA_Table;

これは、カウント機能を使って行数を検出する定番SQLですが、今回のテストでは25msという結果が出てきました。この辺は、シンプルな処理になるかと思いますので、比較の対象には難しい側面も有りますが、取りあえす100万行出来たか？の確認作業になります。

次に、少し負荷の高い処理を仕掛けてみます。

select * from BIG_DATA_Table
where Logistics ='関東中央物流センター';

このSQLは、100万行の中から全国８箇所（各地方に1個仮想設置）の配送センターで、関東エリア（１都６県）で発生した出荷処理を抽出し、その結果全てを出してくる形になります。この結果は先ほどと同様に52msで処理が行われ、SQLクライアント上に大量の抽出結果が表示されました。

さらに集計系の処理を仕掛けてみます。

select SUM(Units) as Sum_of_Units, AVG(Payment) as Avarage_of_Payment, Category
from BIG_DATA_Table
where Category in ('家電','酒類','雑貨') and Prefecture in ('大阪府','東京都','福岡県')
group by Category;

SQL自体は少し複雑化しますが、おそらく一般的に良く使われる形式だと思います。設定された商材全て（家電、酒類、雑貨）と、その注文が発生した都道府県（大阪府、東京都、福岡県）の条件に該当するデータを抽出し、それらの総計と平均を計算してSQLクライアントに表示します。

爆速インメモリのSingleStoreですが、流石に今回最長時間の240msで処理が完了しました。

今回のまとめ

今回は、前回導入した最新版のSingleStore(旧MemSQLの進化版）を使って、100万行のインメモリデータ生成と、出来上がったテーブルに対して、幾つかの典型的なSQLを仕掛けてみました。
動作的には、以前のMemSQL同様にSQL自体を高速コンパイルして、その結果を活用する仕組みが動きますので（メモリ空間のアクセスに最適化したコンパイル済みのSQLで二回目以降アクセスします）、パラメータを変える系の類似処理であれば、二回目以降の処理時間がさらに高速化する可能性が有ります。（今回の検証でも、二回目以降半分位に時短したケースも有ります）

空前のBIブームで、「前向きに行き詰まっている」ケースの多くが「処理性能問題」であるとすれば、単純にこのSingleStoreのインメモリ性能を活用することで、シンプルに解決させる事が可能かもしれません。（じゃあ、どうやって既存の電子帳簿データシステムからデータを持ってくる？という疑問については、これも以前にご紹介しているEqualumなる即時ストリーミングをプログラムレスで実現！ソリューションが有りますので、これを既存系とSingleStoreの間におけば、極めてこれもシンプルに解決できるでしょう・・）

さて次回は・・・・

次回は、今回導入したSingleStore上の100万行のインメモリテーブルを活用し、サクッと100万行のカラムストアを作成して、今回同様に簡単なSQLを流して性能評価を行ってみたいと思います。

謝辞

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