【第4回】ビッグデータ分析のためのSQL基礎｜ブロックチェーンの仕組みとオンチェーンデータ分析

本連載では、ブロックチェーンの基本的な仕組みを解説しながら、オンチェーンデータを分析するための基本的な手法について、全8回で紹介します。

第4回となる今回は、オンチェーンデータ分析の手法としてSQLを用いることのメリットについて、SQLの背景にある概念や歴史などを交えながら解説していきます。

データ分析のためのSQL
オンチェーンデータのためのSQL実行基盤
次回予告

データ分析のためのSQL

SQLとは、もともとリレーショナルデータベースと呼ばれるデータベースシステムからデータを抽出したり、データを操作したりするための専用の言語でした。近年では、SQLの完成度と汎用性の高さから、データベース分野に留まらず、広くデータ分析の用途でもSQLが活用されています。

コード1. SQLのサンプルコード

CREATE TABLE sample (
  id INT,
  name TEXT,
  description TEXT
);

INSERT INTO sample VALUES (1, 'AAA', 'some text');
INSERT INTO sample VALUES (2, 'BBB', 'some text');

SELECT id, name
FROM sample
WHERE id <= 1
LIMIT 10;

ここで、単なるデータの集合とデータベースの違いについて補足しておきます。まず、「データ」という言葉の定義自体がさまざま存在しますが、ここでは「一定の形式（フォーマット）で整えられた事実や数値」といった意味で取り扱うこととします。例えば、ある企業に属する従業員のIDや氏名、生年月日などをCSV形式やJSON形式で保存したテキストファイルは、典型的なデータの一種です。

一方、データベースとは、こうしたデータの集合を体系的に構成し、データを簡単に検索したり、整合性を持って更新したりできるようにしたものを指します。無秩序なデータを単に寄せ集めたもので、目的のデータを簡単に抽出したり更新したりできないような状態になっているデータの集まりであれば、一般的にはデータベースとは呼ばれません。

なお、ビッグデータの文脈では、従来のデータベースで管理されているような体系的なデータの集合を「構造化データ」、それ以外の多種多様で雑多なデータ群を「非構造化データ」と呼ばれることもあります^※1。ICT技術の発展に伴い、この「非構造化データ」が急速に生成・保存されるようになったことで、ビッグデータの活用という概念が注目を集め始めました。この文脈に即して冒頭の説明を言い換えると、「SQLはもともと『構造化データ』を操作するために利用されていた言語だが、近年では『非構造化データ』に対してもSQLを適用してビッグデータ分析に活用する事例も増加している」とも表現できます。

※1 総務省平成25年版情報通信白書 – ビッグデータの概念

3層スキーマ

多くのデータベースシステムでは、物理的に保存されたデータのフォーマットとは別に、抽象的な概念としてのデータモデルを備えています。

米国国家規格協会（ANSI）の標準化計画要求委員会（SPARC）が提唱した「3層スキーマ」というモデルでは、データベースの構造を「外部スキーマ」「概念スキーマ」「内部スキーマ」という3つのスキーマに分けて定義しています。これは、データの物理的な保存形式である「内部スキーマ」と、データを加工して利活用するビューとしての「外部スキーマ」との間に、緩衝材となる抽象的な「概念スキーマ」の層を配置することで、データの独立性を保つための構造です。

3層スキーマの考え方を用いず、物理的なデータを直接プログラミング言語などで加工してデータ分析する手法も存在します。CSVファイルなどで保存されたデータをPythonなどで読み込み、直接加工するような方法です。

このとき、物理的なデータの格納方法を変えたいときに、同時にプログラム側の改修が必要となったり、逆にデータの表示形式を変えたいときに、物理的なデータの格納方法を変更しなければならなくなったりすることがあります。こうした状態はデータの独立性が存在しない状態と言えます。一人のデータ分析者がデータを取り扱うだけであればそれほど問題はないかもしれませんが、多くの分析者が同時に同じデータを扱ったり、分析者とは異なる人物がデータの保存方法をメンテナンスしていたりする場合には、3層スキーマのような形でデータの独立性を担保してあげたほうが良いでしょう。

こうしたデータの独立性を担保するために、SQLという言語は共通のインターフェースとして非常に都合が良かったため、データベース分野だけでなく、広くデータ分析のために活用されてきています。

リレーショナルモデル

データベースの種類には、オブジェクトデータベースやグラフデータベース、ドキュメントデータベースなど様々なものが存在しますが、SQLとともに広く普及しているデータベースの種類がリレーショナルデータベース（RDB）です。

さきほど説明した通り、多くのデータベースでは、物理的なデータを直接取り扱うことは少なく、抽象的なデータモデルを持っている場合があります。リレーショナルデータベースの場合は、リレーショナルモデル（関係モデル）と呼ばれる独自のデータモデルを持っており、そのモデルはSQLの思想にも強く反映されています。

リレーショナルモデルでは、データを取り扱うための基本的な構造を「リレーション（関係）」と呼びます。ただし、これは日常生活における「関係」や「リレーションシップ」という言葉から連想するものとはかなり異なる、数学的な独自用語であることに注意が必要です。

日常生活における「関係」という言葉は、「親子関係」や「因果関係」など、２つの項目に注目した場合が多いでしょう。例えば、「サザエさんとタラちゃんは親子関係である」や「風が吹けば桶屋が儲かるのは因果関係である」といった形です。

この「関係」という言葉を少し数学的に表現すると、「2つの集合A, Bに対して、直積A×Bの部分集合を、AとBの間の二項関係と呼ぶ」といった表現ができます。集合AやBを人物の集合とすると、直積A×Bというのは、その集合に含まれる人物同士のすべての組み合わせの集合となります。その人物同士の組み合わせには、親子関係である組み合わせもあれば、そうでない組み合わせも含まれるでしょう。このとき、親子関係にある組み合わせのみを取り出した集合を、数学的には「直積A×Bの部分集合」という表現をします。

リレーショナルモデルにおけるリレーション（関係）は、上記の数学的な二項関係の定義をN項関係に拡張し、「属性の定義域(ドメイン)D1, D2, D3, …, Dnに対して、直積D1×D2×D3×…×Dnの部分集合Rを関係と呼ぶ」と定義されます。

例えば、日本人のすべての名字を含む集合Aと、日本人のすべての名前を含む集合Bを、それぞれ名字の定義域、名前の定義域として直積A×Bを計算すると、A×Bにはすべての日本人の名字・名前の組み合わせと、存在しない架空の人物の名字・名前の組み合わせが含まれることになります。このとき、実際に存在する日本人の名字・名前の組み合わせを「日本人の名字・名前を示す関係」と呼ぶ、といった考え方が、リレーショナルモデルのデータの表現方法になります。

このリレーション（関係）を人間が理解しやすいように可視化する際に、よく属性列とデータ行とで構成された二次元テーブルの形式で表現することが多いため、リレーショナルモデル＝二次元テーブルだと考えてしまいがちです。しかし、CSVファイルやエクセルファイルで取り扱われることの多い二次元テーブルの表示形式と、抽象的な集合論に基づくリレーション（関係）とでは、見え方が似ていても厳密には異なるものなのだということを頭の片隅に置いていただければ、SQLの理解が深まるでしょう。

関係代数とSQL

リレーショナルモデルでは、上記のように定義したリレーション（関係）というデータモデルに対して、関係代数と呼ばれる演算を定義しています。これは、「整数」という型のデータに対して、足し算や引き算などの演算を定義しているようなイメージです。リレーションに関係代数を適用することで、リレーションの形を変形したり、新たなリレーションを生成したりすることができます。SQLという言語は、この関係代数を簡単に記述するための言語であり、PythonやJavaScriptのようなコンピュータへの命令を記述していく命令型言語よりは、抽象的な関数や述語論理に基づくHaskellやPrologのような宣言型言語に近いプログラミング言語です。

SQLや関係代数の重要な特徴として「閉包性」と呼ばれる性質があります。これは、リレーション（関係）に対して関係代数を適用した結果は、必ずリレーション（関係）になる、という性質です。例えば、整数に対して足し算や引き算を何度繰り返し適用したとしても、その結果は常に整数であり、突然文字列データになったりはしません。このことは、基本的な演算子を無数に組み合わせて複雑な計算を表現することができる点や、どれほど複雑な演算子の組み合わせを適用した計算結果であっても、それを別の演算子の入力として利用できる点などが保証されていることを意味します。

図3. 関係代数の閉包性（関係代数演算の出力は、別の関係代数演算の入力となれる）

したがって、SQLによるデータ加工やデータ分析のためのクエリ文は、非常に複雑な処理を記述できる柔軟性と、それを別のクエリと組み合わせることのできる可用性を担保することができます。この性質により、SQLはデータベースのみならず、ビッグデータ領域を含む幅広い領域でのデータ加工・データ分析用途のために活用され続けています。

標準SQL規格

SQLの言語仕様は、米国国家規格協会（ANSI）や国際標準化機構（ISO）によって標準化されており、異なるデータベースや分析システムであったとしても基本的に同じSQLの標準規格に準拠しており、SQL文の保守性や移植性の担保に貢献しています。ただし、システムによって標準規格への準拠状況が異なっていたり、独自の機能拡張が行われていたりすることも多いため、100%同じSQL文が他のシステムでも流用できるというケースは多くありません。それでも、独自フォーマットのファイルシステムを直接操作するプログラムを移植するよりは、遥かにデータの独立性を保った運用が可能です。

また、標準SQL規格は、古い規格で書かれたSQL文との後方互換性を維持しながら、データ分析用途で需要の高い構文も取り入れつつ進化を続けています。特に、SQL99で導入された共通表式（WITH句）や、SQL:2003で拡張されたウィンドウ関数などは、近年のデータ分析用途では必須の構文となります。詳しい構文の説明は本連載記事の次回以降でおこないますが、SQLを用いたデータ分析のための技術選定をする際には、これらの標準SQL規格への準拠度合いなども参考にしていただければと思います。

オンチェーンデータのためのSQL実行基盤

本連載の第2回や第3回記事において、ビットコインやイーサリアムのオンチェーンデータをSQLで分析できるオンラインサービスとして、Google BigQueryやDuneなどをご紹介しました。

特に、Duneというサービスは、ブロックチェーンのオンチェーンデータ分析に特化したスタートアップであり、これまで複数のSQL実行エンジンを利用しながらバージョンアップを繰り返してきました。ここでは、Duneが採用したSQL実行エンジンをサンプルとしながら、SQLを用いたデータ分析の利点や注意点などを解説します。

Dune

初期のDune v1では、オープンソースのリレーショナルデータベースマネジメントシステム（RDBMS）であるPostgreSQLを用いて、オンチェーンデータのオンライン分析サービスを提供していました。その後、データフォーマットを修正したDune v2が登場し、SparkSQLによるSQL実行環境が追加され、2023年8月現在ではDuneSQLという独自のSQLを用いたインターフェースに移行しています^※2。

ここで、Dune v1とv2の採用しているSQL実行エンジンの違いについて深掘りしてみましょう。PostgreSQLのようなRDBMSは、さまざまなアプリケーションのバックエンドとして、多数のトランザクションを同時に実行するOLTP(Online Transaction Processing)を想定して実装されています。

サンプルデータとして、日本の全人口1億人強の戸籍データベースを想像してみましょう。OLTPで想定するデータ処理としては、約1億人のデータベースから該当する人物のレコードを抽出し、詳細データを表示したり、一部のデータを更新したりする処理が考えられます。このとき、約1億人のデータをCSVなどのファイルベースで管理していると、検索のためのコストも高く、多数の更新トランザクションを同時に実行することも困難となります。このような用途を効率的に処理するためにRDBMSのようなデータベースが存在し、高速な検索のためのインデックス機能や、トランザクションの排他制御などの機能を提供しています。

一方、Dune v2で採用しているようなSparkSQLやDuneSQLなどのSQL実行エンジンは、OLTPではなくOLAP（Online Analytical Processing）と呼ばれるデータ処理を想定して設計されています。さきほどの約1億人の戸籍データベースを例にすると、全人物の平均年齢を計算するような処理がOLAPで想定しているデータ処理です。このようなデータ処理は、もちろんPostgreSQLのようなRDBMSでも可能ではありますが、必ずしも効率的ではありません。そのため、RDBMSとは異なる方向性で最適化したソリューションが登場してきました。OLAP用途のデータ処理で広く採用されているアーキテクチャとして、「列指向」と「分散処理」が挙げられます。

※2 Introducing Dune SQL

列指向アーキテクチャ

列指向とは、データの物理的な保持形式を、レコードのような行単位ではなく、属性値などの列単位とするアイデアです。代表的な実装として、Apache Parquetというオープンソースの列指向データファイル形式があります。

さきほどの戸籍データベースから全人物の平均年齢を計算するという処理の場合、あらかじめ各人物の年齢、もしくは生年月日を記録したデータだけを1箇所にまとめたデータフォーマットにしておいたほうが、ファイル読み込みの効率が良いでしょう。また、全人口が1億人強存在するデータベースであっても、年齢や生年月日といったデータのバリエーションは1億通りあるわけではなく、当然同じ年齢や同じ誕生年、誕生月の人物が大量に存在します。そのようなデータは一般的に圧縮効率がよいため、さらにデータの読み込み効率を上げることができます。Databricksの資料^※3によると、1 TBのCSVデータをParquet形式のデータに変換することで、データサイズが130 GBにまで削減でき、クエリの実行時間もCSVの場合に236秒だったものが、Parquetでは6.78秒にまで短縮されるというデータもあります。

※3 Databricks – Parquetとは

分散処理アーキテクチャ

大量のデータに対してOLAP型のデータ処理をおこなう場合、ボトルネックとなるのはデータ読み込み部分です。このボトルネックを解消するために、列指向と並んで用いられるアーキテクチャが分散処理です。例えば、読み込み性能が100MB/sのハードディスクに2TBのデータを保存した場合、そのデータすべて読み込むためには、単純計算で2万秒（約6時間）かかってしまいます。これを、100台のハードディスクにデータを100分割して同時に読み込むことができれば、読込み時間を100分の1に短縮できるのではないか、というのが分散処理の根源的なアイデアです。

ディスクIOを分散化するための代表的な分散ファイルシステムが、Apache Hadoopというプロジェクトのコア機能であるHDFS（Hadoop Distributed File System）です。このHDFS上のCSVファイルやParquetファイルに対してデータ処理をおこなう方法として、HadoopではMapReduceと呼ばれる分散処理のプログラミングモデルを提供していました。しかし、MapReduceモデルのプログラムをJavaなどのプログラミング言語で実装するのは、専門のスキルが必要であったため、そのハードルを下げるために、SQLのクエリ文を自動的にMapReduceのプログラムに変換するApache Hiveのようなプロジェクトが登場しました。さらにHiveの処理性能を向上させるソリューションとしてApache SparkのSparkSQLや、Trino（旧Presto）などが登場しました。Duneで採用しているDuneSQLは、このTrinoをベースにしています。

SQLの物理データ独立性

DuneのSQL実行エンジンが、PostgreSQLからSparkSQL、TrinoベースのDuneSQLに遷移していったように、データの増加やプロジェクトのフェーズ、技術の進歩といったさまざまな要因で、データの物理的な格納方法が劇的に変わることが想定されます。そのような物理層での変更があった場合でも、SQLをインターフェースとして分析ロジックを実装していれば、その影響は最小限に留めることができます。

もちろん、PostgreSQLやSparkSQL、DuneSQLの間でも、関数や予約語の微妙な違いや、対応している構文に差異があったりして、100%書き換えが必要ないというわけではありません。また、効率的なSQLを記載するためには、列指向や分散処理などの物理層のアーキテクチャを理解する必要がある場合もあります。しかし、物理層と分析ロジックが密に結合しているようなデータ分析環境は、多人数による共同管理や業務分担に支障をきたすことが多いでしょう。SQLを用いたデータ分析では、リレーショナルモデルをベースにした概念スキーマ層が暗黙的に存在し、物理層とアプリケーション層とのデータ独立性を保つことが容易となります。

ブロックチェーン上のオンチェーンデータや、その他ビッグデータの分析に興味を持たれた方は、ぜひ今後の連載を参考に、SQLを用いたビッグデータ分析に挑戦してみてください。