Low level operation classes. Subject to change in non-major releases.
analytic
Operations for analytic window functions.
| Name | Description |
|---|---|
| Analytic | Base class for analytic window function operations. |
| CumeDist | Compute the cumulative distribution function of a column over a window. |
| DenseRank | Rank within an ordered partition, consecutively. |
| Lag | Shift a column forward. |
| Lead | Shift a column backward. |
| MinRank | Rank within an ordered partition. |
| NTile | Compute the percentile of a column over a window. |
| NthValue | Retrieve the Nth element of a column over a window. |
| PercentRank | Compute the percentile rank over a window. |
| RankBase | Base class for ranking operations. |
| RowNumber | Compute the row number over a window, starting from 0. |
| ShiftBase | Base class for shift operations. |
Base class for analytic window function operations.
Compute the cumulative distribution function of a column over a window.
Rank within an ordered partition, consecutively.
Shift a column forward.
Shift a column backward.
Rank within an ordered partition.
Compute the percentile of a column over a window.
Retrieve the Nth element of a column over a window.
Compute the percentile rank over a window.
Base class for ranking operations.
Compute the row number over a window, starting from 0.
Base class for shift operations.
arrays
Operations for array expressions.
| Name | Description |
|---|---|
| Array | Construct an array. |
| ArrayAll | Compute whether all array elements are true. |
| ArrayAny | Compute whether any array element is true. |
| ArrayConcat | Concatenate two or more arrays into a single array. |
| ArrayContains | Return whether an array contains a specific value. |
| ArrayDistinct | Return the unique elements of an array. |
| ArrayFilter | Filter array elements with a function. |
| ArrayFlatten | Flatten a nested array one level. |
| ArrayIndex | Return the element of an array at some index. |
| ArrayIntersect | Return the intersection of two arrays. |
| ArrayLength | Compute the length of an array. |
| ArrayMap | Apply a function to every element of an array. |
| ArrayMax | Compute the maximum value of an array. |
| ArrayMean | Compute the average of an array. |
| ArrayMin | Compute the minimum value of an array. |
| ArrayPosition | Return the position of a specific value in an array. |
| ArrayRemove | Remove an element from an array. |
| ArrayRepeat | Repeat the elements of an array. |
| ArraySlice | Slice an array element. |
| ArraySort | Sort the values of an array. |
| ArraySum | Compute the sum of an array. |
| ArrayUnion | Return the union of two arrays. |
| ArrayZip | Zip two or more arrays into an array of structs. |
| IntegerRange | Produce an array of integers from start to stop, moving by step. |
| Range | Base class for range-generating operations. |
| TimestampRange | Produce an array of timestamps from start to stop, moving by step. |
| Unnest | Unnest an array value into a column. |
Construct an array.
Compute whether all array elements are true.
Compute whether any array element is true.
Concatenate two or more arrays into a single array.
Return whether an array contains a specific value.
Return the unique elements of an array.
Filter array elements with a function.
Flatten a nested array one level.
The input expression must have at least one level of nesting for flattening to make sense.
Return the element of an array at some index.
Return the intersection of two arrays.
Compute the length of an array.
Apply a function to every element of an array.
Compute the maximum value of an array.
Compute the average of an array.
Compute the minimum value of an array.
Return the position of a specific value in an array.
Remove an element from an array.
Repeat the elements of an array.
Slice an array element.
Sort the values of an array.
Compute the sum of an array.
Return the union of two arrays.
Zip two or more arrays into an array of structs.
Produce an array of integers from start to stop, moving by step.
Base class for range-generating operations.
Produce an array of timestamps from start to stop, moving by step.
Unnest an array value into a column.
generic
Generic value operations.
| Name | Description |
|---|---|
| Cast | Explicitly cast a value to a specific data type. |
| Coalesce | Return the first non-null expression from a tuple of expressions. |
| Constant | A function that produces a constant. |
| DateNow | Return the current date. |
| E | The mathematical constant e. |
| Greatest | Return the largest value from a tuple of expressions. |
| Hash | Return the hash of a value. |
| HexDigest | Return the hexadecimal digest of a value. |
| IsNull | Return true if values are null. |
| Least | Return the smallest value from a tuple of expressions. |
| Literal | A constant value. |
| NotNull | Returns true if values are not null. |
| NullIf | Return NULL if an expression equals some specific value. |
| Pi | The mathematical constant pi. |
| RandomScalar | Return a random scalar between 0 and 1. |
| RandomUUID | Return a random UUID. |
| RowID | The row number of the returned result. |
| SearchedCase | Searched case statement. |
| SimpleCase | Simple case statement. |
| TimestampNow | Return the current timestamp. |
| TryCast | Try to cast a value to a specific data type. |
| TypeOf | Return the database data type of the input expression. |
Explicitly cast a value to a specific data type.
Return the first non-null expression from a tuple of expressions.
A function that produces a constant.
Return the current date.
The mathematical constant e.
Return the largest value from a tuple of expressions.
Return the hash of a value.
Return the hexadecimal digest of a value.
Return true if values are null.
Return the smallest value from a tuple of expressions.
A constant value.
Returns true if values are not null.
Return NULL if an expression equals some specific value.
The mathematical constant pi.
Return a random scalar between 0 and 1.
Return a random UUID.
The row number of the returned result.
Searched case statement.
Simple case statement.
Return the current timestamp.
Try to cast a value to a specific data type.
Return the database data type of the input expression.
geospatial
Geospatial operations.
| Name | Description |
|---|---|
| GeoArea | Area of the geo spatial data. |
| GeoAsBinary | Return the Well-Known Binary (WKB) representation of the input, without SRID meta data. |
| GeoAsEWKB | Return the Well-Known Binary representation of the input, with SRID meta data. |
| GeoAsEWKT | Return the Well-Known Text representation of the input, with SRID meta data. |
| GeoAsText | Return the Well-Known Text (WKT) representation of the input, without SRID metadata. |
| GeoAzimuth | Return the angle in radians from the horizontal of the vector defined by the two inputs. |
| GeoBuffer | Return all points whose distance from this geometry is less than or equal to radius. |
| GeoCentroid | Returns the geometric center of a geometry. |
| GeoContains | Check if the first geo spatial data contains the second one. |
| GeoContainsProperly | Check if the left value contains the right one, with no shared no boundary points. |
| GeoConvert | Returns a transformed version of the given geometry from source crs/srid to a target crs/srid. |
| GeoCoveredBy | Check if no point in the left operand is outside that of the right. |
| GeoCovers | Check if no point in the right operand is outside that of the left. |
| GeoCrosses | Check if the inputs have some but not all interior points in common. |
| GeoDFullyWithin | Check if the geometries are fully within distance of one another. |
| GeoDWithin | Check if the geometries are within distance of one another. |
| GeoDifference | Return a geometry that is the delta between the left and right inputs. |
| GeoDisjoint | Check if the Geometries do not spatially intersect. |
| GeoDistance | Returns minimum distance between two geospatial operands. |
| GeoEndPoint | Return the last point of a LINESTRING geometry as a POINT. |
| GeoEnvelope | The bounding box of the supplied geometry. |
| GeoEquals | Returns True if the given geometries represent the same geometry. |
| GeoFlipCoordinates | Returns a new geometry with the coordinates of the input geometry “flipped” so that x = y and y = x. |
| GeoGeometryN | Returns the Nth Geometry of a Multi geometry. |
| GeoGeometryType | Returns the type of the geometry. |
| GeoIntersection | Return a geometry that represents the point-set intersection of the inputs. |
| GeoIntersects | Returns True if the Geometries/Geography “spatially intersect in 2D”. |
| GeoIsValid | Returns true if the geometry is well-formed. |
| GeoLength | Length of geo spatial data. |
| GeoLineLocatePoint | Locate the distance a point falls along the length of a line. |
| GeoLineMerge | Merge a MultiLineString into a LineString. |
| GeoLineSubstring | Clip a substring from a LineString. |
| GeoMaxDistance | Returns the 2-dimensional max distance between two geometries in projected units. |
| GeoNPoints | Return the number of points in a geometry. |
| GeoNRings | Return the number of rings for polygons or multipolygons. |
| GeoOrderingEquals | Check if two geometries are equal and have the same point ordering. |
| GeoOverlaps | Check if the inputs are of the same dimension but are not completely contained by each other. |
| GeoPerimeter | Perimeter of the geo spatial data. |
| GeoPoint | Return a point constructed from the input coordinate values. |
| GeoPointN | Return the Nth point in a single linestring in the geometry. |
| GeoSRID | Returns the spatial reference identifier for the ST_Geometry. |
| GeoSetSRID | Set the spatial reference identifier for the ST_Geometry. |
| GeoSimplify | Returns a simplified version of the given geometry. |
| GeoSpatialBinOp | Geo Spatial base binary. |
| GeoSpatialUnOp | Geo Spatial base unary. |
| GeoStartPoint | Return the first point of a LINESTRING geometry as a POINT. |
| GeoTouches | Check if the inputs have at least one point in common but their interiors do not intersect. |
| GeoTransform | Returns a transformed version of the given geometry into a new SRID. |
| GeoUnaryUnion | Returns the pointwise union of the geometries in the column. |
| GeoUnion | Returns the pointwise union of the two geometries. |
| GeoWithin | Returns True if the geometry A is completely inside geometry B. |
| GeoX | Return the X coordinate of the point, or NULL if not available. |
| GeoXMax | Returns X maxima of a bounding box 2d or 3d or a geometry. |
| GeoXMin | Returns Y minima of a bounding box 2d or 3d or a geometry. |
| GeoY | Return the Y coordinate of the point, or NULL if not available. |
| GeoYMax | Returns Y maxima of a bounding box 2d or 3d or a geometry. |
| GeoYMin | Returns Y minima of a bounding box 2d or 3d or a geometry. |
Area of the geo spatial data.
Return the Well-Known Binary (WKB) representation of the input, without SRID meta data.
Return the Well-Known Binary representation of the input, with SRID meta data.
Return the Well-Known Text representation of the input, with SRID meta data.
Return the Well-Known Text (WKT) representation of the input, without SRID metadata.
Return the angle in radians from the horizontal of the vector defined by the two inputs.
Angle is computed clockwise from down-to-up: on the clock: 12=0; 3=PI/2; 6=PI; 9=3PI/2.
Return all points whose distance from this geometry is less than or equal to radius.
Calculations are in the Spatial Reference System of this geometry.
Returns the geometric center of a geometry.
Check if the first geo spatial data contains the second one.
Check if the left value contains the right one, with no shared no boundary points.
Returns a transformed version of the given geometry from source crs/srid to a target crs/srid.
Check if no point in the left operand is outside that of the right.
Check if no point in the right operand is outside that of the left.
Check if the inputs have some but not all interior points in common.
Check if the geometries are fully within distance of one another.
Check if the geometries are within distance of one another.
Return a geometry that is the delta between the left and right inputs.
Check if the Geometries do not spatially intersect.
Returns minimum distance between two geospatial operands.
Return the last point of a LINESTRING geometry as a POINT.
Returns NULL if the input is not a LINESTRING.
The bounding box of the supplied geometry.
Returns True if the given geometries represent the same geometry.
Returns a new geometry with the coordinates of the input geometry “flipped” so that x = y and y = x.
Returns the Nth Geometry of a Multi geometry.
Returns the type of the geometry.
Return a geometry that represents the point-set intersection of the inputs.
Returns True if the Geometries/Geography “spatially intersect in 2D”.
Returns true if the geometry is well-formed.
Length of geo spatial data.
Locate the distance a point falls along the length of a line.
Returns a float between zero and one representing the location of the closest point on the linestring to the given point, as a fraction of the total 2d line length.
Merge a MultiLineString into a LineString.
Returns a (set of) LineString(s) formed by sewing together the constituent line work of a multilinestring. If a geometry other than a linestring or multilinestring is given, this will return an empty geometry collection.
Clip a substring from a LineString.
Returns a linestring that is a substring of the input one, starting and ending at the given fractions of the total 2d length. The second and third arguments are floating point values between zero and one. This only works with linestrings.
Returns the 2-dimensional max distance between two geometries in projected units.
If g1 and g2 is the same geometry the function will return the distance between the two vertices most far from each other in that geometry
Return the number of points in a geometry.
Return the number of rings for polygons or multipolygons.
Outer rings are counted.
Check if two geometries are equal and have the same point ordering.
Returns true if the two geometries are equal and the coordinates are in the same order.
Check if the inputs are of the same dimension but are not completely contained by each other.
Perimeter of the geo spatial data.
Return a point constructed from the input coordinate values.
Constant coordinates result in construction of a POINT literal.
Return the Nth point in a single linestring in the geometry.
Negative values are counted backwards from the end of the LineString, so that -1 is the last point. Returns NULL if there is no linestring in the geometry
Returns the spatial reference identifier for the ST_Geometry.
Set the spatial reference identifier for the ST_Geometry.
Returns a simplified version of the given geometry.
Geo Spatial base binary.
Geo Spatial base unary.
Return the first point of a LINESTRING geometry as a POINT.
Returns NULL if the input is not a LINESTRING.
Check if the inputs have at least one point in common but their interiors do not intersect.
Returns a transformed version of the given geometry into a new SRID.
Returns the pointwise union of the geometries in the column.
Returns the pointwise union of the two geometries.
Returns True if the geometry A is completely inside geometry B.
Return the X coordinate of the point, or NULL if not available.
Input must be a point
Returns X maxima of a bounding box 2d or 3d or a geometry.
Returns Y minima of a bounding box 2d or 3d or a geometry.
Return the Y coordinate of the point, or NULL if not available.
Input must be a point
Returns Y maxima of a bounding box 2d or 3d or a geometry.
Returns Y minima of a bounding box 2d or 3d or a geometry.
histograms
Operations for computing histograms.
| Name | Description |
|---|---|
| Bucket | Compute the bucket number of a numeric column. |
Compute the bucket number of a numeric column.
json
Operations for working with JSON data.
| Name | Description |
|---|---|
| JSONGetItem | Get a value from a JSON object or array. |
| ToJSONArray | Convert a value to an array of JSON objects. |
| ToJSONMap | Convert a value to a map of string to JSON. |
| UnwrapJSONBoolean | Unwrap a JSON bool into an engine-native bool. |
| UnwrapJSONFloat64 | Unwrap a JSON number into an engine-native float64. |
| UnwrapJSONInt64 | Unwrap a JSON number into an engine-native int64. |
| UnwrapJSONString | Unwrap a JSON string into an engine-native string. |
Get a value from a JSON object or array.
Convert a value to an array of JSON objects.
Convert a value to a map of string to JSON.
Unwrap a JSON bool into an engine-native bool.
Unwrap a JSON number into an engine-native float64.
Unwrap a JSON number into an engine-native int64.
Unwrap a JSON string into an engine-native string.
logical
Logical operations.
| Name | Description |
|---|---|
| And | Logical AND. |
| Between | Check if a value is within a range. |
| Comparison | Base class for comparison operations. |
| Equals | Equality comparison. |
| Greater | Greater than comparison. |
| GreaterEqual | Greater than or equal to comparison. |
| IdenticalTo | Identity comparison. Considers two NULL values equal. |
| IfElse | Ternary case expression. |
| InValues | Check if a value is in a set of values. |
| Less | Less than comparison. |
| LessEqual | Less than or equal to comparison. |
| LogicalBinary | Base class for logical binary operations. |
| Not | Logical negation. |
| NotEquals | Inequality comparison. |
| Or | Logical OR. |
| Xor | Logical XOR. |
Logical AND.
Check if a value is within a range.
Base class for comparison operations.
Equality comparison.
Greater than comparison.
Greater than or equal to comparison.
Identity comparison. Considers two NULL values equal.
Ternary case expression.
Equivalent to
Many backends implement this as a built-in function.
Check if a value is in a set of values.
Less than comparison.
Less than or equal to comparison.
Base class for logical binary operations.
Logical negation.
Inequality comparison.
Logical OR.
Logical XOR.
maps
Operations for working with maps.
| Name | Description |
|---|---|
| Map | Construct a map. |
| MapContains | Check if a map contains a key. |
| MapGet | Get a value from a map by key. |
| MapKeys | Get the keys of a map as an array. |
| MapLength | Compute the number of unique keys in a map. |
| MapMerge | Combine two maps into one. |
| MapValues | Get the values of a map as an array. |
Construct a map.
Check if a map contains a key.
Get a value from a map by key.
Get the keys of a map as an array.
Compute the number of unique keys in a map.
Combine two maps into one.
If a key is present in both maps, the value from the first is kept.
Get the values of a map as an array.
numeric
Operations for numeric expressions.
| Name | Description |
|---|---|
| Abs | Absolute value. |
| Acos | Returns the arc cosine of x. |
| Add | Add two values. |
| Asin | Returns the arc sine of x. |
| Atan | Returns the arc tangent of x. |
| Atan2 | Returns the arc tangent of x and y. |
| BaseConvert | Convert a number from one base to another. |
| BitwiseAnd | Bitwise AND operation. |
| BitwiseBinary | Base class for bitwise binary operations. |
| BitwiseLeftShift | Bitwise left shift operation. |
| BitwiseNot | Bitwise NOT operation. |
| BitwiseOr | Bitwise OR operation. |
| BitwiseRightShift | Bitwise right shift operation. |
| BitwiseXor | Bitwise XOR operation. |
| Ceil | Round up to the nearest integer value greater than or equal to this value. |
| Clip | Clip a value to a specified range. |
| Cos | Returns the cosine of x. |
| Cot | Returns the cotangent of x. |
| Degrees | Converts radians to degrees. |
| Divide | Divide the left value by the right value. |
| Exp | Exponential function. |
| Floor | Round down to the nearest integer value less than or equal to this value. |
| FloorDivide | Divide the left value by the right value and round down to the nearest integer. |
| IsInf | Check if the value is infinite. |
| IsNan | Check if the value is NaN. |
| Ln | Natural logarithm. |
| Log | Logarithm with a specific base. |
| Log10 | Logarithm base 10. |
| Log2 | Logarithm base 2. |
| Logarithm | Base class for logarithmic operations. |
| MathUnary | Base class for unary math operations. |
| Modulus | Return the remainder after the division of the left value by the right value. |
| Multiply | Multiply two values. |
| Negate | Negate the value. |
| Power | Raise the left value to the power of the right value. |
| Radians | Converts degrees to radians. |
| Round | Round a value. |
| Sign | Sign of the value. |
| Sin | Returns the sine of x. |
| Sqrt | Square root of the value. |
| Subtract | Subtract the right value from the left value. |
| Tan | Returns the tangent of x. |
| TrigonometricBinary | Trigonometric base binary. |
| TrigonometricUnary | Trigonometric base unary. |
Absolute value.
Returns the arc cosine of x.
Add two values.
Returns the arc sine of x.
Returns the arc tangent of x.
Returns the arc tangent of x and y.
Convert a number from one base to another.
Bitwise AND operation.
Base class for bitwise binary operations.
Bitwise left shift operation.
Bitwise NOT operation.
Bitwise OR operation.
Bitwise right shift operation.
Bitwise XOR operation.
Round up to the nearest integer value greater than or equal to this value.
Clip a value to a specified range.
Returns the cosine of x.
Returns the cotangent of x.
Converts radians to degrees.
Divide the left value by the right value.
Exponential function.
Round down to the nearest integer value less than or equal to this value.
Divide the left value by the right value and round down to the nearest integer.
Check if the value is infinite.
Check if the value is NaN.
Natural logarithm.
Logarithm with a specific base.
Logarithm base 10.
Logarithm base 2.
Base class for logarithmic operations.
Base class for unary math operations.
Return the remainder after the division of the left value by the right value.
Multiply two values.
Negate the value.
Raise the left value to the power of the right value.
Converts degrees to radians.
Round a value.
Sign of the value.
Returns the sine of x.
Square root of the value.
Subtract the right value from the left value.
Returns the tangent of x.
Trigonometric base binary.
Trigonometric base unary.
reductions
Reduction operations.
| Name | Description |
|---|---|
| All | Check if all values in a column are true. |
| Any | Check if any value in a column is true. |
| ApproxCountDistinct | Approximate number of unique values. |
| ApproxMedian | Compute the approximate median of a column. |
| ApproxMultiQuantile | Compute multiple approximate quantiles of a column. |
| ApproxQuantile | Compute the approximate quantile of a column. |
| Arbitrary | Retrieve an arbitrary element. |
| ArgMax | Compute the index of the maximum value in a column. |
| ArgMin | Compute the index of the minimum value in a column. |
| ArrayCollect | Collect values into an array. |
| BitAnd | Aggregate bitwise AND operation. |
| BitOr | Aggregate bitwise OR operation. |
| BitXor | Aggregate bitwise XOR operation. |
| Correlation | Correlation coefficient of two columns. |
| Count | Count the number of non-null elements of a column. |
| CountDistinct | Count the number of distinct values in a column. |
| CountDistinctStar | Count the number of distinct rows of a relation. |
| CountStar | Count the number of rows of a relation. |
| Covariance | Covariance of two columns. |
| First | Retrieve the first element. |
| GroupConcat | Concatenate strings in a group with a given separator character. |
| Last | Retrieve the last element. |
| Max | Compute the maximum of a column. |
| Mean | Compute the mean of a column. |
| Median | Compute the median of a column. |
| Min | Compute the minimum of a column. |
| Mode | Compute the mode of a column. |
| MultiQuantile | Compute multiple quantiles of a column. |
| Quantile | Compute the quantile of a column. |
| Reduction | Base class for reduction operations. |
| StandardDev | Compute the standard deviation of a column. |
| Sum | Compute the sum of a column. |
| Variance | Compute the variance of a column. |
| VarianceBase | Base class for variance and standard deviation. |
Check if all values in a column are true.
Check if any value in a column is true.
Approximate number of unique values.
Compute the approximate median of a column.
Compute multiple approximate quantiles of a column.
Compute the approximate quantile of a column.
Retrieve an arbitrary element.
Returns a non-null value unless the column is empty or all values are NULL.
Compute the index of the maximum value in a column.
Compute the index of the minimum value in a column.
Collect values into an array.
Aggregate bitwise AND operation.
All elements in an integer column are ANDed together.
This can be used to determine which bit flags are set on all elements.
Aggregate bitwise OR operation.
All elements in an integer column are ORed together. This can be used to determine which bit flags are set on any element.
Aggregate bitwise XOR operation.
All elements in an integer column are XORed together. This can be used as a parity checksum of element values.
Correlation coefficient of two columns.
Count the number of non-null elements of a column.
Count the number of distinct values in a column.
Count the number of distinct rows of a relation.
Count the number of rows of a relation.
Covariance of two columns.
Retrieve the first element.
Concatenate strings in a group with a given separator character.
Retrieve the last element.
Compute the maximum of a column.
Compute the mean of a column.
Compute the median of a column.
Compute the minimum of a column.
Compute the mode of a column.
Compute multiple quantiles of a column.
Compute the quantile of a column.
Base class for reduction operations.
Compute the standard deviation of a column.
Compute the sum of a column.
Compute the variance of a column.
Base class for variance and standard deviation.
relations
Relational operations.
| Name | Description |
|---|---|
| Aggregate | Aggregate a table by a set of group by columns and metrics. |
| DatabaseTable | A table that is bound to a specific backend. |
| Difference | Subtract one table from another. |
| Distinct | Compute the distinct rows of a table. |
| DropColumns | Drop columns from a relation. |
| DropNull | Drop null values in the table. |
| DummyTable | A table constructed from literal values. |
| Field | A field of a relation. |
| FillNull | Fill null values in the table. |
| Filter | Filter a table by a set of predicates. |
| InMemoryTable | A table whose data is stored in memory. |
| Intersection | Intersect two tables. |
| Limit | Limit and/or offset the number of records in a table. |
| Namespace | Object to model namespaces for tables. |
| PhysicalTable | Base class for tables with a name. |
| Project | Project a subset of columns from a relation. |
| Relation | Base class for relational operations. |
| SQLQueryResult | A table sourced from the result set of a SQL SELECT statement. |
| SQLStringView | A view created from a SQL string. |
| Sample | Sample performs random sampling of records in a table. |
| SelfReference | A self-referential relation. |
| Set | Base class for set operations. |
| Sort | Sort a table by a set of keys. |
| TableUnnest | Cross join unnest operation. |
| UnboundTable | A table that is not bound to a specific backend. |
| Union | Union two tables. |
| View | A view created from an expression. |
Aggregate a table by a set of group by columns and metrics.
A table that is bound to a specific backend.
Subtract one table from another.
Compute the distinct rows of a table.
Drop columns from a relation.
Drop null values in the table.
A table constructed from literal values.
A field of a relation.
Fill null values in the table.
Filter a table by a set of predicates.
A table whose data is stored in memory.
Intersect two tables.
Limit and/or offset the number of records in a table.
Object to model namespaces for tables.
Maps to the concept of database and/or catalog in SQL databases that support them.
Base class for tables with a name.
Project a subset of columns from a relation.
Base class for relational operations.
| Name | Description |
|---|---|
| fields | A mapping of column names to fields of the relation. |
| schema | The schema of the relation. |
| values | A mapping of column names to expressions which build up the relation. |
A table sourced from the result set of a SQL SELECT statement.
A view created from a SQL string.
Sample performs random sampling of records in a table.
A self-referential relation.
Base class for set operations.
Sort a table by a set of keys.
Cross join unnest operation.
A table that is not bound to a specific backend.
Union two tables.
A view created from an expression.
sortkeys
Sort key operations.
| Name | Description |
|---|---|
| SortKey | A sort key. |
A sort key.
strings
String operations.
| Name | Description |
|---|---|
| ArrayStringJoin | Join strings in an array with a separator. |
| Capitalize | Capitalize the first letter of a string. |
| EndsWith | Check if a string ends with another string. |
| ExtractAuthority | Extract the authority from a URL. |
| ExtractFile | Extract the file from a URL. |
| ExtractFragment | Extract the fragment from a URL. |
| ExtractHost | Extract the host from a URL. |
| ExtractPath | Extract the path from a URL. |
| ExtractProtocol | Extract the protocol from a URL. |
| ExtractQuery | Extract the query from a URL. |
| ExtractUserInfo | Extract the user info from a URL. |
| FindInSet | Find the position of a string in a list of comma-separated strings. |
| LPad | Pad a string on the left. |
| LStrip | Strip leading whitespace. |
| Levenshtein | Compute the Levenshtein distance between two strings. |
| Lowercase | Convert a string to lowercase. |
| RPad | Pad a string on the right. |
| RStrip | Strip trailing whitespace. |
| RegexExtract | Extract a substring from a string using a regular expression. |
| RegexReplace | Replace a substring in a string using a regular expression. |
| RegexSearch | Search a string with a regular expression. |
| RegexSplit | Split a string using a regular expression. |
| Repeat | Repeat a string. |
| Reverse | Reverse a string. |
| StartsWith | Check if a string starts with another string. |
| StrRight | Extract a substring starting from the right of a string. |
| StringAscii | Compute the ASCII code of the first character of a string. |
| StringConcat | Concatenate strings. |
| StringContains | Check if a string contains a substring. |
| StringFind | Find the position of a substring in a string. |
| StringJoin | Join strings with a separator. |
| StringLength | Compute the length of a string. |
| StringReplace | Replace a substring in a string with another string. |
| StringSQLILike | Case-insensitive SQL LIKE string match operation. |
| StringSQLLike | SQL LIKE string match operation. |
| StringSlice | Extract a substring from a string. |
| StringSplit | Split a string using a delimiter. |
| StringUnary | Base class for string operations accepting one argument. |
| Strip | Strip leading and trailing whitespace. |
| Substring | Extract a substring from a string. |
| Translate | Translate characters in a string. |
| Uppercase | Convert a string to uppercase. |
Join strings in an array with a separator.
Capitalize the first letter of a string.
Check if a string ends with another string.
Extract the authority from a URL.
Extract the file from a URL.
Extract the fragment from a URL.
Extract the host from a URL.
Extract the path from a URL.
Extract the protocol from a URL.
Extract the query from a URL.
Extract the user info from a URL.
Find the position of a string in a list of comma-separated strings.
Pad a string on the left.
Strip leading whitespace.
Compute the Levenshtein distance between two strings.
Convert a string to lowercase.
Pad a string on the right.
Strip trailing whitespace.
Extract a substring from a string using a regular expression.
Replace a substring in a string using a regular expression.
Search a string with a regular expression.
Split a string using a regular expression.
Repeat a string.
Reverse a string.
Check if a string starts with another string.
Extract a substring starting from the right of a string.
Compute the ASCII code of the first character of a string.
Concatenate strings.
Check if a string contains a substring.
Find the position of a substring in a string.
Join strings with a separator.
Compute the length of a string.
Replace a substring in a string with another string.
Case-insensitive SQL LIKE string match operation.
Similar to case-insensitive globbing.
SQL LIKE string match operation.
Similar to globbing.
Extract a substring from a string.
Split a string using a delimiter.
Base class for string operations accepting one argument.
Strip leading and trailing whitespace.
Extract a substring from a string.
Translate characters in a string.
Convert a string to uppercase.
structs
Operations for working with structs.
| Name | Description |
|---|---|
| StructColumn | Construct a struct column from literals or expressions. |
| StructField | Extract a field from a struct value. |
Construct a struct column from literals or expressions.
Extract a field from a struct value.
subqueries
Subquery operations.
| Name | Description |
|---|---|
| ExistsSubquery | Check if a subquery returns any rows. |
| InSubquery | Check if a value is in the result of a subquery. |
| ScalarSubquery | A subquery that returns a single scalar value. |
| Subquery | Base class for subquery operations. |
Check if a subquery returns any rows.
Check if a value is in the result of a subquery.
A subquery that returns a single scalar value.
Base class for subquery operations.
temporal
Temporal operations.
| Name | Description |
|---|---|
| BetweenTime | Check if a time is between two bounds. |
| Date | Extract the date from a timestamp. |
| DateAdd | Add an interval to a date. |
| DateDelta | Compute the difference between two dates as integer number of requested units. |
| DateDiff | Compute the difference between two dates. |
| DateFromYMD | Construct a date from year, month, and day. |
| DateSub | Subtract an interval from a date. |
| DateTruncate | Truncate a date to a specified unit. |
| DayOfWeekIndex | Extract the index of the day of the week from a date or timestamp. |
| DayOfWeekName | Extract the name of the day of the week from a date or timestamp. |
| ExtractDateField | Extract a field from a date. |
| ExtractDay | Extract the day from a date or timestamp. |
| ExtractDayOfYear | Extract the day of the year from a date or timestamp. |
| ExtractEpochSeconds | Extract seconds since the UNIX epoch from a date or timestamp. |
| ExtractHour | Extract the hour from a time or timestamp. |
| ExtractIsoYear | Extract the ISO year from a date or timestamp. |
| ExtractMicrosecond | Extract microseconds from a time or timestamp. |
| ExtractMillisecond | Extract milliseconds from a time or timestamp. |
| ExtractMinute | Extract the minute from a time or timestamp. |
| ExtractMonth | Extract the month from a date or timestamp. |
| ExtractQuarter | Extract the quarter from a date or timestamp. |
| ExtractSecond | Extract the second from a time or timestamp. |
| ExtractTemporalField | Extract a field from a temporal value. |
| ExtractTimeField | Extract a field from a time. |
| ExtractWeekOfYear | Extract the week of the year from a date or timestamp. |
| ExtractYear | Extract the year from a date or timestamp. |
| IntervalAdd | Add two intervals. |
| IntervalBinary | Base class for interval binary operations. |
| IntervalFloorDivide | Divide an interval by a scalar, rounding down. |
| IntervalFromInteger | Construct an interval from an integer. |
| IntervalMultiply | Multiply an interval by a scalar. |
| IntervalSubtract | Subtract one interval from another. |
| Strftime | Format a temporal value as a string. |
| StringToDate | Convert a string to a date. |
| StringToTime | Convert a string to a time. |
| StringToTimestamp | Convert a string to a timestamp. |
| TemporalDelta | Base class for temporal delta operations. |
| Time | Extract the time from a timestamp. |
| TimeAdd | Add an interval to a time. |
| TimeDelta | Compute the difference between two times as integer number of requested units. |
| TimeDiff | Compute the difference between two times. |
| TimeFromHMS | Construct a time from hours, minutes, and seconds. |
| TimeSub | Subtract an interval from a time. |
| TimeTruncate | Truncate a time to a specified unit. |
| TimestampAdd | Add an interval to a timestamp. |
| TimestampBucket | Bucketize a timestamp to a specified interval. |
| TimestampDelta | Compute the difference between two timestamps as integer number of requested units. |
| TimestampDiff | Compute the difference between two timestamps. |
| TimestampFromUNIX | Construct a timestamp from a UNIX timestamp. |
| TimestampFromYMDHMS | Construct a timestamp from components. |
| TimestampSub | Subtract an interval from a timestamp. |
| TimestampTruncate | Truncate a timestamp to a specified unit. |
Check if a time is between two bounds.
Extract the date from a timestamp.
Add an interval to a date.
Compute the difference between two dates as integer number of requested units.
Compute the difference between two dates.
Construct a date from year, month, and day.
Subtract an interval from a date.
Truncate a date to a specified unit.
Extract the index of the day of the week from a date or timestamp.
Extract the name of the day of the week from a date or timestamp.
Extract a field from a date.
Extract the day from a date or timestamp.
Extract the day of the year from a date or timestamp.
Extract seconds since the UNIX epoch from a date or timestamp.
Extract the hour from a time or timestamp.
Extract the ISO year from a date or timestamp.
Extract microseconds from a time or timestamp.
Extract milliseconds from a time or timestamp.
Extract the minute from a time or timestamp.
Extract the month from a date or timestamp.
Extract the quarter from a date or timestamp.
Extract the second from a time or timestamp.
Extract a field from a temporal value.
Extract a field from a time.
Extract the week of the year from a date or timestamp.
Extract the year from a date or timestamp.
Add two intervals.
Base class for interval binary operations.
Divide an interval by a scalar, rounding down.
Construct an interval from an integer.
Multiply an interval by a scalar.
Subtract one interval from another.
Format a temporal value as a string.
Convert a string to a date.
Convert a string to a time.
Convert a string to a timestamp.
Base class for temporal delta operations.
Extract the time from a timestamp.
Add an interval to a time.
Compute the difference between two times as integer number of requested units.
Compute the difference between two times.
Construct a time from hours, minutes, and seconds.
Subtract an interval from a time.
Truncate a time to a specified unit.
Add an interval to a timestamp.
Bucketize a timestamp to a specified interval.
Compute the difference between two timestamps as integer number of requested units.
Compute the difference between two timestamps.
Construct a timestamp from a UNIX timestamp.
Construct a timestamp from components.
Subtract an interval from a timestamp.
Truncate a timestamp to a specified unit.
temporal_windows
Temporal window operations.
udf
User-defined functions (UDFs) implementation.
| Name | Description |
|---|---|
| agg | Aggregate user-defined functions. |
| scalar | Scalar user-defined functions. |
Aggregate user-defined functions.
agg class itself is not a public API, its methods are.
| Name | Description |
|---|---|
| builtin | Construct an aggregate user-defined function that is built-in to the backend. |
Construct an aggregate user-defined function that is built-in to the backend.
| Name | Type | Description | Default |
|---|---|---|---|
| fn | The function to wrap. | None |
|
| name | The name of the UDF in the backend if different from the function name. | None |
|
| database | The database in which the builtin function resides. | None |
|
| catalog | The catalog in which the builtin function resides. | None |
|
| signature | If present, a tuple of the form ((arg0type, arg1type, ...), returntype). For example, a function taking an int and a float and returning a string would be ((int, float), str). If not present, the signature will be derived from the type annotations of the wrapped function. |
None |
|
| kwargs | Additional backend-specific configuration arguments for the UDF. | {} |
Scalar user-defined functions.
scalar class itself is not a public API, its methods are.
| Name | Description |
|---|---|
| builtin | Construct a scalar user-defined function that is built-in to the backend. |
| pandas | Construct a vectorized scalar user-defined function that accepts pandas Series’ as inputs. |
| pyarrow | Construct a vectorized scalar user-defined function that accepts PyArrow Arrays as input. |
| python | Construct a non-vectorized scalar user-defined function that accepts Python scalar values as inputs. |
Construct a scalar user-defined function that is built-in to the backend.
| Name | Type | Description | Default |
|---|---|---|---|
| fn | The function to wrap. | None |
|
| name | The name of the UDF in the backend if different from the function name. | None |
|
| database | The database in which the builtin function resides. | None |
|
| catalog | The catalog in which the builtin function resides. | None |
|
| signature | If present, a tuple of the form ((arg0type, arg1type, ...), returntype). For example, a function taking an int and a float and returning a string would be ((int, float), str). If not present, the signature will be derived from the type annotations of the wrapped function. For builtin UDFs, only the return type annotation is required. See the user guide for more information. |
None |
|
| kwargs | Additional backend-specific configuration arguments for the UDF. | {} |
Construct a vectorized scalar user-defined function that accepts pandas Series’ as inputs.
| Name | Type | Description | Default |
|---|---|---|---|
| fn | The function to wrap. | None |
|
| name | The name of the UDF in the backend if different from the function name. | None |
|
| database | The database in which to create the UDF. | None |
|
| catalog | The catalog in which to create the UDF. | None |
|
| signature | If present, a tuple of the form ((arg0type, arg1type, ...), returntype). For example, a function taking an int and a float and returning a string would be ((int, float), str). If not present, the signature will be derived from the type annotations of the wrapped function. |
None |
|
| kwargs | Additional backend-specific configuration arguments for the UDF. | {} |
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(dict(int_col=[1, 2, 3], str_col=["a", "b", "c"]))
>>> t
┏━━━━━━━━━┳━━━━━━━━━┓
┃ int_col ┃ str_col ┃
┡━━━━━━━━━╇━━━━━━━━━┩
│ int64 │ string │
├─────────┼─────────┤
│ 1 │ a │
│ 2 │ b │
│ 3 │ c │
└─────────┴─────────┘
>>> @ibis.udf.scalar.pandas
... def str_cap(x: str) -> str:
... # note usage of pandas `str` method
... return x.str.capitalize()
>>> str_cap(t.str_col) # doctest: +SKIP
┏━━━━━━━━━━━━━━━━━━━━━━━┓
┃ string_cap_0(str_col) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━┩
│ string │
├───────────────────────┤
│ A │
│ B │
│ C │
└───────────────────────┘Construct a vectorized scalar user-defined function that accepts PyArrow Arrays as input.
| Name | Type | Description | Default |
|---|---|---|---|
| fn | The function to wrap. | None |
|
| name | The name of the UDF in the backend if different from the function name. | None |
|
| database | The database in which to create the UDF. | None |
|
| catalog | The catalog in which to create the UDF. | None |
|
| signature | If present, a tuple of the form ((arg0type, arg1type, ...), returntype). For example, a function taking an int and a float and returning a string would be ((int, float), str). If not present, the signature will be derived from the type annotations of the wrapped function. |
None |
|
| kwargs | Additional backend-specific configuration arguments for the UDF. | {} |
>>> import ibis
>>> import pyarrow.compute as pc
>>> from datetime import date
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
... dict(start_col=[date(2024, 4, 29)], end_col=[date(2025, 4, 29)]),
... )
>>> @ibis.udf.scalar.pyarrow
... def weeks_between(start: date, end: date) -> int:
... return pc.weeks_between(start, end)
>>> weeks_between(t.start_col, t.end_col)┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓ ┃ weeks_between_0(start_col, end_col) ┃ ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩ │ int64 │ ├─────────────────────────────────────┤ │ 52 │ └─────────────────────────────────────┘
Construct a non-vectorized scalar user-defined function that accepts Python scalar values as inputs.
python UDFs are likely to be slow
| Name | Type | Description | Default |
|---|---|---|---|
| fn | The function to wrap. | None |
|
| name | The name of the UDF in the backend if different from the function name. | None |
|
| database | The database in which to create the UDF. | None |
|
| catalog | The catalog in which to create the UDF. | None |
|
| signature | If present, a tuple of the form ((arg0type, arg1type, ...), returntype). For example, a function taking an int and a float and returning a string would be ((int, float), str). If not present, the signature will be derived from the type annotations of the wrapped function. |
None |
|
| kwargs | Additional backend-specific configuration arguments for the UDF. | {} |
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(dict(int_col=[1, 2, 3], str_col=["a", "b", "c"]))
>>> t┏━━━━━━━━━┳━━━━━━━━━┓ ┃ int_col ┃ str_col ┃ ┡━━━━━━━━━╇━━━━━━━━━┩ │ int64 │ string │ ├─────────┼─────────┤ │ 1 │ a │ │ 2 │ b │ │ 3 │ c │ └─────────┴─────────┘
>>> @ibis.udf.scalar.python
... def str_magic(x: str) -> str:
... return f"{x}_magic"
>>> @ibis.udf.scalar.python
... def add_one_py(x: int) -> int:
... return x + 1
>>> str_magic(t.str_col)┏━━━━━━━━━━━━━━━━━━━━━━┓ ┃ str_magic_0(str_col) ┃ ┡━━━━━━━━━━━━━━━━━━━━━━┩ │ string │ ├──────────────────────┤ │ a_magic │ │ b_magic │ │ c_magic │ └──────────────────────┘
window
Window operations.
| Name | Description |
|---|---|
| WindowBoundary | Window boundary object. |
| WindowFunction | Window function operation. |
Window boundary object.
Window function operation.