Overview for YQL Users¶
In this Chapter:
- How to Run YQL Statements
- The Two-Minute Tutorial
- YQL Web Service URLs
- Summary of YQL Statements
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.
In your browser, run the YQL Console.
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.
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.
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.
To run the command in Your YQL Statement, click TEST. The returned place fields should only have the name and country sub-elements.
In the console, examine the URL below REST query:
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.
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.
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:
The next URL requires authorization by OAuth and allows access to both public and private data:
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.
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 Parameters¶
The following table lists the query parameters for the URLs of the YQL Web Service.
|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.|
|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.|
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:
The corresponding query alias can look like this:
Creating Queries Aliases¶
To create a short query alias based on a statement, follow these steps:
Go the YQL console.
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.
Using the YQL console, create a YQL statement or Web Service query that you want to save as a query alias.
Click on the Create Query Alias link, which is located in the upper-right corner of the YQL statement box.
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:
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.
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:
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:
Summary of YQL Statements¶
The following table lists all YQL statements:
|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...|
YQL has a number of reserved words that have special meanings and are part of the language syntax.
The following reserved words cannot be used in table names:
Variables and Filters¶
The following reserved words cannot be used as variables or filters:
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.