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

Quick Start

The goal of this guide is to search for items using eBay's Finding API, get their details using the eBay GetMultipleItems API, and then find their geo-locations using Google's Geocoding API all with a single select statement that makes 12 HTTP requests.

Here is the list of the requests made in this exercise:

  1. Send a request to the Finding API with a keyword to get 10 item IDs (1 HTTP request)
  2. Send a request to the GetMultipleItems API to find the details of all items found in the first step (1 HTTP request)
  3. For each item found, send a request to the Geocoding API with the text-form location found in the second step (10 HTTP requests - one per address).

The steps below use the Web Console on this site and no installation is necessary.

Step 1: Create Tables

Copy and paste the following statements in the Web Console to create tables for the above APIs.

create table 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}'
   with aliases format = 'RESPONSE-DATA-FORMAT', json = 'JSON', xml = 'XML'
   using defaults format = 'XML', globalid = 'EBAY-US', sortorder ='BestMatch',
   apikey =  "{config.eBay.apikey}", limit = 10, pageNumber = 1
   resultset 'findItemsByKeywordsResponse.searchResult.item';

create table details
  on select get from "htttp://open.api.ebay.com/shopping?callname=GetMultipleItems&ItemID={itemId}&responseencoding={format}&appid={^apikey}&version=713&IncludeSelector=ShippingCosts"
     using defaults format = "JSON", apikey = "{config.eBay.apikey}"
     resultset 'Item';

create table google.geocode
  on select get from "http://maps.googleapis.com/maps/api/geocode/{format}?sensor=true&address={^address}"
    using defaults format = 'json'
    resultset 'results';

Each of these statements bind the above APIs into ql.io runtime as "tables".

Step 2: Write Selects and Joins

This exercise requires three steps.

  1. Find items.
  2. Get the details, including free form location, of items found.
  3. Map free form locations into latitude, longitude values.

Copy and paste the following statement below the create table statements in the Web Console.

select itemId from finditems where keywords='mini cooper';

This just selects IDs of items. In order to send these IDs to the details table, replace the above select statement with the one below.

select ItemID, ViewItemURLForNaturalSearch, Location from details where itemId in 
  (select itemId from finditems where keywords='mini cooper');

The next step is to send each Location field from the response to the Google geocoding API to find their locations using a join.


select e.ItemID, e.Title, e.ViewItemURLForNaturalSearch, g.geometry.location
    from details as e, google.geocode as g where
    e.itemId in (select itemId from finditems where keywords = 'mini cooper')
    and g.address = e.Location

Finally, add aliases to selected columns to make the response descriptive.

Try me
select e.ItemID as id, e.Title as title, e.ViewItemURLForNaturalSearch as url, g.geometry.location as latlng
  from details  as e, google.geocode as g
  where e.itemId in (select itemId from finditems where keywords = 'mini cooper')
  and g.address = e.Location

This single statement gives the data for this use case as shown below.