I have many snowflake table which multiple columns with datatype TEXT or varchar having NULL values. I wanted to replace all the NULL values to empty string or blank at table level. I am looking for a way apart from NULLIF and COALESCE as they will work at column level and it is not feasible to specify 100's of columns.
Sample data :
A B C D
1 test NULL NULL
99 NULL test test
Desired output :
A B C D
1 test
99 test test
It is possible to achieve the desired effect with Snowpark and Python:
WITH replace_nulls AS PROCEDURE(table_name TEXT)
RETURNS TEXT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
def main(session, table_name):
session.table(table_name).replace(None, '').write.mode('overwrite').save_as_table(table_name)
return table_name + ' NULL handling completed'
$$
CALL replace_nulls('test_tab');
For sample data:
CREATE OR REPLACE TABLE test_tab(A INT, B TEXT, C TEXT, D TEXT)
AS
SELECT 1, 'test', NULL, NULL UNION
SELECT 99, NULL, 'test', 'test';
SELECT * FROM test_tab;
Before:
After:
*The code is just sample, it does not check the column data type, etc.
More at: Snowflake - invoking Python code without creating UDF/Stored Procedure and Snowflake Python UDFs
As per my knowledge, there is no way to specify such behavior for text/varchar columns on table level.
To automate the replacement for your table, you may write a stored procedure, which is looping over all columns and is generating a corresponding UPDATE-statement.
