How to Transfer Data from Sparkplug B to Snowflake

Open Automation Software can be used to transfer data from Sparkplug B Edge of Network devices to a Snowflake table, locally or over a network. Rows are written continuously using the Snowpipe Streaming REST API, with no staging files, no COPY INTO job and no warehouse running for ingestion.

This guide walks you through downloading and installing OAS, configuring a simulated Edge of Network node and a Host App connector, preparing your Snowflake account, and streaming that tag into a Snowflake table.

In this guide you will source the data from the built-in OAS MQTT broker. Alternatively, you can use an existing broker to receive your Sparkplug B data as long as you have the connection details such as the IP address and authentication credentials.

Typically you will also have an Edge of Network (EoN) node configured. For the purposes of this guide, you will create an EoN node and a Tag to simulate data changes. You can also use your own EoN node if you already have one.

For this guide on how to transfer data from Sparkplug B Edge of Network devices to Snowflake you will need:

• An OAS installation licensed for the Snowflake option
• A Snowflake account with Snowpipe Streaming available
• Access to a Snowflake administrator holding ACCOUNTADMIN, to create the user, role and table

1 - Download and Install OAS

If you have not already done so, you will need to download and install the OAS platform.

Fully functional trial versions of the software are available for Windows, Windows IoT Core, Linux, Raspberry Pi and Docker on our downloads page.

On Windows, run the downloaded setup.exe file to install the Open Automation Software platform. For a default installation, Agree to the End User License Agreement and then click the Next button on each of the installation steps until it has completed.

If you'd like to customize your installation or learn more, use the following instructions:

• Windows Installation
• Linux Installation
• Raspberry Pi Installation
• Docker Installation

The OAS Service Control application will appear when the installation finishes on Windows.

Click on each START SERVICE button to start each of the three OAS services.

2 - Configure OAS

Web Configuration Application

Configuration of the OAS server is performed from the built-in Web Configuration Application. This is the main application for accessing any settings within the OAS server and is completely self-contained within the server.

You can reach this by opening your browser to:

http://<your server>:58725/app/config

If you are browsing from the OAS machine itself, you can use localhost, otherwise use the IP address of the machine. You will also need to ensure port 58725 is open on both the client and the server or you may not be able to reach the application.

On a new installation the OAS engine has no accounts yet, so the first screen you see is Create Admin Account. Once the administrator account exists that screen is not shown again, and browsing to the application displays the sign in screen instead.

1. Enter a name for the administrator in the Admin user name field. Typically you would use admin, but you can use whatever name you choose.

2. Enter a password in the Password field, then enter the same password again in the Confirm password field.

3. Click on the Create admin & sign in button. The account is created and you are signed in to the engine.

To sign in from then on, enter the Network node of the OAS engine, then the Username and Password of your account, and click on the Sign in button.

Legacy Configuration Application

1. From your operating system start menu, open the Configure OAS application.

2. Select the Configure > Tags screen.

   Important

   If this is the first time you have installed OAS, the AdminCreate utility will run when you select a screen in the Configure menu. This will ask you to create a username and password for the admin user. This user will have full permissions in the OAS platform.

   For further information see Getting Started - Security.

3. If this is the first time you are logging in, you will see the AdminCreate utility. Follow the prompts to set up your admin account. Otherwise, select the Log In menu button and provide the Network Node, username and password.

   

   

3 - Check that the MQTT Broker is enabled

If you are using the OAS MQTT Broker, you can follow the steps below to ensure it is enabled, otherwise you can skip this step.

1. Select Options in the sidebar.

2. Expand the MQTT Broker section.

3. Check that the OAS MQTT Broker Enable toggle is turned on and the OAS MQTT Broker Port is configured as 1883.

   

4 - Create Security Group and User

When using Sparkplug B over the OAS MQTT Broker, you need to configure a security group and a user to provide Tag read/write access. You'll need these credentials when creating the Sparkplug B connector instances later.

ℹ️ You can skip this step if you already have your own MQTT Broker.

1. Select Security in the sidebar.

2. Click + Add, enter a Name such as SparkplugAccess and click Create. Once created, the security group name should appear in the list of security groups.

   

3. In the Read Tags section, ensure Disable All Tags From Reading is turned off.

   

4. In the Write Tags section, ensure Disable All Tags From Writing is turned off.

   

5. Select Users in the sidebar.

6. Click + Add, enter a User name such as sparkpluguser, set a Password, set the Security group to SparkplugAccess and click Create. Once created, the user name should appear in the list of users.

   

5 - Set up Sparkplug B Host App

In this section you will create a Sparkplug B driver configured using Host App mode. When you link EoN nodes to this host using the Host ID OAS will automatically create the Tags provided by the EoN node.

1. Select Drivers in the sidebar.

2. Click + Add, enter a name such as SpB Host App in the Driver Interface Name to give this driver interface a unique name, and click Create. Once created, the driver interface name should appear in the list of drivers.

3. Ensure the following parameters are configured:

   • Driver: Sparkplug B
   • Host: localhost
   • Port: 1883
   • User Name: sparkpluguser
   • Password: the password you configured in the previous step
   • Protocol Version: V500
   • Client ID: OAS_Client_Host
   • Mode: Host App
   • Host ID: OAS_Host (you will need to use this in your EoN node configuration)

   

   ℹ️ If you are using your own MQTT Broker, ensure that you configure the Host, Port, User Name, Password and Protocol Version accordingly.

4. Turn on Enable and click on the Apply changes button. The driver interface is not active until it is enabled and the changes are applied.

   

6 - Create Source Sparkplug B EoN node

In order to simulate the Edge of Network (EoN) node acting as a data source, we can use the features available in OAS to create an EoN using a Sparkplug B connector instance and leverage the built-in MQTT broker.

ℹ️ You can skip this section if you want to use your own existing EoN node. You'll have to ensure that your EoN node is configured with the details in step 3 below so that it can talk to the OAS Host App.

1. Select Drivers in the sidebar.

2. Click + Add, enter a name such as EoN Source Node in the Driver Interface Name to give this driver interface a unique name, and click Create. Once created, the driver interface name should appear in the list of drivers.

3. Ensure the following parameters are configured:

   • Driver: Sparkplug B
   • Host: localhost
   • Port: 1883
   • User Name: sparkpluguser
   • Password: the password you configured in the previous step
   • Protocol Version: V500
   • Client ID: OAS_Source_Node
   • Mode: Edge Node
   • Group ID: SourceGroup
   • Edge Node ID: SourceNode

   

4. Turn on Enable and click on the Apply changes button. The driver interface is not active until it is enabled and the changes are applied.

   

7 - Create a Tag attached to EoN for data source simulation

Now that the EoN node has been created we want to be able to publish data from it so we can simulate data being generated by the EoN node.

ℹ️ If you skipped creating an EoN node in OAS and you already have your own EoN node configured, you can skip this section as well and instead generate some data in your own EoN node.

1. Select Tags in the sidebar.

2. If you want to add a Tag to the root Tags group make sure another tag at the root is selected. Click the [+] button and select Add Tag.

   

   If you want to add a Tag to a Tag Group, select the Tag Group first and then click on the [+] button. Alternatively, you can right click on a Tag Group and select Add Tag.

   You can also add Tag Groups by clicking the [+] and selecting Add Group.

3. Provide a Tag Name such as TemperatureSensor and click the Create button.

   

4. To assign this Tag to a Sparkplug B node configure the Host properties to match your EoN node Group ID and Edge Node ID properties.

   • Host Group ID: SourceGroup
   • Host Edge Node ID: SourceNode
   • Host Device ID: EoN Data Source
   • Host Metric Name: Temperature

   

5. Click on the Apply changes button to apply the changes.

8 - Verify Host App Tag Generation for Data Source

You will now check to make sure the Tag Group folder structure and Tag for the Temperature metric was automatically generated and that any updates to the TemperatureSensor tag will flow through from the EoN node to the generated Tag.

1. Select Tags in the sidebar.

   Tips

   If you were already on the Tags screen, you may need to click on the ↻ refresh button next to the Node field to refresh the tag list.

2. You will see a Tag Group structure starting with a parent folder called SpB Host App and then a sub-folder for SourceGroup, another sub-folder for SourceNode and finally a sub-folder for the EoN node called EoN Data Source. The Temperature tag representing the Temperature metric is inside this final sub-folder.

   As you can see the Sparkplug B host app driver has automatically generated the folder structure and Tags.

   

3. Select the Temperature tag in the EoN Data Source sub-folder. You should see a value of zero (0) and the Client parameters configured according to the EoN node Host properties. These properties were automatically configured by OAS.

   

4. Now you will test the data flow.

   ℹ️ If you have your own EoN node configured and you skipped creating an EoN node in OAS then you can trigger a data change in your EoN node to test the configuration.

   If you created an EoN node in OAS you can select the TemperatureSensor tag in the root Tags folder. Type in a value of 24.9 in the Enter Value field and click on the Apply changes button. If everything is working correctly, this change should trigger the EoN node to publish a new value via the OAS MQTT Broker.

   

5. Select the Temperature tag again in the EoN Data Source sub-folder. You should now see a value of 24.9.

   

9 - Prepare Your Snowflake Account

Snowflake is a hosted service, so it has to be prepared before OAS can write to it. These steps are performed in Snowflake, by a Snowflake administrator. OAS does not browse or create objects in your account.

1. Find your account identifier. It is the whole subdomain of your Snowflake URL — everything before .snowflakecomputing.com. In Snowsight, go to Admin > Accounts, open the ... menu on your account and choose Manage URLs.

   Important

   Do not use the LOCATOR column on the Accounts page. It looks like an identifier, it is right there on screen, and it does not work — authentication fails with a bare 401 that explains nothing.

2. Create a user and a role in a Snowsight worksheet, as ACCOUNTADMIN.

   CREATE ROLE  IF NOT EXISTS OAS_STREAM_ROLE;
   CREATE USER  IF NOT EXISTS OAS_SVC
       DEFAULT_ROLE = OAS_STREAM_ROLE
       MUST_CHANGE_PASSWORD = FALSE;
   
   GRANT ROLE OAS_STREAM_ROLE TO USER OAS_SVC;
   ALTER USER OAS_SVC SET TYPE = SERVICE;
   

   Info

   If you already have a service user and role for OAS, skip this and use them. Nothing here is specific to this driver — any user whose default role holds the grants below will work. Note the user name for the driver's User field and move on.

   The driver never sends a role. The role that applies is the user's default role, which is why it is set above.

3. Create the target table.

   CREATE DATABASE IF NOT EXISTS OAS_DB;
   CREATE SCHEMA   IF NOT EXISTS OAS_DB.OAS_DATA;
   
   CREATE TABLE IF NOT EXISTS OAS_DB.OAS_DATA.OAS_TAG_VALUES (
       id         STRING,
       value      VARIANT,
       quality    BOOLEAN,
       timestamp  TIMESTAMP_NTZ
   );
   

   These four columns match the driver's built-in Default column mapping, so a table created this way streams with no mapping work at all. If the table already exists, leave it alone and make the column mapping match its columns instead.

4. Grant the role its privileges.

   GRANT USAGE  ON DATABASE OAS_DB                         TO ROLE OAS_STREAM_ROLE;
   GRANT USAGE  ON SCHEMA   OAS_DB.OAS_DATA                TO ROLE OAS_STREAM_ROLE;
   GRANT INSERT ON TABLE    OAS_DB.OAS_DATA.OAS_TAG_VALUES TO ROLE OAS_STREAM_ROLE;
   

   Info

   Those three grants are the entire permission surface for streaming. No warehouse, no CREATE PIPE, no OPERATE, and the driver never issues DROP or ALTER.

10 - Configure Snowflake Driver

In the following steps you will create and configure a Snowflake Driver. This driver streams the Tags you select into the table you created in the previous step. All screenshots and options in this guide will be using the built-in OAS Web Configuration interface.

1. Log into the interface using the credential created after installing OAS. The application can be found by opening your browser to the address of your OAS installation. If you are accessing it from the machine running OAS, just use localhost, otherwise the IP address of the server:

   http://<server address>:58725/app/config

   

2. In the sidebar menu, select Drivers.

   

3. Click + Add in the sidebar to create a new Driver, then set the Driver Interface Name to Snowflake Stream to give this driver interface instance a unique name.

   

   Info

   The driver interface name appears in system errors, in the driver log file name, and in the automatically generated Snowflake channel name. Choose something you will recognise later.

4. Ensure the following parameters are configured:

   • Driver: Snowflake
   • Account Identifier: your account subdomain, for example abcdefg-xy12345
   • Authentication Type: Key Pair JWT
   • User: OAS_SVC

   

   Leave Account Host Override and Ingest Host Override blank. Snowflake assigns each account a separate host for streaming and the driver discovers it on every connect.

5. Supply the private key. If you do not already have a key pair, click the GENERATE KEY button beside the Private Key Path field.

   

   Choose whether to fill the Private Key PEM field or write a file on the engine machine, pick a key size, and optionally set a passphrase.

   Important

   OAS does not register the key in Snowflake for you. The dialog shows an ALTER USER statement with the public key already in it — copy it and run it in a Snowsight worksheet as ACCOUNTADMIN. Until you do, authentication fails.

   The statement is shown once. Copy it before closing the dialog.

   If you already have a key pair, paste the private key into Private Key PEM or point Private Key Path at the .p8 file, and fill in Private Key Passphrase if the key is encrypted.

6. Set the target table:

   • Database: OAS_DB
   • Schema: OAS_DATA
   • Table: OAS_TAG_VALUES

   

   Leave Pipe and Channel Name blank. Snowflake creates a default pipe for every table, and a channel name is generated from the node and driver interface names.

   Important

   Two OAS engines using the same channel name against the same pipe will disconnect each other. Leave Channel Name blank unless you have a specific reason not to.

7. Turn on Enable, then click Apply changes to temporarily save the settings you have entered to this point. The driver interface is not active until it is enabled and the changes are applied.

11 - Test the Snowflake Connection

Before selecting Tags, confirm that OAS can authenticate. Select the Snowflake Stream driver in the list and click the TEST CONNECTION button.

The test performs three checks in order and reports the first one that fails:

12 - Map Tag Data to Table Columns

The column mapping decides what a row looks like. The table you created in the Prepare Your Snowflake Account section has four columns, and the driver's Default preset matches them exactly.

1. Click the Edit… button on the Column Mapping row of the Snowflake driver configuration.

   

2. Click Use Preset and choose Default (4). Four columns appear:

   Column	Type	Source
   id	STRING	Tag Property → Tag Path
   value	VARIANT	Tag Property → Value, as-is
   quality	BOOLEAN	Tag Property → Quality
   timestamp	TIMESTAMP_NTZ	Tag Property → Timestamp, ISO 8601

   The other preset, ISA-95 Lite, produces 24 columns with the ISA-95 hierarchy flattened one column per level. It needs a different table, so use it only if you created the table from its SQL View.

3. Optionally click + Metadata Columns to add PIPE_ID, CHANNEL_ID and STREAM_OFFSET. These let you prove in SQL that no rows were lost. If you add them here, add the matching columns to the Snowflake table as well.

   Info

   A preset is a starting point, not a mode. Once applied you can add, edit and remove columns freely.

4. Click SQL View to see the CREATE TABLE statement your mapping describes. This is the same statement the driver would use, so it is worth comparing against the table you actually created.

   

   Important

   The mapping has to match the table. The Snowflake type dropdown drives the generated DDL only — it does not change what is sent, and changing it will not fix a value that is arriving in the wrong shape. Change the column's source for that.

   OAS never alters an existing table. If your table differs from the preset, edit the mapping to match the table rather than editing the table.

5. Click OK to return to the driver configuration, Apply changes and then Save the driver so that it will be restored when the OAS server is ever restarted.

13 - Select the Tags to Publish

1. On the Snowflake driver configuration, find the Tags to Publish table and add the Tag you created earlier (for example SpB Host App.Group1.NodeA.EoN Sim.Temperature).

   

   Important

   Every Tag in this list is written to the same table using the same column mapping. Leaving the list empty is a configuration error, not an idle driver — the driver has nothing to send and will report it.

2. Set Publish Type to Continuous with the Publish Interval set to the default of 10 seconds. This will ensure data will be published on the interval, whether the value changed or not.

3. Click Enable, Apply changes and then Save the driver so that it will be restored with the OAS server is ever restarted.

14 - Verify Rows are Arriving in Snowflake

In a Snowsight worksheet, query the table:

SELECT * FROM OAS_DB.OAS_DATA.OAS_TAG_VALUES
ORDER BY TIMESTAMP DESC
LIMIT 20;

Rows normally become visible five to seven seconds after OAS sends them. That delay is Snowpipe Streaming's commit interval, not the driver waiting.

If you added the metadata columns to the mapping, you can also prove that nothing was dropped. This query returns rows only where the offset sequence skipped:

SELECT STREAM_OFFSET,
       LAG(STREAM_OFFSET) OVER (PARTITION BY PIPE_ID, CHANNEL_ID
                                ORDER BY STREAM_OFFSET) AS previous_offset
FROM   OAS_DB.OAS_DATA.OAS_TAG_VALUES
QUALIFY STREAM_OFFSET <> previous_offset + 1;

An empty result means every row the driver sent arrived exactly once.

If no rows appear at all:

While the connection is down, values are held by Store and Forward and delivered oldest first once it recovers, so a network outage delays rows rather than losing them.

15 - Save Changes

Once you have successfully configured your OAS instances, make sure you save your configuration.

On each configuration page that has a Save button, click on it.

If this is the first time you are saving the configuration, or if you are changing the name of the configuration file, OAS will ask you if you want to change the default configuration file.

If you select Yes then OAS will make this configuration file the default and if the OAS service is restarted then this file will be loaded on start-up.

If you select No then OAS will still save your configuration file, but it will not be the default file that is loaded on start-up.