April 29, 2009

With YQL Execute, the Internet becomes your database

The Yahoo! Query Language lets you query, filter, and join data across any web data source or service on the web. Using our YQL web service, apps run faster with fewer lines of code and a smaller network footprint. YQL uses a SQL-like language because it is a familiar and intuitive method for developers to access data. YQL treats the entire web as a source of table data, enabling developers to select * from Internet.

Earlier this year we released Open Data Tables publicly for anyone to use to make data YQL-accessible, and today, as I write, there are seventy-some tables contributed to the community repository on github.

Today, the YQL team has taken the Open Data Table capabilities to the next level by adding a new element to the definition - Execute. The Execute element can contain arbitrary developer code that the YQL data engine runs during the processing of a YQL statement.

With Execute, developers now have full control of how the data is fetched into YQL and how it’s presented back to the user. With Open Data Tables, developers can build tables that manipulate, change, and sign the URLs to access almost any protected content, allowing YQL access and combining data across a variety of different authenticated services such as Netflix or Twitter. Developers can call multiple services and data sources within Execute to join and mashup data however they desire, letting Yahoo! do the work rather than their applications. Data can be tweaked and manipulated into an optimal format for applications to consume.

Execute elements run server-side JavaScript with E4X (native XML) support. This gives developers a fully functional language that web developers know, and lets them do anything they want with the data. We've added a few new global objects to the language to enable developers to: include JavaScript libraries and code from any URL; fetch data from any URL/web page; run other YQL commands; and perform data filtering and conversion.

Hello World example

Let’s take a look at our “Hello World” example to see how it works. Here’s a table that’s served up from http://yqlblog.net/samples/helloworld.xml :

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
  <meta>
    <sampleQuery>select * from {table} where a='cat' and b='dog';</sampleQuery>
  </meta>
  <bindings>
    <select itemPath="" produces="XML">
      <urls>
        <url>http://fake.url/{a}</url>
      </urls>
      <inputs>
        <key id='a' type='xs:string' paramType='path' required="true" />
        <key id='b' type='xs:string' paramType='variable' required="true" />
      </inputs>
      <execute><![CDATA[
        // Your server-side javascript goes here
        response.object = <item>
          <url>{request.url}</url>
          <a>{a}</a>
          <b>{b}</b>
        </item>;
      ]]></execute>
    </select>
  </bindings>
</table>

The heart of this simple example is the “execute” element. This accepts various input values from the YQL engine (as determined by the “inputs” element) and needs to produce data to be returned. In our example, we’re just creating a simple XML document using E4X and returning it with the values “a” and “b” from the YQL statement.

To use a single open data table in YQL you need to prepend a “use” statement before actually running a query on the table. So sending the following YQL statement to our web service, or pasting it into the console, will import the data table above and run it:

use "http://yqlblog.net/samples/helloworld.xml";

select * from helloworld where a="cat" and b="dog";

To try it, follow this link.

Other examples of YQL Execute

Here are some other examples to give you a taste of the new capabilities in Open Data Tables with Execute:

* CSS selectors for HTML - a CSS selector table for getting data from HTML pages

use "http://yqlblog.net/samples/data.html.cssselect.xml";
select * from data.html.cssselect;
Try CSS selectors.

* Unified web+image search - perform a BOSS search that also returns an image from the BOSS image search for the same site and query term in a single result set

use 'http://yqlblog.net/samples/search.imageweb.xml' as searchimageweb;
select * from searchimageweb where query='pizza'
Try Unified web+image search.

* Social application install differ - get all the apps that you and your friends have installed, see who has what.

use "http://yqlblog.net/samples/social.friendapps.xml";
select * from social.friendapps;
Try Social application install differ.

* Search position finder - augments BOSS search results with a rank number so you can then find where certain sites appear

use 'http://yqlblog.net/samples/searchrank.xml' as searchrank;
select * from searchrank where query='pizza' and dispurl like '%pizzahut%'
Try Search position finder.

* Celebrity birthday list - combines BOSS with IMDB HTML data to give you a list of birthdays and bios on a given day (today by default).

use "http://yqlblog.net/samples/celeb.birthdays.xml";
select * from celeb.birthdays;
Try Celebrity birthday list.

* Flickr frob converter - given a user's frob it gives you a Flickr auth key for getting to private data. This one requires HTTPS connections to YQL to keep your Flickr app id secret.

use "http://yqlblog.net/samples/flickr.auth.frob.xml";
desc flickr.auth.frob;
Try Flickr frob converter.

* Search the Netflix catalog - shows the power of being able to sign requests in the table's JavaScript using an external library. The table takes the CK and CKS for Netflix and performs a 2-legged signed request to search their catalog. You can then join or filter this data across other web services.

use "http://yqlblog.net/samples/netflix.catalog.xml";
desc netflix.catalog;
Try Search the Netflix catalog.

If you'd like to learn more, please delve into the documentation and start creating your own open data tables with Execute!

Jonathan Trevor
YQL Lead

Posted at April 29, 2009 8:05 AM | Permalink

Bookmark this on Delicious

Comments

Posted by: Jono! at April 29, 2009 2:25 PM

Posted by: Edward J. Stembler at April 29, 2009 7:48 PM

Posted by: Andrew Wooldridge at April 29, 2009 11:50 PM

Posted by: Balakumar Muthu at April 30, 2009 1:01 AM

Posted by: Edward J. Stembler at April 30, 2009 10:22 AM

Posted by: Balaji_Getfriday at May 1, 2009 9:12 AM

Posted by: Honest at May 4, 2009 4:24 AM

Posted by: Garo Garabedyan at May 4, 2009 11:08 AM

Posted by: YQL user at December 16, 2009 2:16 AM

Posted by: Jonathan Trevor at December 16, 2009 9:09 AM