Support for posexplode

[djouallah]

Trying to port this script from Spark to sqlframe, and I am getting this error

https://colab.research.google.com/drive/15CK2fB3G1fmB04dEZOcT-F2kuYRoSBpH?usp=sharing#scrollTo=Basev2-ohQAP

WARNING:sqlframe.base.session:numPartitions is not supported
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
[<ipython-input-4-04b5098af251>](https://localhost:8080/#) in <cell line: 0>()
     69     base_df
     70     .withColumn("line_array", F.expr("sequence(1, Num_Lines)"))
---> 71     .select("*", F.posexplode("line_array").alias("Line_Pos", "Line_Val"))
     72     .drop("line_array", "Num_Lines")
     73     .withColumn("Order_Line_ID", F.concat(F.col("Order_ID"), F.lit("_"), F.col("Line_Val")))

AttributeError: module 'sqlframe.duckdb.functions' has no attribute 'posexplode'

[eakmanrq]

Sorry for the delay I have been looking into this. The first problem is that duckdb doesn't natively support posexplode. You can see a workaround here: https://stackoverflow.com/questions/78635682/duckdb-support-for-postgres-function-with-ordinality

The second problem is posexplode is special in the PySpark API in that it technically returns 1 column but really it is multiple columns. So the workaround for DuckDB is actually two columns but this breaks the API since it only expects one. Therefore to support this I need to add the ability for a function to return multiple columns which doesn't really match the PySpark API.

[paultiq]

duckdb can return a struct, and one shortcut is to use the table name to get a struct containing all the data.

Example:

select t from (select
        unnest(a.data) as col, generate_subscripts(a.data, 1) as pos
    from values (
        string_to_array('hello@world', '@')
    ) as a(data)
) as t

This returns:

t
{'col': 'hello', 'pos': 1}
{'col': 'world', 'pos': 2}

Then, if you unnest(t), you'd get:

col	pos
hello	1
world	2