CREATE DATABASE - Amazon Redshift

CREATE DATABASE

创建新数据库。

要创建数据库,您必须是超级用户或拥有 CREATEDB 权限。要创建与零 ETL 集成关联的数据库,您必须是超级用户或同时拥有 CREATEDB 和 CREATEUSER 权限。

您不能在以下事务块中运行 CREATE DATABASE:(BEGIN ... END)。有关事务的更多信息,请参阅 可序列化的隔离

语法

CREATE DATABASE database_name [ { [ WITH ] [ OWNER [=] db_owner ] [ CONNECTION LIMIT { limit | UNLIMITED } ] [ COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE } ] [ ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } ] } | { [ WITH PERMISSIONS ] FROM DATASHARE datashare_name ] OF [ ACCOUNT account_id ] NAMESPACE namespace_guid } | { FROM { { ARN '<arn>' } { WITH DATA CATALOG SCHEMA '<schema>' | WITH NO DATA CATALOG SCHEMA } } | { INTEGRATION '<integration_id>' [ DATABASE '<source_database>' ] [SET {REFRESH_INTERVAL <interval>} ] } } | { IAM_ROLE {default | 'SESSION' | 'arn:aws:iam::<account-id>:role/<role-name>' } } ]

参数

database_name

新数据库的名称。有关有效名称的更多信息,请参阅名称和标识符

WITH

可选关键字。

OWNER

指定数据库所有者。

=

可选字符。

db_owner

数据库所有者的用户名。

CONNECTION LIMIT { limit | UNLIMITED }

允许用户同时打开的数据库连接的最大数量。此限制不适用于超级用户。使用 UNLIMITED 关键字设置允许的并行连接的最大数量。可能对每个用户的连接数量也会施加限制。有关更多信息,请参阅 CREATE USER。默认为 UNLIMITED。要查看当前连接,请查询 STV_SESSIONS 系统视图。

注意

如果用户及数据库连接限制均适用,当用户尝试连接时,必须有一个同时低于这两个限制的未使用的连接槽可用。

COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE }

指定字符串搜索或比较是 CASE_SENSITIVE 还是 CASE_INSENSITIVE 的子句。默认值为 CASE_SENSITIVE。

ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT }

指定针对数据库运行查询时使用的隔离级别的子句。

  • SERIALIZABLE 隔离 – 为并发事务提供完全可序列性。有关更多信息,请参阅 可序列化的隔离

  • SNAPSHOT 隔离 – 提供隔离级别,来防止出现更新和删除冲突。这是在预调配集群或无服务器命名空间中创建的数据库的默认设置。

您可以按如下方式查看数据库运行的并发模型:

  • 查询 STV_DB_ISOLATION_LEVEL 目录视图。有关更多信息,请参阅 STV_DB_ISOLATION_LEVEL

    SELECT * FROM stv_db_isolation_level;
  • 查询 PG_DATABASE_INFO 视图。

    SELECT datname, datconfig FROM pg_database_info;

    每个数据库的隔离级别出现在 concurrency_model 键旁边。值为 1 时,表示 SNAPSHOT。值为 2 时,表示 SERIALIZABLE。

在 Amazon Redshift 数据库中,SERIALIZABLE 和 SNAPSHOT 隔离都是可序列化隔离级别的类型。也就是说,根据 SQL 标准,防止脏读、不可重复读和幻读。这两种隔离级别都可保证某事务对该事务开始时就存在的数据快照进行操作,并且没有任何其他事务可以更改该快照。但是,SNAPSHOT 隔离不能提供完全可序列性,因为它不能防止在不同的表行上进行写入偏斜插入和更新。

以下场景说明了使用 SNAPSHOT 隔离级别的写入偏斜更新。名为 Numbers 的表包含名为 digits 的列,其中包含 01 值。每个用户的 UPDATE 语句不会与其他用户重叠。但是,01 值可交换。它们运行的 SQL 遵循以下时间表,结果如下:

时间 用户 1 操作 用户 2 操作
1 BEGIN;
2 BEGIN;
3 SELECT * FROM Numbers;
digits
------
0
1
4 SELECT * FROM Numbers;
digits
------
0
1
5 UPDATE Numbers SET digits=0 WHERE digits=1;
6 SELECT * FROM Numbers;
digits
------
0
0
7 COMMIT;
8 Update Numbers SET digits=1 WHERE digits=0;
9 SELECT * FROM Numbers;
digits
------
1
1
10 COMMIT;
11 SELECT * FROM Numbers;
digits
------
1
0
12 SELECT * FROM Numbers;
digits
------
1
0

如果使用可序列化的隔离运行同一场景,则 Amazon Redshift 因可序列化违规而终止用户 2 并返回错误 1023。有关更多信息,请参阅 如何修复可序列化的隔离错误。在这种情况下,只有用户 1 可以成功提交。并非所有工作负载都需要可序列化隔离作为一项要求,在这种情况下,快照隔离足以作为数据库的目标隔离级别。

FROM ARN '<ARN>'

用于创建数据库的 AWS Glue 数据库 ARN。

{ DATA CATALOG SCHEMA '<schema>' | WITH NO DATA CATALOG SCHEMA }
注意

仅当您的 CREATE DATABASE 命令也使用 FROM ARN 参数时,此参数才适用。

指定是否使用架构创建数据库以帮助访问 AWS Glue Data Catalog 中的对象。

FROM INTEGRATION '<integration_id>' [ DATABASE '<source_database>' ] [SET {REFRESH_INTERVAL <interval>} ]

指定是否使用零 ETL 集成标识符创建数据库。您可以从 SVV_INTEGRATION 系统视图中检索 integration_id。对于 Aurora PostgreSQL 零 ETL 集成,还需要指定 source_database 名称,这也可以从 SVV_INTEGRATION 中检索到。SET REFRESH_INTERVAL 子句设置从零 ETL 源到目标数据库刷新数据的大致时间间隔(秒)。对于源类型为 Aurora MySQL、Aurora PostgreSQL 或 RDS for MySQL 的零 ETL 集成,默认值为零(0)秒。

有关示例,请参阅创建数据库以接收零 ETL 集成的结果。有关使用零 ETL 集成创建数据库的更多信息,请参阅《Amazon Redshift 管理指南》中的在 Amazon Redshift 中创建目标数据库

IAM_ROLE { default | 'SESSION' | 'arn:aws:iam::<AWS 账户-id>:role/<role-name>' }
注意

仅当您的 CREATE DATABASE 命令也使用 FROM ARN 参数时,此参数才适用。

如果您在运行 CREATE DATABASE 命令时指定与集群关联的 IAM 角色,则 Amazon Redshift 将在您对数据库运行查询时使用该角色的凭证。

指定 default 关键字表示要使用设置为默认并与集群关联的 IAM 角色。

如果您使用联合身份连接到 Amazon Redshift 集群并访问使用此命令创建的外部架构中的表,则使用 'SESSION'。有关使用联合身份的示例,请参阅使用联合身份管理 Amazon Redshift 对本地资源和 Amazon Redshift Spectrum 外部表的访问权限,其中说明了如何配置联合身份。

使用 IAM 角色的 Amazon 资源名称(ARN),您的集群使用该角色进行身份验证和授权。IAM 角色至少必须有权在要访问的 Amazon S3 桶上执行 LIST 操作和有权在该桶包含的 Amazon S3 对象上执行 GET 操作。要了解有关在使用 AWS Glue Data Catalog 为数据共享创建数据库时使用 IAM_ROLE 的更多信息,请参阅以使用者身份使用 Lake Formation 托管的数据共享

下面显示了单个 ARN 的 IAM_ROLE 参数字符串的语法。

IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-name>'

您可以将角色串联起来,以便集群可以承担另一个 IAM 角色 (可能属于其他账户)。您最多可串联 10 个角色。有关更多信息,请参阅 在 Amazon Redshift Spectrum 中链接 IAM 角色

对于此 IAM 角色,请附加类似于以下内容的 IAM 权限策略。

{ "Version": "2012-10-17", "Statement": [ { "Sid": "AccessSecret", "Effect": "Allow", "Action": [ "secretsmanager:GetResourcePolicy", "secretsmanager:GetSecretValue", "secretsmanager:DescribeSecret", "secretsmanager:ListSecretVersionIds" ], "Resource": "arn:aws:secretsmanager:us-west-2:123456789012:secret:my-rds-secret-VNenFy" }, { "Sid": "VisualEditor1", "Effect": "Allow", "Action": [ "secretsmanager:GetRandomPassword", "secretsmanager:ListSecrets" ], "Resource": "*" } ] }

有关创建 IAM 角色以用于联合查询的步骤,请参阅创建密钥和 IAM 角色以使用联合查询

注意

请不要在链接的角色列表中包含空格。

下面显示了串联三个角色的语法。

IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-1-name>,arn:aws:iam::<aws-account-id>:role/<role-2-name>,arn:aws:iam::<aws-account-id>:role/<role-3-name>'

将 CREATE DATABASE 与数据共享结合使用的语法

以下语法描述了用于从数据共享中创建数据库以在同一 AWS 账户内共享数据的 CREATE DATABASE 命令。

CREATE DATABASE database_name [ [ WITH PERMISSIONS ] FROM DATASHARE datashare_name ] OF [ ACCOUNT account_id ] NAMESPACE namespace_guid

以下语法描述了用于从数据共享中创建数据库以在 AWS 账户间共享数据的 CREATE DATABASE 命令。

CREATE DATABASE database_name [ [ WITH PERMISSIONS ] FROM DATASHARE datashare_name ] OF ACCOUNT account_id NAMESPACE namespace_guid

将 CREATE DATABASE 与数据共享结合使用的参数

FROM DATASHARE

指示数据库所在位置的关键词。

datashare_name

创建使用者数据库所在的数据共享的名称。

WITH PERMISSIONS

指定通过数据共享创建的数据库需要对象级权限才能访问各个数据库对象。如果没有此子句,则被授予对数据库的 USAGE 权限的用户或角色将自动有权访问数据库中的所有数据库对象。

NAMESPACE namespace_guid

指定数据共享所属的生产者命名空间的值。

ACCOUNT account_id

指定数据共享所属的生产者账户的值。

用于数据共享的 CREATE DATABASE 的使用说明

作为数据库超级用户,当您使用 CREATE DATABASE 从 AWS 账户内的数据共享创建数据库时,指定 NAMESPACE 选项。ACCOUNT 选项为可选项。当您使用 CREATE DATABASE 从 AWS 账户间的数据共享创建数据库时,同时指定生产者的 ACCOUNT 和 NAMESPACE。

对于使用者集群上的数据共享,一个数据共享仅可创建一个使用者数据库。不能创建多个引用同一数据共享的使用者数据库。

CREATE DATABASE(从 AWS Glue Data Catalog)

要使用 AWS Glue 数据库 ARN 创建数据库,请在 CREATE DATABASE 命令中指定 ARN。

CREATE DATABASE sampledb FROM ARN <glue-database-arn> WITH NO DATA CATALOG SCHEMA;

或者,您也可以为 IAM_ROLE 参数提供一个值。有关参数和接受的值的更多信息,请参阅参数

以下示例演示了如何使用 IAM 角色从 ARN 创建数据库。

CREATE DATABASE sampledb FROM ARN <glue-database-arn> WITH NO DATA CATALOG SCHEMA IAM_ROLE <iam-role-arn>
CREATE DATABASE sampledb FROM ARN <glue-database-arn> WITH NO DATA CATALOG SCHEMA IAM_ROLE default;

您也可以使用 DATA CATALOG SCHEMA 创建数据库。

CREATE DATABASE sampledb FROM ARN <glue-database-arn> WITH DATA CATALOG SCHEMA <sample_schema> IAM_ROLE default;

创建数据库以接收零 ETL 集成的结果

要使用零 ETL 集成标识创建数据库,请在 CREATE DATABASE 命令中指定 integration_id

CREATE DATABASE destination_db_name FROM INTEGRATION 'integration_id';

例如,首先从 SVV_INTEGRATION 中获取集成 ID;

SELECT integration_id FROM SVV_INTEGRATION;

然后使用检索到的其中一个集成 ID 创建接收零 ETL 集成的数据库。

CREATE DATABASE sampledb FROM INTEGRATION 'a1b2c3d4-5678-90ab-cdef-EXAMPLE11111';

例如,当需要零 ETL 集成源数据库时,请指定。

CREATE DATABASE sampledb FROM INTEGRATION 'a1b2c3d4-5678-90ab-cdef-EXAMPLE11111' DATABASE 'sourcedb';

您还可以设置数据库的刷新间隔。例如,将来自零 ETL 集成源的数据的刷新间隔设为 7200 秒:

CREATE DATABASE myacct_mysql FROM INTEGRATION 'a1b2c3d4-5678-90ab-cdef-EXAMPLE11111' SET REFRESH_INTERVAL 7200;

查询 SVV_INTEGRATION 目录视图,以获取零 ETL 集成的相关信息,如 integration_id、target_database、source、refresh_interval 等。

SELECT * FROM svv_integration;

CREATE DATABASE 限制

Amazon Redshift 针对数据库强制实施以下限制:

  • 每个集群最多 60 个用户定义的数据库。

  • 数据库名称最多为 127 个字节。

  • 数据库名称不能使用保留字。

数据库排序规则

排序规则是一组规则,用于定义数据库引擎如何对 SQL 中的字符类型数据进行比较和排序。不区分大小写的排序规则是最常用的排序规则。Amazon Redshift 使用不区分大小写的排序规则以帮助从其他数据仓库系统迁移。凭借对不区分大小写的排序规则的本机支持,Amazon Redshift 继续使用重要的调整或优化方法,如分配键、排序键或范围限制扫描。

COLLATE 子句指定数据库中所有 CHAR 和 VARCHAR 列的默认排序规则。如果指定了 CASE_INSENSITIVE,则所有 CHAR 或 VARCHAR 列都使用不区分大小写的排序规则。有关排序规则的信息,请参阅排序规则序列

在不区分大小写的列中插入或摄取的数据将保持其原始大小写。但是所有基于比较的字符串操作,包括排序和分组都不区分大小写。模式匹配操作(如类似于的 LIKE 谓词)和正则表达式函数也不区分大小写。

以下 SQL 操作支持适用的排序规则语义:

  • 比较运算符:=、<>、<、<=、>、>=。

  • LIKE 运算符

  • ORDER BY 子句

  • GROUP BY 子句

  • 使用字符串比较的聚合函数,例如 MIN、MAX 和 LISTAGG

  • 窗口函数,如 PARTITION BY 子句和 ORDER BY 子句

  • 标量函数 greatest() 和 least()、STRPOS()、REGEXP_COUNT()、REGEXP_REPLACE()、REGEXP_INSTR()、REGEXP_SUBSTR()

  • Distinct 子句

  • UNION、INTERSECT 和 EXCEPT

  • IN LIST

对于外部查询(包括 Amazon Redshift Spectrum 和 Aurora PostgreSQL 联合查询),VARCHAR 或 CHAR 列的排序规则与当前数据库级别的排序规则相同。

以下示例将查询 Amazon Redshift Spectrum 表:

SELECT ci_varchar FROM spectrum.test_collation WHERE ci_varchar = 'AMAZON'; ci_varchar ---------- amazon Amazon AMAZON AmaZon (4 rows)

有关如何使用数据库排序规则创建表的信息,请参阅CREATE TABLE

有关 COLLATE 函数的信息,请参阅COLLATE 函数

数据库排序规则限制

以下是在 Amazon Redshift 中使用数据库排序规则时的限制:

  • 所有系统表或视图(包括 PG 目录表和 Amazon Redshift 系统表)均区分大小写。

  • 当使用者数据库和生产者数据库具有不同的数据库级排序规则时,Amazon Redshift 不支持跨数据库和跨集群查询。

  • Amazon Redshift 不支持仅领导节点查询中不区分大小写的排序规则。

    以下示例显示了一个不受支持的不区分大小写的查询以及 Amazon Redshift 发送的错误:

    SELECT collate(usename, 'case_insensitive') FROM pg_user; ERROR: Case insensitive collation is not supported in leader node only query.
  • Amazon Redshift 不支持区分大小写和不区分大小写的列之间的交互,例如比较、函数、联接或集合运算。

    以下示例显示了区分大小写和不区分大小写的列交互时出现的错误:

    CREATE TABLE test (ci_col varchar(10) COLLATE case_insensitive, cs_col varchar(10) COLLATE case_sensitive, cint int, cbigint bigint);
    SELECT ci_col = cs_col FROM test; ERROR: Query with different collations is not supported yet.
    SELECT concat(ci_col, cs_col) FROM test; ERROR: Query with different collations is not supported yet.
    SELECT ci_col FROM test UNION SELECT cs_col FROM test; ERROR: Query with different collations is not supported yet.
    SELECT * FROM test a, test b WHERE a.ci_col = b.cs_col; ERROR: Query with different collations is not supported yet.
    Select Coalesce(ci_col, cs_col) from test; ERROR: Query with different collations is not supported yet.
    Select case when cint > 0 then ci_col else cs_col end from test; ERROR: Query with different collations is not supported yet.
  • Amazon Redshift 不支持 SUPER 数据类型的排序规则。不支持在不区分大小写的数据库中创建 SUPER 列以及 SUPER 列和不区分大小写的列之间的交互。

    以下示例在不区分大小写的数据库中创建一个以 SUPER 作为数据类型的表:

    CREATE TABLE super_table (a super); ERROR: SUPER column is not supported in case insensitive database.

    以下示例使用与 SUPER 数据进行比较的不区分大小写的字符串查询数据:

    CREATE TABLE test_super_collation (s super, c varchar(10) COLLATE case_insensitive, i int);
    SELECT s = c FROM test_super_collation; ERROR: Coercing from case insensitive string to SUPER is not supported.

要使这些查询起作用,请使用 COLLATE 函数将一个列的排序规则转换为与另一列匹配。有关更多信息,请参阅 COLLATE 函数

示例

创建数据库

以下示例创建名为 TICKIT 的数据库并将所有权授予用户 DWUSER。

create database tickit with owner dwuser;

要查看有关数据库的详细信息,请查询 PG_DATABASE_INFO 目录表。

select datname, datdba, datconnlimit from pg_database_info where datdba > 1; datname | datdba | datconnlimit -------------+--------+------------- admin | 100 | UNLIMITED reports | 100 | 100 tickit | 100 | 100

以下示例使用 SNAPSHOT 隔离级别创建名为 sampledb 的数据库。

CREATE DATABASE sampledb ISOLATION LEVEL SNAPSHOT;

以下示例从数据共享 salesshare 中创建了数据库 sales_db。

CREATE DATABASE sales_db FROM DATASHARE salesshare OF NAMESPACE '13b8833d-17c6-4f16-8fe4-1a018f5ed00d';

数据库排序规则示例

创建不区分大小写的数据库

以下示例将创建 sampledb 数据库、创建 T1 表,并将数据插入 T1 表中。

create database sampledb collate case_insensitive;

连接到您刚刚使用 SQL 客户端创建的新数据库。使用 Amazon Redshift 查询编辑器 v2 时,在编辑器中选择 sampledb。使用 RSQL 时,请使用如下命令。

\connect sampledb;
CREATE TABLE T1 ( col1 Varchar(20) distkey sortkey );
INSERT INTO T1 VALUES ('bob'), ('john'), ('Mary'), ('JOHN'), ('Bob');

然后,查询查找含有 John 的结果。

SELECT * FROM T1 WHERE col1 = 'John'; col1 ------ john JOHN (2 row)
按不区分大小写的顺序排序

以下示例显示了表 T1 中不区分大小写的排序。BobbobJohnjohn 的排序具有不确定性,因为它们在不区分大小写的列中是相等的。

SELECT * FROM T1 ORDER BY 1; col1 ------ bob Bob JOHN john Mary (5 rows)

同样,以下示例显示了 GROUP BY 子句中不区分大小写的排序。Bobbob 是相等的,并且属于同一个组。结果中显示哪一个是不确定的。

SELECT col1, count(*) FROM T1 GROUP BY 1; col1 | count -----+------ Mary | 1 bob | 2 JOHN | 2 (3 rows)
使用窗口函数对不区分大小写的列进行查询

以下示例在不区分大小写的列上查询窗口函数。

SELECT col1, rank() over (ORDER BY col1) FROM T1; col1 | rank -----+------ bob | 1 Bob | 1 john | 3 JOHN | 3 Mary | 5 (5 rows)
使用 DISTINCT 关键词进行查询

以下示例查询带有 DISTINCT 关键词的 T1 表。

SELECT DISTINCT col1 FROM T1; col1 ------ bob Mary john (3 rows)
使用 UNION 子句进行查询

以下示例显示来自表 T1T2 的 UNION 的结果。

CREATE TABLE T2 AS SELECT * FROM T1;
SELECT col1 FROM T1 UNION SELECT col1 FROM T2; col1 ------ john bob Mary (3 rows)