使用跨數據庫查詢的示例 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

使用跨數據庫查詢的示例

使用以下示例幫助瞭解如何設置引用 Amazon Redshift 數據庫的跨數據庫查詢。

要啟動,請創建數據庫db1db2和用户user1user2您的 Amazon Redshift 叢集。如需詳細資訊,請參閱 CREATE DATABASECREATE USER

--As user1 on db1 CREATE DATABASE db1; CREATE DATABASE db2; CREATE USER user1 PASSWORD 'Redshift01'; CREATE USER user2 PASSWORD 'Redshift01';

作為user1db1,創建表,將訪問權限授予user2,並將值插入到table1。如需詳細資訊,請參閱 GRANTINSERT

--As user1 on db1 CREATE TABLE table1 (c1 int, c2 int, c3 int); GRANT SELECT ON table1 TO user2; INSERT INTO table1 VALUES (1,2,3),(4,5,6),(7,8,9);

作為user2db2中,運行跨數據庫查詢db2使用三部分符號。

--As user2 on db2 SELECT * from db1.public.table1 ORDER BY c1; c1 | c2 | c3 ---+-----+---- 1 | 2 | 3 4 | 5 | 6 7 | 8 | 9 (3 rows)

作為user2db2,創建一個外部架構並在db2使用外部架構表示法。

--As user2 on db2 CREATE EXTERNAL SCHEMA db1_public_sch FROM REDSHIFT DATABASE 'db1' SCHEMA 'public'; SELECT * FROM db1_public_sch.table1 ORDER BY c1; c1 | c2 | c3 ----+----+---- 1 | 2 | 3 4 | 5 | 6 7 | 8 | 9 (3 rows)

要創建不同的視圖並向這些視圖授予權限,如user1db1,請執行下列動作。

--As user1 on db1 CREATE VIEW regular_view AS SELECT c1 FROM table1; GRANT SELECT ON regular_view TO user2; CREATE MATERIALIZED VIEW mat_view AS SELECT c2 FROM table1; GRANT SELECT ON mat_view TO user2; CREATE VIEW late_bind_view AS SELECT c3 FROM public.table1 WITH NO SCHEMA BINDING; GRANT SELECT ON late_bind_view TO user2;

作為user2db2中,使用三部分表示法運行以下跨數據庫查詢以查看特定視圖。

--As user2 on db2 SELECT * FROM db1.public.regular_view; c1 ---- 1 4 7 (3 rows) SELECT * FROM db1.public.mat_view; c2 ---- 8 5 2 (3 rows) SELECT * FROM db1.public.late_bind_view; c3 ---- 3 6 9 (3 rows)

作為user2db2中,使用外部架構表示法運行以下跨數據庫查詢以查詢後期綁定視圖。

--As user2 on db2 SELECT * FROM db1_public_sch.late_bind_view; c3 ---- 3 6 9 (3 rows)

作為user2db2中,使用已連線表在單個查詢中運行下列命令。

--As user2 on db2 CREATE TABLE table1 (a int, b int, c int); INSERT INTO table1 VALUES (1,2,3), (4,5,6), (7,8,9); SELECT a AS col_1, (db1.public.table1.c2 + b) AS sum_col2, (db1.public.table1.c3 + c) AS sum_col3 FROM db1.public.table1, table1 WHERE db1.public.table1.c1 = a; col_1 | sum_col2 | sum_col3 ------+----------+---------- 1 | 4 | 6 4 | 10 | 12 7 | 16 | 18 (3 rows)

以下範例列出了叢集上的所有資料庫。

select database_name, database_owner, database_type from svv_redshift_databases where database_name in ('db1', 'db2'); database_name | database_owner | database_type ---------------+----------------+--------------- db1 | 100 | local db2 | 100 | local (2 rows)

以下示例列出了集羣上所有數據庫的所有 Amazon Redshift 架構。

select database_name, schema_name, schema_owner, schema_type from svv_redshift_schemas where database_name in ('db1', 'db2'); database_name | schema_name | schema_owner | schema_type ---------------+--------------------+--------------+------------- db1 | pg_catalog | 1 | local db1 | public | 1 | local db1 | information_schema | 1 | local db2 | pg_catalog | 1 | local db2 | public | 1 | local db2 | information_schema | 1 | local (6 rows)

以下示例列出了羣集上所有 Amazon Redshift 表或所有數據庫的視圖。

select database_name, schema_name, table_name, table_type from svv_redshift_tables where database_name in ('db1', 'db2') and schema_name in ('public'); database_name | schema_name | table_name | table_type ---------------+-------------+---------------------+------------ db1 | public | late_bind_view | VIEW db1 | public | mat_view | VIEW db1 | public | mv_tbl__mat_view__0 | TABLE db1 | public | regular_view | VIEW db1 | public | table1 | TABLE db2 | public | table2 | TABLE (6 rows)

以下示例列出了集羣上所有數據庫的所有 Amazon Redshift 和外部架構。

select database_name, schema_name, schema_owner, schema_type from svv_all_schemas where database_name in ('db1', 'db2') ; database_name | schema_name | schema_owner | schema_type ---------------+--------------------+--------------+------------- db1 | pg_catalog | 1 | local db1 | public | 1 | local db1 | information_schema | 1 | local db2 | pg_catalog | 1 | local db2 | public | 1 | local db2 | information_schema | 1 | local db2 | db1_public_sch | 1 | external (7 rows)

以下示例列出了集羣上所有數據庫的所有 Amazon Redshift 和外部表。

select database_name, schema_name, table_name, table_type from svv_all_tables where database_name in ('db1', 'db2') and schema_name in ('public'); database_name | schema_name | table_name | table_type ---------------+-------------+---------------------+------------ db1 | public | regular_view | VIEW db1 | public | mv_tbl__mat_view__0 | TABLE db1 | public | mat_view | VIEW db1 | public | late_bind_view | VIEW db1 | public | table1 | TABLE db2 | public | table2 | TABLE (6 rows)