August 11-14, 2024 — 8.30 Release Notes¶

Outbound private connectivity with Azure External Network Access and External Functions — Preview¶

With this release, we are pleased to announce support for Snowflake accounts on Microsoft Azure to use outbound private connectivity with two features:

• External Network Access

• External Functions

Outbound private connectivity enables you to use Azure Private Link from the VNet that hosts your Snowflake account to connect to an Azure resource using Azure Private Link.

You can configure external network access to use Azure Private Link to connect to external service from UDF/UDTF or stored procedures within Snowpark when you call the stored procedure to connect to the external location. The hostname of the external service is used to provision a private endpoint. The network rule of type PRIVATE_HOST_PORT enables the external access integration to use Azure Private Link. The hostname and the external access integration are then specified in the stored procedure that you create. This allows you to call the stored procedure in Snowflake and use Azure Private Link to connect to the external service.

You can configure external functions in Snowflake to use Azure Private Link to connect to the external service via Azure API Management, using both the Azure Portal and the Azure ARM template. Your Azure subscription and hostname for the API Management service are used to map your external service to the private endpoint that you provision. These are the same values that you specify in the API integration for the external function. This allows you to call an external function in Snowflake and use Azure Private Link to connect to the external service.

For more information, see:

• Private connectivity with external functions: Azure ARM template

• Private connectivity with external functions: Azure Portal

• External network access and private connectivity on Microsoft Azure

• Introduction to private connectivity

Full-text search - Preview¶

With this release, we are pleased to announce the preview of a new full-text search feature which is now available. To use full-text search, call a new SEARCH function to find character data (text) in specified columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns. In most cases, you call the SEARCH function by specifying it in the SELECT list or the WHERE clause of a SELECT statement.

The SEARCH function supports token-based text search across multiple columns (or all columns) of one or more tables, which is a good solution for the following use cases:

• Searching for text in data with an inherent structure, where tokens naturally correspond to words, fields, or message components. Token searches can exactly match the specified text in a large amount of data, which results in fewer false positives and simpler queries. For example, a token search for “unauthorized access” in the system logs finds case-insensitive instances of “unauthorized” and “access” but does not find instances of “authorized” or “accessible.”

• Searching for text without knowing the exact location of relevant data. Because full-text search supports wildcard searches, you can search for relevant text in a set of columns or entire tables without writing complex SQL queries. For example, you can use full-text search to search for a list of email addresses and usernames in a table.

To improve the performance of full-text search queries, you can optionally enable FULL_TEXT search optimization on a specific column or set of columns in a table. To do so, run an ALTER TABLE…ADD SEARCH OPTIMIZATION ON FULL_TEXT statement.

For more information about full-text search, see Using full-text search. For more information about search optimization for full-text search queries, see Enabling and disabling search optimization.

Setting users as SNOWFLAKE_SUPPORT users no longer supported¶

With this release, you can no longer set a user’s SUPPORT_USER attribute using the CREATE USER or ALTER USER commands.

Users with SNOWFLAKE_SUPPORT set to TRUE remain support users until you drop them. Snowflake can access these users through support processes.

RANGE BETWEEN with explicit offsets: Additional window functions supported¶

With this release, we are pleased to announce that the following additional window functions support RANGE BETWEEN window frames with explicit offsets:

• STDDEV, STDDEV_SAMP, STDDEV_POP (and aliases)

• VARIANCE , VARIANCE_SAMP, VARIANCE_POP (and aliases)

• COUNT_IF

For example, you can calculate standard deviation values for a column and specify a RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING window frame.

For more information about window frame syntax, see Window function syntax and usage.

UNDROP command: Support for restoring objects using ID¶

With this release, we are pleased to announce support for the UNDROP command to restore tables, schemas, and databases using an object ID. For example, if you have dropped multiple tables with the same name, you can use this feature to restore a specific table using the table ID. The table is restored with its original name.

For more information, see the following topics:

• UNDROP TABLE

• UNDROP SCHEMA

• UNDROP DATABASE

Wildcard filtering for functions¶

When you specify a wildcard (*) as an argument in a call to a function, you can now use the ILIKE and EXCLUDE keywords for filtering in a SELECT list or GROUP BY clause.

For example, the following call to the COUNT function is now valid:

SELECT COUNT(* ILIKE 'col1%') FROM mytable;

The following call to the OBJECT_CONSTRUCT function is also valid:

SELECT OBJECT_CONSTRUCT(* EXCLUDE col1) AS oc FROM mytable;

The ILIKE and EXCLUDE keywords are now also valid in object constants. For example:

SELECT {* ILIKE 'col1%'} FROM mytable;

SELECT {* EXCLUDE col1} FROM mytable;

For more information, see OBJECT constants.

Loading unstructured data with Document AI — Preview¶

With this release, we are pleased to announce the preview of loading unstructured data with Document AI. By integrating with Document AI, Snowflake now supports loading unstructured data, similar to loading structured and semi-structured data. To load unstructured data with this preview feature, you can run the same COPY INTO table command with a new copy option file_processor.

For more information, see Loading unstructured data with Document AI.