# Adding data to a source Aurora DB cluster and querying it To finish creating a zero-ETL integration that replicates data from Amazon Aurora 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 Aurora DB cluster and see it replicated in Amazon Redshift or Amazon SageMaker. **Topics** + [Creating a target database](#zero-etl.create-db) + [Adding data to the source DB cluster](#zero-etl.add-data-rds) + [Querying your Aurora data in Amazon Redshift](#zero-etl.query-data-redshift) + [Data type differences between Aurora and Amazon Redshift databases](#zero-etl.data-type-mapping) + [DDL operations for Aurora 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 DB cluster After you configure your integration, you can populate the source Aurora DB cluster with data that you want to replicate into your data warehouse. **Note** There are differences between data types in Amazon Aurora and the target analytics warehouse. For a table of data type mappings, see [Data type differences between Aurora and Amazon Redshift databases](#zero-etl.data-type-mapping). First, connect to the source DB cluster using the MySQL or PostgreSQL client of your choice. For instructions, see [Connecting to an Amazon Aurora DB cluster](Aurora.Connecting.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. The pg\$1dump and pg\$1restore PostgreSQL utilities initially create tables without a primary key and then add it afterwards. If you're using one of these utilities, we recommend first creating a schema and then loading data in a separate command. **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'); ``` **PostgreSQL** The following example uses the `[psql](https://www.postgresql.org/docs/current/app-psql.html)` PostgreSQL interactive terminal. When connecting to the cluster, include the named database that you specified when creating the integration. ``` 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'); ``` ## Querying your Aurora data in Amazon Redshift After you add data to the Aurora DB cluster, 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 Aurora DB cluster: ``` 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/AuroraUserGuide/images/zero-etl-redshift-editor.png) + `my_db` is the Aurora database schema name. This option is only needed for MySQL databases. + `books_table` is the Aurora 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 Aurora and Amazon Redshift databases The following tables show the mappings of Aurora MySQL and Aurora PostgreSQL data types to corresponding destination data types. *Amazon Aurora currently supports only these data types for zero-ETL integrations.* If a table in your source DB cluster 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** + [Aurora MySQL](#zero-etl.data-type-mapping-mysql) + [Aurora PostgreSQL](#zero-etl.data-type-mapping-postgres) ### Aurora MySQL [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/zero-etl.querying.html) ### Aurora PostgreSQL Zero-ETL integrations for Aurora 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/AuroraUserGuide/zero-etl.querying.html) ## DDL operations for Aurora PostgreSQL Amazon Redshift is derived from PostgreSQL, so it shares several features with Aurora PostgreSQL due to their common PostgreSQL architecture. Zero-ETL integrations leverage these similarities to streamline data replication from Aurora PostgreSQL to Amazon Redshift, mapping databases by name and utilizing the shared database, schema, and table structure. Consider the following points when managing Aurora PostgreSQL zero-ETL integrations: + Isolation is managed at the database level. + Replication occurs at the database level. + Aurora 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 Aurora 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/AuroraUserGuide/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/AuroraUserGuide/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/AuroraUserGuide/zero-etl.querying.html)