SQL Query - Staging table

asked 2018-11-30 04:42:10 -0500

LuisFe Melo gravatar image

Hi, I try to ingest one table from a SQL Server database to another(Postgres). My idea is to create a Pipeline to ingest all the data and weekly create a staging table with the new records/updates. I already have a SQL query that identifies the new records. I try to use a "JDBC Query Executor" but I'm not sure how to execute it, I was connecting this executor with a "Dev Data generation" (with only one record) but my query was not executed... Do you have any suggestion?

edit retag flag offensive close merge delete


Have you looked into or considered using JDBC Query Consumer origin and JDBC Producer destination instead? https://streamsets.com/documentation/datacollector/latest/help/datacollector/UserGuide/Getting_Started/GettingStarted_Title.html#concept_htw_ghg_jq

iamontheinet gravatar imageiamontheinet ( 2018-11-30 09:50:32 -0500 )edit

That is my second option. However, I would like to put all the steps together in the same pipeline (1. Copy the table from SQL Server to Postgres into a staging table 2. Detect new records/updates from this and copy them to the real table). If I'm not wrong, there is only one producer that I can use

LuisFe Melo gravatar imageLuisFe Melo ( 2018-12-02 02:10:11 -0500 )edit

Yes, you can have multiple destinations in the same pipeline. However, I'd encourage you to look at how SDC is able to process updated records automatically based on timestamp offset, for example, in JDBC Query Consumer origin and change data capture (CDC) records. Read the docs for more details.

iamontheinet gravatar imageiamontheinet ( 2018-12-03 09:30:47 -0500 )edit