19 Replies Latest reply: May 31, 2018 5:50 AM by Nacho Bibián RSS

    POST Data using REST Connector

    Kevin Vaccarin

      Hello Community,

       

      I am trying to POST data in from Qlik (sense or view) in BDD using REST connector.

      When I enter the url, the authorisation token , and the format, and press "TEST CONNECTION", I create my lines in the BDD.

      But when I want to "CREATE" the connection I have an error.

       

      I would like to create every new customer.

      Any idea?

        • Re: POST Data using REST Connector
          Steve Dark

          Hi Kevin,

           

          What error is it that you are receiving?

           

          Generally when I pass values into POST fields I put the key value pairs in the boxes further down, rather than putting in the body text.

           

          Using the request body field spaces could well be an issue, I guess.

           

          Have you proven the connection, authorisation etc. using a tool like PostMan?

            • Re: POST Data using REST Connector
              Kevin Vaccarin

              Hi Steve,

               

              Thanks you for your answer,

              I try my request with postman and it work fine.

              my probleme is that I have 20 new client by day and I don't find a way to loop in Postman, so I tried with Qlik.

              My query header are below (the same as Postman):

              And I got this error:

              But the line is created...

                • Re: POST Data using REST Connector
                  Kevin Vaccarin

                  And I also delete the field spaces in my request

                  • Re: POST Data using REST Connector
                    Steve Dark

                    Try removing the Request body and then putting those values in the Query Parameters Name and Value pairs.  When you Test Connection this should then enter the values and prove that approach works.

                     

                    To have it so that you can inject different values each time you will need to clear out those parameters again (that was just to test) and then overwrite them at load time with a WITH CONNECTION statement.  This allows you to overwrite parts of your connection with new values.  The syntax is:

                     

                    FROM JSON (wrap on) "root" PK "__KEY_root"

                    WITH CONNECTION (

                          URL "https://api.twitter.com/oauth2/token",

                          HTTPHEADER "content-type" "application/x-www-form-urlencoded",

                          HTTPHEADER "Authorization" "Basic $(vBASE64Key)",

                          QUERY "grant_type" "client_credentials"

                        );

                     

                    In your case you will have many QUERY rows, but the rest of the connection may be fine stored in the REST connector, so don't need to be included.  Basically the values in the REST connector are either added to (in the case or HTTPHEADER or QUERY rows) or replaced (URL).

                     

                    Hope that helps.


                    Steve

                      • Re: POST Data using REST Connector
                        Kevin Vaccarin

                        Hi Steve,

                         

                        Thank you very much for your answer!

                        I still have an issue, my post request syntax is like below and i have to enter at least 1 "emails" or 1 "phones"; but i don't know how to put the type and value information in the name and value of the "query parameters" of REST conector.

                        ================================================

                        {

                            "first_name": "KevinTest9",

                            "external_id": "8888444466659",

                            "country": "Bosnia and Herzegovina",

                            "phones": [

                                {

                                    "type": "home",

                                    "value": "+41524204209"

                                }

                            ],

                            "address": "710 Vicky Ports",

                            "city": "Port Chandler",

                            "last_name": "Stokes",

                            "emails": [

                                {

                                    "type": "personal",

                                    "value": "kvac9@gmail.com"

                                }

                            ]

                        }

                        ============================================

                        Then about the syntax you gave me:

                         

                        FROM JSON (wrap on) "root" PK "__KEY_root"

                        WITH CONNECTION (

                              URL "https://api.twitter.com/oauth2/token",

                              HTTPHEADER "content-type" "application/x-www-form-urlencoded",

                              HTTPHEADER "Authorization" "Basic $(vBASE64Key)",

                              QUERY "grant_type" "client_credentials"

                            );

                         

                        Once I got the REST connexion made, I only need to replace your values examples by my values, and add query rows, and it will post information?

                         

                        Kevin

                          • Re: POST Data using REST Connector
                            Steve Dark

                            Hi Kevin,

                             

                            Not sure how you would provide a nested value in the Query Headers parameter, or in the QUERY value in the WITH CONNECTION statement.

                             

                            There is also a body tag with the WITH CONNECTION suffix, like this:

                             

                             

                            FROM JSON (wrap on) "root" PK "__KEY_root"

                            WITH CONNECTION (

                                 BODY "$(vRequestBody)"

                                );

                             

                            This will allow you to put the entire request body in a variable and send it in one hit, with the nested part also.

                             

                            The WITH CONNECTION is a good way of getting changing input into your API calls.  We connect to a number of different APIs and use just two connections for all of them, GenericPOST and GenericREST.  Both of these point to a dummy endpoint then whenever we use them the URL and all the parameters are replaced with the values for that particular call.

                             

                            In your original post you said the line is created but an error appears.  It may be that you just need to wrap the call in SET ERRORMODE statements, you will need to check the error state afterwards though, to ensure you don't ignore messages which actually mean the line isn't posted.

                             

                            Hope that gets you a step nearer.

                             

                            Steve

                              • Re: POST Data using REST Connector
                                Sandeep Roy

                                Hi Steve ,

                                 

                                I am facing the problem in passing the request body using WITH Connection statement.

                                 

                                I using simple Google Matrix API to calculate the distance between two places.

                                http://maps.googleapis.com/maps/api/distancematrix/json?origins=Berlin&destinations=hamburg&mode=driving

                                 

                                 

                                I want to pass different origin and destination inn the API as request body.

                                 

                                Kindly suggest..

                                 

                                Response:

                                 

                                {

                                  "destination_addresses" : [ "Hamburg, Deutschland" ],

                                  "origin_addresses" : [ "Berlin, Deutschland" ],

                                  "rows" : [

                                  {

                                  "elements" : [

                                  {

                                  "distance" : {

                                  "text" : "289 km",

                                  "value" : 288832

                                  },

                                  "duration" : {

                                  "text" : "3 Stunden, 0 Minuten",

                                  "value" : 10821

                                  },

                                  "status" : "OK"

                                  }

                                  ]

                                  }

                                  ],

                                  "status" : "OK"

                                }

                                  • Re: POST Data using REST Connector
                                    Steve Dark

                                    Hi Sandeep,

                                     

                                    In this case it should be as simple as not adding any parameters in the URL of the REST connection, or in the Query Parameters section of the connection.  If you test the connection you will get an invalid response returned (as it requires the parameters).

                                     

                                    To add the parameters you would use WITH CONNECTION like this:

                                     

                                    WITH CONNECTION (

                                          QUERY "origins" "$(vOrigins)",

                                          QUERY "destinations" "$(vDestinations)",

                                          QUERY "mode" "$(vMode)",

                                        );

                                     

                                    This way you can set the variables in the script (let vOrigins = 'Berlin';) and then call the same connection for each pair you need a response for.

                                     

                                    Hope that helps?

                                     

                                    Steve

                                      • Re: POST Data using REST Connector
                                        Sandeep Roy

                                        Thank you Steve. Just one more question if I want to process more that 10000 rows inside the statement . any how I have to introduce the loop concept in that the performance will affect!

                                          • Re: POST Data using REST Connector
                                            Steve Dark

                                            Hi Sandeep,

                                             

                                            Anything that is done 10,000 times is going to take longer than something that is done just once.  Looping in the Qlik load script is straight forward (for iRow = 0 to NoOfRows('TableOfRows') -1), you can then peek out your values (let vOrigins = peek('Origin', iRow, 'TableOfRows');) and use those in the post.

                                             

                                            As well as the fact it could take a while you will also need to be careful of any API call limits there are on the database.

                                             

                                            The key will be to having an incremental load, so that you only look up new destination pairs and revert to QVD for destination pairs you have looked up previously.

                                             

                                            Hope that helps.

                                              • Re: POST Data using REST Connector
                                                Sandeep Roy

                                                Thanks Steve for the valuable information.

                                                  • Re: POST Data using REST Connector
                                                    Sandeep Roy

                                                    Hi Steve,

                                                     

                                                    I have attached the QVF file .. using POST connection and with statement.

                                                    Somehow , my data is being load times of total rows. It Should be generally 12 rows but it coming 90 rows.

                                                     

                                                    Can you please check the error?

                                                      • Re: POST Data using REST Connector
                                                        Steve Dark

                                                        Hi Sandeep,

                                                         

                                                        This is to do with how the JSON is returned in multiple tables and then QlikView associates between the tables.  Each time you call the API the keys between the tables will be reset (probably to 1) so all items will share the same key.

                                                         

                                                        You have two options, one is to replace the keys with the ID from the loop, repeating this for each table in place of the existing key:

                                                         

                                                        $(a) AS [__KEY_root]

                                                         

                                                        The other is to first remove all fields you don't need (you don't need the origin and destination out of the table, as you have this on the way in) and follow this pseudo code:

                                                         

                                                        load origin destination pairs

                                                        loop for each pair

                                                          get JSON from API

                                                          resident load first table into temporary table

                                                          left join resident load distance and time tables onto temporary table

                                                          resident load from temporary table into main table

                                                          drop temporary table

                                                          drop all other REST tables

                                                        end loop

                                                         

                                                        You can see why paring the queries down to just what you need before using the second approach is essential.

                                                         

                                                        The code for building the temp table would be something like:

                                                         

                                                         

                                                        tmpDist:

                                                        LOAD

                                                             '$(vOrigin)' as Origin,

                                                             '$(vDestination)' as Destination

                                                        AUTOGENERATE(1);

                                                         

                                                        LEFT JOIN (tmpDist)
                                                        LOAD

                                                             [text] AS Distance,

                                                             [value] AS Metres

                                                        RESIDENT RestConnectorMasterTable

                                                        WHERE NOT IsNull([__FK_distance]);

                                                         

                                                        LEFT JOIN (tmpDist)

                                                        LOAD

                                                             [text_u0] AS Time,

                                                             [value_u0] AS Seconds

                                                        RESIDENT RestConnectorMasterTable

                                                        WHERE NOT IsNull([__FK_duration]);

                                                         

                                                        Distances:

                                                        LOAD

                                                           *

                                                        RESIDENT tmpDist;

                                                         

                                                        DROP TABLE tmpDist;

                                                        DROP TABLE RestConnectorMasterTable;

                                                         

                                                        next a;

                                                         

                                                        All of the other RESIDENT loads which are inserted from the REST wizard can be ignored.  It is critical that the DROP statements appear within the loop.

                                                         

                                                        Hope that helps.    

                                                          • Re: POST Data using REST Connector
                                                            Steve Dark

                                                            Sandeep,

                                                             

                                                            I don't like putting up solutions which I haven't tested, so I came back to this one and plumbed it into Sense (I had typed straight into Qlik Community previously).

                                                             

                                                            I had missed a couple of places where AUTO CONCATENATE messed up my script.  I've addressed those and have attached an app that solves this.  This is attached.

                                                             

                                                            Whilst I was at it I have created the dimensions and measures to test things out, and have put on graphs of distances, times and average speeds.

                                                            2017-12-22_17-34-29.png

                                                            You can obviously replace my inline load of origins and destinations with your original spreadsheet.

                                                             

                                                            BTW, your original file had a hi-res image in it which was making the file size much larger than it needed to be.

                                                             

                                                            Hope that helps,

                                                            Steve

                                    • Re: POST Data using REST Connector
                                      Steve Dark

                                      Hi  Phil,

                                       

                                      Every different REST API has different fiddly bits to get right.  Given the nested JSON keys I suspect the only way to get that one to work is to construct the BODY and then pass that in using the WITH CONNECTION BODY tags, as outlined above.


                                      When things fail when using the Qlik REST connector there is not usually much steer about what has gone wrong.  I would recommend getting things working with the PostMan app first.  This allows you to send requests and check output much quicker and with much more diagnostics than in Qlik.

                                       

                                      Every API I have dealt with has required a bit of perseverance to get right, and some amount of changing parameters to see what works.

                                       

                                      Hope that helps.  Good luck!

                                       

                                      Steve

                                      • Re: POST Data using REST Connector
                                        Nacho Bibián

                                        Hi Kevin,

                                         

                                        I don't know if you managed to solve this or not, but next time it might prove easier to use LLodi instead. It is a freemium service and allows you to send requests both from dashboards and script.

                                         

                                        Here there is an example to use it from the script with Slack.