横串検索の使用例

次の例では、横串検索の実行方法を示しています。Amazon Redshift データベースに接続されている SQL クライアントを使用して SQL を実行します。

PostgreSQL での横串検索の使用例

次の例は、Amazon Redshift データベース、Aurora PostgreSQL データベース、および Amazon S3 を参照するフェデレーションクエリを設定する方法を示しています。この例は、横串検索の機能を示しています。独自の環境で実行するには、環境に合わせて変更します。これを行うための前提条件については、「PostgreSQL への横串検索を使用した開始方法」を参照してください。

Aurora PostgreSQL データベースを参照する外部スキーマを作成します。

CREATE EXTERNAL SCHEMA apg
FROM POSTGRES
DATABASE 'database-1' SCHEMA 'myschema'
URI 'endpoint to aurora hostname'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

Amazon S3 を参照する別の外部スキーマを作成します。これは Amazon Redshift Spectrum を使用します。さらに、スキーマを使用するアクセス権を public に付与します。

CREATE EXTERNAL SCHEMA s3 
FROM DATA CATALOG 
DATABASE 'default' REGION 'us-west-2' 
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-S3'; 

GRANT USAGE ON SCHEMA s3 TO public;

Amazon Redshift テーブル内の行数を表示します。

SELECT count(*) FROM public.lineitem;
            
  count
----------
25075099

Aurora PostgreSQL テーブル内の行数を表示します。

SELECT count(*) FROM apg.lineitem;
            
count
-------
11760

Amazon S3 の行数を表示します。

SELECT count(*) FROM s3.lineitem_1t_part;
            
   count
------------
6144008876

Amazon Redshift、Aurora PostgreSQL、および Amazon S3 からテーブルのビューを作成します。このビューは、フェデレーテッドクエリの実行に使用されます。

CREATE VIEW lineitem_all AS
  SELECT l_orderkey,l_partkey,l_suppkey,l_linenumber,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag,l_linestatus,
         l_shipdate::date,l_commitdate::date,l_receiptdate::date, l_shipinstruct ,l_shipmode,l_comment 
  FROM s3.lineitem_1t_part 
  UNION ALL SELECT * FROM public.lineitem 
  UNION ALL SELECT * FROM apg.lineitem 
     with no schema binding;

ビュー lineitem_all 内の行数を述語とともに表示し、結果を制限します。

SELECT count(*) from lineitem_all WHERE l_quantity = 10;
               
   count
-----------
123373836

ある商品の毎年 1 月の売上高がいくらかを調べます。

SELECT extract(year from l_shipdate) as year,
       extract(month from l_shipdate) as month,
       count(*) as orders
FROM lineitem_all
WHERE extract(month from l_shipdate) = 1
AND l_quantity < 2
GROUP BY 1,2
ORDER BY 1,2;

 year | month | orders
------+-------+---------
 1992 |     1 |  196019
 1993 |     1 | 1582034
 1994 |     1 | 1583181
 1995 |     1 | 1583919
 1996 |     1 | 1583622
 1997 |     1 | 1586541
 1998 |     1 | 1583198
 2016 |     1 |   15542
 2017 |     1 |   15414
 2018 |     1 |   15527
 2019 |     1 |     151

大文字と小文字が混在した名前の使用例

データベース、スキーマ、テーブル、または列の大文字と小文字が混在する名前を持つ、サポート対象の PostgreSQL リモートデータベースをクエリするには、enable_case_sensitive_identifier を true に設定します。このセッションのパラメータの詳細については、「enable_case_sensitive_identifier」を参照してください。

SET enable_case_sensitive_identifier TO TRUE;

通常、データベース名とスキーマ名は小文字です。次の例では、データベースとスキーマに小文字の名前、テーブルと列に大文字と小文字が混在する名前を持つ、サポート対象の PostgreSQL リモートデータベースに接続する方法を示しています。

小文字のデータベース名 (dblower) と小文字のスキーマ名 (schemalower) を持つ Aurora PostgreSQL データベースを参照する外部スキーマを作成します。

CREATE EXTERNAL SCHEMA apg_lower
FROM POSTGRES
DATABASE 'dblower' SCHEMA 'schemalower'
URI 'endpoint to aurora hostname'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

クエリが実行されるセッションで、enable_case_sensitive_identifier を true に設定します。

SET enable_case_sensitive_identifier TO TRUE;

横串検索を実行して、PostgreSQL データベースからすべてのデータを選択します。テーブル (MixedCaseTab) と列 (MixedCaseName) には大文字と小文字が混在した名前を使っています。結果は 1 行 (Harry) です。

select * from apg_lower."MixedCaseTab";

 MixedCaseName
-------
 Harry

次の例では、データベース、スキーマ、テーブル、および列に対して大文字と小文字が混在した名前を持つ、サポート対象の PostgreSQL リモートデータベースに接続する方法を示しています。

外部スキーマを作成する前に、enable_case_sensitive_identifier を true に設定してください。外部スキーマを作成する前に enable_case_sensitive_identifier が true に設定されていない場合、データベースが存在しないというエラーが発生します。

大文字と小文字が混在するデータベース名 (UpperDB) とスキーマ名 (UpperSchema) を持つ Aurora PostgreSQL データベースを参照する外部スキーマを作成します。

CREATE EXTERNAL SCHEMA apg_upper
FROM POSTGRES
DATABASE 'UpperDB' SCHEMA 'UpperSchema'
URI 'endpoint to aurora hostname'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

横串検索を実行して、PostgreSQL データベースからすべてのデータを選択します。テーブル (MixedCaseTab) と列 (MixedCaseName) には大文字と小文字が混在した名前を使っています。結果は 1 行 (Harry) です。

select * from apg_upper."MixedCaseTab";

 MixedCaseName
-------
 Harry

MySQL での横串検索の使用例

次の例は、Aurora MySQL データベースを参照する横串検索を設定する方法を示しています。この例は、フェデレーテッドクエリの動作を示しています。独自の環境で実行するには、環境に合わせて変更します。これを行うための前提条件については、「MySQL への横串検索の使用を開始する」を参照してください。

この例は、以下の前提条件によって異なります:

• Aurora MySQL データベースのSecrets Manager で設定されたシークレットです。このシークレットは、IAM アクセスポリシーとロールで参照されます。詳細については、「フェデレーテッドクエリを使用するためのシークレットと IAM ロールの作成」を参照してください。

• Amazon Redshift と Aurora MySQL をリンクするように設定されたセキュリティグループ。

Aurora MySQL データベースを参照する外部スキーマを作成します。

CREATE EXTERNAL SCHEMA amysql
FROM MYSQL
DATABASE 'functional'
URI 'endpoint to remote hostname'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

Aurora MySQL テーブルの SQL 選択の例を実行して、Aurora MySQL の employees テーブルから 1 行を表示します。

SELECT level FROM amysql.employees LIMIT 1;
            
 level
-------
     8