Open Data Tables Reference

The following reference describes the structure of an Open Data Table definition:

The following elements and sub-elements of YQL Open Data Tables are discussed in this reference:

table element

Full Path: root element

Example:

This is the root element for the document. A table is the level at which an end-user can 'select' information from YQL sources. A table can have many different bindings or ways of retrieving the data. However, we advise that a single table produce a single type of result data.

Attribute Value(s) Notes
xmlns URL The XML Schema file related to this Open Data Table definition.
securityLevel enumeration, any / app / user

The authorization level required to access.

any: Anonymous access; any user can access this table.

app: 2-legged OAuth; involves authorization without access to private user data.

user: 3-legged OAuth, involves authorization of access to user data.

For more information, refer to Open Data Tables Security and Access Control.

https boolean, true or false If true, the table is only available if the user is connected via HTTPS. If missing or false, either HTTP or HTTPS connections are acceptable.

Warning

If your table requires input that is deemed "private", such as any passwords, authentication keys, or other "secrets", you MUST ensure the https attribute within the table element is set to true.

meta element

Full Path: table/meta

Example:

Along with the table element, you are required to include the meta sub-element, which provides the following information:

Attribute Description Notes
sampleQuery A sample query that users can run to get output from this table. {table} should be used in place of the table name, so that the sample can run when used in different namespaces. Multiple sampleQuery elements may occur. Each sampleQuery may have a description attribute that contains a description about the sample.
documentationURL Additional information about this table or the select called by the table can be found here More then one documentationURL element may be included for each table.
description Plain text description about the table A description of the table.
author Information regarding the author of this Web service Examples of author information include an unformatted email, name, or other related information.

select / insert / update / delete elements

Full Path:

table/bindings/select

table/bindings/insert

table/bindings/update

table/bindings/delete

Example:

Situated within each bindings element, there are one of four keywords: select, insert, update, or delete.

The select element describes the information needed for YQL to read data from an API. The insert and update elements describe the information needed to add or modify data from an API, respectively. When removing data, the delete element is used to describe the necessary bindings.

When a keyword such as select or update is repeated within the bindings array, it can be considered to be an alternative way for YQL to call a remote server to get the same type of structured data. Typically, this is needed when the service supports different sets of query parameters (YQL's "keys") or combinations of optional query parameters.

Attribute Value(s) Notes
itemPath URLs A dot-path that points to where the repeating data elements occur in the response format. These are the "rows" of your table.
pollingFrequencySeconds Integer The frequency in seconds that the YQL Engine should update the response from a data source. See YQL Streaming for more information.
produces enumeration, XML / JSON The type of data coming back from the Web service.

Note

Unlike XML, JSON objects have no "root" node. To work with the dot notation, YQL creates a "pseudo" root node for JSON responses called "json". If you need to return a sub-structure from your Open Data Table that fetches or produces JSON, you'll need to add "json" at the root of the path.

function Element

A stored procedure to bind and run, similar to the built-in Sort and Other Functions. For more information on the function element, see Creating YQL Customized Functions.

.

Full Path: table/bindings/function

Example:

Attribute Type Description
name string The name of the function.
type enumeration The type of function. Allowed values:
  • stream - (default) This type of function gets called for each item at which point it can modify and return the modified version of the item. Example: Decorate an item with extra information.
  • reduce - This type of function gets called for each item which is collected/stored and then finally, gets one last call to return the reduced or aggregated response. Examples of YQL built-in reduce functions: sort, truncate, count.

url element

Full Path: table/bindings/select/urls/url

This is where YQL and the table supporting the service come together. The url element describes the URL that needs to be executed to get data for this table, given the keys in the key elements.

Note

The CDATA/TEXT for this element contains the URL itself that utilizes substitution of values at runtime based on the uri template spec (v3). The names of the values will be substituted and formatted according to the uri template spec, but the simplest method is simply to enclose a key name with curly braces ( {} ):

  • All {name}keys found in the URL will be replaced by the same id key value in the keys elements.
  • YQL currently supports both http and https protocols.

Example:

https://prod.gnipcentral.com/publishers/{publisher}/notification/{bucket}.xml

YQL will look for key elements with the names publisher and bucket. If the YQL developer does not provide those keys in the WHERE clause (and they are not optional), then YQL detects the problem and will produce an error. If an optional variable is not provided, but is part of the Open Data Table definition, it will be replaced with an empty string. Otherwise, YQL will substitute the values directly into the URL before executing it.

execute element

Full Path:table/bindings/select/execute

The execute sub-element allows you to invoke server-side JavaScript in place of a GET request. For more information on executing JavaScript, refer to Executing JavaScript within Open Data Tables.

Example:

key / value / map elements

Full Paths:

table/bindings/[select/insert/update/delete]/inputs/key

table/bindings/[select/insert/update]/inputs/value

table/bindings/[select/insert/update/delete]/inputs/map

Example:

There are three type of elements available within the inputs element: key, value, and map.

key element

Each key element represents a named "key" that you provide in the WHERE or INTO clause of SELECT, INSERT, UPDATE, or DELETE statements. YQL inserts these values into the URL request before it is sent to the server. YQL inserts these values into the URL request if the paramType is set to query or path or header. For a variable type, the key named as the id of the element is made available in the execute section of the Open Data Table.

value element

Use the value element to assign a new "value" or update an existing one within an Open Data Table. The value element defines a field that can only be set as an input and therefore cannot be in YQL statements to satify the "where" clause. The value element only works with the INSERT and UPDATE verbs and in different ways.

When used with the insert keyword, the value element appears in the VALUE expression of the YQL statement, indicating that a new value is being passed into the YQL statement, as seen in the following example:

INSERT into bitly.shorten (login, apiKey, longUrl) VALUES ('YOUR_LOGIN', 'YOUR_API_KEY', 'http://yahoo.com')

When used with the update keyword, the value element is called from the SET portion of the YQL statement. This indicates that you are "setting" a particular value, as seen in the following example:

UPDATE table SET status='Reading the YQL Guide' where guid = me;

map element

Use the map element when you want to use dynamic keys. With this element, YQL uses the value you pass in through the YQL statement as a variable. This variable is used within the execute portion of your Open Data Table to determine what action to take. For example, you may set up a YQL Open Data Table that updates either bit.ly, delicio.us, or tinyurl, depending on the value you specify in the YQL statement.

For a dynamic key called type, the actual ID in a YQL query would look like the following:

field.type = 'Java'

Note

In the absence of the map element as a binding, all identifiers, not corresponding to a binding element and that appear in a YQL query, are treated as local filters.

The map element can be used for all the four paramTypes. Here is an example of the map element being used in a path:

<map id="field" paramType="path"/>

For a query containing the relational expression field.type='rss', only the dynamic parameter name type would be substituted in the urls element. The URI template would look like the following:

http://rss.news.yahoo.com/{type}/topstories

Key, Value, and Map Element Support within YQL Statements

The following table shows the keywords that support the key, value, and map elements:

  select insert update delete
key yes yes yes yes
value no yes yes no
map yes yes yes yes
Attributes for Key, Value, and Map Elements

The following table provides the attributes available within key, value and map elements:

Attribute Value(s) Supported Keywords Notes
id string select, insert, update, delete The name of the key. This represents what the user needs to provide in the WHERE clause.
as string select, insert, update, delete

The alias of the key used in YQL statements.

If the Web source used in the Open Data Table uses a cryptic or poorly named query parameter, you can use as to specify an alias that developers use in the YQL statement. For example, perhaps you have an id called "q" within your Open Data Table, which actually is a search parameter.

Without aliasing, the equivalent YQL statement would look like this:

select * from google.search where q = "pizza"

You can use the as attribute to create an alias in the following way:

<key id="q" as="query" type="xs:string" paramType="query"/>

You then can use search in your YQL statement like this:

select * from google.search where query = "pizza"
type string select, insert, update, delete The type of data coming back from the Web service.
required boolean select, insert, update, delete A boolean that answers the question: Is this key required to be provided in the WHERE clause on the left-hand side of an equality statement? If not set, any key is optional.
paramType enumeration select, insert, update, delete

Determines how this key is represented and passed on to the Web service:

- query: Add the id and its value as a id=value query string parameter to the URL.

- matrix: Add the id and its value as a id=value matrix parameter to the URL path.

- header: Add the id and its value as a id: value as an HTTP header to the URL request.

- path: Substitute all occurrences of {id} in the url string with the value of the id. Not necessarily only in the path.

- variable: Use this key or field as a variable to be used within the execute sub-element instead of being used to format or form the URL.

default string select, insert, update, delete This value is used if one isn't specified by the developer in the SELECT.
private boolean select, insert, update, delete Hide this key's value to the user (in both "desc" and "diagnostics"). This is useful for parameters like appid and keys.
const boolean select, insert, update, delete A boolean that indicates whether the default attribute must be present and cannot be changed by the end user. Constant keys are not shown in desc [table].
batchable boolean select, update, delete

A boolean which answers the question: Does this select and URL support multiple key fetches/requests in a single request (batched fetching)?

For more information about batching requests, refer to Batching Multiple Calls in a Single Request.

maxBatchItems integer select, update, delete

How many requests should be combined in a single batch call.

For more information about batching requests, refer to Batching Multiple Calls in a Single Request.

Aliasing within Key, Value, and Map Elements

If you have an obscurely named id in your Open Data Table, you can use an alias to refer to it within YQL statements. For example, perhaps you have an id called "q" within your Open Data Table, which actually is a search parameter. You can use "as" to create an alias in the following way:

<key id="q" as=type="xs:string" paramType="query"/>

select * from google.search where search ="pizza"

paging element

Full Path: table/bindings/select/paging

Examples:

This element describes how YQL should "page" through the web service results, if they span multiple pages, or the service supports offset and counts.

Attribute Value(s) Supported Keywords Notes
model offset, page, url select

The type of model to use to fetch more than the initial result set from the service.

The offset refers to services that allow arbitrary index offsets into the result set.

Use the page value for services that support distinct "pages" of some number of results.

Use the url value for services that support a URL to access further data.

matrix true, false select A boolean that answers the question: Is the parameter matrix style (part of the URI path; delimited), or query parameter style? The default value is false.

Tip

When using the url paging model, you can also use the pagesize element to, if the Web service allows, adjust the number of results returns at once.

pagesize element

Full Path: table/bindings/select/paging/pagesize

This element contains Information about how the number of items per request can be specified.

Attribute Value(s) Notes
max integer The maximum size of the requested page. If the total requested is below the max pagesize, then the pagesize will be the total requested. Otherwise, the max pagesize will be the size of the page requested.
id string The name of the parameter that controls this page size.
start element

Full Path: table/bindings/select/paging/start

This element contains Information about how the "starting" item can be specified in the set of results.

Attribute Value(s) Notes
default integer The starting item number (generally 0 or 1); for paging style this value always defaults to 1.
id string The name of the parameter that controls the starting page/offset.
total element

Full Path: table/bindings/select/paging/total

This element contains Information about the total number of results available per request by default.

Attribute Value(s) Notes
default integer The number of items that come back by "default" in YQL if the () syntax is not used when querying the table.
nextpage element

Full Path: table/bindings/select/paging/nextpage

This element contains the location of the next page of results. This is an optional element that is used in conjunction with the parent url element.

Attribute Value(s) Notes
path string The path to the next page of results

Table of Contents