Snowflake to JSM Assets Integration Guide

This guide explains how to use OnLink to import Snowflake data into JSM Assets. It covers the Snowflake API connection, sample data, field mapping, validation, and optional query parameters.

Overview

OnLink can import Snowflake API responses into JSM Assets object types by using mapping statements such as key:, map:, and config:. The same approach can be used for Snowflake object types such as databases, schemas, tables, and warehouses, provided the corresponding Snowflake API endpoint and Assets object type are configured.

The high-level process is:

Create or confirm the target JSM Assets schema and object type.
Configure an OnLink Assets Data Source that uses the required authentication to connect to Snowflake.
Set the API method and Snowflake endpoint, then fetch sample data.
Map Snowflake JSON fields to JSM Assets attributes.
Use Get Data to validate the result, then run the import.

Prerequisites

A JSM Assets schema and object type for the data you want to import.
OnLink installed and available from Manage your apps.
Snowflake credentials or access token with permission to access the required API endpoints.
Permission in JSM Assets to configure data sources, mappings, and imports.
The Snowflake API endpoint URL for the object type you plan to import.

Supported Snowflake object types

The examples on this page use Snowflake SQL API https://docs.snowflake.com/en/developer-guide/sql-api/index . The same pattern can be adapted for other Snowflake data, such as databases, schemas, tables, warehouses, and users, by selecting the appropriate Snowflake API endpoint and mapping the returned JSON fields to the matching Assets attributes.

1. Prepare Snowflake credentials

Before configuring OnLink, confirm that you have Snowflake OAUTH credentials with permission to access the required API endpoints. For Snowflake API details, see https://docs.snowflake.com/en/developer-guide/sql-api/index.

Keep the credentials available for the OnLink data source configuration.

2. Configure the OnLink data source

In OnLink, add an Assets Data Source.
Set Type to OAuth2 Refresh Token.
Provide the token URL, client id, client secret and refresh token. Refer to this page for further details on OAUTH configuration in Snowflake https://docs.snowflake.com/en/user-guide/oauth-intro

3. Configure the API method

Use a POST request to retrieve records from the Snowflake SQL API. Configure the request with the required Snowflake endpoint and response data path for the data you want to import.

Set Method to POST.
Set URL to the Snowflake API endpoint for the required object type.
Set Response data path to data.
Leave other settings at their defaults unless the endpoint requires query parameters.

GET /api/v2/statements/

Use Request bodya single line JSON input that can be used as http body for Post method. Use a service like this one to format/convert your json to a single line. Example:

{"statement":"select * from T where c1=?","timeout":10,"database":"TESTDB","schema":"TESTSCHEMA","warehouse":"TESTWH","role":"TESTROLE","bindings":{"1":{"type":"FIXED","value":"123"}}}

4. Fetch sample data

Save the connection, then run it to fetch sample values from Snowflake. Use the returned sample JSON to confirm that the endpoint is correct and to identify the field names needed for mapping.

Sample response

{
  "resultSetMetaData": {
    "numRows": 2,
    "format": "jsonv2",
    "rowType": [
      { "name": "CUSTOMERID",   "type": "fixed" },
      { "name": "CUSTOMERNAME", "type": "text" },
      { "name": "SITEADDRESS",  "type": "text" },
      { "name": "METERSERIAL",  "type": "text" },
      { "name": "INSTALLDATE",  "type": "date" },
      { "name": "STATUS",       "type": "text" }
    ]
  },
  "data": [
    ["10241", "Northgate Utilities", "412 Marlin Ave, Tampa FL", "MTR-88213", "2021-06-14", "ACTIVE"],
    ["10242", "Bayside Holdings",    "77 Pier Rd, Clearwater FL", "MTR-88907", "2022-11-02", "ACTIVE"]
  ],
  "code": "090001",
  "statementHandle": "01b2c3d4-0000-1a2b-0000-abcd0000e1f2"
}

5. Configure the Assets import and mapping

After the connection is established, create the Assets import by following the JSM Assets Data Synchronization guide.

Use the fetched API sample to identify the result from Snowflake JSON field names and map them to the corresponding JSM Assets attributes. The key: mapping should identify the stable unique value used to match and update existing Assets objects.

Snowflake column	Value	OnLink mapping	Asset field

Snowflake column	Value	OnLink mapping	Asset field
`CUSTOMERID`	`10241`	`key:customerid=Customer ID`	Customer ID
`CUSTOMERNAME`	`Northgate Utilities`	`key:customername=Customer Name`	Customer Name
`SITEADDRESS`	`412 Marlin Ave, Tampa FL`	`key:siteaddress=Site Address`	Site Address
`METERSERIAL`	`MTR-88213`	`key:meterserial=Serial Number`	Serial Number
`INSTALLDATE`	`2021-06-14`	`key:installdate=Installation Date`	Installation Date
`STATUS`	`ACTIVE`	`key:status=Asset Status`

Mapping guidance

Start with one unique key: mapping so OnLink can identify whether to create or update an Assets object.
Add map: entries for each Snowflake table result field you want to store in Assets.
Compare the Snowflake sample JSON with the Assets object type attributes before running an import.
If a value is nested in the JSON response, inspect the sample output carefully and use the field path supported by the OnLink mapping configuration.

6. Validate and run the import

Use Get Data to preview the data returned by Snowflake.
Confirm that the sample records contain the expected fields and values.
Verify that the key field maps to a unique JSM Assets attribute.
Review object type mappings and attribute names for spelling and case consistency.
Run the import only after the previewed data and mapping are correct.