Snowflakeで各カラムのnull件数を取得するプロシージャを作成した

初めに

最近Snowflakeのスキーマにあるテーブルの中にnullのデータが存在するかを知りたかったので、取得するプロシージャを作成しました。
正確にはtry_to_timestamp(hoge, 'YYYY-MM-DD')している箇所があり、フォーマットが正しくないとnullになるため、そのフォーマットチェックをしたかったのが発端です。

これがあるとどんなデータが表示されるの？

下記のように、対象のスキーマのテーブルのカラムに対してnullの件数が表示できます。

作ったプロシージャはこれだ

-- 結果を保持するテーブル作成
CREATE OR REPLACE TABLE column_null_result (
    table_schema STRING,
    table_name STRING,
    column_name STRING,
    data_type STRING,
    null_count INTEGER
);

-- データベース：db_hoge
-- スキーマ：schema_huga
-- この二つの箇所を自身の環境に変更すればOK
CREATE OR REPLACE PROCEDURE column_null_info()
RETURNS STRING
LANGUAGE SQL
execute as caller
AS
declare
    res resultset default ( SELECT table_schema, table_name, column_name, data_type
                            FROM db_hoge.information_schema.columns 
                            WHERE table_schema = 'schema_huga'
                            );
    cur1 cursor for res;
    table_schema STRING;
    table_name STRING;
    column_name STRING;
    data_type STRING;
begin
    for raw_valiable in cur1 do
        table_schema := raw_valiable.table_schema;
        table_name := raw_valiable.table_name;
        column_name := raw_valiable.column_name;
        data_type := raw_valiable.data_type;

        EXECUTE IMMEDIATE '
            INSERT INTO column_null_result (table_schema, table_name, column_name, data_type, null_count)
                        SELECT ''' || table_schema || ''', ''' || table_name || ''', ''' || column_name || ''', ''' || data_type || ''', COUNT(*)
                        FROM schema_huga.' || table_name || '
                        WHERE "' || column_name || '" IS NULL;
        ';
                    
    end for;

    RETURN 'Success';
EXCEPTION
    WHEN OTHER THEN
        RETURN 'Error: ' || SQLERRM;
end;

-- プロシージャの実行
CALL column_null_info();

-- 結果のデータを表示
select * from column_null_result ;

-- タイムスタンプ型でnullが存在するカラムを表示
select * from column_null_result 
where null_count > 1
and data_type like 'TIMESTAMP%';

ちょっとだけ解説

SELECT table_schema, table_name, column_name, data_type
                            FROM db_hoge.information_schema.columns 
                            WHERE table_schema = 'schema_huga'

この箇所で対象となるカラムの一覧を取得します。
そのため、where句に他の条件を付けて、初めからtimestamp型だけに絞ることもできます。

INSERT INTO column_null_result (table_schema, table_name, column_name, data_type, null_count)
                        SELECT ''' || table_schema || ''', ''' || table_name || ''', ''' || column_name || ''', ''' || data_type || ''', COUNT(*)
                        FROM schema_huga.' || table_name || '
                        WHERE "' || column_name || '" IS NULL;

この箇所でnullのデータ件数を取得しています。
もちろんnullでは無くて、0のデータ件数を取得したいのであれば、where句を書き換えればOK。

今後の課題

• 1レコードinsertするのに500msくらいかかっています。そのためテーブルやカラム数が多いと時間がかかります（=費用がかかる）。
• 上記対応としてはselectの結果は別で変数に覚えさせて、数10件〜数100件に1回insertするとかにしようと思います。
• 合わせて単純な件数もselectした結果に表示されるとデータとして使い勝手が良いなと感じたので、それも入れたいですね。