On 29th April ’09 we released YQL Execute out into the open. You can find the actual release blog on yqlblog.net. We also released the following screencast which gives an overview of YQL Execute.
Here’s the direct link to the YQL Execute Screencast on YDN. In the screencast, Sam Pullara gives an introduction to YQL Execute and I follow it up with a demo to show the power of this new feature.
For folks who prefer to read and follow the tutorial instead, here is the transcript of the entire demo along with the YQL queries as well as the Open Data Tables used to create the examples.
Let’s get started with a little review on Open Data Tables to set the stage.
Dynamic Tables Recap
YQL already provides dynamic tables which let you access data from many formats across the internet such as rss, atom, raw xml, json, csv or even html.
You can select xml directly from the yahoo local webservice.
select * from xml where url="http://local.yahooapis.com/LocalSearchService/V3/localSearch?appid=YahooDemo&query=pizza&zip=94306&results=2"
Behind the scenes, I read up the documentation on Local search and formed this query. Although this query returns the data from the remote service, you have the burden of constructing the URLs required. It also doesn’t give me the full power of YQL where I can use nested binary operators using the various query or path parameters. In essence you’d have to write all the code to collect the parameters and conditionally create the URL.
This issue can be easily resolved by using an open data table.
Open Data Tables
With an Open Data Table, we expose a way to bind YQL to your webservice with a single XML file. Let’s create an open data table for the local service
<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
<meta>
<author>Nagesh Susarla</author>
<description>Yahoo! Local Data table</description>
<documentationURL>http://developer.yahoo.com/search/local/V3/localSearch.html
</documentationURL>
<sampleQuery>select * from {table} where zip='94085' and query='pizza'</sampleQuery>
</meta>
<bindings>
<select itemPath="ResultSet.Result" produces="XML">
<urls>
<url>http://local.yahooapis.com/LocalSearchService/V3/localSearch</url>
</urls>
<paging model="offset">
<start id="start" default="1"/>
<pagesize id="results" max="20"/>
</paging>
<inputs>
<key id="zip" type="xs:string" paramType="query" required="true"/>
<key id="query" type="xs:string" paramType="query" required="true"/>
<key id="appid" type="xs:string" const="true" private="true" paramType="query"
default="YahooDemo"/>
</inputs>
</select>
</bindings>
</table>
The open data table contains a root “table” element which contains a meta tag. The meta element describes metadata such as author, documentation url and a sample query for the table. The next element “bindings” contains one or more select blocks. This element describes how YQL should map to the webservice. Each select has the base URL followed by the various input keys that it supports. (Before writing the table I read up the documentation and listed the various query parameters here)
Once I have the open data table ready, I simply upload it to my website and access it in yql console by performig the following query.
use 'http://nagiworld.net/yqldefs/mylocal.search.xml' as mylocal; select * from mylocal where query = 'pizza' and zip = 94085
I can even abitrarily nest the binary operators and YQL will do the work of creating the URLs and fetching the data.
use 'http://nagiworld.net/yqldefs/mylocal.search.xml' as mylocal; select * from mylocal where query = 'pizza' and (zip = 94085 OR zip=94536)
This is an easy way to bring the power of YQL to disparate data sources.
With YQL Open Tables, you can take almost any publicly available Web service and make it available as a table for others to use.
Now, what if you wanted to augment or enhance the results of a webservice?
YQL Execute
YQL Execute, our newest feature, lets you enhance or shape the resulting data to suit your needs. You can use “YQL Execute” to mash together or join data from multiple services and return the resulting data.
Let’s say I got an idea to build the next killer app. The idea is called “Search Rank” and its requirements are as follows:
-
Inputs:
- Give me a topic to search for & such as “pizza”
- Give me link to rank such as “pizzahut”
Output: The App should return you the search rank of the link as seen by Yahoo Boss.
Let’s see what search.web already provides today. For this lets go to console and try the following query.
select * from search.web where query = 'pizza'
results …
<?xml version="1.0" encoding="UTF-8"?>
<query...>
<diagnostics/>
<results>
<result xmlns="http://www.inktomi.com/">
<abstract><![CDATA[Official site of Papa John's <b>pizza</b> delivery and carry-out chain. Includes store locator, nutritional info, and franchise and employment opportunities.]]></abstract>
<clickurl>http://.......JWbks-/SIG=10u9f6tba/**http%3A//www.papajohns.com/</clickurl>
<date>2009/05/02</date>
<dispurl><![CDATA[www.<b>papajohns.com</b>]]></dispurl>
<size>2237</size>
<title>Papa John's</title>
<url>http://www.papajohns.com/</url>
</result>
</results>
</query>
You can see that the search results include a dispurl – which is the display URL. The only thing missing is the ranking. So lets write an Open data table to add a rank to each of these results.
<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
<meta>
<author>Nagesh Susarla</author>
<documentationURL>My Search Rank</documentationURL>
</meta>
<bindings>
<select itemPath="results.result" produces="XML">
<urls>
<url/>
</urls>
<inputs>
<key id="query" type="xs:string" paramType="variable" required="true"/>
</inputs>
<execute><![CDATA[
var search = y.query('select * from search.web(100) where query=@query',
{query:query}).results;
var rank = 0;
//y.log("initing rank");
default xml namespace = 'http://www.inktomi.com/';
for each (var result in search.result) {
rank++;
//y.log("rank for " + rank);
result.rank += <rank>{rank}</rank>;
}
response.object = search;
]]></execute>
</select>
</bindings>
</table>
The only additional element in this open data table is the <execute> block. This is where you add the code to enhance or reshape or mashup your data.
Here we use an object called “y” which is implicitly provided by the engine. Among the various methods that it supports is the “query” method which lets me run a YQL query right from inside my execute.
Here, I’m simply wrapping the search api, I issue a search.web call and get the results. Since search already orders the results according to their popularity, all I need to do is add a <rank> element to each result. Also note that the rank is just the index of the unmodified search result. So I loop over each result and use E4X to add a new element to it. Finally I upload this to my website and issue the following query to try it in the console.
use 'http://nagiworld.net/yqldefs/searchrank.xml'; select * from searchrank where query='pizza' and dispurl like '%pizzahut%'
Result from console:
<?xml version="1.0" encoding="UTF-8"?>
<query>
<diagnostics/>
<results>
<result xmlns="http://www.inktomi.com/">
<abstract><![CDATA[Official site for Pizz Hut provides online ordering for dine-in and delivery, a <b>Pizza</b> Hut store finder, coupons, and menu.]]></abstract>
<clickurl>http://..._ylc=X...**http%3A//www.pizzahut.com/</clickurl>
<date>2009/05/02</date>
<dispurl><![CDATA[www.<b>pizzahut.com</b>]]></dispurl>
<size>14113</size>
<title><![CDATA[<b>Pizza</b> Hut]]></title>
<url>http://www.pizzahut.com/</url>
<rank>4</rank>
</result>
</results>
</query>
Voila! and you have the ranking of the particular element. Now I only have to wrap this in a nice UI and I have a killer app which does searchranking using YQL.
Here is the final product using some YUI goodness to wrap the YQL calls: SearchRank
Useful links on YQL Execute:
- Select * from Internet and Execute With YQL
- YQL Execute Docs
- DataTables.org: Community site for data tables and a pointer to the best examples on YQL Execute
- github repository: Open Data Table repository
No related posts.