Preview — 8.43 Release Notes¶

Full-text search — General availability¶

Full-text search is now generally available. To use full-text search, call the new SEARCH and SEARCH_IP functions to find character data (text) and IP addresses in specified columns from a table, including elements in VARIANT, OBJECT, and ARRAY columns. In most cases, you call the 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 a table, 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 than substring searches. For example, a token search for “unauthorized access” in the system logs finds case-insensitive instances of “unauthorized” and “access” but doesn’t find instances of “authorized” or “accessible.”

  In addition, for these cases, SEARCH is typically faster than comparable queries that use ILIKE.

• Searching for text without knowing the exact location of relevant data. Because full-text search supports a column wildcard, 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 usernames in a table.

The SEARCH_IP function searches for valid IPv4 addresses in specified character-string columns, including elements in VARIANT, OBJECT, and ARRAY columns. The search can find matches for a single IP address or a CIDR range of IP addresses in a large amount of data.

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, execute an ALTER TABLE … ADD SEARCH OPTIMIZATION ON FULL_TEXT statement. The resulting access path is generally faster and cheaper to build, and requires less storage on disk than ON SUBSTRING.

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.

EXECUTE IMMEDIATE FROM: Support for using content from staged files in templates¶

With this release, in a Jinja2 template, you can include, import, inherit from, and read content from other files on a stage.

You can use Jinja2’s include, import, and inheritance features or call the SnowflakeFile API to use content from files on a stage. This enables you to make your templates more modular. For example, you can define macros in a common file and use those macros in different templates.

Automatic logging and tracing for Snowflake Scripting stored procedures¶

With this release, you can automatically log and emit trace information about the execution of a Snowflake Scripting stored procedure. The additional log information includes the BEGIN/END of a Snowflake Scripting block and a child job request. The additional types of trace events include exception catching, information about child job execution, child job statistics, and stored procedure statistics, including execution time and input values. By using this feature, you can generate this additional information without modifying the body of the stored procedure.

To use the feature, set the new AUTO_EVENT_LOGGING parameter to LOGGING, TRACING, or ALL using the ALTER PROCEDURE command.

ACCOUNT_USAGE: New SERVERLESS_ALERT_HISTORY view¶

With this release, we are pleased to announce the SERVERLESS_ALERT_HISTORY view in the ACCOUNT_USAGE schema of the shared SNOWFLAKE database. You can query this view to get information about the credits used for serverless alerts.

Authentication with AWS IAM from procedures and functions — General availability¶

With this release, we are pleased to announce general availability of support for authenticating with AWS services from a procedure or functions using Snowpark External Access via Identity and Access Management (IAM).