How to invoke a REST Call action in Data Integration Platform using SQL Server Trigger statements.
How to invoke a REST Call action in Data Integration Platform using SQL Server Trigger statements.
This KB article explains the steps to start and stop the data integration processors by invoking the REST API using the Triggers in the SQL server database.
Goal:
Whenever a new row is inserted or an existing record modified in a SQL server table, the trigger gets fired and the REST API will be invoked to start the Data Integration processors. Instead of scheduling the Data Integration Platform processors on a fixed time to pull records from the SQL table, here, we are going to fire the Trigger when a record is inserted or modified to invoke the Data Integration Platform REST API to start the processors to pull the new or modified record from the SQL server.
Start and Stop Data Integration Processor:
Data integration processor can be started or stopped using the REST API. Follow these steps to start and stop the processor.
Step-1: Get the processor version.
Using the ‘GET’ request, we can get the version of the data integration processor in its JSON response.
http://localhost:60017/dataintegration-api/processors/<ProcessorID>
|
The ProcessorID of the GenerateFlowFile processor is highlighted in the following image.
Step-2: Construct a REST API to start the processors.
Using the ‘PUT’ request, we can start the data integration processor. Construct the REST API to be invoked for starting the processor.
Components | Format | Example |
URL | http://<Hostname>:<PortNo>/dataintegration-api/processors/<ProcessorID> | http://localhost:60017/dataintegration-api/processors/a7f73042-0176-1000-cc85-dae5aa1bcd8f
|
Body | { "revision": { "version":<Version Number> // Version can get from the GET request. }, "component": { "id":<ProcessorID>, "state":"RUNNING" } } | { "revision": { "version":18 }, "component": { "id":"a7f73042-0176-1000-cc85-dae5aa1bcd8f", "state":"RUNNING" } } |
Step-3: Construct the REST URL to stop the processors.
Using the ‘PUT’ request, we can stop the data integration processor. Construct the REST API to be invoked for stopping the processor.
Components | Format | Example |
URL | http://<Hostname>:<PortNo>/dataintegration-api/processors/<ProcessorID> | http://localhost:60017/dataintegration-api/processors/a7f73042-0176-1000-cc85-dae5aa1bcd8f
|
Body | { "revision": { "version":<Version Number> // Version can get from the GET request. }, "component": { "id":<ProcessorID>, "state":"STOPPED" } } | { "revision": { "version":18 }, "component": { "id":"a7f73042-0176-1000-cc85-dae5aa1bcd8f", "state":"STOPPED" } } |
Step-4: State of the processor.
The processor is in a stopped state in the Data Integration Platform before the trigger gets fired.
Step-5: SQL Trigger query to invoke the processor:
Create a SQL Trigger for a SQL table using the following syntax as a reference.
Query syntax:
CREATE TRIGGER <trigger Name> ON <Table name> FOR INSERT AS declare @vPointer INT; declare @vResponseText VARCHAR(8000); declare @vStatus INT; declare @vStatusText VARCHAR(200); declare @Parametres nvarchar(3000); declare @Body as varchar(8000) = '{ "revision": { "version":<version number can get from step-1> }, "component": { "id":"<processorID>", "state":"RUNNING" } } ' SET @Parametres = '' EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer OUTPUT EXEC sp_OAMethod @vPointer, 'open',NULL, 'PUT', ‘http://<hostname>:60017/dataintegration-api/processors/<processorID>’, 0 EXEC sp_OAMethod @vPointer, 'setRequestHeader', null, 'Content-Type', 'application/json' Exec sp_OAMethod @vPointer, 'send', null, @Body EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT EXEC sp_OADestroy @vPointer GO |
Make sure you have created any SQL table before creating a trigger using the following query as a reference. |
Step-6: Invoking the REST API.
The trigger will get fired and the Data Integration processor will be started running as follows if any records are inserted or modified into the created SQL table.
Hi Mohammad Shojaeinia,
Please specify "OUTPUT" type for @vPointer parameter while creating OLE object instance. EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer OUTPUT
We have also updated the documentation.
Regards, Abinaya Karuppasamy
Hello again,
thanks for your great article and your fast reply to my last comment. Another problem I have is that I want to dynamically create the URL. For instance, I want to have: http://someurl.com/api/v1/{id}/feed and I should get the id from the query: (select ID from inserted).
How can I do this?
Regards
Hi Mohammad,
Please follow the below steps to dynamically generate the URL based id from the SQL select query.
- ExecuteSQL – Use this processor to execute your select query (select ID from inserted) for getting the id from your database.
- ConvertAvroToJSON – The result of the ExecuteSQL processor will be in AVRO format. Use this processor to convert Avro formatted data to JSON array.
- SplitJson – Use this processor to split the Json array into individual json element.
- EvaluateJsonPath – Use this processor to parse the value from json and store the id as flow file attribute.
- InvokeHTTP – Once the id is stored as flow file attribute in step 4. We can directly use that id dynamically in URL using this expression http://someurl.com/api/v1/${id}/feed
Regards, Abinaya.
Hello.
Thanks for your helpful article. I have the same problem, I want to send PUT request after insert into a table. So, I want to use a trigger for this purpose. But it seems there is a problem with this trigger definition. My transaction never executes! Because when it comes to "EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer" does not continue anymore. What should I do? My platform is windows 10 and SQL Server 2014.
Regards.