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
- Let's start with the smallest Python UDF
- Now let's see what the catalog says about it
- Now let's write a body that earns the word module
- What happens when the handler names a function that is not there?
- Now let's get the header wrong three more ways
- Does the parameter name have to match?
- Now let's send a big number through it
- Let's make the JavaScript one a float and try again
- Let's read the difference row carefully
- So what did Python actually receive?
- Now let's see what a null does
- What does a raise look like from SQL?
- What happens when the Python does not parse?
- Now let's pass some JSON in and out
- Can we use a library?
- Can a Python UDF read a table?
- Now let's run one over a table
- And a Python function that returns a table
- One last thing: process() runs for rows the WHERE is going to throw away
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 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);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);| 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 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
$$;| 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.
SELECT DOUBLE_T144(21) AS TWENTY_ONE_DOUBLED,
DOUBLE_T144(0) AS NOTHING_DOUBLED,
DOUBLE_T144(-1.5) AS A_NEGATIVE_DOUBLED;| TWENTY_ONE_DOUBLED | NOTHING_DOUBLED | A_NEGATIVE_DOUBLED |
|---|---|---|
| 42.0 | 0.0 | -3.0 |
Declared FLOAT, returns FLOAT, and that is why we get 42.0 rather than 42.
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.
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;| FUNCTION_NAME | ARGUMENT_SIGNATURE | DATA_TYPE | FUNCTION_LANGUAGE |
|---|---|---|---|
| DOUBLE_T144 | (N FLOAT) | FLOAT | PYTHON |
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.
DESCRIBE FUNCTION DOUBLE_T144(FLOAT);| property | value |
|---|---|
| signature | (N FLOAT) |
| returns | FLOAT |
| language | PYTHON |
| null handling | CALLED ON NULL INPUT |
| volatility | VOLATILE |
| body | def double_it(n): return n * 2 |
| imports | [] |
| handler | double_it |
| runtime_version | 3.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 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)))
$$;| 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.
SELECT ROUNDED_T144(17) AS SEVENTEEN,
ROUNDED_T144(42) AS FORTY_TWO;| SEVENTEEN | FORTY_TWO |
|---|---|
| 17.0 rounds to 20 and its square root is 4.123 | 42.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 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
$$;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.
SELECT BADHANDLER_T144(1) AS WHAT_CAME_BACK;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 OR REPLACE FUNCTION NOHANDLER_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
AS
$$
def double_it(n):
return n * 2
$$;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 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
$$;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 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
$$;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.
SELECT WRONGARITY_T144(10) AS WHAT_CAME_BACK;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 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
$$;| status |
|---|
| Function DIFFERENTNAME_T144 successfully created. |
Created without a word. Let's call it.
SELECT DIFFERENTNAME_T144(10) AS WHAT_CAME_BACK;| 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 OR REPLACE FUNCTION UPPERPARAM_T144(amount FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(AMOUNT):
return AMOUNT * 2
$$;| status |
|---|
| Function UPPERPARAM_T144 successfully created. |
SELECT UPPERPARAM_T144(10) AS WHAT_CAME_BACK;| 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.
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 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
$$;| 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 OR REPLACE FUNCTION JSECHO_T144(N NUMBER(38,0))
RETURNS NUMBER(38,0)
LANGUAGE JAVASCRIPT
AS
$$
return N;
$$;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.
SELECT 9007199254740991 AS WHAT_I_SENT,
ECHO_T144(9007199254740991) AS THROUGH_PYTHON,
JSECHO_T144(9007199254740991) AS THROUGH_JAVASCRIPT;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 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
$$;| status |
|---|
| Function PYECHO_T144B successfully created. |
And the JavaScript one, with the same declaration, one more time.
CREATE OR REPLACE FUNCTION JSECHO_T144B(N NUMBER(38,0))
RETURNS NUMBER(38,0)
LANGUAGE JAVASCRIPT
AS
$$
return N;
$$;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 OR REPLACE FUNCTION JSFLOAT_T144B(N FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
return N;
$$;| status |
|---|
| Function JSFLOAT_T144B successfully created. |
And a Python one declared FLOAT too, so we are comparing the type and not the language.
CREATE OR REPLACE FUNCTION PYFLOAT_T144B(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
return n
$$;| 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.
SELECT 9007199254740991 AS WHAT_I_SENT,
PYECHO_T144B(9007199254740991) AS PYTHON_NUMBER,
PYFLOAT_T144B(9007199254740991) AS PYTHON_FLOAT,
JSFLOAT_T144B(9007199254740991) AS JAVASCRIPT_FLOAT;| WHAT_I_SENT | PYTHON_NUMBER | PYTHON_FLOAT | JAVASCRIPT_FLOAT |
|---|---|---|---|
| 9007199254740991 | 9007199254740991 | 9007199254740991.0 | 9007199254740991.0 |
All three agree. Nothing interesting yet, and that is the point of starting here.
Now the very next odd integer above it.
SELECT 9007199254740993 AS WHAT_I_SENT,
PYECHO_T144B(9007199254740993) AS PYTHON_NUMBER,
PYFLOAT_T144B(9007199254740993) AS PYTHON_FLOAT,
JSFLOAT_T144B(9007199254740993) AS JAVASCRIPT_FLOAT;| WHAT_I_SENT | PYTHON_NUMBER | PYTHON_FLOAT | JAVASCRIPT_FLOAT |
|---|---|---|---|
| 9007199254740993 | 9007199254740993 | 9007199254740992.0 | 9007199254740992.0 |
The NUMBER one came back exactly as we sent it. Both float ones came back one lower.
And now thirty digits.
SELECT 123456789012345678901234567890 AS WHAT_I_SENT,
PYECHO_T144B(123456789012345678901234567890) AS PYTHON_NUMBER;| WHAT_I_SENT | PYTHON_NUMBER |
|---|---|
| 123456789012345678901234567890 | 123456789012345678901234567890 |
Every digit survived the round trip through Python.
The same number through the two float functions, for comparison.
SELECT PYFLOAT_T144B(123456789012345678901234567890) AS PYTHON_FLOAT,
JSFLOAT_T144B(123456789012345678901234567890) AS JAVASCRIPT_FLOAT;| PYTHON_FLOAT | JAVASCRIPT_FLOAT |
|---|---|
| 1.2345678901234568e+29 | 1.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.
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;| PYTHON_NUMBER_IS_OUT_BY | PYTHON_FLOAT_IS_OUT_BY | JAVASCRIPT_IS_OUT_BY |
|---|---|---|
| 0 | 0.0 | 0.0 |
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 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__
$$;| status |
|---|
| Function PYTYPE_T144B successfully created. |
SELECT PYTYPE_T144B(9007199254740993) AS WHAT_PYTHON_RECEIVED;| 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 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))
$$;| status |
|---|
| Function WHATTYPES_T144 successfully created. |
SELECT WHATTYPES_T144(1.50, 'text', TRUE, '2026-01-15'::DATE,
PARSE_JSON('{"a":1}')) AS WHAT_PYTHON_SAW;| 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 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
$$;| status |
|---|
| Function CALLED_T144 successfully created. |
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
$$;| 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.
SELECT CALLED_T144(3) AS THE_CALLED_ONE,
STRICT_T144(3) AS THE_STRICT_ONE;| THE_CALLED_ONE | THE_STRICT_ONE |
|---|---|
| the body ran and n was 3.0 | the body ran and n was 3.0 |
Both ran, and both saw the same thing. Now a null.
SELECT CALLED_T144(NULL) AS THE_CALLED_ONE,
STRICT_T144(NULL) AS THE_STRICT_ONE;| THE_CALLED_ONE | THE_STRICT_ONE |
|---|---|
| the body ran and n was None | NULL |
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 OR REPLACE FUNCTION RETURNSNOTHING_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
x = n * 2
$$;| status |
|---|
| Function RETURNSNOTHING_T144 successfully created. |
SELECT RETURNSNOTHING_T144(5) AS WHAT_CAME_BACK,
RETURNSNOTHING_T144(5) IS NULL AS IS_IT_NULL;| WHAT_CAME_BACK | IS_IT_NULL |
|---|---|
| NULL | TRUE |
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 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
$$;| status |
|---|
| Function RAISES_T144 successfully created. |
Let's give it a number it likes.
SELECT RAISES_T144(16) AS SIXTEEN_ROOTED;| SIXTEEN_ROOTED |
|---|
| 4.0 |
That is the path that does not raise. Now a negative.
SELECT RAISES_T144(-1) AS A_NEGATIVE;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 f100357, 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 OR REPLACE FUNCTION BROKEN_T144(N FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(n):
return n *
$$;100357 (P0000): Python Interpreter Error:
File "_udf_code.py", line 3
return n *
^
SyntaxError: invalid syntax
in function BROKEN_T144 with handler fRefused at create, with a caret under the bad line and SyntaxError: invalid syntax.
SELECT BROKEN_T144(1) AS WHAT_CAME_BACK;002139 (02000): SQL compilation error: Unknown function BROKEN_T144.
Nothing was created, so there is nothing to call.
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 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__
$$;| status |
|---|
| Function INSIDE_T144 successfully created. |
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;| AN_OBJECT | AN_ARRAY | A_NUMBER |
|---|---|---|
| a dict with keys: amount,region | a list of 3 things | a 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 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}
$$;| status |
|---|
| Function BUILD_T144 successfully created. |
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;| THE_OBJECT | THE_REGION | WHAT_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 OR REPLACE FUNCTION SPLIT_T144(S VARCHAR)
RETURNS ARRAY
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'f'
AS
$$
def f(s):
return s.split(",")
$$;| status |
|---|
| Function SPLIT_T144 successfully created. |
SELECT SPLIT_T144('north,south,east') AS THE_ARRAY,
ARRAY_SIZE(SPLIT_T144('north,south,east')) AS HOW_MANY;| THE_ARRAY | HOW_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 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]))
$$;| status |
|---|
| Function MEDIAN_T144 successfully created. |
SELECT MEDIAN_T144(ARRAY_CONSTRUCT(1, 2, 3, 10, 100)) AS THE_MEDIAN;| 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 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
$$;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 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))
$$;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 fModuleNotFoundError, 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.
SELECT UNDECLARED_T144(16) AS SIXTEEN_ROOTED;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 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]
$$;| status |
|---|
| Function READSATABLE_T144 successfully created. |
Created, with no complaint at all. Which is encouraging and also misleading, so let's call it.
SELECT READSATABLE_T144() AS WHAT_CAME_BACK;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 fA 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.
SELECT SALE_ID,
AMOUNT,
ROUNDED_T144(AMOUNT) AS WHAT_PYTHON_SAID
FROM SALES_T144
WHERE AMOUNT IS NOT NULL
ORDER BY SALE_ID;| SALE_ID | AMOUNT | WHAT_PYTHON_SAID |
|---|---|---|
| 1 | 95.00 | 95.0 rounds to 100 and its square root is 9.747 |
| 2 | 75.00 | 75.0 rounds to 80 and its square root is 8.660 |
| 3 | 55.00 | 55.0 rounds to 60 and its square root is 7.416 |
| 4 | 10.00 | 10.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 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)
$$;| status |
|---|
| Function SPLITROWS_T144 successfully created. |
SELECT THE_PIECE, WHICH_ONE
FROM TABLE(SPLITROWS_T144('north,south,east'))
ORDER BY WHICH_ONE;| THE_PIECE | WHICH_ONE |
|---|---|
| north | 0 |
| south | 1 |
| east | 2 |
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.
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;| SALE_ID | THE_PIECE |
|---|---|
| 1 | north |
| 1 | everywhere |
| 2 | south |
| 2 | everywhere |
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 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)
$$;| 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.
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;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 SummerTypeError, 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.
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 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)
$$;| 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 OR REPLACE TABLE SALES_T144B (
SALE_ID INT,
REGION VARCHAR(10),
AMOUNT NUMBER(10,2)
);| status |
|---|
| Table SALES_T144B successfully created. |
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);| number of rows inserted |
|---|
| 5 |
And now the same query works.
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;| REGION | SO_FAR | AT_THE_END |
|---|---|---|
| north | 100.0 | NULL |
| north | 145.0 | NULL |
| north | 145.0 | NULL |
| north | NULL | 145.0 |
| south | 250.5 | NULL |
| south | 338.75 | NULL |
| south | NULL | 338.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 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,)
$$;| status |
|---|
| Function COUNTCALLS_T144B successfully created. |
SELECT COUNT(*) AS ROWS_THE_UDTF_PRODUCED
FROM SALES_T144B s,
TABLE(COUNTCALLS_T144B(s.AMOUNT::FLOAT)) c
WHERE s.AMOUNT IS NOT NULL;| ROWS_THE_UDTF_PRODUCED |
|---|
| 4 |
SELECT COUNT(*) AS ROWS_IN_THE_TABLE,
COUNT(AMOUNT) AS ROWS_WITH_AN_AMOUNT
FROM SALES_T144B;| ROWS_IN_THE_TABLE | ROWS_WITH_AN_AMOUNT |
|---|---|
| 5 | 4 |
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_VERSIONandHANDLER. - The body is a module. It can hold imports, helper functions and a class, and
HANDLERsays 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 is001480, and a runtime that is not on offer is001422. - 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 anint, which is why a thirty digit number survives. - JavaScript will not accept
NUMBER(38,0)at all, which is090215. - Declare the argument
FLOATand 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 aDecimal, andVARIANTarrives as adict, alistor a plain value.RETURNS NULL ON NULL INPUTis honoured, and underCALLED ON NULL INPUTa null arrives asNone.- A handler with no
returngives a SQL null. - A
raisecomes back as100357with 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.
PACKAGESis how you get a library, and an import with noPACKAGESclause fails when the function is created.- A Python UDTF is a class whose
processmethodyields tuples. processruns for rows the outerWHEREclause is going to discard, so guard your handler.
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.