Snowflake SQL API Developer Guide

This guide explains how to use the Snowflake SQL API.

In this Topic:

Introduction

The Snowflake SQL API is a REST API that you can use to access and update data in a Snowflake database. You can use this API to develop custom applications and integrations that:

  • Perform simple queries

  • Manage your deployment (e.g. provision users and roles, create tables, etc.)

Capabilities of the SQL API

The Snowflake SQL API provides operations that you can use to:

  • Submit SQL statements for execution.

  • Check the status of the execution of a statement.

  • Cancel the execution of a statement.

You can use this API to execute standard queries and most DDL and DML statements. See Limitations of the SQL API for the types of statements that are not supported.

For queries, you can paginate the results (e.g. retrieve the results in pages of 10 rows each).

The endpoint for the SQL API (/api/statements) is protected by the network policies that you set up to restrict access to your account.

Limitations of the SQL API

The SQL API has the following limitations:

  • The maximum size of a page of results is approximately 10 MB.

  • The maximum number of rows returned in a page of results is 10,000.

  • The service does not currently support fetching results concurrently (i.e. in parallel with multiple threads). Requests are supported only from a single thread.

  • The following statements are not supported:

    • statements with the Snowflake SQL PUT command

    • statements with the Snowflake SQL GET command

Using the API

The SQL API is available at https://account_identifier.snowflakecomputing.com/api, where account_identifier is your account identifier.

The API consists of the /api/statements/ resource and provides the following endpoints:

Endpoint

Description

/api/statements/

Use this endpoint to submit SQL statements for execution.

/api/statements/statementHandle

Use this endpoint to check the status of the execution of a statement. (statementHandle is a unique identifier for the statement submitted for execution.)

/api/statements/statementHandle/cancel

Use this endpoint to cancel the execution of a statement.

You can use development tools and libraries for REST APIs (e.g. Postman) to send requests and handle responses.

Authenticating to the Server

When you send a request, the request must include authentication information. The next sections explain how to add this information to the request:

Using OAuth

To use OAuth, follow these steps:

  1. Set up OAuth for authentication.

    See OAuth for details on how to set up OAuth and get an OAuth token.

  2. Use SnowSQL to verify that you can use a generated OAuth token to connect to Snowflake:

    $ snowsql -a <account_identifier> -u <user> --authenticator=oauth --token=<oauth_token>
    
  3. In your application code, in each API request that you send, set the following headers:

    • Authorization: Bearer oauth_token

      where oauth_token is the generated OAuth token.

    • X-Snowflake-Authorization-Token-Type: OAUTH

      Note that you can also choose to omit the X-Snowflake-Authorization-Token-Type header. If this header is not present, Snowflake assumes that the token in the Authorization header is an OAuth token.

Using Key Pair Authentication

To use key pair authentication, follow these steps:

  1. Set up key-pair authentication.

    As part of this process, you must:

    1. Generate a public-private key pair. The generated private key should be in a file (e.g. named rsa_key.p8).

    2. Assign the public key to your Snowflake user. After you assign the key to the user, run the DESCRIBE USER command. In the output, the RSA_PUBLIC_KEY_FP property should be set to the fingerprint of the public key assigned to the user.

    For instructions on how to generate the key pair and assign a key to a user, see Key Pair Authentication & Key Pair Rotation.

  2. Use SnowSQL to verify that you can use the generated private key to connect to Snowflake:

    $ snowsql -a <account_identifier> -u <user> --private-key-path <path>/rsa_key.p8
    

    If you generated an encrypted private key, SnowSQL prompts you for the passphrase that you created when you generated the key.

  3. In your application code:

    1. Generate the fingerprint (a SHA-256 hash) of the public key for the user. Prefix the fingerprint with SHA256:. For example:

      SHA256:hash

      For an example of generating the fingerprint from a private key file in Python, see jwt-generator.

      The following sections of code demonstrate how to generate the fingerprint.

      from cryptography.hazmat.primitives.serialization import load_pem_private_key
      from cryptography.hazmat.primitives.serialization import Encoding
      from cryptography.hazmat.primitives.serialization import PublicFormat
      from cryptography.hazmat.backends import default_backend
      ..
      import base64
      from getpass import getpass
      import hashlib
      ..
      # If you generated an encrypted private key, implement this method to return
      # the passphrase for decrypting your private key. As an example, this function
      # prompts the user for the passphrase.
      def get_private_key_passphrase():
          return getpass('Passphrase for private key: ')
      
      # Private key that you will load from the private key file.
      private_key = None
      
      # Open the private key file.
      # Replace <private_key_file_path> with the path to your private key file (e.g. /x/y/z/rsa_key.p8).
      with open('<private_key_file_path>', 'rb') as pem_in:
          pemlines = pem_in.read()
          try:
              # Try to access the private key without a passphrase.
              private_key = load_pem_private_key(pemlines, None, default_backend())
          except TypeError:
              # If that fails, provide the passphrase returned from get_private_key_passphrase().
              private_key = load_pem_private_key(pemlines, get_private_key_passphrase().encode(), default_backend())
      
      # Get the raw bytes of the public key.
      public_key_raw = private_key.public_key().public_bytes(Encoding.DER, PublicFormat.SubjectPublicKeyInfo)
      
      # Get the sha256 hash of the raw bytes.
      sha256hash = hashlib.sha256()
      sha256hash.update(public_key_raw)
      
      # Base64-encode the value and prepend the prefix 'SHA256:'.
      public_key_fp = 'SHA256:' + base64.b64encode(sha256hash.digest()).decode('utf-8')
      
    2. Generate a JSON Web Token (JWT) with the following fields in the payload:

      Field

      Description

      Example

      iss

      This is the field for the issuer of the JWT. Set this to the following value:

      account_identifier.user.SHA256:public_key_fingerprint

      where:

      • account_identifier is your Snowflake account identifier.

        If you are using the account locator, exclude any region information from the account locator.

      • user is your Snowflake user name.

      • SHA256:public_key_fingerprint is the fingerprint that you generated in the previous step.

      MYORGANIZATION-MYACCOUNT.MYUSER.SHA256:public_key_fingerprint

      sub

      This is the field for the subject for the JWT. Set this to the following value:

      account_identifier.user

      MYORGANIZATION-MYACCOUNT.MYUSER

      iat

      This is the field for the time when the JWT was issued. Set this to the current time (the number of seconds since the start of the epoch in UTC).

      Note: When choosing the issue time, use the timezone of your Snowflake deployment.

      1615370644

      exp

      This is the field for the time when the JWT expires. Set this to the expiration time (the number of seconds since the start of the epoch in UTC).

      Note: The JWT is valid for at most one hour after the token is issued, even if you specify a longer expiration time.

      1615374184

      For an example of generating a JWT in Python, see jwt-generator.

      The following sections of code demonstrate how to generate a JWT. The example uses the PyJWT module, which you can install by running:

      pip install pyjwt
      
      from datetime import timedelta, timezone, datetime
      
      # This example relies on the PyJWT module (https://pypi.org/project/PyJWT/).
      import jwt
      
      # Construct the fully qualified name of the user in uppercase.
      # - Replace <account_identifier> with your account identifier.
      #   (See https://docs.snowflake.com/en/user-guide/admin-account-identifier.html .)
      # - Replace <user_name> with your Snowflake user name.
      account = "<account_identifier>"
      # Get the account identifier without the region, cloud provider, or subdomain.
      if not '.global' in account:
          idx = account.find('.')
          if idx > 0:
              account = account[0:idx]
          else:
              # Handle the replication case.
              idx = account.find('-')
              if idx > 0:
                  account = account[0:idx]
      # Use uppercase for the account identifier and user name.
      account = account.upper()
      user = "<user_name>".upper()
      qualified_username = account + "." + user
      
      # Get the current time in order to specify the time when the JWT was issued and the expiration time of the JWT.
      now = datetime.now(timezone.utc)
      # Specify the length of time during which the JWT will be valid. You can specify at most 1 hour.
      lifetime = timedelta(minutes=59)
      
      # Create the payload for the token.
      payload = {
          # Set the issuer to the fully qualified username concatenated with the public key fingerprint (calculated in the
          # previous step).
          "iss": qualified_username + '.' + public_key_fp,
      
          # Set the subject to the fully qualified username.
          "sub": qualified_username,
      
          # Set the issue time to now.
          "iat": now,
      
          # Set the expiration time, based on the lifetime specified for this object.
          "exp": now + lifetime
      }
      
      # Generate the JWT. private_key is the private key that you read from the private key file in the previous step when you
      # generated the public key fingerprint.
      encoding_algorithm="RS256"
      token = jwt.encode(payload, key=private_key, algorithm=encoding_algorithm)
      # If you are using a version of PyJWT prior to 2.0, jwt.encode returns a byte string, rather than a string.
      # If the token is a byte string, convert it to a string.
      if isinstance(token, bytes):
        token = token.decode('utf-8')
      decoded_token = jwt.decode(token, key=private_key.public_key(), algorithm=[encoding_algorithm])
      print("Generated a JWT with the following payload:\n{}".format(decoded_token))
      
    3. In each API request that you send, set the following headers:

      • Authorization: Bearer JWT

        where JWT is the token that you generated.

      • X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

Assigning a Unique Request ID for Resubmitting Requests

In some cases, it might not be clear if Snowflake executed the SQL statement in an API request (e.g. due to a network error or a timeout). You might choose to resubmit the same request to Snowflake again, in case Snowflake did not execute the statement.

If Snowflake already executed the statement in the initial request and you resubmit the request again, the statement is executed twice. For some types of requests, repeatedly executing the same statement can have unintended consequences (e.g. inserting duplicate data into a table).

To prevent Snowflake from executing the same statement twice when you resubmit a request, you can use a request ID to distinguish your request from other requests. If you specify the same request ID in the initial request and in the resubmitted request, Snowflake does not execute the statement again if the statement has already been executed successfully.

To specify a request ID, generate a universally unique identifier (UUID) and include this identifier in the requestId query parameter. For example:

POST /api/statements?requestId=ea7b46ed-bdc1-8c32-d593-764fcad64e83 HTTP/1.1

If Snowflake fails to process a request, you can submit the same request again with the same request ID. Using the same request ID indicates to the server that you are submitting the same request again.

Submitting a Request to Execute SQL Statements

To submit SQL statements for execution, send a POST request to the /api/statements/ endpoint. See POST /api/statements for details.

POST /api/statements HTTP/1.1
(request body)

Setting up the Request

In the request URL, you can set query parameters to:

For the body of the request, set the following fields:

  • Set the statement field to the SQL statement that you want to execute.

    If you want to submit multiple statements in a single request, use a semicolon (;) between statements. See Submitting Multiple SQL Statements in a Single Request for details.

  • If you include bind variables (? placeholders) in the statement, set the bindings field to an object that specifies the corresponding Snowflake data types and values for each variable.

    For details, see Using Bind Variables in a Statement.

  • To specify the warehouse, database, schema, and role to use, set the warehouse, database, schema, and role fields.

    The values in these fields are case-sensitive.

  • To set a timeout for the statement execution, set the timeout field to the maximum number of seconds to wait. If the timeout field is not set, the timeout specified by the STATEMENT_TIMEOUT_IN_SECONDS parameter is used.

  • If the query returns a large number of rows, set the pageSize parameter. Otherwise, the number of rows may exceed the default number of rows in a page.

Example of a Request

For example, the following curl command sends a SQL statement for execution. The example uses the file request-body.json to specify the body of the request.

curl -i -X POST \
    -H "Authorization: Bearer <jwt>" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "User-Agent: myApplicationName/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    -d "@request-body.json" \
    https://<account_identifier>.snowflakecomputing.com/api/statements

where:

In this example, request-body.json contains the body of the request:

{
  "statement": "select * from T where c1=?",
  "timeout": 60,
  "resultSetMetaData": {
    "format": "json"
  },
  "database": "TESTDB",
  "schema": "TESTSCHEMA",
  "warehouse": "TESTWH",
  "role": "TESTROLE",
  "bindings": {
    "1": {
      "type": "FIXED",
      "value": "123"
    }
  }
}

In the example above:

  • In the request URL, the pageSize request parameter (&pageSize=10) specifies that the result set in each response should include a maximum of 10 rows.

  • In the body of the request:

    • The statement field specifies the SQL statement to execute.

      The statement includes a bind variable (the question mark in "cl=?"), which evaluates to the first binding ("1") specified in the bindings field.

    • The timeout field specifies that the server allows 60 seconds for the statement to be executed.

    • The database, schema, warehouse, and role fields specify that the TESTDB database, TESTSCHEMA schema, TESTWH warehouse, and TESTROLE role should be used when executing the statement.

Using Bind Variables in a Statement

If you want to use bind variables (? placeholders) in the statement, use the bindings field to specify the values that should be inserted.

Set this field to a JSON object that specifies the Snowflake data type and value for each bind variable.

...
"statement": "select * from T where c1=?",
...
"bindings": {
  "1": {
    "type": "FIXED",
    "value": "123"
  }
},
...

Choose the binding type that corresponds to the type of the value that you are binding. For example, if the value is a string representing a date (e.g. 2021-04-15) and you want to insert the value into a DATE column, use the TEXT binding type.

The following table specifies the values of the type field that you can use to bind to different Snowflake data types for this preview release.

  • The first column on the left specifies the binding types that you can use.

  • The rest of the columns specify the Snowflake data type of the column where you plan to insert the data.

  • Each cell specifies the type of value that you can use with a binding type to insert data into a column of a particular Snowflake data type.

    If the cell for a binding type and Snowflake data type is empty, you cannot use the specified binding type to insert data into a column of that Snowflake data type.

Binding types supported for different Snowflake data types

Snowflake Data Types

INT / NUMBER

FLOAT

VARCHAR

BINARY

BOOLEAN

DATE

TIME

TIMESTAMP_TZ

TIMESTAMP_LTZ

TIMESTAMP_NTZ

Binding . Types

FIXED

integer

integer

integer

0 (false) / nonzero (true)

REAL

integer

int or float

int or float

0/non-0

TEXT

integer

int or float

any text

hexdec

"true"/ "false"

see notes below

see notes below

see notes below

see notes below

see notes below

BINARY

hexdec

BOOLEAN

true/false, 0/1

true/false

DATE

epoch (ms)

epoch (ms)

epoch (ms)

epoch (ms)

epoch (ms)

TIME

epoch (nano)

epoch (nano)

TIMESTAMP_TZ

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

TIMESTAMP_LTZ

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

TIMESTAMP_NTZ

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

Note the following:

  • The values of the bind variables must be strings (e.g. "1.0" for the value 1.0).

  • When using the DATE binding type, specify the number of milliseconds since the epoch.

  • When using the TIME or TIMESTAMP* binding type, specify the number of nanoseconds since the epoch.

  • When using the TIMESTAMP_TZ binding type, specify the number of nanoseconds since the epoch followed by a space and the timezone offset in minutes (e.g. 1616173619000000000 960).

  • When using the TEXT binding type:

    • To insert data into a DATE column, you can use any date format that is supported by AUTO detection.

    • To insert data into a TIME column, you can use any time format that is supported by AUTO detection.

    • To insert data into a TIMEZONE* column, you can use any date-time format that is supported by AUTO detection.

If the value is in a format not supported by Snowflake, the API returns an error:

{
  code: "100037",
  message: "<bind type> value '<value>' is not recognized",
  sqlState: "22018",
  statementHandle: "<ID>"
}

Handling the Response

By default, Snowflake executes the statement synchronously and returns one of the response codes shown in the flow chart below:

Flow chart for submitting a statement for execution

As shown in the flow chart above:

  • If you submitted a single statement that was executed successfully, Snowflake returns the HTTP response code 200 and the rows from the results in a ResultSet object.

    Use the ResultSet object to retrieve the results.

    Note that the object contains at most the number of rows specified by the pageSize parameter. If the numRows field in ResultSet_resultSetMetaData indicates that there are additional rows of results, see Retrieving Additional Pages of Results.

  • If you submitted multiple statements in a single request and the request was processed successfully, Snowflake returns the HTTP response code 200 and a ResultSet object.

    The ResultSet object does not contain any rows from the results. Instead, the data field just contains the message “Multiple statements executed successfully.”

    To retrieve the data, you must get the handles of the individual statements in the request from the statementHandles field. For each statement handle, send a request to check the status of the execution of the statement. See Checking the Status of the Statement Execution and Retrieving the Data.

    For more information about the process of handling a response for a request that specifies multiple SQL statements, see Getting the Results for Each SQL Statement in the Request.

  • If the statement takes longer than 45 seconds to execute or if you specified that the statement should be executed asynchronously, Snowflake returns the HTTP response code 202 with a QueryStatus object.

    You can send a request to the endpoint specified by the statementStatusUrl field in the QueryStatus object to check the status of the execution of the statement. See Checking the Status of the Statement Execution and Retrieving the Data.

    If you want to cancel the execution of the statement, you can send a request to the /api/statements/statementHandle/cancel, using the statement handle from the statementHandle field in the QueryStatus object. See Cancelling the Execution of a SQL Statement.

Checking the Status of the Statement Execution and Retrieving the Data

In some cases, you need to send a request to check the status of the execution of a statement:

  • When you submit a SQL statement for execution, Snowflake returns a 202 response code if the execution of the statement has not yet completed or if you submitted an asynchronous query.

    To check if the statement has finished executing, you must send a request to check the status of the statement.

  • If you submitted multiple SQL statements in a single request, you get the results of each individual statement by sending a request to check the status of the statement.

In both of these cases, you send a GET request to the /api/statements/ endpoint and append the statement handle to the end of the URL path as a path parameter. See GET /api/statements/{statementHandle} for details.

GET /api/statements/{statementHandle}

{statementHandle} is the handle of the statement that you want to check. To get the statement handle:

  • If you received response with a 202 response code, the body of the response includes a QueryStatus object. You can get the statement handle from the statementHandle field of this object.

    Note that you can also get the full URL for the request from the statementStatusUrl field of this object.

    {
      "code": "090001",
      "sqlState": "00000",
      "message": "successfully executed",
      "statementHandle": "e4ce975e-f7ff-4b5e-b15e-bf25f59371ae",
      "statementStatusUrl": "/api/statements/e4ce975e-f7ff-4b5e-b15e-bf25f59371ae"
    }
    
  • If you submitted a request containing multiple SQL statements, the body of the response includes a ResultSet object that contains a statementHandles field. You can get the handles for the individual statements from this field.

    {
      ...
      "statementHandles" : [ "019c9fce-0502-f1fc-0000-438300e02412", "019c9fce-0502-f1fc-0000-438300e02416" ],
      ...
    

For example, the following curl command checks that status of the statement with the handle e4ce975e-f7ff-4b5e-b15e-bf25f59371ae:

curl -i -X GET \
    -H "Authorization: Bearer <jwt>" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "User-Agent: myApplicationName/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    https://<account_identifier>.snowflakecomputing.com/api/statements/e4ce975e-f7ff-4b5e-b15e-bf25f59371ae

where:

When you send a request to check the status, Snowflake returns one of the response codes shown in the flow chart below:

Flow chart for checking the status of a statement submitted for execution

As shown in the flow chart above:

Getting the Results from the Response

If you submit a SQL statement for execution or check the status of statement execution, Snowflake returns a ResultSet object in the body of the response if the statement was executed successfully.

The following is an example of a ResultSet object that is returned for a query. The query specifies that the results should be paginated with 10 rows per page. The numPages field in the resultSetMetaData object indicates that there are 10 pages of results, and the numRows field indicates that the query finds a total of 100 rows.

{
 "code": "090001",
 "statementHandle": "536fad38-b564-4dc5-9892-a4543504df6c",
 "sqlState": "00000",
 "message": "successfully executed",
 "createdOn": 1597090533987,
 "statementStatusUrl": "/api/statements/536fad38-b564-4dc5-9892-a4543504df6c",
 "resultSetMetaData": {
  "page": 1,
  "pageSize": 10,
  "numPages": 10,
  "numRows": 100,
  "format": "json",
  "rowType": [
   {
    "name":"ROWNUM",
    "type":"FIXED",
    "length":0,
    "precision":38,
    "scale":0,
    "nullable":false
   }, {
    "name":"ACCOUNT_NAME",
    "type":"TEXT",
    "length":1024,
    "precision":0,
    "scale":0,
    "nullable":false
   }, {
    "name":"ADDRESS",
    "type":"TEXT",
    "length":16777216,
    "precision":0,
    "scale":0,
    "nullable":true
   }, {
    "name":"ZIP",
    "type":"TEXT",
    "length":100,
    "precision":0,
    "scale":0,
    "nullable":true
   }, {
    "name":"CREATED_ON",
    "type":"TIMESTAMP_NTZ",
    "length":0,
    "precision":0,
    "scale":3,
    "nullable":false
   }
  ]
 },
 "data": [
  ["0","customer1","1234 A Avenue","98765","1565481394123000000"],
  ["1","customer2","987 B Street","98765","1565516712912012345"],
  ["2","customer3","8777 C Blvd","98765","1565605431999999999"],
  ["3","customer4","64646 D Circle","98765","1565661272000000000"]
  ...
 ]
}

Determining If the Result Set Page Size Exceeds the Limit

The SQL API can return a result set page that has a maximum size of approximately 10 MB. If the result set page exceeds this size, the endpoint returns an HTTP response with a truncated result set in the body and the code field set to 391908:

HTTP/1.1 200 OK
...
{
  "code": "391908",
  ...

If this occurs, send the request again with the pageSize parameter set to a smaller value that fits within the maximum size of a page.

Note

Currently, Snowflake returns an HTTP 200 response code when this occurs, but this is subject to change.

Getting Metadata About the Results

In the ResultSet object returned in the response, the resultSetMetaData field contains a ResultSet_resultSetMetaData object that describes the result set (for example, the format of the results, the number of pages of results, etc.).

In this object, the rowType field contains an array of ResultSet_resultSetMetaData_rowType objects. Each object describes a column in the results. The type field specifies the Snowflake data type of the column.

{
 "resultSetMetaData": {
  "rowType": [
   {
    "name":"ROWNUM",
    "type":"FIXED",
    "length":0,
    "precision":38,
    "scale":0,
    "nullable":false
   }, {
    "name":"ACCOUNT_NAME",
    "type":"TEXT",
    "length":1024,
    "precision":0,
    "scale":0,
    "nullable":false
   }, {
    "name":"ADDRESS",
    "type":"TEXT",
    "length":16777216,
    "precision":0,
    "scale":0,
    "nullable":true
   }, {
    "name":"ZIP",
    "type":"TEXT",
    "length":100,
    "precision":0,
    "scale":0,
    "nullable":true
   }, {
    "name":"CREATED_ON",
    "type":"TIMESTAMP_NTZ",
    "length":0,
    "precision":0,
    "scale":3,
    "nullable":false
   }
  ]
 },
}

Getting the Data From the Results

In the ResultSet object in the response, the results are in the data field. The data field contains an array of arrays in JSON. For example:

{
 "data": [
  ["0","customer1","1234 A Avenue","98765","1565481394123000000"],
  ["1","customer2","987 B Street","98765","1565516712912012345"],
  ["2","customer3","8777 C Blvd","98765","1565605431999999999"],
  ["3","customer4","64646 D Circle","98765","1565661272000000000"]
 ],
}

Each array within the array contains the data for a row:

  • The first element in each array is a JSON string containing a sequence ID that starts from 0.

  • The rest of the elements in each array represent the data in a row.

The data in the result set is encoded in JSON v1.0, which means that all data is expressed as strings, regardless of the Snowflake data type of the column.

For example, the value 1.0 in a NUMBER column is returned as the string "1.0". As another example, timestamps are returned as the number of nanoseconds since the epoch. For example, the timestamp for Thursday, January 28, 2021 10:09:37.123456789 PM is returned as "1611871777123456789".

You are responsible for converting the strings to the appropriate data types.

Snowflake returns the values as strings in the following formats, depending on the Snowflake data type:

INT / NUMBER

Decimal number in a string.

FLOAT

Integer or float in a string.

VARCHAR

String.

BINARY

Hexadecimal number in a string.

BOOLEAN

0 (false) or 1 (true) in a string.

DATE

Integer value (in a string) of the number of days since the epoch (e.g. 18262).

TIME, TIMESTAMP_LTZ, TIMESTAMP_NTZ

Float value (with 9 decimal places) of the number of seconds since the epoch (e.g. 82919.000000000).

TIMESTAMP_TZ

Float value (with 9 decimal places) of the number of seconds since the epoch, followed by a space and the time zone offset in minutes (e.g. 1616173619000000000 960)

Retrieving Additional Pages of Results

If you set the pageSize request parameter to paginate the results, Snowflake returns the first page of results in the response. You can use the numPages field in the ResultSet_resultSetMetaData object in the ResultSet object to determine the total number of pages of results.

To get the next page of results or other pages of results, use the URLs provided in the Link header in the HTTP response. The Link header specifies the URLs for retrieving the first, next, previous, and last page of the results:

HTTP/1.1 200 OK
Link: </api/statements/e127cc7c-7812-4e72-9a55-3b4d4f969840?page=1>;rel="last",
      </api/statements/e127cc7c-7812-4e72-9a55-3b4d4f969840?page=1>;rel="next",
      </api/statements/e127cc7c-7812-4e72-9a55-3b4d4f969840512c?page=0>;rel="first"
...

Each URL in the header has a rel attribute with one of the following values:

  • first: The first page of results.

  • next: The next page of results.

  • prev: The previous page of results.

  • last: The last page of results.

Submitting Multiple SQL Statements in a Single Request

In some cases, you might need to specify multiple SQL statements in a request. For example, you might need to:

  • Define an explicit transaction

  • Define a stored procedure

  • Set and use session variables in statements in a request

  • Create and use temporary tables in statements in a request

  • Change the database, schema, warehouse, or role for statements in a request

The following sections explain how to submit a request that contains multiple SQL statements.

Specifying Multiple SQL Statements in the Request

To submit multiple SQL statements in a single request, use a semicolon (;) between each statement. For example:

POST /api/statements HTTP/1.1
Authorization: Bearer <jwt>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0
X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

{
  "statement": "alter session set QUERY_TAG='mytesttag'; select count(*) from mytable",
  ...

Getting the Results for Each SQL Statement in the Request

If a request that contains multiple SQL statements is processed successfully, the response does not include the data returned from executing the individual statements. Instead, the response contains a statementHandles field that contains an array of the handles for the individual statements.

Note

The statementHandles field is different from the statementHandle field:

  • The statementHandle field specifies the handle for the set of SQL statements in the request.

  • The statementHandles field is an array of the handles of the individual SQL statements in the request.

For example, suppose that you send a request that specifies two SQL statements for execution:

POST /api/statements HTTP/1.1
Authorization: Bearer <jwt>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0
X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

{
  "statement": "select * from A; select * from B",
  ...

The response contains a statementHandles field that contains an array of the handles for the individual statements.

HTTP/1.1 200 OK
...
{
  ...
  "statementHandles" : [ "019c9fce-0502-f1fc-0000-438300e02412", "019c9fce-0502-f1fc-0000-438300e02416" ],
  ...

To check the status and retrieve the data for the individual statements, send a GET request to the /api/statements/ endpoint and append the handle for each statement to the URL path. See Checking the Status of the Statement Execution and Retrieving the Data for details.

GET /api/statements/019c9fce-0502-f1fc-0000-438300e02412
...
GET /api/statements/019c9fce-0502-f1fc-0000-438300e02416
...

Handling Errors When Specifying Multiple Statements in a Request

If you specified multiple SQL statements in the request and an error occurred when executing any of the statements, Snowflake returns the HTTP response code 422 with a QueryFailureStatus object.

You can get details about the error from this object.

For example, suppose that your request specifies the following statements in which the second INSERT statement contains an error:

{
  "statement": "create or replace table table1 (i int); insert into table1 (i) values (1); insert into table1 (i) values ('This is not a valid integer.'); insert into table1 (i) values (2); select i from table1 order by i",
  ...

Snowflake returns a response with the HTTP response code 422 and with a QueryFailureStatus object that contains the details about the error:

HTTP/1.1 422 Unprocessable Entity
Content-Type: application/json
...
{
  "code" : "100132",
  "message" : "JavaScript execution error: Uncaught Execution of multiple statements failed on statement \"insert into table1 (i) values ...\" (at line 1, position 75).\nNumeric value 'This is not a valid integer.' is not recognized in SYSTEM$MULTISTMT at '    throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4\nstackstrace: \nSYSTEM$MULTISTMT line: 10",
  "sqlState" : "P0000",
  "statementHandle" : "019d6e97-0502-317e-0000-096d0041f036"
}

In the example above, the INSERT statement with the error starts at the character position 75 in the value of the statement field.

The statements before the statement with the error are executed successfully (the CREATE TABLE and first INSERT statement in this example). The statements after the statement with the error are not executed.

Creating and Calling Stored Procedures

You can use the SQL API to create and call stored procedures. The following is an example of the body of a POST request that creates a new stored procedure that passes in the name of a table and returns the number of rows in that table:

{
  "statement": "create or replace procedure sql_api_stored_proc(table_name varchar) returns varchar language javascript as $$var sql_command = \"select count(*) from \" + TABLE_NAME; var rs = snowflake.execute({sqlText: sql_command}); rs.next(); var rowCount = rs.getColumnValue(1); return rowCount; $$;",
  "resultSetMetaData": {
    "format": "json"
  },
  "role": "MY_ROLE",
  "warehouse": "MY_WAREHOUSE",
  "database": "MY_DB",
  "schema": "MY_SCHEMA"
}

The following is an example of the body of the response for this request:

{
  "resultSetMetaData": {
    "page": 0,
    "numPages": 1,
    "numRows": 1,
    "format": "json",
    "rowType": [ {
      "name": "status",
      "database": "",
      "schema": "",
      "table": "",
      "type": "text",
      "byteLength": 16777216,
      "scale": null,
      "precision": null,
      "nullable": true,
      "collation": null,
      "length": 16777216
    } ]
  },
  "data": [ [ "0", "Function SQL_API_STORED_PROC successfully created." ] ],
  "code": "090001",
  "statementStatusUrl": "/api/statements/019c9f28-0502-f257-0000-438300e0a02a?requestId=...",
  "sqlState": "00000",
  "statementHandle": "019c9f28-0502-f257-0000-438300e0a02a",
  "message": "Statement executed successfully.",
  "createdOn": 1622494569592
}

The following is an example of the body of a POST request that calls the stored procedure, passing in the table name “prices”:

{
  "statement": "call sql_api_stored_proc('prices');",
  "resultSetMetaData": {
    "format": "json"
  },
  "role": "MY_ROLE",
  "warehouse": "MY_WAREHOUSE",
  "database": "MY_DB",
  "schema": "MY_SCHEMA"
}

The following is an example of the body of the response for this request:

{
  "resultSetMetaData": {
    "page": 0,
    "numPages": 1,
    "numRows": 1,
    "format": "json",
    "rowType": [ {
      "name": "SQL_API_STORED_PROC",
      "database": "",
      "schema": "",
      "table": "",
      "type": "text",
      "byteLength": 16777216,
      "length": 16777216,
      "scale": null,
      "precision": null,
      "nullable": true,
      "collation": null
    } ]
  },
  "data": [ [ "0", "4" ] ],
  "code": "090001",
  "statementStatusUrl": "/api/statements/019c9f2a-0502-f244-0000-438300e04496?requestId=...",
  "sqlState": "00000",
  "statementHandle": "019c9f2a-0502-f244-0000-438300e04496",
  "message": "Statement executed successfully.",
  "createdOn": 1622494718694
}

Using Explicit Transactions

To execute SQL statements in an explicit transaction, you must use a single HTTP request to specify the start, end, and statements in the transaction. For example:

{
  "statement": "begin transaction; insert into table2 (i) values (1); commit; select i from table1 order by i",
  ...

As is the case when you specify multiple statements in a request, if the request was processed successfully, Snowflake returns a response containing the statementHandles field, which is set to an array of handles for the statements in the request (including the BEGIN TRANSACTION and COMMIT statements).

HTTP/1.1 200 OK
Content-Type: application/json

{
  "resultSetMetaData" : {
    "page" : 0,
    "numPages" : 1,
    "numRows" : 1,
    "format" : "json",
    "rowType" : [ {
      "name" : "multiple statement execution",
      "database" : "",
      "schema" : "",
      "table" : "",
      "type" : "text",
      "byteLength" : 16777216,
      "scale" : null,
      "precision" : null,
      "nullable" : false,
      "collation" : null,
      "length" : 16777216
    } ]
  },
  "data" : [ [ "0", "Multiple statements executed successfully." ] ],
  "code" : "090001",
  "statementHandles" : [ "019d6ed0-0502-3101-0000-096d00421082", "019d6ed0-0502-3101-0000-096d00421086", "019d6ed0-0502-3101-0000-096d0042108a", "019d6ed0-0502-3101-0000-096d0042108e" ],
  "statementStatusUrl" : "/api/statements/019d6ed0-0502-3101-0000-096d0042107e?requestId=066920fa-e589-43c6-8cca-9dcb2d4be978",
  "sqlState" : "00000",
  "statementHandle" : "019d6ed0-0502-3101-0000-096d0042107e",
  "message" : "Statement executed successfully.",
  "createdOn" : 1625684162876
}

The handles in the statementHandles field correspond to the statements in the request. In this example, the statements and their corresponding handles are:

  • BEGIN TRANSACTION (019d6ed0-0502-3101-0000-096d00421082)

  • INSERT (019d6ed0-0502-3101-0000-096d00421086)

  • COMMIT (019d6ed0-0502-3101-0000-096d0042108a)

  • SELECT (019d6ed0-0502-3101-0000-096d0042108e)

You can use these handles to check the status of each statement.

Getting Details About an Error

If the statement does not execute successfully, Snowflake returns one of the following response codes, as shown in the flow chart below:

Flow chart for handling errors during statement execution

As shown in this flow chart:

Cancelling the Execution of a SQL Statement

To cancel the execution of a statement, send a POST request to the cancel endpoint. See POST /api/statements/{statementHandle}/cancel for details.

POST /api/statements/{statementHandle}/cancel

The following flow chart illustrates the steps that you take to cancel a request.

Flow chart for cancelling the execution of a statement