May 08-09, 2024 — 8.18 Release Notes¶

Dynamic pivot available¶

A dynamic pivot query uses the ANY keyword or a subquery in the PIVOT subclause instead of specifying the pivot values explicitly. With dynamic pivot, the pivot values are determined at runtime based on the use case.

Added support for structured data types in UDFs¶

Structured data types are now supported in user-defined functions (UDFs) created in Java, Python, and Scala. For information about data type mappings for structured data types, see Data Type Mappings Between SQL and Handler Languages.

New SQL functions¶

The following function(s) are now available with this release:

Python user-defined aggregate functions — Preview¶

With this release, Snowflake is pleased to announce the public preview of support for writing user-defined aggregate functions (UDAFs) with a Python handler. You can use Snowpark Python APIs to create and call user-defined aggregate functions (UDAFs), which take one or more rows as input and produce a single row of output. A UDAF operates on values across multiple rows to perform mathematical calculations such as sum, average, counting, finding minimum or maximum values, standard deviation, and estimation, as well as some non-mathematical operations.

For more information, see:

• Python user-defined aggregate functions

  (for SQL- and Python-based instructions)

• Creating User-Defined Aggregate Functions (UDAFs) for DataFrames in Python

  (for a Snowpark Python-based guide)

Access to external network locations on AWS in the Gov region — Preview¶

With this release, Snowflake is pleased to announce the public preview of access to external network locations from function and procedure handlers for code deployed in the AWS Gov region.

When setting up external network access, you create a network rule that represents the external network location. If your handler code will need to authenticate with the external location, you create a secret containing the credentials needed. In handler code, you can use APIs to retrieve credential values from the secret.

For more information, see External network access overview.