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

Examples

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.