Overview

The ROWS statement allows you to create datasets, such as small lookup dictionaries, directly from transform scripts, without having to add a dataset from scratch using the ingest API.

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 ROWS statement uses null.

Example

For example, use ROWS to create this simple dataset:

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 statements inside of each selector:

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, the columns are listed out of order, such (7 as num_letters, 'VT' as code). This is allowed, as the ROWS statement reorders columns as needed when it creates a dataset.