2021_05 Bundle¶

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.

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));

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));

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.

Account-related Commands, Functions, & Interface: Changes to the Account Locator in Output¶

With this release, SQL commands, functions, and interface that include an account locator (formerly known as an auto-generated account name) in the output have changed as follows:

Previously

Returned only the account locator.

Currently

Returns the account name and account locator. If only one value can be shown, the new account name is displayed.

These changes are reflected in the following commands, functions, and pages in the classic web interface:

• Replication-related commands and functions:

  • SHOW REPLICATION ACCOUNTS

  • SHOW REPLICATION DATABASES

  • CURRENT_ACCOUNT

• Data sharing-related commands:

  • SHOW SHARES

• Pages in the classic web interface:

  • Shares

  • Databases

FUNCTIONS View: New Columns in View¶

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

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 *.

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

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:

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.

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 "}

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)

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       |
+-------+-----------+

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 |
+-----+-------+

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

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: