AWS AmazonRDS documentation change
Summary
Added documentation for RDS for PostgreSQL and Oracle support in zero-ETL integrations, including data type mappings, DDL operation behaviors, and example SQL syntax
Security assessment
The changes expand feature documentation for PostgreSQL/Oracle compatibility and data replication behaviors. No security vulnerabilities or security-specific features are mentioned. Changes focus on functionality, limitations, and operational guidance rather than security controls.
Diff
diff --git a/AmazonRDS/latest/UserGuide/zero-etl.querying.md b/AmazonRDS/latest/UserGuide/zero-etl.querying.md index 283cdc847..f4c64f0ea 100644 --- a//AmazonRDS/latest/UserGuide/zero-etl.querying.md +++ b//AmazonRDS/latest/UserGuide/zero-etl.querying.md @@ -5 +5 @@ -Creating a target databaseAdding data to the source databaseQuerying your Amazon RDS data in Amazon RedshiftData type differences +Creating a target databaseAdding data to the source databaseQuerying your Amazon RDS data in Amazon RedshiftData type differencesDDL operations for RDS for PostgreSQL @@ -22,0 +23,2 @@ For connections with Amazon Redshift, connect to your Amazon Redshift cluster or + * DDL operations for RDS for PostgreSQL + @@ -34 +36 @@ For instructions to create a destination database, see [Create a destination dat -After you configure your integration, you can add some data to the RDS database that you want to replicate into your data warehouse. +After you configure your integration, you can populate the source RDS database with data that you want to replicate into your data warehouse. @@ -47,0 +50,2 @@ Make sure that the table has a primary key. Otherwise, it can't be replicated to +**RDS for MySQL** + @@ -59,0 +64,24 @@ The following example uses the [MySQL Workbench utility](https://dev.mysql.com/d +**RDS for PostgreSQL** + +The following example uses the `[psql](https://www.postgresql.org/docs/current/app-psql.html)` PostgreSQL interactive terminal. When connecting to the database, include the database name that you want to replicate. + + + psql -h mydatabase.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'); + +**RDS for Oracle** + +The following example uses SQL*Plus to connect to your RDS for Oracle database. + + + sqlplus 'user_name@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dns_name)(PORT=port))(CONNECT_DATA=(SID=database_name)))' + + SQL> 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)); + + SQL> INSERT INTO books_table VALUES (1, 'The Shining', 'Stephen King', 1977, 'Supernatural fiction'); + @@ -98 +126 @@ For case-sensitivity, use double quotes (" ") for schema, table, and column name -The following table shows the mapping of an RDS for MySQL data type to a corresponding destination data type. _Amazon RDS currently supports only these data types for zero-ETL integrations._ +The following tables show the mappings of RDS for MySQL, RDS for PostgreSQL, and RDS for Oracle data types to corresponding destination data types. _Amazon RDS currently supports only these data types for zero-ETL integrations._ @@ -105,0 +134,11 @@ You can't refresh zero-ETL integrations with an Amazon SageMaker lakehouse. Inst +###### Topics + + * RDS for MySQL + + * RDS for PostgreSQL + + * RDS for Oracle + + + + @@ -142,0 +182,216 @@ JSON | SUPER | Semistructured data or documents as values | None +### RDS for PostgreSQL + +Zero-ETL integrations for RDS for PostgreSQL don't support custom data types or data types created by extensions. + +RDS for PostgreSQL data type | Amazon Redshift data type | Description | Limitations +---|---|---|--- +array | SUPER | Semistructured data or documents as values | None +bigint | BIGINT | Signed eight-byte integer | None +bigserial | BIGINT | Signed eight-byte integer | None +bit varying(n) | VARBYTE(n) | Variable-length binary value up to 16,777,216 bytes | None +bit(n) | VARBYTE(n) | Variable-length binary value up to 16,777,216 bytes | None +bit, bit varying | VARBYTE(16777216) | Variable-length binary value up to 16,777,216 bytes | None +boolean | BOOLEAN | Logical boolean (true/false) | None +bytea | VARBYTE(16777216) | Variable-length binary value up to 16,777,216 bytes | None +char(n) | CHAR(n) | Fixed-length character string value up to 65,535 bytes | None +char varying(n) | VARCHAR(65535) | Variable-length character string value up to 65,535 characters | None +cid | BIGINT | Signed eight-byte integer | None +cidr | VARCHAR(19) | Variable-length string value up to 19 characters | None +date | DATE | Calendar date (year, month, day) | Values greater than 294,276 A.D. not supported +double precision | DOUBLE PRECISION | Double precision floating-point numbers | Subnormal values not fully supported +gtsvector | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +inet | VARCHAR(19) | Variable-length string value up to 19 characters | None +integer | INTEGER | Signed four-byte integer | None +int2vector | SUPER | Semistructured data or documents as values. | None +interval | INTERVAL | Duration of time | Only INTERVAL types that specify either a year to month or a day to second qualifier are supported. +json | SUPER | Semistructured data or documents as values | None +jsonb | SUPER | Semistructured data or documents as values | None +jsonpath | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +macaddr | VARCHAR(17) | Variable-length string value up to 17 characters | None +macaddr8 | VARCHAR(23) | Variable-length string value up to 23 characters | None +money | DECIMAL(20,3) | Currency amount | None +name | VARCHAR(64) | Variable-length string value up to 64 characters | None +numeric(p,s) | DECIMAL(p,s) | User-defined fixed precision value | + + * `NaN` values not supported + * Precision and scale must be explicitly defined and not greater than 38 (precision) and 37 (scale) + * Negative scale not supported + + +oid | BIGINT | Signed eight-byte integer | None +oidvector | SUPER | Semistructured data or documents as values. | None +pg_brin_bloom_summary | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +pg_dependencies | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +pg_lsn | VARCHAR(17) | Variable-length string value up to 17 characters | None +pg_mcv_list | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +pg_ndistinct | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +pg_node_tree | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +pg_snapshot | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +real | REAL | Single precision floating-point number | Subnormal values not fully supported +refcursor | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +smallint | SMALLINT | Signed two-byte integer | None +smallserial | SMALLINT | Signed two-byte integer | None +serial | INTEGER | Signed four-byte integer | None +text | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +tid | VARCHAR(23) | Variable-length string value up to 23 characters | None +time [(p)] without time zone | VARCHAR(19) | Variable-length string value up to 19 characters | `Infinity` and `-Infinity` values not supported +time [(p)] with time zone | VARCHAR(22) | Variable-length string value up to 22 characters | `Infinity` and `-Infinity` values not supported +timestamp [(p)] without time zone | TIMESTAMP | Date and time (without time zone) | + + * `Infinity` and `-Infinity` values not supported + * Values greater than `9999-12-31` not supported + * B.C. values not supported + + +timestamp [(p)] with time zone | TIMESTAMPTZ | Date and time (with time zone) | + + * `Infinity` and `-Infinity` values not supported + * Values greater than `9999-12-31` not supported + * B.C. values not supported + + +tsquery | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +tsvector | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +txid_snapshot | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None +uuid | VARCHAR(36) | Variable-length 36 character string | None +xid | BIGINT | Signed eight-byte integer | None +xid8 | DECIMAL(20, 0) | Fixed precision decimal | None +xml | VARCHAR(65535) | Variable-length string value up to 65,535 characters | None + +### RDS for Oracle + +**Unsupported data types** + +The following RDS for Oracle data types are not supported by Amazon Redshift: + + * `ANYDATA` + + * `BFILE` + + * `REF` + + * `ROWID` + + * `UROWID` + + * `VARRAY` + + * `SDO_GEOMETRY` + + * User-defined data types + + + + +**Data type differences** + +The following table shows the data type differences that affect a zero-ETL integration when RDS for Oracle is the source and Amazon Redshift is the target. + +RDS for Oracle data type | Amazon Redshift data type +---|--- +BINARY_FLOAT | FLOAT4 +BINARY_DOUBLE | FLOAT8 +BINARY | VARCHAR (Length) +FLOAT (P) | If precision is =< 24, then FLOAT4. If precision is > 24, then FLOAT8. +NUMBER (P,S) | If scale is => 0 and =< 37, then NUMERIC (p,s). If scale is => 38 and =< 127, then VARCHAR (Length). If scale is 0: + + * And precision is less than or equal to 2, then INT2. + * And precision is greater than 2 and less than or equal to 4, then INT2. + * And precision is greater than 4 and less than or equal to 9, then INT4. + * And precision is greater than 9, then NUMERIC (p,s). + * And precision is greater than or equal to scale, then NUMERIC (p,s). + +If scale is less than 0, then INT8. +DATE | If the scale is => 0 and =< 6, depending on the Redshift target column type, then one of the following: + + * TIMESTAMP (s) + * TIMESTAMPTZ (s) — If source timestamp contains a zone offset (such as in SQL Server or Oracle) it converts to UTC on insert/update. If it does not contain an offset, then time is considered in UTC already. + +If the scale is => 7 and =< 9, then VARCHAR (37). +INTERVAL_YEAR TO MONTH | If the length is 1–65,535, then VARCHAR (length in bytes). If the length is 65,536–2,147,483,647, then VARCHAR (65535). +INTERVAL_DAY TO SECOND | If the length is 1–65,535, then VARCHAR (length in bytes). If the length is 65,536–2,147,483,647, then VARCHAR (65535). +TIMESTAMP | If the scale is => 0 and =< 6, depending on the Redshift target column type, then one of the following: + + * TIMESTAMP (s) + * TIMESTAMPTZ (s) — If source timestamp contains a zone offset (such as in SQL Server or Oracle) it converts to UTC on insert/update. If it does not contain an offset, then time is considered in UTC already. + +If the scale is => 7 and =< 9, then VARCHAR (37). +TIMESTAMP WITH TIME ZONE | If the length is 1–65,535, then VARCHAR (length in bytes). If the length is 65,536–2,147,483,647, then VARCHAR (65535). +TIMESTAMP WITH LOCAL TIME ZONE | If the length is 1–65,535, then VARCHAR (length in bytes). If the length is 65,536–2,147,483,647, then VARCHAR (65535). +CHAR | If the length is 1–65,535, then VARCHAR (length in bytes). If the length is 65,536–2,147,483,647, then VARCHAR (65535). +VARCHAR2 | When the length is greater than 4,000 bytes, then VARCHAR (maximum LOB size). The maximum LOB size cannot exceed 63 KB. Amazon Redshift does not support VARCHARs larger than 64 KB. When the length is 4,000 bytes or less, then VARCHAR (length in bytes). +NCHAR | If the length is 1–65,535, then NVARCHAR (length in bytes). If the length is 65,536–2,147,483,647, then NVARCHAR (65535). +NVARCHAR2 | When the length is greater than 4,000 bytes, then NVARCHAR (maximum LOB size). The maximum LOB size cannot exceed 63 KB. Amazon Redshift does not support VARCHARs larger than 64 KB. When the length is 4,000 bytes or less, then NVARCHAR (length in bytes).