2021_05 Bundle

The following deprecated features and behavior changes were introduced this month. If you have any questions about these changes, please contact Snowflake Support.

For more details about the new features, enhancements, and fixes introduced this month, see Junho de 2021.

Importante

Unless otherwise noted, these changes were enabled through the 2021_05 bundle in Release 5.23.

Platform Changes

Microsoft Azure Subnet Expansion

With this release, Snowflake adds additional subnets in your Snowflake account’s Microsoft Azure virtual network for improved scalability. These new subnets can affect access to external stages that reference Azure cloud storage, Customer Master Keys access for Tri-Secret Secure, and Azure storage queues access for the auto-ingest Snowpipe.

Please update your network and firewall settings to allow Snowflake access from these additional subnets.

To locate the additional subnets, execute the SYSTEM$GET_SNOWFLAKE_PLATFORM_INFO function in your Snowflake account.

For additional help, please contact your internal Microsoft Azure administrator.

SQL Changes: Commands & Functions

CREATE TABLE Command: Change to Enforcement of PRIMARY KEYS created by Command

With this release, Snowflake changes enforcement of primary key constraints created at the statement level.

In Snowflake, a PRIMARY KEY constraint is implemented as a NOT NULL constraint. (Snowflake does not enforce unique values, even when a PRIMARY KEY constraint is declared.)

The CREATE TABLE command allows PRIMARY KEY constraints to be declared at either the column level or the statement level. The following statement creates the constraint at the column level:

create or replace table temp_table (co1 number(10) primary key, col2 varchar(255));
Copy

The following statement creates the constraint at the statement level:

create or replace table temp_table (col1 number(10), col2 varchar(255), primary key(col1));
Copy

These two statements should be equivalent.

However, due to an error, Snowflake did not create the NOT NULL constraint when a PRIMARY KEY was declared using statement-level syntax.

This error has been corrected.

Previously

When a CREATE TABLE statement declared a PRIMARY KEY constraint at the statement level, the implied NOT NULL constraint was not created.

Currently

When a CREATE TABLE statement declares a PRIMARY KEY constraint at the statement level, the implied NOT NULL constraint is created.

Most Snowflake users should not need to change their tables or SQL statements to take into account this change. However, some users might need or want to make changes.

  • If you have scripts that you run repeatedly and that create tables that specify statement-level PRIMARY KEY constraints but that actually put NULL values into the corresponding table(s), you should either:

    • Modify your script to remove the PRIMARY KEY constraint.

    • Avoid putting NULL values into the table(s).

  • If you have tables that you have already created and that were intended to have an implied NOT NULL constraint but that might not have it, you might wish to execute an ALTER TABLE … ALTER COLUMN statement to add the NOT NULL constraint. (This will give an error message if there are NULL values in the column, in which case you’ll typically want to update the NULL values and then re-try the ALTER TABLE statement.)

Database Replication: AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY Parameter: Obsoleted

The AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY parameter for database objects specifies whether to perform automatic background maintenance of materialized views in the secondary database.

With this release, the AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY parameter is now obsoleted and the behavior has changed as follows:

  • Executing a CREATE DATABASE or ALTER DATABASE statement that specifies the AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY produces a user error.

  • If the parameter has been set on existing secondary databases, it is ignored.

Automatic maintenance of materialized views is enabled by default on all new and existing secondary databases.

LIKE Operator / Function: Change to Handling of Non-String Subjects

With this release, we have changed the way in which the LIKE operator and function work. LIKE compares a subject with a pattern:

<subject> LIKE <pattern> [ ESCAPE <escape> ]

LIKE( <subject> , <pattern> [ , <escape> ] )

In cases when the subject is not a string (e.g. the subject is a boolean) and the pattern is a constant string without any SQL wildcards (“ _ “ or “ % “), the behavior of LIKE has changed as follows:

Previously

The pattern was cast to the same type as the subject before the comparison is made.

For example, in the following LIKE comparison, the subject is a boolean:

select false like 'False';

In this example, the pattern ( 'False' ) was cast to a boolean, and the result of the comparison was true.

As another example, the following comparison failed because the pattern cannot be cast to a boolean:

select false like 'eFalse';

Boolean value 'eFalse' is not recognized

Currently

The subject and pattern are both cast to strings before the comparison is made.

This behavior is consistent with cases in which the pattern contains SQL wildcards (“ _ “ or “ % “).

For example, in the following LIKE comparison, the subject is a BOOLEAN:

select false like 'False';

In this example, the subject ( false ) is cast to a string , and the result is false.

As another example, the following comparison does not fail:

select false like 'eFalse';

In this example, the pattern is already a string but the subject is a boolean. The subject ( false ) is cast to a string , and the result of the comparison is false.

CREATE MANAGED ACCOUNT Command: New Account Name Field in Output

With this release, the output of the CREATE MANAGED ACCOUNT command changes as follows:

Previously

Returned the account locator URL.

Currently

Returns the account name URL and account locator URL. Various system URLs, such as resource monitors, activation token, reset password URL use the account name URL, as well as account locator URL.

Nota

SQL scripts that use the loginURL field of the CREATE MANAGED ACCOUNT command can use either URL (the new URL format that is based on the custom account name), or accountLocatorURL (an auto-generated URL).

SQL Changes: Information Schema

FUNCTIONS View: New Columns in View

With this release, Snowflake adds columns to the FUNCTIONS Information Schema view:

View Name

New Column Name

FUNCTIONS

  • handler

  • imports

  • target_path

These new columns correspond to options that users can specify in the CREATE FUNCTION command.

To help limit the impact of this change, the new columns are added as the last columns in the output. Existing columns in the views are unchanged.

To reduce the impact of similar changes in the future, Snowflake recommends selecting specific columns rather than using SELECT *.

Data Loading / Unloading Changes

Semi-structured Data: Improved Support for NULL_IF and TRIM_SPACE File Format Options When Querying or Loading

Nota

This behavior change was included in the 2021_05 behavior change bundle but is currently disabled independent of the bundle. The change is delayed until further notice. If we enable this behavior change in the future, it will be subject to the standard pending behavior change process to provide the best possible user experience.

With this release , querying semi-structured data in staged files or loading it into relational tables using a SELECT statement (i.e. a COPY transformation) includes improved support for the NULL_IF and TRIM_SPACE file format options.

The file format options are supported for the following semi-structured data types:

  • JSON

  • Avro

  • ORC

  • Parquet

The file format options are defined as follows:

File Format Option

Description

NULL_IF

Comma-separated list of strings to replace with SQL NULL.

For example:

NULL_IF = ('\\N', 'NULL', 'NUL', '')

TRIM_SPACE

Boolean that specifies whether to remove white space from fields. Set this option to TRUE to remove undesirable leading and trailing spaces during the data load.

The behavior has changed as follows:

Previously

Querying semi-structured data in staged files did not support either file format option.

Loading semi-structured data into separate columns in a relational table only supported these file format options when object values were explicitly cast to the data types of the target columns. In addition, the NULL_IF file format option for semi-structured data was only applied to string values.

Currently

Querying object values in staged semi-structured data files applies settings for both file format options to the output.

Loading semi-structured data into a relational table applies these file format options without having to explicitly cast individual column values to the data type of the target columns.

In addition, the NULL_IF file format option is applied to any data type in an object value, similar to the handling for CSV data.

Nota

This behavior change does not apply to loads of semi-structured data into relational tables using the MATCH_BY_COLUMN_NAME copy option. This use case remains the same.

The following example shows the previous and current behaviors. The example uses example JSON data in a file named data.json :

{"c1": NULL,"c2": " foo "},
{"c1": 2,"c2": NULL},
{"c1": 3,"c2": " bar "}
Copy

Create the Snowflake objects used in the example:

-- Create a target relational table for the COPY transformation

CREATE OR REPLACE TABLE t (c1 NUMBER, c2 STRING);

-- Create a JSON file format that enables TRIM_SPACE and defines NULL_IF values

CREATE OR REPLACE FILE FORMAT ff TYPE = 'JSON' TRIM_SPACE = TRUE NULL_IF = ('NULL', 'bar', '3');

-- Create a stage to store the example JSON file

-- Apply the new file format to the stage

CREATE OR REPLACE STAGEs FILE_FORMAT = ff;

-- Execute a PUT command to stage the example JSON file (not shown)
Copy

The following query and COPY transformation statement show the previous behavior:

-- Query the staged JSON file

-- The NULL_IF and TRIM_SPACE file format options are not applied

SELECT $1:c1, $1:c2 FROM @s/data.json.gz (file_format => ff);

+-------+-----------+
| $1:C1 | $1:C2     |
|-------+-----------|
| NULL  | " foo "   |
| 2     | NULL      |
| 3     | " bar "   |
+-------+-----------+

--Execute a COPY transformation to load the staged JSON data into the target relational table

COPY INTO t(c1, c2) FROM (SELECT t.$1:c1, t.$1:c2 FROM @s/data.json.gz t);

--Query the loaded data

-- The NULL_IF and TRIM_SPACE file format options are not applied

SELECT * FROM t;

+-------+-----------+
| C1    | C2        |
|-------+-----------|
| NULL  | foo       |
| 2     | NULL      |
| 3     | bar       |
+-------+-----------+
Copy

The following query and COPY transformation statement show the current behavior:

-- Query the staged JSON file

-- The NULL_IF and TRIM_SPACE file format options are applied

SELECT $1:c1, $1:c2 FROM @s/data.json.gz (file_format => ff);

+-------+-------+
| $1:C1 | $1:C2 |
|-------+-------|
| NULL  | foo   |
| 2     | NULL  |
| NULL  | NULL  |
+-------+-------+

--Execute a COPY transformation to load the staged JSON data into the target relational table

COPY INTO t(c1, c2) FROM (SELECT t.$1:c1, t.$1:c2 FROM @s/data.json.gz t);

--Query the loaded data

-- The NULL_IF and TRIM_SPACE file format options are applied

SELECT * FROM t;

+------+------+
| C1   | C2   |
|------+------|
| NULL | foo  |
| 2    | NULL |
| NULL | NULL |
+-----+-------+
Copy

Data Pipeline Changes

Tasks: Conditional Task Runs Fail When Evaluating a Stale Stream

The optional WHEN clause in a task definition specifies a Boolean SQL expression that the task must evaluate as TRUE before it runs. The expression supports the SYSTEM$STREAM_HAS_DATA function, which indicates whether a specified stream contains change tracking data.

With this release, when the stream referenced in a WHEN expression has become stale , the behavior of the conditional task run has changed as follows:

Previously

The task run was skipped.

The TASK_HISTORY Account Usage view and TASK_HISTORY table function in the Information Schema indicated that the task run was skipped.

Currently

The task run fails and produces an error.

The TASK_HISTORY view and table function display the task state as FAILED with the following error code for the task run:

091092: The stream '<stream_name>' has become stale. Please recreate the stream using CREATE OR REPLACE STREAM.
Copy

Other Implemented Behavior Changes

These changes are enabled by means other than a bundle (e.g. the change is introduced by installing or upgrading to the specified version of a connector or driver). Because these changes are not included in a bundle, they cannot be enabled (for testing purposes) or disabled.

Snowflake Connector for Python 2.4.6: Change to the Default Value of client_session_keep_alive

For the Snowflake Connector for Python, Snowflake provides two settings that you can use to prevent Snowflake from prompting the user to log in again after a period of inactivity during a session:

  • The session parameter CLIENT_SESSION_KEEP_ALIVE. This parameter also affects other connectors and drivers (JDBC, ODBC, and Node.js).

  • The client_session_keep_alive parameter in the snowflake.connector.connect method. This setting is specific to the Snowflake Connector for Python.

Version 2.4.6 of the Snowflake Connector for Python changes the default value of the client_session_keep_alive parameter in the connect method:

Versions 2.4.5 and earlier

In the Snowflake Connector for Python, the default value of the client_session_keep_alive parameter in the connect method is False.

This default value is ignored, and the value of the session parameter CLIENT_SESSION_KEEP_ALIVE is used instead.

Passing client_session_keep_alive=False to the connect method does not override the value TRUE for the CLIENT_SESSION_KEEP_ALIVE session parameter.

Versions 2.4.6 and later

In the Snowflake Connector for Python, the default value of the client_session_keep_alive parameter in the connect method is None .

When the value is None , the value of the session parameter CLIENT_SESSION_KEEP_ALIVE is used.This is equivalent to the default behavior of versions 2.4.5 and earlier of the Snowflake Connector for Python.

Passing client_session_keep_alive=False or True to the connect method overrides the value of the session parameter CLIENT_SESSION_KEEP_ALIVE.