Overview

About ql.io

Language Overview

Runtime Overview

Usage Overview

Getting Started

Prerequisites

Quick Start

Build an App

Examples

Build and Develop ql.io!

Writing Scripts

create table

connectors

select

if-else

insert

update

delete

URI Templates

Data Formats

Variable References

Configuration

Script Routes

Monkey Patching

UDF

try-catch-finally

Timeouts and Back-off

OAuth

Executing Scripts

HTTP Interface

WebSocket Interface

Engine API

Caching (Memcache)

Fallback

Conditions

Monitoring

Runtime Monitoring

References

Language Reference

URI Template Syntax Reference

Best Practices Guide

Troubleshooting

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.

Try me
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.

Try me
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.

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');

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'.