EMRのSparkやHiveでサブクエリが使えるか試してみた_その2

EMRのSparkやHiveでサブクエリが使えるか試してみたメモ。その2。

前回、Hiveでは一部のサブクエリが実行できなかった。今回は、Existを利用したサブクエリが使えるかを確かめたメモ。
メタデータカタログはGlueデータカタログを使用。

確認環境
元ネタ
実行対象のSQL
spark-sql -> いけた
hive -> いけた
参考URL

確認環境

[hadoop@ip-192-168-2-187 ~]$ spark-sql --version
Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /___/ .__/\_,_/_/ /_/\_\   version 2.4.5-amzn-0
      /_/

[hadoop@ip-192-168-2-187 ~]$ hive --version
Hive 2.3.6-amzn-2

元ネタ

spark-sql> select * from fruit_order;
|order_date|category_name|order_quantity|
|2020-02-03|    バナナ|             7|
|2020-02-01|    ぶどう|             2|
|2020-02-01|    ぶどう|             3|
|2020-02-01|    りんご|             5|
|2020-02-02|    りんご|             1|
|2020-02-02|    りんご|             3|
|2020-02-02|    ぶどう|             4|
|2020-02-02|    みかん|             2|

実行対象のSQL

/* fruit_orderテーブルで日毎、フルーツごとに発注量最大のものを抽出 */
select
    *
from
    testdb.fruit_order t1
where
    not exists (
    select
        *
    from
        testdb.fruit_order f2
    where
        t1.order_date = f2.order_date
        and t1.category_name = f2.category_name
        and t1.order_quantity < f2.order_quantity )
;

/* 上述のテーブルの場合の期待値は以下 */
|2020-02-01|    ぶどう|             3|
|2020-02-01|    りんご|             5|
|2020-02-02|    りんご|             3|
|2020-02-02|    ぶどう|             4|
|2020-02-02|    みかん|             2|
|2020-02-03|    バナナ|             7|

spark-sql -> いけた

|2020-02-03      |バナナ  |7|
|2020-02-01      |ぶどう  |3|
|2020-02-01      |りんご  |5|
|2020-02-02      |りんご  |3|
|2020-02-02      |ぶどう  |4|
|2020-02-02      |みかん  |2|

hive -> いけた

|2020-02-01      |ぶどう  |3|
|2020-02-01      |りんご  |5|
|2020-02-02      |りんご  |3|
|2020-02-02      |ぶどう  |4|
|2020-02-02      |みかん  |2|
|2020-02-03      |バナナ  |7|