2022_01 Bundle¶

Transactions: Committing an Aborted Transaction Returns an Error Message¶

With this change, attempting to commit a transaction that has already been aborted returns an error message:

Previously:

If you executed a COMMIT statement in a transaction that was aborted (e.g. through a separate SYSTEM$ABORT_TRANSACTION call or because the session was idle), the statement appeared to succeed with the following message:

Statement executed successfully.

However, the transaction itself had already been aborted.

The output message for the COMMIT statement incorrectly implied that the transaction succeeded.

Currently:

If you execute a COMMIT statement in a transaction that has been aborted (e.g. through a separate SYSTEM$ABORT_TRANSACTION call or because the session has been idle), the statement fails with the error code 000670 and the following message:

000670 (57014): COMMIT failed. Your transaction '<name>', id '<id>', was already aborted.

SHOW TAGS: New Column in Output¶

The following column has been added to the output of the SHOW TAGS command:

• allowed_values: This new column specifies the string values that can be set when a tag is assigned to an object. If a tag does not have any specified allowed_values, the column value is NULL.

To help limit the impact of this change, the new column has been added as the last column in the output.

SHOW USERS: Support Added for Paginated Results, Limited Results, and Terse Output¶

The behavior of the SHOW USERS command has changed as follows:

Previously:

The SHOW USERS command limited the SQL output to 10,000 rows and supported only the following syntax:

SHOW USERS [ LIKE '<pattern>' ]

Copy

Currently:

The SHOW USERS command can be used to return more than 10,000 rows and supports the following syntax:

SHOW [ TERSE ] USERS [ LIKE '<pattern>' ] [ STARTS WITH '<name_string>' ] [ LIMIT <rows> [ FROM '<name_string>' ] ]

Copy

Where:

• TERSE optionally returns only the following subset of the output columns:

  name, created_on, display_name, first_name, last_name, email, org_identity, comment, has_password, has_rsa_public_key.

  Default: No value (all columns are included in the output).

• STARTS WITH 'name_string' optionally filters the command output based on the characters that appear at the beginning of the object name. The string must be enclosed in single quotes and is case-sensitive. For example, the following clauses return different results:

  ... STARTS WITH 'B' ...

  ... STARTS WITH 'b' ...

  Default: No value (no filtering is applied to the output).

• LIMIT rows [ FROM 'name_string' ] optionally limits the maximum number of rows returned, while also enabling “pagination” of the results. Note that the actual number of rows returned may be less than the specified limit (e.g. the number of existing objects is less than the specified limit).

  The optional FROM 'name_string' subclause effectively serves as a “cursor” for the results. This enables fetching the specified number of rows following the first row whose object name matches the specified string:

  The string must be enclosed in single quotes and is case-sensitive. In addition, the string does not have to include the full object name; partial names are supported.

  Default: No value (no limit is applied to the output).

By default, SHOW USERS still returns 10,000 rows as before. If your account has more than 10,000 users, you can run the command twice to return the users above the 10,000 limit:

SHOW USERS;

SHOW USERS LIMIT 10000 FROM 'JOE';

Copy

• The first statement returns the first 10,000 users. The last row returned is a user with the username JOE.

• The second statement returns the next 10,000 users after JOE.

SYSTEM$EXTERNAL_TABLE_PIPE_STATUS and SYSTEM$PIPE_STATUS Functions: Additional Details in JSON Output¶

The JSON output of the SYSTEM$EXTERNAL_TABLE_PIPE_STATUS and SYSTEM$PIPE_STATUS functions now include the following additional string name/value pairs to improve insight into specific data loads, including errors encountered during the loads:

The following examples show sample output for common issues encountered by Snowpipe. The additional details in the function output can help you diagnose these and other issues with your data loads:

• Notification queue configured incorrectly:

  {"executionState":"RUNNING","pendingFileCount":0,"notificationChannelName":"projects/myproject/subscriptions/mysubscription","numOutstandingMessagesOnChannel":0,"lastPulledFromChannelTimestamp:"2022-01-20T06:24:44.771Z"}
  

  Copy

  Snowpipe attempts to periodically pull “create object” event notifications for the pipe from the Amazon Simple Queue Service (SQS) queue, Google Pub/Sub queue, or Microsoft Azure storage queue. A missing or outdated lastPulledFromChannelTimestamp value indicates that Snowpipe has not been able to connect to the storage queue.

  If the lastPulledFromChannelTimestamp timestamp is recent, but numOutstandingMessagesOnChannel is 0, then Snowpipe can receive event notifications from the queue, but no notifications were queued. This last issue can occur when no data files were created in the storage location or the subscription to the queue is misconfigured.

• Notification queue permission issue:

  {"executionState":"RUNNING","pendingFileCount":0,"lastIngestedTimestamp":"2022-01-20T04:30:02.518Z","lastIngestedFilePath":"myfile.csv","notificationChannelName":"projects/myproject/subscriptions/mysubscription","numOutstandingMessagesOnChannel":1,"lastReceivedMessageTimestamp":"2022-01-20T04:30:02.319Z","lastForwardedMessageTimestamp":"2022-01-20T04:30:03.27Z","channelErrorMessage":"no monitoring permission: numOutstandingMessagesOnChannel is not accurate","lastErrorRecordTimestamp":"2022-01-20T04:44:08.461Z","lastPulledFromChannelTimestamp":"2022-01-20T04:44:08.461Z","lastForwardedFilePath":"mypath/myfile.csv"}
  

  Copy

  The channelErrorMessage value indicates that Snowflake was not granted sufficient permissions to access the storage queue and pull the event notifications.

• Mismatch in the paths specified in the pipe definition and event notification configuration:

  {"executionState":"RUNNING","pendingFileCount":0,"lastIngestedTimestamp":"2022-01-20T06:00:01.669Z","lastIngestedFilePath":"myfile.csv","notificationChannelName":"projects/myproject/subscriptions/mysubscription","numOutstandingMessagesOnChannel":1,"lastReceivedMessageTimestamp":"2022-01-20T06:04:01.089Z","lastForwardedMessageTimestamp":"2022-01-20T06:00:02.741Z","lastPulledFromChannelTimestamp":"2022-01-20T06:05:28.49Z","lastForwardedFilePath":"mypath/myfile.csv"}
  

  Copy

  The lastForwardedMessageTimestamp timestamp is earlier than lastReceivedMessageTimestamp. This indicates that Snowpipe pulled at least one “create object” event message from the storage queue, but the message did not match the path defined in the pipe and therefore was not forwarded to the pipe for processing.

• External storage location permission issue:

  {"executionState":"STALLED_STAGE_PERMISSION_ERROR","pendingFileCount":0,"error":"Failed to access the stage, please check storage permission.", "lastPipeErrorTimestamp":"2022-01-20T04:40:01.747Z", "lastIngestedTimestamp":"2022-01-20T04:30:02.518Z","lastIngestedFilePath":"myfile.csv","notificationChannelName":"projects/myproject/subscriptions/mysubscription","numOutstandingMessagesOnChannel":1,"lastReceivedMessageTimestamp":"2022-01-20T04:30:02.319Z","lastForwardedMessageTimestamp":"2022-01-20T04:30:03.27Z","channelErrorMessage":"no monitoring permission: numOutstandingMessagesOnChannel is not accurate","lastErrorRecordTimestamp":"2022-01-20T04:44:08.461Z","lastPulledFromChannelTimestamp":"2022-01-20T04:39:58.494Z","lastForwardedFilePath":"mypath/myfile.csv"}
  

  Copy

  The executionState value and error message indicate that Snowflake was not granted the minimum permissions on the storage location (i.e. Amazon S3 or Google Cloud Storage bucket or Microsoft Azure container) to access the data files in the storage location.

• Error compiling the COPY INTO <table> statement in the pipe definition:

  {"executionState":"STALLED_COMPILATION_ERROR","pendingFileCount":0,"error":"SQL compilation error: error line 1 at position 29\ninvalid identifier 'LAST_NAME'",", "lastPipeErrorTimestamp":"2022-01-20T17:54:30.4Z", "lastIngestedTimestamp":"2022-01-20T17:51:04.73Z","lastIngestedFilePath":"myfile.csv","notificationChannelName":"projects/myproject/subscriptions/mysubscription","numOutstandingMessagesOnChannel":1,"lastReceivedMessageTimestamp":"2022-01-20T17:51:03.336Z","lastForwardedMessageTimestamp":"2022-01-20T17:51:05.081Z","lastPulledFromChannelTimestamp":"2022-01-20T18:03:00.637Z","lastForwardedFilePath":"mypath/myfile.csv"}
  

  Copy

  The executionState value and error message indicate that Snowpipe could not execute the COPY INTO <table> statement in the pipe definition to load the data files retrieved from the storage location.

FUNCTIONS View: New Columns¶

The following columns have been added to the ACCOUNT_USAGE.FUNCTIONS view to make it consistent with the INFORMATION_SCHEMA.FUNCTIONS view:

• handler

• imports

• target_path

To help limit the impact of this change, the new columns have been added as the last columns in the view.

FUNCTIONS View, DESCRIBE FUNCTION, and GET_DDL Function: New Columns¶

The following columns have been added to the INFORMATION_SCHEMA.FUNCTIONS view, as well as the output of the DESCRIBE FUNCTION command and GET_DDL function:

• request_translator: The name of the request translator JavaScript user-defined function, if it exists.

• response_translator: The name of the response translator JavaScript user-defined function, if it exists.

​​​​​Request translators and response translators allow you to change the format of data sent to, and received from, remote services used by external functions. For more information, see Using Request and Response Translators with Data for a Remote Service.

POLICY_REFERENCES Function: New Columns¶

The created_on column has been removed from the output of the INFORMATION_SCHEMA.POLICY_REFERENCES table function.

The following columns have been added to the output of the INFORMATION_SCHEMA.POLICY_REFERENCES table function:

• tag_name

• tag_database

• tag_schema

• policy_status

These new columns can be used to discover policy associations on a column by the tag name.

To help limit the impact of this change, these new columns have been added as the last columns in the output.

TAGS View: New Column¶

The following column has been added to the ACCOUNT_USAGE.TAGS view:

• allowed_values: Specifies the string values that can be set when a tag is assigned to an object. If a tag does not have any specified allowed_values, the column value is NULL.

To help limit the impact of this change, the new column has been added as the last column in the view.

TASK_HISTORY Function: Task Usage History Returned for Previous 7 Days by Default¶

The behavior of the INFORMATION_SCHEMA.TASK_HISTORY table function has changed as follows:

Previously:

By default, the function returned records for historic task runs within a time range that had no determined start date or time. Due to internal data cleanup activity, the time range was generally limited to the previous 14 days; however, in rare circumstances, the period could extend further in the past.

If the SCHEDULED_TIME_RANGE_START argument was specified in a query, the time range was limited to historic records from the previous 7 days or less.

Currently:

The function returns records for historic task runs that started within the previous 7 days.

Note that, as with the previous behavior, if the SCHEDULED_TIME_RANGE_START argument is specified in a query, the time range continues to be limited to historic records from the previous 7 days or less.

TASK_HISTORY View/Function: New Column¶

The following column has been added to the output of the ACCOUNT_USAGE.TASK_HISTORY view and INFORMATION_SCHEMA.TASK_HISTORY table function:

• scheduled_from: Specifies the mechanism that prompted the task run. The only value returned in the column is SCHEDULE. This value indicates that the task run was initiated by the schedule in the task definition. For runs of child tasks in a task tree, the column also returns SCHEDULE.

The column was introduced to support future functionality.

Warehouse Views: Consistent Output for Warehouse Name¶

The owner of a warehouse (i.e. the role that has the OWNERSHIP privilege on the warehouse), or a higher role, can rename the warehouse using the new web interface or the ALTER WAREHOUSE … RENAME command.

The behavior when a warehouse is renamed has changed as follows:

Previously:

The warehouse name was displayed inconsistently in the following view columns:

• ACCOUNT_USAGE, READER_ACCOUNT_USAGE, and ORGANIZATION_USAGE

  • WAREHOUSE_METERING_HISTORY.WAREHOUSE_HAME

• ACCOUNT_USAGE

  • METERING_HISTORY.NAME

Recent records in the views (2-3 hours before the warehouse name was changed) displayed the new name; however, older records displayed the old name.

Currently:

The WAREHOUSE_NAME and NAME columns in these views display the new warehouse name for all records.

JavaScript Stored Procedures: Changes to Error Handling¶

The error handling for stored procedures written in JavaScript has changed as follows:

Previously:

All errors thrown by JavaScript stored procedures had the same error code and SQLSTATE (100183 and P0000 respectively) and the same generic error message ("Execution error in stored procedure SP_NAME...").

The name of the property for accessing the stack trace was stackTraceTxt.

For example, if a database object does not exist, the exception object contains the following:

{
  "stackTraceTxt":"At Statement.execute, line 12 position 19",
  "state":"P0000",
  "code":100183,
  "message":"SQL compilation error:\nObject 'X' does not exist or not authorized."
}

Copy

If your stored procedure did not catch the exception, calling the stored procedure produced the following output:

100183 (P0000): Execution error in store procedure ...:
SQL compilation error:
Object 'X' does not exist or not authorized.
At Statement.execute, line 12 position 19

Copy

Currently:

If the stored procedure executes any queries (as most stored procedures do), and if the error occurs while executing the child query, the error code and SQLSTATE of the child query is used.

In addition, the name of the property for accessing the stack trace has been changed to stack. Although the stackTraceTxt property is still present with the behavior change enabled, stackTraceTxt will be removed in a future release.

For example, if a database object doesn’t exist, the exception object contains the following:

{
  "stack":"Statement.execute, line 12 position 19",
  "stackTraceTxt":"Statement.execute, line 12 position 19", // To be removed in a future release
  "state":"42S02",
  "code":2003,
  "message":"SQL compilation error:\nObject 'X' does not exist or not authorized."
}

Copy

If your stored procedure does not catch the exception, calling the stored procedure produces the following output:

002003 (42S02): Execution error in store procedure ...:
SQL compilation error:
Object 'X' does not exist or not authorized.
At Statement.execute, line 12 position 19

Copy