The Flow Snowflake Consumer allows you to push Flow Measure data into a Cloud hosted Snowflake database, by specifying your own queries to insert/update tables in the database. This can be used when integrating to other systems where staging tables are required, or to utilize other Analytics/BI/ML tools atop Flow data to perform analytics.
Measure data, model attributes, comments, limit exceptions, user details, and event context can be automatically inserted into a separate Snowflake database with its own custom schema.
Configuring a Snowflake Consumer
To create a connection using a Snowflake Consumer, right-click on Data Consumers, and select “New", Snowflake”, then "Snowflake Database".
This will list the Snowflake Consumer called “Snowflake Database”. Select The “Snowflake Database” consumer to open its editor:
To successfully push data to a Snowflake Database Consumer, the following properties need to be set.
|
Name that descriptively identifies the consumer. |
|
Your Snowflake Account Name. This is the first part of your host URL. In the example, the host URL is: beszuvm-la14343.snowflakecomputing.com. The Account details will be beszuvm-la14343 |
|
Your host URL i.e. beszuvm-la14340.snowflakecomputing.com. |
|
Name of the database to be used. |
|
Your Snowflake accounts' Username. *Note - Currently the Snowflake Consumer does not support MFA. It is advised to create a purposeful service account for Flow to use to connect to Snowflake |
|
Yor Snowflake accounts' password. |
|
Permitted time to connect to the Snowflake database. The default is 15 seconds. |
|
Use a definition file to populate insert queries. |
|
Maximum concurrent connections allowed against the Snowflake database. |
|
Option to have millisecond or nanosecond resolution |
Note: As per database-type Data Sources, a custom namespace (definition file) can also be configured for database-type Data Consumers. See How do I create a Definition for a database-type Data Consumer?
After configuring the properties, test the connection by clicking the “Test” button. If successful, a pop-up will confirm your connection or display an error message describing the issue that needs to be rectified.
Configure a Measure to Replicate
From your Flow model, select and open the configuration of a measure that you want to replicate to the configured Snowflake data consumer. Dragging the data consumer onto the specific context to be replicated will create four sample queries that need to be modified to reflect the schema of the database that you are pushing data to.
Note: Both Calendar and Event Context can be pushed to a Microsoft SQL consumer.
The four data items that can be utilized to push to the SQL database include:
|
This is where you configure your query to push measure values to a specified schema. Depending on the context, either measure values or event period values can be consumed. |
|
This is where you configure your query to push comments that have been added to measure values. When a new comment is added (even if against historical measure values) this query will be executed, allowing you to update your Snowflake database with the comments. |
|
This is where you configure your query to push exceptions. Exceptions are generated when a measure's value for a time period falls outside the specified High and/or Low limit values. |
|
Details of users that either edited measure values or added comments can be used in the consumer queries. |
An example query to push Measure values to a Snowflake table is listed below:
insert into flow.data.Integration (ENTRYID, PERIODSTART, PERIODEND, MEASUREOBJECT)
select [measurevalueid],
TO_TIMESTAMP('[PeriodStartUTC]','YYYY/MM/DD HH24:MI:SS.FF3'),
TO_TIMESTAMP('[PeriodEndUTC]','YYYY/MM/DD HH24:MI:SS.FF3'),
PARSE_JSON('{"Name":"[measurename]",
"Description":"[measuredescription]",
"Interval":"[measureintervaltype]",
"UOM":"[measureuom]",
"Hierarchy":"[hierarchicalname]",
"Quality":"[quality]",
"Value":"[measurevalue]"}')
*Note the TO_TIMESTAMP function to convert the .Net DateTime format into a structure that Snowflake will recognise as a Timestamp filed. A Variant object type is used to store a JSON structure for the Measure Value and Measure related meta data.
The Table Structure in Snowflake is defined as below:
Depending on the context selected to be pushed for the particular measure, certain placeholders can be utilized in the SQL queries that will be replaced by values received from the Integration Engine, before the query is executed against the database.
Examples of the particular queries and placeholders for both Calendar and Event contexts is found in What Placeholders can I use when configuring a database-type Data Consumer?