Adding data to a source RDS database and querying it in Amazon Redshift - Amazon Relational Database Service
Creating a destination database in Amazon RedshiftAdding data to the source databaseQuerying your Amazon RDS data in Amazon RedshiftData type differences
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Adding data to a source RDS database and querying it in Amazon Redshift

This is prerelease documentation for Amazon RDS zero-ETL integrations with Amazon Redshift, which is in preview release. The documentation and the feature are both subject to change. We recommend that you use this feature only in test environments, and not in production environments. For preview terms and conditions, see Betas and Previews in Amazon Service Terms.

To finish creating a zero-ETL integration that replicates data from Amazon RDS into Amazon Redshift, you must create a destination database in Amazon Redshift.

First, 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.

Creating a destination database in Amazon Redshift

Before you can start replicating data into Amazon Redshift, after you create an integration, you must create a destination database in your target data warehouse. This destination 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.

Adding data to the source database

After you configure your integration, you can add some data to the RDS database that you want to replicate into your Amazon Redshift data warehouse.

Note

There are differences between data types in Amazon RDS and Amazon Redshift. For a table of data type mappings, see Data type differences between RDS and Amazon Redshift databases.

First, connect to the source database using the MySQL client of your choice. For instructions, see Connecting to a DB instance running the MySQL database engine.

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 following example uses the MySQL Workbench utility.

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');

Querying your Amazon RDS data in Amazon Redshift

After you add data to the RDS database, it's replicated into Amazon Redshift 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.

  2. Connect to your cluster or workgroup and choose your destination database (which you created from the integration) from the dropdown menu (destination_database in this example). For instructions to create a destination database, see Create a destination database in Amazon Redshift.

  3. 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";

    • 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_case_sensitive_identifier.

Data type differences between RDS and Amazon Redshift databases

The following table shows the mapping of an RDS for MySQL data type to a corresponding Amazon Redshift data type. 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 Amazon Redshift 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 Amazon Redshift, you must manually revert the breaking change and then refresh the integration by running ALTER DATABASE...INTEGRATION REFRESH.

RDS for MySQL

RDS for MySQL data type Amazon Redshift data type Description Limitations
INT INTEGER Signed four-byte integer
SMALLINT SMALLINT Signed two-byte integer
TINYINT SMALLINT Signed two-byte integer
MEDIUMINT INTEGER Signed four-byte integer
BIGINT BIGINT Signed eight-byte integer
INT UNSIGNED BIGINT Signed eight-byte integer
TINYINT UNSIGNED SMALLINT Signed two-byte integer
MEDIUMINT UNSIGNED INTEGER Signed four-byte integer
BIGINT UNSIGNED DECIMAL(20,0) Exact numeric of selectable precision
DECIMAL(p,s) = NUMERIC(p,s) DECIMAL(p,s) Exact numeric of selectable precision

Precision greater than 38 and scale greater than 37 not supported
DECIMAL(p,s) UNSIGNED = NUMERIC(p,s) UNSIGNED DECIMAL(p,s) Exact numeric of selectable precision

Precision greater than 38 and scale greater than 37 not supported
FLOAT4/REAL REAL Single precision floating-point number
FLOAT4/REAL UNSIGNED REAL Single precision floating-point number
DOUBLE/REAL/FLOAT8 DOUBLE PRECISION Double precision floating-point number
DOUBLE/REAL/FLOAT8 UNSIGNED DOUBLE PRECISION Double precision floating-point number
BIT(n) VARBYTE(8) Variable-length binary value
BINARY(n) VARBYTE(n) Variable-length binary value
VARBINARY(n) VARBYTE(n) Variable-length binary value
CHAR(n) VARCHAR(n) Variable-length string value
VARCHAR(n) VARCHAR(n) Variable-length string value
TEXT VARCHAR(65535) Variable-length string value up to 65535 bytes
TINYTEXT VARCHAR(255) Variable-length string value up to 255 bytes
ENUM VARCHAR(1020) Variable-length string value up to 1020 bytes
SET VARCHAR(1020) Variable-length string value up to 1020 bytes
DATE DATE Calendar date (year, month, day)
DATETIME TIMESTAMP Date and time (without time zone)
TIMESTAMP(p) TIMESTAMP Date and time (without time zone)
TIME VARCHAR(18) Variable-length string value up to 18 bytes
YEAR VARCHAR(4) Variable-length string value up to 4 bytes
JSON SUPER Semistructured data or documents as values