complex json as input in create table statment for Http post

356 views
Skip to first unread message

NIkhil

unread,
Nov 26, 2012, 1:17:57 AM11/26/12
to ql...@googlegroups.com

 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

unread,
Nov 28, 2012, 2:01:30 AM11/28/12
to ql...@googlegroups.com
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

unread,
Dec 3, 2012, 3:59:18 AM12/3/12
to ql...@googlegroups.com

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

unread,
Dec 3, 2012, 4:00:46 AM12/3/12
to ql...@googlegroups.com
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

unread,
Dec 3, 2012, 8:17:16 AM12/3/12
to ql...@googlegroups.com

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

unread,
Dec 4, 2012, 12:55:54 AM12/4/12
to ql...@googlegroups.com
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

unread,
Dec 4, 2012, 5:21:49 AM12/4/12
to ql...@googlegroups.com

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

unread,
Dec 4, 2012, 12:30:15 PM12/4/12
to ql...@googlegroups.com
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

unread,
Dec 5, 2012, 8:05:45 AM12/5/12
to ql...@googlegroups.com

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

unread,
Dec 5, 2012, 1:28:32 PM12/5/12
to ql...@googlegroups.com
post request data is dumped into the params. This blog should help: http://ql-io.github.com/2012/03/12/en-route.html

NIkhil

unread,
Dec 7, 2012, 8:12:14 AM12/7/12
to ql...@googlegroups.com

     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

unread,
Dec 7, 2012, 2:04:28 PM12/7/12
to ql...@googlegroups.com
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.


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

unread,
Dec 19, 2012, 4:52:08 AM12/19/12
to ql...@googlegroups.com
 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

unread,
Dec 19, 2012, 5:19:20 AM12/19/12
to ql...@googlegroups.com


  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

unread,
Dec 19, 2012, 12:38:09 PM12/19/12
to ql...@googlegroups.com
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

unread,
Dec 25, 2012, 7:27:14 AM12/25/12
to ql...@googlegroups.com

 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
  
 

RouteError.txt

Hongcheng Chang

unread,
Dec 26, 2012, 5:16:17 PM12/26/12
to ql...@googlegroups.com
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

unread,
Dec 31, 2012, 3:25:19 AM12/31/12
to ql...@googlegroups.com
 
       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

unread,
Dec 31, 2012, 6:40:54 AM12/31/12
to ql...@googlegroups.com


 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
Message has been deleted

NIkhil

unread,
Jan 1, 2013, 6:24:37 AM1/1/13
to ql...@googlegroups.com


  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

unread,
Jan 3, 2013, 6:53:56 PM1/3/13
to ql...@googlegroups.com
It seems you were using method two, which insert a json obj into table, not method three (insert into TABLE values ({OPAQUE}))

NIkhil

unread,
Jan 4, 2013, 9:02:47 AM1/4/13
to ql...@googlegroups.com


  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

unread,
Jan 4, 2013, 2:28:15 PM1/4/13
to ql...@googlegroups.com
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.
Reply all
Reply to author
Forward
0 new messages