How to ETL data from tables matching a wildcard

Overview

Etlworks supports extracting data from the database objects such as tables and views, matching the wildcard name, and loading it into a different database or schema.

Process

In Connections

Access Connections here.

Step 1. Create a source Connection for the relational database and test it.

Step 2. Create a destination Connection for the relational database and test it.

In Flows

Access Flows here.

Step 3. Create a new Database to database Flow.

Step 4. Add a new transformation and select FROM and TO Connections.

Step 5. Specify the fully qualified wildcard source name in FROM. For example, public.*. Configure the destination name in TO using either only an asterisk character (*) or any part of the destination name with the asterisk character (public.*).

The system will substitute the asterisk character with the actual source object name (excluding database and schema names). For example, if the actual source name is test.dbo.patient and the destination is public.*, the system will load data into the public.patient.

By default, all tables and views that match the wildcard name in FROM will be included. If you want to exclude specific database objects, enter a comma-separated list of objects to exclude in the Exclude objects field. If you want to include only specific database objects, enter a comma-separated list of the objects in the Include objects field. 

Read how to programmatically change the destination (TO) name.

Step 6. Optionally, specify the exclusion list in the Exclude objects field under MAPPING > Parameters. Use fully-qualified tables names. The wildcard names are allowed. 

To exclude all views, enter all views in the Exclude objects field.

To exclude all tables, enter all tables in the Exclude objects field.

Step 7. Optionally, specify the inclusion list in the Include objects field under MAPPING > Parameters. Use fully-qualified tables names. The wildcard names are allowed.

Step 8. Optionally, configure the Source query. You can use the token {table} in the Source query to do this.

Step 9. Configure MERGE (UPSERT) if needed.

Do not enter Lookup Fields. Instead, enable Predict Lookup Fields.

An alternative to enabling Predict Lookup Fields (which is not always accurate) is specifying the list of table=fields pairs in the Lookup Fields. Use the fully-qualified table names and ; as a separator between table=field pairs.

Example:

test1.inventory=inventory_id,database_name;
test1.payment=payment_id,database_name;
test1.rental=rental_id,database_name;

 

Step 10. Configure Change Replication if needed.

Use the token {TABLE} in the High Watermark Field Value if you are calculating HWM. The system will substitute a token with the actual destination table name.

Step 11. If you want to do a full refresh of the destination tables, you can enter the following SQL in the Before SQL field:

truncate table {TABLE}

Step 12. Enable parallel processing if you want this Flow to work faster. Remember that parallel processing is not always going to be an option, specifically when the destination database objects already exist and they do have foreign constraints between each other. Use the following technique to limit the number of parallel threads.   

Step 13. Save the Flow and execute it manually.

In Schedules

Step 14. Schedule the Flow to be executed periodically.