Create Table with calculated column using a javascript function

user_18208164 0
Viewed 97

Looking to create a table and include a calculated column that references a javascript function. I have found that I can do calculations in the calculated column using as case statement.

but I created the following function, cant seem to get it to work with the calculated column. Do you know if this is functionality I am just using wrong or not currently supported?

create or replace function UTILITY.function.JSON_TO_TABLE_SOURCE_COLUMN_TEXT_test
(COLUMN_NAME_PREFIX varchar, COLUMN_NAME varchar)
returns varchar
language javascript
AS $$
    return COLUMN_NAME_PREFIX + ':' + COLUMN_NAME
$$
;

CREATE OR REPLACE TABLE DEV_BCOLEMAN.dev.T2(
  SOURCE_COLUMN_NAME varchar,
  SOURCE_COLUMN_PREFIX varchar,
  SOURCE_COLUMN_TEXT varchar(100) AS (UTILITY.function.JSON_TO_TABLE_SOURCE_COLUMN_TEXT_TEST(SOURCE_COLUMN_PREFIX, SOURCE_COLUMN_NAME))
);

Hoping the output would look something like this:

SOURCE_COLUMN_NAME SOURCE_COLUMN_PREFIX SOURCE_COLUMN_TEXT
id data data:id

Couple of the ways I tried: SOURCE_COLUMN_TEXT varchar(100) as UTILITY.FUNCTION.JSON_TO_TABLE_SOURCE_COLUMN_TEXT('data','id')

SOURCE_COLUMN_TEXT varchar(100) as (select UTILITY.FUNCTION.JSON_TO_TABLE_SOURCE_COLUMN_TEXT('data', 'id')

The error is:: Invalid virtual column expression [JSON_TO_TABLE_SOURCE_COLUMN_TEXT..]

snowflake-cloud-data-platform
2 Answers

It is possible to use scalar SQL UDF as virtual column's expression:

create or replace function PUBLIC.JSON_TO_TABLE_SOURCE_COLUMN_TEXT
(COLUMN_NAME_PREFIX varchar)
returns varchar
AS 'COLUMN_NAME_PREFIX';

Demo:

SELECT PUBLIC.JSON_TO_TABLE_SOURCE_COLUMN_TEXT('id');
-- id

CREATE OR REPLACE TABLE T2(
  i INT,
  SOURCE_COLUMN_TEXT varchar(100) AS (PUBLIC.JSON_TO_TABLE_SOURCE_COLUMN_TEXT('id'))
);

INSERT INTO t2(i) VALUES (1);
SELECT * FROM t2;

Output:

enter image description here

user_50708790

In order to do what you're attempting here, you need to use the DEFAULT keyword for the column. Snowflake supports the following three types of DEFAULT values for columns:

  • Constant value
  • Simple expression
  • Sequence reference (seq_name.NEXTVAL)

A JavaScript UDF probably does not qualify as a simple expression, but what you want here can be done using a simple expression that the DEFAULT value will use:

create or replace function JSON_TO_TABLE_SOURCE_COLUMN_TEXT_test
(COLUMN_NAME_PREFIX varchar, COLUMN_NAME varchar)
returns varchar
language javascript
AS $$
    return COLUMN_NAME_PREFIX + ':' + COLUMN_NAME
$$
;

CREATE OR REPLACE TABLE T2(
  SOURCE_COLUMN_NAME varchar,
  SOURCE_COLUMN_PREFIX varchar,
  SOURCE_COLUMN_TEXT varchar(100) default SOURCE_COLUMN_PREFIX || ':' || SOURCE_COLUMN_NAME
);

insert into t2 (SOURCE_COLUMN_NAME, SOURCE_COLUMN_PREFIX) values ('ID', 'DATA');

select * from t2;
user_127563810
Related