YQL execute now allows you to convert scraped data with server side JavaScript
I am a big fan of YQL, a terribly easy and fuss-free way to access APIs and mix data retrieved from them in a simple, SQL style language. Say for example you want photos of Paris,France from Flickr that are licensed with Creative Commons attribution, you can do this with a single command:
-
select * from flickr.photos.info where photo_id in (select id from flickr.photos.search where woe_id in (select woeid from geo.places where text=‘paris,france’) and license=4)
Try it out here and you see what I mean.
The next step of this interface was to open it out to the public. You can define an “Open Table” as a simple XML schema and bring your own API into this interface with that.
One thing that’s been burning on my tongue to tell the world about has been finally released now: YQL execute. Instead of making the YQL language itself much more complex (and thus running in circles) we now allow you to embed JavaScript in the Open Table XML that will run on the YQL server and allow you to access other web services, authenticate and scrape HTML with JavaScript and E4X. As Simon Willison put it:
This is nuts (in a good way). Yahoo!’s intriguing universal SQL-style XML/JSONP web service interface now supports JavaScript as a kind of stored procedure language, meaning you can use JavaScript and E4X to screen-scrape web pages, then query the results with YQL.
Using this, you can augment the original functionality of YQL to whatever you need. For example, you can scrape HTML with YQL using XPATH, but there was no way to use CSS selectors. Using an open table that invokes James Padolsey’s css2xpath JavaScript on the server side, this is now possible.
-
use ‘http://yqlblog.net/samples/data.html.cssselect.xml’ as data.html.cssselect; select * from data.html.cssselect where url=“www.yahoo.com” and css=“#news a”
The data table is pretty easy:
-
<?xml version=“1.0″ encoding=“UTF-8″ ?>
-
<table xmlns=“http://query.yahooapis.com/v1/schema/table.xsd”>
-
<meta>
-
<samplequery>select * from {table} where url="www.yahoo.com" and css="#news a"</samplequery>
-
</meta>
-
<bindings>
-
<select itemPath=“” produces=“XML”>
-
<urls>
-
<url></url>
-
-
</urls>
-
<inputs>
-
<key id=“url” type=“xs:string” paramType=“variable” required=“true” />
-
<key id=“css” type=“xs:string” paramType=“variable” />
-
</inputs>
-
<execute><![CDATA[
-
//include css to xpath convert function
-
y.include("http://james.padolsey.com/scripts/javascript/css2xpath.js");
-
var query = null;
-
if (css) {
-
var xpath = CSS2XPATH(css);
-
y.log("xpath "+xpath);
-
query = y.query("select * from html where url=@url and xpath=\""+xpath+"\"",{url:url});
-
} else {
-
query = y.query("select * from html where url=@url",{url:url});
-
}
-
response.object = query.results;
-
]]></execute>
-
</select>
-
</bindings>
-
</table>
Check the official Yahoo Developer Network blog post on YQL execute for more examples, including authentication examples for flickr and netflix.
