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 SQL API.

As part of these changes, some functionality is being deprecated as outlined in in this article:

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.

  • Note: 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 the SQL API returns partitions, which are single physical chunks of data. The size of each partition is variable and determined automatically by Snowflake at runtime.

With these changes:

  • You can no longer specify the <>nullable parameter in a GET request. It can only be specified in a POST request to submit a SQL statement for execution.

    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.

Language: English