topic Re: spark.sql() ArrayIndexOutOfBoundsException in Data Engineering

spark.sql() ArrayIndexOutOfBoundsException

sharp1FL — Tue, 08 Apr 2025 18:56:37 GMT

I have the following Pyspark code that reads a table schema and formats a MERGE statement to perform an upsert. This works for all of the 30 or so required tables except for 1. On one particular table, I get java.lang.ArrayIndexOutOfBoundsException.

We are on Runtime v 1.3

How can I troubleshoot or correct this issue?

Thanks.

# Get column names from TEMP_TABLE

temp_cols = [f.name for f in df_clean.schema.fields]

# Dynamically build MERGE statement

merge_sql = f"""

MERGE INTO {target_table} AS t

USING {temp_table} AS s

ON t.{primary_key_col} = s.{primary_key_col} -- join on primary key

WHEN MATCHED THEN UPDATE SET

"""

first_col = 0

for col in temp_cols[0:]: # Skip the first column used in the join condition

if (col != primary_key_col 😞

if( first_col == 0) :

first_col = 1

else:

merge_sql += f"""

, """

merge_sql += f"""t.{col} = s.{col}"""

merge_sql += f"""

WHEN NOT MATCHED THEN INSERT ({', '.join(temp_cols)})

VALUES ({', '.join(['s.' + col for col in temp_cols])})

"""

print(merge_sql )

merged_df = spark.sql( merge_sql )

display( merged_df )

Re: spark.sql() ArrayIndexOutOfBoundsException

v-prasare — Wed, 09 Apr 2025 11:42:39 GMT

Hi @sharp1FL,

Print and Review Generated SQL
Use print(merge_sql) to manually check for SQL syntax errors or invalid column references.
Fix Code Syntax Error & Wrap Column Names in Backticks
Handle special characters: t. \{col}` = s.`{col}``
Avoid Complex/Nested Types: MERGE fails with arrays/structs—check using df_clean.printSchema()
Check for Duplicate Columns: Ensure column names are unique and clean.
Validate Runtime Compatibility

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

Re: spark.sql() ArrayIndexOutOfBoundsException

sharp1FL — Wed, 09 Apr 2025 15:36:53 GMT

Thanks for the reply. I reviewed generated merge_sql. There are no complex data types or syntax errors. I removed each column to try an isolate an issue, to no effect on the error.

Re: spark.sql() ArrayIndexOutOfBoundsException

v-prasare — Mon, 21 Apr 2025 06:12:35 GMT

Hi @sharp1FL ,

In this scenario i suggest you to raise a support ticket here. so, that they can assit you in addressing the issue you are facing. please follow below link on how to raise a support ticket:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thanks,

Prashanth

Re: spark.sql() ArrayIndexOutOfBoundsException

v-prasare — Fri, 30 May 2025 04:31:02 GMT

Hi, We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.