Already have an account? Sign in

Replicating Database Views

Replicating a database view is almost the same as replicating a database table. In this guide, we’ll cover the database integrations that support views and the additional steps required to replicate a database view.

Supported databases

Database views can be replicated from the following database integrations only:

• Amazon Aurora MySQL RDS

• Amazon Aurora MySQL RDS

• Amazon Aurora PostgreSQL RDS

• Amazon DynamoDB

• Amazon Microsoft SQL Server RDS

• Amazon MySQL RDS

• Amazon Oracle RDS

• Amazon PostgreSQL RDS

• Amazon PostgreSQL RDS

• Google CloudSQL MySQL

• Google CloudSQL MySQL

• Google CloudSQL PostgreSQL

• Google CloudSQL PostgreSQL

• Heroku

• MariaDB

• Microsoft Azure MySQL

• Microsoft Azure SQL Database

• Microsoft SQL Server

• MySQL

• Oracle

• PostgreSQL

• PostgreSQL

Setting a view to replicate

To replicate a database view, follow these steps:

• Step 1: Verify the database user’s permissions

• Step 2: Select the view in Stitch

• Step 3: Define the view’s Primary Key

• Step 4: Define the view’s Replication Method

• Step 5: Save the view’s settings

Step 1: Verify the database user's permissions

To replicate the view, the Stitch database user must have the appropriate level of permissions to access it.

If you don’t see the view you want in the Stitch app, it may be because the Stitch user has insufficient permissions. Verify that the Stitch user’s permissions and, if necessary, grant any that are missing.

For a refresher on the permissions Stitch needs, refer to the articles linked below:

• Amazon Aurora MySQL RDS

• Amazon Aurora MySQL RDS

• Amazon Aurora PostgreSQL RDS

• Amazon DynamoDB

• Amazon Microsoft SQL Server RDS

• Amazon MySQL RDS

• Amazon Oracle RDS

• Amazon PostgreSQL RDS

• Amazon PostgreSQL RDS

• Google CloudSQL MySQL

• Google CloudSQL MySQL

• Google CloudSQL PostgreSQL

• Google CloudSQL PostgreSQL

• Heroku

• MariaDB

• Microsoft Azure MySQL

• Microsoft Azure SQL Database

• Microsoft SQL Server

• MySQL

• Oracle

• PostgreSQL

• PostgreSQL

Step 2: Select the view in Stitch

Note:Parent objects - like the database containing the view - must also be set to replicate. If they aren’t, set them to replicate before continuing.

1. Click into the database integration from the Stitch Dashboard page.
2. Navigate to the view you want to replicate.
3. Click the icon next to theSync Status column to set the view to replicate.
4. Once a view has been set to replicate, theView Settings page will display.

Step 3: Define the view's Primary Key

Next, you’ll define the Primary Key setting for the view. There are two options:

• No Primary Key: The view will be replicated usingAppend-Only Replication. Existing rows will not be updated, and any new rows will be appended to the end of the table.
• Custom Primary Key: If selected, the field or fields you define will be used as the view’s Primary Keys. To add additional fields, click theAdd button.

Note: You can change the Primary Key setting - including adding or removing fields to the Custom Primary Key - at any time, but doing so will require a full re-replication of the view. This is to ensure that there aren’t any gaps in the data.

Step 4: Define the view's Replication Method

The last step is to define the view’s Replication Method. Replication Methods tell Stitch how to extract data from a source table, which will directly impact your overall row usage.

The following Replication Methods are available for use with database views:

• Key-based Incremental Replication: This method will only replicate new and updated data, based on the Replication Key column you define.A Replication Key is required to use this method. Refer to theReplication Key documentation for more info.

• Full Table Replication: This method will replicate the view in full during every replication job.

Step 5: Save the view's settings

Once you’ve defined the view’s Primary Key(s) and Replication Method, click theSave Settings button.

During the next replication job, Stitch will replicate the view according to the settings you defined.

Did this article help? If you have questions or feedback, feel free tosubmit a pull request with your suggestions,open an issue on GitHub, orreach out to us.