DevinL Tracker

Re: Trouble with Request Body in Copy Data data flow

DevinL — Thu, 11 Jul 2024 17:10:46 GMT

Thanks for the reply.

Getting an error though: DataSource.Error: Web.Contents failed to get contents from 'https://apiaddressishere/search/credits' (422): Unprocessable Entity

Re: How do I flatten columns into rows in Copy Data activity?

DevinL — Wed, 10 Jul 2024 21:02:49 GMT

That did it.

The example code you gave was a perfect example!

I'm going to take this and find a way to make this work for any sized table. Not sure how, but that is what's next.

The hope is that when the API pulls the data and writes it to a JSON file in the datalake that this dataflow gen2 can take that JSON (no matter how many rows) and do the same conversion the put it into a datalake table.

Thanks for all your help!

Re: How do I flatten columns into rows in Copy Data activity?

DevinL — Wed, 10 Jul 2024 20:50:02 GMT

Thanks for the responses!

The data shouldn't have that many rows normally because the process would run daily and probably have less than 20 entries in a day. That being said, I'm not sure I could make this work if there were over 90 entries because it stops creating columns at a certain point.

There was an option in the transform pane to "Extract" text before/after a delimiter and you can also set it to ignore x number of delimiters. I did an extract with a delimiter of "." and ignored the first two and was left with the column names I needed.

The trouble now is transposing/pivoting to get the correct results in the correct columns.

It's getting a lot closer!

Re: How do I flatten columns into rows in Copy Data activity?

DevinL — Wed, 10 Jul 2024 16:45:56 GMT

Yes, I've tried using dataflow gen2 but I can't seem to find a way to get these number array columns into rows. In Dataflow gen2 I can import the JSON file and it will expand all of the individual arrays. I don't see a way to put the data into rows instead of one row with many of columns.

I tried to repost this into the Synapse forum, because I didn't mean to post in the Data Factory forum, but then a very "helpful" person merged the topics and stuck it back into the Data Factry forum. XD

Re: Trouble with Request Body in Copy Data data flow

DevinL — Wed, 10 Jul 2024 15:25:12 GMT

The REST API is for Sonar. I'm pulling back details for credits/debits/accounts/etc. and using the search feature in the sonar API to pull a specific date range. The purpose of doing it this way is to only bring in the new rows of data for each day rather than an entire replacement each day.

Re: How do I flatten columns into rows in Copy Data activity?

DevinL — Wed, 10 Jul 2024 03:21:23 GMT

Thanks for the reply, but this solution doesn't work in synapse Data Engineering.

Trouble with Request Body in Copy Data data flow

DevinL — Tue, 09 Jul 2024 21:36:58 GMT

I'm connecting to a REST API through a POST request and trying to pass some parameters (filters) in the request body:

{ "size": 100, "page": 1, "search": { "date_limits": [ { "field": "datetime", "from": "2024-06-25 00:00:00", "to": "2024-07-01 00:00:00" } ] } }

When I use this code in Postman (body -> raw), I'm able to pull back the correct filtered results. When I put this same code into Microsoft Fabric in a Copy Data activity (Request body), it doesn't return the filtered results.

I do not get an error, I just get the wrong unfiltered results. Also, I have to specify the size and page in the relative URL whereas in postman I can just include it in the body.

Any help is greatly appreciated!

Re: How do I flatten columns into rows in Copy Data activity?

DevinL — Tue, 09 Jul 2024 16:33:15 GMT

I have a data pull that creates a new column for each row of data as follows:

This is creating a column for each row, but what I need is for it to create a row for each numbered array. Essentially, I need to remove the data.results.#. and have a row for each numbered array.

I tried changing the destination on the right from "data.results.0.amount" to "amount", "data.results.1.amount" to "amount", etc. but then it throws an error saying that there are duplicate columns, so that's not the way to go. The closest thing I could find online was something about a "Flatten activity" and "unroll" which I don't see available in Microsoft Fabric.

Any ideas on how to do this?

How do I flatten columns into rows in Copy Data activity?

DevinL — Mon, 08 Jul 2024 18:03:42 GMT

I have a data pull that creates a new column for each row of data as follows:

This is creating a column for each row, but what I need is for it to create a row for each numbered array. Essentially, I need to remove the data.results.#. and have a row for each numbered array.

Any ideas on how to do this?

Trouble with Request Body in Copy Data data flow

DevinL — Tue, 02 Jul 2024 18:53:00 GMT

I'm connecting to a REST API through a POST request and trying to pass some parameters (filters) in the body:

{ "size": 100, "page": 1, "search": { "date_limits": [ { "field": "datetime", "from": "2024-06-25 00:00:00", "to": "2024-07-01 00:00:00" } ] } }

I do not get an error, I just get the wrong unfiltered results.

If I use a Web activity and put the same code into the body, I get the correct results. I just don't know how to copy data from a web activity to a table in my datalake. Maybe this is the correct way of doing this? I'm not sure.

Any help is greatly appreciated!

Re: Looping through URL fabric pipeline

DevinL — Tue, 25 Jun 2024 21:40:36 GMT

@v-cboorla-msft I may have figured out a solution, although it seems extremely slow. The address for the API call has to be concatenated with the variable call. @concat('/api/accounts/', variables('cust_id'),'/transactions/debits') was what I used to make it work.

This works, but it takes about 20 seconds per pull. Let's say there are 50,000 records to pull. That would put this at around 11 days to pull all the data. Is there no faster way to do this within a fabric pipeline?

Re: Looping through URL fabric pipeline

DevinL — Tue, 21 May 2024 17:14:00 GMT

v-cboorla-msft I've tried using the Web Activity and I get a 404 error. I also tried doing a Copy Data and used the same address //address.com/api/v1/accounts/@variables('cust_id')/transactions/debits and again got a 404 error.

It doesn't show it on the web activity error details, but it does on the copy data details that the variable part of the address is not being replaced.

The error shows the address as:
//address.com/api/v1/accounts/@variables('cust_id')/transactions/debits

When it should show as:
//address.com/api/v1/accounts/1234/transactions/debits

Re: Looping through URL fabric pipeline

DevinL — Wed, 15 May 2024 18:52:58 GMT

Thank you again, cboorla.

I'm able to query fine now and the correct values are being passed from the lookup to the ForEach.

I'm unclear at this point what should be in the Set variable 1 within the ForEach. Also, I'm guessing that within the ForEach is where I would put my REST API pull with the variables('id') in the address. I guess I'm confused on how to place the lookup value into the variable('id').

Re: Looping through URL fabric pipeline

DevinL — Tue, 14 May 2024 18:36:36 GMT

Thank you for your response!

I've tried to follow your steps but unfortunately there isn't an option to query when selecting a DataLake. I tried doing "Dynamic Content" with "select id from mytablename" and it throws an error stating that my table doesn't exist.

I can select the table's name in a dropdown, but I'm unsure how to only pass the column I need into the ForEach.

Thanks again for taking the time to help me out!

Looping through URL fabric pipeline

DevinL — Tue, 14 May 2024 20:48:18 GMT

Background: I've pulled all account information from a REST API using a data pipeline and stored it in a datalake table called "Accounts". I now want to pull debit transaction information for each id in the Accounts table and store the data in a new datalake table called "Debits". The URL that I would need to use is something like: //mysource.com/api/accounts/{id}/transactions/debits/

The API doesn't have an option to pull all debits at once, but requires you to replace the {id} with the individual ID's that are now in my Source_Accounts table in the id column.

How does someone go about automating this in Microsoft Fabric?