SQL, Pig, HiveそれぞれのEXISTS

仕事でSQLのEXISTSを書いたことがないwyukawaです。こんばんわ。

今日は以下の本を元ネタにEXISTSの話をしようと思います。SQL, Pig, Hiveだとどうなるかもあわせて書きたいと思います。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

まず論理学の話をしましょう。といっても僕も数時間調べただけですので自信は全くないです（汗。

まず言葉の定義です。

命題：真偽が定まる文。例：「香川はサッカー選手である」は真の命題ですね。

RDBMSの場合1行が1つの命題と見立てます。

name	age
鈴木	20
山田	30

「nameが鈴木でageが20」という命題ですね。

述語：値を受け取ってから真偽が定まる文。例：「xはサッカー選手である。」xが香川なら真、イチローなら偽ですね。

SQLの場合はWHERE句にくるやつが述語ですね。
WHERE句が真になる命題をselectするわけです。

述語には=とかBETWEENとかいろいろあるわけですが、EXISTSはこれらと異なり引数にスカラー値ではなく複数行を受け取ります。

次に述語論理には全称量化子と存在量化子という特別な述語が存在します。

前者は日本語で言えば「すべてのxが条件Pを満たす」で上記本ではと表現されています。ちなみにデータベース実践講義 ―エンジニアのためのリレーショナル理論 (THEORY/IN/PRACTICE)ではFORALL x ( p ( x ) )と表現されています。

後者は日本語で言えば「条件Pを満たすxが（少なくとも1つ）存在する」で上記本ではと表現されています。ちなみにデータベース実践講義 ―エンジニアのためのリレーショナル理論 (THEORY/IN/PRACTICE)ではEXISTS x ( p ( x ) )と表現されています。

SQLのEXISTSは存在量化子に対応します。なお全称量化子に対応するSQLはありません。
無くてもド・モルガンの法則から片方で代用できます。

（すべてのxが条件Pを満たす = 条件Pを満たさないxが存在しない）

（条件Pを満たすxが存在する = すべてのxが条件Pを満たさないわけではない）

ではSQLの具体例いきましょう。データは下記です。

student_id	subject	score
100	math	100
100	Japanese	80
100	science	80
200	math	80
200	Japanese	95
300	math	40
300	Japanese	90
300	society	55
400	math	80

ここで全ての教科が50点以上の生徒を選択してください。答えは100,200,400です。

「全ての〜」は全称量化子FORALLなので二重否定して存在量化子EXISTSにします。
つまり「50点未満の教科が一つも存在しない」です

データベース実践講義 ―エンジニアのためのリレーショナル理論 (THEORY/IN/PRACTICE)だと
FORALL x ( p ( x ) ) = NOT ( EXISTS x ( NOT ( p ( x ) ) ) )
という表現をしていますね。

SQLはこんな感じ

SELECT DISTINCT student_id FROM test ts1
WHERE NOT EXISTS
        (SELECT *
                FROM test ts2
                WHERE ts2.student_id = ts1.student_id
                AND ts2.score < 50);

ちなみにH2で動作確認してます。

$ cat TestScores.csv 
100,math,100
100,Japanese,80
100,science,80
200,math,80
200,Japanese,95
300,math,40
300,Japanese,90
300,society,55
400,math,80

というデータを以下のDDLで突っ込んでます。

CREATE TABLE test(student_id INT, subject VARCHAR(255), score INT)
        AS SELECT *
                 FROM CSVREAD('TestScores.csv', 'student_id,subject,score', 'charset=UTF-8 fieldSeparator=,');

では次Pig行ってみましょう。

Pigの場合はEXISTSは存在しませんのでnested blockと集合が空かどうか判定するIS_EMPTYで代用します。

データベース実践講義 ―エンジニアのためのリレーショナル理論 (THEORY/IN/PRACTICE)だと
FORALL x ( p ) = IS_EMPTY ( x WHERE NOT ( p ) )
と表現されているものを使うイメージです。

50点未満の教科の集合は空か否かを判定します。

Pigのクエリはこんな感じ。

test = LOAD 'TestScores.csv' USING PigStorage(',') AS(student_id:int,subject:chararray,score:int);

grp = GROUP test BY student_id;

f = FOREACH grp {

  fil = FILTER test BY score < 50;
  GENERATE group AS student_id, (IsEmpty(fil) ? 0 : 1) AS condition;

}

result_condition = FILTER f BY condition == 0;

result = FOREACH result_condition GENERATE student_id;

DUMP result;

条件判定の結果を一旦conditionカラムで受け取ってあとでFILTERします。
この書き方は別に僕が思いついたわけではなく教えてもらったんですが、ヘーって感じですね。

てかこのクエリを書ける人はそんなに多くない気がします。

PigはHiveに比べると独自言語なので学習コストが高いと思われますが、正直単純なPigクエリだったらそんなに学習コストは高くないと思います。

上記クエリを書けるようになるためにはPigの学習コストよりも集合指向的な考えが身に付いているか否かがポイントな気がします。なのでSQL強い人はPigのクエリをすんなり書けるようになると思います。Pigって手続き指向っぽく書けますが上記のようなクエリを書くためには集合指向的な考えが必要でしょうからね。

次にHive行ってみましょう。

Hiveの場合はEXISTSも無いしINの引数にサブクエリも書けないです。
なのでleft semi joinなのかなあと思って下記のように書きました。これはSQLとPigの例とは全く異なる書き方ですが、そうじゃない書き方あるかな。

select
	distinct test.student_id
from
	test
left semi join
	(select
		student_id
	from
		test
	group by
		student_id
	having
		min(score) >= 50
	)tab
on
	test.student_id = tab.student_id
;

ちなみにDDLとロードは以下のような感じです。

CREATE TABLE test (
student_id int,
subject string,
score int
)
ROW FORMAT DELIMITED
   FIELDS TERMINATED BY ','
;

LOAD DATA LOCAL INPATH 'TestScores.csv' INTO TABLE test;