statements

Creates, updates, deletes, gets or lists a statements resource.

Overview

Name	statements
Type	Resource
Id	snowflake.sqlapi.statements

Fields

The following fields are returned by SELECT queries:

get_statement_status

The statement was executed successfully, and the response includes any data requested.

Name	Datatype	Description
code	string	(example: 000123)
createdOn	integer (int64)	Timestamp that specifies when the statement execution started.‌ The timestamp is expressed in milliseconds since the epoch.‌
data	array	Result set data.
message	string	(example: successfully executed)
resultSetMetaData	object
sqlState	string	(example: 42601)
statementHandle	string (uuid)	(example: 536fad38-b564-4dc5-9892-a4543504df6c)
statementStatusUrl	string (uri)
stats	object	these stats might not be available for each request.

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Optional Params	Description
get_statement_status	select	statement_handle, endpoint	requestId, partition	Checks the status of the execution of the statement with the specified statement handle. If the statement was executed successfully, the operation returns the requested partition of the result set.
submit_statement	insert	endpoint	requestId, async, nullable	Submits one or more statements for execution. You can specify that the statement should be executed asynchronously.
cancel_statement	delete	statement_handle, endpoint	requestId	Cancels the execution of the statement with the specified statement handle.

Parameters

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

Name	Datatype	Description
endpoint	string	Organization and account identifier (orgname-accountname) (default: orgname-accountname)
statement_handle	string (uuid)	The handle of the statement that you want to use (e.g. to fetch the result set or cancel execution).
async	boolean	Set to true to execute the statement asynchronously and return the statement handle. If the parameter is not specified or is set to false, a statement is executed and the first result is returned if the execution is completed in 45 seconds. If the statement execution takes longer to complete, the statement handle is returned.
nullable	boolean	Set to true to execute the statement to generate the result set including null. If the parameter is set to false, the result set value null will be replaced with a string 'null'.
partition	integer (int64)	Number of the partition of results to return. The number can range from 0 to the total number of partitions minus 1.
requestId	string (uuid)	Unique ID of the API request. This ensures that the execution is idempotent. If not specified, a new UUID is generated and assigned.

SELECT examples

get_statement_status

Checks the status of the execution of the statement with the specified statement handle. If the statement was executed successfully, the operation returns the requested partition of the result set.

SELECT
code,
createdOn,
data,
message,
resultSetMetaData,
sqlState,
statementHandle,
statementStatusUrl,
stats
FROM snowflake.sqlapi.statements
WHERE statement_handle = '{{ statement_handle }}' -- required
AND endpoint = '{{ endpoint }}' -- required
AND requestId = '{{ requestId }}'
AND partition = '{{ partition }}'
;

INSERT examples

submit_statement

Submits one or more statements for execution. You can specify that the statement should be executed asynchronously.

INSERT INTO snowflake.sqlapi.statements (
statement,
timeout,
"database",
"schema",
warehouse,
role,
bindings,
parameters,
endpoint,
requestId,
async,
nullable
)
SELECT 
'{{ statement }}',
{{ timeout }},
'{{ database }}',
'{{ schema }}',
'{{ warehouse }}',
'{{ role }}',
'{{ bindings }}',
'{{ parameters }}',
'{{ endpoint }}',
'{{ requestId }}',
'{{ async }}',
'{{ nullable }}'
RETURNING
code,
createdOn,
data,
message,
resultSetMetaData,
sqlState,
statementHandle,
statementStatusUrl,
stats
;

Manifest

# Description fields are for documentation purposes
- name: statements
props:
  - name: endpoint
    value: "{{ endpoint }}"
    description: Required parameter for the statements resource.
  - name: statement
    value: "{{ statement }}"
    description: |
      SQL statement or batch of SQL statements to execute. You can specify query, DML and DDL statements. The following statements are not supported: PUT, GET, USE, ALTER SESSION, BEGIN, COMMIT, ROLLBACK, statements that set session variables, and statements that create temporary tables and stages.
  - name: timeout
    value: {{ timeout }}
    description: |
      Timeout in seconds for statement execution. If the execution of a statement takes longer than the specified timeout, the execution is automatically canceled. To set the timeout to the maximum value (604800 seconds), set timeout to 0.
  - name: database
    value: "{{ database }}"
    description: |
      Database in which the statement should be executed. The value in this field is case-sensitive.
  - name: schema
    value: "{{ schema }}"
    description: |
      Schema in which the statement should be executed. The value in this field is case-sensitive.
  - name: warehouse
    value: "{{ warehouse }}"
    description: |
      Warehouse to use when executing the statement. The value in this field is case-sensitive.
  - name: role
    value: "{{ role }}"
    description: |
      Role to use when executing the statement. The value in this field is case-sensitive.
  - name: bindings
    value: "{{ bindings }}"
    description: |
      Values of bind variables in the SQL statement. When executing the statement, Snowflake replaces placeholders ('?' and ':name') in the statement with these specified values.
  - name: parameters
    description: |
      Session parameters that should be set before executing the statement.
    value:
      timezone: "{{ timezone }}"
      query_tag: "{{ query_tag }}"
      binary_output_format: "{{ binary_output_format }}"
      date_output_format: "{{ date_output_format }}"
      time_output_format: "{{ time_output_format }}"
      timestamp_output_format: "{{ timestamp_output_format }}"
      timestamp_ltz_output_format: "{{ timestamp_ltz_output_format }}"
      timestamp_ntz_output_format: "{{ timestamp_ntz_output_format }}"
      timestamp_tz_output_format: "{{ timestamp_tz_output_format }}"
      multi_statement_count: {{ multi_statement_count }}
  - name: requestId
    value: "{{ requestId }}"
    description: Unique ID of the API request. This ensures that the execution is idempotent. If not specified, a new UUID is generated and assigned.
    description: Unique ID of the API request. This ensures that the execution is idempotent. If not specified, a new UUID is generated and assigned.
  - name: async
    value: {{ async }}
    description: Set to true to execute the statement asynchronously and return the statement handle. If the parameter is not specified or is set to false, a statement is executed and the first result is returned if the execution is completed in 45 seconds. If the statement execution takes longer to complete, the statement handle is returned.
    description: Set to true to execute the statement asynchronously and return the statement handle. If the parameter is not specified or is set to false, a statement is executed and the first result is returned if the execution is completed in 45 seconds. If the statement execution takes longer to complete, the statement handle is returned.
  - name: nullable
    value: {{ nullable }}
    description: Set to true to execute the statement to generate the result set including null. If the parameter is set to false, the result set value null will be replaced with a string 'null'.
    description: Set to true to execute the statement to generate the result set including null. If the parameter is set to false, the result set value null will be replaced with a string 'null'.

DELETE examples

cancel_statement

Cancels the execution of the statement with the specified statement handle.

DELETE FROM snowflake.sqlapi.statements
WHERE statement_handle = '{{ statement_handle }}' --required
AND endpoint = '{{ endpoint }}' --required
AND requestId = '{{ requestId }}'
;