November 2021 - No Bundle

This topic describes the following behavior changes (if any) for the month:

  • Features that were deprecated.

  • Bundled changes that were enabled.

  • Other, non-bundled changes that were implemented.

If you have any questions about these changes, please contact Snowflake Support.

For details about the new features, enhancements, and fixes introduced this month, see November 2021.

In this Topic:

Deprecated Features

The following functionality has been deprecated in this release:

Feature

Deprecated Functionality

Notes

SQL API

No longer allows you to specify the number and size of pages returned by the API. Instead, the SQL API returns results as single physical chunks of data called partitions. Snowflake determines the number and size of partitions at runtime.

The new functionality changes and deprecates some of the parameters and other functionality related to results.

For details, see Snowflake SQL API: Changes to the Process of Submitting SQL Statements (in this article).

Other Implemented Changes

This section describes changes implemented this month that were not included in any behavior change bundle.

Snowflake SQL API: Changes to the Process of Submitting SQL Statements

With this release, the Snowflake SQL API introduces changes designed to improve the processing efficiency of the API. As part of these changes, some functionality has been deprecated as outlined in Changed and Deprecated Functionality (in this topic).

Enabling the New SQL API Functionality

To enable the new SQL API functionality for a given request, set the format field for resultSetMetaData to jsonv2 as shown in the following example:

{
  "statement": "select * from mytable",
  "resultSetMetaData": {
    "format": "jsonv2"
  },
  ...
}
Copy

To use the deprecated functionality for processing results, omit the resultSetMetaData field or set the format field to json.

Bemerkung

In the future, the SQL API will use this new functionality by default. When the SQL API is released for general availability (GA), the deprecated functionality will no longer be supported.

Changed and Deprecated Functionality

The Snowflake SQL API no longer returns data in consistently sized pages. Instead it returns partitions, which are single physical chunks. The size of each partition is variable and determined automatically by Snowflake at runtime.

This new functionality includes the following changed and deprecated functionality:

  • You can no longer specify the nullable parameter in a GET request. It can only be specified in a POST request.

    For example:

    POST /api/statements?nullable=false

  • The pageSize parameter is deprecated.

    Snowflake returns a partition of data in each response, and Snowflake determines the size of the partition that is returned. The size of a partition is variable and is based on the amount of data returned by Snowflake for a particular SQL query.

  • The page parameter is replaced by the partition parameter.

    Rather than using the page parameter to specify the next page of data to return, use the partition parameter to specify the next partition of data to return.

    After receiving the response containing the first partition of data, you can get the rest of the partitions by submitting requests with partition=partition_number, where partition_number identifies the partition of data to return. The partition number 0 identifies the first partition of data, which is returned in the initial request.

    For example, after receiving the first partition of data, you can get the second partition of data by submitting a request with the partition parameter set to 1:

    GET /api/statements/<handle>?partition=1

  • When you submit a request to execute a query, the response includes metadata that describes how the data is partitioned across responses as well as the first partition of data.

    The body of this response includes a partitionInfo field. This field contains an array of objects, each of which describes a partition of data. This first object describes the partition of data returned in this response. The rest of the objects describe the additional partitions that you can retrieve by submitting subsequent requests with partition=partition_number.

    Each object in the array specifies the number of rows and size of a partition. Your application can use this partition metadata to determine how to handle the partitions returned for subsequent requests.

    The following shows an example of part of the response:

    {
      "resultSetMetaData": {
        "numRows: 1300,
        "format": "jsonv2"
        "rowType": {
          ... // column metadata. No change
        },
        "partitionInfo": [{
            "rowCount": 12344,
            "uncompressedSize": 14384873,
          },{
            "rowCount": 47387,
            "uncompressedSize": 76483423,
            "compressedSize": 4342748
          },{
            "rowCount": 43746,
            "uncompressedSize": 43748274,
            "compressedSize": 746323
        }]
      },
      "data": [
        ["customer1", 1234 A Avenue", "98765", "2021-01-20 12:34:56.03459878"],
        ["customer2", 987 B Street", "98765", "2020-05-31 01:15:43.765432134"],
        ["customer3", 8777 C Blvd", "98765", "2019-07-01 23:12:55.123467865"],
        ["customer4", 64646 D Circle", "98765", "2021-08-03 13:43:23.0"]
      ]
    }
    
    Copy

    In this example:

    • The first object in the partitionInfo field describes the partition of data in the data field of this response.

    • The second object describes the second partition of data, which contains 47387 rows and which you can retrieve by sending the request GET /api/statements/<handle>?partition=1.

    • The third object describes the third partition of data, which contains 47386 rows and which you can retrieve by sending the request GET /api/statements/<handle>?partition=2.

  • Additional partitions are returned in compressed format.

    In the response for a GET /api/statements/<handle>?partition=partition_number request, the body contains JSON data in compressed form (using gzip).

    The response includes the HTTP header Content-Encoding: gzip, which indicates that the body of the response is compressed.

    These responses do not contain any metadata. Metadata for all partitions is provided in the first, uncompressed partition.

  • The deprecated page, pageSize, and numPages fields are no longer included in the resultSetMetaData object in the body of the response.

    With the new functionality, the SQL API no longer returns data in pages. Instead, the SQL API returns data in partitions, and you use the partitionInfo field in the resultSetMetaData object to determine the number of partitions and the number of rows in each partition.

  • Row numbers are no longer included in the result set.

    To include row numbers in the response, call the SEQUENCE or ROW_NUMBER window function in your query to generate the row numbers.

  • Boolean values are now returned as true or false rather than 1 or 0.