【Snowflake】【トラブル】エラー「Failed to cast variant value "" to XXX」時の対応

Snowflake トラブル

■ はじめに

https://dk521123.hatenablog.com/entry/2021/12/15/162658

で少し触れているのだが、
エラー「Failed to cast variant value "" to TIMESTAMP_NTZ」などの
例外が発生したので、その際に行った対応策をメモしておく。

目次

【１】現象詳細
　１）スクリプト・イメージ
　２）試してダメだったこと
【２】エラー内容
【３】原因
【４】対応案
　１）スクリプト・イメージ（修正版）
　２）対応案に関する補足説明

【１】現象詳細

　S3上にあるParquetファイルを入力として、
ステージに取り込んで、Snowflakeにインポートするために、
以下の「スクリプト・イメージ」のようなスクリプトを組んだが
後述の「【２】エラー内容」で例外が発生してしまった。

１）スクリプト・イメージ

-- 使用するロール/ウェアハウスを指定
USE ROLE sysadmin;
USE WAREHOUSE compute_wh;

-- 専用のDB/Schemaを作成
CREATE DATABASE demo_db;
CREATE SCHEMA demo_schema;

-- 使用するDB/Schemaを指定
USE DATABASE demo_db;
USE SCHEMA demo_schema;

-- データロード用のテーブルを用意
CREATE TABLE demo_db.demo_schema.demo_user_table IF NOT EXISTS
(
  user_id integer not null,
  user_name varchar not null,
  age number, -- NULL許容
  start_time timestamp -- NULL許容
);

-- ロード用のステージを作成
CREATE OR REPLACE TEMPORARY STAGE demo_db.demo_schema.demo_stage
  URL = 's3://your-s3-bucket-name/demo_warehouse/demo_database/demo_user_table/'
  STORAGE_INTEGRATION = xxxxx
  FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY)
;

-- データロード
COPY INTO demo_db.demo_schema.demo_user_table
FROM
(
  SELECT
    $1:user_id,
    $1:user_name,
    $1:age, -- ★ここでもエラー
    $1:start_time -- ★ここでエラー
  FROM
   @demo_db.demo_schema.demo_stage
);

２）試してダメだったこと

case whenで判定
https://community.snowflake.com/s/question/0D50Z00009C4j4XSAR/failed-to-cast-variant-value-to-timestampntz

にあるような
「select case when $1:startdate ='""' then null else to_timestamp($1:startdate)  end as startdate,」
のような感じを試したけどダメ。

NULL_IF
https://docs.snowflake.com/ja/sql-reference/sql/create-stage.html#type-parquet

にある「NULL_IF = ('””', '')」を試したが現象は変わらず。

Try_Cast のみを使用 

* 別の例外「Function TRY_CAST cannot be used・・・」が発生
 => 詳細は、後述「[1] TRY_CAST について」を参照。

【２】エラー内容

NULL許容のNUMBER型の項目「age」の場合 

Failed to cast variant value "" to FIXED

NULL許容のTIMESTAMP型の項目「start_time」の場合 

Failed to cast variant value "" to TIMESTAMP_NTZ

【３】原因

取り込んだParquetファイルでNULL（Empty？）値があり
その際に（何故か）「variant value ""」として取り込まれて
キャストに失敗している

【４】対応案

* TRY_CAST(TO_VARCHAR(<項目名>) AS <キャストしたいデータ型>)をする
 => あくまで案。もっといい方法があればいいのだが。。。

１）スクリプト・イメージ（修正版）

-- データロード
COPY INTO demo_db.demo_schema.demo_user_table
FROM
(
  SELECT
    $1:user_id,
    $1:user_name,
    TRY_CAST(TO_VARCHAR($1:age) AS NUMBER), -- ★ここを修正
    TRY_CAST(TO_VARCHAR($1:start_time) AS TIMESTAMP) -- ★ここを修正
  FROM
   @demo_db.demo_schema.demo_stage
);

２）対応案に関する補足説明

[1] TRY_CAST について 

* TRY_CAST(<引数>)の引数は、以下の公式ドキュメントにあるように
　あくまで「文字列」or「数字」で、VARIANT型には対応していない
 => 実際、別の例外「Function TRY_CAST cannot be used with arguments of types VARIANT」が発生する

https://docs.snowflake.com/ja/sql-reference/functions/try_cast.html

より抜粋
~~~~~~~~~
TRY_CAST( <source_string_expr> AS <target_data_type> )

使用上の注意
文字列式でのみ機能します。
~~~~~~~~~

[2] TO_VARCHAR() について 

* [1]の仕様で仕方ないので、TO_VARCHAR() を使って、
　無理やり文字列に変換している。

https://docs.snowflake.com/ja/sql-reference/functions/to_char.html

より抜粋
~~~~~~~~~
TO_VARCHAR( <expr> )
TO_VARCHAR( <numeric_expr> [, '<format>' ] )
TO_VARCHAR( <date_or_time_expr> [, '<format>' ] )
TO_VARCHAR( <binary_expr> [, '<format>' ] )

使用上の注意
VARIANT、 ARRAY、または OBJECT 入力の場合、
出力は JSON ドキュメントまたは JSON 基本値を含む文字列です
　VARIANT に保存された文字列はそのまま保存されます
　（JSON 文字列に変換されません）。
~~~~~~~~~

関連記事

Snowflake ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ～ 入門編 / Hello world ～
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ～ 基本編 / キャスト ～
https://dk521123.hatenablog.com/entry/2021/12/15/162658
Snowflake ～ 基本編 / データロード ～
https://dk521123.hatenablog.com/entry/2021/11/15/221245
エラー「Insufficient privileges to operate」時の対応
https://dk521123.hatenablog.com/entry/2022/08/02/090439
エラー「Table does not exist or not authorized」時の対応
https://dk521123.hatenablog.com/entry/2022/08/18/120935