2022_02 Bundle¶

Hierarchical Data Queries: Iteration Limits No Longer Enforced¶

When querying hierarchical data, you can use recursive CTEs or the CONNECT BY command to iterate over each level of hierarchy.

The limit on the number of iterations, which was previously set to 100 (internally by Snowflake), is now no longer enforced:

Previously:

If a query exceeded the maximum number of iterations (100), the query failed with the following error message:

Recursion exceeded max iteration count (n)

where n was the maximum number of iterations allowed.

The error code for this error was 100189.

Currently:

There is no limit to the number of iterations performed.

Queries that previously failed with the error message above (in particular, queries that result in infinite loops) no longer fail and continue to run until the query either succeeds or times out, which can be controlled by setting the STATEMENT_TIMEOUT_IN_SECONDS parameter.

To determine if you had queries that exceeded the maximum number of iterations before the change was enabled, check the QUERY_HISTORY view for queries that failed with the error code 100189:

SELECT * FROM snowflake.account_usage.query_history WHERE error_code = 100189;

Copy

With the change enabled, if these same queries ae executed, they will not fail. If an infinite loop occurs, the query will not terminate early. Instead, the query will run until it either succeeds or times out (e.g. exceeds the number of seconds set in the STATEMENT_TIMEOUT_IN_SECONDS parameter).

See Troubleshooting a Recursive CTE for information on how infinite loops can occur, how to identify them, and how to rectify them.

Time Travel: Inherited DATA_RETENTION_TIME_IN_DAYS Parameter Retained in Transient Tables¶

The behavior for transient tables when the DATA_RETENTION_TIME_IN_DAYS parameter is explicitly set to 0 (days) for a parent object (account, database, or schema) has changed as follows:

Previously:

Transient tables did not inherit the DATA_RETENTION_TIME_IN_DAYS parameter setting from parent objects when the data retention time was 0 days. Transient tables were created with a data retention time of 1 day, regardless of the parent object’s data retention time.

Currently:

Transient tables inherit the data retention time set on a parent object (schema, database, account) if the parent object’s DATA_RETENTION_TIME_IN_DAYS is set to 0.

To generate a list of transient tables in an account where at least one of its parents (schema or database) has DATA_RETENTION_TIME_IN_DAYS set to 0, execute the statements in the following example. However, note the following before executing the statements:

• The list includes transient tables that have the DATA_RETENTION_TIME_IN_DAYS parameter explicitly set to 1.

  If DATA_RETENTION_TIME_IN_DAYS is set to 0 at the account level, execute the set of statements in the second example below to list all transient tables with DATA_RETENTION_TIME_IN_DAYS set to 1.

• Before unsetting the parameter for any table, we recommend that you verify Time Travel should be disabled for that table.

show tables in account;
set
  table_qid = (
    select
      last_query_id()
);

show schemas in account;
set
  schema_qid = (
    select
      last_query_id()
);

show databases in account;
set
  database_qid = (
    select
      last_query_id()
);

with table_v as (
    select
      "database_name" as database_name,
      "schema_name" as schema_name,
      "name" as table_name,
      "kind" = 'TRANSIENT' as is_transient,
      "retention_time" as table_retention_time
    from
      table(result_scan($table_qid))
  ),
  schema_v as (
    select
      "name" as schema_name,
      iff(
        try_to_number("retention_time") is null,
        0,
        try_to_number("retention_time")
      ) as schema_retention_time
    from
      table(result_scan($schema_qid))
  ),
  database_v as (
    select
      "name" as database_name,
      "retention_time" as database_retention_time
    from
      table(result_scan($database_qid))
  )
select
  *
from
  table_v
  left join schema_v using (schema_name)
  left join database_v using (database_name)
where
  is_transient
  and table_retention_time = 1
  and (
    schema_retention_time = 0
    or database_retention_time = 0
  );

Copy

If DATA_RETENTION_TIME_IN_DAYS is set to 0 at the account level, execute the following statements to list all transient tables with DATA_RETENTION_TIME_IN_DAYS set to 1:

-- Verify account level DATA_RETENTION_TIME_IN_DAYS setting is 0
show parameters like 'DATA_RETENTION_TIME_IN_DAYS' in account;

show tables in account;

select
  "database_name" as database_name,
  "schema_name" as schema_name,
  "name" as table_name,
  "kind" = 'TRANSIENT' as is_transient,
  "retention_time" as table_retention_time
from
  table(result_scan(last_query_id()))
where
  is_transient
  and table_retention_time = 1;

Copy

To unset the DATA_RETENTION_TIME_IN_DAYS parameter for an existing transient table, which allows it to inherit the parameter setting from a parent object, use ALTER TABLE:

ALTER TABLE <table_name> UNSET DATA_RETENTION_TIME_IN_DAYS;

Copy

To verify the data retention time set on a table, use SHOW TABLES:

SHOW TABLES LIKE '<table_name>';

Copy

SHOW ORGANIZATION ACCOUNTS Command: New Column¶

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

SHOW PROCEDURES Command: Output Includes Both User-created and Built-in Stored Procedures¶

Snowflake supports creating stored procedures as schema-level objects in any database in an account. The SHOW PROCEDURES command returns information about these user-created stored procedures.

With the introduction of data classification, Snowflake now also provides built-in stored procedures that can be called as global objects, similar to built-in functions.

The output for the SHOW PROCEDURES command has changed as follows to support built-in stored procedures:

Previously:

The command returned only user-created stored procedures in the current or specified database/schema (or for the entire account).

To view the built-in stored procedures provided by Snowflake, you could use the BUILTIN keyword in the command. For example:

SHOW BUILTIN PROCEDURES;

Copy

However, note that Snowflake only provides a single built-in stored procedure, ASSOCIATE_SEMANTIC_CATEGORY_TAGS.

Currently:

The function returns all stored procedures, including both user-created and built-in stored procedures.

This make the SHOW PROCEDURES command consistent with the SHOW FUNCTIONS command.

The change does not affect the BUILTIN or USER keywords, which can be used to explicitly return either built-in or user-created stored procedures. For example:

SHOW BUILTIN PROCEDURES;

SHOW USER PROCEDURES;

Copy

SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS Function: Changes to Basis for Estimation¶

SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS is a system function that you can call to determine the estimated costs of adding search optimization to a table.

This function has changed to use a small sample as the basis for estimating the costs. With this change, the function reports more accurate cost estimates. However, the change impacts warehouse usage and output of the function, as well as may affect performance of the function, as described below:

Previously:

The function used a simple model to estimate the costs. Because the function used a simple model to estimate the costs:

• You did not need to have a warehouse in use when calling the function.

• Because the function did not use a warehouse, you were not billed for warehouse usage for this function.

• The function executed and returned within seconds.

• In the returned JSON output, for the objects named BuildCosts and StorageCosts in the costPositions array:

  • There was no comment field.

  • The computationMethod field was set to "EstimatedUpperBound".

Currently:

The function now takes a small sample of data from the specified table, produces a temporary search access path, analyzes the cost of the process, and extrapolates the results to estimate the cost for the entire table. Because the function uses sampling to estimate the costs:

• To call the function, you need to have a warehouse in use. If no warehouse is currently in use, the function prints the following message:

  No active warehouse selected in the current session.

  Select an active warehouse with the USE WAREHOUSE command. To execute this function, you can use an X-Small warehouse. The warehouse size has no effect on the speed and performance of this function.

• Because the function uses a warehouse, you are now billed for warehouse usage for this function.

• The function takes longer to execute and return results (somewhere in the range of 20 seconds to 10 minutes). As noted above, using a larger warehouse size does not result in the faster execution of this function.

• In the returned JSON output, for the objects named BuildCosts and StorageCosts in the costPositions array:

  • The comment field is set to "estimated via sampling".

  • The computationMethod field is set to "Estimated".

LOGIN_HISTORY View (Account Usage): New Column¶

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

QUERY_HISTORY Views (Account Usage): Output Consistent with QUERY_HISTORY Function¶

The values for inbound and outbound data transfer bytes are inconsistent between the following:

• ACCOUNT_USAGE.QUERY_HISTORY and READER_ACCOUNT_USAGE.QUERY_HISTORY views

• INFORMATION_SCHEMA.QUERY_HISTORY table function output

The Account Usage views include inbound and outbound data transfer bytes when the data transfer cloud value (INBOUND_DATA_TRANSFER_CLOUD or OUTBOUND_DATA_TRANSFER_CLOUD respectively) is Null.

These views have changed as follows:

Previously:

The QUERY_HISTORY views in ACCOUNT_USAGE and READER_ACCOUNT_USAGE included the following:

• INBOUND_DATA_TRANSFER_BYTES column included data transfer bytes when the INBOUND_DATA_TRANSFER_CLOUD value is Null.

• OUTBOUND_DATA_TRANSFER_BYTES column included data transfer bytes when the OUTBOUND_DATA_TRANSFER_CLOUD value is Null.

Currently:

The views are now consistent with the output of the INFORMATION_SCHEMA.QUERY_HISTORY table function.

The INBOUND_DATA_TRANSFER_BYTES and OUTBOUND_DATA_TRANSFER_BYTES columns do not include bytes from file transfers when the associated INBOUND_DATA_TRANSFER_CLOUD or OUTBOUND_DATA_TRANSFER_CLOUD value is Null.

DESCRIBE STREAM / SHOW STREAM Commands: New Columns in Output¶

The output of the DESCRIBE STREAM and SHOW STREAMS commands now includes the following additional columns:

The new columns have been inserted between the existing TABLE_NAME and TYPE columns.

Directory Tables: Metadata Refreshed Once Automatically When Stage is Created¶

When you create a stage that includes a directory table, the directory table metadata now automatically refreshes immediately a single time. Refreshing the directory table metadata synchronizes the metadata with the current list of data files in the specified stage path.

Previously, in order to register existing data files in the directory table metadata, users had to execute an ALTER STAGE … REFRESH statement after the stage was created.

This improvement is implemented through the new REFRESH_ON_CREATE parameter for the CREATE STAGE command. When REFRESH_ON_CREATE = TRUE (default value), Snowflake automatically refreshes the directory table metadata automatically a single time when the stage is created.