{"id":4069,"date":"2018-07-27T17:58:58","date_gmt":"2018-07-27T17:58:58","guid":{"rendered":"https:\/\/max-drake.cc\/?p=4069"},"modified":"2018-07-27T18:02:07","modified_gmt":"2018-07-27T18:02:07","slug":"knime-3-and-api-access","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=4069","title":{"rendered":"Knime 3. API access and workflows"},"content":{"rendered":"

In Knime you can go to the EXAMPLES from the Knime Server  in the KNIME EXPLORER Tab and open\/save to local workspace some examples to play with. You may need to see about getting some API keys for the examples.<\/p>\n

\"\"<\/p>\n

I tested  Translate using Google API <\/strong>Example. I needed an Google Translate API key and if you follow this Article<\/strong><\/a> on how to obtain one. Note, apparently its a paid API so beware about using it too much.<\/strong><\/p>\n

\"\"<\/p>\n

Anyway, the workflow works and you get the output. That is fine, but its not the data I want. So, how to set up something for your own data.<\/p>\n

In my case I look for an example that has a similar process to what I want. In the Examples the “Data API using the new REST nodes”<\/strong> looks like what I want (screenshot below).<\/p>\n

Just a note on those nodes, the GET requests work but they call:<\/p>\n

http:\/\/apps.who.int\/gho\/athena\/data\/GHO\/MORT_400.xml?profile=simple&filter=AGEGROUP:*;MGHEREG:WBDCP_HIINCOME;GHECAUSES:*;SEX:*<\/strong><\/em><\/p>\n

but when you try to put this in the browser it convers the Http to a Https<\/p>\n

https:\/\/apps.who.int\/gho\/athena\/data\/GHO\/MORT_400.xml?profile=simple&filter=AGEGROUP:*;MGHEREG:WBDCP_HIINCOME;GHECAUSES:*;SEX:*
\n<\/strong><\/em><\/p>\n

and gives you a 404 error<\/strong> of not found. But it works in Knime, weird!<\/p>\n

The top process (1) calls one URL and the bottom example (2) calls from a table a number of URL’s.<\/p>\n

I am interested in seeing about calling from a number of URL’s. I will think about using the Guardian API which will allow me to create multiple queries to test. I would also like to put the information in an Excel file and this to be read into the GET request.<\/p>\n

For the single instance  I will use the Stats NZ data . At this point I am not bothered about the Graphics at the end but of:<\/p>\n

1\/ Getting the Data via GET request<\/p>\n

2\/ Restructuring the data so that it is tabulated and structured so that I can export it to a CSV or excel file.<\/p>\n

\"\"<\/p>\n

Process<\/h2>\n

First, I will copy the 2 workflows in the above example.<\/p>\n

Then I will modify the single GET node to the Stats.NZ information. I will use the test information I used in Postman an copy it across to the Knime Node. Both the URL (with parameters).<\/p>\n

\"\"<\/p>\n

and the Request Header API key.<\/p>\n

\"\"<\/p>\n

Then I will right click the node to test the node by Executing it (F7)<\/p>\n

\"\"<\/p>\n

Then I will Right click the node to Get results to see what the output is. In this example it is one Cell of JSON output.<\/p>\n

\"\"<\/p>\n

So, good. We have the expected output.   <\/p>\n

With the list in Excel<\/h3>\n

I have tested the Guardian Get requests in Postman and then cut\/paste URL’s into an Excel Sheet.<\/p>\n

\"\"<\/p>\n

I then connected the Excel sheet to the Get request (note, first time it didn’t connect, so I did a Connection to Table writer and stored the output to a file, then used a Table Reader node to read the output from the Table Writer node (a bit convoluted but it worked))<\/p>\n

The 2nd time around it connected (I think I may not have executed the Excel Reader node so the Get Request could not find the URL’s column).<\/p>\n

So in the Excel Reader setup, after choosing the file, tick Table first row is column headers (item 4) and refresh the data(item 5) to see what the table will look like. Then Execute  the Excel Reader Node and then connect to Get Request Node.<\/p>\n

As you can see, after running the Get Request Node there were errors about the<\/p>\n

\"\"<\/p>\n

Anyway there were errors. The errors are shown in the console (items 10,11 & 12 in image below). Now it says illegal character at index 130. I hate counting up the characters to find index 130, so after doing this a few times I found this site for counting string lengths<\/strong><\/a>.\"\"So it identified the problem as a whitespace , so you need to replace whitespace with %20  and the string works fine in Knime.<\/p>\n

  The Get request node configures to the URLs column & also go to the Request Headers tab and put in your API key. \"\"<\/p>\n

The Get Request Output table shows the JSON column data from each of the rows of URL requests. \"\"<\/p>\n

Configuring Output from  GET Requests<\/h2>\n

So we have now got the Request data in Knime. The next part is to order the data in a useful manner.<\/p>\n

Now the next part I will just hop to, where I convert the JSON to XML. The reason is I have played around with the JSON export previously but could not get the JSON to modify to tabular data that I could put into a CSV or Excel table. If you can, well done.<\/p>\n

So I take the JSON data and send it to a JSON to XLM node and then use a XPath Node. You can configure the Xpath node to selectively take data from the XML data.<\/p>\n

In the JSON to XML node, although you can append an XML column, I find it easier just to replace the JSON column.<\/p>\n

\"\"<\/p>\n

With the Xpath node you need to configure what information you want to extract from the XML file. You use the Add Xpath and configure it to what you want to extract. If you have multiple groups of data (in this case the <item> data) you can iterate through it using the [*]. If you only want, say an attribute such as IDCATEGORY_Name from the first <item> group then you’d use [1] (maybe [0], not sure if JS notation where index is 0 or 1 but you’d work it out).<\/p>\n

\"\"<\/p>\n

So you do the above for all the data that you want to pull out of the Get request in a structured manner. Writing an Xpath Query for each column of data that you want.<\/p>\n

\"\"<\/p>\n

So process for the Stats NZ data as follows:<\/p>\n

\"\"<\/p>\n

Output to Excel file :  \"\"<\/p>\n

Using a Pivot Table to display the data (similar to Stats NZ Site) . I have noticed that there is a pivoting node<\/strong> and a database pivot<\/strong> too,  so the pivoting may have been able to be done in Knime.<\/p>\n

\"\" Also, instead of pushing to Excel, I could have pushed the Data to a DataBase, or else looked at some sort of Graphic display such as a Barchart.<\/p>\n

End thoughts<\/h3>\n

When I started playing with API’s this is where I started, playing with Knime and trying to process the data, unfortunately I did not get too far at the time.<\/p>\n

This time around, with the help of the Postman App<\/strong><\/a> I found it a lot easier to set up and use.<\/p>\n

I do like Knime, as one you have the workflow set-up you just need to hook your source data to it and it usually runs pretty smoothly.<\/p>\n

The next part will be to look at some graphical output the next time around. Also to explore some other API’s to see what data is out there to gather and play with.<\/p>\n","protected":false},"excerpt":{"rendered":"

In Knime you can go to the EXAMPLES from the Knime Server  in the KNIME EXPLORER Tab and open\/save to local workspace some examples to play with. You may need to see about getting some API keys for the examples. I tested  Translate using Google API Example. I needed an Google Translate API key and […]<\/p>\n","protected":false},"author":1,"featured_media":4073,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35,40,3,33],"tags":[],"class_list":["post-4069","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-api_json","category-data-analysis","category-data-extraction","category-knime-orange-rapidminer"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/07\/im03-1.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/07\/im03-1.jpg","author_info":{"display_name":"Max Drake","author_link":"https:\/\/max-drake.cc\/?author=1"},"_links":{"self":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/4069","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4069"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/4069\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/4073"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4069"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4069"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4069"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}