向源 Aurora 数据库集群中添加数据并在 Amazon Redshift 中对其进行查询 - Amazon Aurora

向源 Aurora 数据库集群中添加数据并在 Amazon Redshift 中对其进行查询

要创建将数据从 Amazon Aurora 复制到 Amazon Redshift 的零 ETL 集成,您必须在 Amazon Redshift 中创建目标数据库。

首先,连接到您的 Amazon Redshift 集群或工作组,并创建一个引用您的集成标识符的数据库。然后,您可以向 Aurora 数据库集群中添加数据,并在 Amazon Redshift 中查看其复制的内容。

在 Amazon Redshift 中创建目标数据库

在开始将数据复制到 Amazon Redshift 中之前,创建集成后,您必须在目标数据仓库中创建一个目标数据库。此目标数据库必须包含对集成标识符的引用。您可以使用 Amazon Redshift 控制台或查询编辑器 v2 来创建数据库。

有关创建目标数据库的说明,请参阅在 Amazon Redshift 中创建目标数据库

向源数据库集群中添加数据

在配置集成后,您可以将一些数据添加到您希望复制到 Amazon Redshift 数据仓库的 Aurora 数据库集群中。

注意

Amazon Aurora 和 Amazon Redshift 中的数据类型存在差异。有关数据类型映射的表,请参阅Aurora 和 Amazon Redshift 数据库之间的数据类型差异

首先,使用您选择的 MySQL 或 PostgreSQL 客户端连接到源数据库集群。有关说明,请参阅 连接到 Amazon Aurora 数据库集群

然后,创建一个表并插入一行示例数据。

重要

确保该表有主键。否则,它无法复制到目标数据仓库。

pg_dump 和 pg_restore PostgreSQL 实用程序最初创建没有主键的表,然后添加主键。如果您使用其中一个实用程序,我们建议您先创建一个架构,然后在单独的命令中加载数据。

MySQL

以下示例使用 MySQL Workbench 实用程序

CREATE DATABASE my_db; USE my_db; CREATE TABLE books_table (ID int NOT NULL, Title VARCHAR(50) NOT NULL, Author VARCHAR(50) NOT NULL, Copyright INT NOT NULL, Genre VARCHAR(50) NOT NULL, PRIMARY KEY (ID)); INSERT INTO books_table VALUES (1, 'The Shining', 'Stephen King', 1977, 'Supernatural fiction');

PostgreSQL

以下示例使用 psql PostgreSQL 交互式终端。连接到集群时,请包括您在创建集成时指定的命名数据库。

psql -h mycluster.cluster-123456789012.us-east-2.rds.amazonaws.com -p 5432 -U username -d named_db; named_db=> CREATE TABLE books_table (ID int NOT NULL, Title VARCHAR(50) NOT NULL, Author VARCHAR(50) NOT NULL, Copyright INT NOT NULL, Genre VARCHAR(50) NOT NULL, PRIMARY KEY (ID)); named_db=> INSERT INTO books_table VALUES (1, 'The Shining', 'Stephen King', 1977, 'Supernatural fiction');

在 Amazon Redshift 中查询您的 Aurora 数据

将数据添加到 Aurora 数据库集群后,它会复制到 Amazon Redshift 中并准备好以供查询。

查询复制的数据
  1. 导航到 Amazon Redshift 控制台,然后从左侧导航窗格中选择查询编辑器 v2

  2. 连接到您的集群或工作组,然后从下拉菜单中选择您通过集成创建的目标数据库(本示例中为 destination_database)。有关创建目标数据库的说明,请参阅在 Amazon Redshift 中创建目标数据库

  3. 使用 SELECT 语句来查询您的数据。在本例中,您可以运行以下命令,从您在源 Aurora 数据库集群中创建的表中选择所有数据:

    SELECT * from my_db."books_table";
    在 Redshift 查询编辑器中运行 SELECT 语句。结果是添加到 RDS 数据库中的单行示例数据。
    • my_dbAurora 数据库模式名称。仅 MySQL 数据库需要此选项。

    • books_tableAurora 表名称。

也可以使用命令行客户端查询数据。例如:

destination_database=# select * from my_db."books_table"; ID | Title | Author | Copyright | Genre | txn_seq | txn_id ----+–------------+---------------+-------------+------------------------+----------+--------+ 1 | The Shining | Stephen King | 1977 | Supernatural fiction | 2 | 12192
注意

为了区分大小写,请对架构、表和列名使用双引号(" ")。有关更多信息,请参阅 enable_case_sensitive_identifier

Aurora 和 Amazon Redshift 数据库之间的数据类型差异

表显示 Aurora MySQL 或 Aurora PostgreSQL 数据类型与相应 Amazon Redshift 数据类型的映射。Amazon Aurora 目前仅支持将这些数据类型用于零 ETL 集成。

如果您的源数据库集群中的表包含不受支持的数据类型,则该表将不同步并且 Amazon Redshift 目标无法使用该表。从源到目标的流式传输仍在继续,但数据类型不受支持的表不可用。要修复该表并使其在 Amazon Redshift 中可用,您必须手动恢复重大更改,然后通过运行 ALTER DATABASE...INTEGRATION REFRESH 来刷新集成。

Aurora MySQL

Aurora MySQL 数据类型 Amazon Redshift 数据类型 描述 限制
INT INTEGER 有符号的四字节整数
SMALLINT SMALLINT 有符号的二字节整数
TINYINT SMALLINT 有符号的二字节整数
MEDIUMINT INTEGER 有符号的四字节整数
BIGINT BIGINT 有符号的八字节整数
INT UNSIGNED BIGINT 有符号的八字节整数
TINYINT UNSIGNED SMALLINT 有符号的二字节整数
MEDIUMINT UNSIGNED INTEGER 有符号的四字节整数
BIGINT UNSIGNED DECIMAL(20,0) 可选精度的精确数字
DECIMAL(p,s) = NUMERIC(p,s) DECIMAL (p,s) 可选精度的精确数字

不支持精度大于 38 和比例大于 37

DECIMAL(p,s) UNSIGNED = NUMERIC(p,s) UNSIGNED DECIMAL (p,s) 可选精度的精确数字

不支持精度大于 38 和比例大于 37

FLOAT4/REAL REAL 单精度浮点数
FLOAT4/REAL UNSIGNED REAL 单精度浮点数
DOUBLE/REAL/FLOAT8 DOUBLE PRECISION 双精度浮点数
DOUBLE/REAL/FLOAT8 UNSIGNED DOUBLE PRECISION 双精度浮点数
BIT(n) VARBYTE(8) 长度可变的二进制值
BINARY(n) VARBYTE(n) 长度可变的二进制值
VARBINARY (n) VARBYTE(n) 长度可变的二进制值
CHAR(n) VARCHAR (n) 长度可变的字符串值
VARCHAR (n) VARCHAR (n) 长度可变的字符串值
TEXT VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
TINYTEXT VARCHAR(255) 长度可变、最多 255 个字符的字符串值
MEDIUMTEXT VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
LONGTEXT VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
ENUM VARCHAR(1020) 长度可变、最多 1020 个字符的字符串值
SET VARCHAR(1020) 长度可变、最多 1020 个字符的字符串值
DATE DATE 日历日期(年、月、日)
DATETIME TIMESTAMP 日期和时间(没有时区)
TIMESTAMP(p) TIMESTAMP 日期和时间(没有时区)
TIME VARCHAR(18) 长度可变、最多 18 个字符的字符串值
YEAR VARCHAR(4) 长度可变、最多 4 个字符的字符串值
JSON SUPER 作为值的半结构化数据或文档

Aurora PostgreSQL

Aurora PostgreSQL 的零 ETL 集成不支持自定义数据类型或由扩展创建的数据类型。

Aurora PostgreSQL 数据类型 Amazon Redshift 数据类型 描述 限制
array SUPER 作为值的半结构化数据或文档
bigint BIGINT 有符号的八字节整数
bigserial BIGINT 有符号的八字节整数
bit varying(n) VARBYTE(n) 长度可变、最多 16,777,216 字节的二进制值
bit(n) VARBYTE(n) 长度可变、最多 16,777,216 字节的二进制值
bit、bit varying VARBYTE(16777216) 长度可变、最多 16,777,216 字节的二进制值
布尔值 BOOLEAN 逻辑布尔值(true/false)
bytea VARBYTE(16777216) 长度可变、最多 16,777,216 字节的二进制值
char(n) CHAR(n) 长度固定、最多 65,535 字节的字符串值
char varying(n) VARCHAR(65535) 长度可变、最多 65,535 个字符的字符串值
cid BIGINT

有符号的八字节整数

cidr

VARCHAR(19)

长度可变、最多 19 个字符的字符串值

date DATE 日历日期(年、月、日)

不支持大于 294276 A.D. 的值

double precision DOUBLE PRECISION 双精度浮点数 不完全支持亚正常值

gtsvector

VARCHAR(65535)

长度可变、最多 65535 个字符的字符串值

inet

VARCHAR(19)

长度可变、最多 19 个字符的字符串值

整数 INTEGER 有符号的四字节整数

int2vector

SUPER 作为值的半结构化数据或文档。
interval INTERVAL 持续时间 仅支持指定年至月或日至秒限定符的 INTERVAL 类型。
json SUPER 作为值的半结构化数据或文档
jsonb SUPER 作为值的半结构化数据或文档
jsonpath VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值

macaddr

VARCHAR(17) 长度可变、最多 17 个字符的字符串值

macaddr8

VARCHAR(23) 长度可变、最多 23 个字符的字符串值
money DECIMAL(20,3) 货币金额
名称 VARCHAR(64) 长度可变、最多 64 个字符的字符串值
numeric(p,s) DECIMAL (p,s) 用户定义的固定精度值
  • 不支持 NaN

  • 不支持精度大于 38 和比例大于 37

  • 不支持负比例

oid BIGINT 有符号的八字节整数
oidvector SUPER 作为值的半结构化数据或文档。
pg_brin_bloom_summary VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
pg_dependencies VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
pg_lsn VARCHAR(17) 长度可变、最多 17 个字符的字符串值
pg_mcv_list VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
pg_ndistinct VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
pg_node_tree VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
pg_snapshot VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
real REAL 单精度浮点数 不完全支持亚正常值
refcursor VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
smallint SMALLINT 有符号的二字节整数
smallserial SMALLINT 有符号的二字节整数
Serial INTEGER 有符号的四字节整数
文本 VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
tid VARCHAR(23) 长度可变、最多 23 个字符的字符串值
不带时区的 time [(p)] VARCHAR(19) 长度可变、最多 19 个字符的字符串值 不支持 Infinity-Infinity
有时区的 time [(p)] VARCHAR(22) 长度可变、最多 22 个字符的字符串值 不支持 Infinity-Infinity
不带时区的 timestamp [(p)] TIMESTAMP 日期和时间(没有时区)
  • 不支持 Infinity-Infinity

  • 不支持大于 9999-12-31 的值

  • 不支持 B.C. 值

有时区的 timestamp [(p)] TIMESTAMPTZ 日期和时间(有时区)
  • 不支持 Infinity-Infinity

  • 不支持大于 9999-12-31 的值

  • 不支持 B.C. 值

tsquery VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
tsvector VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
txid_snapshot VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值
uuid VARCHAR(36) 长度可变的 36 个字符的字符串
xid BIGINT 有符号的八字节整数
xid8 DECIMAL(20, 0) 固定精度小数
xml VARCHAR(65535) 长度可变、最多 65535 个字符的字符串值

Aurora PostgreSQL 的 DDL 操作

Amazon Redshift 源自 PostgreSQL,由于两者具有共同的 PostgreSQL 架构,因此与 Aurora PostgreSQL 共享多个特征。零 ETL 集成利用这些相似之处来简化从 Aurora PostgreSQL 到 Amazon Redshift 的数据复制,从而按名称映射数据库并利用共享的数据库、架构和表结构。

管理 Aurora PostgreSQL 零 ETL 集成时应考虑以下几点:

  • 隔离是在数据库级别进行管理。

  • 复制发生在数据库级别。

  • Aurora PostgreSQL 数据库按名称映射到 Amazon Redshift 数据库,如果原始 Redshift 数据库被重命名,则数据将流向相应的重命名数据库。

Amazon Redshift 和 Aurora PostgreSQL 尽管有相似之处,但也存在重大差异。以下各节概述了 Amazon Redshift 系统对常见 DDL 操作的响应。

数据库操作

下表显示了系统对数据库 DDL 操作的响应。

DDL 操作 Redshift 系统响应
CREATE DATABASE 无操作
DROP DATABASE Amazon Redshift 删除目标 Redshift 数据库中的所有数据。
RENAME DATABASE Amazon Redshift 会删除原始目标数据库中的所有数据,然后重新同步新目标数据库中的数据。如果新数据库不存在,您必须手动创建一个。有关说明,请参阅在 Amazon Redshift 中创建目标数据库

架构操作

下表显示了系统对架构 DDL 操作的响应。

DDL 操作 Redshift 系统响应
CREATE SCHEMA 无操作
DROP SCHEMA Amazon Redshift 会删除原始架构。
RENAME SCHEMA Amazon Redshift 会删除原始架构,然后重新同步新架构中的数据。

表操作

下表显示了系统对表 DDL 操作的响应。

DDL 操作 Redshift 系统响应
CREATE TABLE

Amazon Redshift 会创建表。

某些操作会导致表创建失败,例如创建没有主键的表或执行声明式分区。有关更多信息,请参阅Aurora PostgreSQL 限制Aurora 与 Amazon Redshift 的零 ETL 集成问题排查

DROP TABLE Amazon Redshift 会删除表。
TRUNCATE TABLE Amazon Redshift 会截断表。
ALTER TABLE (RENAME...) Amazon Redshift 会重命名表或列。
ALTER TABLE (SET SCHEMA)

Amazon Redshift 会删除原始架构中的表,然后重新同步新架构中的表。

ALTER TABLE (ADD PRIMARY KEY) Amazon Redshift 会添加主键并重新同步表。
ALTER TABLE (ADD COLUMN) Amazon Redshift 会向表中添加列。
ALTER TABLE (DROP COLUMN)

如果不是主键列,则 Amazon Redshift 会删除该列。否则,它会重新同步表。

ALTER TABLE (SET LOGGED/UNLOGGED) 如果您将表更改为已记录,Amazon Redshift 会重新同步该表。如果您将表更改为未记录,Amazon Redshift 会删除该表。