ROWS creates datasets, such as small lookup dictionaries, directly from transform scripts.
ROWS statement allows you to create a dataset directly in a transformation script without having to add a dataset from scratch using the ingest API or user interface.
The syntax for
ROWS is as follows:
- List one or more selectors, separated by commas.
- In each selector, specify the values in any order. Use literal values, or expressions of literal values.
- If you omit a field value, the
ROWSstatement uses null.
For example, you can use
ROWS to create a simple dataset of state postal abbreviations for New England states.
ne_state_abbrev: ROWS ('MA' as code, 'Massachusetts' as name), ('CT' as code, 'Connecticut' as name), ('VT' as code, 'Vermont' as name), ('NH' as code, 'New Hampshire' as name), ('RI' as code, 'Rhode Island' as name), ('ME' as code, 'Maine' as name);
The following example uses the
length() function inside of some of the selectors to determine the number of letters in each state name. See length().
num_letters_in_state_name: ROWS ('MA' as code, 13 as num_letters), (7 as num_letters, 'VT' as code), ('CT' as code, length('Connecticut') as num_letters), ('NH' as code, length('New' || ' ' || 'Hampshire') as num_letters), ('RI' as code, length('Rhode') + length('Island') as num_letters), ('' as code);
In this example, some selectors list the column names in a different order, such
(7 as num_letters, 'VT' as code). This is allowed, as the
ROWS statement reorders columns as needed when it creates the dataset.
Updated 10 months ago