Logical Relations¶

message ReadRel {
  RelCommon common = 1;
  NamedStruct base_schema = 2;
  Expression filter = 3;
  Expression best_effort_filter = 11;
  Expression.MaskExpression projection = 4;
  substrait.extensions.AdvancedExtension advanced_extension = 10;

  // Definition of which type of scan operation is to be performed
  oneof read_type {
    VirtualTable virtual_table = 5;
    LocalFiles local_files = 6;
    NamedTable named_table = 7;
    ExtensionTable extension_table = 8;
    IcebergTable iceberg_table = 9;
  }

  // A base table. The list of string is used to represent namespacing (e.g., mydb.mytable).
  // This assumes shared catalog between systems exchanging a message.
  message NamedTable {
    repeated string names = 1;
    substrait.extensions.AdvancedExtension advanced_extension = 10;
  }

  // Read an Iceberg Table
  message IcebergTable {
    oneof table_type {
      MetadataFileRead direct = 1;
      // future: add catalog table types (e.g. rest api, latest metadata in path, etc)
    }

    // Read an Iceberg table using a metadata file. Implicit assumption: required credentials are already known by plan consumer.
    message MetadataFileRead {
      // the specific uri of a metadata file (e.g. s3://mybucket/mytable/<ver>-<uuid>.metadata.json)
      string metadata_uri = 1;

      // snapshot options. if none set, uses the current snapshot listed in the metadata file
      oneof snapshot {
        // the snapshot id to read.
        string snapshot_id = 2;

        // the timestamp that should be used to select the snapshot (Time passed in microseconds since 1970-01-01 00:00:00.000000 in UTC)
        int64 snapshot_timestamp = 3;
      }
    }
  }

  // A table composed of expressions.
  message VirtualTable {
    repeated Expression.Literal.Struct values = 1 [deprecated = true];
    repeated Expression.Nested.Struct expressions = 2;
  }

  // A stub type that can be used to extend/introduce new table types outside
  // the specification.
  message ExtensionTable {
    google.protobuf.Any detail = 1;
  }

  // Represents a list of files in input of a scan operation
  message LocalFiles {
    repeated FileOrFiles items = 1;
    substrait.extensions.AdvancedExtension advanced_extension = 10;

    // Many files consist of indivisible chunks (e.g. parquet row groups
    // or CSV rows).  If a slice partially selects an indivisible chunk
    // then the consumer should employ some rule to decide which slice to
    // include the chunk in (e.g. include it in the slice that contains
    // the midpoint of the chunk)
    message FileOrFiles {
      oneof path_type {
        // A URI that can refer to either a single folder or a single file
        string uri_path = 1;
        // A URI where the path portion is a glob expression that can
        // identify zero or more paths.
        // Consumers should support the POSIX syntax.  The recursive
        // globstar (**) may not be supported.
        string uri_path_glob = 2;
        // A URI that refers to a single file
        string uri_file = 3;
        // A URI that refers to a single folder
        string uri_folder = 4;
      }

      // Original file format enum, superseded by the file_format oneof.
      reserved 5;
      reserved "format";

      // The index of the partition this item belongs to
      uint64 partition_index = 6;

      // The start position in byte to read from this item
      uint64 start = 7;

      // The length in byte to read from this item
      uint64 length = 8;

      message ParquetReadOptions {}
      message ArrowReadOptions {}
      message OrcReadOptions {}
      message DwrfReadOptions {}
      message DelimiterSeparatedTextReadOptions {
        // Delimiter separated files may be compressed.  The reader should
        // autodetect this and decompress as needed.

        // The character(s) used to separate fields.  Common values are comma,
        // tab, and pipe.  Multiple characters are allowed.
        string field_delimiter = 1;
        // The maximum number of bytes to read from a single line.  If a line
        // exceeds this limit the resulting behavior is undefined.
        uint64 max_line_size = 2;
        // The character(s) used to quote strings.  Common values are single
        // and double quotation marks.
        string quote = 3;
        // The number of lines to skip at the beginning of the file.
        uint64 header_lines_to_skip = 4;
        // The character used to escape characters in strings.  Backslash is
        // a common value.  Note that a double quote mark can also be used as an
        // escape character but the external quotes should be removed first.
        string escape = 5;
        // If this value is encountered (including empty string), the resulting
        // value is null instead.  Leave unset to disable.  If this value is
        // provided, the effective schema of this file is comprised entirely of
        // nullable strings.  If not provided, the effective schema is instead
        // made up of non-nullable strings.
        optional string value_treated_as_null = 6;
      }

      // The format of the files along with options for reading those files.
      oneof file_format {
        ParquetReadOptions parquet = 9;
        ArrowReadOptions arrow = 10;
        OrcReadOptions orc = 11;
        google.protobuf.Any extension = 12;
        DwrfReadOptions dwrf = 13;
        DelimiterSeparatedTextReadOptions text = 14;
      }
    }
  }

}

message FilterRel {
  RelCommon common = 1;
  Rel input = 2;
  Expression condition = 3;
  substrait.extensions.AdvancedExtension advanced_extension = 10;

}

message SortRel {
  RelCommon common = 1;
  Rel input = 2;
  repeated SortField sorts = 3;
  substrait.extensions.AdvancedExtension advanced_extension = 10;

}

message ProjectRel {
  RelCommon common = 1;
  Rel input = 2;
  repeated Expression expressions = 3;
  substrait.extensions.AdvancedExtension advanced_extension = 10;

}

message CrossRel {
  RelCommon common = 1;
  Rel left = 2;
  Rel right = 3;

  substrait.extensions.AdvancedExtension advanced_extension = 10;

}

message JoinRel {
  RelCommon common = 1;
  Rel left = 2;
  Rel right = 3;
  Expression expression = 4;
  Expression post_join_filter = 5;

  JoinType type = 6;

  enum JoinType {
    JOIN_TYPE_UNSPECIFIED = 0;
    JOIN_TYPE_INNER = 1;
    JOIN_TYPE_OUTER = 2;
    JOIN_TYPE_LEFT = 3;
    JOIN_TYPE_RIGHT = 4;
    JOIN_TYPE_LEFT_SEMI = 5;
    JOIN_TYPE_LEFT_ANTI = 6;
    JOIN_TYPE_LEFT_SINGLE = 7;
    JOIN_TYPE_RIGHT_SEMI = 8;
    JOIN_TYPE_RIGHT_ANTI = 9;
    JOIN_TYPE_RIGHT_SINGLE = 10;
    JOIN_TYPE_LEFT_MARK = 11;
    JOIN_TYPE_RIGHT_MARK = 12;
  }

  substrait.extensions.AdvancedExtension advanced_extension = 10;

}

message SetRel {
  RelCommon common = 1;
  // The first input is the primary input, the remaining are secondary
  // inputs.  There must be at least two inputs.
  repeated Rel inputs = 2;
  SetOp op = 3;
  substrait.extensions.AdvancedExtension advanced_extension = 10;

  enum SetOp {
    SET_OP_UNSPECIFIED = 0;
    SET_OP_MINUS_PRIMARY = 1;
    SET_OP_MINUS_PRIMARY_ALL = 7;
    SET_OP_MINUS_MULTISET = 2;
    SET_OP_INTERSECTION_PRIMARY = 3;
    SET_OP_INTERSECTION_MULTISET = 4;
    SET_OP_INTERSECTION_MULTISET_ALL = 8;
    SET_OP_UNION_DISTINCT = 5;
    SET_OP_UNION_ALL = 6;
  }

}

message FetchRel {
  RelCommon common = 1;
  Rel input = 2;
  // Note: A oneof field is inherently optional, whereas individual fields
  // within a oneof cannot be marked as optional. The unset state of offset
  // should therefore be checked at the oneof level. Unset is treated as 0.
  oneof offset_mode {
    // the offset expressed in number of records
    // Deprecated: use `offset_expr` instead
    int64 offset = 3 [deprecated = true];
    // Expression evaluated into a non-negative integer specifying the number
    // of records to skip. An expression evaluating to null is treated as 0.
    // Evaluating to a negative integer should result in an error.
    // Recommended type for offset is int64.
    Expression offset_expr = 5;
  }
  // Note: A oneof field is inherently optional, whereas individual fields
  // within a oneof cannot be marked as optional. The unset state of count
  // should therefore be checked at the oneof level. Unset is treated as ALL.
  oneof count_mode {
    // the amount of records to return
    // use -1 to signal that ALL records should be returned
    // Deprecated: use `count_expr` instead
    int64 count = 4 [deprecated = true];
    // Expression evaluated into a non-negative integer specifying the number
    // of records to return. An expression evaluating to null signals that ALL
    // records should be returned.
    // Evaluating to a negative integer should result in an error.
    // Recommended type for count is int64.
    Expression count_expr = 6;
  }
  substrait.extensions.AdvancedExtension advanced_extension = 10;

}

message AggregateRel {
  RelCommon common = 1;

  // Input of the aggregation
  Rel input = 2;

  // A list of zero or more grouping sets that the aggregation measures should
  // be calculated for. There must be at least one grouping set if there are no
  // measures (but it can be the empty grouping set).
  repeated Grouping groupings = 3;

  // A list of one or more aggregate expressions along with an optional filter.
  // Required if there are no groupings.
  repeated Measure measures = 4;

  // A list of zero or more grouping expressions that grouping sets (i.e.,
  // `Grouping` messages in the `groupings` field) can reference. Each
  // expression in this list must be referred to by at least one
  // `Grouping.expression_references`.
  repeated Expression grouping_expressions = 5;

  substrait.extensions.AdvancedExtension advanced_extension = 10;

  message Grouping {
    // Deprecated in favor of `expression_references` below.
    repeated Expression grouping_expressions = 1 [deprecated = true];

    // A list of zero or more references to grouping expressions, i.e., indices
    // into the `grouping_expression` list.
    repeated uint32 expression_references = 2;
  }

  message Measure {
    AggregateFunction measure = 1;

    // An optional boolean expression that acts to filter which records are
    // included in the measure. True means include this record for calculation
    // within the measure.
    // Helps to support SUM(<c>) FILTER(WHERE...) syntax without masking opportunities for optimization
    Expression filter = 2;
  }

}

message ReferenceRel {
  int32 subtree_ordinal = 1;

}

message WriteRel {
  // Definition of which TABLE we are operating on
  oneof write_type {
    NamedObjectWrite named_table = 1;
    ExtensionObject extension_table = 2;
  }

  // The schema of the table (must align with Rel input (e.g., number of leaf fields must match))
  NamedStruct table_schema = 3;

  // The type of operation to perform
  WriteOp op = 4;

  // The relation that determines the records to add/remove/modify
  // the schema must match with table_schema. Default values must be explicitly stated
  // in a ProjectRel at the top of the input. The match must also
  // occur in case of DELETE to ensure multi-engine plans are unequivocal.
  Rel input = 5;

  CreateMode create_mode = 8; // Used with CTAS to determine what to do if the table already exists

  // Output mode determines what is the output of executing this rel
  OutputMode output = 6;
  RelCommon common = 7;

  enum WriteOp {
    WRITE_OP_UNSPECIFIED = 0;
    // The insert of new records in a table
    WRITE_OP_INSERT = 1;
    // The removal of records from a table
    WRITE_OP_DELETE = 2;
    // The modification of existing records within a table
    WRITE_OP_UPDATE = 3;
    // The Creation of a new table, and the insert of new records in the table
    WRITE_OP_CTAS = 4;
  }

  enum CreateMode {
    CREATE_MODE_UNSPECIFIED = 0;
    CREATE_MODE_APPEND_IF_EXISTS = 1; // Append the data to the table if it already exists
    CREATE_MODE_REPLACE_IF_EXISTS = 2; // Replace the table if it already exists ("OR REPLACE")
    CREATE_MODE_IGNORE_IF_EXISTS = 3; // Ignore the request if the table already exists ("IF NOT EXISTS")
    CREATE_MODE_ERROR_IF_EXISTS = 4; // Throw an error if the table already exists (default behavior)
  }

  enum OutputMode {
    OUTPUT_MODE_UNSPECIFIED = 0;
    // return no records at all
    OUTPUT_MODE_NO_OUTPUT = 1;
    // this mode makes the operator return all the record INSERTED/DELETED/UPDATED by the operator.
    // The operator returns the AFTER-image of any change. This can be further manipulated by operators upstreams
    // (e.g., retunring the typical "count of modified records").
    // For scenarios in which the BEFORE image is required, the user must implement a spool (via references to
    // subplans in the body of the Rel input) and return those with anounter PlanRel.relations.
    OUTPUT_MODE_MODIFIED_RECORDS = 2;
  }

  substrait.extensions.AdvancedExtension advanced_extension = 9;

}

message UpdateRel {
  oneof update_type {
    NamedTable named_table = 1;
  }

  NamedStruct table_schema = 2; // The full schema of the named_table
  Expression condition = 3; // condition to be met for the update to be applied on a record

  // The list of transformations to apply to the columns of the named_table
  repeated TransformExpression transformations = 4;

  message TransformExpression {
    Expression transformation = 1; // the transformation to apply
    int32 column_target = 2; // index of the column to apply the transformation to
  }

  substrait.extensions.AdvancedExtension advanced_extension = 5;

}

message DdlRel {
  // Definition of which type of object we are operating on
  oneof write_type {
    NamedObjectWrite named_object = 1;
    ExtensionObject extension_object = 2;
  }

  // The columns that will be modified (representing after-image of a schema change)
  NamedStruct table_schema = 3;
  // The default values for the columns (representing after-image of a schema change)
  // E.g., in case of an ALTER TABLE that changes some of the column default values, we expect
  // the table_defaults Struct to report a full list of default values reflecting the result of applying
  // the ALTER TABLE operator successfully
  Expression.Literal.Struct table_defaults = 4;

  // Which type of object we operate on
  DdlObject object = 5;

  // The type of operation to perform
  DdlOp op = 6;

  // The body of the CREATE VIEW
  Rel view_definition = 7;
  RelCommon common = 8;

  enum DdlObject {
    DDL_OBJECT_UNSPECIFIED = 0;
    // A Table object in the system
    DDL_OBJECT_TABLE = 1;
    // A View object in the system
    DDL_OBJECT_VIEW = 2;
  }

  enum DdlOp {
    DDL_OP_UNSPECIFIED = 0;
    // A create operation (for any object)
    DDL_OP_CREATE = 1;
    // A create operation if the object does not exist, or replaces it (equivalent to a DROP + CREATE) if the object already exists
    DDL_OP_CREATE_OR_REPLACE = 2;
    // An operation that modifies the schema (e.g., column names, types, default values) for the target object
    DDL_OP_ALTER = 3;
    // An operation that removes an object from the system
    DDL_OP_DROP = 4;
    // An operation that removes an object from the system (without throwing an exception if the object did not exist)
    DDL_OP_DROP_IF_EXIST = 5;
  }

  substrait.extensions.AdvancedExtension advanced_extension = 9;

  //TODO add PK/constraints/indexes/etc..?

}