YQL Execute Screencast & Tutorial

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"

Try in YQL Console!

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">
        <author>Nagesh Susarla</author>
        <description>Yahoo! Local Data table</description>
        <sampleQuery>select * from {table} where zip='94085' and query='pizza'</sampleQuery>
        <select itemPath="ResultSet.Result" produces="XML">
            <paging model="offset">
                <start id="start" default="1"/>
                <pagesize id="results" max="20"/>
                <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"

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

Try in YQL Console!

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)

Try in YQL Console!

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:


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

Try in YQL Console!

results …

<?xml version="1.0" encoding="UTF-8"?>
        <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>
            <title>Papa John's</title>

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">
        <author>Nagesh Susarla</author>
        <documentationURL>My Search Rank</documentationURL>
        <select itemPath="results.result" produces="XML">
                <key id="query" type="xs:string" paramType="variable" required="true"/>
                    var search = y.query('select * from search.web(100) where query=@query',
                    var rank = 0;
                    //y.log("initing rank");
                    default xml namespace = 'http://www.inktomi.com/';
                    for each (var result in search.result) {
                        //y.log("rank for " + rank);
                        result.rank += <rank>{rank}</rank>;
                    response.object = search;

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

Try in YQL Console!

Result from console:

<?xml version="1.0" encoding="UTF-8"?>
        <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>
            <title><![CDATA[<b>Pizza</b> Hut]]></title>

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:

  • del.icio.us
  • Facebook
  • Digg
  • description

No related posts.

  • http://www.ResumeBuilderPlus.com Resumes


  • http://www.k9puppy.co.uk/DogsforSale/ dogs

    Hey the video link is not working with me! But fortunately I had some other link and the tutorial is pretty good. She is been quite helpful to me, since I started working in this field. Is there any other tutorial videos or something like that?

  • http://www.ResumeBuilderPlus.com resume wizard

    thanks a lot for the tutorial, this was just exactly what I'm looking for, by the way, the video made it easier for me to understand.

  • Puppies4sale

    Thanks for haring your informative tutorial with us. Really a good one

  • max191

    Thanks for the great post. It reminds me that I have to bring more structure in to my blogging. Your blog is very interesting. Please let me know how to go for your rss blog.
    charcoal grill

  • http://www.getpaidtodosurveysnow.com/ Get Paid To Do Surveys

    Hey I'v been reading your blog from quite some time now and I just wanted to say keep up the good work.

  • http://www.moviesforfree.tv Watch Movies Online

    I had some other link and the tutorial is pretty good. She is been quite helpful to me, since I started working in this field.The video made it easier for me to understand.

  • http://www.netbookchart.com netbookchart

    hahahaha thanks for this great article.. anyway can we use it in yahoo pipes?

  • http://www.bradfordsbakers.com/?target=category&category_id=347 valentines day gifts

    You are so talented aside from being so thoughtful: you've shared your knowledge so others may also learn.

  • pornvideos

    yes, the videolink is not working, im using firefox, if it matters

  • http://www.adultnetflixdvdrentals.com/ adult netflix

    videolink works fine for me, using ie 8

  • http://www.resume-builder.net Resume Builder

    Thanks for the sharing tips with us, this is very useful for us.

    Thanks a lot

  • http://www.easysitebuild.com/ website builder

    Thank you for sharing these codes to us.

  • http://pulse.yahoo.com/_MD6BFPAXROCY5BA2DI33FRJSLM Sam

    Can we get copy of YQL to run on our own boxes for internal applications. These applications are not meant to be open, But we like to use YQL features to work with data coming from different in-house applications.

  • kaili

    I was searching forgw2 gold this topic and just found your site when I was
    googlingguild wars 2 gold. Your blog can be much better if you put some pictures in i

blog comments powered by Disqus