Redshift 跨库查询使用方法

一、背景

Redshift默认会创建名为dev的数据库，在其中又包含名为public的schema，然后用户在其中创建表和视图。如果希望在同一个Redshift集群内同时创建多个数据库，并且进行跨数据库访问，那么可参照本文的方法。

注：本功能仅支持RA3机型，老的dc2/ds2机型上不支持。

二、基础环境

以下操作用具有admin权限的默认用户awsuser完成。可通过AWS控制台上的Redshift Query Editor V2图形界面完成，也可以是使用管理员账户awsuser连接到JDBC默认的dev库后，运行如下命令。

1、创建db1和db2

CREATE DATABASE db1;
CREATE DATABASE db2;

2、创建对应的user1和user2

CREATE USER user1 PASSWORD 'Redshift01';
CREATE USER user2 PASSWORD 'Redshift02';

3、为两个用户分别赋予各自库的权限

GRANT ALL ON DATABASE db1 TO user1;
GRANT ALL ON DATABASE db2 TO user2;

准备完毕。

三、以user1身份创建测试数据和设置权限

本文使用SQL Workbench J作为JDBC客户端访问Redshift，使用的JDBC驱动版本是redshift-jdbc42-2.0.0.4.jar。在登陆时候，设置JDBC字符串地址为：

jdbc:redshift://redshift-cluster-1.caochylzoc3i.cn-northwest-1.redshift.amazonaws.com.cn:5439/db1

以上信息可以看到，JDBC连接字符串的末尾已经替换为了db1。

1、登陆、建表、注入数据

以user1身份执行本命令。

CREATE TABLE table1 (c1 int, c2 int, c3 int);
INSERT INTO table1 VALUES (1,2,3),(4,5,6),(7,8,9);

测试本数据库对应的user1数据访问正常。

select * from table1

可看到数据正常加载。

2、以user1身份登陆、将user1对应的db1内的表授权给user2可查询

执行如下命令：

GRANT SELECT ON TABLE table1 TO user2;

四、以user2身份登陆db2、并验证对db1的跨数据库访问

配置JDBC使用user2登陆，并声明连接数据库为db2。

jdbc:redshift://redshift-cluster-1.caochylzoc3i.cn-northwest-1.redshift.amazonaws.com.cn:5439/db2

执行如下命令查询：

select * from db1.public.table1

可看到查询正常。

五、跨数据库创建视图（可选）

注意：注意：这一步是可选的，因为上一步测试跨数据库查询已经正常了。以下命令继续在db2内使用用户user02完成。

1、在db2内创建表

由于上文db1内已经有了table1表，因此这里在db2内创建table2表。执行如下命令：

CREATE TABLE table2 (a int, b int, c int);
INSERT INTO table2 VALUES (1,2,3), (4,5,6), (7,8,9);

确认db2内的table2表数据正常：

select * from table2

返回结果正常。至此db1和db2都分别有了自己的数据表。

2、在db2内创建包含db1和db2的物化视图

在db2内创建物化视图total，执行如下命令：

CREATE MATERIALIZED VIEW total
AS
SELECT t1.c1 AS c1,
       t1.c2 AS c2,
       t1.c3 AS c3,
       t2.a AS a,
       t2.b AS b,
       t2.c AS c,
       (t1.c1 + t1.c2 ++ t1.c3 + t2.a + t2.b + t2.c) AS SUM
FROM db1.public.table1 t1,
     db2.public.table2 t2
WHERE t1.c1 = t2.a

返回信息如下：

Warnings:
An incrementally maintained materialized view could not be created, reason: External tables other than Elastic Views are unsupported. The materialized view created, total, will be recomputed from scratch for every REFRESH.

Materialized view total created

Execution time: 3.58s

这是正常的，因为存在跨数据库，因此Redshift在External tables上创建的物化视图不支持自动递增刷新，查询时候会触发整个视图的完整刷新。

3、在db2内查询包含db1数据的物化视图

select * from total

返回结果如下：

c1c2c3abcsum
12312312
45645630
78978948

这里看到，返回的查询结果，前三列来自db1的table1，后三列来自db2的table2，最后一列是所有字段的求和。这个视图的查询结果与预期结果相符。

六、其他辅助命令

列出所有数据库、Schema名为Public下的表：

select database_name, schema_name, table_name, table_type 
from svv_redshift_tables
where schema_name in ('public');

七、参考文档

使用跨数据库查询的示例：

https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/cross-database_example.html

GRANT命令赋予权限的使用说明：

https://docs.aws.amazon.com/zhcn/redshift/latest/dg/rGRANT-examples.html

刷新物化视图：

https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/materialized-view-refresh.html