Snowflake Features & Architecture Flashcards
User Defined Function (UDF)
User defined functions (UDF) are schema-level objects that enable users to write their own functions in four different languages: SQL, JavaScript, Python, and Java
-UDFs accept 0 or more parameters
-UDFs can return scalar or tabular results (UDTF)
-UDFs can be called as part of a SQL statement.
-UDFs can be overloaded.
JavaScript UDF
-JavaScript is specified with the language parameter.
-Enables use of high-level programming language features.
-JavaScript UDFs can refer to themselves recursively.
-Snowflake data types are mapped to JavaScript data types.
Java UDF
-Snowflake boots up a JVM to execute function written in Java.
-Snowflake currently supports writing UDFs in Java versions 8.x, 9.x, 10.x, and 11.x
-Java UDFs can specify their definition as in-line code or a pre-compiled jar file.
-Java UDFs cannot be designated as secure.
Create or Replace External Function
Create or Replace API Integration
External Function Call Lifecycle
External Function Limitations
-Slower due to not being to optimize outside process
-Scalar only
-Not shareable
-Less secure
-Egress charges
Stored Procedure
In Relational Database Management Systems (RDMS) stored procedures are named collections of SQL statements often containing procedural logic.
Snowflake Stored Procedure Options
- JavaScript
- Snowflake Scripting - SQL
- Snowpark - Python, Java, or Scala
Stored Procedure: JavaScript
Stored Procedures & UDFs
Sequences - Inset into a Table
Task
A task is an object used to schedule the execution of a SQL command or a stored procedure.
Task Workflow
Tree of Tasks