I'm working on a Azure Data Factory Pipeline and have following challenge currently:
I'd like to make an API call (Post) which requires some data with the syntax of array
and in it, multiple objects
.
Now - both, the data retrieving (from SQL
db) and API
call work when used independently (In case of the API call: I've been using hardcoded mock data for the body). The challenge is in connecting both of them. That means:
I'd like to get multiple rows out of a SQL table, convert them to the required json structure and fill that data then into the API call.
See picture below:
In simple steps explained again:
- Get rows from SQL table
- Convert each row into an object e.g.
{ "somekey": valueOfRow}
- Collect all objects in an
array
- Provide
array
toAPI
call
I'm just unsure how to proceed with the connection.
Additional Information
As requested, some further detailed information.
Currently the API call uses following hardcoded mockdata:
[{"idType": "ID_ISIN", "idValue": "US0123456789" }]
From the dataflow I'll get rows with one column called isin
(with row values such as US0123456789
)
The goal is to fill API's body dynamically such that it receives something like this:
[
{
"idType": "ID_ISIN",
"idValue": "US0123456789"
},
{
"idType": "ID_ISIN",
"idValue": "US9876543210"
},
{...}
]
I saw that one can achieve something similiar with SQL
query - see: https://docs.microsoft.com/en-us/azure/azure-sql/database/json-features?view=azuresql
But I'd miss the part "idType": "ID_ISIN"
in each row/object.
CodePudding user response:
Using dataflow to retrieve records and create an array of objects (where each object is row from SQL table) might not be the right way to achieve your requirement.
We can use
Lookup
activity which returns the rows based on given table or query as an array of objects. Look at the following demonstration.The following is the data in my table
repro1
which I am going to use (we only need ID_ISIN column).
In your data factory studio, create a new lookup activity. Create a new source dataset for your SQL database table and click OK.
Uncheck
First Row only
checkbox. There is no need to select a table here, instead we can just query from the required table based on requirement. Therefore, check theQuery
box.Now, I have written the following query to extract data as required from the
repro1
table (such that it would give results which are in line with hardcoded mock data).
select 'ID_ISIN' as idType, ID_ISIN as idValue from repro1
Now when I debug the pipeline, you can see the debug output of the lookup activity.
The above output consists of lot of other information along with the rows returned as a result of given query.
Now you can directly retrieve the above highlighted array of objects using the following dynamic content (if your lookup activity name is Lookup1).
@activity('Lookup1').output.value
You can use the above dynamic content (an array of objects) to fill your API call's body.