AWS AmazonRDS documentation change
Summary
Modified statement about regular expression support in filters from 'Using regular expressions...' to 'You can't use regular expressions...'
Security assessment
This is a clarification of feature limitations without any security context. The change simply makes existing restrictions more explicit without introducing or modifying security controls.
Diff
diff --git a/AmazonRDS/latest/UserGuide/zero-etl.filtering.md b/AmazonRDS/latest/UserGuide/zero-etl.filtering.md index 1674ba8ac..552f826e9 100644 --- a//AmazonRDS/latest/UserGuide/zero-etl.filtering.md +++ b//AmazonRDS/latest/UserGuide/zero-etl.filtering.md @@ -225,0 +226,115 @@ The following examples demonstrate how data filtering works for RDS for Oracle z +### Case sensitivity considerations + +Oracle Database and Amazon Redshift handle object name casing differently, which affects both data filter configuration and target queries. Note the following: + + * Oracle Database stores database, schema, and object names in uppercase unless explicitly quoted in the `CREATE` statement. For example, if you create `mytable` (no quotes), the Oracle data dictionary stores the table name as `MYTABLE`. If you quote the object name, the data dictionary preserves the case. + + * Zero-ETL data filters are case sensitive and must match the exact case of object names as they appear in the Oracle data dictionary. + + * Amazon Redshift queries default to lowercase object names unless explicitly quoted. For example, a query of `MYTABLE` (no quotes) searches for `mytable`. + + + + +Be mindful of the case differences when you create the Amazon Redshift filter and query the data. + +#### Creating an uppercase integration + +When you create a table without specifying the name in double quotes, the Oracle database stores the name in uppercase in the data dictionary. For example, you can create `MYTABLE` using any of the following SQL statements. + + + CREATE TABLE REINVENT.MYTABLE (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + CREATE TABLE reinvent.mytable (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + CREATE TABLE REinvent.MyTable (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + CREATE TABLE reINVENT.MYtabLE (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + +Because you didn't quote the table name in the preceding statements, the Oracle database stores the object name in uppercase as `MYTABLE`. + +To replicate this table to Amazon Redshift, you must specify the uppercase name in your data filter of your `create-integration` command. The Zero-ETL filter name and Oracle data dictionary name must match. + + + aws rds create-integration \ + --integration-name upperIntegration \ + --data-filter "include: ORCL.REINVENT.MYTABLE" \ + ... + +By default, Amazon Redshift stores data in lowercase. To query `MYTABLE` in the replicated database in Amazon Redshift, you must quote the uppercase name `MYTABLE` so that it matches the case in the Oracle data dictionary. + + + SELECT * FROM targetdb1."REINVENT"."MYTABLE"; + +The following queries don't use the quoting mechanism. They all return an error because they search for an Amazon Redshift table named `mytable`, which uses the default lowercase name, but the table is named `MYTABLE` in the Oracle data dictionary. + + + SELECT * FROM targetdb1."REINVENT".MYTABLE; + SELECT * FROM targetdb1."REINVENT".MyTable; + SELECT * FROM targetdb1."REINVENT".mytable; + +The following queries uses the quoting mechanism to specify a mixed case name. The queries all return an error because they search for an Amazon Redshift table that isn't named `MYTABLE`. + + + SELECT * FROM targetdb1."REINVENT"."MYtablE"; + SELECT * FROM targetdb1."REINVENT"."MyTable"; + SELECT * FROM targetdb1."REINVENT"."mytable"; + +#### Creating a lowercase integration + +In the following alternative example, you use double quotes to store the table name in lowercase in the Oracle data dictionary. You create `mytable` as follows. + + + CREATE TABLE REINVENT."mytable" (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + +The Oracle database stores the table name as `mytable` in lowercase. To replicate this table to Amazon Redshift, you must specify the lowercase name `mytable` in your Zero-ETL data filter. + + + aws rds create-integration \ + --integration-name lowerIntegration \ + --data-filter "include: ORCL.REINVENT.mytable" \ + ... + +When you query this table in the replicated database in Amazon Redshift, you can specify the lowercase name `mytable`. The query succeeds because it searches for a table named `mytable`, which is the table name in the Oracle data dictionary. + + + SELECT * FROM targetdb1."REINVENT".mytable; + +Because Amazon Redshift defaults to lowercase object names, the following queries also succeed in finding `mytable`. + + + SELECT * FROM targetdb1."REINVENT".MYtablE; + SELECT * FROM targetdb1."REINVENT".MYTABLE; + SELECT * FROM targetdb1."REINVENT".MyTable; + +The following queries use the quoting mechanism for the object name. They all return an error because they search for an Amazon Redshift table whose name is different from `mytable`. + + + SELECT * FROM targetdb1."REINVENT"."MYTABLE"; + SELECT * FROM targetdb1."REINVENT"."MyTable"; + SELECT * FROM targetdb1."REINVENT"."MYtablE"; + +#### Create a table with a mixed-case integration + +In the following example, you use double quotes to store the table name in lowercase in the Oracle data dictionary. You create `MyTable` as follows. + + + CREATE TABLE REINVENT."MyTable" (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + +The Oracle database stores this table name as `MyTable` with mixed case. To replicate this table to Amazon Redshift, you must specify the mixed case name in the data filter. + + + aws rds create-integration \ + --integration-name mixedIntegration \ + --data-filter "include: ORCL.REINVENT.MyTable" \ + ... + +When you query this table in the replicated database in Amazon Redshift, you must specify the mixed case name `MyTable` by quoting the object name. + + + SELECT * FROM targetdb1."REINVENT"."MyTable"; + +Because Amazon Redshift defaults to lowercase object names, the following queries don't find the object because they are searching for the lowercase name `mytable`. + + + SELECT * FROM targetdb1."REINVENT".MYtablE; + SELECT * FROM targetdb1."REINVENT".MYTABLE; + SELECT * FROM targetdb1."REINVENT".mytable; + @@ -228 +343 @@ The following examples demonstrate how data filtering works for RDS for Oracle z -Using regular expressions in the filter value for database name, schema, or table name, is not supported in RDS for Oracle integrations. +You can't use regular expressions in the filter value for database name, schema, or table name in RDS for Oracle integrations.