complex json as input in create table statment for Http post

[NIkhil]

 Hi,

  I have to pass a complex json object to a post method. Creating a mustache template of the json has consumed quite a time and it is still not done.
  Is there any other alternative to mustache, can't we pass the json directly to the create table statement in ql.io ?

Sample json;
{
"a":
   "c" : "c",
   "d"  : [ 1, 2, 3 ],
   "e" : {
        "user" : "user1",
        "token": "token"
        }
}

One more complication while creating a mustache in my case is that the key - value pairs under "a" key are optional.

Any help is appreciated.

Thanks,
Nikhil

[shimonchayim]

You can you ejs (embedded javascript) templates if you like. e.g.AddDisputes script here https://github.com/ql-io/ql.io-ebay-mp-apis/tree/master/tables/trading

[NIkhil]

Thanks shimonchayim,

EJS looks more flexible than mustache.

I have written a ejs script that produces a valid json as required by the api used in the create table.
EJS-
<%
function handleArray(arrayKey, array){
var key = arrayKey %>
"<%= arrayKey %>": [

<% for(var i=0; i<array.length -1; i++) {%>
    "<%= array[i] %>",
<% } %>

"<%= array[array.length-1] %>"
]
<%}
%>

<%function handleJsonRecord(params){ %>
"report" : {
"report_type" : "<%= params.report.report_type  %>",
"report_interval" : "<%= params.report.report_interval %>",
 <% handleArray("columns", params.report.columns ) %> }
<%}
%>

<%
handleJsonRecord(data)
%>

This ejs works correctly when invoked through javascript.

But now I am facing issues while integrating it with ql.io
I get the following error with same ejs:

ReferenceError: ejs:24
    22| %>
    23|
 >> 24| <%
    25| handleJsonRecord(data)
    26| %>

data is not defined
    at eval (eval at <anonymous> (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ejs/lib/ejs.js:223:12))
    at exports.compile (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ejs/lib/ejs.js:225:15)
    at Object.exports.render (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ejs/lib/ejs.js:263:13)
    at Object.exports.serialize (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/lib/serializers/ejs.js:30:16)
    at Object.module.exports.tmpl (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/lib/engine/source/verb.js:209:42)
    at send (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/lib/engine/source/verb.js:628:37)
    at module.exports.exec.async.parallel.ret.headers.content-type (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/lib/engine/source/verb.js:362:25)
    at async.parallel.results (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/node_modules/async/lib/async.js:454:21)
    at _asyncMap (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/node_modules/async/lib/async.js:185:13)
    at async.forEach (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/node_modules/async/lib/async.js:86:13)

   
With heuristics, I could get that ql.io takes the input data in variable named "params"   
but then I get the following error - It is unable to access the nested json objects.

TypeError: ejs:17
    15| <%function handleJsonRecord(data){ %>
    16| "report" : {
 >> 17| "report_type" : <%= data.report.report_type  %>,
    18| "report_interval" : "<%= data.report.report_interval %>",
    19|  <% handleArray("columns", data.report.columns ) %> }
    20|

Cannot read property 'report_type' of undefined
    at handleJsonRecord (eval at <anonymous> (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ejs/lib/ejs.js:223:12))
    at eval (eval at <anonymous> (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ejs/lib/ejs.js:223:12))
    at exports.compile (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ejs/lib/ejs.js:225:15)
    at Object.exports.render (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ejs/lib/ejs.js:263:13)
    at Object.exports.serialize (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/lib/serializers/ejs.js:30:16)
    at Object.module.exports.tmpl (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/lib/engine/source/verb.js:209:42)
    at send (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/lib/engine/source/verb.js:628:37)
    at module.exports.exec.async.parallel.ret.headers.content-type (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/lib/engine/source/verb.js:362:25)
    at async.parallel.results (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/node_modules/async/lib/async.js:454:21)
    at _asyncMap (/root/myapp/node_modules/ql.io-app/node_modules/ql.io-console/node_modules/ql.io-engine/node_modules/async/lib/async.js:185:13)


Can you please let me know what is wrong with my setup here.   


ql.io scripts:
table:
create table AN_getPublisherAnalytics
   on select post to "http://sand.api.appnexus.com/report?publisher_id={^publisher_id}"
   using headers 'Authorization'='{token}'
   using bodyTemplate 'Publisher.ejs' type 'application/json'

route:
return select * from AN_getPublisherAnalytics
    where publisher_id='{publisher_id}' and token='{token}'
        via route '/AN_getPublisherAnalytics?publisher_id={publisher_id}&token={token}' using method post;


Thanks,
Nikhil

[NIkhil]

Sample input  -

{
    "report" : {
        "report_type" :    "publisher_analytics",
        "report_interval" : "last_48_hours",
        "columns" : ["imps_sold", "imps_total", "total_convs", "publisher_rpm", "clicks"],
        "orders" :[
            {
                "order_by" : "imps_sold",
                "direction" : "ASC"
            }           
        ],
        "format" :"csv"

[NIkhil]

Update -

I got rid of the error by changing the ejs:
from

<%function handleJsonRecord(params){ %>
"report" : {
"report_type" : "<%= params.report.report_type  %>",
"report_interval" : "<%= params.report.report_interval %>",
 <% handleArray("columns", params.report.columns ) %> }
<%}
%>

to
<%function handleJsonRecord(params){ %>
"report" : {
"report_type" : "<%= params.report_type  %>",
"report_interval" : "<%= params.report_interval %>",
 <% handleArray("columns", params.columns ) %> }
<%}
%>


but now the value for the keys e.g. "report_type" is not correct,
using the sample input mentioned above, it should be "publisher_analytics".
Is there any way to display the output of ejs, in context of ql.io?

Thanks,
Nikhil

[shimonchayim]

Hi Nikhil,

You should be able to verify the ejs file using a separate program. But you want to verify if the values are being set correctly so that the ejs can be rendered correctly then I would recommend the "debug" feature available in the console for latest code (not updated on http://ql.io site yet but packages published).

- Cylus

[NIkhil]

Thanks again Cylus,

I have verified the ejs using javascript + html and it renders correctly with few changes as mentioned above.

Also, I have a post query, so I guess, console would not be useful.  Where in console can I specify the request json?

create table AN_getPublisherAnalytics
   on select post to "http://sand.api.appnexus.com/report?publisher_id={^publisher_id}"
   using headers 'Authorization'='{token}'
   using bodyTemplate 'Publisher.ejs' type 'application/json'

[shimonchayim]

Hi Nikhil,

Say you are using param.abc in your ejs.

Then you can essentially do something like:

abc = {... you json ..}

Because headers and body just get dumped in to the param (bag).

- Cylus

[NIkhil]

Thanks for confirming that "params" has contents of body and header.

but when I try to display "params" - it is empty i.e. it has no data.
I used tcpmon.

And that is the reason why the values are getting passed to the target site,
Can you please tell me how is the flow of request json (post) to target site in ql.io.
How can I access the post request data in ql.io?

<%function handleJsonRecord(params){ %>
{
"msg":"<%= JSON.stringify(params) %>"
}

<%}
%>


<%
handleJsonRecord(params)
%>

It

[shimonchayim]

post request data is dumped into the params. This blog should help: http://ql-io.github.com/2012/03/12/en-route.html

[NIkhil]

     Still not working. I am stuck bigtime!!


   I am not getting the post data in params.
   The params is empty - verified using tcpmon.

   Thanks in anticipation,
      Nikhil

[shimonchayim]

Hi Nikhil,

We have quite a few examples (taking production traffic) with post. Likely something in your setup or config.

There are tons of examples using params in the body for tables available here: https://github.com/ql-io/ql.io-ebay-mp-apis

Most of these examples actually use ejs and not mustache but that doesn't matter. The inner workings are the same for populating params.

e.g. refer to ejs body template here: https://github.com/ql-io/ql.io-ebay-mp-apis/blob/master/tables/trading/AddItem.ejs

If you like you can clone this repo, get a proper eBay token and try out lot of these examples to see if it helps.

- Cylus

[NIkhil]

 Hi,

Unfortunately I could not find any examples of route that uses POST method.

Here are the details that I have tried,

Table definition:
Test.ql -->

create table connectorTest
    on insert   post to "http://br0192:5000/VertiCloudConnectorNew/v0.1/connectors/"
     using defaults format = "JSON"
     using bodyTemplate 'Test.ejs' type 'text/plain' ;
   
===============================

Route:
TestRoute.ql -->

     return  insert '{dummy}' into connectorTest
    via route '/conTestRoute' using method post;
=============================

EJS script
Test.ejs -->

<% function insertData(params, connectorName, description, connectorVersion, source){%>
{
    "PARAMS" : "<%= params %>",
    "PARAMS" : "<%= JSON.stringify(params) %>",
    "connectorName" : "<%= connectorName %>",
    "description" : "<%= description %>",
    "connectorVersion" : "<%= connectorVersion %>",
    "source" : "<%= source%>",
    "domain" : "XXX"
}
 
<%}%>

<%
insertData(params, params.connectorName, params.description, params.connectorVersion, params.source)
%>

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

Input Json - Request body
{
    "connectorName" : "Connector1",
    "description" : "Connector1 connector",
    "connectorVersion" : "1.0",
    "source" : "SOURCE"
}

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

TCPMon trace -->

{
    "PARAMS" : "[object Object]",
    "PARAMS" : "{}",
    "connectorName" : "undefined",
    "description" : "undefined",
    "connectorVersion" : "undefined",
    "source" : "undefined",
    "domain" : "XXX"
}
 
==================
Ql.io version -->
root@v-team:~/myapp# node node_modules/.bin/ql.io.app -V
1.0.0


As you can see the value for params is empty. I followed all the scripts you pointed out but couldn't get any related.

Can you please point out to me the stuff I am doing incorrectly or
I am missing any thing.

  Any Help is deeply appreciated.

  Thanks,
    NIkhil

[NIkhil]

  The following script work in ql.io console


create table connectorTest
    on insert  post to "http://br0192:5000/VCConnector/v0.1/connectors/"

    using defaults format = "JSON"

    using bodyTemplate 'tables/test/Connector.ejs' type 'text/plain';
   

json = {
    "connectorName" : "Con21",
    "description" : "Con21",
    "connectorVersion" : "1.0",
    "source" : "S1",
    "domain" : "EXCHANGE"
   
}

 
 --insert into connectorTest (connectorName,connectorVersion,source,domain) values ( '{json.connectorName}','{json.connectorVersion}','{json.source}','{json.domain}');

insert '{json}' into connectorTest;

With this ejs ->

<% function insertData(params){%>
{
    "connectorName" : "<%= params.connectorName %>",
    "description" : "<%= params.description %>",
    "connectorVersion" : "<%= params.connectorVersion %>",
    "source" : "<%= params.source%>",
    "domain" : "ADEXCHANGE"
}
 
<%}%>

<%
insertData(params)
%>


 So, I guess the problem is with the route.

 Thanks,
  Nikhil

[shimonchayim]

Hi Nikhil,

Checkout http://ql-io.github.com/2012/03/12/en-route.html especially section "Non-Idempotent and Unsafe"

Also, I see one issue, if you are trying to insert "{json}" then you will need to define that object in your script by filling in the values. If you don't fill-in "{json}" object then params will not contain anything. 

first say:

json = {
    "connectorName" : "{connectorName}",
    "description" : "{description}",
    "connectorVersion" : "{connectorVersion},
    "source" : "{source}",
    "domain" : "{domain}"
    

} 

This is assuming that you body looks like the following:

{
    "connectorName" : "Connector1",
    "description" : "Connector1 connector",
    "connectorVersion" : "1.0",
    "source" : "SOURCE"
}

Now the following statement will make sense.

insert '{json}' into connectorTest

- Cylus

[NIkhil]

 Hey Cylus,

 Thanks very much for your constant effort to help resolve my problem.
  
   I have figured out the mistake I was making. I had ignored the content-type to be set and few more details. I probably need more hands-on on Web Services.
 
   Now I have got the experiment working.

  The route that worked is :
  return  insert into connectorTest (connectorName,connectorVersion,source,domain)
values ( '{connectorName}','{connectorVersion}','{source}','{domain}')

via route '/conTestRoute' using method post;

   This method of insert works,
 But I am more interested in the method specified at http://ql.io/docs/routes
  in the "HTTP request : non idempotent usage"  :





return insert into ebay.SellingManagerInventoryFolder
via route '/opaqueparam' using method post;

 
 In the above case, you don't have to specify  any details of the request body.

  I have services that take a huge request body and it is not feasible to specify all the columns in the route.

 I tried this route :
  return insert into connectorTest

via route '/conTestRoute' using method post;

 but it does not work for me.
It gives me this error :

TypeError: first argument must be a string or Buffer
    at ClientRequest.OutgoingMessage.write (http.js:714:11)
    at sendHttpRequest

 I have attached the complete error in the file - RouteError.txt
  
 

[Hongcheng Chang]

Hi Nikhil
Method 3 in http://ql.io/docs/insert teaches you how to use opaque parameter, which take a string input at req.body and directly use it as the http post body.
For example
The conventional way of inserting into this table, is to use name-value pairs.

1	insert into insert.into (name) values ("Tom")

Or, to ignore column-value pairs,

1	insert into insert.into values ("<name>Tom</name>")

Hope it helps
Hongcheng Chang

On Sunday, November 25, 2012 10:17:57 PM UTC-8, NIkhil wrote:

[NIkhil]

 
       Thanks Chang,
 Is it also necessary to write/use the server (node.js) e.g. mentioned in the method 3 of http://ql.io/docs/insert for opaque input to work.

 With my setup, where I am not using any such node.js script, i am still getting that error.

 I want it to work like the example mentioned in the HTTP request : non idempotent usage section of http://ql.io/docs/routes.

[NIkhil]

 One more update on the previous post -

   The route -
   return insert "{req.body}" into testTable  via route '/conTestRoute' using method post;

  works, but then I still have to write the mustache/ejs template because  without the template only blank input goes to the target site.
  What am I missing here.
 
   Thanks,
   Nikhil

[NIkhil]

  To be precise I want to avoid writing any body templates mostly because of the complexity of the input body (xml/json)

  I guess these two scripts should be sufficient to post the data to the target site -

  table -
  create table connectorTest
    on insert   post to "http://br0192:5000/VertiCloudConnectorNew/v0.1/connectors/"
    using defaults format=JSON

and
 route -
return insert "{req.body}" into connectorTest
via route '/conTest2Route' using method post;


   But without using the template, the data is not getting passed to the target site.
  What am I missing here?

[Hongcheng Chang]

It seems you were using method two, which insert a json obj into table, not method three (insert into TABLE values ({OPAQUE}))

[NIkhil]

  Yes I am using method 2 of insert,
because with method 3 - insert into ConnectorTest values ({req.body})
 the value of req.body is not getting substituted and the string "req.body" is getting passed.

 Thanks,
Nikhil

[Hongcheng Chang]

It has been too long so I am not 100% sure. For method three, you should able to skip the "values ("{something opaque}")" by providing a request body through your own HTTP POST.