Snowflake connector API guide

Last updated on October 4, 2023

Overview

This guide provides detailed instructions on how to create, configure, and manage a Snowflake connector using the API. With the provided endpoints, you can seamlessly integrate the Snowflake connector into your data streaming workflows.

Create a Snowflake connector using the API

Create roles, users, and databases. Ensure that you grant the necessary privileges to users. See Configure Snowflake permission setup to learn how. This ensures that the Snowflake connector has the required permissions to access your Snowflake data.

Create the Snowflake connector configuration following the structure below via API endpoint [POST] /analytics-connector/v1/admin/connectors. Check the field configuration description in the description of the connector configuration section. Then, get a list of analytics data to fill out the filter configuration.

{
   "connectorName": "<snowflake-connector-name>",
   "connectorType": "SINK",
   "storageType": "KAFKA_SNOWFLAKE",
   "config": {
     "snowflakeAccountId": "<snowflake-accountid>",
     "snowflakeUsername": "<snowflake_username>",
     "snowflakeDatabase": "<snowflake-database>",
     "snowflakeSchema": "<snowflake-schema>",
     "flushInterval": "<flush-interval>",
     "flushSize": "<flush-size>",
     "eventType": "<event-type>",
     "model": "<table-model>",
     "isFlatten": "<column-model>",
     "tableNameFormat": "<table-name-format>"
   },
   "filter": {
     "<namespace>": [
       "<topic>"
     ]
   }
 }

After successfully creating the Snowflake connector configuration, the API response will provide the public key required for authentication.
Execute the following query in your Snowflake account to set the RSA_PUBLIC_KEY:
```
ALTER USER <snowflake_username> SET RSA_PUBLIC_KEY = "<public_key>";
```
Replace <snowflake_username> with the appropriate Snowflake username and <public_key> with the public key retrieved from the previous step.
Activate the Snowflake connector configuration using the following API endpoint [PUT] /analytics-connector/v1/admin/connectors/{id}/activate. Replace {id} with the Snowflake connector ID from the API response.

Connector configuration description

Config

snowflake-accountid: The Snowflake Account ID is the prefix before http://snowflakecomputing.com in your Snowflake account URL. Example: if your Snowflake URL is rsa42644.us-east-1.snowflakecomputing.com then the account ID is rsa42644.us-east-1.
snowflake_username: The username to authenticate with the database.
snowflake-database: The name of the snowflake database.
snowflake-schema: The name of the snowflake schema.
event-type: The type of event, there are two types justice_event and game_telemetry.
- justice_event: System-generated events from AccelByte services (Service Telemetry).
- game_telemetry: Custom telemetry events that are sent from game clients (Custom Telemetry).
flush-interval: Maximum time interval in milliseconds that the data should be periodically written into Redshift. The flush interval range is between one and 15 minutes.
flush-size: Maximum number of events that should be written into Redshift. The flush size range is 100 and 1000. Data will be sent depending on which condition is reached first between flush-interval or flush-size.
table-model: Presents how the table is created. There are two types of table models.
- single: All events will be inserted into one table based on the event type.
  - Example topics:
    - analytics_game_telemetry.dev.lightfantastic.gameStarted
    - analytics_game_telemetry.dev.lightfantastic.gameEnded
  - Expected table (only has one table with table format schema.table_name):
    - public.game_telemetry_dev
- mapping: Events will be inserted into multiple tables based on the topics.
  - Example topics:
    - analytics_game_telemetry.dev.lightfantastic.gameStarted
    - analytics_game_telemetry.dev.lightfantastic.gameEnded
  - Expected table:
    - analytics_game_telemetry_dev_lightfantastic_gamestarted
    - analytics_game_telemetry_dev_lightfantastic_gameended
table-name-format: Presents how table name will be created. There are two types of table name format.
- topic: The topic name will be table name
  - Example topics:
    - analytics_game_telemetry.dev.lightfantastic.gameStarted
    - analytics_game_telemetry.dev.lightfantastic.gameEnded
  - Expected table:
    - analytics_game_telemetry_dev_lightfantastic_gameStarted
    - analytics_game_telemetry_dev_lightfantastic_gameEnded
- event: The event name will be table name
  - Example topics:
    - analytics_game_telemetry.dev.lightfantastic.gameStarted
    - analytics_game_telemetry.dev.lightfantastic.gameEnded
  - Expected table:
    - gameStarted
    - gameEnded

column-flatten: Presents how the column is created. There are two types of the column model.

false (recommended for better performance): All events will be inserted into one column.

Example event:

{
  "EventNamespace": "lightfantastic",
  "EventTimestamp": "2023-07-20T03:30:00.036483Z",
  "EventId": "d110582c54804a29ab1d95650ca4c644",
  "Payload": {
    "winning": true,
    "hero": "Captain America",
    "kill": 9,
    "network": 912.27,
    "item": [
      {
        "name": "vibranium shield",
        "defense": 10,
        "attack": 1
      },
      {
        "name": "mjolnir hammer",
        "defense": 1,
        "attack": 9
      }
    ]
  },
  "EventName": "gameEnded"
}

Expected column:

events
{"EventNamespace":"lightfantastic","EventTimestamp":"2023-07-20T03:30:00.036483Z","EventId":"d110582c54804a29ab1d95650ca4c644","Payload":{"winning":true,"hero":"Captain America","kill":9,"network":912.27,"item":[{"name":"vibranium shield","defense":10,"attack":1},{"name":"mjolnir hammer","defense":1,"attack":9}]},"EventName":"gameEnded"}

true: All events will be inserted into multiple columns, based on event property.

Expected column:

eventid	eventnamespace	eventtimestamp	eventname	payload_item	payload_kill	payload_winning	payload_network	payload_hero
d110582c54804a29ab1d95650ca4c644	lightfantastic	2023-07-20T03:30:00.036483Z	gameEnded	[{"defense":10,"attack":1,"name":"vibranium shield"},{"defense":1,"attack":9,"name":"mjolnir hammer"}]	9	true	912.27	Captain America

注記

The column flatten feature cannot be applied to the single table model, as each event may have different payload structures, which could result in a large number of columns.

Filter

namespace: Property to filter specific namespace, using an asterisk (*) for all namespaces.
topic: Property to filter specific analytics topics, using an asterisk (*) for all topics.

Example Snowflake connector configuration

{
  "connectorName": "snowflake-connector",
  "connectorType": "SINK",
  "storageType": "KAFKA_SNOWFLAKE",
  "config": {
    "snowflakeAccountId": "id12345",
    "snowflakeUsername": "snowflakeUser",
    "snowflakeDatabase": "snowflakeDb",
    "snowflakeSchema": "snowflakeSchema",
    "flushSize": "100",
    "flushInterval": "5",
    "model": "mapping",
    "eventType": "game_telemetry",
    "isFlatten": "true",
    "tableNameFormat": "topic"
  },
  "filter": {
    "*": [
      "*"
    ]
  }
}

Overview​

Create a Snowflake connector using the API​

Connector configuration description​

Config​

Filter​

Example Snowflake connector configuration​

On this page