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));
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.
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 |
|
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:
|
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 "}
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 |
+-----+-------+
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.
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 This default value is ignored, and the value of the session parameter Passing |
Versions 2.4.6 and later |
In the Snowflake Connector for Python, the default value of the When the value is Passing |