Creating table/view selection patterns
This topic walks you through selecting tables/views using patterns. For example, you can include all tables/views that belong to the HR schema except for one or two tables/views that you exclude. You can also only exclude one or more table/view schemas or tables/views. This replicates the entire endpoint, except for those tables/views that you excluded.
The following example shows a pattern that replicates all tables that are members of the dbo schema except for the dbo.PRODUCT_1% table.
Include dbo.%
Exclude dbo.PRODUCT_1%
You can also use the "_" wildcard character to match a single character. For example, specifying Exclude m_d% will exclude all tables that begin with m and end with d%, such as model or msdb.
Do not escape wildcard characters as this will instruct Replicate to interpret them as standard characters. As escape character conventions differ across databases, you should consult your database Help for guidance about supported escape characters. Some examples (where an underscore is the wildcard character) are as follows:
- MySQL and PostgreSQL - \\_
- Microsoft SQL Server - [_]
- Oracle - For Oracle, use the escapeCharacter internal parameter to define a custom escape character.
When you explicitly select tables/views, all selected tables/views are replicated in full unless you define transformations or filters for the table/view. If you need to make changes to the table/view structures in the target endpoint or if you only want to select specific columns, then you need to perform the procedures described in Defining transformations for a single table/view and Using filters respectively.
To view all of the tables/views included when you use a table selection pattern, click the Full Table List tab in Designer view. The Full Table List lists all of the tables/views included in any table pattern you defined as well as all explicitly selected tables/views. To view only patterns and explicitly selected tables/views, click the Patterns and Selected Tables tab in Designer view.
To create table/view selection patterns:
-
In the Designer view, in the Select Tables/Views dialog box, do any of the following:
- Select a schema from the Schema drop-down list. All tables/views that belong to that schema are included in the table/view selection pattern.
- Type the name or partial name of a table/view in the Table/View field. Any string that you enter here is included in the table/view selection pattern.
- If the table/view that you type here is a member of the schema you selected in the Schema drop-down list, then you only have to type the name of the table/view.
- If you did not select a schema or the table/view belongs to another schema, include the schema with the table name in the following format: HR.Employees, where HR is the schema.
- Click Include to include all of the tables/views that match the selection criteria.
- Click Exclude to exclude any tables that match the selection criteria.
- Click OK to close the Select Tables/Views dialog box.
- Click Save to make sure that Qlik Replicate saves the table/view information for this task.
Excluding specific tables from the replication task
You can easily exclude specific tables from being replicated.
To do this:
- Open the the Select Tables/Views dialog box.
-
Select a Schema and then click Search.
Any tables in that schema will be shown in the search results.
- Select the tables by adding them to the list on the right.
-
Click the Include button.
Include <schema_name>.% will be added to the Table Selection Patterns list.
- Select the Use exact table name check box.
- Type the name of the table you want to exclude in the Table/View field.
-
Click the Exclude button.
Exclude <schema_name>.<table_name> will be added to the Table Selection Patterns list.
- To exclude additional tables from the same schema, repeat Steps 6-7. To exclude tables from a different schema, clear the Use exact table name check box and then repeat Steps 2-7.
- Click OK to save your settings.