Connect to Rest API and retrieve data

[amirmbhd]

Hello Everyone,
I am trying to retrieve data from the rest API of a website. They have listed the Rest API URLs here, but I have no idea how to connect to it and retrieve the data. I tried GET REQUEST and it seems like it’s working but I dont know what to do next to have the data in a readable format. Would appreciate any help.

[ArjenEX]

Hi @amirmbhd

There are many examples around on the forum and the KNIME hub regarding this topic. Could you please upload your workflow so that the community does not have to re-create what you already have made? People will be more included to help then

Also, please include your expected output.

[amirmbhd]

Hi @ArjenEX , thank you for your reply. I am sorry I am new to KNIME and I dont have a workflow because I dont really know where to even start. That website I listed is a database for all clinical trials in US and I am trying to retrive the data and eventually build a dashboard with it based on a certain criteria ( I have prior experience with building dasboards with Knime but I never used Rest API). I beleive I need to use GET REQUEST to connect but I dont know what to do after in order to read the data. I would appreciate any help.

[ArjenEX]

Hi @amirmbhd

The website you are referring to is very straightforward and gives you a lot of clues. For the query URL’s, they give three options.

Click on the demo option under Name, you wil end up on a page which lets you play with the API based on your input such as your desired search expression. I used the default setup here. Click on Send Request and the website will return a result. It also provides the query URL it used.

To use it in KNIME, open a workflow and add a Get Request node. Under Connection Settings → URL, fill in the URL that the website provided, like this:

Nothing else is required in this case because it’s an open API. Run the node and you’ll see the same result.

In this case, although there is a format parameter in the query, the API returns plain text instead of it being pre-formatted.

As such, I convert it to XML which is easier for text processing by the means of a String to XML node. For this, a small clean-up step is between. This now generates a column which is actually of type XML.

To get information out of it, use the Xpath node. Here, you can extract certain information based on it’s path/location within the XML.

Let’s assume you want to extract all the references. You can retrieve this information “section” as a whole by using /FullStudiesResponse/FullStudyList/FullStudy/Struct/Struct/Struct[@Name="ReferencesModule"] as Xpath query with output type node. Although you can do it in one go, this is visually more easy to understand. With another Xpath, I can extract the individual elements of the References, namely PMID, Type and Citation.

As per the above image, you will notice a certain pattern in here. The location of the ReferencePMID for the first record is /Struct/List/Struct/Field[1] for the second record is /Struct/List/Struct[2]/Field[1], third record is /Struct/List/Struct[3]/Field[1]. You the only changing number is Struct[], Field[1] always stays the same. You can therefore use a wildcard for the Struct[]. If you then select multiple rows as output, KNIME will automatically extract all PMID’s for you. A similar systematic applies to the other fields.

Final output:

This is just one example on how to go from the Get Request to information in a useable format. Your use case will ultimately determine how you should approach this.

Again, if you want to know more: search online on places like the KNIME Hub, this forum for more examples and instructions on Get Requests and Xpath’s. There are also some useful cheat sheets available online, like:

Xpath cheatsheet

You’ll have to go out there and invest some time and effort into learning this, I’m not able to teach you all the ropes in one forum post. If you are unfamiliar with these topics, I would recommend to do some studying on these subjects first and then come back to this topic and read it again, it will make a lot more sense

See the WF I created:
ClinicTrails GET Request plus processing.knwf (61.0 KB)

Hope this helps!

[amirmbhd]

@ArjenEX

Xpath cheatsheet

Thank you so much @ArjenEX ! This is more than enough for me to get started on this project. I really appreciate your time and consideration. and dont worry! now that I know where I need to focus on I will study it and will figure out the rest.