# Adding data to a source RDS database and querying it To finish creating a zero-ETL integration that replicates data from Amazon RDS into Amazon Redshift, you must create a database in the target destination. For connections with Amazon Redshift, connect to your Amazon Redshift cluster or workgroup and create a database with a reference to your integration identifier. Then, you can add data to your source RDS database and see it replicated in Amazon Redshift or Amazon SageMaker. **Topics** + [ ## Creating a target database ](#zero-etl.create-db) + [ ## Adding data to the source database ](#zero-etl.add-data-rds) + [ ## Querying your Amazon RDS data in Amazon Redshift ](#zero-etl.query-data-redshift) + [ ## Data type differences between RDS and Amazon Redshift databases ](#zero-etl.data-type-mapping) + [ ## DDL operations for RDS for PostgreSQL ](#zero-etl.ddl-postgres) ## Creating a target database Before you can start replicating data into Amazon Redshift, after you create an integration, you must create a database in your target data warehouse. This database must include a reference to the integration identifier. You can use the Amazon Redshift console or the Query editor v2 to create the database. For instructions to create a destination database, see [Create a destination database in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-using.creating-db.html#zero-etl-using.create-db). ## Adding data to the source database After you configure your integration, you can populate the source RDS database with data that you want to replicate into your data warehouse. **Note** There are differences between data types in Amazon RDS and the target analytics warehouse. For a table of data type mappings, see [Data type differences between RDS and Amazon Redshift databases](#zero-etl.data-type-mapping). First, connect to the source database using the MySQL client of your choice. For instructions, see [Connecting to your MySQL DB instance](USER_ConnectToInstance.md). Then, create a table and insert a row of sample data. **Important** Make sure that the table has a primary key. Otherwise, it can't be replicated to the target data warehouse. **RDS for MySQL** The following example uses the [MySQL Workbench utility](https://dev.mysql.com/downloads/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'); ``` **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\$1Plus 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'); ``` ## Querying your Amazon RDS data in Amazon Redshift After you add data to the RDS database, it's replicated into the destination database and is ready to be queried. **To query the replicated data** 1. Navigate to the Amazon Redshift console and choose **Query editor v2** from the left navigation pane. 1. Connect to your cluster or workgroup and choose your destination database (which you created from the integration) from the dropdown menu (**destination\$1database** in this example). For instructions to create a destination database, see [Create a destination database in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-using.creating-db.html#zero-etl-using.create-db). 1. Use a SELECT statement to query your data. In this example, you can run the following command to select all data from the table that you created in the source RDS database: ``` SELECT * from my_db."books_table"; ``` ![\[Run a SELECT statement within the query editor. The result is a single row of sample data that was added to the Amazon RDS database.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/zero-etl-redshift-editor.png) + `my_db` is the RDS database schema name. + `books_table` is the RDS table name. You can also query the data using the a command line client. For example: ``` 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 ``` **Note** For case-sensitivity, use double quotes (" ") for schema, table, and column names. For more information, see [enable\$1case\$1sensitive\$1identifier](https://docs.aws.amazon.com/redshift/latest/dg/r_enable_case_sensitive_identifier.html). ## Data type differences between RDS and Amazon Redshift databases 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.* If a table in your source database includes an unsupported data type, the table goes out of sync and isn't consumable by the destination target. Streaming from the source to the target continues, but the table with the unsupported data type isn't available. To fix the table and make it available in the target destination, you must manually revert the breaking change and then refresh the integration by running `[ALTER DATABASE...INTEGRATION REFRESH](https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_DATABASE.html)`. **Note** You can't refresh zero-ETL integrations with an Amazon SageMaker lakehouse. Instead, delete and try to create the integration again. **Topics** + [ ### RDS for MySQL ](#zero-etl.data-type-mapping-mysql) + [ ### RDS for PostgreSQL ](#zero-etl.data-type-mapping-postgres) + [ ### RDS for Oracle ](#zero-etl.data-type-mapping-oracle) ### RDS for MySQL [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/zero-etl.querying.html) ### RDS for PostgreSQL Zero-ETL integrations for RDS for PostgreSQL don't support custom data types or data types created by extensions. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/zero-etl.querying.html) ### 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. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/zero-etl.querying.html) ## DDL operations for RDS for PostgreSQL Amazon Redshift is derived from PostgreSQL, so it shares several features with RDS for PostgreSQL due to their common PostgreSQL architecture. Zero-ETL integrations leverage these similarities to streamline data replication from RDS for PostgreSQL to Amazon Redshift, mapping databases by name and utilizing the shared database, schema, and table structure. Consider the following points when managing RDS for PostgreSQL zero-ETL integrations: + Isolation is managed at the database level. + Replication occurs at the database level. + RDS for PostgreSQL databases are mapped to Amazon Redshift databases by name, with data flowing to the corresponding renamed Redshift database if the original is renamed. Despite their similarities, Amazon Redshift and RDS for PostgreSQL have important differences. The following sections outline Amazon Redshift system responses for common DDL operations. **Topics** + [ ### Database operations ](#zero-etl.ddl-postgres-database) + [ ### Schema operations ](#zero-etl.ddl-postgres-schema) + [ ### Table operations ](#zero-etl.ddl-postgres-table) ### Database operations The following table shows the system responses for database DDL operations. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/zero-etl.querying.html) ### Schema operations The following table shows the system responses for schema DDL operations. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/zero-etl.querying.html) ### Table operations The following table shows the system responses for table DDL operations. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/zero-etl.querying.html)