Filter rows and explode arrays in Databricks

1.Filtering rows with filter and where

Adding, renaming and dropping change a table's columns. Filtering and exploding change its rows: a filter removes rows, and an explode can multiply them. filter(condition) "Filters rows using the given condition" and returns a new DataFrame containing only the rows that satisfy it. The condition can take either of two forms: "A Column of BooleanType or a string of SQL expressions." So df.filter(df.age > 3) and df.filter("age > 3") return the same rows. The same reference page also demonstrates where, which takes the same kind of condition.

df.where(df.age == 2).show()
# +---+-----+-------+
# |age| name|subject|
# +---+-----+-------+
# |  2|Alice|   Math|
# +---+-----+-------+

df.filter("age > 3").show()
# +---+-------+---------+
# |age|   name|  subject|
# +---+-------+---------+
# |  5|    Bob|  Physics|
# |  7|Charlie|Chemistry|
# +---+-------+---------+

To combine Column conditions, the documented example wraps each comparison in parentheses and joins them with the & operator. It keeps only rows where age is over 3 and the subject is Physics, which leaves just Bob. Because the string form accepts SQL expressions, the whole condition can also be written as one SQL string.

df.filter((df.age > 3)  ?  (df.subject == "Physics")).show()

Sources1

2.Exploding array and map columns into rows

Nested data often arrives as an array column, for example one row per order with an array of item IDs. explode un-nests that array into rows. Take a DataFrame with three rows: (1, [1, 2, 3, NULL]), (2, []) and (3, NULL).

df.select('*', sf.explode('a')).show()

+---+---------------+----+
|  i|              a| col|
+---+---------------+----+
|  1|[1, 2, 3, NULL]|   1|
|  1|[1, 2, 3, NULL]|   2|
|  1|[1, 2, 3, NULL]|   3|
|  1|[1, 2, 3, NULL]|NULL|
+---+---------------+----+

explode "Returns a new row for each element in the given array or map." The other columns are repeated on every output row, and the new values go into a column named col unless you alias it. Rows whose array is empty or NULL have no elements, so they produce no output rows. If you count rows before and after an explode, those parents disappear without any error. A map column works the same way, except that each entry becomes a row with two columns, key and value. You can rename both at once by passing two names to alias. The SQL reference also lists related generator functions: explode_outer, posexplode, posexplode_outer, inline and inline_outer.

Sources234

3.explode_outer and the SQL table-reference form

When parent rows must survive even if their array is NULL, use explode_outer. The SQL reference says: "To return a single row with NULLs for the array or map values use the explode_outer() function." The two queries below differ only in the function name.

> SELECT explode_outer(c1) AS elem, 'Spark' FROM VALUES(array(10, 20)), (null) AS T(c1);
 10   Spark
 20   Spark
 NULL Spark

> SELECT explode(c1) AS elem, 'Spark' FROM VALUES(array(10, 20)), (null) AS T(c1);
 10 Spark
 20 Spark

Those examples call explode in the SELECT list, which is the older style. From Databricks Runtime 12.2 LTS and in Databricks SQL, calling it from the SELECT list or a LATERAL VIEW clause is deprecated. Instead, invoke it as a table reference in the FROM clause and name its output column with a table alias.

> SELECT elem, 'Spark' FROM explode(array(10, 20)) AS t(elem);
 10 Spark
 20 Spark

Sources4

4.Exploding several arrays and arrays of structs

Some tables have two array columns to un-nest. The PySpark reference sets a hard rule: "Only one explode is allowed per SELECT clause." In the older SQL-list style, a second generator in the same SELECT list raises UNSUPPORTED_GENERATOR.MULTI_GENERATOR. In PySpark, the workaround is to explode one array per select and chain the selects. Every element of the first array is paired with every element of the second, so a 2-element array and a 3-element array produce six rows. In SQL from 12.2 LTS, two table-reference explodes in the FROM clause produce the same kind of pairing.

df = spark.sql('SELECT ARRAY(1,2) AS a1, ARRAY(3,4,5) AS a2')
df.select(
    '*', sf.explode('a1').alias('v1')
).select('*', sf.explode('a2').alias('v2')).show()

+------+---------+---+---+
|    a1|       a2| v1| v2|
+------+---------+---+---+
|[1, 2]|[3, 4, 5]|  1|  3|
|[1, 2]|[3, 4, 5]|  1|  4|
|[1, 2]|[3, 4, 5]|  1|  5|
|[1, 2]|[3, 4, 5]|  2|  3|
|[1, 2]|[3, 4, 5]|  2|  4|
|[1, 2]|[3, 4, 5]|  2|  5|
+------+---------+---+---+

JSON sources often produce arrays of structs. Exploding one gives a single struct column. Selecting s.* then expands the struct's fields into top-level columns, because the star operator selects all the fields within a given field. Together, these two steps turn an array of records into an ordinary table.

df = spark.sql('SELECT ARRAY(NAMED_STRUCT("a",1,"b",2), NAMED_STRUCT("a",3,"b",4)) AS a')
df.select(sf.explode('a').alias("s")).select("s.*").show()

+---+---+
|  a|  b|
+---+---+
|  1|  2|
|  3|  4|
+---+---+

One recall item from page 1, since a flattened struct often leaves a column you no longer need: drop has two behaviours that are easy to get wrong.

Sources2435

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.explode keeps every parent row, and a NULL array just produces a row with a NULL value.Why is that wrong?

   explode produces no rows for a NULL collection, so the parent row disappears. Use explode_outer to keep a single row with NULLs.

   Covered in explode_outer and the SQL table-reference form

2. 2.You can explode two array columns in the same select to un-nest them in one step.Why is that wrong?

   Only one explode is allowed per SELECT clause. Chain one select per explode, which pairs every element of one array with every element of the other.

   Covered in Exploding several arrays and arrays of structs