node-red-incorta

Node-RED nodes to read and write to a Snowflake database.

Pre-requisites

These modules are installed as part of a Node-RED environment. Node-RED is a tool for wiring together hardware devices, APIs and online services in new and interesting ways, these Snowflake nodes allow Node-Red to use Snowflake as a datasource.

Installation of the Node-Red environment is described here : Node-Red Getting Started.

You will need a Snowflake Database to connect to, this is available here: Snowflake database.

Connection to the snowflake will require jdbc and java modules. These will be installed by npm in the following "Install" step.

Install

To install from the npm website, run the following command in the Node-RED install home directory of your user, typically ~/.nodered

npm install node-red-snowflake

To install from a local zip file, run the following command in theNode-RED install home directory of your user:

npm install {path to file}/node-red-snowflake-0.1.1.tgz

The snowflake.jar file containing the JDBC driver is part of the Incorta database installation (usually found in {snowflake-install-directory}/libs). This should be copied into {node-red-user-directory}/node_modules/node-red-snowflake/jars.

Usage

Allows basic access to a Snowflake database using the JDBC protocol

This node uses SQL to query or update the configured database. By it's very nature it allows SQL injection... so be careful out there...

The result rows can be sent together as an array or as individual messages.

A Server configuration specifies the server, database and credentials to access.

Node Red Nodes

There are one node red nodes:

• snowflake sql - executes any sql query within the Snowflake environment, allows complex queries.

snowflake sql

Queries a Snowflake database based on a configured SQL query string.

The Query can be specified as a template including "mustache" format tags, which are then substituted for values from the input message.

e.g. SELECT * FROM {{{payload.table}}} WHERE ID = '{{{payload.id}}}' substitutes msg.payload.table and msg.payload.id into the query.

e.g. {{{msg.sql}}} will execute the SQL specified in the sql property of the incoming message, allowing the node red flow to formulate the query.

The result is returned in the outgoing message payload. The results can be returned as individual messages or a single array in one message.

Server configurations

Each Snowflake node has a link to a shared Server configuration. A number of Server configurations can be set to allow access to multiple Snowflake databases. Each Server configuration specifies the server, database and credentials to access.