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 guardian.content.search 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 guardian.content.search 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:

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 yql.storage.admin 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.

Important

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.

Table of Contents