YQL – Zubin's Web Log

YQL or Yahoo Query Language allows developers to query, join and filter data from multiple data sources on the web who have made their data available through the YQL interface. Since YQL is an expressive SQL like language, developers familiar with the SQL interface should feel comfortable with the interface.

YQL provides the developers a consistent way to access online services which are otherwise dispersed across a range of formats. It hides the implementation of the various data sources on the web and make it easier to query the data.

YQL is both a service and a language. The service is through which external web services can make their data available for developers to consume through the yahoo query ‘language’.

Understanding the language of YQL

If you are familiar with the SQL, you will feel at home with YQL. This language allows developers to use SQL syntax to extract data from the service. It can return the results in either XML format or in JSON format.

Yahoo has provided a YQL Console for developers to test their queries.

For example, if the application wants to search from YouTube and display a list of results, the developer can use a simple query like below and get the information from the web service.

 SELECT * FROM youtube.search WHERE query="soccer ronaldo"

1	SELECT * FROM youtube.search WHERE query="soccer ronaldo"

The output of the above query in XML format is below. Adding ‘limit 1’ will limit the output to the top 1 result.

<?xml version="1.0" encoding="UTF-8"?>
<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"
    yahoo:count="1" yahoo:created="2012-04-26T03:29:10Z" yahoo:lang="en-US">
    <diagnostics>
        <publiclyCallable>true</publiclyCallable>
        <url execution-start-time="21" execution-stop-time="25"
            execution-time="4" proxy="DEFAULT"><![CDATA[http://www.datatables.org/youtube/youtube.search.xml]]></url>
        <url execution-start-time="179" execution-stop-time="238"
            execution-time="59" proxy="DEFAULT"><![CDATA[http://gdata.youtube.com/feeds/api/videos?q=soccer%20ronaldo]]></url>
        <javascript execution-time="376" instructions-used="972223" table-name="youtube.search"/>
        <user-time>406</user-time>
        <service-time>63</service-time>
        <build-version>26856</build-version>
    </diagnostics> 
    <results>
        <video>
            <id>tlYDcv6l95c</id>
            <url>http://www.youtube.com/watch?v=tlYDcv6l95c&amp;feature=youtube_gdata_player</url>
            <title>Ronaldo Penalty Miss || Real Madrid vs Bayern Munich (1-3) CL Semi Final ||26.04.12||</title>
            <content>Real Madrid vs Bayern Munich Penalties 1-3 Bayern Wins</content>
            <author>elly produxion</author>
            <duration>18</duration>
            <comment_count>4</comment_count>
            <categories>
                <category>Sports</category>
            </categories>
            <tags>
                <tag>penalty</tag>
                <tag>miss</tag>
                <tag>ronaldo</tag>
                <tag>real</tag>
                <tag>madrid</tag>
                <tag>vs</tag>
                <tag>bayern</tag>
                <tag>munich</tag>
                <tag>munchen</tag>
                <tag>all</tag>
                <tag>goals</tag>
                <tag>highlights</tag>
                <tag>Goal</tag>
                <tag>football</tag>
                <tag>Soccer</tag>
            </tags>
            <thumbnails>
                <thumbnail height="90" time="00:00:09" width="120">http://i.ytimg.com/vi/tlYDcv6l95c/default.jpg</thumbnail>
                <thumbnail height="180" time="" width="320">http://i.ytimg.com/vi/tlYDcv6l95c/mqdefault.jpg</thumbnail>
                <thumbnail height="360" time="" width="480">http://i.ytimg.com/vi/tlYDcv6l95c/hqdefault.jpg</thumbnail>
                <thumbnail height="90" time="00:00:04.500" width="120">http://i.ytimg.com/vi/tlYDcv6l95c/1.jpg</thumbnail>
                <thumbnail height="90" time="00:00:09" width="120">http://i.ytimg.com/vi/tlYDcv6l95c/2.jpg</thumbnail>
                <thumbnail height="90" time="00:00:13.500" width="120">http://i.ytimg.com/vi/tlYDcv6l95c/3.jpg</thumbnail>
            </thumbnails>
            <files>
                <file type="application/x-shockwave-flash">http://www.youtube.com/v/tlYDcv6l95c?version=3&amp;f=videos&amp;app=youtube_gdata</file>
                <file type="video/3gpp">rtsp://v3.cache6.c.youtube.com/CiILENy73wIaGQmX96X-cgNWthMYDSANFEgGUgZ2aWRlb3MM/0/0/0/video.3gp</file>
                <file type="video/3gpp">rtsp://v3.cache4.c.youtube.com/CiILENy73wIaGQmX96X-cgNWthMYESARFEgGUgZ2aWRlb3MM/0/0/0/video.3gp</file>
            </files>
        </video>
    </results>
</query>

<?xml version="1.0" encoding="UTF-8"?>

<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"

yahoo:count="1" yahoo:created="2012-04-26T03:29:10Z" yahoo:lang="en-US">

<url execution-start-time="21" execution-stop-time="25"

execution-time="4" proxy="DEFAULT"><![CDATA[http://www.datatables.org/youtube/youtube.search.xml]]></url>

<url execution-start-time="179" execution-stop-time="238"

execution-time="59" proxy="DEFAULT"><![CDATA[http://gdata.youtube.com/feeds/api/videos?q=soccer%20ronaldo]]></url>

<user-time>406</user-time>

<service-time>63</service-time>

<build-version>26856</build-version>

</diagnostics>

<video>

<id>tlYDcv6l95c</id>

<url>http://www.youtube.com/watch?v=tlYDcv6l95c&feature=youtube_gdata_player</url>

<title>Ronaldo Penalty Miss || Real Madrid vs Bayern Munich (1-3) CL Semi Final ||26.04.12||</title>

<content>Real Madrid vs Bayern Munich Penalties 1-3 Bayern Wins</content>

<author>elly produxion</author>

<comment_count>4</comment_count>

<category>Sports</category>

</categories>

<tags>

<tag>penalty</tag>

<tag>ronaldo</tag>

<tag>madrid</tag>

<tag>bayern</tag>

<tag>munich</tag>

<tag>munchen</tag>

<tag>goals</tag>

<tag>highlights</tag>

<tag>football</tag>

<tag>Soccer</tag>

</tags>

<thumbnail height="90" time="00:00:09" width="120">http://i.ytimg.com/vi/tlYDcv6l95c/default.jpg</thumbnail>

<thumbnail height="180" time="" width="320">http://i.ytimg.com/vi/tlYDcv6l95c/mqdefault.jpg</thumbnail>

<thumbnail height="360" time="" width="480">http://i.ytimg.com/vi/tlYDcv6l95c/hqdefault.jpg</thumbnail>

<thumbnail height="90" time="00:00:04.500" width="120">http://i.ytimg.com/vi/tlYDcv6l95c/1.jpg</thumbnail>

<thumbnail height="90" time="00:00:09" width="120">http://i.ytimg.com/vi/tlYDcv6l95c/2.jpg</thumbnail>

<thumbnail height="90" time="00:00:13.500" width="120">http://i.ytimg.com/vi/tlYDcv6l95c/3.jpg</thumbnail>

</thumbnails>

<files>

<file type="application/x-shockwave-flash">http://www.youtube.com/v/tlYDcv6l95c?version=3&f=videos&app=youtube_gdata</file>

<file type="video/3gpp">rtsp://v3.cache6.c.youtube.com/CiILENy73wIaGQmX96X-cgNWthMYDSANFEgGUgZ2aWRlb3MM/0/0/0/video.3gp</file>

<file type="video/3gpp">rtsp://v3.cache4.c.youtube.com/CiILENy73wIaGQmX96X-cgNWthMYESARFEgGUgZ2aWRlb3MM/0/0/0/video.3gp</file>

</files>

</video>

</results>

</query>

Similar to SQL, YQL also let the developer query for individual columns. The developer can limit what is returned from the service by specifying the data that should be returned as in the below example.

 SELECT id, url, title, content FROM youtube.search WHERE query="soccer ronaldo"  limit 1

1	SELECT id, url, title, content FROM youtube.search WHERE query="soccer ronaldo" limit 1

Which returns just those 4 columns that are requested.

<?xml version="1.0" encoding="UTF-8"?>
<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"
    yahoo:count="1" yahoo:created="2012-04-26T03:36:52Z" yahoo:lang="en-US">
    <diagnostics>
        <publiclyCallable>true</publiclyCallable>
        <url execution-start-time="22" execution-stop-time="26"
            execution-time="4" proxy="DEFAULT"><![CDATA[http://www.datatables.org/youtube/youtube.search.xml]]></url>
        <url execution-start-time="139" execution-stop-time="188"
            execution-time="49" proxy="DEFAULT"><![CDATA[http://gdata.youtube.com/feeds/api/videos?q=soccer%20ronaldo]]></url>
        <javascript execution-time="341" instructions-used="972223" table-name="youtube.search"/>
        <user-time>371</user-time>
        <service-time>53</service-time>
        <build-version>26856</build-version>
    </diagnostics> 
    <results>
        <video>
            <id>tlYDcv6l95c</id>
            <url>http://www.youtube.com/watch?v=tlYDcv6l95c&amp;feature=youtube_gdata_player</url>
            <title>Ronaldo Penalty Miss || Real Madrid vs Bayern Munich (1-3) CL Semi Final ||26.04.12||</title>
            <content>Real Madrid vs Bayern Munich Penalties 1-3 Bayern Wins</content>
        </video>
    </results>
</query>

<?xml version="1.0" encoding="UTF-8"?>

<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"

yahoo:count="1" yahoo:created="2012-04-26T03:36:52Z" yahoo:lang="en-US">

<url execution-start-time="22" execution-stop-time="26"

execution-time="4" proxy="DEFAULT"><![CDATA[http://www.datatables.org/youtube/youtube.search.xml]]></url>

<url execution-start-time="139" execution-stop-time="188"

execution-time="49" proxy="DEFAULT"><![CDATA[http://gdata.youtube.com/feeds/api/videos?q=soccer%20ronaldo]]></url>

<user-time>371</user-time>

<service-time>53</service-time>

<build-version>26856</build-version>

</diagnostics>

<video>

<id>tlYDcv6l95c</id>

<url>http://www.youtube.com/watch?v=tlYDcv6l95c&feature=youtube_gdata_player</url>

<title>Ronaldo Penalty Miss || Real Madrid vs Bayern Munich (1-3) CL Semi Final ||26.04.12||</title>

<content>Real Madrid vs Bayern Munich Penalties 1-3 Bayern Wins</content>

</video>

</results>

</query>

Using the YQL Service

Let’s look at the usage of these services. Let us say, for example, an application wants to list out the current popular music charts.

Head over to the YQL Console and put your query as below:

SELECT * FROM music.artist.popular limit 5

1	SELECT * FROM music.artist.popular limit 5

Click on TEST button. The console will show the output in the format that you selected, either XML or in JSON format.

Once you are satisfied with the results, head to the bottom area of the console to grab the complete URL.

The resulting query:

http://query.yahooapis.com/v1/public/yql?q=%20SELECT%20*%20FROM%20music.artist.popular%20limit%205&diagnostics=true

The application can post this query to get the response and the response being in XML or JSON consistently across the web services makes this interface very luring to use.

Other Useful Links

Complete YQL Documentation
http://developer.yahoo.com/yql/docs/

YQL Screencast from the Yahoo! Developer Network
http://developer.yahoo.com/yos/screencasts/yql_screencast.html