この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部コンサルティングチームの石川です。先日、Redshift クラスター内のデータベース間でクエリを実行する機能がGA(Generally Available)になりました。AWSマニュアルではPUBLICスキーマで解説していますが、実環境と同じようにユーザーごとに作成したスキーマにて機能を試し、BIツールから参照できるワークアラウンドもご紹介します。
Cross-database queries とは
これまでは、Redshiftクラスター内のデータベース間のデータは分離されており、データベース間を横断したクエリを実行できませんでしたが、最新のRA3インスタンスで可能になりました。
尚、以下のリンクは「プレビュー」と書いておりますが、本日すでにGA(Generally Available)です!
検証データベースの準備
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>の3つをドットで修飾した表記法でアクセスします。この表記法でテーブルのレコードを参照できることが確認できました。以下のオペレーションは、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>の3つをドットで修飾した表記法は、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>の3つをドットで修飾した表記法で参照すれば良いだけで済むので検証するまでもないと考えていましたが、Cross-database queriesは、単に同一クラスタのデータベース間で参照できるようにする機能ではなく、Redshift Data Sharingの設定の基礎となる機能です。
適切な権限の付与やBIツールから参照する外部スキーマの定義まで把握していないと、Redshift Data Sharingの設定や運用で支障をきたすと考えブログにまとめました。Redshift Data Sharingをお試しになる前にご理解することをおすすめします。