Substrait

https://github.com/substrait-io/substrait
Admin
Substrait is a project that produces an independent description of data compute operations,...
Tokens:43,252
Snippets:289
Trust Score:8.3
License:Apache-2.0
Update:10 months ago
Show doc for...
Tokens:
Raw
### Include Example File in Markdown

Source: https://github.com/substrait-io/substrait/blob/main/site/examples/README.md

Demonstrates how to include example files directly into markdown documentation using the pymdownx.snippets syntax. This allows for dynamic inclusion and syntax highlighting of external code snippets.

```markdown
```yaml
--8<-- "examples/extensions/distance_functions.yaml"
```
```

--------------------------------

### Setup and Activate Virtual Environment (Shell)

Source: https://github.com/substrait-io/substrait/blob/main/site/README.md

Sets up a Python virtual environment named 'venv' using virtualenv and activates it. This is a one-time setup for isolating project dependencies. It requires Python 3.7/3.8 and virtualenv to be installed.

```shell
# cd to the site/ directory
cd site/
# setup the virtual environment (only needed once)
virtualenv -p $(which python3) venv
# activate the virtual environment
. venv/bin/activate
# Install or update the dependencies as usual
pip install -r ./requirements.txt
```

--------------------------------

### Install MkDocs and Dependencies (Shell)

Source: https://github.com/substrait-io/substrait/blob/main/site/README.md

Installs MkDocs and project dependencies using pip. It assumes a virtual environment is already set up and activated. This script is intended to be run from the 'site/' directory.

```shell
# Activate the virtual environment (if installed)
cd site/
. venv/bin/activate
# Install or update the dependencies
pip install -r ./requirements.txt
```

--------------------------------

### Substrait Test File Example

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

A minimal example of a Substrait scalar test file. It demonstrates the structure including the test file version, an included extension file for arithmetic functions, and a simple test case for the 'add' function with integer arguments and expected result.

```substrait
### SUBSTRAIT_SCALAR_TEST:V1
### SUBSTRAIT_INCLUDE: /extensions/functions_arithmetic.yaml

# Common Maths
add(126::i8, 1::i8) = 127::i8

```

--------------------------------

### SQL Query Example

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

An example SQL query to calculate total sales for products not in the 'Computers' category. It joins 'orders' and 'products' tables, filters based on the 'categories' column, and groups results by product name and ID.

```sql
SELECT
  product_name,
  product_id,
  sum(quantity * price) as sales
FROM
  orders
INNER JOIN
  products
ON
  orders.product_id = products.product_id
WHERE
  -- categories does not contain "Computers"
  INDEX_IN("Computers", categories) IS NULL
GROUP BY
  product_name,
  product_id
```

--------------------------------

### Example Function Signature

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/extensions/index.md

Illustrates a concrete example of a function signature based on the defined format. This signature represents a function named 'add' that takes two arguments, both of a short type represented by 'i' (integer), and returns a result. This specific format ensures unambiguous referencing of function implementations.

```text
add:i_i
```

--------------------------------

### Substrait Aggregate Test Case (Mixed Arguments Example)

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

An example demonstrating an aggregate test case with mixed argument types, specifically showing a single column for `LIST_AGG` and a separator string.

```code
((20), (-3), (1), (10)) LIST_AGG(col0::fp32, ','::string) = 1::fp64
```

--------------------------------

### Substrait Plan Structure Example (JSON)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Illustrates the nested structure of a Substrait plan in JSON, showing how relations like aggregate, join, filter, and read are interconnected through their 'input' fields.

```json
{
    "aggregate": {
        "input": {
            "join": {
                "left": {
                    "filter": {
                        "input": {
                            "read": {
                                "...": "..."
                            }
                        },
                        "...": "..."
                    }
                },
                "right": {
                    "read": {
                        "...": "..."
                    }
                },
                "...": "..."
            }
        },
        "...": "..."
    }
}
```

--------------------------------

### Substrait Logical Plan Representation

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

A logical plan representation in a tree-like structure for the example SQL query. It outlines the operations: aggregation, inner join, filtering, and reading tables.

```text
|-+ Aggregate({sales = sum(quantity_price)}, group_by=(product_name, product_id))
  |-+ InnerJoin(on=orders.product_id = products.product_id)
    |- ReadTable(orders)
    |-+ Filter(INDEX_IN("Computers", categories) IS NULL)
      |- ReadTable(products)
```

--------------------------------

### Substrait Table Schema Representation (Products Table)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

An example JSON of a `NamedStruct` for the 'products' table, showcasing nested types like lists and structs. This demonstrates depth-first naming conventions for complex schemas.

```json
{
  "names": [
    "product_id",
    "categories",
    "details",
    "manufacturer",
    "year_created",
    "product_name"
  ],
  "struct": {
    "types": [
      {
        "i64": {
          "nullability": "NULLABILITY_REQUIRED"
        }
      },
      {
        "list": {
          "type": {
            "string": {
              "nullability": "NULLABILITY_REQUIRED"
            }
          },
          "nullability": "NULLABILITY_REQUIRED"
        }
      },
      {
        "struct": {
          "types": [
            {
              "string": {
                "nullability": "NULLABILITY_NULLABLE"
              },
              "i32": {
                "nullability": "NULLABILITY_NULLABLE"
              }
            }
          ],
          "nullability": "NULLABILITY_NULLABLE"
        }
      },
      {
        "string": {
          "nullability": "NULLABILITY_NULLABLE"
        }
      }
    ],
    "nullability": "NULLABILITY_REQUIRED"
  }
}
```

--------------------------------

### Substrait Decimal Type Representation

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Illustrates the JSON format for a decimal data type in Substrait, including precision and scale parameters. This example shows how to define specific characteristics for numeric types.

```json
{
  "decimal": {
    "precision": 10,
    "scale": 2,
    "nullability": "NULLABILITY_NULLABLE"
  }
}
```

--------------------------------

### Substrait Table Schema Representation (Orders Table)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Provides a JSON example of a `NamedStruct` message representing the schema for the 'orders' table. It includes column names and their corresponding data types (i64, i32, date, decimal).

```json
{
  "names": [
    "product_id",
    "quantity",
    "order_date",
    "price"
  ],
  "struct": {
    "types": [
      {
        "i64": {
          "nullability": "NULLABILITY_REQUIRED"
        }
      },
      {
        "i32": {
          "nullability": "NULLABILITY_REQUIRED"
        }
      },
      {
        "date": {
          "nullability": "NULLABILITY_REQUIRED"
        }
      },
      {
        "decimal": {
          "precision": 10,
          "scale": 2,
          "nullability": "NULLABILITY_NULLABLE"
        }
      }
    ],
    "nullability": "NULLABILITY_REQUIRED"
  }
}
```

--------------------------------

### Substrait List and Map Type Syntax Examples

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/types/type_parsing.md

Illustrates the syntax for declaring list and map types in Substrait. Lists can contain a single type parameter, while maps require two type parameters (key and value). These types support recursive type references and variations, enabling the representation of nested and complex data structures. Examples are provided for both YAML and text formats.

```yaml
list?<type>>
map<type0, type1>
```

```text
list?<list<string>>
list<struct<string, i32>>
map<i32?, list<map<i32, string?>>>
```

--------------------------------

### Get Main Substrait FileSystem (Alias) in Go

Source: https://context7.com/substrait-io/substrait/llms.txt

Provides backward compatibility and convenient access to extension definitions by acting as an alias for `GetSubstraitExtensionsFS`. This Go function demonstrates using both `GetSubstraitFS` and `GetSubstraitExtensionsFS` to access extension type definitions and verifies they provide identical content.

```go
package main

import (
    "fmt"
    "io/fs"
    "github.com/substrait-io/substrait"
)

func main() {
    // Both methods return the same filesystem
    fs1 := substrait.GetSubstraitFS()
    fs2 := substrait.GetSubstraitExtensionsFS()

    // Access extension type definitions
    typeData, err := fs.ReadFile(fs1, "extensions/extension_types.yaml")
    if err != nil {
        panic(err)
    }

    fmt.Printf("Extension types: %s\n", string(typeData))
    // Output: Contains type definitions like point{latitude:i32, longitude:i32}

    // Verify both methods work identically
    files1, _ := fs.ReadDir(fs1, "extensions")
    files2, _ := fs.ReadDir(fs2, "extensions")
    fmt.Printf("Both methods provide same content: %v\n", len(files1) == len(files2))
    // Output: Both methods provide same content: true
}
```

--------------------------------

### Substrait Filter Relation with Condition (JSON)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Example of a Substrait 'filter' relation that takes a 'read' relation as input and applies a 'condition' to filter data, in this case, excluding the 'Computers' category.

```json
{
  "filter": {
    "input": {
      "read": {
        "...": "..."
      }
    },
    "condition": {
      "scalarFunction": {
        "functionReference": 2,
        "outputType": {
          "bool": {
            "nullability": "NULLABILITY_REQUIRED"
          }
        },
        "arguments": [
          {
            "value": {
              "scalarFunction": {
                "functionReference": 1,
                "outputType": {
                  "i64": {
                    "nullability": "NULLABILITY_NULLABLE"
                  }
                },
                "arguments": [
                  {
                    "value": {
                      "literal": {
                        "string": "Computers"
                      }
                    }
                  },
                  {
                    "value": {
                      "selection": {
                        "directReference": {
                          "structField": {
                            "field": 1
                          }
                        },
                        "rootReference": {}
                      }
                    }
                  }
                ]
              }
            }
          }
        ]
      }
    }
  }
}
```

--------------------------------

### SQL Example for Set Comparison Subquery

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/expressions/subqueries.md

Illustrates an SQL set comparison subquery using the ANY operator. This checks if a left-hand side expression satisfies a comparison operation against any of the values returned by the subquery.

```sql
SELECT * FROM t1 WHERE x < ANY(SELECT y from t2)
```

--------------------------------

### Substrait Literal Examples

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

Illustrates the syntax for various literal values used in Substrait test cases, including strings, integers, decimals, floating-point numbers (with special values), booleans, dates, times, timestamps, and intervals. It specifies the required format and range for each type.

```text
#### String
- **string**, **fixedchar**, **varchar**: A sequence of characters enclosed in single quotes. To include a single quote or backslash within the sequence, escape them with a backslash (e.g., `\' for a single quote and `\\` for a backslash). Example: 'Hello, world!'

#### Integer
Integers are represented as sequences of digits. Negative numbers are preceded by a minus sign.
- **i8**: 8-bit integer, range: -128 to 127
- **i16**: 16-bit integer, range: -32768 to 32767
- **i32**: 32-bit integer, range: -2147483648 to 2147483647
- **i64**: 64-bit integer, range: -9223372036854775808 to 9223372036854775807

#### Fixed Point decimals
- **decimal**: Fixed-point decimal number. Maximum 38 digits total, with up to 37 digits after the decimal point.
  Example: 123.456

#### Floating Point numbers
- **float**: General floating-point number, can be represented as:
  * Standard decimal notation: 123.456
  * Scientific notation: 1.23e4
  * Special values: `+inf` (Positive Infinity), `-inf` (Negative Infinity), `+0` (Positive Zero), `-0` (Negative Zero), `nan` (Not a Number), `snan` (Signaling NaN)
- **float32**: Single-precision float, approximately 6 significant digits, range: ~1.2e-38 to ~3.4e38
- **float64**: Double-precision float, approximately 15 significant digits, range: ~2.3e-308 to ~1.7e308

#### Boolean
- Valid values: true, false

#### Date and Time
All date and time literals use ISO 8601 format:

- **date**: `YYYY-MM-DD`, example: `2021-01-01`
- **time**: `HH:MM:SS[.fraction]`, example: `12:00:00.000`
- **timestamp**: `YYYY-MM-DD HH:MM:SS[.fraction]`, example: `2021-01-01 12:00:00`
- **timestamp_tz**: `YYYY-MM-DD HH:MM:SS[.fraction]±HH:MM`, example: `2021-01-01 12:00:00+05:30`
- **interval year**: `'P[n]Y[n]M'`, example: `'P2Y3M'` (2 years, 3 months)
- **interval days**: `'P[n]DT[n]H[n]M[n]S'`, example: `'P2DT3H2M9S'` (2 days, 3 hours, 2 minutes, 9 seconds)
  ex2: 'P1DT2H3M4.45::iday<3>' (1 day, 2 hours, 3 minutes, 4 seconds, 450 milliseconds)

#### Other complex types

- **list**: A list of values enclosed in brackets and separated by commas. Example: `[1, 2, 3]::list<i8>`, `['a', 'b', 'c']::list<str>`

**TODO** Add support for complex types like structs, maps etc.

```

--------------------------------

### Substrait Scalar Test Case Example

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

Defines a scalar test case, including the function name, arguments (literals), optional arguments, and the expected result or error. Arguments and results include their data types.

```code
add(126::i8, 1::i8) = 127::i8  # addition of two numbers
```

--------------------------------

### Substrait Test Group Description

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

Defines a logical grouping for related test cases. The description starts with a '#' and aids in organizing and understanding test suites.

```code
# Common Maths
```

--------------------------------

### Example UDF: Double Function (YAML)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/expressions/user_defined_functions.md

This snippet defines a user-defined function in YAML format that doubles its input. It specifies the function's signature and potential implementation details. Note that this example is specifically for 32-bit floats and integers, but could be extended to other data types. The user can configure behavior for out-of-range values.

```yaml
--8<-- "examples/extensions/double_function.yaml"
```

--------------------------------

### Substrait Citation (BibTeX)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/community/index.md

BibTeX entry for citing the Substrait project in academic research. This format is commonly used in LaTeX documents for bibliographies.

```bibtex
@misc{substrait,
  author = {substrait-io},
  title = {Substrait: Cross-Language Serialization for Relational Algebra},
  year = {2021},
  month = {8},
  day = {31},
  publisher = {GitHub},
  journal = {GitHub repository},
  howpublished = {\url{https://github.com/substrait-io/substrait}}
}
```

--------------------------------

### Substrait Aggregate Test Case (Single Argument)

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

An example of an aggregate test case with a single argument, which is a column of values from a defined table. The expected result is also provided with its data type.

```code
sum((1, 2, 3, 4, 5)::i8) = 15::i8  # addition of two numbers
```

--------------------------------

### Aggregate Function Correlation Test in Substrait

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

An example of a Substrait test file for aggregate functions, specifically demonstrating correlation. It includes defining input data, using the 'corr' function, and specifying expected results for different data scenarios.

```Substrait
### SUBSTRAIT_AGGREGATE_TEST:V1
### SUBSTRAIT_INCLUDE: /extensions/functions_arithmetic.yaml

# Correlation Tests
((20, 20), (-3, -3), (1, 1), (10,10), (5,5)) corr(col0::fp32, col1::fp32) = 1::fp64
DEFINE t1(fp32, fp32) = ((20, -20), (-3, 3), (1, -1), (10, -10), (5, -5))
corr(t1.col0, t1.col1) = -11::fp64
```

--------------------------------

### Substrait Struct and Named Struct Syntax Examples

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/types/type_parsing.md

Demonstrates the textual representation of Substrait structs and named structs. Structs define a sequence of types, while named structs associate names with types. These are crucial for defining complex data structures within Substrait plans and are supported in YAML and text formats, with named structs primarily for human consumption.

```yaml
# Struct
struct?[variation]<type0, type1,..., typeN>

# Named Struct
nstruct?[variation]<name0:type0, name1:type1,..., nameN:typeN>
```

```text
// Struct
struct?<string, i8, i32?, timestamp_tz>

// Named structs are not yet supported in the text format.
```

--------------------------------

### Substrait Protobuf Text for Field Reference Example

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/expressions/field_references.md

This code snippet demonstrates the Protobuf Text representation of a nested field reference in Substrait, corresponding to the SQL expression 'a.b[2].c['my_map_key'].x'. It shows how direct and nested references are structured.

```protobuf
selection {
  direct_reference {
    struct_field {
      field: 0 # .a
      child {
        struct_field {
          field: 0 # .b
          child {
            list_element {
              offset: 2
              child {
                struct_field {
                  field: 0 # .c
                  child {
                    map_key {
                      map_key {
                        string: "my_map_key" # ['my_map_key']
                      }
                      child {
                        struct_field {
                          field: 0 # .x
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
  root_reference { }
}
```

--------------------------------

### Substrait Custom Extension Types Definition

Source: https://context7.com/substrait-io/substrait/llms.txt

Defines custom structured data types for Substrait using YAML. Examples include 'point' with latitude and longitude, and 'line' composed of two 'point' types.

```yaml
# From extensions/extension_types.yaml
---
urn: extension:io.substrait:extension_types
types:
  - name: point
    structure:
      latitude: i32
      longitude: i32
  - name: line
    structure:
      start: point
      end: point
```

--------------------------------

### Defining a Point Type using NSTRUCT Sugar

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/types/type_classes.md

This YAML snippet is equivalent to the 'point_with_structure.yaml' example, using the NSTRUCT syntactic sugar. It defines a 'point' type composed of 'longitude' and 'latitude' i32 fields, providing structural information for interoperability.

```yaml
--8<-- "examples/types/point_with_nstruct.yaml"
```

--------------------------------

### Substrait Cast Expression Example

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/expressions/specialized_record_expressions.md

Demonstrates how to use a cast expression to convert a value from one type to another in Substrait. It includes specifying the target type, the input argument, and the failure behavior (null on failure or throw exception).

```protobuf
message Cast { 
  // Type to cast to.
  Type expected_type = 1;
  // The expression to cast.
  Expression input = 2;
  // Defines behavior when cast fails.
  CastBehavior failure_behavior = 3;
}
```

--------------------------------

### Referencing External Types in YAML

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/extensions/index.md

This example demonstrates how to reference types defined in another YAML extension file. It shows the use of the 'dependencies' key to declare an external extension URN and assign an alias. This alias is then used as a namespace prefix when referencing types from the external definition.

```yaml
--8<-- "examples/extensions/distance_functions.yaml"
```

--------------------------------

### Substrait Literal Value Expression (JSON)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Represents a literal value within a Substrait expression. This example shows a string literal. Substrait supports various literal types, including strings, integers, booleans, etc., as defined in the Expression proto definition.

```json
{
  "literal": {
    "string": "Computers"
  }
}
```

--------------------------------

### Substrait Field Reference Expression (JSON)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Represents a direct reference to a field within a data structure, identified by its numerical index. This is analogous to referencing columns by name in SQL but uses numerical indices in Substrait, requiring context from a schema. The example shows a reference to the second field (index 1).

```json
{
  "selection": {
    "directReference": {
      "structField": {
        "field": 1
      }
    },
    "rootReference": {}
  }
}
```

--------------------------------

### Creating and Verifying Substrait Plans in Go

Source: https://context7.com/substrait-io/substrait/llms.txt

Demonstrates how to programmatically construct and serialize Substrait plans using Protocol Buffer Go bindings. It shows accessing extension definitions via an embedded filesystem and verifying available extension URNs.

```go
package main

import (
    "fmt"
    "strings"
    "github.com/spf13/afero"
    "github.com/substrait-io/substrait"
    // Note: In real usage, import generated protobuf package:
    // pb "github.com/substrait-io/substrait-protobuf/go/substraitpb"
)

func main() {
    // Access extension definitions using embedded filesystem
    extFS := substrait.GetSubstraitExtensionsFS()

    // Create a plan (pseudo-code showing structure)
    // plan := &pb.Plan{
    //     Version: &pb.Version{
    //         MajorNumber: 0,
    //         MinorNumber: 44,
    //         PatchNumber: 0,
    //         Producer:    "my-query-engine",
    //     },
    //     ExtensionUrns: []*pb.SimpleExtensionURN{
    //         {Uri: "extension:io.substrait:functions_arithmetic"},
    //     },
    //     Relations: []*pb.PlanRel{
    //         // Define query relations here
    //     },
    // }

    // Verify extension files exist by listing them
    files, _ := afero.ReadDir(extFS, "extensions")
    fmt.Printf("Available extension URNs: %d files\n", len(files))
    for _, f := range files[:3] {
        fmt.Printf("  - extension:io.substrait:%s\n",
            strings.TrimSuffix(f.Name(), ".yaml"))
    }
    // Example Output:
    // Available extension URNs: 17 files
    //   - extension:io.substrait:functions_arithmetic
    //   - extension:io.substrait:functions_string
    //   - extension:io.substrait:functions_datetime
}
```

--------------------------------

### Serve Substrait Site Locally (Shell)

Source: https://github.com/substrait-io/substrait/blob/main/site/README.md

Builds and serves the Substrait website locally using MkDocs. This command allows developers to preview changes in real-time before committing them. It should be run from the root of the project.

```shell
mkdocs serve
```

--------------------------------

### SQL Table Definitions

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Defines the schema for 'orders' and 'products' tables. The 'orders' table includes product ID, quantity, order date, and price. The 'products' table contains product ID, categories, details (struct), and product name.

```sql
CREATE TABLE orders (
  product_id: i64 NOT NULL,
  quantity: i32 NOT NULL,
  order_date: date NOT NULL,
  price: decimal(10, 2)
);
```

```sql
CREATE TABLE products (
  product_id: i64 NOT NULL,
  categories: list<string NOT NULL> NOT NULL,
  details: struct<manufacturer: string, year_created: int32>,
  product_name: string
);
```

--------------------------------

### Substrait Test Case Validation in Go

Source: https://context7.com/substrait-io/substrait/llms.txt

Demonstrates how to programmatically parse and validate Substrait test cases using Go. It reads test files from a virtual filesystem and extracts individual test case strings.

```go
package main

import (
    "bufio"
    "fmt"
    "io/fs"
    "strings"
    "github.com/substrait-io/substrait"
)

func main() {
    testFS := substrait.GetSubstraitTestsFS()

    // Parse test file
    content, _ := fs.ReadFile(testFS, "tests/cases/arithmetic/add.test")
    scanner := bufio.NewScanner(strings.NewReader(string(content)))

    testCases := []string{}
    for scanner.Scan() {
        line := scanner.Text()
        // Parse test cases (lines without # prefix that contain '=')
        if !strings.HasPrefix(line, "#") && strings.Contains(line, "=") {
            testCases = append(testCases, strings.TrimSpace(line))
        }
    }

    fmt.Printf("Parsed %d test cases:\n", len(testCases))
    for _, tc := range testCases[:3] {
        fmt.Printf("  %s\n", tc)
    }
    // Output:
    // Parsed 10+ test cases:
    //   add(120::i8, 5::i8) = 125::i8
    //   add(100::i16, 100::i16) = 200::i16
    //   add(30000::i32, 30000::i32) = 60000::i32
}
```

--------------------------------

### Access Embedded Substrait Test Cases FileSystem in Go

Source: https://context7.com/substrait-io/substrait/llms.txt

Retrieves an embedded filesystem containing all Substrait test case files used for validating function implementations. It requires the 'github.com/substrait-io/substrait' package and standard Go 'io/fs'. The function demonstrates reading a specific test case (e.g., 'arithmetic/add.test') and listing all tests within a directory.

```go
package main

import (
    "fmt"
    "io/fs"
    "github.com/substrait-io/substrait"
)

func main() {
    // Get the test cases filesystem
    testFS := substrait.GetSubstraitTestsFS()

    // Read a specific test case
    testData, err := fs.ReadFile(testFS, "tests/cases/arithmetic/add.test")
    if err != nil {
        panic(err)
    }

    fmt.Printf("Test case content:\n%s\n", string(testData))

    // List all arithmetic test cases
    arithmeticTests := []string{}
    fs.WalkDir(testFS, "tests/cases/arithmetic", func(path string, d fs.DirEntry, err error) error {
        if !d.IsDir() && d.Name() != "arithmetic" {
            arithmeticTests = append(arithmeticTests, path)
        }
        return nil
    })

    fmt.Printf("Found %d arithmetic test cases\n", len(arithmeticTests))
    // Output: Tests include add.test, subtract.test, multiply.test, divide.test, etc.
```

--------------------------------

### Validate Substrait Plan with substrait-validator CLI

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Command-line usage for validating a Substrait plan JSON file using the substrait-validator tool. This command generates an HTML report detailing the plan's schema and column indices.

```sh
substrait-validator final_plan.json --out-file output.html
```

--------------------------------

### SQL Example for IN Subquery Predicate

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/expressions/subqueries.md

Demonstrates the usage of an SQL IN subquery predicate, checking if an expression is contained within the results of a subquery. This is useful for filtering data based on a list of values returned by another query.

```sql
SELECT * FROM t1 WHERE x IN (SELECT * FROM t2)
```

```sql
SELECT * FROM t1 WHERE (x, y) IN (SELECT a, b FROM t2)
```

--------------------------------

### Substrait Extension URNs Definition

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Defines the URN identifiers for extension files used within a Substrait plan. Each entry maps an anchor to a specific URN, linking function references to their definitions.

```json
[
  {
    "extensionUrnAnchor": 1,
    "urn": "extension:io.substrait:functions_set"
  },
  {
    "extensionUrnAnchor": 2,
    "urn": "extension:io.substrait:functions_comparison"
  },
  {
    "extensionUrnAnchor": 3,
    "urn": "extension:io.substrait:functions_arithmetic_decimal"
  }
]
```

--------------------------------

### Substrait Plan Message Structure (Protocol Buffers)

Source: https://context7.com/substrait-io/substrait/llms.txt

Defines the `Plan` message, representing a complete query execution plan. It includes version information, extension declarations, and relation trees. This structure is fundamental for serializing and deserializing query plans.

```protobuf
syntax = "proto3";
package substrait;

message Plan {
  Version version = 6;
  repeated substrait.extensions.SimpleExtensionURN extension_urns = 8;
  repeated substrait.extensions.SimpleExtensionDeclaration extensions = 2;
  repeated PlanRel relations = 3;
  substrait.extensions.AdvancedExtension advanced_extensions = 4;
  repeated string expected_type_urls = 5;
  repeated DynamicParameterBinding parameter_bindings = 7;
  repeated TypeAlias type_aliases = 9;
}

message Version {
  uint32 major_number = 1;
  uint32 minor_number = 2;
  uint32 patch_number = 3;
  string git_hash = 4;
  string producer = 5;
}

message DynamicParameterBinding {
  uint32 parameter_anchor = 1;
  Expression.Literal value = 2;
}
```

--------------------------------

### Substrait Extension Functions Definition

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Lists the specific functions used in a Substrait plan, referencing their URNs and anchors. This allows consumers to understand the meaning and origin of each function within the plan.

```json
[
  {
    "extensionFunction": {
      "extensionUrnReference": 1,
      "functionAnchor": 1,
      "name": "index_in"
    }
  },
  {
    "extensionFunction": {
      "extensionUrnReference": 2,
      "functionAnchor": 2,
      "name": "is_null"
    }
  },
  {
    "extensionFunction": {
      "extensionUrnReference": 2,
      "functionAnchor": 3,
      "name": "equal"
    }
  },
  {
    "extensionFunction": {
      "extensionUrnReference": 3,
      "functionAnchor": 4,
      "name": "sum"
    }
  },
  {
    "extensionFunction": {
      "extensionUrnReference": 3,
      "functionAnchor": 5,
      "name": "multiply"
    }
  }
]
```

--------------------------------

### Substrait Plan Message Serialization (Proto)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/serialization/binary_serialization.md

This snippet defines the main top-level object, the Plan message, used for communicating Substrait plans via protobuf. It is composed of data structures to minimize repetition and one or more Relation trees. This is the core structure for binary serialization.

```proto
syntax = "proto3";

package io.substrait;

import "substrait/extensions/extensions.proto";
import "substrait/plan_common.proto";

message Plan {
  repeated Relation roots = 1;
  repeated ExtensionDeclaration extensions = 2;
  optional Version version = 3;
  optional string partial_velocity_// _expr_extension_uri = 4;
  repeated Expression_ExtensionAnchor expr_extension_anchors = 5;
}

```

--------------------------------

### Access Embedded Substrait Extensions FileSystem in Go

Source: https://context7.com/substrait-io/substrait/llms.txt

Retrieves an embedded filesystem containing all Substrait extension YAML files, allowing access to function and type definitions. Dependencies include the 'github.com/substrait-io/substrait' package and standard Go 'io/fs' package. It reads specific files like 'functions_arithmetic.yaml' and lists all available extension files.

```go
package main

import (
    "fmt"
    "io/fs"
    "github.com/substrait-io/substrait"
)

func main() {
    // Get the embedded filesystem
    embedFS := substrait.GetSubstraitExtensionsFS()

    // Read the arithmetic functions definition
    data, err := fs.ReadFile(embedFS, "extensions/functions_arithmetic.yaml")
    if err != nil {
        panic(err)
    }

    fmt.Printf("Loaded %d bytes from functions_arithmetic.yaml\n", len(data))

    // List all available extension files
    files := []string{}
    fs.WalkDir(embedFS, ".", func(path string, d fs.DirEntry, err error) error {
        if !d.IsDir() {
            files = append(files, path)
        }
        return nil
    })

    fmt.Printf("Found %d extension files\n", len(files))
    // Output: Found 17+ extension files including functions_arithmetic.yaml,
    // functions_string.yaml, functions_datetime.yaml, etc.
}
```

--------------------------------

### Substrait Relational Algebra Common Fields (Protocol Buffers)

Source: https://context7.com/substrait-io/substrait/llms.txt

Defines the `RelCommon` message, which includes common fields for relational algebra operations. It supports different emission kinds (`Direct`, `Emit`), hints, and advanced extensions, providing metadata for query plan nodes.

```protobuf
syntax = "proto3";
package substrait;

message RelCommon {
  oneof emit_kind {
    Direct direct = 1;
    Emit emit = 2;
  }

  Hint hint = 3;
  substrait.extensions.AdvancedExtension advanced_extension = 4;

  message Direct {}

  message Emit {
    repeated int32 output_mapping = 1;
  }

  message Hint {
    Stats stats = 1;
    RuntimeConstraint constraint = 2;
    string alias = 3;
    repeated string output_names = 4;

    message Stats {
      double row_count = 1;
      double record_size = 2;
    }
  }
}
```

--------------------------------

### Simple Extension Declaration (Proto)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/serialization/binary_serialization.md

Represents a specific extension within a YAML document, combining a reference to an extension URN with a unique key for that extension. It also defines a declaration anchor, which is a plan-specific unique value used for referencing.

```proto
syntax = "proto3";

package io.substrait;

message SimpleExtensionDeclaration {
  string extension_urn_anchor = 1;
  string extension_name_anchor = 2;
  optional string declaration_anchor = 3;
}

```

--------------------------------

### Substrait List of Strings Type Representation

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Shows the JSON structure for a list data type in Substrait, where the elements are strings. This highlights how to represent collection types and their element types.

```json
{
  "list": {
    "type": {
      "string": {
        "nullability": "NULLABILITY_REQUIRED"
      }
    },
    "nullability": "NULLABILITY_REQUIRED"
  }
}
```

--------------------------------

### Substrait String Type Representation

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Demonstrates the JSON structure for representing a nullable string data type in Substrait. This is a fundamental building block for defining column types.

```json
{
  "string": {
    "nullability": "NULLABILITY_NULLABLE"
  }
}
```

--------------------------------

### Advanced Extension Object (Proto)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/serialization/binary_serialization.md

Defines the structure for advanced extensions in Substrait's protobuf serialization. This object is exposed in multiple places within the representation and is used for extensions that are either optimizations or enhancements to the plan's behavior or efficiency.

```proto
syntax = "proto3";

package io.substrait;

message AdvancedExtension {
  optional string extension_uri = 1;
  optional string name = 2;
  optional string version = 3;
  optional string annotation = 4;
  optional string type = 5;
  oneof extension_type {
    message Optimization {} // Properties that may help consumers work more efficiently.
    message Enhancement {} // Properties that functionally change the behavior of the plan.
  }
}

```

--------------------------------

### Substrait Plan Structure

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

The fundamental JSON structure of a Substrait plan, including fields for extension URNs, extensions, and relations. Relations are typically root relations, often with a single root for most queries.

```json
{
  "extensionUrns": [ ... ],
  "extensions": [ ... ],
  "relations": [
    {
      "root": {
        "names": [
          "product_name",
          "product_id",
          "sales"
        ],
        "input": { ... }
      }
    }
  ]
}
```

--------------------------------

### Local Files Read

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/relations/logical_relations.md

Describes how to read data from local files, including various formats like Parquet, Arrow, and ORC, and supports slicing files by byte offsets.

```APIDOC
## Read Local Files

### Description
This endpoint allows reading data from local files. It supports various file formats including Parquet, Arrow, ORC, Drwf, and Delimiter Separated Text. It also supports slicing files by specifying byte offsets.

### Method
GET

### Endpoint
/read/local_files

### Parameters
#### Query Parameters
- **Items** (array) - Required - An array of Items (path or path glob) associated with the read.
- **format** (string) - Optional - Specifies the file format (e.g., 'parquet', 'arrow', 'orc', 'drwf', 'text', 'any'). Defaults to 'any'.
- **slice_start** (integer) - Optional - The starting byte offset for file slicing.
- **slice_end** (integer) - Optional - The ending byte offset for file slicing.

### Request Example
```json
{
  "items": [
    "file:///path/to/your/data.parquet",
    "s3://your-bucket/data/*.orc"
  ],
  "format": "parquet",
  "slice_start": 1024,
  "slice_end": 2048
}
```

### Response
#### Success Response (200)
- **data** (array) - The data read from the specified files.

#### Response Example
```json
{
  "data": [
    { "column1": "value1", "column2": 123 },
    { "column1": "value2", "column2": 456 }
  ]
}
```
```

--------------------------------

### Substrait Aggregate Test Case (Multiple Columns Verbose)

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

A verbose format for aggregate test cases with multiple columns. A table is explicitly defined using `DEFINE`, and function arguments refer to the table's columns by name.

```code
DEFINE t1(fp32, fp32) = ((20, 20), (-3, -3), (1, 1), (10,10), (5,5))
corr(t1.col0, t1.col1) = 1::fp64
```

--------------------------------

### Control Column Order with Emit Property in Aggregate Relation

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Demonstrates how to use the 'emit' property with 'outputMapping' to reorder columns in the output schema of an aggregate relation. This allows specifying the desired order of fields by referencing their indices.

```json
{
  "aggregate": {
    "input": { ... },
    "groupings": [ ... ],
    "measures": [ ... ],
    "common": {
      "emit": {
        "outputMapping": [1, 0, 2]
      }
    }
  }
}
```

--------------------------------

### Substrait Include Statement for Extensions

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

Specifies the path to Substrait extension functions required for testing. This statement is optional but recommended for complex function sets.

```code
### SUBSTRAIT_INCLUDE: /extensions/functions_aggregate_approx.yaml
```

--------------------------------

### Simple Extension URN Definition (Proto)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/serialization/binary_serialization.md

Defines the structure for a Simple Extension URN in Substrait's protobuf serialization. This URN serves as a unique identifier for an extension, pointing to a YAML document that specifies one or more extensions and declares an anchor for use in declarations.

```proto
syntax = "proto3";

package io.substrait;

message SimpleExtensionURN {
  string extension_urn = 1;
  repeated SimpleExtensionDeclaration extension_declarations = 2;
}

```

--------------------------------

### Substrait Scalar Test File Grammar

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

Defines the formal grammar for Substrait scalar test files, outlining the structure of version declarations, includes, test groups, test cases, and their components like functions, arguments, literals, results, and options. This grammar is used by the `FuncTestCaseParser.g4` ANTLR parser.

```ANTLR Grammar
doc         := <version>
               (<include>)+ 
               ((<test_group>)?(<test_case>)+
)+
version     := ### SUBSTRAIT_SCALAR_TEST: <test_library_version>
include     := ### SUBSTRAIT_INCLUDE: <uri>
test_group  := # <description>
test_case   := <function>(<arguments>) ([<options>])? = <result> (#<description>)?
description := string
function    := string
arguments   := <argument>, <argument>, ... <argument>
argument    := <literal>
literal     := <literal_value>::<datatype>
result      := <substrait_error> | <literal>
options     := <option>, <option>, ... <option>
option      := <option_name>:<option_value>
literal_value := string | integer | decimal | float | boolean | date | time | timestamp | timestamp_tz | interval year | interval days | null
datatype    := <basic_type> | <parametrized_type> | <complex_type>
basic_type := bool | i8 | i16 | i32 | i64 | f32 | f64 | str | date | time | ts | tstz | iyear | iday | <parametrized_type> 
parametrized_type := dec<int,int> | fchar<int> | vchar<int> | vbin<int>
complex_type := <struct> | <list> | <map>
substrait_error := <!ERROR> | <!UNDEFINED>

```

--------------------------------

### Substrait Arithmetic Function Test Specification

Source: https://context7.com/substrait-io/substrait/llms.txt

Specifies test cases for Substrait arithmetic functions, including basic operations, overflow scenarios, floating-point exceptions, and rounding behaviors. Uses a clear, human-readable format.

```text
### SUBSTRAIT_SCALAR_TEST: v1.0
### SUBSTRAIT_INCLUDE: '/extensions/functions_arithmetic.yaml'

# basic: Basic examples without any special cases
add(120::i8, 5::i8) = 125::i8
add(100::i16, 100::i16) = 200::i16
add(30000::i32, 30000::i32) = 60000::i32

# overflow: Examples demonstrating overflow behavior
add(120::i8, 10::i8) [overflow:ERROR] = <!ERROR>
add(120::i8, 10::i8) [overflow:SATURATE] = 127::i8
add(120::i8, 10::i8) [overflow:SILENT] = <!UNDEFINED>

# floating_exception: Examples demonstrating exceptional floating point cases
add(1.5e+308::fp64, 1.5e+308::fp64) = inf::fp64
add(-1.5e+308::fp64, -1.5e+308::fp64) = -inf::fp64

# rounding: Examples demonstrating floating point rounding behavior
add(4.5::fp32, 2.5000007152557373046875::fp32) [rounding:TIE_TO_EVEN] = 7.00000095367431640625::fp32
```

--------------------------------

### Substrait Data Types

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

Lists the available short names for data types supported by Substrait, referencing the official extensions documentation. It covers primitive types like boolean and integers, floating-point numbers, fixed-point decimals, strings, dates, times, and intervals.

```text
- **bool**: Boolean
- **i8**: 8-bit signed integer
- **i16**: 16-bit signed integer
- **i32**: 32-bit signed integer
- **i64**: 64-bit signed integer
- **f32**: 32-bit floating point number
- **f64**: 64-bit floating point number
- **dec**: Fixed-point `decimal<P,S>`
- **str**: Variable-length string
- **fchar**: Fixed-length string `fixedchar<N>`
- **vchar**: Variable-length string `varchar<N>`
- **vbin**: Fixed-length binary `fixedbinary<N>`
- **date**: Date
- **time**: Time
- **ts**: Timestamp
- **tstz**: Timestamp with timezone
- **iyear**: Interval year
- **iday**: Interval days
- **pt**: Precision Time
- **pts**: Precision Timestamp
- **ptstz**: Precision Timestamp with timezone
```

--------------------------------

### YAML Schema for Simple Extensions

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/extensions/index.md

This snippet refers to the YAML schema used for defining simple extensions in Substrait. It outlines the structure for data types, type variations, scalar functions, aggregate functions, and window functions. Developers must ensure their YAML files conform to this schema for proper interpretation and interoperability.

```yaml
--8<-- "text/simple_extensions_schema.yaml"
```

--------------------------------

### Substrait Aggregate Test Case (Multiple Columns Compact)

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

A compact format for aggregate test cases involving multiple columns. The table data is defined inline before the function call, with columns specified as col0, col1, etc.

```code
((20, 20), (-3, -3), (1, 1), (10,10), (5,5)) corr(col0::fp32, col1::fp32) = 1::fp64
```

--------------------------------

### ProjectRel Message Definition (ProtoBuf)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/relations/logical_relations.md

Defines the structure for the ProjectRel message in Substrait's algebra, including input and expressions properties for data projection operations.

```proto
%%% proto.algebra.ProjectRel %%%
```

--------------------------------

### Substrait Read Relation Definition (JSON)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/tutorial/sql_to_substrait.md

Defines a Substrait 'read' relation for the 'orders' table, including its 'namedTable' identifier and 'baseSchema' which specifies column names and types.

```json
{
  "read": {
    "namedTable": {
      "names": [
        "orders"
      ]
    },
    "baseSchema": {
      "names": [
        "product_id",
        "quantity",
        "order_date",
        "price"
      ],
      "struct": {
        "types": [
          {
            "i64": {
              "nullability": "NULLABILITY_REQUIRED"
            }
          },
          {
            "i32": {
              "nullability": "NULLABILITY_REQUIRED"
            }
          },
          {
            "date": {
              "nullability": "NULLABILITY_REQUIRED"
            }
          },
          {
            "decimal": {
              "scale": 10,
              "precision": 2,
              "nullability": "NULLABILITY_NULLABLE"
            }
          }
        ],
        "nullability": "NULLABILITY_REQUIRED"
      }
    }
  }
}
```

--------------------------------

### CrossRel Message Definition (ProtoBuf)

Source: https://github.com/substrait-io/substrait/blob/main/site/docs/relations/logical_relations.md

Defines the structure for the CrossRel message in Substrait's algebra, specifying left and right input properties for cross product operations.

```proto
%%% proto.algebra.CrossRel %%%
```

--------------------------------

### Substrait Aggregate Test Declaration

Source: https://github.com/substrait-io/substrait/blob/main/tests/README.md

Declares a test file to contain aggregate tests and specifies the file format version. This must be the first line of the file and cannot be mixed with scalar tests.

```code
### SUBSTRAIT_AGGREGATE_TEST: V1
```
Tokens:
Raw