Skip to main content

SQL Reference

The Patterns SQL api consists of two special objects Tables and Parameters. These objects work similar to and take the same arguments as their python equivalents. To use them wrap them in templating braces {{ Table(...) }} in your SQL. For an overview and examples, see the introduction to SQL in Patterns.

Table​

select * from {{ Table() }};

Arguments​

Table(
  name: str,
  mode: str = "r",
  description: str = None,
  schema: str = None,
  required: bool = True
)

A Table is a thin abstraction over a database table that provides a stable reference across versions of the table.

Arguments:

  • name - The Patterns name for the table. The actual database table on disk will include this name and a hash.
  • mode - Whether to use the table in "read" mode ("r") or "write" mode ("w")
  • description - An optional short description of this table
  • schema - An optional explicit Schema for this table. If not provided the schema will be inferred.
  • required - Whether this table is a required table for the operation of the node, or is optional.

Parameter​

select {{ Parameter() }} as param;

Arguments​

Parameter(
  description: str = None,
  type: Type[T] = str,
  default: Any = "MISSING"
)

Parameters let a SQL script take values from the end user and/or UI easily.

Allowed parameter types:

  • str
  • int
  • float
  • bool
  • datetime
  • date
  • list
  • Connection

Arguments:

  • description - Description / help text
  • type - should be the actual python type, e.g. type=str
  • default - default value. If not set explicitly, the parameter is assumed optional. May be set to None