Zenn
🍊

TrinoのTABLEや列の情報などのメタデータを調べるためのクエリを調べた話

2024/10/20に公開
SQL
Trino
tech

はじめに

こんにちは。Rakutenでデータエンジニアをやっています。

弊チームではTrino(旧Presto)を使用しているのですが，チームで合計何個のTABLEやVIEWを保有しているのか，や，特定の列が含まれているTABLEは何か，というメタデータがほしい瞬間があります。

そのメタデータの出力についてのナレッジを備忘録として書きたいと思います。

https://trino.io/

環境

  • Mac Ventura 13.6
  • Trino 423

まとめると

具体的には2種類ある。INFORMATION_SCHEMAとSHOWステートメントである。

INFORMATION_SCHEMAという，メタデータを格納したテーブルがある。どうやらSQL標準に掲載されていて，Trinoだけでなく，MySQL，PostgreSQL，Google BigQuery，Microsoft SQL Server/Azure SQL Database，でこのテーブルが提供されているらしい。

ただし，標準に定められているからといって，その標準に記されたすべてのメタデータテーブルが提供されているというわけではないし，独自のメタデータテーブルが提供されていることもある。なので，自分の知りたいメタデータが使用中のデータベースに実装されていたらいいな，程度の情報源だと捉えると裏切られない。

また，TrinoやMySQLなどでは別で，SHOW ~というクエリが使えることがある。これによりテーブル一覧が取得できたり，TABLEのDDLが確認できたりする。

参考にしたリンクはこれら。

https://docs.pingcap.com/ja/tidb/stable/information-schema

https://en.wikipedia.org/wiki/Information_schema

実際に自分が使ったメタデータテーブルの列構成

ここでは，Trinoにおいて自分が使用していた3種のテーブルを紹介します。

  • information_schema.tables
  • information_schema.views
  • information_schema.columns

(個人的には，このテーブルが最近いつアクセスされたりINSERTされたりしたか，という情報もほしいのだが，見つかっていない。あったら教えてください。)

information_schema.tables

TABLEおよびVIEWの情報が格納されている。列構成は次のとおり。データテーブルが格納されているカタログ，スキーマ，そしてテーブル自体の名前，種類，である。table_typeがVIEWのものは文字通りTABLEでなくVIEWである。

  • table_catalog
  • table_schema
  • table_name
  • table_type
    • BASE TABLEもしくはVIEW。

information_schema.views

VIEWの情報が格納されている。列構成は次のとおりで，information_schema.tablesと大きく変わらない。view_definitionはVIEWのDDLが得られる。

  • table_catalog
  • table_schema
  • table_name
  • view_definition

information_schema.columns

TABLEおよびVIEWを構成するデータ列の情報が得られる。ordinal_positionはそのTABLE(VIEW)内の列の順番，column_defaultは列のデフォルト値，is_nullableはNULLを許容するかどうか，data_typeはデータ型である。

  • table_catalog
  • table_schema
  • table_name
  • column_name
  • ordinal_position
  • column_default
  • is_nullable
  • data_type

https://trino.io/docs/current/language/types.html

使用例

Trinoにおいては，カタログ，スキーマ，テーブルの順で細かくデータ格納場所が区切られていく。メタデータ出力時は全部必要な情報というわけではないであろうので，いい感じにWHERE句でフィルタすること。

<schema_name>など，<>で記載した部分は，任意の固有名詞で置き換えてください。

列を出力する

-- 特定のTABLEやVIEWに含まれている列をすべて出力する
-- SHOW COLUMNSとは違って，column_default, is_nullableが見られる

SELECT 
    *
FROM 
    information_schema.columns AS c 
WHERE 
    c.table_catalog = '<catalog_name>'
    AND c.table_schema = '<schema_name>'
    AND c.table_name = '<table_or_view_name>'

-- information_schemaとは違ってCommentも見られる。
-- data_typeはtypeという名前で出力される

SHOW COLUMNS FROM <catalog_name>.<schema_name>.<table_name>

TABLEやVIEWのデータ定義(DDL)を出力する

DDL，正式名称Data Definition Language。

-- TABLEのDDLを取得する
SHOW CREATE TABLE <catalog_name>.<schema_name>.<table_name>

-- VIEWのDDLを取得する
SHOW CREATE VIEW <catalog_name>.<schema_name>.<view_name>

TABLEやVIEWのリストを出力する

-- 指定のスキーマに格納されているTABLEとVIEWをリストアップする

SELECT
    *
FROM
    information_schema.tables AS t
WHERE
    t.table_catalog = '<catalog_name>'
    AND t.table_schema IN ('<schema_name_a>', '<schema_name_b>')

-- 指定のスキーマに格納されているTABLEとVIEWをリストアップする
-- SELECT table_name FROM information_schema.tables WHERE ~ と同義

SHOW TABLES FROM <catalog_name>.<schema_name>

TABLEやVIEWの数を数える

-- 指定のスキーマに格納されているTABLEとVIEWの数を数える
-- スキーマの指定にはINなども使える

SELECT
    table_type
    , COUNT(DISTINCT table_name)
FROM
    information_schema.tables AS t
WHERE
    t.table_catalog = '<catalog_name>'
    AND t.table_schema IN ('<schema_name_a>', '<schema_name_b>')
GROUP BY
    table_type

おわりに

初心者向けなのか分かりませんが将来役立ちますように。。。

余談

現在，弊社Rakutenではモバイルの社員紹介キャンペーンを実施しております。
下記リンクから，Rakuten会員でログインいただくと，回線変更で最大14,000ポイントがもらえるので，ご興味ある方はぜひアクセスしてみてください！

https://r10.to/hNCoik

Discussion