Expressions¶

In DataFusion an expression is an abstraction that represents a computation. Expressions are used as the primary inputs and outputs for most functions within DataFusion. As such, expressions can be combined to create expression trees, a concept shared across most compilers and databases.

Column¶

The first expression most new users will interact with is the Column, which is created by calling col(). This expression represents a column within a DataFrame. The function col() takes as in input a string and returns an expression as it’s output.

Literal¶

Literal expressions represent a single value. These are helpful in a wide range of operations where a specific, known value is of interest. You can create a literal expression using the function lit(). The type of the object passed to the lit() function will be used to convert it to a known data type.

In the following example we create expressions for the column named color and the literal scalar string red. The resultant variable red_units is itself also an expression.

In [1]: red_units = col("color") == lit("red")

Boolean¶

When combining expressions that evaluate to a boolean value, you can combine these expressions using boolean operators. It is important to note that in order to combine these expressions, you must use bitwise operators. See the following examples for the and, or, and not operations.

In [2]: red_or_green_units = (col("color") == lit("red")) | (col("color") == lit("green"))

In [3]: heavy_red_units = (col("color") == lit("red")) & (col("weight") > lit(42))

In [4]: not_red_units = ~(col("color") == lit("red"))

Arrays¶

For columns that contain arrays of values, you can access individual elements of the array by index using bracket indexing. This is similar to callling the function datafusion.functions.array_element(), except that array indexing using brackets is 0 based, similar to Python arrays and array_element is 1 based indexing to be compatible with other SQL approaches.

In [5]: from datafusion import SessionContext, col

In [6]: ctx = SessionContext()

In [7]: df = ctx.from_pydict({"a": [[1, 2, 3], [4, 5, 6]]})

In [8]: df.select(col("a")[0].alias("a0"))
Out[8]: 
DataFrame()
+----+
| a0 |
+----+
| 1  |
| 4  |
+----+

Warning

Indexing an element of an array via [] starts at index 0 whereas array_element() starts at index 1.

Structs¶

Columns that contain struct elements can be accessed using the bracket notation as if they were Python dictionary style objects. This expects a string key as the parameter passed.

In [9]: ctx = SessionContext()

In [10]: data = {"a": [{"size": 15, "color": "green"}, {"size": 10, "color": "blue"}]}

In [11]: df = ctx.from_pydict(data)

In [12]: df.select(col("a")["size"].alias("a_size"))
Out[12]: 
DataFrame()
+--------+
| a_size |
+--------+
| 15     |
| 10     |
+--------+

Functions¶

As mentioned before, most functions in DataFusion return an expression at their output. This allows us to create a wide variety of expressions built up from other expressions. For example, alias() is a function that takes as it input a single expression and returns an expression in which the name of the expression has changed.

The following example shows a series of expressions that are built up from functions operating on expressions.

In [13]: from datafusion import SessionContext

In [14]: from datafusion import column, lit

In [15]: from datafusion import functions as f

In [16]: import random

In [17]: ctx = SessionContext()

In [18]: df = ctx.from_pydict(
   ....:     {
   ....:         "name": ["Albert", "Becca", "Carlos", "Dante"],
   ....:         "age": [42, 67, 27, 71],
   ....:         "years_in_position": [13, 21, 10, 54],
   ....:     },
   ....:     name="employees"
   ....: )
   ....: 

In [19]: age_col = col("age")

In [20]: renamed_age = age_col.alias("age_in_years")

In [21]: start_age = age_col - col("years_in_position")

In [22]: started_young = start_age < lit(18)

In [23]: can_retire = age_col > lit(65)

In [24]: long_timer = started_young & can_retire

In [25]: df.filter(long_timer).select(col("name"), renamed_age, col("years_in_position"))
Out[25]: 
DataFrame()
+-------+--------------+-------------------+
| name  | age_in_years | years_in_position |
+-------+--------------+-------------------+
| Dante | 71           | 54                |
+-------+--------------+-------------------+

Column Selections

Joins