ApplicationExperienceEdge 1.52.0

Snowflake Node

The Snowflake Node allows for executing a SQL query against a Snowflake table.

Snowflake Node

Node Properties

Configuration for the node is broken up into several sections.

Credentials

First, provide authentication information to authorize the query. For Application and Experience, this is done by providing a Credential Name Template, which is a string template resolving to the name of one of your application’s Snowflake service credentials.

For Edge Workflows, these values must be provided in the node configuration, each of which supports string templates:

  • Account/Server URL Template: The unique URL for your Snowflake account instance, which can be found in your Snowflake account details. In most cases this is in the format of <orgname>-<account_name>.snowflakecomputing.com.
  • User Name Template: The Snowflake instance’s user through which queries using this credential are made.
  • Private Key Template: The private key for authenticating the provided user against the Snowflake database. Note: Its corresponding public key must be applied to the user in your Snowflake instance for authentication to succeed. More information can be found in Snowflake’s documentation.

Database

Next, provide the following values for selecting the database to run your query against. All of the following support string templates:

  • Database Name Template: The name of the database to run the query against.
  • Schema Template: The schema to use in reference to your query.
  • Warehouse Name Template: The name of the warehouse where the database is stored. If not provided, the query will use the default warehouse set for the user.
  • Role Template: A role under which to execute the query. This field is optional.

Query

With a database selected, provide a query to run against it. The query may be provided one of two ways …

  • Enter query as a string template …: If selected, enter your SQL query directly. The query may use string templates to reference values on the payload.
  • Provide a payload path to the query …: If selected, provide a payload path to where the query string is stored on your payload.

Optionally, you may also provide a Timeout Template, which is a string template resolving to a maximum query duration, in milliseconds, before the request times out. If not provided, this defaults to 30000 (30 seconds) - or, in Edge Workflows, the value of the MAX_EXTERNAL_CALL_TIME environment variable.

Result

Finally, provide a payload path where the result of the query will be placed on the payload. If successful, the value placed at this path will be an object with a rows array whose items vary depending on the query that was executed. If the query failed, the value at the path will be an error object containing details about the failure.

Node Examples

The following examples provide result payloads for a variety of Snowflake queries …

Describing Tables

Given the following SQL query, which returns information about a table called turbofan …

DESCRIBE TABLE turbofan;

The Snowflake Node places the following object on the payload at the specified path …

{
  "rows": [
    {
      "name": "ID",
      "type": "VARCHAR(16777216)",
      "kind": "COLUMN",
      "null?": "Y",
      "default": null,
      "primary key": "N",
      "unique key": "N",
      "check": null,
      "expression": null,
      "comment": null,
      "policy name": null,
      "privacy domain": null
    },
    {
      "name": "TIMESTAMP",
      "type": "NUMBER(38,0)",
      "kind": "COLUMN",
      "null?": "Y",
      "default": null,
      "primary key": "N",
      "unique key": "N",
      "check": null,
      "expression": null,
      "comment": null,
      "policy name": null,
      "privacy domain": null
    },
    {
      "name": "T2",
      "type": "NUMBER(38,2)",
      "kind": "COLUMN",
      "null?": "Y",
      "default": null,
      "primary key": "N",
      "unique key": "N",
      "check": null,
      "expression": null,
      "comment": null,
      "policy name": null,
      "privacy domain": null
    },
    {
      "name": "T24",
      "type": "NUMBER(38,2)",
      "kind": "COLUMN",
      "null?": "Y",
      "default": null,
      "primary key": "N",
      "unique key": "N",
      "check": null,
      "expression": null,
      "comment": null,
      "policy name": null,
      "privacy domain": null
    },
    {
      "name": "T30",
      "type": "NUMBER(38,2)",
      "kind": "COLUMN",
      "null?": "Y",
      "default": null,
      "primary key": "N",
      "unique key": "N",
      "check": null,
      "expression": null,
      "comment": null,
      "policy name": null,
      "privacy domain": null
    },
    {
      "name": "T50",
      "type": "NUMBER(38,2)",
      "kind": "COLUMN",
      "null?": "Y",
      "default": null,
      "primary key": "N",
      "unique key": "N",
      "check": null,
      "expression": null,
      "comment": null,
      "policy name": null,
      "privacy domain": null
    },
    {
      "name": "FAILURE",
      "type": "NUMBER(38,0)",
      "kind": "COLUMN",
      "null?": "Y",
      "default": null,
      "primary key": "N",
      "unique key": "N",
      "check": null,
      "expression": null,
      "comment": null,
      "policy name": null,
      "privacy domain": null
    }
  ]
}

Inserting Rows

Given the following SQL query, which inserts a row into a table called turbofan …

INSERT INTO turbofan (id, timestamp, T2, T24, T30, T50, failure)
VALUES (
  '{{device.id}}',
  {{formatDate time 'x'}},
  {{data.T2}},
  {{data.T24}},
  {{data.T30}},
  {{data.T50}},
  {{data.failure}}
);

The Snowflake Node places the following object on the payload at the specified path …

{
  "rows": [
    {
      "number of rows inserted": 1
    }
  ]
}

Querying Rows

Given the following SQL query, which returns a limited set of rows from a table called turbofan …

SELECT * FROM turbofan LIMIT 5

The Snowflake Node places the following object on the payload at the specified path …

{
  "rows": [
    {
      "ID": "657cc36e1c6c955843791957",
      "TIMESTAMP": 1738438676000,
      "T2": 518.67,
      "T24": 642.29,
      "T30": 1591.65,
      "T50": 1400.64,
      "TRA": 100,
      "W31": 39.04,
      "W32": 23.2086,
      "FAILURE": 0
    },
    {
      "ID": "657cc36e1c6c955843791957",
      "TIMESTAMP": 1738438976000,
      "T2": 491.19,
      "T24": 607.42,
      "T30": 1482.14,
      "T50": 1247.63,
      "FAILURE": 0
    },
    {
      "ID": "657cc36e1c6c955843791957",
      "TIMESTAMP": 1738439276000,
      "T2": 449.44,
      "T24": 555.72,
      "T30": 1368.24,
      "T50": 1122.24,
      "FAILURE": 0
    },
    {
      "ID": "657cc36e1c6c955843791957",
      "TIMESTAMP": 1738439576000,
      "T2": 491.19,
      "T24": 606.87,
      "T30": 1479.62,
      "T50": 1246.22,
      "FAILURE": 0
    },
    {
      "ID": "657cc36e1c6c955843791957",
      "TIMESTAMP": 1738439876000,
      "T2": 449.44,
      "T24": 555.88,
      "T30": 1357.58,
      "T50": 1129.36,
      "FAILURE": 0
    }
  ]
}

Node Errors

If the query fails for any reason, an error object will be placed on the payload at the specified path. Some common example are listed below …

Invalid Private Key

Given a private key provided in the node configuration or in the Snowflake service credential, the following will be placed on the payload at the specified path …

{
  "error": {
    "message": "error:1E08010C:DECODER routines::unsupported"
  }
}

Incorrect User Name

Given a valid private key and user name, but the corresponding public key having not been applied to the user in Snowflake, the following will be placed on the payload at the specified path …

{
  "error": {
    "message": "JWT token is invalid. [669a3764-8e36-4b06-9fe0-f1d645ccfb09]"
  }
}

Invalid SQL Query

Given an invalid SQL query, the following will be placed on the payload at the specified path …

{
  "error": {
    "message": "SQL compilation error:\nsyntax error line 1 at position 15 unexpected ';'."
  }
}

Was this page helpful?

Still looking for help? You can also search the Losant Forums or submit your question there.