2021_01 Bundle¶

DUO Sign-in & MFA Enrollment¶

In this release, Snowflake updates the web interface and uses the DUO Web SDK to facilitate logging into Snowflake with single sign-on (SSO) and enrollment in multi-factor authentication (MFA).

Previously

Users can authenticate using DUO Push, entering a passcode from the DUO mobile application, or request SMS passcodes to their mobile device.

Currently

After enrollment, users can use the DUO mobile to authenticate using DUO Push, following instructions from a phone call to their mobile device, or entering a passcode from the DUO Mobile application.

Access to Persisted Large Query Results Reduced to 6 Hours¶

Snowflake persists (i.e. caches) the results of a query for a period of time. When this time period expires, the result is purged from the system. Snowflake uses persisted query results to avoid regenerating results when a query and the underlying table data have not changed (i.e. “retrieval optimization”).

With this release, the behavior of persisted query results has changed as follows:

Previously

The security token used by Snowflake clients to access persisted query results expired after 24 hours.

Currently

The security token used to access large persisted query results (i.e. more than 100 KB in size) expires after 6 hours. A new token can be retrieved to access results while they are still in cache.

Note that smaller persisted query results do not use an access token.

For persisted query results of all sizes, the cache expires after 24 hours.

Note that this change does not affect the token provided to the Snowflake Connector for Spark (“Spark connector”), which continues to expire after 24 hours regardless of the size of the persisted query results. The Spark connector leverages the longer cache expiration time to avoid timeouts in some use cases.

Transactions: Change to Automatic Aborting of Idle Blocking Transactions¶

If an automated process such as a script or a third-party tool starts a transaction in Snowflake and then fails, the transaction is left open and idle. This scenario can also occur if a session disconnects abruptly, leaving any transactions that are running in the session unable to commit or roll back. After a certain time, Snowflake aborts idle transactions automatically and rolls them back.

As announced previously, with this release, the guidelines that Snowflake follows to automatically abort idle transactions have changed as follows:

Previously

All idle transactions are aborted and rolled back after 4 hours by default.

Currently

Any transaction that blocks another transaction from acquiring a lock on the same table and is idle for 5 minutes is automatically aborted and rolled back.

Any idle transactions that do not block other transactions from modifying the same table and are older than 4 hours are aborted and rolled back.

VARIANT Data Type: Trailing Zeroes Removed When Casting VARIANT with Fixed-Point Value to VARCHAR¶

A VARIANT value can contain a fixed-point (NUMBER) value. When Snowflake casts such a VARIANT to VARCHAR, the result can contain trailing zeros, which can interfere with string comparison. For example, the numeric values 5 and 5.00 are equivalent in a numerical comparison, but the string values 5 and 5.00 are not equivalent in a string comparison.

As announced previously, with this release, the behavior of casting has changed as follows:

Previously

Casting a fixed-point value inside a VARIANT to a VARCHAR could result in varying numbers of trailing zeroes.

Currently

Casting a fixed-point value inside a VARIANT to a VARCHAR results in no trailing zeros.

Stored Procedures and UDFs: Upgrade to the JavaScript Engine — Non-bundle Change¶

As announced previously, the JavaScript engine for JavaScript stored procedures and user-defined functions (UDFs) has been upgraded to a newer version. The newer version of the engine improves performance and offers new JavaScript features from ECMAScript 2020.

As a result of this upgrade, the SfDate.toString method returns the full name of the time zone, rather than the abbreviation. For example, the method returns Coordinated Universal Time, rather than UTC.

Stored Procedures and Scoped Transactions — Non-bundle Change¶

With this release, Snowflake now issues an error message when implicitly rolling back an open transaction at the end of a stored procedure:

Previously

If a stored procedure contained an explicit or implicit BEGIN TRANSACTION statement, but did not have a corresponding explicit or implicit COMMIT or ROLLBACK, then Snowflake would silently execute an implicit rollback at the end of the stored procedure.

Currently

The implicit rollback is no longer silent; it is accompanied by an error message.

COPY_HISTORY View: Changes to Columns — Non-bundle Change¶

As announced previously, the following changes have been made in this release to the Account Usage COPY_HISTORY view:

POLICY_REFERENCES Table Function: New Column¶

As announced previously, with this release, Snowflake added a new column to the Information Schema POLICY_REFERENCES table function:

Snowflake JDBC Driver 3.12.17 (and Later): Session Timezone Used for Returned Dates and Times — Non-bundle Change¶

As announced previously, version 3.12.17 (and later) of the JDBC Driver now uses the timezone of the session (rather than the JVM timezone) for the getDate(), getTime(), and getTimestamp() methods of the ResultSet class:

Previously

getDate(), getTime(), and getTimestamp() used the timezone of the JVM for the returned Date, Time, and Timestamp objects.

Currently

getDate(), getTime(), and getTimestamp() use the timezone of the session for the returned Date, Time, and Timestamp objects.

This makes the results from the JDBC Driver consistent with the results from the web interface and other clients.

Snowflake JDBC Driver 3.13.0: JNA Classes No Longer Packaged — Non-bundle Change¶

As announced previously, version 3.13.0 of the JDBC Driver no longer packages the Java Native Access (JNA) classes with the JDBC Driver JAR file:

Previously

The JNA classes were included in the JDBC Driver JAR file.

Currently

The JNA classes are no longer included in the JDBC Driver JAR file.

Connection caching for browser-based SSO and multi-factor authentication (MFA) token caching require the JNA classes. If you are relying on this feature in your application, you must set up your application to depend on the following libraries:

• For macOS:

  • JNA

• For Microsoft Windows:

  • JNA

  • JNA Platform

Note that for systems that use the AArch64 architecture (e.g. the Apple M1 chip), you must use v5.7.0 (or later) of the JNA libraries. JNA versions prior to v5.7.0 are not compatible with Windows and macOS systems that run on AArch64.

COPY INTO <table> Command: LIMIT and TOP <n> Disallowed¶

The LIMIT or TOP <n> clause constrains the maximum number of rows returned by a query. The use of either clause in a COPY transformation (i.e. a query inside a COPY INTO <table> statement) is undocumented and has never been officially supported.

As announced previously, with this release, the behavior has changed as follows:

Previously

COPY INTO <table> statements that included either clause did not return an error. The statements constrained the maximum number of rows loaded into a table. However, the rows loaded were nondeterministic due to the unsupported ORDER BY clause and the parallel scanning of data.

Currently

COPY INTO <table> statements that include either clause return the following error:

LIMIT and TOP are not supported for COPY with transform during loading.