Docs
Open Lodol
DocumentationIntegrationsAPI

Snowflake

App · Databases · 20 actions

Run SQL against your Snowflake data warehouse and manage what is in it: query and change table rows with bound parameters, create and drop tables, schemas and databases, list every object a role can see, start and stop warehouses, run scheduled tasks, and read query history and credit usage. Long statements can be submitted asynchronously and collected later.

Actions

ActionWhat it does
Cancel statementStop a statement that is still running, by handle.
Create databaseCreate a database, leaving an existing one of that name alone.
Create tableCreate a table from a column definition list such as 'ID NUMBER, NAME TEXT, CREATED_AT TIMESTAMP_NTZ'.
Delete rowsRemove the rows matching a condition.
Describe tableThe columns of a table: name, type, nullability, default and whether each is part of the primary key.
Drop databaseDelete a database and everything in it.
Drop tableDelete a table and everything in it.
Execute SQLRun one SQL statement and wait for its result.
Execute SQL asynchronouslySubmit a statement without waiting for it, and get back a handle.
Get current contextWho the connection is acting as: current user, role, warehouse, database, schema, account, region and Snowflake version.
Get statement resultRead a submitted statement's status and results by handle.
Insert rowAdd one row to a table.
List objectsList the databases, schemas, tables, views, warehouses, roles, tasks, stages, file formats, sequences, functions or procedures the connected role can see.
List query historyRecent queries this account has run, newest first, with who ran each, how long it took, how many rows it produced and any error.
List tasksThe scheduled tasks in a database or schema, each with its schedule, state and the SQL it runs.
List warehouse credit usageCredits each warehouse has burned over a recent window, split into compute and cloud services.
Query table rowsRead rows from one table without writing SQL: pick the columns, filter, sort and limit.
Resume or suspend warehouseStart a warehouse before a heavy job, or stop one to stop it burning credits.
Run task nowRun a scheduled task once, immediately, without waiting for its schedule.
Update rowsChange columns on the rows matching a condition.

Cancel statement

Stop a statement that is still running, by handle. A statement that has already finished cannot be cancelled.

Inputs

Statement handlestatement_handletextRequired
The handle returned when the statement was submitted.

Outputs

cancelledtrue/false
codetext
messagetext
statement_handletext
Example output
{
  "cancelled": true,
  "code": "000604",
  "message": "Statement cancelled.",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456"
}

Create database

Create a database, leaving an existing one of that name alone. Needs a role that may create databases, such as SYSADMIN.

Inputs

Database namenametextRequired
The name for the new database.
Commentcommenttext
An optional note stored with the database.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 0,
  "rows": [],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 0
}

Create table

Create a table from a column definition list such as 'ID NUMBER, NAME TEXT, CREATED_AT TIMESTAMP_NTZ'. By default an existing table of that name is left alone.

Inputs

TabletabletextRequired
The table, as TABLE, SCHEMA.TABLE or DATABASE.SCHEMA.TABLE. Use 'List objects' to find one.
Column definitionscolumn_definitionstextRequired
The columns and their types, as they would appear inside CREATE TABLE's brackets.
Replace an existing tablereplace_existingtrue/false
Drop and recreate the table if it already exists. Everything in it is lost.Default: false.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 0,
  "rows": [],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 0
}

Delete rows

Remove the rows matching a condition. The condition is required: without one the table would be emptied.

Inputs

TabletabletextRequired
The table, as TABLE, SCHEMA.TABLE or DATABASE.SCHEMA.TABLE. Use 'List objects' to find one.
WherewheretextRequired
Which rows to delete, as a SQL condition without the word WHERE. Required, so a delete cannot empty the table by accident.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 0,
  "rows": [],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {
    "numRowsDeleted": 1
  },
  "total_rows": 0
}

Describe table

The columns of a table: name, type, nullability, default and whether each is part of the primary key.

Inputs

TabletabletextRequired
The table, as TABLE, SCHEMA.TABLE or DATABASE.SCHEMA.TABLE. Use 'List objects' to find one.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 1,
  "rows": [
    {
      "ID": 1,
      "NAME": "Ada Lovelace"
    }
  ],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 1
}

Drop database

Delete a database and everything in it. Does nothing if it is not there.

Inputs

Database namenametextRequired
The database to drop.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 0,
  "rows": [],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 0
}

Drop table

Delete a table and everything in it. Does nothing if the table is not there.

Inputs

TabletabletextRequired
The table, as TABLE, SCHEMA.TABLE or DATABASE.SCHEMA.TABLE. Use 'List objects' to find one.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 0,
  "rows": [],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 0
}

Execute SQL

Run one SQL statement and wait for its result. Use '?' placeholders and supply the values separately so quotes in the data cannot change the statement. What the statement may touch is decided by the connected role's privileges.

Inputs

SQL statementstatementtextRequired
The SQL to run. Use '?' for each value supplied below. What this can touch is limited by the connected role's privileges.
Parameter valuesbindingslist
Values for each '?' in the statement, in order. Passing values here rather than pasting them into the SQL is what stops a quote in the data from changing the statement.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.
Timeout (seconds)timeoutnumber
Give up after this many seconds. Leave empty for the account default.From 5 to 3600.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 1,
  "rows": [
    {
      "ID": 1,
      "NAME": "Ada Lovelace"
    }
  ],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 1
}

Execute SQL asynchronously

Submit a statement without waiting for it, and get back a handle. Use for long jobs that would outlast a step: read the result later with 'Get statement result'.

Inputs

SQL statementstatementtextRequired
The SQL to run. Use '?' for each value supplied below. What this can touch is limited by the connected role's privileges.
Parameter valuesbindingslist
Values for each '?' in the statement, in order. Passing values here rather than pasting them into the SQL is what stops a quote in the data from changing the statement.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.
Timeout (seconds)timeoutnumber
Give up after this many seconds. Leave empty for the account default.From 5 to 3600.

Outputs

codetext
messagetext
runningtrue/false
statement_handletext
Example output
{
  "code": "333334",
  "message": "Statement submitted.",
  "running": true,
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456"
}

Get current context

Who the connection is acting as: current user, role, warehouse, database, schema, account, region and Snowflake version. The quickest way to confirm a connection works.

Inputs

Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "USER",
      "nullable": false,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 1,
  "rows": [
    {
      "ROLE": "SYSADMIN",
      "USER": "LODOL_SERVICE"
    }
  ],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 1
}

Get statement result

Read a submitted statement's status and results by handle. 'running' is true while it is still executing. A large result comes back in partitions: read the next with 'next_partition'.

Inputs

Statement handlestatement_handletextRequired
The handle returned when the statement was submitted.
Partitionpartitionnumber
Which page of a large result to read. Starts at 0.From 0 to 10000.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
runningtrue/false
Whether the statement is still executing
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 1,
  "rows": [
    {
      "ID": 1,
      "NAME": "Ada Lovelace"
    }
  ],
  "running": false,
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 1
}

Insert row

Add one row to a table. The values are sent as bound parameters, so text containing quotes is stored as data rather than changing the statement.

Inputs

TabletabletextRequired
The table, as TABLE, SCHEMA.TABLE or DATABASE.SCHEMA.TABLE. Use 'List objects' to find one.
ColumnscolumnslistRequired
The columns to fill, in order.
ValuesvalueslistRequired
One value per column, in the same order as 'Columns'.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 0,
  "rows": [],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {
    "numRowsInserted": 1
  },
  "total_rows": 0
}

List objects

List the databases, schemas, tables, views, warehouses, roles, tasks, stages, file formats, sequences, functions or procedures the connected role can see. This is how to find a table name the other actions take.

Inputs

What to listobject_typetext
Which kind of object to list.One of: DATABASES, SCHEMAS, TABLES, VIEWS, WAREHOUSES, ROLES, TASKS, STAGES, FILE FORMATS, SEQUENCES, FUNCTIONS, PROCEDURES. Default: TABLES.
In databasein_databasetext
Only look inside this database.
In schemain_schematext
Only look inside this schema. Narrower than 'In database', and used instead of it when both are given.
Name starts withstarts_withtext
Only names beginning with this text.
Maximum resultslimitnumber
How many to return at most.From 1 to 10000.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 1,
  "rows": [
    {
      "ID": 1,
      "NAME": "Ada Lovelace"
    }
  ],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 1
}

List query history

Recent queries this account has run, newest first, with who ran each, how long it took, how many rows it produced and any error. Useful for spotting a failing or slow job. Needs a database in context (the connection's default, or the field below) purely so Snowflake can resolve INFORMATION_SCHEMA: the queries returned are account-wide, not limited to it.

Inputs

Maximum querieslimitnumber
How many recent queries to return.From 1 to 1000. Default: 50.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 1,
  "rows": [
    {
      "ID": 1,
      "NAME": "Ada Lovelace"
    }
  ],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 1
}

List tasks

The scheduled tasks in a database or schema, each with its schedule, state and the SQL it runs. Snowflake's SQL API refuses to run this without a database in scope, so set 'In database' (or 'In schema') here or a default database on the connection.

Inputs

In databasein_databasetext
Only look inside this database.
In schemain_schematext
Only look inside this schema.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 1,
  "rows": [
    {
      "ID": 1,
      "NAME": "Ada Lovelace"
    }
  ],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 1
}

List warehouse credit usage

Credits each warehouse has burned over a recent window, split into compute and cloud services. This is what a Snowflake bill is made of. Needs a database in context (the connection's default, or the field below) purely so Snowflake can resolve INFORMATION_SCHEMA, and a role holding ACCOUNTADMIN or MONITOR USAGE: Snowflake restricts this data to those roles.

Inputs

Days to coverdaysnumber
How far back to look.From 1 to 365. Default: 7.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 1,
  "rows": [
    {
      "ID": 1,
      "NAME": "Ada Lovelace"
    }
  ],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 1
}

Query table rows

Read rows from one table without writing SQL: pick the columns, filter, sort and limit. A limit is always applied, so a mistyped filter cannot pull a whole warehouse-sized table into the step.

Inputs

TabletabletextRequired
The table, as TABLE, SCHEMA.TABLE or DATABASE.SCHEMA.TABLE. Use 'List objects' to find one.
Columnscolumnslist
Which columns to return. Leave empty for all of them.
Wherewheretext
A SQL condition without the word WHERE, such as STATUS = 'active'. Leave empty for every row.
Sort by columnorder_bytext
Column to sort on. Leave empty for no ordering.
Sort directionsort_directiontext
Which way to sort, when a sort column is given.One of: ASC (Ascending), DESC (Descending). Default: ASC.
Maximum rowslimitnumber
How many rows to return at most.From 1 to 10000. Default: 100.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 1,
  "rows": [
    {
      "ID": 1,
      "NAME": "Ada Lovelace"
    }
  ],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 1
}

Resume or suspend warehouse

Start a warehouse before a heavy job, or stop one to stop it burning credits. Running the step again when it is already in that state does nothing rather than failing.

Inputs

Warehousewarehouse_nametextRequired
The warehouse to start or stop.
Actionwarehouse_actiontextRequired
Whether to start or stop it.One of: RESUME (Resume (start it)), SUSPEND (Suspend (stop it)).
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 0,
  "rows": [],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 0
}

Run task now

Run a scheduled task once, immediately, without waiting for its schedule. The task must be resumed for this to work.

Inputs

Tasktask_nametextRequired
The task to run, as TASK, SCHEMA.TASK or DATABASE.SCHEMA.TASK.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 0,
  "rows": [],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {},
  "total_rows": 0
}

Update rows

Change columns on the rows matching a condition. The condition is required: without one every row in the table would be rewritten.

Inputs

TabletabletextRequired
The table, as TABLE, SCHEMA.TABLE or DATABASE.SCHEMA.TABLE. Use 'List objects' to find one.
Columns to setset_columnslistRequired
The columns to change, in order.
New valuesset_valueslistRequired
One value per column, in the same order as 'Columns to set'.
WherewheretextRequired
Which rows to change, as a SQL condition without the word WHERE. Required, so an update cannot hit the whole table by accident.
Databasedatabasetext
Run against this database. Leave empty to use the connection's.
Schemaschematext
Run against this schema. Leave empty to use the connection's.
Warehousewarehousetext
Compute to run on. Leave empty to use the connection's warehouse.
Roleroletext
Run with this role's privileges. Leave empty to use the connection's.

Outputs

codetext
Snowflake's response code
columnslist
The result set's columns, in order
has_moretrue/false
Whether another partition follows
messagetext
Snowflake's own message
next_partitionnumber
The 'partition' that reads the next page
partitionnumber
Which partition this is
partition_countnumber
How many partitions the result set has
row_countnumber
Rows in this partition
rowslist
The rows returned, each keyed by column name
sql_statetext
The SQL state code
statement_handletext
Snowflake's id for this statement
statsobject
Row counts for a statement that changed data
total_rowsnumber
Rows in the whole result set
Example output
{
  "code": "090001",
  "columns": [
    {
      "name": "ID",
      "nullable": false,
      "precision": 38,
      "scale": 0,
      "type": "fixed"
    },
    {
      "name": "NAME",
      "nullable": true,
      "precision": null,
      "scale": null,
      "type": "text"
    }
  ],
  "has_more": false,
  "message": "Statement executed successfully.",
  "next_partition": null,
  "partition": 0,
  "partition_count": 1,
  "row_count": 0,
  "rows": [],
  "sql_state": "00000",
  "statement_handle": "01b2c3d4-0000-1234-0000-abcdef123456",
  "stats": {
    "numRowsUpdated": 1
  },
  "total_rows": 0
}

Smithsonian Open Access

Previous Page

Snyk

Next Page

On this page

ActionsCancel statementCreate databaseCreate tableDelete rowsDescribe tableDrop databaseDrop tableExecute SQLExecute SQL asynchronouslyGet current contextGet statement resultInsert rowList objectsList query historyList tasksList warehouse credit usageQuery table rowsResume or suspend warehouseRun task nowUpdate rows