Vertica Analytics Platform Version 10.1.x Documentation

ROW

Represents structured data (structs) in external tables in the Parquet or ORC format only. Permitted field types are:

  • Primitive types
  • Other ROWs of primitive types
  • For Parquet only, arrays of primitive types
  • For Parquet only, heterogeneous combinations of arrays and ROWs (for details, see Reading Combinations of Structs and Arrays)

Syntax

In column definitions:

ROW(field type[, ...])

In literals:

ROW(value[, ...])

Row Input Format for Column Definition

In a column definition in an external table, a ROW consists of one or more comma-separated pairs of field names and types. This syntax is similar to that for columns in table definitions. The following example defines a ROW to represent an address struct in the data.

=> CREATE EXTERNAL TABLE customers (name VARCHAR, 
	address ROW(street VARCHAR, city VARCHAR, zipcode INT))
    AS COPY FROM '...' PARQUET;

ROWs can be nested; a field can have a type of ROW:

=> CREATE EXTERNAL TABLE employees(
    employeeID INT, 
    personal ROW(
      name VARCHAR, 
      address ROW(street VARCHAR, city VARCHAR, zipcode INT),
      taxID INT), 
    department VARCHAR)
  AS COPY FROM '...' PARQUET;

ROWs can contain arrays:

=> CREATE EXTERNAL TABLE customers(
  name VARCHAR,
  contact ROW(
    street VARCHAR,
    city VARCHAR,
    zipcode INT,
    email ARRAY[VARCHAR]
  ),
  accountid INT ) 
    AS COPY FROM '...' PARQUET;

The primitive types in the table definition must match those in the data. The ROW structure must also match; a ROW must contain all and only the fields in the struct in the data.

ROW columns have several restrictions:

  • The maximum nesting depth is 100.
  • Vertica tables support up to 9800 columns and fields. The ROW itself is not counted, only its fields.
  • ROW columns cannot use any constraints (such as NOT NULL) or defaults.
  • ROW fields cannot be auto_increment or setof.
  • A ROW definition must include at least one field.
  • "Row" is a reserved keyword within a ROW definition, but is permitted as the name of a table or column.
  • ROW columns cannot be modified using ALTER TABLE...ALTER COLUMN. You must drop and recreate the external table to change a ROW column.
  • External tables containing ROW columns cannot also contain identity, auto-increment, or sequence columns.

Row Input Format for Literals

In a literal, such as a value in a comparison operation, a ROW consists of one or more values. Omit field names; Vertica generates them automatically. If you do not coerce types, Vertica infers the types from the data values.

=> SELECT ROW('Amy',2,false);
                row
--------------------------------------------
 {"f0":"Amy","f1":2,"f2":false}
(1 row)

You can coerce types explicitly:

=> SELECT ROW('Amy',2.5::int,false::varchar);
               row
------------------------------------------
 {"f0":"Amy","f1":3,"f2":"f"}
(1 row)			

Supported Operators and Predicates

The following query operators and predicates may be used with ROWs of primitive types, including nested ROWs. They may not be used for ROWs containing array fields.

  • INNER and OUTER JOIN
  • Comparison, IN, BETWEEN (non-nullable filters only)
  • IS NULL, IS NOT NULL
  • CASE
  • GROUP BY, ORDER BY
  • SELECT DISTINCT

The following operators and predicates are not supported for ROW columns:

  • Math operators
  • Type coercion
  • BITWISE, LIKE
  • MLA (ROLLUP, CUBE, GROUPING SETS)
  • Aggregate functions including MAX, MIN, and SUM
  • Set operators including UNION, UNION ALL, MINUS, and INTERSECT

In comparison operations (including implicit comparisons like ORDER BY), a ROW is treated as the sequence of its field values. For example, the following two statements are equivalent:

GROUP BY ROW(zipcode, city)
GROUP BY zipcode, city				

NULL Inputs

A ROW in which each field is null is equivalent to a null ROW:

=> SELECT ROW(null, null, null) IS NULL;
 ?column?
----------
 t
(1 row)				

Row Output Format

ROW values read from external tables are output in JSON format as in the following example.

=> CREATE EXTERNAL TABLE customers (name VARCHAR, 
	address ROW(street VARCHAR, city VARCHAR, zipcode INT))
    AS COPY FROM '...' PARQUET;

=> SELECT address FROM customers WHERE address.city ='Pasadena';
                              address
--------------------------------------------------------------------
 {"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":91001}
 {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001}
 {"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":91001}
 {"street":"15 Raymond Dr","city":"Pasadena","zipcode":91003}
(4 rows)

The following table specifies the mappings from Vertica data types to JSON data types.

Vertica Type JSON Type
Integer Integer
Float Numeric
Numeric Numeric
Boolean Boolean
All others String

Escape single quotes in literal inputs using single quotes, as in the following example:

=> SELECT ROW('Howard''s house',2,false);
                      row
---------------------------------------------------
 {"f0":"Howard's house","f1":2,"f2":false}
(1 row)	

Using Rows in Views and Subqueries

You can use ROW columns to construct views and in subqueries. Consider employee and customer tables with the following definitions:

=> CREATE EXTERNAL TABLE customers(name VARCHAR, 
        address ROW(street VARCHAR, city VARCHAR, zipcode INT), accountID INT) 
    AS COPY FROM '...' PARQUET;
				
=> CREATE EXTERNAL TABLE employees(employeeID INT, 
        personal ROW(name VARCHAR, 
		address ROW(street VARCHAR, city VARCHAR, zipcode INT), 
		taxID INT), department VARCHAR)
    AS COPY FROM '...' PARQUET;

The following example creates a view and queries it.

=> CREATE VIEW neighbors (num_neighbors, area(city, zipcode))
AS SELECT count(*), ROW(address.city, address.zipcode)
FROM customers GROUP BY address.city, address.zipcode;
CREATE VIEW

=> SELECT employees.personal.name, neighbors.area FROM neighbors, employees
WHERE employees.personal.address.zipcode=neighbors.area.zipcode AND neighbors.nu
m_neighbors > 1;
        name        |                area
--------------------+-------------------------------------
 Sheldon Cooper     | {"city":"Pasadena","zipcode":91001}
 Leonard Hofstadter | {"city":"Pasadena","zipcode":91001}
(2 rows)