JDBC Multi Table Consumer can only read from one database. In some cases there is a requirement to get data from multiple databases - perhaps the table structure is similar or the databases contain related information.
There is one simple options to read from 2 databases in one pipeline -- use database-level Synonyms.
Example:
The following creates a synonym for the base table, "Product" in the AdventureWorks2012 database:
CREATE SYNONYM MyProduct
FOR AdventureWorks2012.Production.Product;
For SQL Server synonyms, more info can be found in the following links:
https://docs.microsoft.com/en-us/sql/relational-databases/synonyms/synonyms-database-engine?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-synonym-transact-sql?view=sql-server-2017
You can also use two pipelines by creating a pipeline for each database with the JDBC origin. If the pipeline is complex, you can use pipeline fragments to better manage the stages in the rest of the pipeline
Documentation for fragments (Control Hub 3.2.0 and up): https://streamsets.com/documentation/controlhub/3.2.0/help/pdesigner/#datacollector/UserGuide/Pipeline_Fragments/PipelineFragments.html%23concept_msg_4hf_ndb
3. Create one pipeline with JDC Multi-table origin for each database and feed into an 'combined' database or a queueing system such as Kafka. Then make a third pipeline that reads from the 'combined' database.
