Forum Discussion

sharp1FL's avatar
sharp1FL
Helper I
1 year ago
Solved

spark.sql() ArrayIndexOutOfBoundsException

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 )
data engineering
Notebook

4 Replies

  • v-prasare's avatar
    v-prasare
    Community Support
    1 year ago

    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

     

  • sharp1FL's avatar
    sharp1FL
    Helper I
    1 year ago

    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.

  • v-prasare's avatar
    v-prasare
    Community Support
    1 year ago

    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.