Graph in SQL Server on Linuxで100万件データを試してみる

f:id:ninna2:20170504184530p:plain:w360

SQL Server 2017ではGraph形式のデータが扱えるようになりました。Graphデータがどのようなものかは、前回の投稿でやってみましたが、データ件数がそれほど多くなかったので、少し多めのデータ(とりあえず100万件)でどうなるのか試してみたいと思います。

前回の記事は下記です。Microsoftのサンプルで一通り試してみたので参考にしてください。

ninna2.hatenablog.com

環境

私がお試しでいろいろやっている環境は、Azure Virtual Machine上のRedhat Enterpraise Linuxに構築したSQL Server Linuxです。Azure Virtual Machineは、Standard DS2 v2 (2コア、7GBメモリ)です。SQL Server on Linuxの機能検証に最低限必要なリソースで構成しています。Redhat Enterpraise Linuxは、7.3です。これは、AzureでSQL Server on Linuxを構築する際に選択の余地がなかったので、こうなってます。

SQL Server on Linuxの構築方法も過去記事にあるので、良ければ参照ください。

ninna2.hatenablog.com

テストデータ準備

Graphデータとして作成するのは友達関係を表すデータモデルです。Personデータ(Node)とfriendOfデータ(Edge)です。Microsoftのサンプルでも出ていたデータ定義を流用して使っていきます。大量のテストデータを準備しないといけないのでそこからやっていきます。テストデータって結構作成するの面倒だったりするのでいろいろ調べて参考にさせてもらいました。

qiita.com

とりあえず100万件のIDデータの作成をします。chiseiさん参考にさせてもらってます。

-- ベースの10件の作成
CREATE TABLE digit(num integer);
INSERT INTO digit VALUES
(0),
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9);
GO

CREATE TABLE digit2(num integer);

--直積で大量データ生成
INSERT INTO digit2(num) 
SELECT 
    d1.num + d2.num*10 + d3.num*100 + d4.num*1000 + d5.num*10000 + d6.num*100000 as num
FROM 
    digit d1, 
    digit d2, 
    digit d3, 
    digit d4, 
    digit d5, 
    digit d6;
GO

この基礎データをもとにPersonデータをINSERT～SELECTで作成します。Personテーブルは、Nodeになるので、AS NODEを忘れずに。

CREATE TABLE Person (
  ID INTEGER PRIMARY KEY, 
  name VARCHAR(100)
) AS NODE;
GO

--直積データから作成
INSERT INTO Person(ID, name)
SELECT num, CONCAT('user',num) FROM digit2;
GO

Personデータは比較的簡単にできました。friendOfデータは、だれとだれが友達かというデータなので少しだけ手間です。仮に今回は、ある人に対して5人の友達がいることにしましょう。100万人登録したので、friendOfデータは500万ということになります。500万レコード作成していきます。

まず、組み合わせをたくさん作っていきましょう。なんでもよいので中間テーブルを作成して100万件ずつ生成しています。INSERT～SELECT文は5回実行してください。ランダムな人を5回組み合わせています。

CREATE TABLE tempData(fromID integer,toID integer);
GO

--5回実行
INSERT INTO tempData(fromID,toID) 
SELECT 
    num,
    ROW_NUMBER() OVER(ORDER BY NEWID()) AS toID
FROM 
    digit2
ORDER BY
    num;
GO

500万レコードできたのでこれを元にfriendOfデータを作成していきます。Edgeデータは、from_nodeとto_nodeの2つの$node_idを指定しないといけないのでこのような面倒な方法になりました。5分ぐらいデータ投入に時間がかかります。

CREATE TABLE friendOf AS EDGE;
GO

INSERT INTO friendOf 
SELECT
    person1.$node_id,
    person2.$node_id
FROM 
    tempData 
    INNER JOIN Person person1
    on tempData.fromID = person1.ID
    INNER JOIN Person person2
    on tempData.toID = person2.ID
ORDER BY
    tempData.fromID;
GO

これで、Person(Node)に100万件、friendOf(EDGE)に500万件できました。

検索してみる

さて、大量データができたので検索やってみよう。1人につき5人友達がいることになるので10人先(友達の友達の…)で100万以上を超えます(まぁ本来は重複があるのですが)。ですので、9人先までたどってみます。今回、5人しか友達いない設定で10回聞いて回ると100万人超えるって、現実世界で10回聞いて回ると考えるとすごいことになりそうですよね。

SELECT 
    DISTINCT person2.name 
FROM 
    Person person1,
    friendOf friendOf1,
    Person person2
WHERE 
    MATCH(
        person1-(friendOf1)->person2
        )
    and person1.name = 'user100'
ORDER BY
    person2.name;
GO

これをどんどん拡張して行って、9階層までやってみます。途中割愛します。9回目は下記です。

SELECT 
    DISTINCT person9.name 
FROM 
    Person person1,
    friendOf friendOf1,
    Person person2,
    friendOf friendOf2,
    Person person3,
    friendOf friendOf3,
    Person person4,
    friendOf friendOf4,
    Person person5, 
    friendOf friendOf5,
    Person person6, 
    friendOf friendOf6,
    Person person7, 
    friendOf friendOf7,
    Person person8, 
    friendOf friendOf8,
    Person person9 
WHERE 
    MATCH(
        person1-(friendOf1)->person2
        -(friendOf2)->person3
        -(friendOf3)->person4
        -(friendOf4)->person5
        -(friendOf5)->person6
        -(friendOf6)->person7
        -(friendOf7)->person8
        -(friendOf8)->person9
        )
    and person1.name = 'user100'
ORDER BY
    person9.name;
GO

f:id:ninna2:20170507023225j:plain