2021_10 Bundle¶

DESCRIBE USER Command: New RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2 Columns in Output¶

The output of the DESCRIBE USER command includes two new columns:

• RSA_PUBLIC_KEY

• RSA_PUBLIC_KEY_2

These two columns facilitate obtaining public keys that are currently set for the user.

Constraints: Changes to the RELY Constraint Property and Views¶

The behavior of the RELY constraint property, and two views for constraints (TABLE_CONSTRAINTS View in ACCOUNT_USAGE and TABLE_CONSTRAINTS View in INFORMATION_SCHEMA) have changed as follows:

Previously

When you created a new constraint:

• RELY was the default.

• You could not override this by specifying NORELY in the command. If you specified NORELY in the command, NORELY was ignored or an error was thrown.

For existing constraints, you could not change the RELY constraint property.

The following views did not provide information about the RELY constraint property:

• TABLE_CONSTRAINTS in ACCOUNT_USAGE.

• TABLE_CONSTRAINTS in INFORMATION_SCHEMA.

Currently

When you create a new constraint:

• NORELY is the default.

• You can override this by specifying RELY in the command.

Any existing constraints have the NORELY property (regardless of whether or not the constraint currently has the RELY or NORELY property). You can change the constraint property from NORELY to RELY.

The following views include a RELY column that specifies whether or not the RELY constraint property is set:

• TABLE_CONSTRAINTS in INFORMATION_SCHEMA. (Update: The RELY column was added in August 2022.)

The RELY column will be added to TABLE_CONSTRAINTS in ACCOUNT_USAGE in an upcoming release.

This change was made to support upcoming improvements to query optimization. These improvements make use of the constraints defined for the table.

With the new default NORELY setting, the query optimizer does not assume that the data in a table complies with the constraints. If you have ensured that the data in the table does comply with the constraints, you can change this to RELY (i.e. to indicate that the query optimizer should expect the data in the table to adhere to the constraints).

Materialized Views: Change to How Views Are Created When Cloning Databases¶

The way in which materialized views are created when you clone a database has changed as follows:

Previously

When you cloned a database, any materialized views in the cloned database were created with fully qualified names, even if you did not specify the fully qualified name when creating the view in the original database.

For example, suppose that you created a materialized view with the unqualified name mv in a database db1.:

use database db1;
create materialized view mv as ...;

Copy

Suppose that you then clone the database db1:

create database db1_clone clone db1;

Copy

The CREATE MATERIALIZED VIEW statement that created the view in the cloned database used the fully qualified name of the view.

You could view this statement by running the SHOW MATERIALIZED VIEWS command:

use database db1_clone;
show materialized views;

Copy

The column named text contains the text of the command that created this materialized view:

| text
+------ ...
| create or replace materialized view DB1_CLONE.PUBLIC.MV as ...

Copy

As demonstrated in this example, the command used the fully qualified name for the materialized view (DB1_CLONE.PUBLIC.MV).

Currently

The CREATE MATERIALIZED VIEW statement in the cloned database does not include the name of the cloned database and schema unless those names were specified in the original CREATE MATERIALIZED VIEW statement.

For example, suppose that you create a materialized view with the unqualified name mv in a database db1.:

use database db1;
create materialized view mv as ...;

Copy

Suppose that you then clone the database db1:

create database db1_clone clone db1;

Copy

When you run the command:

use database db1_clone;
show materialized views;

-- OR --

use database db1_clone;
show views;

Copy

The CREATE MATERIALIZED VIEW statement in the text column uses the unqualified name for the view because the unqualified name was used in the original CREATE MATERIALIZED VIEW statement:

| text
+------ ...
| create or replace materialized view mv as ...

Copy

This change was made to prevent potential issues when renaming a cloned database. When you rename a cloned database, the original name of the cloned database is not updated in the materialized view.

For example, suppose that you rename the cloned database db1_clone to db2:

alter database db1_clone rename to db2;

When you run the following command:

use database db2;
show materialized views;

The command in the text column used the original name of the cloned database (db1_clone), not the new name of the cloned database (db2):

| text
+------ ...
| create or replace materialized view DB1_CLONE.PUBLIC.MV as ...

As a result, querying the materialized view results in an error:

select * from mv;

SQL compilation error: Failure during expansion of view 'MV': Cannot perform SELECT.

This behavior change prevents this error from occurring.

SHOW ORGANIZATION ACCOUNTS Command: New Columns in Output¶

To provide a better understanding of the mappings of accounts to billing entities in an organization, the following columns have been added output of the SHOW ORGANIZATION ACCOUNTS command:

GET_DDL Function: Changes for Views¶

The GET_DDL function returns a DDL statement that can be used to recreate the specified object, with variations in the query output depending on the specified object in the function argument. The behavior of the GET_DDL function for views has changed as follows:

Previously

Snowflake returned the exact SQL statement to recreate the view. If the view was a secure view, Snowflake returned the DDL statement to create the view and an ALTER statement to set the SECURE property on the view.

Currently

Snowflake has updated the query result for views as follows:

• The query result returns lowercase SQL text for create or replace view, even if the casing in the original SQL statement used to create the view was uppercase or mixed case.

• The OR REPLACE clause is always included in the CREATE VIEW statement.

• SQL comments before the view body (AS) are removed.

• The column list is always generated. If a masking policy is set on a column, the result specifies the masking policy for the column.

• If the view has a masking policy on one or more of its columns or a row access policy and the role executing the GET_DDL query does not have the global APPLY MASKING POLICY or APPLY ROW ACCESS POLICY privilege, the policy name is replaced with #unknown_policy. See the note below.

• If the view is secure, the query result includes the SECURE property in the CREATE statement; an additional ALTER statement to set the SECURE property is no longer included in the query result.

• COPY GRANTS is not included, even if it was specified in the original CREATE VIEW statement.

• Ensures the CREATE VIEW statement always includes a semicolon at the end of the statement.

INFER_SCHEMA Function: Change to NULL Columns¶

The INFER_SCHEMA function detects the column definitions in a set of staged data files that contain semi-structured data, and retrieves the metadata in a format suitable for creating Snowflake objects.

The condition that determines whether the function returns the NULL or NOT NULL constraint for a column has changed as follows:

Previously

The INFER_SCHEMA function returned the nullability constraint for a column as indicated in the metadata for the files that include the column. When the input for the function was a single file, the nullability constraint returned for the columns was always correct. However, when a column was identified in the metadata as required but was not included in all of the input files, the function still returned a NOT NULL constraint for the column. This logic could result in errors when loading all of the files into a table created using the INFER_SCHEMA function output.

When creating a table with the column definitions derived from a set of staged data files (using the CREATE TABLE … USING TEMPLATE syntax), all columns in the table were defined as nullable.

Currently

The INFER_SCHEMA function returns a column as nullable if the column is missing or indicated as optional from any input files. The function returns a column as non-nullable only if the column is identified as required in all input files.

The GENERATE_COLUMN_DESCRIPTION function and CREATE TABLE … USING TEMPLATE command syntax follows the same nullability behavior as the INFER_SCHEMA function.

ACCESS_HISTORY View: Support for Write Operations¶

The behavior of the ACCESS_HISTORY View in the ACCOUNT_USAGE schema has changed as follows:

Previously

The ACCESS_HISTORY view only supported SQL read operations.

Currently

The ACCESS_HISTORY view supports SQL read and write operations as follows:

• Additional rows have been included in the query output of the view to indicate that write operations occurred.

• A new column, OBJECTS_MODIFIED, of ARRAY data type, specifies objects that were modified in the write portion of a SQL query.

• If a stage was accessed, the objectDomain field specifies the value STAGE.

• If a stage was accessed in the read portion of the query, the DIRECT_OBJECTS_ACCESSED and BASE_OBJECTS_ACCESSED columns have been updated as follows:

  • A new JSON field, stageKind, specifies the stage.

  • The objectName and objectId fields specify the corresponding values for a user, table, and named stage.

• For details on supported and not supported write operations, see the notes below.

Note the following:

• The OBJECTS_MODIFIED column returns an array in the following format:

  {
    "columns": [
       {
         "columnName": <string>,
         "columnId": <number>
       },
       {
         "columnName": <string>,
         "columnId": <number>
       }
      ...
    ],
    "objectId": <number>,
    "objectName": <string>,
    "objectDomain": TABLE | STAGE,
    "location": <string>,
    "stageKind": Table | User | Internal Named | External Named
  }
  

  Copy

  If a stage was accessed in the write portion of the query:

  • The objectId value is as follows:

    • NAME identifier for a user (User stage).

    • TABLE_ID number for a table (Table stage).

    • STAGE_ID number for a stage (Named stage).

  • The objectName value is as follows:

    • User stage: the value is the username.

    • Table stage: the value is the table_name.

    • Named stage: the value is the stage_name.

• If a stage was accessed in the write portion of the query, the BASE_OBJECTS_ACCESSED and DIRECT_OBJECTS_ACCESSED columns include the following JSON fields:

  {
    "objectDomain": STAGE
    "objectName": <string>,
    "objectId": <number>,
    "stageKind": <string>
  }
  

  Copy

  The possible values for the field names in these two columns are the same as the OBJECTS_MODIFIED column.

• Snowflake supports the following write operations in the ACCESS_HISTORY view:

  • GET <internal_stage>

  • PUT <internal_stage>

  • DELETE

  • INSERT

    • INSERT INTO … FROM SELECT *

    • INSERT INTO TABLE … VALUES ()

  • MERGE INTO … FROM SELECT *

  • UPDATE

    • UPDATE TABLE … FROM SELECT * FROM …

    • UPDATE TABLE … WHERE …

  • Data loading statements:

    • COPY INTO TABLE FROM internalStage

    • COPY INTO TABLE FROM externalStage

    • COPY INTO TABLE FROM externalLocation

  • Data unloading statements:

    • COPY INTO internalStage FROM TABLE

    • COPY INTO externalStage FROM TABLE

    • COPY INTO externalLocation FROM TABLE

  • CREATE:

    • CREATE DATABASE … CLONE

    • CREATE SCHEMA … CLONE

    • CREATE TABLE … CLONE

    • CREATE TABLE … AS SELECT

• Snowflake does not support the following write operations in the ACCESS_HISTORY view:

  • The operations to populate views, materialized views, and streams.

  • Data movement resulting from replication.

COPY_HISTORY: Consistent STATUS Column Letter Case in Output¶

The status of a data load is reported in the STATUS column in the output of the COPY_HISTORY table function in the Information Schema and the COPY_HISTORY View in ACCOUNT_USAGE. The values returned in the STATUS column have changed as follows:

Previously

For bulk data loads (COPY INTO <table> statements), the status values were returned with the first letter of the first word in uppercase and the remaining words in all lowercase, as documented: Loaded, Load failed, etc.

For Snowpipe data loads, the status values were returned in uppercase: LOADED, LOAD FAILED, etc.

Currently

For both bulk and Snowpipe data loads, the status values are returned with the first letter of the first word in uppercase and the remaining words in all lowercase.

This change applies consistency to the STATUS column values and brings them into alignment with the product documentation.

Java UDFs: Changes to Scala JAR File Inclusion Criteria¶

Java UDF behavior has changed as follows:

Previously

For Java UDFs, Scala JAR files were included in the JVM classpath.

Currently

For Java UDFs, Scala libraries are no longer included in the classpath. If any of your Java UDF code depends on Scala libraries, include the Scala JAR files in the imports list when you create new UDFs or replace existing UDFs. For example:

create or replace function add(x integer, y integer)
returns integer
language java
imports = ('@stage/scala-library-2.12.jar')
handler='TestAddFunc.add'

Copy

UDFs created using the Snowpark Scala library are not affected.

COPY INTO <table> Command: MATCH_BY_COLUMN_NAME Copy Option Returns Error When Loading CSV Data¶

The MATCH_BY_COLUMN_NAME copy option enables loading semi-structured data into separate columns in the target table that match corresponding columns represented in the source data. The copy option does not support loading data from comma-separated values (CSV) files.

The behavior when attempting to load CSV-formatted data with the MATCH_BY_COLUMN_NAME copy option set to either CASE_SENSITIVE or CASE_INSENSITIVE has changed as follows:

Previously

The COPY INTO <table> statement did not return an error when used with CSV file formats, but the MATCH_BY_COLUMN_NAME setting did not affect the data load and is ignored.

Currently

The COPY INTO <table> statement returns a user error as the option does not support CSV files.

COPY INTO <location> Command: Explicit Column Casts Ignored When Unloading to Parquet Data¶

When unloading numeric table data to Parquet files, calling the CAST function in the COPY INTO <location> statement enables you to choose Snowflake data types that map to Parquet data types.

The behavior when explicitly casting numeric column data to Parquet files has changed as follows:

Previously

When at least one numeric column was explicitly cast to a data type that did not map to a Parquet data type, the data unload operation ignored any explicit casts that did map to Parquet data types. Fixed-point number columns were unloaded as DECIMAL columns; while floating-point number columns were unloaded as DOUBLE columns.

Currently

Data unload operations honor all explicit casts of numeric column data, regardless of whether the target Snowflake data types map to Parquet data types.

Managed Accounts: Changes to Support New Account Name Format¶

Snowflake introduces a new URL based on an updated managed account name, which customers can choose and change. The URL has the following format:

<organization_name>-<managed_account_name>.snowflakecomputing.com

The new managed account name has 2 new naming rules:

• Underscore (_) is the only valid delimiter in the name.

• The name must be unique to the organization that the managed account is in.

Most existing managed account names already follow the new naming rules and the names will stay the same. Managed account names that did not follow these rules were automatically updated as follows:

• If the managed account name contained non-underscore separators, they were converted to underscores. For example if the managed account name was managed account-1, the new managed account name is managed_account_1.

• If the managed account name was not unique to the organization, the locator name was appended to the managed account name. For example if the managed account name was managed with a locator of RE47190, the new managed account name is managed_RE47190.

The updated managed account name is used in all managed account commands:

• CREATE MANAGED ACCOUNT enforces the new naming rules.

• SHOW MANAGED ACCOUNTS shows the updated managed account name in the name column.

• DROP MANAGED ACCOUNT uses the updated managed account name as a parameter.

SHOW SHARES Command & Data Sharing UI: Changes to Output¶

The SHOW SHARES command and the web interface for data sharing that include an account locator (formerly known as an auto-generated account name) in the output have changed to use the organization name and new account name as follows:

Previously

name column displayed <account_locator>.<share_name>

to column (for outbound shares) displayed <account_locator>

Currently

name column will display <organization_name>.<account_name>.<share_name>

to column (for outbound shares) will display <organization_name>.<account_name>

In addition, commands that use <account_locator>.<share_name> as a parameter (DESCRIBE SHARE and CREATE DATABASE … FROM SHARE) can use <organization_name>.<account_name>.<share_name> as a parameter.

For more information on the difference between the account locator and the new account name, see Account Identifiers.

SHOW Commands & SELECT Statements: Changes to Output When Multiple Shares Originate from the Same Database¶

Previously

When a consumer mounted more than one database from data shares that were created from the same provider database (a one database to many shares provider configuration), in specific circumstances, objects from the same-origin data shares appeared to a user unexpectedly in mounted databases based on those same-origin data shares. This issue did not affect actual access to the data. Objects only appeared unexpectedly in a database the user was querying if they had permission to access those objects.

For example, assume a provider wants to share data from ORIGIN_DATABASE. ORIGIN_DATABASE has two schemas, SCHEMA_A and SCHEMA_Z. The provider wants to share three tables. Two tables are from SCHEMA_A: SCHEMA_A.TABLE1 and SCHEMA_A.TABLE2. The provider also wants to share SCHEMA_Z.TABLE1.

The provider creates three data shares: FIRST_DATASHARE from SCHEMA_A.TABLE1, SECOND_DATASHARE from SCHEMA_A.TABLE2, and THIRD_DATASHARE from SCHEMA_Z.TABLE1. The provider then adds a consumer to the three shares.

In this example, the consumer mounted three databases based on the data shares from the provider: FIRST_DATABASE is mounted from FIRST_DATASHARE, SECOND_DATABASE from SECOND_DATASHARE, and THIRD_DATABASE from THIRD_DATASHARE.

The consumer in this example may or may not have been aware that the databases they had mounted were derived from one provider database.

When the consumer uses SHOW OBJECTS (e.g., TABLES, FUNCTIONS etc.) to explore their mounted databases, they see the same set of objects from the same origin schema in each of the mounted databases, despite the databases being mounted are from different shares.

In this example, the consumer saw the following result when using SHOW OBJECTS IN ACCOUNT. For the purposes of this example, only the relevant columns from the output are included. In the output, the database names are overwritten by the most recent mounted database from the same origin.

name	database_name	schema_name
APPLICABLE_ROLES	THIRD_DATABASE	INFORMATION_SCHEMA
<other info schema objects>
TABLE1	THIRD_DATABASE	SCHEMA_A
TABLE2	THIRD_DATABASE	SCHEMA_A
APPLICABLE_ROLES	THIRD_DATABASE	INFORMATION_SCHEMA
<other info schema objects>
TABLE1	THIRD_DATABASE	SCHEMA_A
TABLE2	THIRD_DATABASE	SCHEMA_A
APPLICABLE_ROLES	THIRD_DATABASE	INFORMATION_SCHEMA
<other info schema objects>
TABLE1	THIRD_DATABASE	SCHEMA_Z

Currently

When a consumer mounts databases from data shares, the consumer only sees the expected objects that the provider wants for each data share, even if those data shares originate from the same provider database.

The consumer in the example sees the following result after using SHOW OBJECTS IN ACCOUNT. For the purposes of this example, only the elevant columns from the output are included.

name	database_name	schema_name
APPLICABLE_ROLES	FIRST_DATABASE	INFORMATION_SCHEMA
<other info schema objects>
TABLE1	FIRST_DATABASE	SCHEMA_A
APPLICABLE_ROLES	SECOND_DATABASE	INFORMATION_SCHEMA
<other info schema objects>
TABLE2	SECOND_DATABASE	SCHEMA_A
APPLICABLE_ROLES	THIRD_DATABASE	INFORMATION_SCHEMA
<other info schema objects>
TABLE1	THIRD_DATABASE	SCHEMA_Z