Setting up Snowflake account and key pair

To send data to Snowflake you first need to generate a private/public key pair that will be used for authentication. Generate the keys:

# Create Private key - keep this safe, do not share! openssl genrsa -out snowflake_key.pem 2048 # Generate public key from private key. You can share your public key. openssl rsa -in snowflake_key.pem -pubout -out snowflake_key.pub

You should now have two files:

$ ls -l snowflake_key* -rw-r--r-- 1 rmoff staff 1679 21 Nov 09:28 snowflake_key.pem -rw-r--r-- 1 rmoff staff 451 21 Nov 09:28 snowflake_key.pub

Now you need to get your public key:

$ cat snowflake_key.pub -----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAya/BRlyhsfdlJQnPqoRn lJfxKxujoyionNBPIDFpVpGZ9C1ZE7Q1kGIrEoZfq1t2p6lT8cX6gIZkMDF10I/8 yqHGiCdSEQBuMYXwWpnl3C1sttFHNfxbsjiKSZDlMTbEmzwU5s5LpMt8YvFWp8Iu 3ilHK9Vwy0wbsMDCjDcrC6xCS6qp1n4oso+V24aaxKd/mUtpPy9toAx2NC5GMoDb tehlbTyPkk/9qFl7GUsf46HbQMEGoGkRrY9VFm+3Z8wCwsFNpURIvLEBcrTFdnmn IgDBa96+dKgaN8qV6RW3ZMheQOJH1tP3M0qXsLNbR00E7yAlCYjNQD3hXjGKL3Oc 5wIDAQAB -----END PUBLIC KEY-----

But minus the header and footer and joined over a single line. You can do this manually, or automagically:

$ grep -v "BEGIN PUBLIC" snowflake_key.pub | grep -v "END PUBLIC" |tr -d

MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAya/BRlyhsfdlJQnPqoRnlJfxKxujoyionNBPIDFpVpGZ9C1ZE7Q1kGIrEoZfq1t2p6lT8cX6gIZkMDF10I/8yqHGiCdSEQBuMYXwWpnl3C1sttFHNfxbsjiKSZDlMTbEmzwU5s5LpMt8YvFWp8Iu3ilHK9Vwy0wbsMDCjDcrC6xCS6qp1n4oso+V24aaxKd/mUtpPy9toAx2NC5GMoDbtehlbTyPkk/9qFl7GUsf46HbQMEGoGkRrY9VFm+3Z8wCwsFNpURIvLEBcrTFdnmnIgDBa96+dKgaN8qV6RW3ZMheQOJH1tP3M0qXsLNbR00E7yAlCYjNQD3hXjGKL3Oc5wIDAQAB

Now head to Snowflake, where we need to create a user for loading the data. First up, switch to the SECURITYADMIN role.

Note Make sure you do this in the Context section of the worksheet, not the top-right dropdown (otherwise you’ll get SQL access control error: Insufficient privileges to operate on account 'xyz' ).

Now create the user, here called kafka . Because we’re in demo-land we’re also granting Kafka the keys to the kingdom ( SYSADMIN ), just to make everything nice 'n easy.

CREATE USER kafka RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAya/BRlyhsfdlJQnPqoRnlJfxKxujoyionNBPIDFpVpGZ9C1ZE7Q1kGIrEoZfq1t2p6lT8cX6gIZkMDF10I/8yqHGiCdSEQBuMYXwWpnl3C1sttFHNfxbsjiKSZDlMTbEmzwU5s5LpMt8YvFWp8Iu3ilHK9Vwy0wbsMDCjDcrC6xCS6qp1n4oso+V24aaxKd/mUtpPy9toAx2NC5GMoDbtehlbTyPkk/9qFl7GUsf46HbQMEGoGkRrY9VFm+3Z8wCwsFNpURIvLEBcrTFdnmnIgDBa96+dKgaN8qV6RW3ZMheQOJH1tP3M0qXsLNbR00E7yAlCYjNQD3hXjGKL3Oc5wIDAQAB'; GRANT ROLE SYSADMIN TO USER kafka;

Now we need to extract the private key for the key pair, which is in the .pem file that we created, minus the header and footer and on a single line:

Note Your private key is private - don’t share it with anyone who shouldn’t have access to the account, and definitely don’t post it on the internet on a blog post!

As before you can extract the key automagically with:

grep -v "BEGIN RSA PRIVATE" snowflake_key.pem | grep -v "END RSA PRIVATE" |tr -d



Put this value, along with the URL of your Snowflake environment and the user that we created ( kafka ) in the .env file