Overview for YQL Users

In this Chapter:

How to Run YQL Statements

You can run YQL statements in the following ways:

  • YQL Console: In your browser with the YQL Console.
  • SELECT statements using HTTP GET: A Web application can use an HTTP GET request when running SELECT statements, specifying the YQL statement as a query parameter of the Web Service URL.
  • INSERT, UPDATE, and DELETE statements using HTTP POST, PUT, or DELETE: A Web application can similarly use an HTTP GET, PUT, or DELETE for INSERT, UPDATE, and DELETE statements. The only exception is when you specify a JSONP callback, in which case you can use an HTTP GET request and specify a callback query parameter on the GET URI.

The Two-Minute Tutorial

This tutorial shows you how to run YQL statements and examine the resulting data with the YQL Console.

  1. In your browser, run the YQL Console.

  2. Under “Example Queries”, click get san francisco geo data. The console calls the YQL Web Service with the following query:

    select * from geo.places where text="san francisco, ca"

    This SELECT statement requests geo data about San Francisco. The “*” indicates that all fields of the geo.places table will be returned. In the filter of the WHERE clause, the string “san francisco, ca” is the value of the search query.

  3. Note the XML response in the FORMATTED VIEW tab. The information from the geo.places table is in the results element. To get the response in JSON format, select the JSON radio button and click TEST.

  4. In Your YQL Statement, replace the “*” with the name and country fields:

    select name, country from geo.places where text="san francisco, ca"

    Make sure that name and country are lowercase. Unlike SQL, in YQL the field and table names are case sensitive.

  5. To run the command in Your YQL Statement, click TEST. The returned place fields should only have the name and country sub-elements.

  6. In the console, examine the URL below REST query:

    http://query.yahooapis.com/v1/public/yql?q=select%20name%2C%20country%20from%20geo.places%20where%20text%3D%22san%20francisco%2C%20ca%22&diagnostics=true

    To call the YQL Web Service, an application would call an HTTP GET method on this URL. The q parameter in the URL matches the SELECT statement displayed under Your YQL Statement (except that characters such as spaces are URL encoded). The COPY URL button copies this URL to your clipboard, so that you can paste it into the source code of an application.

  7. To view YQL’s pre-defined tables, expand the Data Tables list on the right side of the console. You can run an example query on each of these tables by clicking the table name.

  8. Advanced: To view the description of a table, under Data Tables, expand the geo menu to see all of the Geo tables. Move your mouse cursor over the table name geo.places, then click desc. On the TREE VIEW tab, take a look at these nodes: “query->results->table”. The nodes under the “table” node contain information such as meta-data and search fields (input keys). For more information on input keys, see Remote Filters.

YQL Web Service URLs

The YQL Web Service has two URLs. The following URL allows access to public data, which does not require authorization:

`` http://query.yahooapis.com/v1/public/yql?[query_params] ``

The next URL requires authorization by OAuth and allows access to both public and private data:

`` http://query.yahooapis.com/v1/yql?[query_params] ``

The following URLs are for accessing data from Open Data Tables configured to use YQL streaming. The first URL is used to get public data, and the second requires authorization by OAuth and allows access to both public and private data.

http://query.yahooapis.com/v1/public/streaming/yql

http://query.yahooapis.com/v1/streaming/yql

Note

The public URL has a lower rate limit than the OAuth-protected URL. Therefore, if you plan to use YQL heavily, you should access the OAuth-protected URL.

.._yql_query_params:

YQL Query Parameters

The following table lists the query parameters for the URLs of the YQL Web Service.

Query Parameter Required? Default Description
q Yes (none) The YQL statement to execute, such as SELECT.
format No xml The format of the results of the call to the YQL Web Service. Allowed values: xml or json.
callback No (none) The name of the JavaScript callback function for JSONP format. If callback is set and if format=json, then the response format is JSON. For more information on using XML instead of JSON, see JSONP-X.
crossProduct No (none) When given the value optimized, the projected fields in SELECT statements that may be returned in separate item elements in the response are optimized to be in a single item element instead. See Optimizing the Response for more information.
diagnostics No true Diagnostic information is returned with the response unless this parameter is set to false.
debug No (none)

Enables network-level logging of each network call within a YQL statement or API query.

For more information, see, Enabling Logging.

env No (none) Allows you to use multiple Open Data Tables through a YQL environment file.
jsonCompat No (none) Enables lossless JSON processing. The only allowed value is new. See JSON-to-JSON Transformation.

YQL Query Aliases

To make YQL Web Service queries easier to remember and share, you can shorten them with query aliases. Take for example a normal YQL REST query:

http://query.yahooapis.com/v1/yql?q=select+*+from+weather.forecast+where+location%3D%40zip

The corresponding query alias can look like this:

http://query.yahooapis.com/v1/public/yql/jonathan/weather?zip=94025

Creating Queries Aliases

The easiest way to create query aliases is through the YQL console, though you can also use some of the more advanced functions through the yql.queries and yql.queries.query tables.

To create a short query alias based on a statement, follow these steps:

  1. Go the YQL console.

  2. Sign in with your Yahoo ID if you haven’t already. Click on the Sign In link, which is located along the top of the page. Signing in is required for saving and accessing query aliases.

  3. Using the YQL console, create a YQL statement or Web Service query that you want to save as a query alias.

  4. Click on the Create Query Alias link, which is located in the upper-right corner of the YQL statement box.

  5. When first creating a query alias, type in a prefix that will be associated with all your query aliases. For example, if you choose “prefix,” each query alias you create will start like this:

    http://query.yahooapis.com/v1/public/yql/prefix/

    Tip

    If you decide later that you want to start over with a new prefix for your query aliases, you can run the following YQL statement to delete your existing prefix:

    delete from yql.queries

    Keep in mind that deleting your prefix also deletes all its associated query aliases.

  6. Click Next and then type the actual name of the query alias. This alias name is appended to the end of your query after the prefix. If you choose “alias”, the resulting query will look like this:

    http://query.yahooapis.com/v1/public/yql/prefix/alias/

  7. After reviewing the public URL and the Authenticated URL, click Close to return to the YQL console.

All of your saved query aliases appear along the right side the YQL console under the Query Aliases heading. There, you can click on a alias name to see it in the REST query box. You can also click on the red X next to a particular alias name to delete it.

Variable Substitution with Query Aliases

Query aliases support variable substitution using the @ symbol. For example, the following YQL statement searches for questions about poetry that contain the name Auden:

select * from answers.search where query="Auden" and category_id=2115500137 and type="resolved"

Using variable substitution, you can replace Auden with the variable poet. Here is an example:

select * from answers.search where query=@poet and category_id=2115500137 and type="resolved"

A corresponding query alias with the required parameter can look like this:

http://query.yahooapis.com/v1/public/yql/prefix/poet_questions?poet="Auden"

Summary of YQL Statements

The following table lists all YQL statements:

Statement Example Description
SELECT SELECT * FROM social.profile WHERE guid=me Retrieves data from the specified table. See the SELECT Statement chapter for more information.
INSERT ``INSERT INTO table (key1, key2, key3) VALUES (‘value1’, ‘value2’, ‘value3’) `` Inserts data into the specified table. See the INSERT, UPDATE, DELETE statements chapter for more information.
UPDATE UPDATE (table) SET field1=value WHERE filter Updates data in the specified table. See the INSERT, UPDATE, DELETE statements chapter for more information.
DELETE DELETE FROM (table) WHERE filter Deletes data in the specified table. See the INSERT, UPDATE, DELETE statements chapter for more information.
SHOW TABLES SHOW TABLES Gets a list of the tables available in YQL.
DESC DESC social.connections Gets a description of the table.
USE USE "http://myserver.com/mytables.xml" AS mytable; Maps a table name to the URL of an Open Data Table.
  SELECT * FROM mytable WHERE...  
SET SET (name)=(value); Allows you to set up key values for use within Open Data Tables
  SELECT * FROM mytable WHERE...  

Reserved Words

YQL has a number of reserved words that have special meanings and are part of the language syntax.

Table Names

The following reserved words cannot be used in table names:

  • and
  • desc
  • from
  • in
  • or
  • show
  • table
  • tables
  • tenant
  • tenants
  • view
  • views
  • where

Variables and Filters

The following reserved words cannot be used as variables or filters:

  • and
  • in
  • or
  • where

Authorization

A YQL table contains either public or private data. An example of public data is search information, such as the local.search table. An application can access a public table through the /v1/public/yql endpoint, which does not require authorization. (For the full endpoint, see YQL Web Service URLs.)

A user’s personal information, such as the social.contacts table, is private. Access to private data requires the user’s approval. To access a private table, an application must use OAuth and the /v1/yql endpoint. YQL supports two-legged and three-legged OAuth.

For YQL code examples with OAuth, see the YQL Code Examples For details, see the Yahoo OAuth Quick Start Guide and the OAuth site.

Best Practices for Forming YQL Statements

Because YQL statements may contain user input and are passed as a query string parameter to the YQL Web Service URL, it is important to take some measures to make sure that they are properly formed. You can avoid malformed YQL statements that will lead to errors or potentionally contain harmful code by following the guidelines below:

  • Constrain and sanitize any user input that will be used as a key value.
  • Confirm that the values to input keys are enclosed in quotation marks.
  • URL-encode the YQL statement.
  • Use variable substitution for the values of input keys in the YQL statement.
  • For YQL statements that you plan to use often, use query aliases, which can also be used with variable substitution.