2021_02 Bundle¶

Casting Strings to DATE, TIME, and TIMESTAMP Values: Changes to the Fallback Behavior¶

With this release, we have changed the way in which failures are handled when casting strings to DATE, TIME, and TIMESTAMP values.

When casting a string to a DATE, TIME, or TIMESTAMP value, you can specify the expected input format of the string (either as an argument or in a parameter, such as DATE_INPUT_FORMAT). The way in which Snowflake handles a mismatch between the expected format and the string has changed:

Previously

If the string does not match the input format, Snowflake uses one of the following fallback input formats to cast the string to a DATE, TIME, or TIMESTAMP value:

• For DATE values: YYYY-MM-DD

• For TIME values: HH24:MI:SS.FF

• For TIMESTAMP_NTZ and TIMESTAMP_LTZ values: UUUU-MM-DD HH24:MI:SS.FFTZH:TZM

• For TIMESTAMP_TZ values: UUUU-MM-DD HH24:MI:SS.FFZtz=TZIDX

Currently

If the string does not match the input format, Snowflake fails to cast the string to a DATE, TIME, or TIMESTAMP value.

注釈

If the conversion is done through a TRY_ function (e.g. TRY_TO_DATE, TRY_TO_TIME, etc.), the function returns NULL and does not raise an error.

For example, in the following statements, the input string does not match the specified format. In the previous release of Snowflake, these statements executed successfully. These statements fail to execute in the current release:

select to_date('2021-02-03', 'YYYY/MM/DD');

select to_time('12:34:56.789', 'HH24-MI-SS');

select to_timestamp_ntz('2021-02-03 12:34:56.789-08:00', 'YYYY/MM/DD HH24:MI:SS');

select to_timestamp_tz('2021-02-03 12:34:56.789Ztz=960', 'YYYY/MM/DD HH24:MI:SS TZH:TZM');

alter session set timestamp_input_format='YYYY/MM/DD HH24:MI:SS TZH:TZM';

select to_timestamp_tz('2021-02-03 12:34:56.789Ztz=960');

Casting Strings to DATE, TIME, and TIMESTAMP Values: Operation Fails When the Format for the Wrong Type is Used¶

With this release, we have changed the way in which failures are handled when strings in the wrong format are cast to DATE, TIME, and TIMESTAMP values.

When you cast a string to a DATE, TIME, or TIMESTAMP value and rely on Snowflake to detect the format automatically, Snowflake expects the format of the string to match the type of the value. For example, if you cast a string to a TIME value, Snowflake expects the string to match a TIME value (e.g. "12:30:00"), not a DATE value (e.g. "2021-02-03").

In some cases (e.g. when the input is a string literal in an SQL statement), Snowflake does not return an error when the format of the string does not match the expected format of the type. This has changed:

Previously

If the string uses a TIME format and you cast the string to a DATE or TIMESTAMP value, Snowflake converts the string to a DATE or TIMESTAMP value.

If the string uses a DATE or TIMESTAMP format and you cast the string to a TIME value, Snowflake converts the string to a TIME value.

Currently

Snowflake fails to convert the string.

注釈

If the conversion is done through a TRY_ function (e.g. TRY_TO_DATE, TRY_TO_TIME, etc.), the function returns NULL and does not raise an error.

For example, in the following statements, the input string does not match the expected format. In the previous release of Snowflake, these statements execute successfully. These statements fail to execute in the current release:

select to_time('2021-02-03');

select to_date('10:12:56.123875789');

DESCRIBE [API] INTEGRATION Command: Additional Output Rows¶

With this release, the output of the DESCRIBE INTEGRATION command includes the following additional rows for API integration objects:

• API_KEY (VARCHAR): This displays a masked value if an API key was entered. (This does not display either the original unencrypted key or the encrypted version of the key.)

• API_PROVIDER (VARCHAR): This lists the platform on which the proxy service runs (e.g. AWS_API_GATEWAY).

TABLE_STORAGE_METRICS View: Includes Information on Materialized Views¶

With this release, we now provide information on materialized views in the INFORMATION_SCHEMA.TABLE_STORAGE_METRICS view:

Previously

The INFORMATION_SCHEMA.TABLE_STORAGE_METRICS view did not include rows for some materialized views. For other materialized views, the view contained NULL values for some of the columns (such as TABLE_NAME).

The view also did not contain rows for the storage used for the search optimization service.

This was inconsistent with the ACCOUNT_USAGE.TABLE_STORAGE_METRICS view, which does include rows for materialized views.

Currently

The INFORMATION_SCHEMA.TABLE_STORAGE_METRICS view now includes rows for materialized views, and TABLE_NAME (and other columns) now includes information about the views.

INFORMATION_SCHEMA.TABLE_STORAGE_METRICS now includes rows for the search optimization service.

Snowpipe: Pipes Stopped by Default When Container is Cloned¶

When a database or schema is cloned, only pipe objects that reference external (Amazon S3, Google Cloud Storage, or Microsoft Azure) stages are cloned; pipes that reference internal (Snowflake) stages are not cloned.

As announced previously, with this release, the behavior when a database or schema containing one or more pipes is cloned has changed as follows:

Previously

All cloned pipes were paused by default (i.e. have a status of PAUSED). Pipes that leverage cloud messaging to trigger automated data loads continued to accumulate event notifications. When the pipe was resumed, Snowpipe processed the accumulated event notifications.

A cloned pipe can be resumed by executing the following command:

ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = FALSE

Currently

Cloned pipe objects that have the AUTO_INGEST parameter set to TRUE are in a new state, STOPPED_CLONED, by default. In this state, pipes do not accumulate event notifications as a result of newly staged files. When a pipe is explicitly resumed, it only processes data files triggered as a result of new event notifications.

Cloned pipe objects that have the AUTO_INGEST parameter set to FALSE