<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Brad Culberson on Medium]]></title>
        <description><![CDATA[Stories by Brad Culberson on Medium]]></description>
        <link>https://medium.com/@bradculberson?source=rss-34379116c51a------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*VxEq2tyKU9TtT6JuWHd-mQ.png</url>
            <title>Stories by Brad Culberson on Medium</title>
            <link>https://medium.com/@bradculberson?source=rss-34379116c51a------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 13:11:56 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@bradculberson/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Streaming Data to Snowflake with Redpanda Connect]]></title>
            <link>https://medium.com/snowflake/streaming-data-to-snowflake-with-redpanda-connect-03c4c02dfd37?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/03c4c02dfd37</guid>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Wed, 29 Jan 2025 15:04:14 GMT</pubDate>
            <atom:updated>2025-02-01T01:49:59.769Z</atom:updated>
            <content:encoded><![CDATA[<p><a href="https://www.redpanda.com/">Redpanda</a> has released a new output for Redpanda Connect which can send data directly to Snowflake via Snowflake Snowpipe Streaming. Snowpipe Streaming is the most efficient way to load data into Snowflake and is great for customers with large data throughput. Before Redpanda Connect added support, there were several options to get data in via Snowpipe Streaming: <a href="https://docs.snowflake.com/en/user-guide/data-load-snowpipe-streaming-overview">Snowflake Streaming SDK</a>, <a href="https://docs.snowflake.com/en/user-guide/kafka-connector">Snowflake Kafka Connector</a>, <a href="https://www.striim.com/">Striim</a>, and <a href="https://aws.amazon.com/about-aws/whats-new/2024/01/stream-data-snowflake-kinesis-data-firehose-snowpipe-streaming-preview/">AWS Data Firehose</a>. Snowpipe Streaming is relatively new (GA July 2023) so it will take time for all partners to add native support. I’m really excited to see Redpanda Connect joining the partners which are leveraging this new streaming technology.</p><p>Recently, I was working with a customer who wanted to leverage streaming for a large workload, but wasn’t using Java or Kafka. With <a href="https://www.redpanda.com/connect">Redpanda Connect</a>, it was possible to run a service which exposed an http endpoint their application (non-java) could use directly. Redpanda Connect would then buffer to a write ahead log in sqlite for durability, batch the messages, and stream them to Snowflake via Snowpipe Streaming. Data could then be sent from the application via http and data in the table could be queried with a Snowflake warehouse. Data was available in seconds with Gbps throughput!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/420/0*jRaWiO3hMhuZUm9H" /></figure><p>In order to get to the 27 Gbit/sec required, we used 32 instances (r8g.2xlarge) behind an AWS Application Load Balancer to scale up the Redpanda Connect service.</p><p>We wanted to make sure that a restart of the service would not lose any in-flight data which hadn’t been sent to Snowflake yet, so we leveraged Redpanda Connect’s buffer to sqlite.</p><p>Here is the configuration we used:</p><pre>http:<br>  enabled: true<br>  address: 0.0.0.0:4195<br>  root_path: /<br>  debug_endpoints: true<br><br>input:<br>  label: &quot;www&quot;<br>  http_server:<br>    timeout: 60s<br>    address: 0.0.0.0:4196<br>    path: /post<br>    allowed_verbs:<br>      - POST<br><br>pipeline:<br>  processors:<br>    - unarchive:<br>        format: lines<br>    - mapping: root = content() <br><br>output:<br>  snowflake_streaming:<br>    account: REDACTED<br>    user: REDACTED<br>    private_key_file: REDACTED.p8<br>    private_key_pass: &quot;REDACTED&quot;<br>    role: REDACTED<br>    database: REDACTED<br>    schema: REDACTED<br>    table: REDACTED<br>    channel_prefix: &quot;HOSTNAME&quot;<br>    max_in_flight: 8<br>    batching:<br>      count: 40000<br>      period: 8s<br><br>buffer:<br>  sqlite:<br>    path: ./data.db</pre><p>These were very large messages (~23kb) so this will batch quite a bit of data before flushing to Snowflake. This provided good performance for this dataset, but you would want to test different batch sizes based on your record sizes, throughput, and application needs. Ideally you’d be batching enough data to send at least 16MB files to Snowflake. In this case we chose even larger batches to get more throughput. If too many files/small files are uploaded this will negatively affect query performance (until file migration occurs).</p><p>Check out <a href="https://docs.redpanda.com/redpanda-connect/components/outputs/snowflake_streaming/">the documentation</a> for how to configure Redpanda Connect for Snowpipe Streaming.</p><p>I’m really excited Redpanda Connect has added Snowpipe Streaming support. It’s the best way to get data into Snowflake and their framework will unlock a lot of opportunities that customers have had challenges building before. Their framework can do a lot more than what I described in this document. I’m really excited about how many input sources the framework has which can now all be sent to Snowflake with no-code.</p><p>If you have advanced streaming needs, check out the <a href="https://www.redpanda.com/connect">Redpanda Connect</a> framework now.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=03c4c02dfd37" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/streaming-data-to-snowflake-with-redpanda-connect-03c4c02dfd37">Streaming Data to Snowflake with Redpanda Connect</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Running an SFTP server in Snowflake]]></title>
            <link>https://medium.com/@bradculberson/running-an-sftp-server-in-snowflake-3b2389de637b?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/3b2389de637b</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Thu, 29 Aug 2024 20:21:54 GMT</pubDate>
            <atom:updated>2024-08-29T20:21:54.916Z</atom:updated>
            <content:encoded><![CDATA[<p>It is really common for those that have built or building data pipelines to use SFTP to either send data to another location or retrieve data for it’s own use. SFTP has been around forever and is an industry standard for transfers. Teams could use the Snowflake Drivers to solve these needs, but that requires very different tools and code.</p><p>In this post, I’ll show how you can run an SFTP server in Snowflake to allow direct access to Snowflake Stages via SFTP. This is now possible leveraging Snowflake Container Services. I’ll use <a href="https://ngrok.com/">ngrok</a> in this post, but it also works with <a href="https://ockam.io/">Ockam</a>. These services are necessary to jump over the network boundary that exists between where you are running the clients and where Snowflake is hosted.</p><p>If you want to follow along, you’ll need an account at <a href="https://ngrok.com/">ngrok</a> and a paid account in <a href="https://www.snowflake.com/en/">Snowflake</a>. Paid accounts are necessary to gain access to Snowflake Container Services.</p><p>Before going to build the containers and start the services, several things are needed:</p><ul><li>Role to run the service</li><li>Database to store the Stage</li><li>Repository to store Containers</li><li>Warehouse to have compute for queries</li><li>Compute Pool to run Containers</li><li>Network policy and integration to allow outbound access</li></ul><p>This SQL will setup all those requirements.</p><pre>USE ROLE ACCOUNTADMIN;<br><br>CREATE ROLE IF NOT EXISTS sftp_role;<br>CREATE DATABASE IF NOT EXISTS sftp_db;<br>CREATE SCHEMA IF NOT EXISTS sftp_schema;<br>CREATE OR REPLACE NETWORK RULE ngrok_egress_access<br>  MODE = EGRESS<br>  TYPE = HOST_PORT<br>  VALUE_LIST = (&#39;connect.ngrok-agent.com:443&#39;, &#39;crl.ngrok-agent.com:80&#39;);<br><br>CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION ngrok_egress_access_integration<br>  ALLOWED_NETWORK_RULES = (ngrok_egress_access)<br>  ENABLED = true;<br>GRANT OWNERSHIP ON DATABASE sftp_db TO ROLE sftp_role COPY CURRENT GRANTS;<br>GRANT OWNERSHIP ON SCHEMA sftp_db.sftp_schema TO ROLE sftp_role COPY CURRENT GRANTS;<br>GRANT USAGE ON INTEGRATION ngrok_egress_access_integration TO ROLE sftp_role;<br><br>CREATE OR REPLACE WAREHOUSE sftp_warehouse WITH<br>  WAREHOUSE_SIZE=&#39;X-SMALL&#39;;<br>GRANT USAGE ON WAREHOUSE sftp_warehouse TO ROLE sftp_role;<br><br>CREATE COMPUTE POOL sftp_compute_pool<br>  MIN_NODES = 1<br>  MAX_NODES = 1<br>  INSTANCE_FAMILY = CPU_X64_XS;<br>GRANT USAGE, MONITOR ON COMPUTE POOL sftp_compute_pool TO ROLE sftp_role;<br><br>GRANT ROLE SFTP_ROLE TO ROLE ACCOUNTADMIN;<br>USE ROLE sftp_role;<br>USE DATABASE sftp_db;<br>USE SCHEMA sftp_schema;<br>USE WAREHOUSE sftp_warehouse;<br><br>CREATE IMAGE REPOSITORY IF NOT EXISTS sftp_repository;<br>CREATE OR REPLACE STAGE sftp_stage ENCRYPTION = (type = &#39;SNOWFLAKE_SSE&#39;);<br><br>SHOW IMAGE REPOSITORIES;</pre><p>The next step is to create a container for sftp. I chose to base this post off of <a href="https://github.com/atmoz/sftp">atmoz/sftp</a> as it looked very popular and was easy to use. I made a Dockerfile that bases on that image and just adds keys so I can have keypair authentication for the SFTP user.</p><pre>FROM atmoz/sftp:latest<br><br>COPY sftp_host_rsa_key.pub /home/foo/.ssh/keys/sftp_host_rsa_key.pub<br>RUN chmod 444 /home/foo/.ssh/keys/sftp_host_rsa_key.pub</pre><p>The keypair is generated in the build script. This does more than build, I chose to upload also. You’ll see I set an environment variable, REPOSITORY_URL, set to the repository url that was created by Snowflake earlier.</p><pre>#!/bin/bash<br><br>if [ ! -f ./sftp_host_rsa_key ]<br>then<br>    echo &quot;Generating keypair for SFTP server...&quot;<br>    ssh-keygen -b 4096 -t rsa -f ./sftp_host_rsa_key -N &#39;&#39;<br>fi<br><br>echo &quot;Building SFTP server image...&quot;<br><br>docker pull --platform linux/amd64 atmoz/sftp:latest<br>docker buildx build --platform linux/amd64 -t ${REPOSITORY_URL}/sftp:latest --push .</pre><p>I’m on an m2 mac, so I had to specify the platform as Snowflake Container Services are amd64.</p><p>I’ll also create another container to run ngrok and run that in the same service.</p><p>I chose to build a custom image from the ngrok/ngrok base image here also so I could embed the configuration.</p><pre>FROM ngrok/ngrok:latest<br><br>COPY ./ngrok.yml /etc/ngrok.yml<br><br>CMD [ &quot;start&quot;, &quot;--all&quot;, &quot;--config&quot;, &quot;/etc/ngrok.yml&quot; ]</pre><p>I wanted the ngrok.yml to be generated, so I created a bash script to do that. You’ll need an environment variable, NGROK_AUTHTOKEN, set to get this working correctly.</p><pre>#!/bin/sh<br><br>IP_ADDRESS=`curl ipinfo.io/ip`<br>cat ngrok.yml.tpl | sed &quot;s/NGROK_AUTHTOKEN/$NGROK_AUTHTOKEN/g&quot; | sed &quot;s/IP_ADDRESS/$IP_ADDRESS/g&quot; &gt; ngrok.yml<br>docker buildx build --platform linux/arm64/v8,linux/amd64 -t ${REPOSITORY_URL}/ngrok:latest --push .</pre><p>This script puts the variables into the template and saves it for the container build.</p><p>Here is the template before substitution.</p><pre>version: 2<br><br>authtoken: NGROK_AUTHTOKEN<br>log_level: debug<br>log_format: json<br>log: stdout<br><br>tunnels:<br>  sftp:<br>    proto: tcp<br>    addr: localhost:22<br>    # IP restriction is a paid feature<br>    # ip_restriction:<br>    #   allow_cidrs: [IP_ADDRESS/32]</pre><p>I had to comment out the ip restriction as that wasn’t available to free accounts… The key pair auth will have to be good enough for this demo, but anyone running this in production should lock this down more with a paid subscription.</p><p>Now that both images are uploaded, I created the service. This was ran in Snowflake to start everything.</p><pre>USE ROLE SFTP_ROLE;<br>USE DATABASE SFTP_DB;<br>USE SCHEMA SFTP_SCHEMA;<br>CREATE SERVICE sftp_service<br>  IN COMPUTE POOL sftp_compute_pool  <br>  FROM SPECIFICATION $$<br>    spec:<br>      containers:<br>      - name: sftp<br>        image: /sftp_db/sftp_schema/sftp_repository/sftp:latest<br>        args:<br>         - foo::1001<br>        volumeMounts:<br>         - name: stage<br>           mountPath: /home/foo/stage<br>      - name: ngrok<br>        image: /sftp_db/sftp_schema/sftp_repository/ngrok:latest<br>      volumes:<br>      - name: stage<br>        source: &quot;@SFTP_STAGE&quot;<br>      $$<br>   EXTERNAL_ACCESS_INTEGRATIONS = (ngrok_egress_access_integration)<br>   MIN_INSTANCES=1<br>   MAX_INSTANCES=1;</pre><p>After that started, I was able to run an sftp client on my desktop and use the key pair generated in the build to connect to the url given in the ngrok console (and in the container logs). I uploaded a file which dropped the file into the Snowflake Stage, and I was able to unload data from Snowflake Tables into the Stage and offer them to the SFTP clients easily.</p><p>That was all pretty fun and shows how powerful it is to have Snowflake Container Services. It’s an extremely powerful building block that you should consider to help accelerate your projects and allows you to leverage the tools and protocols and languages you like to work in.</p><p>All code is available <a href="https://github.com/sfc-gh-bculberson/sftp_server/">here</a>.</p><p>I will follow up with another post soon on how you can use Snowflake Containers services to host a webdav server using <a href="https://www.ockam.io/">Ockam</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3b2389de637b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Python Connector for Snowflake v 3.5.0]]></title>
            <link>https://medium.com/snowflake/python-connector-for-snowflake-v-3-5-0-ea6d19400864?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/ea6d19400864</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Wed, 15 Nov 2023 14:57:10 GMT</pubDate>
            <atom:updated>2023-11-21T16:57:47.579Z</atom:updated>
            <content:encoded><![CDATA[<p>Nov 13th, 2023 was a big day.. But you probably didn’t hear the news. So I’m here to make sure you know what’s up. Snowflake released a huge upgrade to the <a href="https://github.com/snowflakedb/snowflake-connector-python">Snowflake Connector for Python</a>. It definitely deserved a major version increase… but I’m not in charge of that.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*k1gt6dnanG4uryxr.png" /></figure><p><a href="https://github.com/snowflakedb/snowflake-connector-python/releases/tag/v3.5.0">Version 3.5.0</a> of the connector adds GA support for <a href="https://arrow.apache.org/nanoarrow/0.3.0/index.html">nanoarrow</a> and removes many old/large dependencies. Snowflake connectors use <a href="https://arrow.apache.org/">Apache Arrow</a> to return results from Snowflake quickly and efficiently. This update reduces the number of packages needed in the runtime and allows for smaller AND faster deploys.</p><p>I updated a <a href="https://quickstarts.snowflake.com/guide/build_a_custom_api_in_python/index.html">quickstart</a> and got a baseline for a lambda layer pulling in all the dependencies needed using version 3.4.1. That layer was 36mb. After upgrading to 3.5.0, the layer decreased to 13mb for a savings of 23mb! Check out the <a href="https://github.com/Snowflake-Labs/sfguide-snowflake-python-api/pull/6">pull request</a> if you want to test for yourself.</p><p>If you are using the Snowflake Connector for Python, I suggest you run to grab the update. It’ll make your life better.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ea6d19400864" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/python-connector-for-snowflake-v-3-5-0-ea6d19400864">Python Connector for Snowflake v 3.5.0</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Pulling Data from an External API into Snowflake with Python]]></title>
            <link>https://medium.com/snowflake/pulling-data-from-an-external-api-into-snowflake-with-python-dcc1ba6ecc69?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/dcc1ba6ecc69</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[external-api]]></category>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Tue, 14 Nov 2023 17:39:51 GMT</pubDate>
            <atom:updated>2023-11-21T16:56:26.453Z</atom:updated>
            <content:encoded><![CDATA[<p>Recently, Snowflake released a new feature in preview, <a href="https://docs.snowflake.com/en/developer-guide/external-network-access/external-network-access-overview">External Network Access</a>, which allows code running on warehouses to access anything on the public internet (which is allowed).</p><p>I put together a really simple example which pulls data from <a href="http://jsonplaceholder.typicode.com/">jsonplaceholder.typicode.com</a> which offers a completely free json API.</p><p>This will use a Python UDTF to pull records from the API and allow it to be queried directly from SQL on a Snowflake warehouse.</p><p>First, I added a network rule which allows traffic to the api endpoints.</p><pre>CREATE OR REPLACE NETWORK RULE api_network_rule<br>  MODE = EGRESS<br>  TYPE = HOST_PORT<br>  VALUE_LIST = (&#39;jsonplaceholder.typicode.com&#39;);</pre><p>I then added that Network Rule to a new External Access Integration which can be used by other objects in Snowflake. In this case, I will use it in a Python UDTF.</p><pre>CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION apis_access_integration<br>  ALLOWED_NETWORK_RULES = (api_network_rule)<br>  ENABLED = true;</pre><p>And I created a python UDTF which uses Python requests package to pull data from the API. This UDTF specifies the External Access Integration so it can have network access to the destination needed.</p><p>Here is an example of the data from the API:</p><pre>[<br>  {<br>    &quot;userId&quot;: 1,<br>    &quot;id&quot;: 1,<br>    &quot;title&quot;: &quot;delectus aut autem&quot;,<br>    &quot;completed&quot;: false<br>  },<br>  {<br>    &quot;userId&quot;: 1,<br>    &quot;id&quot;: 2,<br>    &quot;title&quot;: &quot;quis ut nam facilis et officia qui&quot;,<br>    &quot;completed&quot;: false<br>  },<br>  {<br>    &quot;userId&quot;: 1,<br>    &quot;id&quot;: 3,<br>    &quot;title&quot;: &quot;fugiat veniam minus&quot;,<br>    &quot;completed&quot;: false<br>  }<br>]</pre><p>And the code I wrote to request and parse the data and return the rows in Python:</p><pre>CREATE OR REPLACE FUNCTION get_todos(completed boolean) returns<br>table (userId number, id number, title varchar, completed boolean)<br>LANGUAGE PYTHON<br>RUNTIME_VERSION = 3.8<br>HANDLER = &#39;ApiData&#39;<br>EXTERNAL_ACCESS_INTEGRATIONS = (apis_access_integration)<br>PACKAGES = (&#39;requests&#39;)<br>AS<br>$$<br>import requests<br> <br>class ApiData:  <br>    def process(self, completed):<br>        data = requests.get(&quot;https://jsonplaceholder.typicode.com/todos&quot;).json()<br>        for row in data:<br>            if row[&quot;completed&quot;] == completed:<br>                yield (row[&quot;userId&quot;], row[&quot;id&quot;], row[&quot;title&quot;], row[&quot;completed&quot;])<br>$$;</pre><p>I added some filtering into the function so I could query it for completed and non-completed todos as that seems like something that would be useful.</p><p>To test this capability, I just ran a simple select on the new udtf.</p><pre>select * from table(get_todos(FALSE));</pre><p>The data is now easily queryable in Snowflake and could be joined/stored as needed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9Q8ULJrseF58F2FbNm3idg.png" /></figure><p>External Network Access is extremely useful to add integration options to external sources. I can’t wait to see what you build!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dcc1ba6ecc69" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/pulling-data-from-an-external-api-into-snowflake-with-python-dcc1ba6ecc69">Pulling Data from an External API into Snowflake with Python</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Public Endpoints in Snowpark Container Services]]></title>
            <link>https://medium.com/@bradculberson/public-endpoints-in-snowpark-container-services-256d73765e9c?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/256d73765e9c</guid>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Wed, 08 Nov 2023 17:28:41 GMT</pubDate>
            <atom:updated>2023-11-09T00:44:35.413Z</atom:updated>
            <content:encoded><![CDATA[<p>Snowpark Container Services is currently in Preview on Snowflake. I’m fortunate enough to be able to get my hands on anything in preview and have been helping customers build prototypes on Snowpark Container Services. Currently when a container is launched in Snowflake, the client of those endpoints in the containers must be an authenticated Snowflake user. This is great by default because it’s easy and very secure. You can grant users the ability to use those container endpoints with Role Based Access Control.</p><p>But if you need to authenticate users that aren’t Snowflake users, how can you do that?</p><p>Snowpark Container Services can communicate with public endpoints, which means we can expose a public endpoint if we have the right technology hosted outside the container. In this post, I’ll use <a href="https://ngrok.com/">ngrok</a> because I’ve used it before and it is very developer friendly. You can solve this in other ways without using ngrok.</p><p>Inbound connections from the user is reverse proxied into the container which means the container doesn’t need to have an exposed public port. In the code later, you’ll see all external inbound access is denied.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IKSgS4XZvr3SLHtUgARpZw.png" /><figcaption>Network and authentication paths using ngrok on a Snowpark Container Service</figcaption></figure><p>I wrote a simple echo service in Flask, and packaged that in a container and deployed that service in Snowpark Container Services. If you are not familiar with flask, check out very similar code in a <a href="https://quickstarts.snowflake.com/guide/build_a_custom_api_in_python/index.html?">quickstart</a>.</p><p>Users visiting this service before this post authenticated to Snowflake to gain access to the service. The service runs as the role owner and has access to Snowflake with owner’s rights. The service I will be offering to users in this post will be accessible to users authenticated to Google IdP with any email address @snowflake.com. All queries would be running as owner’s rights.</p><p>ngrok has a <a href="https://github.com/ngrok/ngrok-python">Python sdk</a> which can be used to connect to their infrastructure. To add it to my project, I added this to my Dockerfile:</p><pre>ARG BASE_IMAGE=python:3.10-slim-buster<br>FROM $BASE_IMAGE<br>COPY echo_service.py ./<br>COPY templates/ ./templates/<br><br>EXPOSE 8000<br>RUN pip install --upgrade pip &amp;&amp; \<br>    pip install flask ngrok<br><br>ENV NGROK_AUTHTOKEN=274qQREItGQCfUHEzEN1OX3kSOm_3iRr4m82JYVsSvkQoTNyD<br>CMD [&quot;python3&quot;, &quot;echo_service.py&quot;]</pre><p>The NGROK_AUTHTOKEN is available in the ngrok dashboard after you create a free dev account.</p><p>Click the Your Authtoken under Getting Started in the dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/698/0*HKIu8znWuhr5-QEI" /></figure><p>Don’t worry, that token isn’t real :)</p><p>I wanted to be able to do custom authentication, so I created a new <a href="https://ngrok.com/docs/guides/how-to-set-up-a-custom-domain/">Domain</a> and <a href="https://ngrok.com/docs/network-edge/edges/">Edge</a> in ngrok. The defaults work fine for these.</p><p>Under Cloud Edge in dashboard, add a Domain and Edge.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*EGD8kI8BXSF88fg6" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*wXjIkYHyfmXFrxwY" /></figure><p>The edge in ngrok will give you an identifier which can be used in the ngrok connection to automatically connect the ngrok custom domain to the container on the public internet. You can see it in the screenshot above with the edge=edghts_2XrFea08KOrxjjMPI6w3GJ4OUlv.</p><p>Under the edge configuration, I chose to do OAuth using Google as the Identity Provider, used the ngrok-managed OAuth application and restricted access to anyone @snowflake.com.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Vn6_Edj8NsJbVvqH" /></figure><p>In the Python service, I imported the ngrok package and added a connection to ngrok using the label from the edge I configured.</p><pre>from flask import Flask<br>from flask import abort, request<br>from flask import make_response<br>from flask import render_template<br>import logging<br>import os<br>import sys<br>import ngrok<br>import ipaddress<br><br>SERVICE_HOST = os.getenv(&#39;SERVER_HOST&#39;, &#39;0.0.0.0&#39;)<br>SERVICE_PORT = os.getenv(&#39;SERVER_PORT&#39;, 8080)<br>CHARACTER_NAME = os.getenv(&#39;CHARACTER_NAME&#39;, &#39;I&#39;)<br><br>listener = ngrok.connect(addr=f&quot;localhost:{SERVICE_PORT}&quot;,<br>                         authtoken_from_env=True,<br>                         proto=&quot;labeled&quot;,<br>                         labels=&quot;edge:edghts_2XrFea08KOrxjjMPI6w3GJ4OUlv&quot;)<br><br>def get_logger(logger_name):<br>    logger = logging.getLogger(logger_name)<br>    logger.setLevel(logging.INFO)<br>    handler = logging.StreamHandler(sys.stdout)<br>    handler.setLevel(logging.INFO)<br>    handler.setFormatter(<br>        logging.Formatter(<br>            &#39;%(name)s [%(asctime)s] [%(levelname)s] %(message)s&#39;))<br>    logger.addHandler(handler)<br>    return logger<br><br><br>logger = get_logger(&#39;echo-service&#39;)<br>app = Flask(__name__)<br><br><br>@app.before_request<br>def limit_remote_addr():<br>    if ipaddress.ip_address(request.remote_addr).is_private:<br>        return<br>    else:<br>        # don&#39;t allow any inbound connections<br>        abort(403)  # Forbidden<br><br>@app.get(&quot;/healthcheck&quot;)<br>def readiness_probe():<br>    logger.info(f&quot;client ip is {request.remote_addr}&quot;)<br>    return &quot;I&#39;m ready!&quot;<br><br><br>@app.post(&quot;/echo&quot;)<br>def echo():<br>    &#39;&#39;&#39;<br>    Main handler for input data sent by Snowflake.<br>    &#39;&#39;&#39;<br>    message = request.json<br>    logger.debug(f&#39;Received request: {message}&#39;)<br><br>    if message is None or not message[&#39;data&#39;]:<br>        logger.info(&#39;Received empty message&#39;)<br>        return {}<br><br>    input_rows = message[&#39;data&#39;]<br>    logger.info(f&#39;Received {len(input_rows)} rows&#39;)<br><br>    output_rows = [[row[0], get_echo_response(row[1])] for row in input_rows]<br>    logger.info(f&#39;Produced {len(output_rows)} rows&#39;)<br><br>    response = make_response({&quot;data&quot;: output_rows})<br>    response.headers[&#39;Content-type&#39;] = &#39;application/json&#39;<br>    logger.debug(f&#39;Sending response: {response.json}&#39;)<br>    return response<br><br><br>@app.route(&quot;/ui&quot;, methods=[&quot;GET&quot;, &quot;POST&quot;])<br>def ui():<br>    &#39;&#39;&#39;<br>    Main handler for providing a web UI.<br>    &#39;&#39;&#39;<br>    if request.method == &quot;POST&quot;:<br>        # getting input in HTML form<br>        input_text = request.form.get(&quot;input&quot;)<br>        # display input and output<br>        return render_template(&quot;basic_ui.html&quot;,<br>            echo_input=input_text,<br>            echo_reponse=get_echo_response(input_text))<br>    return render_template(&quot;basic_ui.html&quot;)<br><br><br>def get_echo_response(input):<br>    return f&#39;{CHARACTER_NAME} said {input}&#39;<br><br><br>if __name__ == &#39;__main__&#39;:<br>    app.run(host=SERVICE_HOST, port=SERVICE_PORT)</pre><p>To test locally, I did a docker run. That worked the first time of course ;)… Then I pushed the updated container to Snowpark Container Services. After the service was suspended and started (to get the new container), it was online according to the ngrok console and the URL was working!</p><p>After going to the uri <a href="https://fine-hog-verbally.ngrok-free.app/ui">https://fine-hog-verbally.ngrok-free.app/ui</a> I was redirected to Google to authenticate.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/470/0*EP4cQ4obnVkPdSwL" /></figure><p>After logging in, I was redirected to the service which is running in Snowpark Container Services without any additional authentication needed.</p><p>This architecture could support authentication modes offered by ngrok (like IP Addresses, other IdPs), completely public endpoints, or those secured by custom code built into the application hosted in Snowflake.</p><p>I am getting really excited for Snowpark Container Services and can’t wait to see what you will build!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=256d73765e9c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating a Keypair for Snowflake]]></title>
            <link>https://medium.com/snowflake/creating-a-keypair-for-snowflake-3f39f9448d7e?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/3f39f9448d7e</guid>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Wed, 19 Jul 2023 21:57:24 GMT</pubDate>
            <atom:updated>2023-07-19T22:11:27.298Z</atom:updated>
            <content:encoded><![CDATA[<p><a href="https://docs.snowflake.com/en/user-guide/key-pair-auth">Keypair authentication</a> is useful for services to connect directly to Snowflake. It’s a better choice over password authentication because credentials can be rotated without downtime. Each user in Snowflake can have 2 public keys associated at any time.</p><p>To create a keypair, you can use openssl. This guide will use a web based version of openssl which was compiled to webassembly, but you can run the same commands directly using openssl.</p><p><strong>Step 1:</strong> Go to <a href="https://www.cryptool.org/en/cto/openssl">https://www.cryptool.org/en/cto/openssl</a> and click on “Generate Keys”.</p><p><strong>Step 2:</strong> Choose Key type: RSA</p><p><strong>Step 3:</strong> In the “Generate private key” section, choose 2048-bit Key length, output to file</p><p>This will automatically generate the first command that is needed to be run to generate the keypair which generates the private key.</p><pre>openssl genrsa -out privkey.pem 2048</pre><p><strong>Step 4:</strong> Click Run</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*yq1ED3cZOimIMHOl" /></figure><p><strong>Step 5:</strong> In the “Derive public key” section, choose Output to file and the Public key output format: PEM format to generate the public key.</p><pre>openssl rsa -pubout -in /privkey.pem -outform PEM -out pubkey.pem</pre><p><strong>Step 6:</strong> Click Run</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*P_tGBDdiM515302n" /></figure><p><strong>Step 7:</strong> Go to the Files section and Download the privkey.pem and the pubkey.pem files.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Xl8rzOL8KCoIYHe8" /></figure><p>Open the pubkey.pem in your favorite text editor, the file will look like this:</p><pre>-----BEGIN PUBLIC KEY-----<br>MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA2lN+3ajOnQSiwTP82c12<br>u1da2sTyOzAqLLVlAcnpSvrWKD21lYX+3DFlCOvOoNz8byzCmXgM+p5Sygtus02y<br>ko3ANe/IvPWPWxjs/p+BOo7enpBxKn5qAgf0D8g9Lvnzrv/QYZpvW70EzlOWyYUC<br>4C+9MECa6xkC8/OdmtPhq5gb37D18ewJm+BZr8txffjpmoPoVi+hmQMBb8YsLz6L<br>YJSeeAoxjyfu/pEmd9tnIgGzPpx+uf6bI/fA9AR89xXxZn6+2QqH0FIGbDTd19XA<br>Z+8zaJGAMNjnq4+RBtH57Fxx8P7x/LJhuIcpvXHce5z3w11ENKzizOEVbXU5Qs0P<br>wwIDAQAB<br>-----END PUBLIC KEY-----</pre><p>To set this keypair on a user in Snowflake you would run the following SQL in Snowsight. You have to remove the first and last lines as well as line breaks.</p><pre>ALTER USER &lt;USERNAME&gt; SET RSA_PUBLIC_KEY=&#39;MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA2lN+3ajOnQSiwTP82c12u1da2sTyOzAqLLVlAcnpSvrWKD21lYX+3DFlCOvOoNz8byzCmXgM+p5Sygtus02yko3ANe/IvPWPWxjs/p+BOo7enpBxKn5qAgf0D8g9Lvnzrv/QYZpvW70EzlOWyYUC4C+9MECa6xkC8/OdmtPhq5gb37D18ewJm+BZr8txffjpmoPoVi+hmQMBb8YsLz6LYJSeeAoxjyfu/pEmd9tnIgGzPpx+uf6bI/fA9AR89xXxZn6+2QqH0FIGbDTd19XAZ+8zaJGAMNjnq4+RBtH57Fxx8P7x/LJhuIcpvXHce5z3w11ENKzizOEVbXU5Qs0PwwIDAQAB&#39;;</pre><p>To test the keypair, you can use <a href="https://developers.snowflake.com/snowsql/">SnowSql</a>.</p><p>Start snowsql with the private_key_path set to the privkey.pem downloaded along with your accountname, username. After successful connection you’ve validated your keypair is working as intended. Now keep your private key secure and make a plan for your rotation schedule.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3f39f9448d7e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/creating-a-keypair-for-snowflake-3f39f9448d7e">Creating a Keypair for Snowflake</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Storage Lifecycles in Snowflake]]></title>
            <link>https://medium.com/snowflake/storage-lifecycles-in-snowflake-f2bdc2b92713?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/f2bdc2b92713</guid>
            <category><![CDATA[icebergs]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[storage]]></category>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Mon, 27 Mar 2023 22:02:02 GMT</pubDate>
            <atom:updated>2023-03-27T22:02:02.385Z</atom:updated>
            <content:encoded><![CDATA[<p>Many Snowflake users have rapidly growing data which has hot (actively queried) and cold components (rarely queried). For example: Many users query recent data but as that data ages it is used less and it’s desired to have more economical storage options as well as have automatic expiration/deletion. It is very convenient when this lifecycle can be fully automated.</p><p>In this post, I will show an example of how this can be easily done using Snowflake storage paired with External Tables in AWS S3. Hot data (1 year) will be kept in Snowflake native storage format. A task will be created to move that data daily out to S3 which is configured with Intelligent-Tiering for another year and then moved to Glacier Instant Retrieval. After 3 years that data is automatically expired and deleted. A view can be used to query data in hot and cold together for the 3 years until deletion.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*qpkvM1C4JAGbTdXp" /><figcaption>Photo by <a href="https://unsplash.com/@sophiasimoes?utm_source=medium&amp;utm_medium=referral">Sophia Simoes</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>In order to show this working, I will use the <a href="https://docs.snowflake.com/en/sql-reference/functions/generator">generator</a> to create data that is spread over time from 1970 to 2023. This is creating a great sample dataset to work with, it will take about 4 minutes to complete on a Large warehouse.</p><pre>CREATE OR REPLACE TABLE EXAMPLE_DATA AS<br>SELECT UUID_STRING() as ID, uniform(1, 10, RANDOM(12)) as NUM_1, uniform(1, 1000000, RANDOM(12)) as NUM_2, randstr(255, random()) as STR_1,<br>TO_TIME(dateadd(second, seq4(), DATE_FROM_PARTS(1970,1,1))) as EVT_TIME,<br>TO_DATE(dateadd(second, seq4(), DATE_FROM_PARTS(1970,1,1))) as EVT_DATE<br>FROM TABLE(GENERATOR(ROWCOUNT =&gt; 2147483647)) WHERE EVT_DATE &lt; &#39;2023–03–26&#39;;</pre><p>To age out this data to S3, I need an S3 bucket setup alongside a <a href="https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration">storage integration</a> and external stage. I will use this storage integration to unload data from the EXAMPLE_DATA table as well as use that as an external table for queries.</p><p>For the first lifecycle move from Snowflake native storage to S3, I’ll run a manual unload and delete. This will have to be moved to a task later after I verify it’s all working. This large manual unload is only needed once as future tasks will only unload a single day, it will take around 40 minutes to complete on a large warehouse.</p><pre>COPY INTO @LIFECYCLE_EXAMPLE_STAGE<br>FROM (SELECT ID, NUM_1, NUM_2, STR_1, EVT_TIME, EVT_DATE from EXAMPLE_DATA WHERE EVT_DATE &lt;= DATEADD(year, -1, current_date()))<br>PARTITION BY (&#39;date=&#39; || to_varchar(EVT_DATE, &#39;YYYY-MM-DD&#39;))<br>FILE_FORMAT = (TYPE=parquet)<br>MAX_FILE_SIZE = 32000000<br>HEADER=true;<br><br>DELETE FROM EXAMPLE_DATA WHERE EVT_DATE &lt;= DATEADD(year, -1, current_date());</pre><p>After checking S3, this all worked and now I have the hot data in Snowflake and the cold data in S3. I really want one way to query the whole dataset so I’ll create an external table and a view to use for both.</p><pre>CREATE OR REPLACE EXTERNAL TABLE EXT_EXAMPLE_DATA(<br>ID varchar as (value:ID::varchar),<br>NUM_1 int AS (value:NUM_1::int),<br>NUM_2 int AS (value:NUM_1::int),<br>STR_1 varchar as (value:STR_1::varchar),<br>EVT_TIME time AS TO_TIME(value:EVT_TIME::time),<br>EVT_DATE date AS TO_DATE(SPLIT_PART(SPLIT_PART(metadata$filename, &#39;/&#39;, 2),&#39;=&#39;,2))<br>)<br>PARTITION BY (EVT_DATE) location=@LIFECYCLE_EXAMPLE_STAGE/<br>FILE_FORMAT = (type=parquet);<br><br>CREATE OR REPLACE VIEW ALL_EXAMPLE_DATA AS<br>SELECT ID, NUM_1, NUM_2, STR_1, EVT_TIME, EVT_DATE FROM EXAMPLE_DATA<br>UNION<br>SELECT ID, NUM_1, NUM_2, STR_1, EVT_TIME, EVT_DATE FROM EXT_EXAMPLE_DATA;</pre><p>After querying the view all data is now visible across both storage backends.</p><pre>SELECT COUNT(*) from ALL_EXAMPLE_DATA;</pre><p>It is also efficiently pruning when dates are filtered on the view!</p><pre>SELECT COUNT(*) from ALL_EXAMPLE_DATA WHERE EVT_DATE &gt; TO_DATE(&#39;2022–01–01&#39;);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/350/1*xo6NgPM9fXMa7PWWouC5rg.png" /><figcaption>Only 82 or 19,076 partitions scanned</figcaption></figure><p>To automate this, I will create a procedure and task to run the unloads and deletes on a schedule. The data unloaded will also filter out any data that already exists in the destination to prevent duplicates on retries.</p><pre>CREATE OR REPLACE PROCEDURE RUN_LIFECYCLE()<br>RETURNS VARCHAR<br>AS<br>BEGIN<br>  COPY INTO @BKC_LIFECYCLE_EXAMPLE_STAGE<br>  FROM (SELECT n.ID, n.NUM_1, n.NUM_2, n.STR_1, n.EVT_TIME, n.EVT_DATE from EXAMPLE_DATA as n WHERE n.EVT_DATE &lt;= DATEADD(year, -1, current_date()) AND NOT EXISTS(SELECT 1 FROM EXT_EXAMPLE_DATA as o WHERE n.ID = o.ID AND n.EVT_DATE = o.EVT_DATE))<br>  PARTITION BY (&#39;date=&#39; || to_varchar(EVT_DATE, &#39;YYYY-MM-DD&#39;))<br>  FILE_FORMAT = (TYPE=parquet)<br>  MAX_FILE_SIZE = 32000000<br>  HEADER=true;<br>  DELETE FROM EXAMPLE_DATA WHERE EVT_DATE &lt;= DATEADD(year, -1, current_date());<br>  ALTER EXTERNAL TABLE EXT_EXAMPLE_DATA REFRESH;<br>  RETURN &#39;DONE&#39;;<br>END;<br><br>CREATE TASK LIFECYCLE_TASK<br>SCHEDULE = &#39;USING CRON 0 0 * * * UTC&#39;<br>USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = &#39;XSMALL&#39;<br>AS<br>CALL RUN_LIFECYCLE();</pre><p>This will migrate the data (over a year old) from Snowflake managed storage to the external table every day. It would be more efficient to have the <a href="https://docs.snowflake.com/en/user-guide/tables-external-s3#option-1-creating-a-new-s3-event-notification">refresh</a> triggered from S3 events but that is an exercise I’ll skip for this example.</p><p>Now that this is all working, I still need to create the lifecycle rule in S3. In the AWS management console I go to the S3 Service and the bucket I created earlier. Under Management tab I add a new Lifecycle rule. I set the objects to move directly to Intelligent Tiering which will let AWS manage when to move the files into Infrequent-Access. This tier automatically moves objects to Infrequent Access <a href="https://aws.amazon.com/s3/pricing/">($0.0125/gb</a>) and Archive Instant Access (<a href="https://aws.amazon.com/s3/pricing/">$0.004/gb</a>) depending on access patterns. After 1 year, I move the objects to Glacier Instant Retrieval which removes the S3 Intelligent Tiering automation costs (<a href="https://aws.amazon.com/s3/pricing/">$0.0025 per 1,000 objects</a>) for those objects. At this point, the data in Snowflake, in Intelligent Tiering, AND <a href="https://aws.amazon.com/s3/storage-classes/glacier/">Glacier Instant Retrieval</a> are ALL queryable via the view in Snowflake. These tiers all allow Snowflake to retrieve the objects in a similar way, just with slower performance). Data could also be moved into Glacier Deep Archive but it would not be queryable unless a job was created and fulfilled to retrieve from storage. I also add an object expiration a year later which will automatically delete data after 3 years elapses.</p><p>Here is the lifecycle:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/399/1*35egD-cJMjqdbNb8isHhSg.png" /></figure><p>This is just an example of how you can use the tools provided by Snowflake and AWS to provide automated storage lifecycles on your data. If you are in <a href="https://learn.microsoft.com/en-us/azure/storage/blobs/access-tiers-overview#blob-lifecycle-management">Azure</a> or <a href="https://cloud.google.com/storage/docs/lifecycle">Google</a>, you can also use their hot and cool tiers in the same way.</p><p>Hopefully, this example makes it easier to automate your data governance and more efficiently store your data. It is important to remember that using external tables and these slower storage options will have performance impact so should be used in cases where that data is queried infrequently. I am really excited about Snowflake’s work on Iceberg and will create another post in the future leveraging Iceberg instead of external tables when that is available.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f2bdc2b92713" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/storage-lifecycles-in-snowflake-f2bdc2b92713">Storage Lifecycles in Snowflake</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Key Rotation For Connected Applications]]></title>
            <link>https://medium.com/snowflake/key-rotation-for-connected-applications-45828748d1fa?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/45828748d1fa</guid>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Tue, 21 Feb 2023 16:29:20 GMT</pubDate>
            <atom:updated>2023-02-21T17:19:09.525Z</atom:updated>
            <content:encoded><![CDATA[<p><a href="https://www.snowflake.com/blog/powered-by-snowflake-building-a-connected-application-for-growth-and-scale/">Connected Application</a> builders require consumers that want to use their application to provide access to their account. I often get questions about how to manage this credential. This article will go through steps I would take to secure and use a credential given to me as a builder of a connected application.</p><p>In order to get credentials secured for a consumer’s account, I would document and create a script the consumer can easily audit and run to get started. It would also be really nice if I could plan for credential rotation to prevent manual processes later. In this article I will provide a basic example but every application will want to customize this based on their needs and use-case.</p><p>Here is a workflow diagram of the process:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*tKmB3yqTID5wFy9s" /></figure><p>I will be using key pair authentication for the connected accounts. I would not use usernames and passwords because there is no easy way to automate rotation (each user only has 1 password). Every user in Snowflake has 2 keypairs that can be active at a time which is perfect for rotations without downtime.</p><p>First, the builder will create a keypair for authentication. This could be created per customer or be a shared key, that is up to the builder to choose.</p><p># generate a public &amp; private key</p><pre>#!/bin/bash<br>openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt<br>openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub<br>PUBLIC_KEY=`cat rsa_key.pub | head -n 8 | tail -n 7 | tr -d &#39;\n&#39;`</pre><p>The following SQL script could be sent to be run by the application customer which will create a role, user, warehouse, and database I can use. You should replace the PUBLIC_KEY in the script with the key generated above before sending it to the application customer to run.</p><pre>/* EXAMPLE SETUP SCRIPT FOR CONSUMER TO RUN AS ACOUNTADMIN */<br>CREATE OR REPLACE ROLE MY_CONNECTED_APP_ROLE;<br>CREATE OR REPLACE WAREHOUSE MY_CONNECTED_APP_WH;<br>CREATE OR REPLACE DATABASE MY_CONNECTED_APP_DB;<br><br>CREATE USER MY_CONNECTED_APP_USER must_change_password=false<br>default_role=MY_CONNECTED_APP_ROLE rsa_public_key=&#39;PUBLIC_KEY&#39;<br>default_namespace=MY_CONNECTED_APP_DB.PUBLIC<br>default_warehouse=MY_CONNECTED_APP_WH;<br><br>GRANT ROLE MY_CONNECTED_APP_ROLE TO USER MY_CONNECTED_APP_USER;<br>GRANT ROLE MY_CONNECTED_APP_ROLE TO ROLE ACCOUNTADMIN;<br>GRANT OWNERSHIP ON WAREHOUSE MY_CONNECTED_APP_WH TO ROLE MY_CONNECTED_APP_ROLE;<br>GRANT ALL ON WAREHOUSE MY_CONNECTED_APP_WH TO ROLE ACCOUNTADMIN;<br>GRANT OWNERSHIP ON DATABASE MY_CONNECTED_APP_DB TO ROLE MY_CONNECTED_APP_ROLE;<br><br>USE ROLE MY_CONNECTED_APP_ROLE;<br>CREATE OR REPLACE SCHEMA MY_CONNECTED_APP_DB.APP;</pre><p>I would also like to be able to rotate my own credential from my infrastructure to persist the new private and public keypair in my secrets store. In order to do so, I will also create a stored procedure to allow the user to rotate their credential.</p><pre>/* EXAMPLE SETUP SCRIPT FOR CONSUMER TO RUN AS ACOUNTADMIN */<br>CREATE OR REPLACE SCHEMA MY_CONNECTED_APP_DB.ADMIN;<br>USE ROLE ACCOUNTADMIN;<br><br>CREATE OR REPLACE PROCEDURE MY_CONNECTED_APP_DB.ADMIN.SET_CREDENTIAL(key1 STRING, key2 STRING)<br>returns varchar not null<br>language sql<br>execute as owner<br>as<br>BEGIN<br>let k1 TEXT := :KEY1;<br>let k2 TEXT := :KEY2;<br>let sql TEXT := &#39;ALTER USER MY_CONNECTED_APP_USER set rsa_public_key=? rsa_public_key_2=?&#39;;<br>execute immediate :sql using (k1, k2);<br>return &#39;ok&#39;;<br>END;<br><br>GRANT USAGE ON SCHEMA MY_CONNECTED_APP_DB.ADMIN TO ROLE MY_CONNECTED_APP_ROLE;<br>GRANT USAGE ON PROCEDURE MY_CONNECTED_APP_DB.ADMIN.SET_CREDENTIAL(STRING,STRING) TO ROLE MY_CONNECTED_APP_ROLE;</pre><p>Now anytime the builder wants to rotate the credential they can create the new public and private key and call the set_credentials with the current public key and the new public key. After that is successful the secrets store can be updated to store the new keypair. The application can then use the new keypair for authentication. After another rotation the previous key is no longer valid and couldn’t be used.</p><p>You can see the procedure code is running as owner, this is necessary because users cannot alter their own public keys. This procedure hard coded the connected application user so it is only solving for this one need and not allowing more than is required. This also fits well with what is <a href="https://docs.snowflake.com/en/sql-reference/stored-procedures-rights#alter-user">allowed in Snowflake</a> with owner’s rights.</p><p>I only included the Snowflake pieces in this post, mostly because there are many different ways to store secrets and run processes depending on your organization’s technology and cloud choices. It is a <strong>Best Practice </strong>to store these keys in something backed by an HSM like AWS Parameter Store EncryptedString and for those to be rotated regularly. To do so, I would create and schedule a task that would:</p><ol><li>Create a new keypair</li><li>Connect to the consumer’s account using current keypair and call SET_CREDENTIALS with both public keys which should be valid</li><li>Update the keypair in the secrets store so the application will start using the new credential</li><li>Have the application read the keypair from the secrets store</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=45828748d1fa" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/key-rotation-for-connected-applications-45828748d1fa">Key Rotation For Connected Applications</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Tips for Clustering in Snowflake]]></title>
            <link>https://medium.com/snowflake/tips-for-clustering-in-snowflake-d83aa32f4a6f?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/d83aa32f4a6f</guid>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Mon, 05 Dec 2022 17:33:01 GMT</pubDate>
            <atom:updated>2022-12-05T18:10:49.208Z</atom:updated>
            <content:encoded><![CDATA[<p>One of the most powerful tools for Snowflake users to gain performance and efficiency is clustering. Data that is well clustered can be queried faster and more affordably due to partition pruning. Partition pruning allows Snowflake to skip data that does not pertain to the query based on statistics of the micro partitions.</p><p>Some assumptions made by new users are incorrect and provide no performance benefit while wasting credits. This quick write up will hopefully help you understand what clustering is on Snowflake, Why it is important, How to pick cluster keys, and what not to do.</p><h3>What is Clustering on Snowflake?</h3><p>Clustering keys on Snowflake are metadata on a table which is used by the <a href="https://docs.snowflake.com/en/user-guide/tables-auto-reclustering.html">Automatic Clustering</a> service. The clustering keys are configured during create/alter of the table. Clustering in Snowflake relates to how rows are co-located with other similar rows in a micro partition. Snowflake does not shard micro partitions to only store one set of cluster key values, but rather a micro partition will often have a set of contiguous values.</p><p>When data is inserted into an existing table, the micro partitions are split based on the volume of data and in the order it was received regardless of the clustering configuration on the table. If a new table is created via a CTAS and has cluster keys, it will automatically be clustered.</p><p>When clustering is defined, the <a href="https://docs.snowflake.com/en/user-guide/tables-auto-reclustering.html">Automatic Clustering</a> service will, in the background, use that information to rewrite micro partitions to group rows with similar values for the clustering columns in the same micro partition.</p><p>To see how this works, you can insert some data and I’ll show you what is happening. First, create the table by running the following commands which will generate enough random data to create multiple micro partitions in a table with clustering on.</p><pre>CREATE DATABASE CLUSTERING_TIPS;<br><br>CREATE SCHEMA CLUSTERING_TIPS;<br><br>CREATE OR REPLACE TABLE CLUSTERING_DEMO cluster by (TENANT_ID)(<br>ID NUMBER(10,0),<br>TENANT_ID NUMBER(4,0),<br>KEY VARCHAR(4),<br>VALUE VARCHAR(255)<br>);<br><br>INSERT OVERWRITE INTO CLUSTERING_DEMO<br>SELECT<br>seq4() AS ID,<br>uniform(1, 1000, random()) AS TENANT_ID,<br>randstr(4, random()) AS KEY,<br>randstr(255, random()) AS VALUE<br>FROM table(generator(rowcount =&gt; 250000));</pre><p>We now have some data in the table. To prove data is inserted as it comes in (in this case completely random), we should now see that if we query all data for a single tenant it still does a full table scan.</p><p>Run this query and open the Query Profile Statistics.</p><pre>SELECT * FROM CLUSTERING_DEMO WHERE TENANT_ID = 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/364/0*KBRj-QkL_xYE-GGg" /></figure><p>As you can see, there are 3 micro partitions in the table and all 3 were scanned to return the result. If you don’t, make sure you do this quickly after the table is created or Automatic Clustering service will impact the results.</p><p>Now you can set a timer for several hours from now to come back and run the same query. Auto Clustering service only runs every several hours. After Automatic Clustering runs on this table you will see this query will only scan 1 partition of the 3.</p><p>If you’re impatient like me, you may want to change how the table is created to sort the data based on that tenant identifier which will then land it in a way to allow for more efficient queries.</p><p>This is being done for demonstration purposes, this is something to be done with care for large datasets.</p><pre>CREATE OR REPLACE TABLE CLUSTERING_DEMO_SORTED cluster by (TENANT_ID)(<br>ID NUMBER(10,0),<br>TENANT_ID NUMBER(4,0),<br>KEY VARCHAR(4),<br>VALUE VARCHAR(255)<br>);<br><br>INSERT OVERWRITE INTO CLUSTERING_DEMO_SORTED<br>SELECT<br>seq4() AS ID,<br>uniform(1, 1000, random()) AS TENANT_ID,<br>randstr(4, random()) AS KEY,<br>randstr(255, random()) AS VALUE<br>FROM table(generator(rowcount =&gt; 250000))<br>ORDER BY TENANT_ID;</pre><p>Now run the same query and look at the query profile statistics to see how it now eliminated ⅔ of the data.</p><pre>SELECT * FROM CLUSTERING_DEMO_SORTED WHERE TENANT_ID = 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/371/0*ZAap_KahDuZseV2k" /></figure><p>The reason this is now able to eliminate most of the data is because the data for the tenant only existed in a small number of micro partitions (1 in this case). Snowflake knows this because it calculated and stored metadata for every micro partition and that data is queried during compile time to eliminate as much data as possible. The metadata used in this query would have been the MIN and MAX values of the TENANT_ID on each micro partition.</p><p>When micro partitions have random data across a large span of values in a field there is no way to eliminate those during query execution which means the query will need to scan more micro partitions, taking more compute and requiring larger warehouses to complete in a timely manner.</p><h3>Why is Clustering Important?</h3><p>Many Snowflake workloads have some set of queries which run frequently on their clusters. These often have common shapes and often filter and group by the same fields. For example, data that is often queried by a range of dates, can be clustered by the date and queries will read a smaller set of micro partitions. In this case, by clustering the data well, queries can be pruned which will allow them to run on smaller warehouses, be faster to execute, and save credit consumption!</p><p>Yes, we can spin up thousands of cores and query a massive dataset that’s sitting in blob store fairly quickly which is impressive. That’s just throwing money at the query. I don’t mind for research and occasional ad-hoc queries because it’s not worth the effort and time to optimize, but it shouldn’t be done for queries that are expected, frequent, AND can be optimized.</p><h3>How to Pick Cluster Keys?</h3><p>Cluster keys should be fields that are often filtered on. In the case above, I chose tenant_id because I see a lot of multi-tenant tables and often those could really gain an advantage with clustering. Time Series data is also often clustered by a record DATE. You’ll need to know the shape of the data and the queries pretty well to determine the best cluster keys.</p><p>If I’m looking into how to cluster a table and don’t know enough about the shape or common queries, I will often look at recent query history filtered down to those queries landing on the table. If there is a very common filter, that’s a great place to start. If there is always one filter, and occasionally another, that pair is what you want to use.</p><p>At this point everyone asks… What do you do if you have 2 different fields that are filtered on separately but each separately, often. You have to pick 1 or the other. The keys are hierarchical so consider the relationship of the data for similar values of the first field to determine if this is productive. This is also related to how we store in blob store. To have another query pattern that can also prune well, it will have to be persisted differently. If you need to solve this for multiple query patterns (this is pretty rare), you’ll want to look at materialization so another copy of the data can be clustered in a way to support all query patterns. This should ONLY be done when you have a huge number of queries that have these predicates. If it’s only a small number, you’ll end up spending more time engineering and compute costs maintaining the materialization than you would have throwing bigger warehouses at the queries.</p><p>When picking cluster keys the cardinality of the key(s) values should be considered. In cases where the cardinality is higher than the micro partition count there will be inefficiencies in clustering. To avoid this inefficiency, it is common to truncate dates, substring varchars, divide ints, and in some cases persist a new value and filtering on that to have the good performance and reasonable costs of clustering.</p><h3>What NOT to do?</h3><p>There are a few things I see frequently in clustering choices that don’t have any performance gains and are a waste of consumption. I’ll go through the ones I see frequently that you should avoid.</p><h3>DO NOT: Cluster by a timestamp when most predicates are by DAY.</h3><p>The reasoning is that the Automatic Clustering service will spend a lot of time organizing records that happened the same minute/second/hour together. The choice of the TIMESTAMP field means work will be done that may never be needed in predicates.</p><p>In this case you should cluster by the DATE_TRUNC by DAY as long as most filters only filter by day. Similarly to DATE_TRUNC to HOUR if most filters go to hours. This will save unnecessary reclustering costs. All existing queries will prune as the MIN and MAX on the columns will work on the existing predicates without any modifications. Et al: There is no need to change the predicate to DATE_TRUNC.</p><h3>DO NOT: Cluster on a VARCHAR field that has low cardinality prefix</h3><p>Snowflake only clusters on the first 6 characters. Due to this, it often does not behave how customers expect when clustering on varchar fields. I’ve seen this several times when dates were strings starting with YYYY, or a uuid generator had a common prefix. The reason this is a bad idea is that there isn’t enough cardinality in those first few characters to allow for good pruning (micro partition elimination).</p><p>One solution to this challenge is to convert those fields to dates which will be more efficient to compare and where clustering will work directly. If that is too much work, you can leave the dates as strings, but then cluster on the value as a date Ex: Cluster on to_date(&lt;stringdate&gt;,’YYYY-MM-DD’). This works well because the MIN/MAX statistics stored for the micro partitions will work as long as they start with something like YYYY-MM-DD which is alphabetically sorted the same as a date field. In this case the predicates are all comparing strings, not date fields.</p><p>For cases where the beginning of the string has low cardinality, but characters later have high cardinality, cluster by a substring containing the higher cardinality. In this case the data with similar substrings will be put together with Automatic Clustering and the MIN &amp; MAX values stored for each micro partition will be useful for pruning.</p><h3>DO NOT: Try to compensate for starting character cardinality with a HASH function</h3><p>Often Snowflake users will notice the front edge cardinality not allowing for good clustering and will jump to a use hash function. We only store statistics on stored fields, so even if this is done AND the predicates are modified to have a match on the HASH we still can’t eliminate any micro partitions. The only way this will work is if this HASH value is also persisted so we can keep statistics on it as well as that new field being added where needed to the predicates for elimination.</p><p>A better way to reduce cardinality when the starting characters are similar is the cluster by a substring of the field which was discussed in the previous case.</p><p>DO NOT: Try to reduce cardinality with MODULO functions in cluster definition without persisting the MODULO result and adding it in predicates</p><p>This can help in some occasions where there is too much cardinality in the field(s) chosen to cluster on, but ONLY works if that field is persisted because we need statistics to do pruning. Queries also have to be modified to add the additional predicate on that new persisted field.</p><p>In cases where the field value is an integer/numeric it is more useful to divide the field by a constant and cluster by that value as it will reduce the cardinality of the values while still allowing MIN &amp; MAX values to be useful for pruning.</p><h3>DO NOT: Change clustering in production on a table w/o a manual rewrite</h3><p>When you change the clustering configuration, the background service will take a lot of time and credits while trying not to impact other queries. It will likely rewrite many micro partitions multiple times to get to the best outcome.</p><p>You can do an overwrite in production to do a one time recluster.</p><pre>INSERT OVERWRITE INTO YOUR_TABLE <br>SELECT * FROM YOUR_TABLE ORDER BY &lt;YOUR NEW CLUSTER CHOICE&gt;;</pre><p>You can do that on a very large warehouse and get the entire table overwritten immediately. Any table up to a tb can easily be rewritten this way. The Automatic Clustering service will then <em>not</em> have to rewrite all the micro partitions in the table in the background to meet the new clustering configuration.</p><p>This can be an efficient way to recluster a table, but care should be taken to size the warehouse properly for the dataset size to prevent spilling. For very large tables (many tbs), it may be possible to rewrite in pieces to run on warehouses without spilling.</p><p>Auto Clustering service does work on any table if these alternatives are too complex but will use more credits to complete.</p><h3>Conclusion</h3><p>Snowflake clustering is an extremely important tool to have in your toolbox. Run some tests on smaller datasets/queries to understand how it works (hopefully my example gave you some ideas). If I need queries to be faster AND more efficient this is the first thing I look at when I see slow TableScans in Query Profile to see if I can leverage it to my advantage.</p><p>Don’t forget to go back and run the query on the table that was set to Automatically Cluster on the tenant. You’ll see after that data is rewritten that the query will only need to scan 1 micro partition.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d83aa32f4a6f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/tips-for-clustering-in-snowflake-d83aa32f4a6f">Tips for Clustering in Snowflake</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Developers Guide to Snowflake’s SQL API]]></title>
            <link>https://medium.com/@bradculberson/developers-guide-to-snowflakes-sql-api-ded20fe77928?source=rss-34379116c51a------2</link>
            <guid isPermaLink="false">https://medium.com/p/ded20fe77928</guid>
            <category><![CDATA[api]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[c-sharp-programming]]></category>
            <dc:creator><![CDATA[Brad Culberson]]></dc:creator>
            <pubDate>Thu, 04 Aug 2022 20:45:19 GMT</pubDate>
            <atom:updated>2025-09-02T15:37:30.333Z</atom:updated>
            <content:encoded><![CDATA[<p>There are a lot of <a href="https://docs.snowflake.com/en/user-guide/conns-drivers.html">options</a> for how to connect to Snowflake as an application developer. One of the newest options is the <a href="https://docs.snowflake.com/en/developer-guide/sql-api/index.html">SQL API</a>. Most developers picking the SQL API are looking for a lighter weight option that doesn’t require a lot of libraries and/or are using languages not supported by our other connectors.</p><h3>What is it?</h3><p>Snowflake’s SQL API is a set of endpoints that allow you to issue SQL commands and retrieve results over TLS (Transport Level Security) with standard HTTP verbs, requests, and responses. All results are in JSON. With these common technologies in use, it is simple to build a client from standard libraries available in all modern programming languages..</p><p>While this may look like a <a href="https://restfulapi.net/">REST</a> API for Snowflake, it does not exactly fit into the expectations of traditional REST APIs. Typical REST APIs have URLs as resources, which are acted upon with HTTP verbs (e.g., GET, DELETE, PATCH, etc), and are navigated with <a href="https://stateless.group/hal_specification.html">HAL</a>. The Snowflake SQL API is more of a mechanism to send SQL statements to Snowflake and retrieve results over HTTP, somewhat like an RPC service. That is not to say that it is not a powerful tool in the toolbox; it is an amazing new capability for Snowflake.</p><p>It unlocks the power of Snowflake to almost any developer.</p><h3>What is the workflow?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aR_veh7oNwNU7d4DfEEPZQ.png" /></figure><p>The first endpoint that you need to use is <a href="https://docs.snowflake.com/en/developer-guide/sql-api/reference.html">/api/v2/statements/</a>. To execute a new SQL statement, a body is created including the SQL statement and sent to the endpoint using the HTTP verb POST. You have the option to include a unique request id. The request id is used to deduplicate retried requests to the API. I suggest you do provide that id, I use a UUID generator to create it and use that before my call to the API, which does retry on failures. You can also specify if the statement should execute asynchronously. If you don’t specify that, a synchronous response is still not guaranteed; it depends on the execution time. To write the least amount of code, you can set it to execute asynchronously.</p><p>After calling the statements endpoint, you will get a 202 status code, which means Snowflake is processing the request. You will get a statementHandle and statementStatusUrl which can be used to poll for results. If you get a 429, your client is being throttled and you can retry.</p><p>To fetch the results, you poll /api/v2/statements/{statementHandle} with a GET verb. It is useful to pause briefly between polling requests; I use a sleep of 100 ms between checks. If the polling request returns a 202 status code, continue the loop. If you get a 429, your client is being throttled and you can retry. The polling request will return a 200 status code when the query is complete. The body of this response with the 200 status code will provide the first page of data for the query response (if any) and the information needed to grab more pages if needed. You can fetch the remaining pages sequentially or in parallel depending on your application needs.</p><h3>How do I authenticate the requests?</h3><p>I think the easiest way to authenticate is to use <a href="https://docs.snowflake.com/en/user-guide/key-pair-auth.html">keypair</a> authentication. We have code samples for creating a <a href="https://jwt.io/introduction">JWT</a> from the key in <a href="https://docs.snowflake.com/en/developer-guide/sql-api/authenticating.html#python-example">python</a>, <a href="https://docs.snowflake.com/en/developer-guide/sql-api/authenticating.html#java-example">java</a>, and <a href="https://docs.snowflake.com/en/developer-guide/sql-api/authenticating.html#node-js-example">node.js</a>. The JWT key is added in the Authentication header as a Bearer token which allows the above endpoints to be executed as a Snowflake user.</p><p>If you’re using .NET, here is a class I wrote that can generate a JWT token. Forgive me if the code isn’t idiomatic, I haven’t written production C# since 2004. JWT is a standard so there are common libraries to create these signatures in most languages. Here I used <a href="https://www.nuget.org/packages/JWT/">JWT</a> and <a href="https://www.nuget.org/packages/BouncyCastle">BouncyCastle</a>.</p><p>You can download the code <a href="https://pastebin.com/UQ2BH5MH">here</a>.</p><pre>using System.Text;<br>using System.Security.Cryptography;<br>using Org.BouncyCastle.OpenSsl;<br>using Org.BouncyCastle.Security;<br>using Org.BouncyCastle.Crypto.Parameters;<br>using Org.BouncyCastle.X509;<br>using JWT;<br>using JWT.Algorithms;<br>using JWT.Serializers;</pre><pre>namespace Snowflake.SqlApi<br>{<br>    public class KeyPairAuthentication<br>    {<br>        private String PubKeyHash;<br>        private RSAParameters RsaParams;<br>        private IJwtEncoder Encoder;<br>        private String Account;<br>        private String User;</pre><pre>public KeyPairAuthentication(String account, String user, String publicKey, String privateKey, String privateKeyPassword)<br>        {<br>            PubKeyHash = GetPublicKeyHash(publicKey);<br>            RsaParams = GetRsaPrivateCrtKeyParameters(privateKey, privateKeyPassword);<br>            Encoder = GetRS256JWTEncoder(RsaParams);<br>            Account = account.ToUpper();<br>            User = user.ToUpper();<br>        }</pre><pre>public KeyPairAuthentication(String account, String user, StreamReader publicKey, StreamReader privateKey, String privateKeyPassword)<br>        {<br>            PubKeyHash = GetPublicKeyHash(publicKey);<br>            RsaParams = GetRsaPrivateCrtKeyParameters(privateKey, privateKeyPassword);<br>            Encoder = GetRS256JWTEncoder(RsaParams);<br>            Account = account.ToUpper();<br>            User = user.ToUpper();<br>        }</pre><pre>public String GenerateJwt()<br>        {<br>            return GenerateJwt(3600);<br>        }</pre><pre>public String GenerateJwt(int expiration)<br>        {<br>            var iat = Convert.ToInt32((DateTime.UtcNow - DateTime.UnixEpoch).TotalSeconds);<br>            var exp = iat + expiration;<br>            var sub = Account + &quot;.&quot; + User;<br>            var iss = sub + &quot;.SHA256:&quot; + PubKeyHash;<br>            var payload = new Dictionary&lt;string, object&gt;<br>            {<br>                { &quot;iss&quot;, iss},<br>                { &quot;sub&quot;, sub},<br>                { &quot;iat&quot;, iat},<br>                { &quot;exp&quot;, exp},<br>            };</pre><pre>return Encoder.Encode(payload, new byte[0]);<br>        }</pre><pre>private static IJwtEncoder GetRS256JWTEncoder(RSAParameters rsaParams)<br>        {<br>            var csp = new RSACryptoServiceProvider();<br>            csp.ImportParameters(rsaParams);<br>            var algorithm = new RS256Algorithm(csp, csp);<br>            var serializer = new JsonNetSerializer();<br>            var urlEncoder = new JwtBase64UrlEncoder();<br>            return new JwtEncoder(algorithm, serializer, urlEncoder);<br>        }</pre><pre>private static RSAParameters GetRsaPrivateCrtKeyParameters(string rsaPrivateKey, string password)<br>        {<br>            var byteArray = Encoding.ASCII.GetBytes(rsaPrivateKey);<br>            using (var ms = new MemoryStream(byteArray))<br>            {<br>                using (var sr = new StreamReader(ms))<br>                {<br>                    return GetRsaPrivateCrtKeyParameters(sr, password);<br>                }<br>            }<br>        }</pre><pre>private static RSAParameters GetRsaPrivateCrtKeyParameters(StreamReader sr, string password)<br>        {<br>            var pemReader = new PemReader(sr, new PasswordFinder(password));<br>            var privateKey = pemReader.ReadObject() as RsaPrivateCrtKeyParameters;<br>            return DotNetUtilities.ToRSAParameters(privateKey);<br>        }</pre><pre>private static String GetPublicKeyHash(string rsaPublicKey)<br>        {<br>            var byteArray = Encoding.ASCII.GetBytes(rsaPublicKey);<br>            using (var ms = new MemoryStream(byteArray))<br>            {<br>                using (var sr = new StreamReader(ms))<br>                {<br>                    return GetPublicKeyHash(sr);<br>                }<br>            }<br>        }</pre><pre>private static string GetPublicKeyHash(StreamReader sr)<br>        {<br>            var pemReader = new PemReader(sr);<br>            var publicKey = pemReader.ReadObject() as RsaKeyParameters;<br>            var publicKeyDer = SubjectPublicKeyInfoFactory.CreateSubjectPublicKeyInfo(publicKey).GetDerEncoded();<br>            var publicKeyDerBase64 = Convert.ToBase64String(publicKeyDer);<br>            var sha256Hash = SHA256.Create();<br>            var hash = sha256Hash.ComputeHash(publicKeyDer);<br>            return Convert.ToBase64String(hash);<br>        }</pre><pre>private class PasswordFinder : IPasswordFinder<br>        {<br>            private string password;</pre><pre>public PasswordFinder(string password)<br>            {<br>                this.password = password;<br>            }</pre><pre>public char[] GetPassword()<br>            {<br>                return password.ToCharArray();<br>            }<br>        }<br>    }<br>}</pre><p>The JWT tokens have a maximum expiration of 1 hour. Code to manage the expiration and create the tokens is pretty complicated. I prefer a pattern that creates a new JWT token for every call to the API. This is very fast, in the code above, you can call GenerateJWT when generating each HTTP call (The instance of KeyPairAuthentication is something I would put in global state. With this pattern, the public and private key is loaded in memory and very little CPU is needed to generate the token.)</p><h3>Conclusion</h3><p>If you are looking for a lightweight option for talking to Snowflake, the SQL API is a great solution. You can use libraries you are familiar with: HTTP, JSON, and JWT to execute SQL and return results to your code.</p><p>I can’t wait to see what you build!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ded20fe77928" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>