snowflake.snowpark.DataFrame.join_table_function¶

DataFrame.join_table_function(func: Union[str, List[str], TableFunctionCall], *func_arguments: Union[Column, str], **func_named_arguments: Union[Column, str]) → DataFrame[source] (https://github.com/snowflakedb/snowpark-python/blob/v1.16.0/src/snowflake/snowpark/dataframe.py#L2408-L2550)¶

Lateral joins the current DataFrame with the output of the specified table function.

References: Snowflake SQL functions.

Example 1

Lateral join a table function by using the name and parameters directly:

>>> df = session.sql("select 'James' as name, 'address1 address2 address3' as addresses")
>>> df.join_table_function("split_to_table", df["addresses"], lit(" ")).show()
--------------------------------------------------------------------
|"NAME"  |"ADDRESSES"                 |"SEQ"  |"INDEX"  |"VALUE"   |
--------------------------------------------------------------------
|James   |address1 address2 address3  |1      |1        |address1  |
|James   |address1 address2 address3  |1      |2        |address2  |
|James   |address1 address2 address3  |1      |3        |address3  |
--------------------------------------------------------------------

Copy

Example 2

Lateral join a table function by calling:

>>> from snowflake.snowpark.functions import table_function
>>> split_to_table = table_function("split_to_table")
>>> df = session.sql("select 'James' as name, 'address1 address2 address3' as addresses")
>>> df.join_table_function(split_to_table(df["addresses"], lit(" "))).show()
--------------------------------------------------------------------
|"NAME"  |"ADDRESSES"                 |"SEQ"  |"INDEX"  |"VALUE"   |
--------------------------------------------------------------------
|James   |address1 address2 address3  |1      |1        |address1  |
|James   |address1 address2 address3  |1      |2        |address2  |
|James   |address1 address2 address3  |1      |3        |address3  |
--------------------------------------------------------------------

Copy

Example 3

Lateral join a table function with the partition and order by clause:

>>> from snowflake.snowpark.functions import table_function
>>> split_to_table = table_function("split_to_table")
>>> df = session.create_dataframe([
...     ["John", "James", "address1 address2 address3"],
...     ["Mike", "James", "address4 address5 address6"],
...     ["Cathy", "Stone", "address4 address5 address6"],
... ],
... schema=["first_name", "last_name", "addresses"])
>>> df.join_table_function(split_to_table(df["addresses"], lit(" ")).over(partition_by="last_name", order_by="first_name")).show()
----------------------------------------------------------------------------------------
|"FIRST_NAME"  |"LAST_NAME"  |"ADDRESSES"                 |"SEQ"  |"INDEX"  |"VALUE"   |
----------------------------------------------------------------------------------------
|John          |James        |address1 address2 address3  |1      |1        |address1  |
|John          |James        |address1 address2 address3  |1      |2        |address2  |
|John          |James        |address1 address2 address3  |1      |3        |address3  |
|Mike          |James        |address4 address5 address6  |2      |1        |address4  |
|Mike          |James        |address4 address5 address6  |2      |2        |address5  |
|Mike          |James        |address4 address5 address6  |2      |3        |address6  |
|Cathy         |Stone        |address4 address5 address6  |3      |1        |address4  |
|Cathy         |Stone        |address4 address5 address6  |3      |2        |address5  |
|Cathy         |Stone        |address4 address5 address6  |3      |3        |address6  |
----------------------------------------------------------------------------------------

Copy

Example 4

Lateral join a table function with aliasing the output column names:

>>> from snowflake.snowpark.functions import table_function
>>> split_to_table = table_function("split_to_table")
>>> df = session.sql("select 'James' as name, 'address1 address2 address3' as addresses")
>>> df.join_table_function(split_to_table(col("addresses"), lit(" ")).alias("seq", "idx", "val")).show()
------------------------------------------------------------------
|"NAME"  |"ADDRESSES"                 |"SEQ"  |"IDX"  |"VAL"     |
------------------------------------------------------------------
|James   |address1 address2 address3  |1      |1      |address1  |
|James   |address1 address2 address3  |1      |2      |address2  |
|James   |address1 address2 address3  |1      |3      |address3  |
------------------------------------------------------------------

Copy

Parameters:

func_name – The SQL function name.
func_arguments – The positional arguments for the SQL function.
func_named_arguments – The named arguments for the SQL function, if it accepts named arguments.

Returns:

A new DataFrame that has the columns carried from this DataFrame, plus new columns and rows from the lateral join with the table function.