URI templates
ql.io uses URI templates in create table statements. A URI template consists of
tokens embedded in curly braces. In the example below, the create table statement
refers to a a URI template with one tokenized parameter {address}.
create table geocoder
on select get from "http://maps.googleapis.com/maps/api/geocode/json?address={address}&sensor=true"
resultset "results";
Values for tokens can be supplied from where clause, URI parameters, request
headers, or from configuration. With this example below, ql.io supplies
the value of address specified in the where clause while formatting the
URI template into a URI.
select address_components[0].long_name from geocoder where address = 'Sammamish, WA'
Types of Tokens
ql.io uses a few special modifiers for each token.
Required Tokens
These are preceded by ^. You can modifiy the URI template in the
above example to say that address is required to use this API.
create table geocoder
on select get from "http://maps.googleapis.com/maps/api/geocode/json?address={^address}&sensor=true"
resultset "results";
select address_components[0].long_name from geocoder;
When a token is marked as required, statements must include a value in the where clause, or as a query parameter, or as a header, or specified in the config. If not, ql.io returns an error.
Single-Valued Tokens
By default every token can only have one value. When you supply multiple values for a token, ql.io forks multiple requests to the API and join on the responses. Here is an example.
Try me
create table geocoder
on select get from "http://maps.googleapis.com/maps/api/geocode/json?address={^address}&sensor=true"
resultset "";
select * from geocoder where address in
('San Jose, CA', 'Palo Alto, CA', 'San Francisco, CA');
In this case, ql.io makes three HTTP requests to the API, and merge the responses field by field by default, and so, you will get a response like this:
{
"results": [
{...},
{...},
{...}
],
"status": [
"OK",
"OK",
"OK"
]
}
You can prepend the token with a # in your create table statement to
let ql.io merge the results into a single array in stead.
create table geocoder
on select get from "http://maps.googleapis.com/maps/api/geocode/json?address={#^address}&sensor=true"
resultset "";
select * from geocoder where address in ('San Jose, CA', 'Palo Alto, CA', 'San Francisco, CA');
Notice the difference in the response.
[
{
"results": [],
"status": "OK"
},
{
"results": [],
"status": "OK"
},
{
"results": [],
"status": "OK"
}
]
Multi-Valued Tokens
If a token can have multiple values (separated by commas), prefix the name of the token by `|`.
In the example below, the second table uses a URI template that can tak upto 20 item
IDs.
create table eBay.findItems
on select get from
"http://svcs.ebay.com/services/search/FindingService/v1?OPERATION-NAME=findItemsByKeywords&SERVICE-VERSION=1.8.0&GLOBAL-ID={globalid}&SECURITY-APPNAME={apikey}&RESPONSE-DATA-FORMAT={format}&REST-PAYLOAD&keywords={^keywords}&paginationInput.entriesPerPage={limit}&paginationInput.pageNumber={pageNumber}&outputSelector%280%29=SellerInfo&sortOrder={sortOrder}"
using defaults format = "XML", globalid = "EBAY-US", sortorder = "BestMatch",
apikey = "{config.eBay.apikey}", limit = 10, pageNumber = 1
resultset "findItemsByKeywordsResponse.searchResult.item"
create table eBay.itemDetails
on select get from
"http://open.api.ebay.com/shopping?callname=GetMultipleItems&responseencoding={format}&appid={^apikey}&version=715&IncludeSelector={includeSelector}&ItemID={20|itemId}"
using defaults format='JSON', apikey="{config.eBay.apikey}"
resultset "Item"
ids = select itemId from eBay.findItems where keywords = "mini cooper" limit 2;
return select * from eBay.itemDetails where itemId in ("{ids}");
As the URI template for the second table included {20|itemId}, if you supply more
than 20 IDs in the in clause, ql.io will automatically fork additional
requests to fetch items details in batches of 20.
See URI Template Syntax Reference for the railroad diagrams.
Escape Params
You can use ` to escape encoding of the parameters that are passed in the URL.
Example :
create table geocoder.test
on select get from "http://maps.googleapis.com/maps/api/geocode/json?address={`address}&sensor=true"
select * from geocoder.test where address = 'S FO'
In the example, we want to escape encoding of the parameter passed in "address". So we use `. Thus the parameter passed to the URL here will be 'S FO' and not 'S%20FO'.