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:

Tip

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:

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:

Tip

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:

http://developer.yahoo.com/yql/console/?env=http://datatables.org/alltables.env

Try this example in the YQL console

Tip

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.

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,

Environment File Transversal

Figure 6.1. Environment File Transversal


Usage Rules

YQL processes environment files using an in-order traversal. In the example diagram above:

  • (A) is the root environment file.
  • (B) and (D) are environments that are invoked by (A).
  • (C) and (E) are environments that are invoked by (E).
  • 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.

Table of Contents