Databricks (Spark) の SQL にて Array の値から範囲外の値を取得しようとした際のエラーへの対応方法

概要

Databricks にて SQL の SPLIT 関数で生成された配列に対して、範囲外のインデックスを指定した場合に発生した下記エラーへの対応方法を共有します。本エラーは、"spark.sql.ansi.enabled"の設定が"True"の場合に発生します。"spark.sql.ansi.enabled"の設定が"False"の場合と同様の対応を実施するには、 Get 関数を利用すればいいようです。

[INVALID_ARRAY_INDEX] The index 1 is out of bounds. The array has 1 elements. Use the SQL function get() to tolerate accessing element at invalid index and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. SQLSTATE: 22003

エラーについてドキュメントにて言及されています。

array_col[index]: 無効なインデックスを使用している場合、この演算子は ArrayIndexOutOfBoundsException をスローします。

引用元：Databricks Runtime での ANSI 準拠 - Azure Databricks - Databricks SQL | Microsoft Learn

"spark.sql.ansi.enabled"の設定が"False"の場合の動作確認

spark.conf.set("spark.sql.ansi.enabled", "False")

%sql
WITH src AS (
  SELECT 
    "-1" as  col_1
)
SELECT
  *,
  SPLIT(`col_1`, "[\\.]")[1]
  FROM
    src

エラーの再現

spark.conf.set("spark.sql.ansi.enabled", "True")

%sql
WITH src AS (
  SELECT 
    "-1" as  col_1
)
SELECT
  *,
  SPLIT(`col_1`, "[\\.]")[1]
  FROM
    src

[INVALID_ARRAY_INDEX] The index 1 is out of bounds. The array has 1 elements. Use the SQL function get() to tolerate accessing element at invalid index and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. SQLSTATE: 22003

エラーへの対応方法

%sql
WITH src AS (
  SELECT 
    "-1" as  col_1
)
SELECT
  *,
  get(SPLIT(`col_1`, "[\\.]"), 1)
  FROM
    src