AWS Glue Data Catalog のクエリ - Amazon Athena

AWS Glue Data Catalog のクエリ

AWS Glue Data Catalog は多くの AWS のサービス で中央メタデータリポジトリとして使用されるため、データカタログメタデータのクエリが必要になる場合があります。これを実行するには、Athena で SQL クエリを使用できます。Athena を使用して、データベース、テーブル、パーティション、および列などの AWS Glue カタログメタデータをクエリできます。

AWS Glue カタログのメタデータを取得するには、Athena バックエンドで information_schema データベースをクエリします。このトピックのクエリ例は、一般的なユースケースのために Athena を使用して AWS Glue カタログメタデータをクエリする方法を示しています。

考慮事項と制約事項

  • information_schema データベースをクエリする代わりに、個別の Apache Hive DDL コマンドを使用して、Athena から特定のデータベース、テーブル、ビュー、パーティション、および列のメタデータ情報を抽出することができます。ただし、出力は表形式になりません。

  • information_schema のクエリのパフォーマンスは、AWS Glue メタデータの量が少ない、または中程度である場合に最も高くなります。大量のメタデータがある場合は、エラーが発生する可能性があります。

  • CREATE VIEW を使用して information_schema データベース上にビューを作成することはできません。

データベースのリスト化と指定したデータベースの検索

このセクションの例では、メタデータ内のデータベースをスキーマ名別にリストする方法を示します。

例 – データベースのリスト化

次のクエリ例は、information_schema.schemata テーブルのデータベースを一覧表示します。

SELECT schema_name FROM information_schema.schemata LIMIT 10;

次の表に項目の例を示します。

6 alb-databas1
7 alb_original_cust
8 alblogsdatabase
9 athena_db_test
10 athena_ddl_db

例 – 指定したデータベースの検索

次のクエリの例では、rdspostgresql はサンプルデータベースです。

SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'rdspostgresql'

次の表に項目の例を示します。

schema_name
1 rdspostgresql

指定したデータベース内のテーブルのリスト化と名前によるテーブルの検索

テーブルのメタデータを一覧表示するには、テーブルスキーマまたはテーブル名でクエリを実行します。

例 – テーブルのスキーマ別のリスト化

次のクエリは、rdspostgresql テーブルスキーマを使用するテーブルを一覧表示します。

SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema = 'rdspostgresql'

次の表は、サンプル結果を示しています。

table_schema table_name table_type
1 rdspostgresql rdspostgresqldb1_public_account BASE TABLE

例 – 名前によるテーブルの検索

次のクエリは、テーブル athena1 のメタデータ情報を取得します。

SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_name = 'athena1'

次の表は、サンプル結果を示しています。

table_schema table_name table_type
1 default athena1 BASE TABLE

特定のテーブルのパーティションのリスト化

以下の例のように、SHOW PARTITIONS table_name を使用して、指定したテーブルのパーティションをリスト化できます。

SHOW PARTITIONS cloudtrail_logs_test2

また、メタデータクエリを使用して、特定のテーブルのパーティション番号とパーティション値をリスト化することも可能です。使用する構文は、Athena エンジンのバージョンに応じて異なります。

例 – Athena エンジンバージョン 2 でのテーブルのパーティションのクエリ

以下の例のクエリでは、Athena エンジンバージョン 2 を使用して、テーブル cloudtrail_logs_test2 のパーティションをリスト化しています。

SELECT * FROM default."cloudtrail_logs_test2$partitions" ORDER BY partition_number

次の表に項目の例を示します。

table_catalog table_schema table_name
1 awsdatacatalog デフォルト cloudtrail_logs_test2 2020 08 10
2 awsdatacatalog デフォルト cloudtrail_logs_test2 2020 08 11
3 awsdatacatalog デフォルト cloudtrail_logs_test2 2020 08 12

例 – Athena エンジンバージョン 1 でのテーブルのパーティションのクエリ

以下の例のクエリでは、Athena エンジンバージョン 1 を使用して、テーブル cloudtrail_logs_test2 のパーティションをリスト化しています。

SELECT * FROM information_schema.__internal_partitions__ WHERE table_schema = 'default' AND table_name = 'cloudtrail_logs_test2' ORDER BY partition_number

次の表に項目の例を示します。

table_catalog table_schema table_name partition_number partition_key partition_value
1 awsdatacatalog デフォルト cloudtrail_logs_test2 1 year 2018
2 awsdatacatalog デフォルト cloudtrail_logs_test2 1 か月 09
3 awsdatacatalog デフォルト cloudtrail_logs_test2 1 30

指定したテーブルまたはビューの列のリスト化または検索

指定したデータベースとテーブルで、テーブルのすべての列、ビューのすべての列を一覧表示したり、名前で列を検索したりできます。

列を一覧表示するには、SELECT * クエリを使用します。FROM 句で、information_schema.columns を指定します。WHERE 句で、table_schema='database_name' を使用してデータベースを指定し、table_name = 'table_name' を使用して一覧表示する列を含むテーブルまたはビューを指定します。

例 – 指定したテーブルのすべての列のリスト化

次のクエリ例は、テーブル rdspostgresqldb1_public_account のすべての列を一覧表示します。

SELECT * FROM information_schema.columns WHERE table_schema = 'rdspostgresql' AND table_name = 'rdspostgresqldb1_public_account'

次の表に項目の例を示します。

table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type [Comment] (コメント) extra_info
1 awsdatacatalog rdspostgresql rdspostgresqldb1_public_account password 1 はい varchar
2 awsdatacatalog rdspostgresql rdspostgresqldb1_public_account user_id 2 はい integer
3 awsdatacatalog rdspostgresql rdspostgresqldb1_public_account created_on 3 はい timestamp
4 awsdatacatalog rdspostgresql rdspostgresqldb1_public_account last_login 4 はい timestamp
5 awsdatacatalog rdspostgresql rdspostgresqldb1_public_account email 5 はい varchar
6 awsdatacatalog rdspostgresql rdspostgresqldb1_public_account username 6 はい varchar

例 – 指定したビューの列のリスト化

次のクエリ例は、ビュー defaultarrayview データベース内のすべての列を一覧表示します。

SELECT * FROM information_schema.columns WHERE table_schema = 'default' AND table_name = 'arrayview'

次の表に項目の例を示します。

table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type [Comment] (コメント) extra_info
1 awsdatacatalog デフォルト arrayview searchdate 1 はい varchar
2 awsdatacatalog デフォルト arrayview sid 2 はい varchar
3 awsdatacatalog デフォルト arrayview btid 3 はい varchar
4 awsdatacatalog デフォルト arrayview p 4 はい varchar
5 awsdatacatalog デフォルト arrayview infantprice 5 はい varchar
6 awsdatacatalog デフォルト arrayview sump 6 はい varchar
7 awsdatacatalog デフォルト arrayview journeymaparray 7 はい array(varchar)

例 – 指定したデータベースとテーブルでの名前による列の検索

次のクエリ例は、sid データベースの arrayview ビューで default 列のメタデータを検索します。

SELECT * FROM information_schema.columns WHERE table_schema = 'default' AND table_name = 'arrayview' AND column_name='sid'

次の表は、サンプル結果を示しています。

table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type [Comment] (コメント) extra_info
1 awsdatacatalog デフォルト arrayview sid 2 はい varchar