Skip to main content
Last updated on

CREATE TABLE ... FLOW (pipelines)

Beta

This feature is in Beta.

Use the CREATE TABLE ... FLOW statement to create a managed table in a pipeline, written by one or more flows.

Syntax​

CREATE TABLE
  table_name
  [ table_specification ]
  [ table_clauses ]
  [ flow_clause ]

table_specification
  ( { column_identifier column_type [column_properties] } [, ...] )

table_clauses
  { PARTITIONED BY (col [, ...]) |
    CLUSTER BY clause |
    LOCATION path |
    COMMENT table_comment |
    TBLPROPERTIES clause |
    WITH { ROW FILTER clause } } [ ... ]

flow_clause
  FLOW INSERT [ONCE] BY NAME query

To fan multiple sources into one managed table, declare several flows that target it with CREATE FLOW (pipelines):

CREATE FLOW flow_name AS INSERT INTO table_name BY NAME query

Parameters​

  • table_name

    The name of the managed table to create. If the name is not qualified, the table is created in the pipeline's target schema. The name must not already belong to a streaming table.

  • table_specification

    Optionally defines the columns, their types, properties, and descriptions. If omitted, the schema is inferred from the flow query.

  • PARTITIONED BY (col [, ...])

    Optionally partitions the table by a subset of columns.

  • CLUSTER BY clause

    Optionally enables liquid clustering on the table. You cannot combine PARTITIONED BY and CLUSTER BY.

  • LOCATION path

    An optional storage location for the table data.

  • COMMENT table_comment

    A STRING literal describing the table.

  • TBLPROPERTIES clause

    Optionally sets one or more user-defined table properties.

  • WITH ROW FILTER clause

    Adds a row filter function to the table. Future queries for that table receive a subset of the rows for which the function evaluates to TRUE.

  • FLOW INSERT [ONCE] BY NAME query

    Defines an append flow that inserts the result of query into the table, matching result columns to table columns by name. query can reference batch or streaming sources. ONCE runs the flow a single time (for example, for a backfill) rather than on every update. Each named flow processes its input exactly once per pipeline update, identical to FLOW INSERT BY NAME on a streaming table.

Limitations​

  • Managed tables do not support CDC change flows. AUTO CDC INTO (SQL) or apply_changes / apply_changes_from_snapshot (Python) against a managed table fails with MANAGED_TABLE_DOES_NOT_SUPPORT_CDC. Use a CREATE STREAMING TABLE (pipelines) for CDC targets.
  • Managed tables do not support FLOW ... REPLACE WHERE. Only FLOW INSERT BY NAME is supported.
  • Managed tables are supported only in pipelines with Unity Catalog. The Hive metastore is not supported.
  • You cannot reuse the name of an existing streaming table for a managed table. Drop the streaming table first, or the statement fails with CANNOT_SWITCH_STREAMING_TABLE_TO_MANAGED_TABLE.

Examples​

SQL
-- Create a managed table populated by an append flow
CREATE TABLE output
FLOW INSERT BY NAME SELECT id FROM LIVE.source;

-- Create a partitioned managed table from a streaming source
CREATE TABLE events
PARTITIONED BY (bucket)
FLOW INSERT BY NAME
  SELECT id, bucket FROM STREAM read_files('abfss://my_path', format => 'json');

Additional resources​

On this page