[新機能] Amazon Redshift Cross-database queries を試してみました

データアナリティクス事業本部コンサルティングチームの石川です。先日、Redshift クラスター内のデータベース間でクエリを実行する機能がGA（Generally Available）になりました。AWSマニュアルではPUBLICスキーマで解説していますが、実環境と同じようにユーザーごとに作成したスキーマにて機能を試し、BIツールから参照できるワークアラウンドもご紹介します。

Cross-database queries とは

これまでは、Redshiftクラスター内のデータベース間のデータは分離されており、データベース間を横断したクエリを実行できませんでしたが、最新のRA3インスタンスで可能になりました。

尚、以下のリンクは「プレビュー」と書いておりますが、本日すでにGA（Generally Available）です！

• Amazon Redshift でのクロスデータベースクエリを発表 (プレビュー)

検証データベースの準備

Redshiftクラスタは、東京リージョンにra3.xlplusのクラスタ構成を用意しました。2つのデータベースとユーザー、検証するオブジェクト（テーブルやビュー等）を作成します。最終的には以下の構成になります。

testuser1 ユーザーとtestuser2 ユーザーの作成

以下のオペレーションは、スーパーユーザー（admin）で実行します。

dev=# CREATE USER testuser1 WITH PASSWORD 'TestUser1';
CREATE USER

dev=# CREATE USER testuser2 WITH PASSWORD 'TestUser2';
CREATE USER

testdb1 データベースとtestdb2 データベースの作成

データベースを作成して、所有者を各ユーザーに割り当てます。以下のオペレーションは、スーパーユーザー（admin）で実行します。

dev=# CREATE DATABASE testdb1;
CREATE DATABASE

dev=# ALTER DATABASE testdb1 OWNER TO testuser1;
ALTER DATABASE

dev=# CREATE DATABASE testdb2;
CREATE DATABASE

dev=# ALTER DATABASE testdb2 OWNER TO testuser2;
ALTER DATABASE

dev=# \l
                    List of databases
     name     |   owner   | encoding | access privileges
--------------+-----------+----------+-------------------
 dev          | rdsdb     | UNICODE  |
 padb_harvest | rdsdb     | UNICODE  |
 template0    | rdsdb     | UNICODE  | rdsdb=CT/rdsdb
 template1    | rdsdb     | UNICODE  | rdsdb=CT/rdsdb
 testdb1      | testuser1 | UNICODE  |
 testdb2      | testuser2 | UNICODE  |
(6 rows)

testdb2 データベースにスキーマとオブジェクトを作成

testdb1 データベースのtestuser1ユーザーからtestdb2 データベースのオブジェクトを参照したいので、testdb2 データベースにスキーマとオブジェクトを作成します。

testdb2 データベースにtestschema2スキーマを作成

以下のオペレーションは、testuser2ユーザーで実行します。

testdb2=> CREATE SCHEMA testschema2;
CREATE SCHEMA

testdb2=> ALTER SCHEMA testschema2 OWNER TO testuser2;
ALTER SCHEMA

testschema2スキーマにオブジェクトを作成

オブジェクトを作成します。以下のオペレーションは、testuser2ユーザーで実行します。

• テーブル：testschema2.testtable2
• ビュー：testschema2.testview2
• レイトバインディングビュー：testschema2.testlbview2
• マテリアライズドビュー；testschema2.testmtview2

testdb2=> CREATE TABLE testschema2.testtable2 (id int, name varchar(64));
CREATE TABLE

testdb2=> INSERT INTO testschema2.testtable2 (id, name) VALUES(2, 'testuser2');
INSERT 0 1

testdb2=> CREATE VIEW testschema2.testview2 AS (SELECT * FROM testschema2.testtable2);
CREATE VIEW

testdb2=> CREATE VIEW testschema2.testlbview2 AS (SELECT * FROM testschema2.testtable2) WITH NO SCHEMA BINDING;
CREATE VIEW

testdb2=> CREATE MATERIALIZED VIEW testschema2.testmtview2 AS (SELECT * FROM testschema2.testtable2);
CREATE MATERIALIZED VIEW

testuser1にオブジェクトへの参照アクセス許可を付与

以下のオペレーションは、testuser2ユーザーで実行します。

testdb2=> GRANT USAGE ON SCHEMA testschema2 TO testuser1;
GRANT

testdb2=> GRANT SELECT ON testschema2.testtable2 TO testuser1;
GRANT

testdb2=> GRANT SELECT ON testschema2.testview2 TO testuser1;
GRANT

testdb2=> GRANT SELECT ON testschema2.testlbview2 TO testuser1;
GRANT

testdb2=> GRANT SELECT ON testschema2.testmtview2 TO testuser1;
GRANT

Cross-database queriesを試す

testuser1ユーザーからtestuser2のオブジェクトを参照できるか試します。

オブジェクトの参照は、テーブル、ビュー、レイトバインディングビュー、マテリアライズドビューといったデータベースオブジェクトは、<database>.<schema>.<object>の３つをドットで修飾した表記法でアクセスします。この表記法でテーブルのレコードを参照できることが確認できました。以下のオペレーションは、testuser1ユーザーで実行します。

$ psql -h cross-database.xxxxxxxxxxxx.ap-northeast-1.redshift.amazonaws.com -U testuser1 -d testdb1 -p 5439

Password for user testuser1:
psql (13.1, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

testdb1=> SELECT * FROM testdb2.testschema2.testtable2;
 id |   name
----+-----------
  2 | testuser2
(1 row)

testdb1=> SELECT * FROM testdb2.testschema2.testview2;
 id |   name
----+-----------
  2 | testuser2
(1 row)

testdb1=> SELECT * FROM testdb2.testschema2.testlbview2;
 id |   name
----+-----------
  2 | testuser2
(1 row)

testdb1=> SELECT * FROM testdb2.testschema2.testmtview2;
 id |   name
----+-----------
  2 | testuser2
(1 row)

BIツールから参照できるワークアラウンドを試す

データベースオブジェクトを<database>.<schema>.<object>の３つをドットで修飾した表記法は、BIツールなどでは、データベースの指定がサポートされていないためスキーマが参照できません。BIツールからスキーマとして参照できるようにするには、更に<database>.<schema>の部分を外部スキーマとして登録し、<external_schema>.<table>の形式で再定義します。以下のオペレーションは、testuser1ユーザーで実行します。

testdb1=> CREATE EXTERNAL SCHEMA external_testschema2
FROM REDSHIFT
DATABASE 'testdb2' SCHEMA 'testschema2';
CREATE SCHEMA

testuser1ユーザーの外部スキーマを経由して、testuser2のオブジェクトを参照します。

外部スキーマは、<external_schema>.<table>の形式で参照します。testuser1ユーザーの外部スキーマを経由して、testuser2のオブジェクトを参照できることが確認できました。以下のオペレーションは、testuser1ユーザーで実行します。

testdb1=> SELECT * FROM external_testschema2.testtable2;
 id |   name
----+-----------
  2 | testuser2
(1 row)

testdb1=> SELECT * FROM external_testschema2.testview2;
 id |   name
----+-----------
  2 | testuser2
(1 row)

testdb1=> SELECT * FROM external_testschema2.testlbview2;
 id |   name
----+-----------
  2 | testuser2
(1 row)

testdb1=> SELECT * FROM external_testschema2.testmtview2;
 id |   name
----+-----------
  2 | testuser2
(1 row)

BIツール（ODBC Driver/Version 1.4.20）からも外部スキーマ経由で参照できることを確認しました。

最後に

当初は、<database>.<schema>.<object>の３つをドットで修飾した表記法で参照すれば良いだけで済むので検証するまでもないと考えていましたが、Cross-database queriesは、単に同一クラスタのデータベース間で参照できるようにする機能ではなく、Redshift Data Sharingの設定の基礎となる機能です。

適切な権限の付与やBIツールから参照する外部スキーマの定義まで把握していないと、Redshift Data Sharingの設定や運用で支障をきたすと考えブログにまとめました。Redshift Data Sharingをお試しになる前にご理解することをおすすめします。