Trinoの動きを見ていく(1)

2023年3月16日 17:42

はじめに

LaKeel Data Solution Group の小西です。私のグループで担当している LaKeel Data Insight という製品では、大量データを扱う関係もあり、データベースとの接続に Trino という高速クエリエンジンを利用しています。

そのTrinoについて、良く知っておきたいということもあり、実際にどういう動きをしてくれているのか、なぜ早いのか、を調べていきたくなり、興味の向くままに調べていった内容を、少しずつでもブログに残していこうと始めてみます。
よろしくお願いします。

Trinoとは

上の画像のリンクから公式サイトに飛べます。
書かれているキャッチフレーズはGoogle翻訳だと「Trino は、とんでもない速度で実行されるクエリエンジンです。」となりますが、ludicrousという単語の訳を見ると「ばかげた」と出てくるので、そういうニュアンスの「とんでもない」速度ということのようです。
マスコットキャラクターはうさぎです。

Googleで、「Trino vs 」まで入力するといろんなデータベースやクエリエンジンが出てきて、速度だけで見ると他にもっと早い成績が出てるものもあるので一番ではないのですが、使い勝手のよさと対応しているデータベースの豊富さで、とても優れているクエリエンジンだと思います。

何より元のデータベースはそのまま変わらず利用を継続できるという点が良いです。便利です。

Trinoの振る舞い

さてそのTrinoですが、これはデータベースではなく、データベースの手前に登場してがんばってくれるミドルウェアです。
例えば通常のデータベースへのクエリは以下のような流れになりますが、

これが、
Trinoを利用すると大まかには以下のような流れになります。

ちなみに、LaKeel Data InsightではほぼSELECTとINSERTのためにしか使っていませんが、その他の更新系を含む標準的なSQLにも広く対応しています。（接続先のデータベースによって対応していない構文もあります）

では、動きを見ていくために以下のような簡単なテーブルをMySQL上に用意して、これをTrinoでSELECTしてみます。

まずは、以下の簡単なSELECT文をTrinoで実行してみました。
Trinoの場合、テーブルの指定の書き方は [カタログ名].[スキーマ名].[テーブル名] となります。

# Trino から mysqlカタログを指定して実行するSELECT文

SELECT
    *
FROM
    mysql.demo1.emp_info
ORDER BY
    emp_id DESC
LIMIT 5

カタログというのはTrino側で定義するもので、接続するデータベースへの接続情報等をまとめた設定情報のかたまりです。

そしてTrinoでの結果表示はこうなりました。（当たり前ですが正しい結果です。）

 emp_id | emp_name | dept_id  |   sdate    |   edate    | lastupdate
--------+----------+----------+------------+------------+------------
 EMP005 | 緒方     | DEPT0002 | 2016-04-01 | 2999-12-31 | 2016-03-31
 EMP004 | 榎本     | DEPT0002 | 2006-04-01 | 2010-03-31 | 2010-01-31
 EMP004 | 榎本     | DEPT0002 | 2002-04-01 | 2006-03-31 | 2005-12-31
 EMP004 | 榎本     | DEPT0002 | 2010-04-01 | 2999-12-31 | 2010-01-31
 EMP003 | 宇野     | DEPT0002 | 2002-04-01 | 2999-12-31 | 2002-03-31
(5 rows)

ここで先に結論というか、TrinoからMySQLのemp_infoテーブルに向けて送られたクエリはどうだったかというと、このようなクエリになっていました。

SELECT 
    `emp_id`,
    `emp_name`,
    `dept_id`,
    `sdate`,
    `edate`,
    `lastupdate`
FROM
    `demo1`.`emp_info`

SELECT句の * がそれぞれカラム名指定に置き換えられてますが、さらにorder by句とlimit句が無くなっています。

また、このTrinoに対して送ったSELECT文が上記のorder by句とlimit句の無いSELECT文になってMySQLに送られる前に（というか間に）、別の10を超える数のクエリ（と要求）がTrinoからMySQLに向かって送られていることがわかりました。

以下長いですが、実際に投げられたクエリとそのクエリの簡単な内容を貼り付けていきます。（クエリ部分は適宜改行等を入れて整形してます）

# その1. MySQLの様々な設定内容を取得
SELECT
    @@session.auto_increment_increment AS auto_increment_increment,
    @@character_set_client AS character_set_client,
    @@character_set_connection AS character_set_connection,
    @@character_set_results AS character_set_results,
    @@character_set_server AS character_set_server,
    @@collation_server AS collation_server,
    @@collation_connection AS collation_connection,
    @@init_connect AS init_connect,
    @@interactive_timeout AS interactive_timeout,
    @@license AS license,
    @@lower_case_table_names AS lower_case_table_names,
    @@max_allowed_packet AS max_allowed_packet,
    @@net_write_timeout AS net_write_timeout,
    @@performance_schema AS performance_schema,
    @@query_cache_size AS query_cache_size,
    @@query_cache_type AS query_cache_type,
    @@sql_mode AS sql_mode,
    @@system_time_zone AS system_time_zone,
    @@time_zone AS time_zone,
    @@transaction_isolation AS transaction_isolation,
    @@wait_timeout AS wait_timeout

# その2. 直前の警告・エラーの内容を取得
SHOW WARNINGS

# その3. 結果の文字コード変換を無効化
SET character_set_results = NULL

# その4. 自動コミットを有効化
SET autocommit=1

# その5. スキーマ一覧を取得
SHOW DATABASES

# その6. 指定スキーマにあるテーブルの情報を取得
SELECT 
    TABLE_SCHEMA AS TABLE_CAT,
    NULL AS TABLE_SCHEM,
    TABLE_NAME,
    CASE
        WHEN
            TABLE_TYPE = 'BASE TABLE'
        THEN
            CASE
                WHEN
                    TABLE_SCHEMA = 'mysql'
                        OR TABLE_SCHEMA = 'performance_schema'
                THEN
                    'SYSTEM TABLE'
                ELSE 'TABLE'
            END
        WHEN TABLE_TYPE = 'TEMPORARY' THEN 'LOCAL_TEMPORARY'
        ELSE TABLE_TYPE
    END AS TABLE_TYPE,
    TABLE_COMMENT AS REMARKS,
    NULL AS TYPE_CAT,
    NULL AS TYPE_SCHEM,
    NULL AS TYPE_NAME,
    NULL AS SELF_REFERENCING_COL_NAME,
    NULL AS REF_GENERATION
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'demo1'
HAVING TABLE_TYPE IN ('TABLE' , 'VIEW', NULL, NULL, NULL)
ORDER BY TABLE_TYPE , TABLE_SCHEMA , TABLE_NAME

# その7. 指定スキーマの、指定テーブルの情報を取得（その6.のSQLとほぼ同じ）
SELECT 
    TABLE_SCHEMA AS TABLE_CAT,
    NULL AS TABLE_SCHEM,
    TABLE_NAME,
    CASE
        WHEN
            TABLE_TYPE = 'BASE TABLE'
        THEN
            CASE
                WHEN
                    TABLE_SCHEMA = 'mysql'
                        OR TABLE_SCHEMA = 'performance_schema'
                THEN
                    'SYSTEM TABLE'
                ELSE 'TABLE'
            END
        WHEN TABLE_TYPE = 'TEMPORARY' THEN 'LOCAL_TEMPORARY'
        ELSE TABLE_TYPE
    END AS TABLE_TYPE,
    TABLE_COMMENT AS REMARKS,
    NULL AS TYPE_CAT,
    NULL AS TYPE_SCHEM,
    NULL AS TYPE_NAME,
    NULL AS SELF_REFERENCING_COL_NAME,
    NULL AS REF_GENERATION
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'demo1'
        AND TABLE_NAME LIKE 'emp\_info'
HAVING TABLE_TYPE IN ('TABLE' , 'VIEW', NULL, NULL, NULL)
ORDER BY TABLE_TYPE , TABLE_SCHEMA , TABLE_NAME

ここで一度セッションを終了させ新しいセッションに切り替えています。
（Quit → Connect）
そしてまたTrinoからMySQLへのクエリが続きます。

# その1.～4.まで、さきほどと同じSQLを再度実行している
SELECT（以下略。その1.と同じ）
SHOW WARNINGS（その2.と同じ）
SET character_set_results = NULL（その3.と同じ）
SET autocommit=1（その4.と同じ）

# その8. 対象のテーブルのカラム情報を取得
#        その際に情報をtrinoで扱うための形式に変換
SELECT 
    TABLE_SCHEMA,
    NULL,
    TABLE_NAME,
    COLUMN_NAME,
    CASE
        WHEN UPPER(DATA_TYPE) = 'DECIMAL' THEN 3
        WHEN UPPER(DATA_TYPE) = 'DECIMAL UNSIGNED' THEN 3
        WHEN UPPER(DATA_TYPE) = 'TINYINT' THEN - 6
        WHEN UPPER(DATA_TYPE) = 'TINYINT UNSIGNED' THEN - 6
        WHEN UPPER(DATA_TYPE) = 'BOOLEAN' THEN 16
        WHEN UPPER(DATA_TYPE) = 'SMALLINT' THEN 5
        WHEN UPPER(DATA_TYPE) = 'SMALLINT UNSIGNED' THEN 5
        WHEN UPPER(DATA_TYPE) = 'INT' THEN 4
        WHEN UPPER(DATA_TYPE) = 'INT UNSIGNED' THEN 4
        WHEN UPPER(DATA_TYPE) = 'FLOAT' THEN 7
        WHEN UPPER(DATA_TYPE) = 'FLOAT UNSIGNED' THEN 7
        WHEN UPPER(DATA_TYPE) = 'DOUBLE' THEN 8
        WHEN UPPER(DATA_TYPE) = 'DOUBLE UNSIGNED' THEN 8
        WHEN UPPER(DATA_TYPE) = 'NULL' THEN 0
        WHEN UPPER(DATA_TYPE) = 'TIMESTAMP' THEN 93
        WHEN UPPER(DATA_TYPE) = 'BIGINT' THEN - 5
        WHEN UPPER(DATA_TYPE) = 'BIGINT UNSIGNED' THEN - 5
        WHEN UPPER(DATA_TYPE) = 'MEDIUMINT' THEN 4
        WHEN UPPER(DATA_TYPE) = 'MEDIUMINT UNSIGNED' THEN 4
        WHEN UPPER(DATA_TYPE) = 'DATE' THEN 91
        WHEN UPPER(DATA_TYPE) = 'TIME' THEN 92
        WHEN UPPER(DATA_TYPE) = 'DATETIME' THEN 93
        WHEN UPPER(DATA_TYPE) = 'YEAR' THEN 91
        WHEN UPPER(DATA_TYPE) = 'VARCHAR' THEN 12
        WHEN UPPER(DATA_TYPE) = 'VARBINARY' THEN - 3
        WHEN UPPER(DATA_TYPE) = 'BIT' THEN - 7
        WHEN UPPER(DATA_TYPE) = 'JSON' THEN - 1
        WHEN UPPER(DATA_TYPE) = 'ENUM' THEN 1
        WHEN UPPER(DATA_TYPE) = 'SET' THEN 1
        WHEN UPPER(DATA_TYPE) = 'TINYBLOB' THEN - 3
        WHEN UPPER(DATA_TYPE) = 'TINYTEXT' THEN 12
        WHEN UPPER(DATA_TYPE) = 'MEDIUMBLOB' THEN - 4
        WHEN UPPER(DATA_TYPE) = 'MEDIUMTEXT' THEN - 1
        WHEN UPPER(DATA_TYPE) = 'LONGBLOB' THEN - 4
        WHEN UPPER(DATA_TYPE) = 'LONGTEXT' THEN - 1
        WHEN UPPER(DATA_TYPE) = 'BLOB' THEN - 4
        WHEN UPPER(DATA_TYPE) = 'TEXT' THEN - 1
        WHEN UPPER(DATA_TYPE) = 'CHAR' THEN 1
        WHEN UPPER(DATA_TYPE) = 'BINARY' THEN - 2
        WHEN UPPER(DATA_TYPE) = 'GEOMETRY' THEN - 2
        WHEN UPPER(DATA_TYPE) = 'UNKNOWN' THEN 1111
        WHEN UPPER(DATA_TYPE) = 'POINT' THEN - 2
        WHEN UPPER(DATA_TYPE) = 'LINESTRING' THEN - 2
        WHEN UPPER(DATA_TYPE) = 'POLYGON' THEN - 2
        WHEN UPPER(DATA_TYPE) = 'MULTIPOINT' THEN - 2
        WHEN UPPER(DATA_TYPE) = 'MULTILINESTRING' THEN - 2
        WHEN UPPER(DATA_TYPE) = 'MULTIPOLYGON' THEN - 2
        WHEN UPPER(DATA_TYPE) = 'GEOMETRYCOLLECTION' THEN - 2
        WHEN UPPER(DATA_TYPE) = 'GEOMCOLLECTION' THEN - 2
        ELSE 1111
    END AS DATA_TYPE,
    UPPER(CASE
                WHEN
                    LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) != 0
                        AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0
                        AND LOCATE('SET', UPPER(DATA_TYPE)) <> 1
                        AND LOCATE('ENUM', UPPER(DATA_TYPE)) <> 1
                THEN
                    CONCAT(DATA_TYPE, ' UNSIGNED')
                WHEN UPPER(DATA_TYPE) = 'POINT' THEN 'GEOMETRY'
                WHEN UPPER(DATA_TYPE) = 'LINESTRING' THEN 'GEOMETRY'
                WHEN UPPER(DATA_TYPE) = 'POLYGON' THEN 'GEOMETRY'
                WHEN UPPER(DATA_TYPE) = 'MULTIPOINT' THEN 'GEOMETRY'
                WHEN UPPER(DATA_TYPE) = 'MULTILINESTRING' THEN 'GEOMETRY'
                WHEN UPPER(DATA_TYPE) = 'MULTIPOLYGON' THEN 'GEOMETRY'
                WHEN UPPER(DATA_TYPE) = 'GEOMETRYCOLLECTION' THEN 'GEOMETRY'
                WHEN UPPER(DATA_TYPE) = 'GEOMCOLLECTION' THEN 'GEOMETRY'
                ELSE UPPER(DATA_TYPE)
            END) AS TYPE_NAME,
    UPPER(CASE
                WHEN UPPER(DATA_TYPE) = 'DATE' THEN 10
                WHEN
                    UPPER(DATA_TYPE) = 'TIME'
                THEN
                    8 + (CASE
                        WHEN DATETIME_PRECISION > 0 THEN DATETIME_PRECISION + 1
                        ELSE DATETIME_PRECISION
                    END)
                WHEN
                    UPPER(DATA_TYPE) = 'DATETIME'
                        OR UPPER(DATA_TYPE) = 'TIMESTAMP'
                THEN
                    19 + (CASE
                        WHEN DATETIME_PRECISION > 0 THEN DATETIME_PRECISION + 1
                        ELSE DATETIME_PRECISION
                    END)
                WHEN UPPER(DATA_TYPE) = 'YEAR' THEN 4
                WHEN
                    UPPER(DATA_TYPE) = 'MEDIUMINT'
                        AND LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) != 0
                THEN
                    8
                WHEN UPPER(DATA_TYPE) = 'JSON' THEN 1073741824
                WHEN UPPER(DATA_TYPE) = 'GEOMETRY' THEN 65535
                WHEN UPPER(DATA_TYPE) = 'POINT' THEN 65535
                WHEN UPPER(DATA_TYPE) = 'LINESTRING' THEN 65535
                WHEN UPPER(DATA_TYPE) = 'POLYGON' THEN 65535
                WHEN UPPER(DATA_TYPE) = 'MULTIPOINT' THEN 65535
                WHEN UPPER(DATA_TYPE) = 'MULTILINESTRING' THEN 65535
                WHEN UPPER(DATA_TYPE) = 'MULTIPOLYGON' THEN 65535
                WHEN UPPER(DATA_TYPE) = 'GEOMETRYCOLLECTION' THEN 65535
                WHEN UPPER(DATA_TYPE) = 'GEOMCOLLECTION' THEN 65535
                WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION
                WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN 2147483647
                ELSE CHARACTER_MAXIMUM_LENGTH
            END) AS COLUMN_SIZE,
    65535 AS BUFFER_LENGTH,
    UPPER(CASE
                WHEN UPPER(DATA_TYPE) = 'DECIMAL' THEN NUMERIC_SCALE
                WHEN
                    UPPER(DATA_TYPE) = 'FLOAT'
                        OR UPPER(DATA_TYPE) = 'DOUBLE'
                THEN
                    CASE
                        WHEN NUMERIC_SCALE IS NULL THEN 0
                        ELSE NUMERIC_SCALE
                    END
                ELSE NULL
            END) AS DECIMAL_DIGITS,
    10 AS NUM_PREC_RADIX,
    CASE
        WHEN IS_NULLABLE = 'NO' THEN 0
        ELSE CASE
            WHEN IS_NULLABLE = 'YES' THEN 1
            ELSE 2
        END
    END AS NULLABLE,
    COLUMN_COMMENT AS REMARKS,
    COLUMN_DEFAULT AS COLUMN_DEF,
    0 AS SQL_DATA_TYPE,
    0 AS SQL_DATETIME_SUB,
    CASE
        WHEN CHARACTER_OCTET_LENGTH > 2147483647 THEN 2147483647
        ELSE CHARACTER_OCTET_LENGTH
    END AS CHAR_OCTET_LENGTH,
    ORDINAL_POSITION,
    IS_NULLABLE,
    NULL AS SCOPE_CATALOG,
    NULL AS SCOPE_SCHEMA,
    NULL AS SCOPE_TABLE,
    NULL AS SOURCE_DATA_TYPE,
    IF(EXTRA LIKE '%auto_increment%',
        'YES',
        'NO') AS IS_AUTOINCREMENT,
    IF(EXTRA LIKE '%GENERATED%',
        'YES',
        'NO') AS IS_GENERATEDCOLUMN
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'demo1'
        AND TABLE_NAME LIKE 'emp\_info'
ORDER BY TABLE_SCHEMA , TABLE_NAME , ORDINAL_POSITION

またここで一度セッションを終了させ新しいセッションに切り替えてます。
（Quit → Connect）
そしてさらにTrinoからMySQLへのクエリが続きます。

# その1.～4.まで、さきほどと同じSQLを再度実行している
SELECT（以下略。その1.と同じ）
SHOW WARNINGS（その2.と同じ）
SET character_set_results = NULL（その3.と同じ）
SET autocommit=1（その4.と同じ）

# その9. トランザクションのアクセスモードを読み取り専用に設定
set session transaction read only

# その10. 書き込み時の待機秒数の設定
SET net_write_timeout=600

# その11. 今回のクライアントから送られたクエリ用のデータ取得
SELECT 
    `emp_id`,
    `emp_name`,
    `dept_id`,
    `sdate`,
    `edate`,
    `lastupdate`
FROM
    `demo1`.`emp_info`

# その12. 元の設定に戻している（その10.で変えた値）
SET net_write_timeout=60

ここまでやって、Trinoは結果を返してくれました。
（MySQLの勉強にもなりそうなクエリがいろいろ送られていました。）

Trinoは、MySQLからorder by（並べ替え）の仕事を奪って、データだけをもらって、並べ替えはTrino側で行うという動きのようです。
Trinoはメモリ型のクエリエンジンなので、対象が数千万件とかだった場合には効果がありそうです。

ところでちなみに試しで、ただlimit句を指定しただけの

select * from mysql.demo1.emp_info limit 5;

を実行させてみたときにどうなるかを見たら、
limit句は消えずに

SELECT 
    `emp_id`,
    `emp_name`,
    `dept_id`,
    `sdate`,
    `edate`,
    `lastupdate`
FROM
    `demo1`.`emp_info`
LIMIT 5

というSELECT文がTrinoからMySQLに送られていました。
無条件にlimitを消しているわけではなく、ちゃんと頭良かったです。

多少想像も含めると、Trinoが間に入ってやってることはこんな感じ（下図）なのかも、ということがわかってきました。

いろいろ試したいですが、わりと長くなってしまっているので、今回は最後に結合（JOIN）の場合の動きを少しだけ確認して残りは次回以降にしようと思います。

SELECT 
    e.emp_id
    e.emp_name,
    d.dept_name
FROM
    demo1.emp_info e
JOIN
    demo1.dept_info d
    ON
        e.dept_id = d.dept_id
WHERE
    DATE('2023-03-01') BETWEEN e.sdate AND e.edate
        AND DATE('2023-03-01') BETWEEN d.sdate AND d.edate

上のemp_infoとdept_infoを結合した、よくある結合のSELECT文ですが、これをTrinoで実行したとき、TrinoからMySQLへ送られるクエリの中のデータ取得部分のみに注目すると、以下の2つのSQLに分かれて送られていました。

# Trino → MySQLへのSQL
# その1
SELECT 
    `dept_id`, `dept_name`
FROM
    `demo1`.`dept_info`
WHERE
    `sdate` <= '2023-03-01'
        AND `edate` >= '2023-03-01'


# Trino → MySQLへのSQL
# その2
SELECT 
    `emp_id`, `emp_name`, `dept_id`
FROM
    `demo1`.`emp_info`
WHERE
    `sdate` <= '2023-03-01'
        AND `edate` >= '2023-03-01'

MySQLからみると、単純な1つのテーブルに対するクエリが2つリクエストされているだけです。テーブルの結合をするSELECT文は、データベースに負荷がかかる部分ですが、この処理をデータベース側ではなくTrinoが引き受けるという形ですね。

ここまででわかったこと

結合が前述のような動きになるということは、今回は同じMySQL同士の結合で試しましたが、別のデータベース同士でも、Trinoから見たら処理は変わらず、むしろデータベース側の負荷が分散して早いかもしれないという期待が持てます。
昔々、マスタデータとトランデータのディスクは物理的に分けて配置、みたいな鉄則がありましたが、似たようなものが復活しそうです。

基本的にTrinoはデータベースをただのデータの入れものとして扱い、処理として重くなる部分をTrinoが引き受けて、そこを高速で処理するのが売り！という印象です。
今回の記事内では行っていませんが、実際に製品で利用しているうえで、大量データに対するクエリ処理は早いです。

ただ今回の記事のような、普通にMySQLでも早いものはMySQLを直接使う方が早いです。ここはやはり使いわけでしょうか。Trinoを利用しているLaKeel Data Insightでも、すべてのクエリをTrinoで処理しているわけではない状態です。
今後この部分はもっと詰めていきたいと思っていますが、今のところは仕方ないのかな、と思っている部分です。

まとめ

TrinoはSQLを処理しますが、データは持たずに別のデータベース（DBMS）からデータを持ってきて結果を返す。
異なるシステムのテーブルの結合が、JOIN文を書くだけでできる。
中の動きを追ってみると面白い（勉強にもなる）

本来の強みである、大量データの処理についての紹介が今回できていませんが、そのあたりは次回以降にしていきたいと思います。まずはTrinoって、どこで何をしているものか、の紹介でした。

LaKeel Data Insightでは、まとめの2番目の特徴である、異なるデータベースの結合が容易であることの恩恵により、データレイクとデータマートの結合等、データのロケーションを気にせず自由なデータ結合が可能な環境を提供できております。

製品のリンクも最後に貼っておきますので、
こちらもよろしくお願いします。

今回は以上となりますが、
次は結合と並列処理あたりについて、Trinoあり／なしの速度比較と合わせてメリットデメリットを追っていきたいと思ってます。

最後までお読みいただきありがとうございました。