Snowflake SQL Tutorial 144 - Python Kept My 30-Digit Number And JavaScript Could Not Even Declare It

Snowflake SQL · Episode 144

In this post we are going to cover Python UDFs in Snowflake.

A Python UDF is a function you call from SQL whose body is Python. You give Snowflake the code, Snowflake runs it once for every value you hand it, and the answer comes back as an ordinary column.

Compared with a JavaScript UDF there are two extra lines in the header: a RUNTIME_VERSION and a HANDLER. We will start with the smallest function there is, see what those two lines are for, and build it up step by step.

One word in a grid near the middle of this post explains why Python is the one that keeps your number, and we will get to it properly rather than announcing it now.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 144 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

Let's create some sample data

Most of this post is a single function and a single value, so we do not need much. Later on we will run a function once per row, and for that we need a table.

The script below creates a small SALES_T144 table and puts five rows into it. The fifth row has no amount, and that missing value turns out to matter a great deal by the end of the post.

Sample data
-- Sample data for this post.
-- Five sales rows, and the fifth one has no amount on purpose.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE SALES_T144 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

INSERT INTO SALES_T144 (SALE_ID, REGION, AMOUNT) VALUES
    (1, 'north', 95.00),
    (2, 'south', 75.00),
    (3, 'east',  55.00),
    (4, 'west',  10.00),
    (5, 'north', NULL);
The insert
INSERT INTO SALES_T144 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 95.00), (2, 'south', 75.00),
       (3, 'east',  55.00), (4, 'west',  10.00),
       (5, 'north',  NULL);
Result1 row · 1 column
number of rows inserted
5

Five rows in. Now let's write some Python.

Let's start with the smallest Python UDF

Here is a function that doubles a number. Read the header before the body, because the header is where Python differs from everything we have written so far.

Create
CREATE OR REPLACE FUNCTION DOUBLE_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'double_it'
AS
$$
def double_it(n):
    return n * 2
$$;
Result1 row · 1 column
status
Function DOUBLE_T144 successfully created.

Three things in that header. LANGUAGE PYTHON, which we would expect. RUNTIME_VERSION, which says which Python interpreter to use. And HANDLER, which names the function inside the body that Snowflake should call.

Let's call it three times in one query.

Query
SELECT DOUBLE_T144(21)   AS TWENTY_ONE_DOUBLED,
       DOUBLE_T144(0)    AS NOTHING_DOUBLED,
       DOUBLE_T144(-1.5) AS A_NEGATIVE_DOUBLED;
Result1 row · 3 columns
TWENTY_ONE_DOUBLEDNOTHING_DOUBLEDA_NEGATIVE_DOUBLED
42.00.0-3.0

Declared FLOAT, returns FLOAT, and that is why we get 42.0 rather than 42.

Why there is a handler at all

HANDLER exists because a Python body is a module, not an expression. It can hold imports, several functions and a class. Snowflake does not guess which one you meant, so you tell it.

Now let's see what the catalog says about it

The function is a real object in the schema, so the catalog can describe it.

Query
SELECT FUNCTION_NAME,
       ARGUMENT_SIGNATURE,
       DATA_TYPE,
       FUNCTION_LANGUAGE
FROM INFORMATION_SCHEMA.FUNCTIONS
WHERE FUNCTION_SCHEMA = 'PUBLIC'
  AND ENDSWITH(FUNCTION_NAME, '_T144')
ORDER BY FUNCTION_NAME;
Result1 row · 4 columns
FUNCTION_NAMEARGUMENT_SIGNATUREDATA_TYPEFUNCTION_LANGUAGE
DOUBLE_T144(N FLOAT)FLOATPYTHON

Name, signature, return type and language. Nothing surprising.

DESCRIBE FUNCTION is worth a look here, though, because it tells us far more about a Python function than it did about a SQL one.

Query
DESCRIBE FUNCTION DOUBLE_T144(FLOAT);
Result12 rows · 2 columns · showing first 10
Twelve property rows. The grid is cut after ten, because the row after these is a single cell listing every package in the runtime.
propertyvalue
signature(N FLOAT)
returnsFLOAT
languagePYTHON
null handlingCALLED ON NULL INPUT
volatilityVOLATILE
body def double_it(n): return n * 2
imports[]
handlerdouble_it
runtime_version3.11
packages[]

The body itself came back, and so did the handler name and the runtime version. For a plain SQL function this would have been a much shorter list.

Now let's write a body that earns the word module

If the body really is a module, we should be able to put an import and a second function in it. Let's try exactly that.

Create
CREATE OR REPLACE FUNCTION ROUNDED_T144(N FLOAT)
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'describe_it'
AS
$$
import math

def nearest_ten(x):
    return int(round(x / 10.0)) * 10

def describe_it(n):
    return "%s rounds to %s and its square root is %.3f" % (
        n, nearest_ten(n), math.sqrt(abs(n)))
$$;
Result1 row · 1 column
status
Function ROUNDED_T144 successfully created.

There are two functions in there. describe_it is the handler, and nearest_ten is a helper that only describe_it calls. There is also an import math at the top.

Query
SELECT ROUNDED_T144(17) AS SEVENTEEN,
       ROUNDED_T144(42) AS FORTY_TWO;
Result1 row · 2 columns
SEVENTEENFORTY_TWO
17.0 rounds to 20 and its square root is 4.12342.0 rounds to 40 and its square root is 6.481

The helper ran and the import worked. Snowflake called describe_it because we said so in the header, and it never tried to call nearest_ten from SQL.

What happens when the handler names a function that is not there?

The handler is a promise about the body. Let's break it and see when Snowflake notices.

Create
CREATE OR REPLACE FUNCTION BADHANDLER_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'not_in_there'
AS
$$
def double_it(n):
    return n * 2
$$;
Query failed — Snowflake returned an error
100357 (P0000): Could not find handler in function BADHANDLER_T144 with handler not_in_there

100357, could not find handler. Read what that means carefully: this is the CREATE statement failing, not a call.

So the function was never made. Let's confirm that by calling it.

Query
SELECT BADHANDLER_T144(1) AS WHAT_CAME_BACK;
Query failed — Snowflake returned an error
002139 (02000): SQL compilation error:
Unknown function BADHANDLER_T144.

002139, unknown function. There is nothing there. The handler is checked when the function is created, which is the best time for it to be checked.

Now let's get the header wrong three more ways

First, leave the handler out altogether.

Create
CREATE OR REPLACE FUNCTION NOHANDLER_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
AS
$$
def double_it(n):
    return n * 2
$$;
Query failed — Snowflake returned an error
001480 (42601): Property 'handler' must be specified

001480, property handler must be specified. It is not optional and there is no default.

Next, ask for a runtime that is not on offer.

Create
CREATE OR REPLACE FUNCTION OLDRUNTIME_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '2.7'
HANDLER = 'double_it'
AS
$$
def double_it(n):
    return n * 2
$$;
Query failed — Snowflake returned an error
001422 (22023): SQL compilation error:
invalid value '2.7' for property 'RUNTIME_VERSION'

001422, invalid value for property RUNTIME_VERSION.

And last, a handler that takes two parameters when the SQL signature only has one argument.

Create
CREATE OR REPLACE FUNCTION WRONGARITY_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(a, b):
    return a * b
$$;
Query failed — Snowflake returned an error
100357 (P0000): Python function is defined with 2 arguments, but UDF definition contains 1 arguments in function WRONGARITY_T144 with handler f

100357 again, and the message counts both sides for us. Let's check this one does not exist either.

Query
SELECT WRONGARITY_T144(10) AS WHAT_CAME_BACK;
Query failed — Snowflake returned an error
002139 (02000): SQL compilation error:
Unknown function WRONGARITY_T144.

Unknown function. So all four of these mistakes are caught at create time, and none of the four functions ever existed.

Does the parameter name have to match?

Here the SQL argument is called AMOUNT and the Python parameter is called something else entirely.

Create
CREATE OR REPLACE FUNCTION DIFFERENTNAME_T144(AMOUNT FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(something_else_entirely):
    return something_else_entirely * 2
$$;
Result1 row · 1 column
status
Function DIFFERENTNAME_T144 successfully created.

Created without a word. Let's call it.

Query
SELECT DIFFERENTNAME_T144(10) AS WHAT_CAME_BACK;
Result1 row · 1 column
WHAT_CAME_BACK
20.0

It does not matter at all. Now let's try the case difference as well, with a lower-case SQL argument and an upper-case Python parameter.

Create
CREATE OR REPLACE FUNCTION UPPERPARAM_T144(amount FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(AMOUNT):
    return AMOUNT * 2
$$;
Result1 row · 1 column
status
Function UPPERPARAM_T144 successfully created.
Query
SELECT UPPERPARAM_T144(10) AS WHAT_CAME_BACK;
Result1 row · 1 column
WHAT_CAME_BACK
20.0

The same answer. Binding here is positional: the first SQL argument goes to the first Python parameter, whatever either of them is called.

Worth knowing if you came from Episode 143

This is the opposite of a JavaScript UDF, where the argument arrives as a variable with the declared name, in upper case, and a mismatch is a reference error at call time. In Python the name is yours to choose. The number of parameters still has to match, which is the refusal we saw in the section above.

Now let's send a big number through it

This is the part of the episode I actually wanted to find out about. A Python function that takes a number and hands it straight back, declared as NUMBER(38,0).

Create
CREATE OR REPLACE FUNCTION ECHO_T144(N NUMBER(38,0))
RETURNS NUMBER(38,0)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
    return n
$$;
Result1 row · 1 column
status
Function ECHO_T144 successfully created.

Fine. Now the same function in JavaScript, so the two can sit in one grid side by side.

Create
CREATE OR REPLACE FUNCTION JSECHO_T144(N NUMBER(38,0))
RETURNS NUMBER(38,0)
LANGUAGE JAVASCRIPT
AS
$$
    return N;
$$;
Query failed — Snowflake returned an error
090215 (42601): Language JAVASCRIPT does not support type 'NUMBER(38,0)' for argument or return type.

090215. JavaScript does not support NUMBER(38,0) for an argument or a return type. It will not even let us declare the function.

Which means the comparison query I had written refers to a function that does not exist. Let's run it anyway and see what that looks like.

Query
SELECT 9007199254740991            AS WHAT_I_SENT,
       ECHO_T144(9007199254740991)   AS THROUGH_PYTHON,
       JSECHO_T144(9007199254740991) AS THROUGH_JAVASCRIPT;
Query failed — Snowflake returned an error
002139 (02000): SQL compilation error:
Unknown function JSECHO_T144.

002139, unknown function. The whole query was refused, including the part that would have worked. So we have to give the JavaScript one a type it will accept.

Let's make the JavaScript one a float and try again

I re-ran this part on its own so the comparison would be fair. First the Python function, declared the same way as before.

Create
CREATE OR REPLACE FUNCTION PYECHO_T144B(N NUMBER(38,0))
RETURNS NUMBER(38,0)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
    return n
$$;
Result1 row · 1 column
status
Function PYECHO_T144B successfully created.

And the JavaScript one, with the same declaration, one more time.

Create
CREATE OR REPLACE FUNCTION JSECHO_T144B(N NUMBER(38,0))
RETURNS NUMBER(38,0)
LANGUAGE JAVASCRIPT
AS
$$
    return N;
$$;
Query failed — Snowflake returned an error
090215 (42601): Language JAVASCRIPT does not support type 'NUMBER(38,0)' for argument or return type.

The same refusal. So let's declare it FLOAT instead, which JavaScript is happy with.

Create
CREATE OR REPLACE FUNCTION JSFLOAT_T144B(N FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
    return N;
$$;
Result1 row · 1 column
status
Function JSFLOAT_T144B successfully created.

And a Python one declared FLOAT too, so we are comparing the type and not the language.

Create
CREATE OR REPLACE FUNCTION PYFLOAT_T144B(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
    return n
$$;
Result1 row · 1 column
status
Function PYFLOAT_T144B successfully created.

Now we have three functions that all do nothing except hand a number back. Let's start with the largest integer a double holds exactly.

Query
SELECT 9007199254740991              AS WHAT_I_SENT,
       PYECHO_T144B(9007199254740991) AS PYTHON_NUMBER,
       PYFLOAT_T144B(9007199254740991) AS PYTHON_FLOAT,
       JSFLOAT_T144B(9007199254740991) AS JAVASCRIPT_FLOAT;
Result1 row · 4 columns
WHAT_I_SENTPYTHON_NUMBERPYTHON_FLOATJAVASCRIPT_FLOAT
900719925474099190071992547409919007199254740991.09007199254740991.0

All three agree. Nothing interesting yet, and that is the point of starting here.

Now the very next odd integer above it.

Query
SELECT 9007199254740993              AS WHAT_I_SENT,
       PYECHO_T144B(9007199254740993) AS PYTHON_NUMBER,
       PYFLOAT_T144B(9007199254740993) AS PYTHON_FLOAT,
       JSFLOAT_T144B(9007199254740993) AS JAVASCRIPT_FLOAT;
Result1 row · 4 columns
WHAT_I_SENTPYTHON_NUMBERPYTHON_FLOATJAVASCRIPT_FLOAT
900719925474099390071992547409939007199254740992.09007199254740992.0

The NUMBER one came back exactly as we sent it. Both float ones came back one lower.

And now thirty digits.

Query
SELECT 123456789012345678901234567890              AS WHAT_I_SENT,
       PYECHO_T144B(123456789012345678901234567890) AS PYTHON_NUMBER;
Result1 row · 2 columns
WHAT_I_SENTPYTHON_NUMBER
123456789012345678901234567890123456789012345678901234567890

Every digit survived the round trip through Python.

The same number through the two float functions, for comparison.

Query
SELECT PYFLOAT_T144B(123456789012345678901234567890) AS PYTHON_FLOAT,
       JSFLOAT_T144B(123456789012345678901234567890) AS JAVASCRIPT_FLOAT;
Result1 row · 2 columns
PYTHON_FLOATJAVASCRIPT_FLOAT
1.2345678901234568e+291.2345678901234568e+29

Both of them came back in scientific notation, and both of them came back the same. The language is not what made the difference. The declared type is.

Let's read the difference row carefully

I also asked Snowflake to subtract each answer from what I sent, so that none of the numbers above is a claim of mine.

Query
SELECT 9007199254740993 - PYECHO_T144B(9007199254740993)  AS PYTHON_NUMBER_IS_OUT_BY,
       9007199254740993 - PYFLOAT_T144B(9007199254740993)  AS PYTHON_FLOAT_IS_OUT_BY,
       9007199254740993 - JSFLOAT_T144B(9007199254740993)  AS JAVASCRIPT_IS_OUT_BY;
Result1 row · 3 columns
PYTHON_NUMBER_IS_OUT_BYPYTHON_FLOAT_IS_OUT_BYJAVASCRIPT_IS_OUT_BY
00.00.0
Do not read this as three clean bills of health

Three zeros, and only the first one means anything.
The first subtraction was done in NUMBER, which can represent the loss, and it is telling us there was none.
The other two subtractions were done in floating point. The answer they lost is the same answer they are subtracting, so they cannot see their own loss and they report nothing wrong.
The evidence for those two is the side-by-side grid above, where the value came back lower than it went in. It is not this row.

So what did Python actually receive?

There is one word that explains the whole of the last two sections, and we can ask Python for it directly. Here is a function that reports the type name of its own argument.

Create
CREATE OR REPLACE FUNCTION PYTYPE_T144B(N NUMBER(38,0))
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
    return type(n).__name__
$$;
Result1 row · 1 column
status
Function PYTYPE_T144B successfully created.
Query
SELECT PYTYPE_T144B(9007199254740993) AS WHAT_PYTHON_RECEIVED;
Result1 row · 1 column
WHAT_PYTHON_RECEIVED
int

int. Not float. A Python int has no fixed width, so a thirty digit whole number is an ordinary value to it, and that is why every digit came back.

Declare the same argument FLOAT and Python gets a float, which loses exactly the bottom bit JavaScript loses, because they are both doubles.

So let's ask the same question of the other SQL types.

Create
CREATE OR REPLACE FUNCTION WHATTYPES_T144(
    A NUMBER(10,2), B VARCHAR, C BOOLEAN, D DATE, E VARIANT)
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(a, b, c, d, e):
    return ", ".join(type(x).__name__ for x in (a, b, c, d, e))
$$;
Result1 row · 1 column
status
Function WHATTYPES_T144 successfully created.
Query
SELECT WHATTYPES_T144(1.50, 'text', TRUE, '2026-01-15'::DATE,
                      PARSE_JSON('{"a":1}')) AS WHAT_PYTHON_SAW;
Result1 row · 1 column
WHAT_PYTHON_SAW
Decimal, str, bool, date, dict

Five types, five answers. The one to notice is the first: a NUMBER(10,2) arrives as a Decimal, not as a float. If you write Python that assumes it got a float, that is where it will bite you.

Now let's see what a null does

Snowflake has two clauses that decide whether the body runs at all when an argument is null. Let's create one of each, with identical bodies.

Create
CREATE OR REPLACE FUNCTION CALLED_T144(N FLOAT)
RETURNS VARCHAR
LANGUAGE PYTHON
CALLED ON NULL INPUT
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
    if n is None:
        return "the body ran and n was None"
    return "the body ran and n was %s" % n
$$;
Result1 row · 1 column
status
Function CALLED_T144 successfully created.
Create
CREATE OR REPLACE FUNCTION STRICT_T144(N FLOAT)
RETURNS VARCHAR
LANGUAGE PYTHON
RETURNS NULL ON NULL INPUT
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
    if n is None:
        return "the body ran and n was None"
    return "the body ran and n was %s" % n
$$;
Result1 row · 1 column
status
Function STRICT_T144 successfully created.

The only difference between those two is the clause. Both bodies report what they saw. Let's hand them a real value first.

Query
SELECT CALLED_T144(3) AS THE_CALLED_ONE,
       STRICT_T144(3) AS THE_STRICT_ONE;
Result1 row · 2 columns
THE_CALLED_ONETHE_STRICT_ONE
the body ran and n was 3.0the body ran and n was 3.0

Both ran, and both saw the same thing. Now a null.

Query
SELECT CALLED_T144(NULL) AS THE_CALLED_ONE,
       STRICT_T144(NULL) AS THE_STRICT_ONE;
Result1 row · 2 columns
THE_CALLED_ONETHE_STRICT_ONE
the body ran and n was NoneNULL

The first one ran and saw None, which is Python's own null. The second one returned null and never ran at all.

So the clause is honoured on a Python UDF, exactly as it is on a JavaScript one.

And a handler with no return in it

Python functions that fall off the end return None. Let's see what SQL makes of that.

Create
CREATE OR REPLACE FUNCTION RETURNSNOTHING_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
    x = n * 2
$$;
Result1 row · 1 column
status
Function RETURNSNOTHING_T144 successfully created.
Query
SELECT RETURNSNOTHING_T144(5)         AS WHAT_CAME_BACK,
       RETURNSNOTHING_T144(5) IS NULL AS IS_IT_NULL;
Result1 row · 2 columns
WHAT_CAME_BACKIS_IT_NULL
NULLTRUE

None became a SQL null, and the second column confirms it is a real null rather than text.

What does a raise look like from SQL?

Here is a function that refuses negative numbers with a message of my own.

Create
CREATE OR REPLACE FUNCTION RAISES_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
    if n < 0:
        raise ValueError("I will not take a negative number")
    return n ** 0.5
$$;
Result1 row · 1 column
status
Function RAISES_T144 successfully created.

Let's give it a number it likes.

Query
SELECT RAISES_T144(16) AS SIXTEEN_ROOTED;
Result1 row · 1 column
SIXTEEN_ROOTED
4.0

That is the path that does not raise. Now a negative.

Query
SELECT RAISES_T144(-1) AS A_NEGATIVE;
Query failed — Snowflake returned an error
100357 (P0000): Python Interpreter Error:
Traceback (most recent call last):
  File "_udf_code.py", line 4, in f
    raise ValueError("I will not take a negative number")
ValueError: I will not take a negative number
 in function RAISES_T144 with handler f

100357, Python interpreter error, with the whole traceback and my own message on the ValueError line. Nothing is hidden and nothing is translated.

What happens when the Python does not parse?

This is the one that reverses what we saw with JavaScript. Here is a body that is not valid Python.

Create
CREATE OR REPLACE FUNCTION BROKEN_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
    return n *
$$;
Query failed — Snowflake returned an error
100357 (P0000): Python Interpreter Error:
  File "_udf_code.py", line 3
    return n *
              ^
SyntaxError: invalid syntax
 in function BROKEN_T144 with handler f

Refused at create, with a caret under the bad line and SyntaxError: invalid syntax.

Query
SELECT BROKEN_T144(1) AS WHAT_CAME_BACK;
Query failed — Snowflake returned an error
002139 (02000): SQL compilation error:
Unknown function BROKEN_T144.

Nothing was created, so there is nothing to call.

The difference that matters here

A broken JavaScript UDF is created happily and only fails when somebody calls it. Python is compiled when the function is created, so a typo is caught by the person who wrote it rather than by whoever runs the report on Monday.

Now let's pass some JSON in and out

A VARIANT argument can hold an object, an array or a bare value. Let's write a function that reports what Python sees in each case.

Create
CREATE OR REPLACE FUNCTION INSIDE_T144(DOC VARIANT)
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(doc):
    if doc is None:
        return "nothing came in"
    if isinstance(doc, dict):
        return "a dict with keys: " + ",".join(sorted(doc.keys()))
    if isinstance(doc, list):
        return "a list of %d things" % len(doc)
    return "a %s" % type(doc).__name__
$$;
Result1 row · 1 column
status
Function INSIDE_T144 successfully created.
Query
SELECT INSIDE_T144(PARSE_JSON('{"region":"north","amount":100}')) AS AN_OBJECT,
       INSIDE_T144(PARSE_JSON('[1,2,3]'))                        AS AN_ARRAY,
       INSIDE_T144(PARSE_JSON('7'))                              AS A_NUMBER;
Result1 row · 3 columns
AN_OBJECTAN_ARRAYA_NUMBER
a dict with keys: amount,regiona list of 3 thingsa int

An object arrives as a dict, an array arrives as a list, and a bare number arrives as an int. A JavaScript UDF sees that array as an object keyed by position, which is a much less pleasant thing to work with.

Now the other direction. A handler that builds a dict and returns it.

Create
CREATE OR REPLACE FUNCTION BUILD_T144(R VARCHAR, A FLOAT)
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(r, a):
    return {"region": r, "amount": a, "taxed": a * 1.2}
$$;
Result1 row · 1 column
status
Function BUILD_T144 successfully created.
Query
SELECT BUILD_T144('north', 100)          AS THE_OBJECT,
       BUILD_T144('north', 100):region   AS THE_REGION,
       TYPEOF(BUILD_T144('north', 100))  AS WHAT_SQL_THINKS_IT_IS;
Result1 row · 3 columns
THE_OBJECTTHE_REGIONWHAT_SQL_THINKS_IT_IS
{ "amount": 1.000000000000000e+02, "region": "north", "taxed": 1.200000000000000e+02 }"north"OBJECT

A real VARIANT came back. TYPEOF says object, and the colon notation reaches inside it for one key.

Have a look at the amount inside that object, though. It went in as a Python float and it came out in scientific notation. Nothing is wrong with the value. That is simply how a float is written into a VARIANT.

And a handler that returns a list.

Create
CREATE OR REPLACE FUNCTION SPLIT_T144(S VARCHAR)
RETURNS ARRAY
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(s):
    return s.split(",")
$$;
Result1 row · 1 column
status
Function SPLIT_T144 successfully created.
Query
SELECT SPLIT_T144('north,south,east')             AS THE_ARRAY,
       ARRAY_SIZE(SPLIT_T144('north,south,east')) AS HOW_MANY;
Result1 row · 2 columns
THE_ARRAYHOW_MANY
[ "north", "south", "east" ]3

A list becomes an ARRAY, and ARRAY_SIZE counts it like any other array.

Can we use a library?

This is the reason most people reach for Python here. A package goes in a PACKAGES clause in the header and is imported in the body as usual.

Create
CREATE OR REPLACE FUNCTION MEDIAN_T144(A ARRAY)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('numpy')
HANDLER = 'f'
AS
$$
import numpy as np

def f(a):
    return float(np.median([float(x) for x in a]))
$$;
Result1 row · 1 column
status
Function MEDIAN_T144 successfully created.
Query
SELECT MEDIAN_T144(ARRAY_CONSTRUCT(1, 2, 3, 10, 100)) AS THE_MEDIAN;
Result1 row · 1 column
THE_MEDIAN
3.0

That is the median of the five numbers we passed in, worked out by numpy inside Snowflake.

Now let's ask for a package that does not exist.

Create
CREATE OR REPLACE FUNCTION NOPACKAGE_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('no_such_package_t144')
HANDLER = 'f'
AS
$$
def f(n):
    return n
$$;
Query failed — Snowflake returned an error
391577 (42601): SQL compilation error: Cannot create a Python function with the specified artifact repository packages. Make sure the specified packages and dependencies exist and are spelled correctly. Also, you can run the pip command locally to see if the package specification is valid. 'Command = pip install --dry-run --python-version 3.11 no_such_package_t144. Error = Using Python 3.11.15 environment at: /usr/lib/python_udf/02b3cc1e77e833cb3d419af5274874e50dec30c9cba4597b023f533449273bfe
  × No solution found when resolving dependencies:
  ╰─▶ Because no-such-package-t144 was not found in the package registry and
      you require no-such-package-t144, we can conclude that your requirements
      are unsatisfiable.
'.

391577, and the message carries the dependency resolver's own complaint with it.

And the mistake that is easier to make: importing something without declaring it.

Create
CREATE OR REPLACE FUNCTION UNDECLARED_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
import numpy as np

def f(n):
    return float(np.sqrt(n))
$$;
Query failed — Snowflake returned an error
100357 (P0000): Python Interpreter Error:
Traceback (most recent call last):
  File "_udf_code.py", line 2, in <module>
    import numpy as np
ModuleNotFoundError: No module named 'numpy'
 in function UNDECLARED_T144 with handler f

ModuleNotFoundError, and look at when it happened. This is the CREATE statement. The import at the top of the module runs when the function is created, not when it is called.

Query
SELECT UNDECLARED_T144(16) AS SIXTEEN_ROOTED;
Query failed — Snowflake returned an error
002139 (02000): SQL compilation error:
Unknown function UNDECLARED_T144.

Unknown function, which is what we now expect.

Can a Python UDF read a table?

It is a fair thing to try. Snowpark is a package, so we can ask for it and build a session in the body.

Create
CREATE OR REPLACE FUNCTION READSATABLE_T144()
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'f'
AS
$$
from snowflake.snowpark import Session

def f():
    session = Session.builder.getOrCreate()
    return session.sql("SELECT COUNT(*) FROM SALES_T144").collect()[0][0]
$$;
Result1 row · 1 column
status
Function READSATABLE_T144 successfully created.

Created, with no complaint at all. Which is encouraging and also misleading, so let's call it.

Query
SELECT READSATABLE_T144() AS WHAT_CAME_BACK;
Query failed — Snowflake returned an error
100357 (P0000): Python Interpreter Error:
Traceback (most recent call last):
  File "<site-packages>/snowflake/snowpark/session.py", line 540, in getOrCreate
    session = _get_active_session()
              ^^^^^^^^^^^^^^^^^^^^^
  File "<site-packages>/snowflake/snowpark/session.py", line 331, in _get_active_session
    raise SnowparkClientExceptionMessages.SERVER_NO_DEFAULT_SESSION()
snowflake.snowpark.exceptions.SnowparkSessionException: (1403): No default Session is found. Please create a session before you call function 'udf' or use decorator '@udf'.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "_udf_code.py", line 5, in f
    session = Session.builder.getOrCreate()
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<site-packages>/snowflake/snowpark/session.py", line 547, in getOrCreate
    return self.create()
           ^^^^^^^^^^^^^
  File "<site-packages>/snowflake/snowpark/session.py", line 525, in create
    session = self._create_internal(self._options.get("connection"))
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<site-packages>/snowflake/snowpark/session.py", line 567, in _create_internal
    ServerConnection({}, conn) if conn else ServerConnection(self._options),
                                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<site-packages>/snowflake/connector/__init__.py", line 64, in Connect
    return SnowflakeConnection(**kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<site-packages>/snowflake/connector/connection.py", line 736, in __init__
    self.connect(**kwargs)
  File "<site-packages>/snowflake/connector/connection.py", line 1222, in connect
    self.__config(**kwargs)
  File "<site-packages>/snowflake/connector/connection.py", line 1876, in __config
    Error.errorhandler_wrapper(
  File "<site-packages>/snowflake/connector/errors.py", line 298, in errorhandler_wrapper
    handed_over = Error.hand_to_other_handler(
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<site-packages>/snowflake/connector/errors.py", line 361, in hand_to_other_handler
    connection.errorhandler(connection, cursor, error_class, error_value)
  File "<site-packages>/snowflake/connector/errors.py", line 229, in default_errorhandler
    raise error_class(
snowflake.connector.errors.ProgrammingError: 251005: User is empty, but it must be provided unless authenticator is one of PROGRAMMATIC_ACCESS_TOKEN, OAUTH, PAT_WITH_EXTERNAL_SESSION, OAUTH_CLIENT_CREDENTIALS, OAUTH_AUTHORIZATION_CODE, NO_AUTH, WORKLOAD_IDENTITY.
 in function READSATABLE_T144 with handler f

A traceback out of Snowpark's own session file. There is no active session inside a UDF for it to pick up, and it cannot make one.

So a scalar UDF is for turning values into values. If you want to read a table, that is a stored procedure, and that is the next post.

Now let's run one over a table

Everything so far has been one call at a time. Let's put the rounding function from earlier into a query over our sample table, skipping the row with no amount.

Query
SELECT SALE_ID,
       AMOUNT,
       ROUNDED_T144(AMOUNT) AS WHAT_PYTHON_SAID
FROM SALES_T144
WHERE AMOUNT IS NOT NULL
ORDER BY SALE_ID;
Result4 rows · 3 columns
SALE_IDAMOUNTWHAT_PYTHON_SAID
195.0095.0 rounds to 100 and its square root is 9.747
275.0075.0 rounds to 80 and its square root is 8.660
355.0055.0 rounds to 60 and its square root is 7.416
410.0010.0 rounds to 10 and its square root is 3.162

Python ran once for each of those rows and the answers came back as an ordinary column. From the query's point of view there is nothing special about it at all.

And a Python function that returns a table

A table function in Python is a class with a process method that yields tuples. The handler names the class.

Create
CREATE OR REPLACE FUNCTION SPLITROWS_T144(S VARCHAR)
RETURNS TABLE (THE_PIECE VARCHAR, WHICH_ONE INT)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'Splitter'
AS
$$
class Splitter:
    def process(self, s):
        for i, piece in enumerate(s.split(",")):
            yield (piece, i)
$$;
Result1 row · 1 column
status
Function SPLITROWS_T144 successfully created.
Query
SELECT THE_PIECE, WHICH_ONE
FROM TABLE(SPLITROWS_T144('north,south,east'))
ORDER BY WHICH_ONE;
Result3 rows · 2 columns
THE_PIECEWHICH_ONE
north0
south1
east2

Three rows out of one string. And have a look at WHICH_ONE: the column is declared INT and Python yields a Python int, so whole numbers come back whole. The JavaScript table function in the last post gave the same positions with a decimal point on them.

A table function can also be joined to a table, with a comma, so that each row of the table gets its own set of rows out of the function.

Query
SELECT s.SALE_ID, p.THE_PIECE
FROM SALES_T144 s,
     TABLE(SPLITROWS_T144(s.REGION || ',everywhere')) p
WHERE s.SALE_ID <= 2
ORDER BY s.SALE_ID, p.WHICH_ONE;
Result4 rows · 2 columns
SALE_IDTHE_PIECE
1north
1everywhere
2south
2everywhere

Two sales rows went in and each one came back twice, once for each piece of its own string.

One last thing: process() runs for rows the WHERE is going to throw away

Here is the one that caught me out, and it only showed up on real data.

A table function can also carry state. This class keeps a running total in self.total, yields it on every row, and then yields the final figure from an end_partition method when the partition ends.

Create
CREATE OR REPLACE FUNCTION RUNNING_T144(N FLOAT)
RETURNS TABLE (SO_FAR FLOAT, AT_THE_END FLOAT)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'Summer'
AS
$$
class Summer:
    def __init__(self):
        self.total = 0

    def process(self, n):
        self.total += n
        yield (self.total, None)

    def end_partition(self):
        yield (None, self.total)
$$;
Result1 row · 1 column
status
Function RUNNING_T144 successfully created.

Now let's run it over our sample table. The query partitions by region, and the WHERE clause throws away the row with no amount, so the handler should never see a null.

Query
SELECT r.SO_FAR, r.AT_THE_END
FROM SALES_T144 s,
     TABLE(RUNNING_T144(s.AMOUNT::FLOAT)
           OVER (PARTITION BY s.REGION ORDER BY s.SALE_ID)) r
WHERE s.AMOUNT IS NOT NULL
ORDER BY r.SO_FAR NULLS LAST;
Query failed — Snowflake returned an error
100357 (P0000): Python Interpreter Error:
Traceback (most recent call last):
  File "_udf_code.py", line 7, in process
    self.total += n
TypeError: unsupported operand type(s) for +=: 'float' and 'NoneType'
 in function RUNNING_T144 with handler Summer

TypeError, adding a float to a NoneType. So process ran for the row the WHERE clause was going to discard. I assumed it would not. It did.

The useful part

Read that query again. The filter is in the outer query, and the table function is in the FROM clause. The function is fed rows before anything is filtered. A handler that carries state has to cope with every row in the table, not just the rows you are going to keep.

So let's guard the handler against the null. Nothing else about it changes.

Create
CREATE OR REPLACE FUNCTION RUNNING_T144B(N FLOAT)
RETURNS TABLE (SO_FAR FLOAT, AT_THE_END FLOAT)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'Summer'
AS
$$
class Summer:
    def __init__(self):
        self.total = 0

    def process(self, n):
        if n is not None:
            self.total += n
        yield (self.total, None)

    def end_partition(self):
        yield (None, self.total)
$$;
Result1 row · 1 column
status
Function RUNNING_T144B successfully created.

I re-ran this part on a fresh table with five rows, one of which again has no amount.

Create
CREATE OR REPLACE TABLE SALES_T144B (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);
Result1 row · 1 column
status
Table SALES_T144B successfully created.
Insert
INSERT INTO SALES_T144B (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'north', 45.00),
       (3, 'south', 250.50), (4, 'south', 88.25),
       (5, 'north', NULL);
Result1 row · 1 column
number of rows inserted
5

And now the same query works.

Query
SELECT s.REGION, r.SO_FAR, r.AT_THE_END
FROM SALES_T144B s,
     TABLE(RUNNING_T144B(s.AMOUNT::FLOAT)
           OVER (PARTITION BY s.REGION ORDER BY s.SALE_ID)) r
ORDER BY s.REGION, r.SO_FAR NULLS LAST;
Result7 rows · 3 columns
REGIONSO_FARAT_THE_END
north100.0NULL
north145.0NULL
north145.0NULL
northNULL145.0
south250.5NULL
south338.75NULL
southNULL338.75

Two regions, each with its own running total and its own closing row. Notice the north region has three rows with a SO_FAR value in them even though only two of its rows carry an amount. The third is the guarded pass over the null row: the total did not move, and the handler yielded anyway.

Those NULLs are real nulls, not blanks. SO_FAR is null on the closing row and AT_THE_END is null on all the others, because each row only fills one of the two columns.

And how the final result looks once the filter is back

For completeness, here is the shape of the result with the filter in place, using a handler that just counts its own calls.

Create
CREATE OR REPLACE FUNCTION COUNTCALLS_T144B(N FLOAT)
RETURNS TABLE (CALLS INT)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'Counter'
AS
$$
class Counter:
    def __init__(self):
        self.calls = 0

    def process(self, n):
        self.calls += 1
        yield (self.calls,)
$$;
Result1 row · 1 column
status
Function COUNTCALLS_T144B successfully created.
Query
SELECT COUNT(*) AS ROWS_THE_UDTF_PRODUCED
FROM SALES_T144B s,
     TABLE(COUNTCALLS_T144B(s.AMOUNT::FLOAT)) c
WHERE s.AMOUNT IS NOT NULL;
Result1 row · 1 column
ROWS_THE_UDTF_PRODUCED
4
Query
SELECT COUNT(*) AS ROWS_IN_THE_TABLE,
       COUNT(AMOUNT) AS ROWS_WITH_AN_AMOUNT
FROM SALES_T144B;
Result1 row · 2 columns
ROWS_IN_THE_TABLEROWS_WITH_AN_AMOUNT
54

The table has five rows and four of them have an amount, and the filtered result has one row per row with an amount. That is only a description of the final shape. It cannot tell us whether the handler saw the fifth row, because the filter removes that row's output either way. The thing that told us the handler saw it is the TypeError above.

Key points

  • A Python UDF needs two lines a JavaScript one does not: RUNTIME_VERSION and HANDLER.
  • The body is a module. It can hold imports, helper functions and a class, and HANDLER says which one to call.
  • The handler is checked at create time. A name that is not in the body is 100357, no handler at all is 001480, and a runtime that is not on offer is 001422.
  • A handler with the wrong number of parameters is also caught at create time. None of those functions ever exists.
  • The parameter name does not matter. Binding is positional.
  • A NUMBER(38,0) argument arrives in Python as an int, which is why a thirty digit number survives.
  • JavaScript will not accept NUMBER(38,0) at all, which is 090215.
  • Declare the argument FLOAT and Python loses the same bottom bit JavaScript loses. The type is what decides, not the language.
  • A difference of zero computed in floating point is not evidence of anything. Read the side-by-side values instead.
  • NUMBER(10,2) arrives as a Decimal, and VARIANT arrives as a dict, a list or a plain value.
  • RETURNS NULL ON NULL INPUT is honoured, and under CALLED ON NULL INPUT a null arrives as None.
  • A handler with no return gives a SQL null.
  • A raise comes back as 100357 with the whole traceback and your own message in it.
  • Python that does not parse is refused at create time, which is the opposite of JavaScript.
  • PACKAGES is how you get a library, and an import with no PACKAGES clause fails when the function is created.
  • A Python UDTF is a class whose process method yields tuples.
  • process runs for rows the outer WHERE clause is going to discard, so guard your handler.
Next in this series

In the next post we move from functions to stored procedures. A procedure is a statement of its own rather than something you put in a query, it can change data, and the RETURNS clause that a SQL function quietly ignored turns out to be enforced.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The three way number comparison and the working running total were captured in a second pass, on their own tables, which is why their function names end in B. Every count in the text is read off one of the grids on this page. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake Python UDF documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.