> For the complete documentation index, see [llms.txt](https://docs.akenza.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.akenza.io/device-management/your-data-flow/connectors/databases/snowflake.md). # Snowflake The akenza Snowflake connector enables direct ingestion of device telemetry into the Snowflake Cloud Data Platform for large-scale analytics and cloud-native data warehousing. The connector uses the [Snowflake Snowpipe Streaming API](https://docs.snowflake.com/en/user-guide/snowpipe-streaming/data-load-snowpipe-streaming-overview) to continuously ingest device data with low latency and high throughput (check its [high-performance architecture](https://docs.snowflake.com/en/user-guide/snowpipe-streaming/snowpipe-streaming-high-performance-overview)). This allows sensor data processed by akenza to be written directly into Snowflake tables without intermediate storage layers. #### Setup Follow the steps below to configure Snowflake for use with the akenza Snowflake connector. {% hint style="info" %} Prerequisites:\ Before configuring the Snowflake connector, make sure you have access to a Snowflake account.\ If you do not already have a user, you will first need to create one. Snowflake users can be created by an account administrator. The process is straightforward and can be done throught the **Snowsight** UI. Please follow the instructions in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/admin-user-management#creating-users).\ \ This guide explains how to create and manage users in Snowflake using Snowsight. {% endhint %} #### Create the database and table Create a database and a table that will store the incoming device data. {% hint style="info" %} The table schema must match the structure of the `custom payload` defined in the akenza Snowflake output connector configuration. Each field defined in the custom payload must correspond to a column in the target Snowflake table, and the data types must be compatible. {% endhint %} ```sql CREATE DATABASE ; CREATE SCHEMA ; CREATE TABLE .. ( , , ... ); ``` **Example** ```sql CREATE DATABASE TEST_DATABASE; CREATE SCHEMA TEST_SCHEMA; CREATE TABLE TEST_DATABASE.TEST_SCHEMA.TEST_TABLE ( DEVICE_ID VARCHAR, TEMPERATURE FLOAT, TIMESTAMP TIMESTAMP_TZ ); ``` **Example custom payload** ```json { "DEVICE_ID": {{device.id}}, "TEMPERATURE": {{data.temperature}}, "TIMESTAMP": {{timestamp}} } ``` The names in the payload **must match the column names used in the Snowflake table**, as these fields are mapped directly when the data is ingested through Snowpipe Streaming. #### Create the Snowpipe Streaming pipe Create a pipe that reads data from the Snowpipe Streaming data source. ```sql CREATE OR REPLACE PIPE .. AS COPY INTO .. FROM ( SELECT $1:, $1: 'STREAMING')) ); ``` **Example** ```sql CREATE OR REPLACE PIPE TEST_DATABASE.TEST_SCHEMA.TEST_PIPE AS COPY INTO TEST_DATABASE.TEST_SCHEMA.TEST_TABLE FROM ( SELECT $1:DEVICE_ID, $1:TEMPERATURE, $1:TIMESTAMP FROM TABLE(DATA_SOURCE(TYPE => 'STREAMING')) ); ``` This pipe defines how incoming streamed data is mapped to the columns of the target table. Each field referenced with `$1:` corresponds to a field in the JSON payload sent to Snowflake via Snowpipe Streaming. The field names must match the fields used in the custom payload described in the previous section. #### Create a role with required privileges for Snowpipe Streaming ingestion To follow the principle of least privilege, it is recommended to create a dedicated role that only allows the connector to ingest data through the Snowpipe Streaming pipe. This role will be assigned to a service user used by the connector. **Create the role** Create a dedicated role that will be used for Snowpipe Streaming ingestion. This role will have only the privileges necessary to ingest data ```sql CREATE ROLE ; ``` **Example** ```sql CREATE ROLE TEST_ROLE; ``` **Grant required privileges** Grant the minimal privileges required for the role to access the database, schema, and streaming pipe. {% hint style="info" %} **Note:** You have two options to run the commands: 1. **Run each command separately**: place your cursor on a command and execute it. This works fine for all four commands. 2. **Run all commands at once**: **do not just copy and paste all commands and hit run**. If you paste all four and execute without selecting them, only the command where your cursor is will run (usually the last one), which can cause errors. {% endhint %} ```sql GRANT USAGE ON DATABASE TO ROLE ; GRANT USAGE ON SCHEMA . TO ROLE ; GRANT OPERATE ON PIPE .. TO ROLE ; GRANT INSERT, SELECT ON TABLE .. TO ROLE ; ``` **Example** 
GRANT USAGE ON DATABASE TEST_DATABASE TO ROLE TEST_ROLE;
GRANT USAGE ON SCHEMA TEST_DATABASE.TEST_SCHEMA TO ROLE TEST_ROLE;
GRANT OPERATE ON PIPE TEST_DATABASE.TEST_SCHEMA.TEST_PIPE TO ROLE TEST_ROLE;
GRANT INSERT, SELECT ON TABLE TEST_DATABASE.TEST_SCHEMA.TEST_TABLE TO ROLE TEST_ROLE;
#### Create a Snowpipe Streaming user Create a user that will be used by the connector to authenticate with Snowflake. The akenza Snowflake connector authenticates using RSA key pair authentication, and supports two key management options: **Managed** and **BYOK** (bring your own key). {% tabs %} {% tab title="Managed" %} akenza generates and manages the RSA key pair. Assign the akenza public key below to the `RSA_PUBLIC_KEY` parameter when you create the Snowflake user. ```sql CREATE USER DEFAULT_ROLE = DEFAULT_NAMESPACE = . DEFAULT_WAREHOUSE = RSA_PUBLIC_KEY = ''; ``` Akenza public key: ``` MIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAvarY+sda3OZsHZGAdoc9mqiClLbs8rVs0MH/lbPYqg8e45hJ1m02vji12DOhEm/mEUzR2r/42I61osBrgBXHMgNsTHW5Mb1A3GXYrEikZv6HBrvx/6ZoVLn6QuKrdZ4dH0FUKO+6sWCu1yQ74i6ixHLqdKJbBr2Bk3ehB5YlqHb7G4TU9XMwzlzYkLMjEFP5OjLNxCMFthvIuugQ39ULcqZk7OUCbZigQdQhihF+TEbG1aDU6MxLcaQx6rB/Fq2a6iw+E9SkFxTFqQbtCi8hT1dy1pZCCKJFjQEQgMhvuLIgywYSD344RSttci/Bo8CZUjtn8prhkSEIFZaUboLN+rfhbzLzTPTfnqqP4umi5mtZRTqhIieGFmuzkOkCrhWSOuvL2l/SjEcVEHdkpIqb2kquK2EdyDZnEX0n1iaGOTe4Mtr9+wvBrMf8OaeGKro2oX4zIugDNMBzAx2duW8mr3cgyGCVX8M/BG3fdvB1cUfspwmVZNZ0IjZrPU0+69wZxgTcOze1ndXmMDTyzsI4EYeNvD02AeaVmDGY5xe57BtOVz0MQRNoCP0GCCSzjbpUXMpaMgZ/2O/UqmWFd5fMqTFtp57h/UxAHV2xln8rh2Z5JXOThK6qzuA4k8ekRfc+9seCIrPrbTAch6RguXu4sv9eHK2gLmMcOp8j6scwMdsCAwEAAQ== ``` In the akenza connector configuration, select **Managed** under **Key management**. No private key needs to be entered in akenza. **Example** ```sql CREATE USER AKENZA_USER DEFAULT_ROLE = TEST_ROLE DEFAULT_NAMESPACE = TEST_DATABASE.TEST_SCHEMA DEFAULT_WAREHOUSE = COMPUTE_WH RSA_PUBLIC_KEY ='MIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAvarY+sda3OZsHZGAdoc9mqiClLbs8rVs0MH/lbPYqg8e45hJ1m02vji12DOhEm/mEUzR2r/42I61osBrgBXHMgNsTHW5Mb1A3GXYrEikZv6HBrvx/6ZoVLn6QuKrdZ4dH0FUKO+6sWCu1yQ74i6ixHLqdKJbBr2Bk3ehB5YlqHb7G4TU9XMwzlzYkLMjEFP5OjLNxCMFthvIuugQ39ULcqZk7OUCbZigQdQhihF+TEbG1aDU6MxLcaQx6rB/Fq2a6iw+E9SkFxTFqQbtCi8hT1dy1pZCCKJFjQEQgMhvuLIgywYSD344RSttci/Bo8CZUjtn8prhkSEIFZaUboLN+rfhbzLzTPTfnqqP4umi5mtZRTqhIieGFmuzkOkCrhWSOuvL2l/SjEcVEHdkpIqb2kquK2EdyDZnEX0n1iaGOTe4Mtr9+wvBrMf8OaeGKro2oX4zIugDNMBzAx2duW8mr3cgyGCVX8M/BG3fdvB1cUfspwmVZNZ0IjZrPU0+69wZxgTcOze1ndXmMDTyzsI4EYeNvD02AeaVmDGY5xe57BtOVz0MQRNoCP0GCCSzjbpUXMpaMgZ/2O/UqmWFd5fMqTFtp57h/UxAHV2xln8rh2Z5JXOThK6qzuA4k8ekRfc+9seCIrPrbTAch6RguXu4sv9eHK2gLmMcOp8j6scwMdsCAwEAAQ=='; ``` {% endtab %} {% tab title="BYOK" %} You generate your own RSA key pair and provide the private key directly to akenza. Snowflake only ever receives the public key, akenza only ever receives the private key. Generate a 2048-bit RSA key pair, for example with OpenSSL: ```bash openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub ``` Assign the public key to the Snowflake user with `RSA_PUBLIC_KEY`: ```sql CREATE USER DEFAULT_ROLE = DEFAULT_NAMESPACE = . DEFAULT_WAREHOUSE = RSA_PUBLIC_KEY = ''; ``` **Example** ```sql CREATE USER CUSTOMER_USER DEFAULT_ROLE = TEST_ROLE DEFAULT_NAMESPACE = TEST_DATABASE.TEST_SCHEMA DEFAULT_WAREHOUSE = COMPUTE_WH RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAx2Tkc4Iv8JNV9TDQmxi32r25y4NZsPUQRtV12D9PuVhPbYFxDk67423/Tv6l3Aq9+JDxKy/sTGFAO6jhl/aZssiL2i9oEIITob16TiwSnPqWo1aHWbPkES9Sw3EoXw4TuI19BAWsvEQScAk5fdMPlqFslS/KsNzXIS+4QoImlSakFCDz+q6KaPzHj/lBNfq/H6Wd4061nWdLQ5wiShVDvhXTMlAUKJilKnd3vQLYfT9ECnzMtfuR7LSO5oQPC8NZeCOmYizC62DyoIAe+Yk2CsYF/QR4dE0rEB4WrDMRDLMGkaTvJBRXPdBItn5Vcql7UUtSQYQNG7pBOrTVLq36gQIDAQAB'; ``` In the akenza connector configuration, select **BYOK** under **Key management**, then paste the private key in PEM format, including the `BEGIN PRIVATE KEY` and `END PRIVATE KEY` lines: ``` -----BEGIN PRIVATE KEY----- MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDHZORzgi/wk1X1 MNCbGLfavbnLg1mw9RBG1XXYP0+5WE9tgXEOTrvjbf9O/qXcCr34kPErL+xMYUA7 qOGX9pmyyIvaL2gQghOhvXpOLBKc+pajVodZs+QRL1LDcShfDhO4jX0EBay8RBJw CTl90w+WoWyVL8qw3NchL7hCgiaVJqQUIPP6ropo/MeP+UE1+r8fpZ3jTrWdZ0tD nCJKFUO+FdMyUBQomKUqd3e9Ath9P0QKfMy1+5HstI7mhA8Lw1l4I6ZiLMLrYPKg gB75iTYKxgX9BHh0TSsQHhasMxEMswaRpO8kFFc90Ei2flVyqXtRS1JBhA0bukE6 tNUurfqBAgMBAAECggEAU1NW4ux/UzaHH/F347C3OHq714/7Heezlx5iYm3Gx8pf WwBSeiut+hOAM4dYUbXNgMA1gJpE0gB8ItJnACXmQh/rzdCs2OU7BgbUQzHIz4Ar DnQ9GOADyaiwmgtCV0LfO+so2NL0RjLeT6KTO4FFzOmFomy39CQ95Nj31Bdmv0IL HoomZ3qU5x4HGsT5WqIM2CwhcNgb7evDs9IqK2nIClXjPAMqoLv/HwR7XrA361NC wKwnBUmZOLXIqXmKT47MFgQGrcqOXHY389JaUI99TEbejqNWrevudgySr66T2Ze2 Lxg7ZrZcVB07O6TvGKqG3ATNd2/f+lTPbIAzHamjTwKBgQDsSzrfgzw9DySzDHrm AcaZ8cZRIQRAJ7Aw/aFNXN1nBMtYtIcAwPXDj8GoT4q6pz7HMGitFiCwRXVCBAdD vZ8Gxvd0BvFCDovwvBFZMyXBHjHNLYRGHk66zVpwiMywsJgGFqesHyvW4SO5B2Pv mLeEXps0MMaCG+s1ufnt+FIJxwKBgQDYBd5t/7BKQoJUkD4bMnOz3mf66McsSxbw jP4i2GIrLMJZFb3CcaCceKqP0nYr2hNeW1kwbBHWyL5NvzajyKgFWDyGUNhpMLpw O4TYUiRrbyGvmw789wO/t8wT/Fgj3yLB8HLZ6xajsyeuWVz40rpWpA+2dRSvKrSl S9ZKLbEZdwKBgDOro70amoKTnCfXE8zWGJKzTvWQrbhFtX6RB8O+CeAsKTdFirv8 dgGEnwlqE2ELj2vJRyqKSy2gkqqT7O7AOVUnDxd9KSaTvcAvr+98gsX/D07ZpDId EIRCwYMYNwJwK14xeAroahK7+q7MUesjzwWQFopMHCx5ZG7EM0X5Zh8jAoGAdjHv 29u0h00Qplgtww+9wsKDvgMzRwbmzEM4obQnMlxREhEVPJiTOknDeOG8io3NXyk4 CYh30j8P8NYYfVDCrfnzU8JURmnE+Ld4HiwDhUzO75g1gS9eO73CyYSebTIfgGGh VZ6NSpAxu/FlLzeNgO5PMpOo95a0NGTkIL4TUk0CgYEAutgm78tYH235XCgo0gXj cWYEUFezQNRRYApgtx6ewjgTCo8OtGfF9QsXS9v40ElYq9HN+rlEwhLykOAbf4bU XhzaYP7kxlw7zAUqkL4ceyMSMBh315rj9D4ScioY09r8EtsCZTxFBM7x0qR2vHMg AHFTSHFrNF0fpGMUd9A8C/c= -----END PRIVATE KEY----- ``` {% hint style="warning" %} Treat the private key as a secret. Store it safely, and do not share it outside the Snowflake connector configuration in akenza. {% endhint %} {% endtab %} {% endtabs %} #### Grant the required role Grant the necessary role to the user. ```sql GRANT ROLE TO USER ; ``` Example ```sql -- Managed key GRANT ROLE TEST_ROLE TO USER AKENZA_USER; -- BYOK GRANT ROLE TEST_ROLE TO USER CUSTOMER_USER; ``` #### Verify the configuration (optional step) You can run the following commands to verify that the user, pipe, privileges, and data ingestion are configured correctly. ```sql -- Check the user configuration DESC USER ; -- List pipes in the schema SHOW PIPES IN SCHEMA .; -- Check the privileges granted to the user SHOW GRANTS TO USER ; -- Inspect the data in the target table SELECT * FROM ..; -- Check the number of ingested rows SELECT COUNT(*) FROM ..; -- Optionally, you can sort the query results when inspecting the table data: SELECT * FROM .. ORDER BY DESC; or SELECT * FROM .. ORDER BY ASC; ``` ### Finding Your Snowflake Account Identifier and URL To configure a Snowflake output connector, you will need your **account identifier** and **URL**. You can find both directly in the Snowflake web interface. 1. Log in to your Snowflake account. 2. Look at the account settings (bottom-left corner or as shown in the screenshot below). Navigate to `View account details`. You will see:\ \- Account identifier - a string like `ZQPUGTG-YF50693`\ \- Account/Server URL - a hostname like `ZQPUGTG-YF50693.snowflakecomputing.com` 3. Use these values when creating a new Snowflake output connector:\ \- Account identifier: ``, like `ZQPUGTG-YF50693`\ \- URL: ``, like `https://ZQPUGTG-YF50693.snowflakecomputing.com` (prepend `https://` to the hostname)
### Troubleshooting If ingestion does not work as expected, check the following link. * [Troubleshooting Snowpipes](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-ts) --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.akenza.io/device-management/your-data-flow/connectors/databases/snowflake.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.