[VL] Unsupported spark function list [please leave a comment if you plan to pick some]

[PHILO-HE]

Description

Here listed spark functions still not supported by Gluten Velox backend. Please leave a comment if you'd like to pick some. In the below list, [√] means someone is working in progress for the corresponding function.
You can find all functions' support status from this gluten doc.

To avoid duplicate work, before starting, please check whether a PR has been submitted in Velox community or whether it has already been implemented in Velox who holds most sql functions in its sparksql folder & prestosql folder.

Reference:

• spark sql expression
• spark built-in functions

---

• percentile_approx/approx_percentile (WIP, guangxin)
• concat_ws (PR ready, Add concat_ws Spark function facebookincubator/velox#8854)
• unix_timestamp: "Only supports string type, with session timezone considered, todo: support date type"
• locate
• parse_url (PR drafted, not merged)
• urldecoder: "UDF, supported by spark as a built-in function since 3.4.0."
• normalizenanandzero
• arrayintersects
• default.json_split (udf, no need to impl.): "external UDF"
• parsejsonarray: "external UDF"
• struct
• percentile (@Yohahaha)
• first/first_value (@JkSelf)
• last/last_value (@JkSelf)
• posexplode (WIP, @marin-ma)
• trunc (WIP, HannanKan)
• months_between (PR ready)
• date_trunc (WIP, HannanKan)
• stack
• grouping_id
• printf (@Surbhi-Vijay)
• space (WIP, rhh777)
• inline (WIP, @marin-ma)
• to_unix_timestamp: "Only supports string type, with session timezone considered. todo: support date type"
• from_csv
• from_json
• json_object_keys
• json_tuple
• schema_of_csv
• schema_of_json
• to_csv
• to_json (Suppose workable with folly function used)
• make_ym_interval (WIP, @marin-ma)
• make_timestamp (WIP, @marin-ma)
• make_interval
• make_dt_interval
• from_utc_timestamp (@acvictor)
• extract
• exists (@lyy-pineapple)
• date_part
• zip_with
• transform (@Yohahaha)
• transform_keys
• transform_values
• map_from_entries (WIP, MaYan)
• map_filter (WIP, MaYan)
• map_entries (Done, by MaYan)
• map_concat
• forall (@lyy-pineapple)
• flatten (@ivoson)
• filter
• filter (array) (@ivoson)
• width_bucket
• array_sort (may just map to a velox function)
• xpath
• xpath_boolean
• xpath_double
• xpath_float
• xpath_int
• xpath_long
• xpath_number
• xpath_short
• xpath_string
• unbase64 (WIP, @fyp711)
• decode (partially supported if translated to caseWhen. WIP Cody)
• initcap (WIP, velox PR: 8676)
• unix_date (velox PR 8725, completed)
• count_min_sketch
• bool_and/every (@mskapilks)
• bool_or/any/some (@mskapilks)
• shuffle (completed)
• bround (@xumingming)
• format_string (@gaoyangxiaozhu)
• format_number (@gaoyangxiaozhu)
• soundex (@zhli1142015)
• levenshtein (@zhli1142015)
• cot (@honeyhexin)
• expm1 (@Donvi)
• stack (generator function, @xumingming)

• Since Spark-3.3 (related to ML, low priority)

• regr_count
• regr_avgx
• regr_avgy
• regr_r2
• regr_sxx
• regr_sxy
• regr_syy
• regr_slope
• regr_intercept

• Since Spark-3.3

• array_size (@acvictor)

• Since Spark-3.4

• mode
• get (@Yohahaha)
• array_append (@ivoson)
• array_insert (@ivoson)

[Yohahaha]

I'd like support hex and unhex.

update: hex and unhex has already supported in Gluten.

[zwangsheng]

Hi i'd like to give a try with hour function.

[konjac]

Hi, I'd like to have a look into map_keys

[fyp711]

Hi I'd like to support find_in_set in velox

[HannanKan]

Hi, I'd like to support date_trunc/trunc.

[JkSelf]

Hi, I'd like to support dense_rank.

[JkSelf]

dense_rank already supported in velox facebookincubator/velox#6289.

[zhztheplayer]

• percentile_approx

• approx_percentile: "Third argument accuracy is different with velox, velox is double but spark is long"

The two stand for the same function I assume? I'll take these two if nobody is working on it.

[PHILO-HE]

• percentile_approx

• approx_percentile: "Third argument accuracy is different with velox, velox is double but spark is long"

The two stand for the same function I assume? I'll take these two if nobody is working on it.

Yes, they are one thing. Just unify them into one checkbox. Thanks!

[JkSelf]

I will take a look ntile window function.

[zhouyuan]

ubase64:
#4482

[zjuwangg]

Is there any plan to suppport from_json function?

[yma11]

I'd like take map_entries and map_from_entries, there are already presto implementation in velox, will need check consistency .

[acvictor]

I'd like to give date_from_unix_date a shot

[PHILO-HE]

Just removed the below functions from the list, since they have been supported. Thanks! @acvictor, @Yohahaha, @fyp711, @zwangsheng, @JkSelf, etc.

to_date hour mod pow ifnull add_months next_day dense_rank find_in_set hex ntile
date_from_unix_date array_repeat array_position array_except array_distinct weekday
year month day

[acvictor]

@PHILO-HE I see support for year, month, day, last_day in Velox too. I can also give from_utc_timestamp a go.

[Surbhi-Vijay]

nullif is out of the box supported. Spark send the converted expression as If expression and it is supported in Gluten.

[PHILO-HE]

nullif is out of the box supported. Spark send the converted expression as If expression and it is supported in Gluten.

Thanks so much for your feedback! Just removed it from the list.

[acvictor]

@PHILO-HE I see support for year, month, day, last_day in Velox too. I can also give from_utc_timestamp a go.

Will do minute as well.

[rui-mo]

I'd like to work on locate and arrayintersect.

[mskapilks]

I would like to work on bool_and, bool_or

[zhztheplayer]

• collect_list (velox supported, needs Gluten to enable array for project plan node)
• collect_set

@PHILO-HE Should we uncheck these two? I ran a test and the two functions are both fallen back (in 3.3).

[Surbhi-Vijay]

I would like to give printf a try.

[Yohahaha]

I'd like take get function, as known as GetArrayItem.

[Yohahaha]

I'd like take transform function.

[lyy-pineapple]

@PHILO-HE hello, I'd like take forall function.

[ivoson]

I'd like to take flatten function.

[acvictor]

I'd like to try array_size.

[lyy-pineapple]

@PHILO-HE hello, I'd like take forall function.

and exists(array) also support

[gaoyangxiaozhu]

hey @zhouyuan could you help add format_string and format_number in the list and I would take format_string and format_number later

[PHILO-HE]

hey @zhouyuan could you help add format_string and format_number in the list and I would take format_string and format_number later

@gaoyangxiaozhu, just added them into the list. Thanks!

[zhli1142015]

I'd like to take soundex and levenshtein, thanks.

[honeyhexin]

I'd like to take cot, thanks.

[Donvi]

I'd like and am working in the math function expm1.

[gaoyangxiaozhu]

PR for width_bucket support, #5634 looks still need velox side change for to support case as bucket_number <=0, will send PR in velox repository to fix

[ivoson]

I'd like to implement array_append and array_insert for spark 3.4+

[xumingming]

I'd like to take a look at stack function, it seems like a Generator, meaning one row of input might return multiple rows of output, does Velox has this generator ability?

[marin-ma]

I'd like to take a look at stack function, it seems like a Generator, meaning one row of input might return multiple rows of output, does Velox has this generator ability?

@xumingming Currently, 4 generator functions are supported : explode, pos_explode, inline and json_tuple. The approach is creating a ProjectNode + UnnestNode + ProjectNode pattern in Velox pipeline. But seems like the stack function cannot use this pattern. Perhaps we can build another pipeline by leveraging the ExpandNode in Velox (Not sure if this approach really works).

[xumingming]

@marin-ma Thanks for the advice, I will take a look.

[NEUpanning]

I'd like to take unix_date, thanks.

[PHILO-HE]

I'd like to take unix_date, thanks.

@NEUpanning, we have supported it in both Gluten & Velox. Just changed its state in the list. Thanks!
#5287
facebookincubator/velox#8725

[NEUpanning]

@PHILO-HE Thanks for your feedback. So i'd like to take date_part. Is to_date supported in gluten now? It doesn't shows in the list. I also would like to pick it.

[PHILO-HE]

@PHILO-HE Thanks for your feedback. So i'd like to take date_part. Is to_date supported in gluten now? It doesn't shows in the list. I also would like to pick it.

@NEUpanning, this list only maintains working-in-progress functions. I think to_date has been supported. See https://github.com/apache/incubator-gluten/blob/main/docs/velox-backend-support-progress.md.

date_part may be supported also. I note the below test in Gluten. You can confirm whether all date patterns have been supported.

incubator-gluten/backends-velox/src/test/scala/org/apache/gluten/execution/ScalarFunctionsValidateSuite.scala

Line 808 in d74fc97

"SELECT date_part('yearofweek', dt), extract(yearofweek from dt)" +

[NEUpanning]

@PHILO-HE Thanks for your feedback. So i'd like to take date_part. Is to_date supported in gluten now? It doesn't shows in the list. I also would like to pick it.

@NEUpanning, this list only maintains working-in-progress functions. I think to_date has been supported. See https://github.com/apache/incubator-gluten/blob/main/docs/velox-backend-support-progress.md.

date_part may be supported also. I note the below test in Gluten. You can confirm whether all date patterns have been supported.

incubator-gluten/backends-velox/src/test/scala/org/apache/gluten/execution/ScalarFunctionsValidateSuite.scala

Line 808 in d74fc97

"SELECT date_part('yearofweek', dt), extract(yearofweek from dt)" +

I can't find any implementation of date_part and to_date function in Velox. Would you like to help me find it? Thanks.

[xumingming]

shuffle, array_sort are already supported, can be marked as complete.

[xumingming]

I will take a look at bround.

[PHILO-HE]

@PHILO-HE Thanks for your feedback. So i'd like to take date_part. Is to_date supported in gluten now? It doesn't shows in the list. I also would like to pick it.

@NEUpanning, this list only maintains working-in-progress functions. I think to_date has been supported. See https://github.com/apache/incubator-gluten/blob/main/docs/velox-backend-support-progress.md.
date_part may be supported also. I note the below test in Gluten. You can confirm whether all date patterns have been supported.

incubator-gluten/backends-velox/src/test/scala/org/apache/gluten/execution/ScalarFunctionsValidateSuite.scala

Line 808 in d74fc97

"SELECT date_part('yearofweek', dt), extract(yearofweek from dt)" +

I can't find any implementation of date_part and to_date function in Velox. Would you like to help me find it? Thanks.

@NEUpanning, not a direct replacement. date_part is covered here. to_date is converted to Cast + GetTimestamp by Spark.

[PHILO-HE]

shuffle, array_sort are already supported, can be marked as complete.

@xumingming, seems sort_array is supported, but array_sort is not. Please spare some time to confirm. Thanks!