Snowflake SQL

The foglamp-north-snowflake-sql plugin sends data from FogLAMP to Snowflake using the Snowflake ODBC Driver. This plugin facilitates the transfer of data from FogLAMP’s buffer to Snowflake, ensuring seamless integration with Snowflake’s cloud-based data warehouse.

snowflake_1

Configuration Details

The plugin requires the following configuration parameters:

  • Snowflake Server URL: The Snowflake server URL (e.g., your_snowflake_account.snowflakecomputing.com).

  • Snowflake Server Port: The Snowflake server port (default: 443).

  • Database Name: The Snowflake database name where the data will be stored.

  • Username: The username for connecting to the Snowflake account.

  • Password: The password for authenticating the Snowflake account.

  • FogLAMP Instance Name: FogLAMP instance name.

  • Data Source: The source of FogLAMP data to be sent to Snowflake (e.g., readings or statistics).

The plugin will create a table in Snowflake if it does not already exist. The table structure is dynamically determined based on the data provided by FogLAMP.

Current Limitation

The plugin uses a hard-coded schema name: FOGLAMP.

Note

Before using the plugin, ensure that the FOGLAMP schema already exists in the specified Snowflake database. Neither the database nor the schema is created automatically and must be set up manually.

Steps to Prepare Snowflake:

  1. Create Database (if not exists):

CREATE DATABASE IF NOT EXISTS your_database_name;

snowflake_2

  1. Create Schema:

CREATE SCHEMA IF NOT EXISTS FOGLAMP;

snowflake_3

  1. Verify Setup:

SHOW SCHEMAS IN DATABASE your_database_name;
SHOW TABLES IN SCHEMA FOGLAMP;

snowflake_4

Hints

A filter plugin foglamp-filter-snowflake-sql-hints allows hints to be added to the readings. It will affect how the data is stored within/mapped with the Snowflake SQL. Currently it supports only one hint that is table name. A new datapoint SnowflakeSql-Hint hint will be added into existing reading.

If foglamp-filter-snowflake-sql-hints filter is used with foglamp-north-snowflake-sql then a table will be created as per the table name provided in the hints

Sample hints JSON will be as follows

{
  "asset" : {
    "tablename" : "snowflake"
  }
}