Skip to content

STRUCT columns in lineage, explained

Some columns hold more than one value. A BigQuery STRUCT column or a Trino ROW column holds a small record with named fields. A field can hold another record, or a list of records. This page calls all of these columns STRUCT columns.

Data lineage tells you where the value of each column comes from. A STRUCT column adds one more question:

Does this value come from the whole STRUCT column, or from one field inside it? And from which field?

GSP Java answers this question in the same way for BigQuery, Athena, Presto and Trino. This page shows the answer with small SQL examples and the real lineage output of GSP Java. It also explains why the answer is correct.

Who this page is for

You use the data lineage of GSP Java (DataFlowAnalyzer) on SQL that reads or writes STRUCT columns. You want to know what the lineage looks like, and why it looks like that. You do not need to know how GSP works inside.

Verified against
GSP Java version 4.2.14, not released yet. The samples ran on a build of the master branch at commit 3ca283f96. That build still reports TBaseType.versionid 4.2.13. The released 4.2.13 and older versions give different lineage for STRUCT columns.
Page last verified 2026-10-02
Samples 1 Java program, run on 41 SQL examples
Java versions the samples give the same output on JDK 8, 17 and 21

Every block of program output on this page was copied from a real run of that build. None of it is written by hand. See Refreshing this page for how to bring the page forward to a newer release.

All examples on this page use one Java program, StructLineage, from section 2.4. Put the GSP jar on the class path (on Windows, use ; instead of : between the entries):

1
2
javac -cp gsqlparser-4.2.14.jar StructLineage.java
java  -cp gsqlparser-4.2.14.jar:. StructLineage dbvbigquery orders_raw.sql query.sql

1. What is a STRUCT column?

1.1 A column that holds a record

In this BigQuery table, each order keeps all data about its customer in one column, customer:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- BigQuery: orders_raw.sql
CREATE TABLE orders_raw (
  order_id INT64,
  customer STRUCT<
    name    STRING,
    phone   STRING,
    address STRUCT<city STRING>
  >,
  items    ARRAY<STRUCT<item_name STRING, qty INT64>>
);

One row of this table can look like this:

1
2
3
order_id: 1001
customer: {name: "Ann", phone: "555-0100", address: {city: "Paris"}}
items:    [{item_name: "pen", qty: 2}, {item_name: "ink", qty: 1}]
  • customer is a STRUCT column. Its value is a record with three fields: name, phone and address.
  • address is a field, and it is a STRUCT too. It has its own field, city. We say that address is a nested STRUCT.
  • items is an ARRAY of STRUCTs: a list of records. Each record in the list is an element of the array. Each element has the fields item_name and qty.

A query reads a field with dots: customer.name, customer.address.city. It reads the elements of an array with UNNEST, or with a subscript such as items[OFFSET(0)].

1.2 The tables on this page

The BigQuery examples use the table orders_raw above. Save it as the file orders_raw.sql.

The Trino and Presto examples use the table events. Its column payload is a ROW, the Trino name for a STRUCT. Save it as events.sql:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- Trino, Presto: events.sql
CREATE TABLE events (
  event_id BIGINT,
  payload  ROW(
    address ROW(city VARCHAR),
    phone   VARCHAR,
    items   ARRAY(ROW(name VARCHAR, qty INTEGER))
  )
)
WITH (format = 'PARQUET', external_location = 's3://my-bucket/events/');

Athena creates tables with the Hive syntax, so the same table looks different there. Athena queries use Trino SQL. Save it as events_athena.sql:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Athena: events_athena.sql
CREATE EXTERNAL TABLE events (
  event_id BIGINT,
  payload  STRUCT<
    address: STRUCT<city: STRING>,
    phone:   STRING,
    items:   ARRAY<STRUCT<name: STRING, qty: INT>>
  >
)
STORED AS PARQUET
LOCATION 's3://my-bucket/events/';

Both tables read their data from files in s3://my-bucket/events/. GSP shows these files as a path, and the path gives the data of each column that the table declares, and of each field declared inside a column. So the lineage of the table events starts with one edge per column, events.event_id <- 's3://my-bucket/events/'.event_id and events.payload <- 's3://my-bucket/events/'.payload, and one per declared field, such as events.payload.address.city <- 's3://my-bucket/events/'.payload.address.city. A query that reads payload.address.city can therefore be traced back to the files.

1.3 STRUCT columns in other databases

Most analytic databases have a type like this, with other names and another syntax:

Database STRUCT type Array of STRUCTs Read a field Read the elements of an array
BigQuery STRUCT<city STRING> ARRAY<STRUCT<...>> customer.address.city UNNEST(items), items[OFFSET(0)]
Trino, Presto ROW(city VARCHAR) ARRAY(ROW(...)) payload.address.city UNNEST(...), items[1]
Athena STRUCT<city: STRING> in CREATE EXTERNAL TABLE ARRAY<STRUCT<...>> payload.address.city UNNEST(...), items[1]
Databricks, Spark SQL, Hive STRUCT<city: STRING> ARRAY<STRUCT<...>> customer.address.city explode(items), items[0]
Snowflake VARIANT or OBJECT, with no declared fields ARRAY customer:address.city FLATTEN(...), items[0]
Redshift SUPER, with no declared fields SUPER o.customer.address.city FROM orders_raw AS o, o.items AS i
PostgreSQL a composite type, CREATE TYPE ... AS (...) an array of it, customer_t[] (customer).address unnest(items), items[1]
Oracle an object type, CREATE TYPE ... AS OBJECT (...) VARRAY, nested table o.customer.address.city TABLE(o.items)

GSP Java builds the STRUCT lineage that this page describes for BigQuery, Athena, Presto and Trino, and the same tree of fields for the STRUCT columns of Databricks, Hive and Spark SQL (section 12). Section 13 shows what GSP gives today for the other databases.

2. How GSP shows a STRUCT column

2.1 A tree of nodes

In GSP lineage, a table has nodes, and edges connect the nodes. A normal column is one node. A STRUCT column is a small tree of nodes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
orders_raw
├── order_id
├── customer                       the column
│   ├── customer.name              a field
│   ├── customer.phone             a field
│   └── customer.address           a field that is a STRUCT
│       └── customer.address.city  a field of that field
└── items                          the column (an ARRAY)
    └── items[*]                   an element of the array
        ├── items[*].item_name     a field of an element
        └── items[*].qty

There are three kinds of nodes:

  • A column node is a column that the table declares: customer, items.
  • A field node is one field of a column, at any depth: customer.name, customer.address, customer.address.city. A field that is a STRUCT, such as customer.address, has its own node too.
  • An element node stands for the elements of an array: items[*] is an element of items, and items[*].item_name is the field item_name of an element. GSP writes [*] because a query can read any element. Which element it reads is known only when the query runs.

Run the sample program on the table alone, and GSP lists the nodes of the table:

1
java -cp gsqlparser-4.2.14.jar:. StructLineage dbvbigquery orders_raw.sql

1
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty

Trino, Presto and Athena build the same tree for events:

1
java -cp gsqlparser-4.2.14.jar:. StructLineage dbvtrino events.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
table events: event_id, payload, payload.address, payload.address.city, payload.phone, payload.items, payload.items[*], payload.items[*].name, payload.items[*].qty
events.event_id <- 's3://my-bucket/events/'.event_id
events.payload <- 's3://my-bucket/events/'.payload
events.payload.address <- 's3://my-bucket/events/'.payload.address
events.payload.address.city <- 's3://my-bucket/events/'.payload.address.city
events.payload.phone <- 's3://my-bucket/events/'.payload.phone
events.payload.items <- 's3://my-bucket/events/'.payload.items
events.payload.items[*] <- 's3://my-bucket/events/'.payload.items[*]
events.payload.items[*].name <- 's3://my-bucket/events/'.payload.items[*].name
events.payload.items[*].qty <- 's3://my-bucket/events/'.payload.items[*].qty

1
java -cp gsqlparser-4.2.14.jar:. StructLineage dbvathena events_athena.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
table events: event_id, payload, payload.address, payload.address.city, payload.phone, payload.items, payload.items[*], payload.items[*].name, payload.items[*].qty
events.event_id <- 's3://my-bucket/events/'.event_id
events.payload <- 's3://my-bucket/events/'.payload
events.payload.address <- 's3://my-bucket/events/'.payload.address
events.payload.address.city <- 's3://my-bucket/events/'.payload.address.city
events.payload.phone <- 's3://my-bucket/events/'.payload.phone
events.payload.items <- 's3://my-bucket/events/'.payload.items
events.payload.items[*] <- 's3://my-bucket/events/'.payload.items[*]
events.payload.items[*].name <- 's3://my-bucket/events/'.payload.items[*].name
events.payload.items[*].qty <- 's3://my-bucket/events/'.payload.items[*].qty

2.2 The names of the nodes

The name of a node is its path, from the column down to the field, with dots between the steps:

Node name Meaning
customer the column customer
customer.address the field address of the column customer
customer.address.city the field city of the field address of the column customer
items[*] an element of the array column items
items[*].item_name the field item_name of an element of items

In the lineage output, a field node is in the list of columns of its table, after the column that it belongs to. The first part of its name is the name of that column.

2.3 Five rules

GSP follows five rules when it turns a read into an edge:

  1. A read of the whole column comes from the column node.
  2. A read of a field of a table comes from exactly that field node. It does not come from the whole column, and it does not come from the other fields. This holds for a read through a CTE or a subquery too; see section 9.
  3. A read of a column or of a field gives the same edge with or without the DDL of the table. The DDL adds the nodes of the fields that the query does not read. It also tells GSP which fields exist, so GSP can list the fields of customer.* (section 6) and ignore a field that does not exist (section 4.2).
  4. When GSP cannot prove which table owns the column, it draws no edge.
  5. GSP does not invent a node or an edge that the SQL does not prove.

The sections below show each rule with an example. Section 11 explains why these rules give correct lineage.

2.4 The sample program

This program runs DataFlowAnalyzer on one or more SQL files and prints three things: the nodes of each table and view, the edges, and the messages of the analysis.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.dlineage.DataFlowAnalyzer;
import gudusoft.gsqlparser.dlineage.dataflow.model.Option;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.column;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.dataflow;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.error;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.relationship;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.sourceColumn;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.table;

import java.io.File;

public class StructLineage {
    public static void main(String[] args) throws Exception {
        EDbVendor vendor = EDbVendor.valueOf(args[0]);     // for example dbvbigquery
        File[] files = new File[args.length - 1];           // the SQL files, in order
        for (int i = 1; i < args.length; i++) {
            files[i - 1] = new File(args[i]);
        }

        Option option = new Option();
        option.setVendor(vendor);
        option.setSimpleOutput(true);   // only source -> target, no intermediate result sets
        option.setOutput(false);        // keep the result in memory, do not write XML

        DataFlowAnalyzer analyzer = new DataFlowAnalyzer(files, option);
        analyzer.generateDataFlow();
        dataflow df = analyzer.getDataFlow();

        // 1. The nodes: each table and view, with its columns and field nodes
        for (table t : df.getTables()) {
            System.out.println("table " + t.getName() + ": " + names(t));
        }
        for (table v : df.getViews()) {
            System.out.println("view " + v.getName() + ": " + names(v));
        }
        // 2. The edges: where the value of each target comes from
        for (relationship r : df.getRelationships()) {
            if (!"fdd".equals(r.getType())) {
                continue;               // fdd = the value comes from the sources
            }
            StringBuilder line = new StringBuilder(
                    r.getTarget().getParent_name() + "." + r.getTarget().getColumn() + " <-");
            for (sourceColumn s : r.getSources()) {
                line.append(' ').append(s.getParent_name()).append('.').append(s.getColumn());
            }
            System.out.println(line);
        }
        // 3. The messages, for example an orphan column
        for (error e : df.getErrors()) {
            System.out.println("message: " + e.getErrorMessage());
        }
        analyzer.dispose();
    }

    static String names(table t) {
        StringBuilder names = new StringBuilder();
        for (column c : t.getColumns()) {
            if ("RelationRows".equals(c.getName())) {
                continue;               // a system column, not data
            }
            names.append(names.length() == 0 ? "" : ", ").append(c.getName());
        }
        return names.toString();
    }
}

How to read the output:

  • table orders_raw: order_id, customer, ... lists the nodes of a table. view v: ... lists the columns of a view.
  • v.city <- orders_raw.customer.address.city is an edge: the value of the view column v.city comes from the node customer.address.city of the table orders_raw.
  • A line that starts with message: is a message of the analysis, such as an orphan column (section 10.1).

The program uses the simple output (Option.setSimpleOutput(true)): each edge goes straight from a source node to a target column. Without it, GSP also shows the intermediate result sets of the query, such as RS-1, between them.

Most examples on this page run twice. The tab With the DDL runs the query after the table file of its database from section 1.2: orders_raw.sql, events.sql or events_athena.sql. For BigQuery:

1
java -cp gsqlparser-4.2.14.jar:. StructLineage dbvbigquery orders_raw.sql query.sql

The tab Without DDL runs the query alone, so GSP must find the columns and fields from the query itself:

1
java -cp gsqlparser-4.2.14.jar:. StructLineage dbvbigquery query.sql

3. Read a STRUCT column

3.1 The whole column

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT customer AS c FROM orders_raw;

1
2
3
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: c
v.c <- orders_raw.customer

1
2
3
table orders_raw: customer
view v: c
v.c <- orders_raw.customer

v.c holds the whole record, so it comes from the column node customer (rule 1). There is one edge, not one edge for each field: the fields are inside customer, and the tree already shows that.

3.2 One field

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT customer.address.city AS city FROM orders_raw;

1
2
3
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: city
v.city <- orders_raw.customer.address.city

1
2
3
table orders_raw: customer, customer.address, customer.address.city
view v: city
v.city <- orders_raw.customer.address.city

The query reads only the city, so the edge goes to exactly the node customer.address.city (rule 2). It does not go to customer, and it does not go to customer.phone.

Without the DDL, GSP still knows that customer is a column and not a table: customer is not a table in the FROM clause, so customer.address.city must be a column with fields. GSP creates the nodes on the path to the field, customer and customer.address, and it draws the edge from the field.

3.3 A field that is a STRUCT

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT customer.address AS address FROM orders_raw;

1
2
3
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: address
v.address <- orders_raw.customer.address

1
2
3
table orders_raw: customer, customer.address
view v: address
v.address <- orders_raw.customer.address

customer.address is a record with its own field, city. The edge goes to the node customer.address. It does not go to customer.address.city, and it does not go to the other fields of customer, such as customer.phone.

3.4 A table alias

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT o.customer.address.city AS city FROM orders_raw AS o;

1
2
3
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: city
v.city <- orders_raw.customer.address.city

1
2
3
table orders_raw: customer, customer.address, customer.address.city
view v: city
v.city <- orders_raw.customer.address.city

o is the alias of orders_raw. GSP removes the alias and reads the rest of the name as the column and its fields. The result is the same node as in section 3.2.

3.5 Several reads in one query

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT customer, customer.name, customer.address.city FROM orders_raw;

1
2
3
4
5
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: customer, name, city
v.customer <- orders_raw.customer
v.name <- orders_raw.customer.name
v.city <- orders_raw.customer.address.city

1
2
3
4
5
table orders_raw: customer, customer.name, customer.address, customer.address.city
view v: customer, name, city
v.customer <- orders_raw.customer
v.name <- orders_raw.customer.name
v.city <- orders_raw.customer.address.city

Each view column has its own edge, to its own node.

3.6 Trino, Presto and Athena

The same reads in Trino SQL, on the table events. The three databases print the same lines, so the program output is shown once:

1
2
3
4
5
-- Trino, Presto, Athena
CREATE VIEW v AS
SELECT payload AS p, payload.phone AS phone, payload.address AS address,
       payload.address.city AS city, e.payload.address.city AS city2
FROM events AS e;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
table events: event_id, payload, payload.address, payload.address.city, payload.phone, payload.items, payload.items[*], payload.items[*].name, payload.items[*].qty
view v: p, phone, address, city, city2
events.event_id <- 's3://my-bucket/events/'.event_id
events.payload <- 's3://my-bucket/events/'.payload
events.payload.address <- 's3://my-bucket/events/'.payload.address
events.payload.address.city <- 's3://my-bucket/events/'.payload.address.city
events.payload.phone <- 's3://my-bucket/events/'.payload.phone
events.payload.items <- 's3://my-bucket/events/'.payload.items
events.payload.items[*] <- 's3://my-bucket/events/'.payload.items[*]
events.payload.items[*].name <- 's3://my-bucket/events/'.payload.items[*].name
events.payload.items[*].qty <- 's3://my-bucket/events/'.payload.items[*].qty
v.p <- events.payload
v.phone <- events.payload.phone
v.address <- events.payload.address
v.city <- events.payload.address.city
v.city2 <- events.payload.address.city

1
2
3
4
5
6
7
table events: payload, payload.phone, payload.address, payload.address.city
view v: p, phone, address, city, city2
v.p <- events.payload
v.phone <- events.payload.phone
v.address <- events.payload.address
v.city <- events.payload.address.city
v.city2 <- events.payload.address.city

Trino first tries to read the start of a name with dots as a table in the FROM clause: the alias e here, or a qualified name such as sch.events. The rest of the name is then a column and its fields. When no table matches, the first part is a column (payload). GSP reads the names in the same way.

3.7 A field inside PIVOT

The aggregate of a BigQuery PIVOT can read a field. Here COUNT(customer.name) reads the field customer.name, so the column 1 that the PIVOT makes comes from that field and from order_id, the column whose values name the new columns. With the DDL, the other column of the table, items, is passed on as a grouping column, which GSP prints as ITEMS:

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT * FROM orders_raw PIVOT(COUNT(customer.name) FOR order_id IN (1));

1
2
3
4
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: ITEMS, 1, *
v.ITEMS <- orders_raw.items
v.1 <- orders_raw.customer.name orders_raw.order_id

1
2
3
table orders_raw: customer, customer.name, order_id
view v: 1, *
v.1 <- orders_raw.customer.name orders_raw.order_id

4. With and without DDL

4.1 The edges are the same

Look again at the two tabs of each example in section 3. The edges are always the same. Only the list of nodes changes:

  • With the DDL, the table lists all nodes that the DDL declares, also the fields that the query does not read, such as customer.phone.
  • Without DDL, the table lists only the nodes that the query reads, and the nodes on the path to them.

This is rule 3. It is important: when you add the DDL of a table later, the lineage of your queries does not change. You only see more of the table.

4.2 A field that the DDL does not declare

The DDL of orders_raw has no field customer.email:

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT customer.email AS email FROM orders_raw;

1
2
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: email

1
2
3
table orders_raw: customer, customer.email
view v: email
v.email <- orders_raw.customer.email

With the DDL, GSP knows that customer has no field email, and BigQuery would reject this query. So GSP creates no node and no edge, in the same way as for a column that the table does not declare (rule 5). Without DDL, GSP cannot know this, and the query is the only information that it has.

5. Arrays of STRUCTs

5.1 BigQuery: UNNEST

UNNEST(o.items) AS i gives one row for each element of items. i is the element, and i.item_name is a field of the element:

1
2
3
4
-- BigQuery
CREATE VIEW v AS
SELECT o.order_id, i AS item, i.item_name, i.qty
FROM orders_raw AS o CROSS JOIN UNNEST(o.items) AS i;

1
2
3
4
5
6
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: order_id, item, item_name, qty
v.order_id <- orders_raw.order_id
v.item <- orders_raw.items[*]
v.item_name <- orders_raw.items[*].item_name
v.qty <- orders_raw.items[*].qty

1
2
3
4
5
6
table orders_raw: items, items[*], items[*].item_name, items[*].qty, order_id
view v: order_id, item, item_name, qty
v.order_id <- orders_raw.order_id
v.item <- orders_raw.items[*]
v.item_name <- orders_raw.items[*].item_name
v.qty <- orders_raw.items[*].qty

The whole element comes from the element node items[*], and each field from its own node below it.

5.2 A subscript

In Trino SQL, items[1] is the first element of the array:

1
2
3
-- Trino, Presto, Athena
CREATE VIEW v AS
SELECT payload.items[1].name AS first_item FROM events;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
table events: event_id, payload, payload.address, payload.address.city, payload.phone, payload.items, payload.items[*], payload.items[*].name, payload.items[*].qty
view v: first_item
events.event_id <- 's3://my-bucket/events/'.event_id
events.payload <- 's3://my-bucket/events/'.payload
events.payload.address <- 's3://my-bucket/events/'.payload.address
events.payload.address.city <- 's3://my-bucket/events/'.payload.address.city
events.payload.phone <- 's3://my-bucket/events/'.payload.phone
events.payload.items <- 's3://my-bucket/events/'.payload.items
events.payload.items[*] <- 's3://my-bucket/events/'.payload.items[*]
events.payload.items[*].name <- 's3://my-bucket/events/'.payload.items[*].name
events.payload.items[*].qty <- 's3://my-bucket/events/'.payload.items[*].qty
v.first_item <- events.payload.items[*].name

1
2
3
table events: payload, payload.items, payload.items[*], payload.items[*].name
view v: first_item
v.first_item <- events.payload.items[*].name

The edge goes to payload.items[*].name. All elements of an array have the same fields, and lineage shows where data comes from, not which row: the field name of the first element is the field name of an element.

A BigQuery subscript reads the element in the same way, so items[OFFSET(0)].item_name comes from items[*].item_name:

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT items[OFFSET(0)].item_name AS first_item FROM orders_raw;

1
2
3
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: first_item
v.first_item <- orders_raw.items[*].item_name

1
2
3
table orders_raw: items, items[*], items[*].item_name
view v: first_item
v.first_item <- orders_raw.items[*].item_name

5.3 Trino, Presto and Athena: UNNEST

1
2
3
4
-- Trino, Presto, Athena
CREATE VIEW v AS
SELECT e.event_id, t.item AS item
FROM events AS e CROSS JOIN UNNEST(e.payload.items) AS t(item);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
table events: event_id, payload, payload.address, payload.address.city, payload.phone, payload.items, payload.items[*], payload.items[*].name, payload.items[*].qty
view v: event_id, item
events.event_id <- 's3://my-bucket/events/'.event_id
events.payload <- 's3://my-bucket/events/'.payload
events.payload.address <- 's3://my-bucket/events/'.payload.address
events.payload.address.city <- 's3://my-bucket/events/'.payload.address.city
events.payload.phone <- 's3://my-bucket/events/'.payload.phone
events.payload.items <- 's3://my-bucket/events/'.payload.items
events.payload.items[*] <- 's3://my-bucket/events/'.payload.items[*]
events.payload.items[*].name <- 's3://my-bucket/events/'.payload.items[*].name
events.payload.items[*].qty <- 's3://my-bucket/events/'.payload.items[*].qty
v.event_id <- events.event_id
v.item <- events.payload.items[*]

1
2
3
4
table events: payload, payload.items, payload.items[*], event_id
view v: event_id, item
v.event_id <- events.event_id
v.item <- events.payload.items[*]

The column t.item of the UNNEST comes from the element node payload.items[*].

6. Read every field: customer.*

payload.* means "every field of payload":

1
2
3
-- Trino, Presto, Athena
CREATE VIEW v AS
SELECT payload.* FROM events;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
table events: event_id, payload, payload.address, payload.address.city, payload.phone, payload.items, payload.items[*], payload.items[*].name, payload.items[*].qty
view v: address, phone, items
events.event_id <- 's3://my-bucket/events/'.event_id
events.payload <- 's3://my-bucket/events/'.payload
events.payload.address <- 's3://my-bucket/events/'.payload.address
events.payload.address.city <- 's3://my-bucket/events/'.payload.address.city
events.payload.phone <- 's3://my-bucket/events/'.payload.phone
events.payload.items <- 's3://my-bucket/events/'.payload.items
events.payload.items[*] <- 's3://my-bucket/events/'.payload.items[*]
events.payload.items[*].name <- 's3://my-bucket/events/'.payload.items[*].name
events.payload.items[*].qty <- 's3://my-bucket/events/'.payload.items[*].qty
v.address <- events.payload.address
v.phone <- events.payload.phone
v.items <- events.payload.items

1
2
3
table events: payload
view v: *
v.* <- events.payload

With the DDL, GSP knows the fields: the view gets one column for each field of payload, and each column comes from its field node. Without DDL, GSP does not know the fields. The view gets one column *, and it comes from the column node payload, which stands for all of its fields.

BigQuery gives the same result with the DDL. Save this query as star.sql:

1
2
3
-- BigQuery: star.sql
CREATE VIEW v AS
SELECT customer.* FROM orders_raw;

1
java -cp gsqlparser-4.2.14.jar:. StructLineage dbvbigquery orders_raw.sql star.sql

1
2
3
4
5
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: name, phone, address
v.name <- orders_raw.customer.name
v.phone <- orders_raw.customer.phone
v.address <- orders_raw.customer.address

Without DDL, GSP does not know the fields of customer, so the view gets one column *, which reads the whole column:

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT customer.* FROM orders_raw;

1
2
3
table orders_raw: customer
view v: *
v.* <- orders_raw.customer

7. Build a STRUCT

7.1 STRUCT(...) in a SELECT list

A query can build a new STRUCT value from other columns:

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT STRUCT(customer.name AS name, order_id AS id) AS buyer FROM orders_raw;

1
2
3
4
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: buyer.name, buyer.id
v.buyer.name <- orders_raw.customer.name
v.buyer.id <- orders_raw.order_id

1
2
3
4
table orders_raw: customer, customer.name, order_id
view v: buyer.name, buyer.id
v.buyer.name <- orders_raw.customer.name
v.buyer.id <- orders_raw.order_id

The new STRUCT buyer has two fields, and each field has its own source. So the view gets one node for each field, buyer.name and buyer.id, and each one has its own edge.

7.2 INSERT into a STRUCT column

An INSERT without a column list fills the columns of the target table in order. A STRUCT(...) value fills the fields of a STRUCT column in order:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- BigQuery
CREATE TABLE orders_raw (
  order_id INT64,
  customer STRUCT<name STRING, phone STRING, address STRUCT<city STRING>>
);
CREATE TABLE contacts (
  id      INT64,
  contact STRUCT<name STRING, city STRING>
);
INSERT INTO contacts
SELECT order_id, STRUCT(customer.name, customer.address.city) FROM orders_raw;

1
2
3
4
5
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city
table contacts: id, contact, contact.name, contact.city
contacts.id <- orders_raw.order_id
contacts.contact.name <- orders_raw.customer.name
contacts.contact.city <- orders_raw.customer.address.city

The first value of the STRUCT goes into the first field of contact, name, and the second value into the second field, city.

7.3 Trino: ROW(...)

A Trino ROW value built with CAST(ROW(...) AS ROW(...)) gives the view column one field node for each field of the ROW type, and each field node gets the edge of its own value:

1
2
3
4
-- Trino
CREATE VIEW v AS
SELECT CAST(ROW(payload.address.city, payload.phone) AS ROW(city VARCHAR, phone VARCHAR)) AS contact
FROM events;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
table events: event_id, payload, payload.address, payload.address.city, payload.phone, payload.items, payload.items[*], payload.items[*].name, payload.items[*].qty
view v: contact, contact.city, contact.phone
events.event_id <- 's3://my-bucket/events/'.event_id
events.payload <- 's3://my-bucket/events/'.payload
events.payload.address <- 's3://my-bucket/events/'.payload.address
events.payload.address.city <- 's3://my-bucket/events/'.payload.address.city
events.payload.phone <- 's3://my-bucket/events/'.payload.phone
events.payload.items <- 's3://my-bucket/events/'.payload.items
events.payload.items[*] <- 's3://my-bucket/events/'.payload.items[*]
events.payload.items[*].name <- 's3://my-bucket/events/'.payload.items[*].name
events.payload.items[*].qty <- 's3://my-bucket/events/'.payload.items[*].qty
v.contact.city <- events.payload.address.city
v.contact.phone <- events.payload.phone

1
2
3
4
table events: payload, payload.address, payload.address.city, payload.phone
view v: contact, contact.city, contact.phone
v.contact.city <- events.payload.address.city
v.contact.phone <- events.payload.phone

8. Write one field: UPDATE and MERGE

An UPDATE can change one field of a STRUCT column:

1
2
3
4
5
-- BigQuery
UPDATE orders_raw o
SET o.customer.address.city = s.city
FROM staging s
WHERE o.order_id = s.order_id;

1
2
3
table staging: city, order_id
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
orders_raw.customer.address.city <- staging.city

1
2
3
table orders_raw: customer, customer.address, customer.address.city, order_id
table staging: city, order_id
orders_raw.customer.address.city <- staging.city

MERGE works in the same way:

1
2
3
4
-- BigQuery
MERGE orders_raw t
USING staging s ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET customer.name = s.name;

1
2
3
table staging: name, order_id
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
orders_raw.customer.name <- staging.name

1
2
3
table orders_raw: customer, customer.name, order_id
table staging: name, order_id
orders_raw.customer.name <- staging.name

The statement changes only one field, so the edge goes into that field node. The other fields of customer keep their values, and they get no edge.

9. Through a CTE or a subquery

A CTE or a subquery can pass a STRUCT column on to the outer query:

1
2
3
4
-- BigQuery
CREATE VIEW v AS
WITH c AS (SELECT customer FROM orders_raw)
SELECT customer.address.city AS city FROM c;

1
2
3
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: city
v.city <- orders_raw.customer.address.city

1
2
3
table orders_raw: customer, customer.address, customer.address.city
view v: city
v.city <- orders_raw.customer.address.city

The edge goes to the field node customer.address.city, as for a direct read. The CTE passes on the column orders_raw.customer unchanged, so reading customer.address.city from the CTE reads that field of orders_raw.customer.

The DDL still counts here. When it shows that the column passed on by the CTE has no such field, for example because it is an INT64 column, GSP draws no edge, as for a direct read (section 4.2).

When the CTE passes on a field, the read of a field below it goes to that field too:

1
2
3
4
-- BigQuery
CREATE VIEW v AS
WITH c AS (SELECT customer.address AS addr FROM orders_raw)
SELECT addr.city AS city FROM c;

1
2
3
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: city
v.city <- orders_raw.customer.address.city

1
2
3
table orders_raw: customer, customer.address, customer.address.city
view v: city
v.city <- orders_raw.customer.address.city

Trino, Presto and Athena work in the same way:

1
2
3
4
-- Trino, Presto, Athena
CREATE VIEW v AS
WITH c AS (SELECT payload FROM events)
SELECT payload.address.city AS city FROM c;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
table events: event_id, payload, payload.address, payload.address.city, payload.phone, payload.items, payload.items[*], payload.items[*].name, payload.items[*].qty
view v: city
events.event_id <- 's3://my-bucket/events/'.event_id
events.payload <- 's3://my-bucket/events/'.payload
events.payload.address <- 's3://my-bucket/events/'.payload.address
events.payload.address.city <- 's3://my-bucket/events/'.payload.address.city
events.payload.phone <- 's3://my-bucket/events/'.payload.phone
events.payload.items <- 's3://my-bucket/events/'.payload.items
events.payload.items[*] <- 's3://my-bucket/events/'.payload.items[*]
events.payload.items[*].name <- 's3://my-bucket/events/'.payload.items[*].name
events.payload.items[*].qty <- 's3://my-bucket/events/'.payload.items[*].qty
v.city <- events.payload.address.city

1
2
3
table events: payload, payload.address, payload.address.city
view v: city
v.city <- events.payload.address.city

10. When GSP draws no edge

10.1 Two tables could own the column

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT customer.name AS name FROM orders, returns;

1
2
3
4
5
table orders: 
table returns: 
table pseudo_table_include_orphan_column: customer.name
view v: name
message: find orphan column(10500) near: customer(3,8)

Without any DDL, both orders and returns could have a column customer. A guess could be wrong, and a wrong edge looks the same as a right one. So GSP draws no edge (rule 4). It reports customer as an orphan column: a column with no proven table. The node goes into the special table pseudo_table_include_orphan_column.

When a DDL shows which table has the column, GSP uses that table:

1
2
3
4
5
-- BigQuery
CREATE TABLE orders (order_id INT64, customer STRUCT<name STRING>);
CREATE TABLE returns (order_id INT64, reason STRING);
CREATE VIEW v AS
SELECT customer.name AS name FROM orders, returns;

1
2
3
4
table orders: order_id, customer, customer.name
table returns: order_id, reason
view v: name
v.name <- orders.customer.name

10.2 A qualified name is not a field path

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT sales.orders.order_id AS id FROM sales.orders;

1
2
3
table sales.orders: order_id
view v: id
v.id <- sales.orders.order_id

sales.orders is a table in the FROM clause, so sales.orders.order_id is the column order_id of that table. It is not a column sales with the fields orders and order_id. A name with dots is a field path only when its first part is a column.

11. Why this lineage is correct

It follows the model of the database. BigQuery itself treats a STRUCT column and its fields as two kinds of things. Its view INFORMATION_SCHEMA.COLUMNS lists the columns (customer). Its view INFORMATION_SCHEMA.COLUMN_FIELD_PATHS lists each column and each field in it as a path (customer, customer.name, customer.address, customer.address.city), and it lists the policy tags of each path: a field such as customer.phone can have its own access rules. The GSP tree has the same column nodes and the same field paths, so a rule on a field and the lineage of that field meet on the same node.

Exact edges answer impact questions. Suppose that you plan to change the field customer.phone, and you ask: which views use it? Compare the tree with two simpler models:

Model SELECT customer.address.city comes from SELECT customer comes from Problem
Every read comes from the whole column customer customer Too coarse. The view that reads only the city also seems to use the phone number.
Every read comes from the leaf fields customer.address.city customer.name, customer.phone, customer.address.city Too many edges. A STRUCT with 50 fields gives 50 edges for one read, and a read of customer.address is hard to show without its sibling fields.
The tree (GSP) customer.address.city customer

With the tree, the answer is exact: a view uses customer.phone when it reads customer.phone, or when it reads the whole customer, which contains the phone. A view that reads only customer.address.city does not use it. To find "contains", follow the tree: customer.phone is below customer.

The lineage does not depend on the metadata that you have. A read of a column or a field gives the same edge with and without the DDL (rule 3). Without the DDL, GSP finds the column and the fields from the query, in the way that the database reads the names. With the DDL, GSP also lists the fields that the query does not read.

No guesses. When the SQL does not prove which table owns a column, GSP reports an orphan column and draws no edge (rule 4). When the DDL says that a field does not exist, GSP does not create it (rule 5). A missing edge is a problem too, but a wrong edge is a problem that nobody can see in the output, so GSP draws an edge only when the SQL proves it.

12. One model for many databases

The tree needs only three things: a column, the names of its fields, and the elements of an array. Every STRUCT type in section 1.3 has them. They only use other syntax to read them:

Database Read in SQL Node in the tree
BigQuery customer.address.city customer.address.city
BigQuery i.item_name, with UNNEST(items) AS i items[*].item_name
Trino, Presto, Athena payload.address.city payload.address.city
Trino, Presto, Athena payload.items[1].name payload.items[*].name

So the same tree, with the same node names, can describe a STRUCT column of each of these databases. Today GSP builds it for BigQuery, Athena, Presto and Trino, and for the STRUCT columns of Databricks, Hive and Spark SQL.

In Databricks, Hive and Spark SQL, each read of a field comes from its field node, as in BigQuery. Databricks and Hive print the same lines:

1
2
3
4
5
6
7
-- Databricks, Hive
CREATE TABLE orders_raw (
  order_id BIGINT,
  customer STRUCT<name: STRING, address: STRUCT<city: STRING>>
);
CREATE VIEW v AS
SELECT customer.name AS name, customer.address.city AS city FROM orders_raw;

1
2
3
4
table orders_raw: order_id, customer, customer.name, customer.address, customer.address.city
view v: name, city
v.name <- orders_raw.customer.name
v.city <- orders_raw.customer.address.city

Without the DDL, the reads give the same edges:

1
2
3
-- Databricks, Hive
CREATE VIEW v AS
SELECT customer.name AS name, customer.address.city AS city FROM orders_raw;

1
2
3
4
table orders_raw: customer, customer.name, customer.address, customer.address.city
view v: name, city
v.name <- orders_raw.customer.name
v.city <- orders_raw.customer.address.city

Spark SQL prints the same lines:

1
2
3
4
5
6
7
-- Spark SQL
CREATE TABLE orders_raw (
  order_id BIGINT,
  customer STRUCT<name: STRING, address: STRUCT<city: STRING>>
);
CREATE VIEW v AS
SELECT customer.name AS name, customer.address.city AS city FROM orders_raw;

1
2
3
4
table orders_raw: order_id, customer, customer.name, customer.address, customer.address.city
view v: name, city
v.name <- orders_raw.customer.name
v.city <- orders_raw.customer.address.city

13. Other databases today

For Snowflake, PostgreSQL, Oracle and Redshift (section 1.3), GSP does not build the STRUCT tree yet. Here is what GSP gives today for the same kind of query.

Snowflake and PostgreSQL: every read of a field comes from the whole column. This is true, but not exact:

1
2
3
4
-- Snowflake
CREATE TABLE orders_raw (order_id NUMBER, customer VARIANT);
CREATE VIEW v AS
SELECT customer:name::STRING AS name, customer:address.city::STRING AS city FROM orders_raw;

1
2
3
4
table orders_raw: order_id, customer
view v: name, city
v.name <- orders_raw.customer
v.city <- orders_raw.customer

1
2
3
4
5
6
-- PostgreSQL
CREATE TYPE address_t AS (city text);
CREATE TYPE customer_t AS (name text, address address_t);
CREATE TABLE orders_raw (order_id int, customer customer_t);
CREATE VIEW v AS
SELECT (customer).name AS name, ((customer).address).city AS city FROM orders_raw;

1
2
3
4
table orders_raw: order_id, customer
view v: name, city
v.name <- orders_raw.customer
v.city <- orders_raw.customer

Oracle and Redshift: every read of a field comes from the whole column, one field deep or two:

1
2
3
4
5
6
-- Oracle
CREATE TYPE address_t AS OBJECT (city VARCHAR2(30));
CREATE TYPE customer_t AS OBJECT (name VARCHAR2(30), address address_t);
CREATE TABLE orders_raw (order_id NUMBER, customer customer_t);
CREATE VIEW v AS
SELECT o.customer.name AS name, o.customer.address.city AS city FROM orders_raw o;

1
2
3
4
table orders_raw: order_id, customer
view v: name, city
v.name <- orders_raw.customer
v.city <- orders_raw.customer

1
2
3
4
-- Redshift
CREATE TABLE orders_raw (order_id INT, customer SUPER);
CREATE VIEW v AS
SELECT o.customer.name AS name, o.customer.address.city AS city FROM orders_raw AS o;

1
2
3
4
table orders_raw: order_id, customer
view v: name, city
v.name <- orders_raw.customer
v.city <- orders_raw.customer

Without the DDL, Redshift gives the same edges:

1
2
3
-- Redshift
CREATE VIEW v AS
SELECT o.customer.name AS name, o.customer.address.city AS city FROM orders_raw AS o;

1
2
3
4
table orders_raw: customer
view v: name, city
v.name <- orders_raw.customer
v.city <- orders_raw.customer

14. Current limits

These shapes do not get the exact lineage of this page yet. Each one shows the real output of the build in the stamp at the top of this page.

A BigQuery UNNEST without an alias, without DDL, gives no edge. Without the element type, nothing proves whether item_name is a column of the table or a field of the element, so GSP reports an orphan column (rule 4). With the DDL, the read goes to the field:

1
2
3
-- BigQuery
CREATE VIEW v AS
SELECT item_name FROM orders_raw, UNNEST(items);

1
2
3
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: item_name
v.item_name <- orders_raw.items[*].item_name

1
2
3
table orders_raw: items, items[*]
table pseudo_table_include_orphan_column: item_name
view v: item_name

A BigQuery field read through SELECT *, without DDL, goes to the whole column customer, which GSP prints as CUSTOMER, not to the field. With the DDL, it goes to the field node:

1
2
3
4
-- BigQuery
CREATE VIEW v AS
WITH c AS (SELECT * FROM orders_raw)
SELECT customer.address.city AS city FROM c;

1
2
3
table orders_raw: order_id, customer, customer.name, customer.phone, customer.address, customer.address.city, items, items[*], items[*].item_name, items[*].qty
view v: city
v.city <- orders_raw.customer.address.city

1
2
3
table orders_raw: CUSTOMER, *
view v: city
v.city <- orders_raw.CUSTOMER

15. API used on this page

All classes are in the package gudusoft.gsqlparser.dlineage.dataflow.model.xml, unless another package is given.

Class Method Description
Option (gudusoft.gsqlparser.dlineage.dataflow.model) setVendor(EDbVendor) The database of the SQL.
Option setSimpleOutput(boolean) true: each edge goes straight from a source to a target, without intermediate result sets.
Option setOutput(boolean) false: keep the result in memory and do not write XML. generateDataFlow() then returns null.
DataFlowAnalyzer (gudusoft.gsqlparser.dlineage) DataFlowAnalyzer(File[] sqlFiles, Option option), generateDataFlow(), getDataFlow(), dispose() Runs the analysis on the SQL files, in order, and returns the result, a dataflow. DataFlowAnalyzer(String sqlContent, Option option) does the same for SQL text.
dataflow getTables(), getViews() The tables and views, as table objects.
dataflow getRelationships() The edges, as relationship objects.
dataflow getErrors() The messages of the analysis, as error objects.
table getName(), getColumns() The name, and the nodes: the columns, each followed by its field nodes.
column getName() The name of a node: customer, customer.address.city, items[*].item_name.
relationship getType() fdd when the value of the target comes from the sources.
relationship getTarget(), getSources() The target, a targetColumn, and the sources, sourceColumn objects.
targetColumn, sourceColumn getParent_name(), getColumn() The table or view of the node, and the name of the node.
error getErrorMessage() The text of a message.

Refreshing this page

This page is written by hand, but every output on it is measured. To bring it forward to a newer GSP release:

1. Read the version from TBaseType.versionid and TBaseType.releaseDate in gsp_java_core/src/main/java/gudusoft/gsqlparser/TBaseType.java.

2. Run every example again. Compile the program of section 2.4 against the new jar. For each example, run it as section 2.4 shows: with the table file of section 1.2 for the tab With the DDL, and alone for the tab Without DDL or for an example with one output block. Paste the new output. Do not edit an output block by hand. An example marked -- Trino, Presto, Athena must print the same lines for each of the three databases.

A script does all of this. It compiles the program as the page prints it, runs every example, and compares each output block line by line. With --fix, it writes the new output into the page and lists each change for you to review:

1
2
python3 site-docs/tools/check_struct_lineage_page.py \
    site-docs/docs/reference/struct-column-lineage.md gsp_java_core/target/gsqlparser-4.2.14.jar

3. Check the limits. When a limit in section 14 or a database in section 13 gives new lineage, move its example to the section where it now belongs, and update the text.

4. Update the stamp at the top: the version, its release date, today's date, and the number of examples. Change it only after you have run the examples on the new build.

5. Verify before you publish:

1
2
3
python3 ../gsp_dotnet/site-docs/tools/check_api_symbols.py \
    --docs site-docs/docs --source gsp_java_core/src/main/java/gudusoft/gsqlparser --lang java
mkdocs build -f site-docs/mkdocs.yml

See also