Using YQL Open Data Tables

In this Chapter:

Overview of Open Data Tables

YQL contains an extensive list of built-in tables for you to use that cover a wide range of Yahoo Web services and access to off-network data. Open Data Tables in YQL allow you to create and use your own table definitions, enabling YQL to bind to any data source through the SQL-like syntax and fetch data. Once created anyone can use these definitions in YQL.

An Open Data Table definition is an XML file that contains information as you define it, including, but not limited to the following:

  • Authentication and Security Options**: The kind of authentication you require for requests coming into your service. Also, whether you require incoming connections to YQL be made over a secure socket layer (via HTTPS).
  • Sample Query: A sample query that developers can run via YQL to get information back from this connection.
  • YQL Data Structure: Instructions on how YQL should create URLs that access the data available from your Web service. Also, an Open Data Table definition provides YQL with the URL location of your Web service along with the individual query parameters (keys) available to YQL.
  • Pagination Options: How YQL should “page” through results. If your service can provide staggered results, paging will allow YQL to limit the amount of data returned.

Invoking an Open Data Table Definition within YQL

If you want to access external data that is not provided through the standard YQL set of tables (accessible through the show tables query), YQL provides the use statement when you want to import external tables defined through your Open Data Table definition.

Invoking a Single Open Data Table

You can access a single Open Data Table using the USE and AS verbs:

USE "" AS mytable;
SELECT * FROM mytable WHERE...


The AS verb in the above example is optional. If you omit the AS verb, YQL uses the filename (without the .xml file ending) to name the table.

In the above query, USE precedes the location of the Open Data Table definition, which is then followed by AS and the table as defined within your Open Data Table definition. After the semicolon, the query is formed as would be any other YQL query. YQL fetches the URL above and makes it available as a table named mytable ``\ in the current request scope. The statements following ``use can then select or describe the particular table using the name mytable.

Invoking Multiple Open Data Tables

You can also specify multiple Open Data Tables by using multiple USE statements in the following manner:

USE "" as table1;
USE "" as table2;
SELECT * FROM table1 WHERE id IN (select id FROM table2)

Invoking Multiple Open Data Tables as an Environment

An easier way to use multiple Open Data Tables is to write or use a YQL environment file, which allows you to use multiple tables at once without the USE verb in your YQL statements.

An environment file is simply a text file that contains a list of USE and SET statements, typically ending with a ”.env” suffix.

Here is how an environment file can look:


The AS verb in the above example is optional. If you omit the AS verb, YQL uses the filename (without the .xml file ending) to name the table.

Once you upload the environment file to your server, you can simply access the YQL console and append the location of the file as follows:

Try this example in the YQL console


You can include multiple environment files at once by using multiple “env” query parameters. These are loaded in the order they appear in the query string. &env=

Working with Nested Environment Files

Advanced YQL users can better organize their Open Data Tables by nesting environment files within other environment files. For example, you can group similar tables into different environments and include each environment in a main (root) environment file as necessary. In addition to invoking environments, you can apply any SET statements to environments you invoke.

The following example both invokes multiple environments within a root environment file,

// Include the search group of tables
env '';
// Also use this api-key on all of these envs
set apikey='abcsecret' on search;

// Also include the Flickr group since my env requires them
env ';

Environment File Transversal

Usage Rules

In the example diagram above:

    1. is the root environment file.
    1. and (D) are environments that are invoked by (A).
  • (C) and (E) are environments that are invoked by (B) and (D) respectively.
  • Tables are added in the following order (C), (B), (E), (D), (A) assuming that all tables are defined after the environment inclusions.
  • The order in which you define tables can be changed by first using USE statements and then including other environments.
  • SET statements can be called only on tables defined in either the defining environment or any of it parents. For example, in the example above, sets in (A)/(B)/(C) can apply to tables defined in (C).
  • YQL detects and disallows circular references in environments inclusions.
  • The last table definition for a given name takes precedence in a given environment.
  • SET statements do not work across environments or across peers. For example, in the above example, (D) cannot set a key on tables defined in (B) or (C).
  • If the same environment is included again as a peer, the last inclusions wins and overrides all previously SET statements.
  • SET statements are scoped and the farthest parent with the most appropriate table prefix match wins.

Setting Key Values for Open Data Tables

For greater convenience and security, YQL allows you to set up key values for use within Open Data Tables. You can set values, such as passwords, API keys, and other required values, independently of YQL statements and API calls.

The following example sets the api_key value within the YQL statement itself:

select * from where api_key="1234567890" and query="environment"

The SET keyword allows you to set key values outside of a YQL statement, including environment files. The SET keyword uses the following syntax within an environment file:

SET api_key="1234567890" ON guardian;

In the example above, SET is followed by the key (api_key) and its value (1234567890). You must also specify the prefix of the table, which in this case is guardian.

Once you set the key value within an environment file, remove these values in the YQL statement:

select * from where query="environment"

Because key values set in an environment file using the SET keyword are not limited to a specific table binding, those keys must be defined as optional in the select binding to prevent them from being used as local filters in SELECT statements.

For example, suppose you want to set the game_status value for INSERT statements with the following:

SET game_status="Available to play" ON online_games;

Although you only intended to use game_status for INSERT statements, the key would still be used as a local filter in a SELECT statement unless it was declared as an optional key (with its required attribute set to "false") in the select binding, as shown here:

  <select itemPath="online_games.player.updates" produces="XML">
      <key id="game_status" type="xs:string" paramType="variable"  required="false" />

The following precedence rules apply when setting key values with the SET keyword:

  • Keys that are set within the YQL statement take precedence over keys that are set using the SET keyword.
  • If the set key is defined more than once, the most precise definition, based on the length of the table prefix, takes precedence.
  • If the set key is defined more than once with the same preciseness, the last definition is used.

Using SET to Hide Key Values or Data

To avoid exposing private data when you share YQL Open Data Tables, you can use a combination of YQL features to hide such data:

  1. Add your private values to an environment file using the SET keyword.
  2. Use the table to import the environment file or an Open Data Table with a memorable name. YQL provides you with a set of shared access keys.
  3. Use the shared execute or select access keys as you would an Open Data Table, environment file, or JavaScript.


Ensure that you place all USE and SET statements together respectively in one environment file to prevent private data being handed over to redefined tables.