inline table-valued generator function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Explodes an array of structs into a table.

Syntax

inline(expr)

Arguments

  • expr: An ARRAY<STRUCT> expression.

Returns

A set of rows composed of the fields in the struct elements of the array expr. The columns produced by inline are the names of the fields.

If expr is NULL no rows are produced.

  • Applies to: check marked yes Databricks Runtime 12.1 and earlier:

    inline can only be placed in the SELECT list as the root of an expression or following a LATERAL VIEW. When placing the function in the SELECT list there must be no other generator function in the same SELECT list or UNSUPPORTED_GENERATOR.MULTI_GENERATOR is raised.

  • Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above:

    Invocation from the LATERAL VIEW clause or the SELECT list is deprecated. Instead, invoke inline as a table_reference.

Examples

Applies to: check marked yes Databricks Runtime 12.1 and earlier:

> SELECT inline(array(struct(1, 'a'), struct(2, 'b'))), 'Spark SQL';
 1  a Spark SQL
 2  b Spark SQL

> SELECT inline(array(struct(1, 'a'), struct(1, 'b'))),
         inline(array(struct('c', 1.0), struct('d', 2.0))),
         'Spark SQL';
 1  a Spark SQL
 2  b Spark SQL
Error: UNSUPPORTED_GENERATOR.MULTI_GENERATOR

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above:

> SELECT i.*, 'Spark SQL'
    FROM inline(array(struct(1, 'a'), struct(2, 'b'))) AS i;
 1  a Spark SQL
 2  b Spark SQL

> SELECT i1.*, i2.*, 'Spark SQL'
   FROM  inline(array(struct(1, 'a'), struct(1, 'b'))) AS i1,
         inline(array(struct('c', 1.0), struct('d', 2.0))) AS i2;
 1      a       c       1.0     Spark SQL
 1      b       c       1.0     Spark SQL
 1      a       d       2.0     Spark SQL
 1      b       d       2.0     Spark SQL