Creating destination databases in Amazon Redshift - Amazon Redshift

Creating destination databases in Amazon Redshift

To replicate data from your source into Amazon Redshift, you must create a database from your integration in Amazon Redshift.

Connect to your target Redshift Serverless workgroup or provisioned cluster and create a database with a reference to your integration identifier. This identifier is the value returned for integration_id when you query the SVV_INTEGRATION view.

Important

Before creating a database from your integration, your zero-ETL integration must be created and in the Active state on the Amazon RDS or Amazon Redshift console.

Creating a destination database in Amazon Redshift

Before you can start replicating data from your source into Amazon Redshift, create a database from the integration in Amazon Redshift. You can either create the database using the Amazon Redshift console or the query editor v2.

Creating a destination database using the Amazon Redshift console

  1. In the left navigation pane, choose Zero-ETL integrations.

  2. From the integration list, choose an integration.

  3. If you're using a provisioned cluster, you must first connect to the database. Choose Connect to database. You can connect using a recent connection, or by creating a new connection.

  4. To create a database from the integration, choose Create database from integration.

  5. Enter a Database name. The Integration ID and data warehouse name are pre-populated.

    For Aurora PostgreSQL sources, also enter the named database that you specified when creating your zero-ETL integration.

  6. Choose Create database.

Creating a destination database using the query editor v2

  1. Navigate to the Amazon Redshift console and choose Query editor v2.

  2. In the left panel, choose your Amazon Redshift Serverless workgroup or Amazon Redshift provisioned cluster, and then connect to it.

  3. To get the integration ID, navigate to the integration list on the Amazon Redshift console.

    Alternatively, run the following command to get the integration_id value:

    SELECT integration_id FROM SVV_INTEGRATION;
  4. Then, run the following command to create the database. By specifying the integration ID, you create a connection between the database and your source.

    Substitute integration_id with the value returned by the previous command.

    CREATE DATABASE destination_db_name FROM INTEGRATION 'integration_id';

    For Aurora PostgreSQL sources, you must also include a reference to the named database within the cluster that you specified when you created the integration. For example:

    CREATE DATABASE destination_db_name FROM INTEGRATION 'integration_id' DATABASE named_db;
Note

Only your integration source can update data in the database you create from your integration. To change the schema of a table, run DDL or DML commands against tables in the source. You can run DDL and DML commands against tables in the source, but you can only run DDL commands and read-only queries on the destination database.

For information about viewing the status of a destination database, see Managing zero-ETL integrations.

Add data to your source

After creating a destination database, you can add data to your source. To add data to your source, see one of the following topics: