SnowDDL
Search…
FUNCTION
Config path: /<database>/<schema>/function/<name>(<dtypes>).yaml
Example:
arguments:
input_val: OBJECT
​
returns: VARCHAR(1000)
​
body: |-
GET(input_val, COALESCE('BOOKINGS_LANG', 'en'))::varchar(1000)
arguments:
x: VARCHAR(10000)
​
returns: VARCHAR(255)
language: java
​
imports:
- stage: test_internal_stage
path: /lib/zero-allocation-hashing-0.15.jar
​
handler: SnowHash.xxHash
​
body: |-
import net.openhft.hashing.LongHashFunction;
​
class SnowHash {
public static LongHashFunction hash_func = LongHashFunction.xx();
​
public static String xxHash(String x) {
return Long.toHexString(hash_func.hashChars(x));
}
}
​
language: python
runtime_version: "3.8"
​
returns: VARIANT
​
packages:
- numpy
- pandas
- xgboost==1.5.0
​
handler: udf
​
body: |-
import numpy as np
import pandas as pd
import xgboost as xgb
​
def udf():
return [np.__version__, pd.__version__, xgb.__version__]
​

Schema

  • language (str) - language of function (default: SQL)
  • runtime_version (string) - used to specify version of Python, Java, etc.
  • arguments (dict)
    • {key} (ident) - argument name
    • {value} (str) - argument data type
  • returns (str) - for single return value, return data type --- OR ---
  • returns (dict) - for table return values
    • {key} (ident) - return column name
    • {value} (str) - return column data type
  • body (str) - function body
  • is_secure (bool) - is function SECURE
  • is_strict (bool) - is function STRICT (always returns NULL on NULL input)
  • is_immutable (bool) - is function IMMUTABLE (same input always produced the same output)
  • imports (list) - files to import (usually JAR packages)
    • {items} (dict)
      • stage (ident) - name of stage
      • path (str) - path to file
  • packages (list) - Snowflake system packages to import as dependencies
    • {items} (str) - name of package, with optional version of package
  • handler (str) - name of class and method to be called
  • comment (str)

Usage notes

  1. 1.
    Snowflake supports overloading of function names. Multiple functions may have the same name as long as they have different arguments. It is required to use comma-separated base data types of arguments in config names. For example: my_function(number).yaml, my_function(varchar,number).yaml
  2. 2.
    Files for imports should be maintained using STAGE FILES.
  3. 3.
    If function body is empty, handler and imports with pre-compiled JAR or Python code are required.
  4. 4.
    runtime_version should be specified as string with explicit double-quotes (e.g. "3.8"). Otherwise YAML parser may confuse it with number, which may cause some unwanted effects.

Links

​
Copy link
On this page
Schema
Usage notes
Links