Create and configure a Snowflake sink connector for Apache Kafka®
The Apache Kafka Connect® Snowflake sink connector moves data from Aiven for Apache Kafka® topics to a Snowflake database. It requires configuration in both Snowflake and Aiven for Apache Kafka.
Prerequisites
-
An Aiven for Apache Kafka service with Apache Kafka Connect enabled, or a dedicated Kafka Connect cluster
-
Access to the target Snowflake account with privileges to create users, roles, and schemas
-
OpenSSL installed locally to generate key pairs
-
Collect the following connection details:
-
SNOWFLAKE_URL
: In the formatACCOUNT_LOCATOR.REGION_ID.snowflakecomputing.com
tipTo retrieve your account locator and region ID, run the following in the Snowflake worksheet:
SELECT CURRENT_ACCOUNT(), CURRENT_REGION();
-
SNOWFLAKE_USERNAME
: The user created for the connector -
SNOWFLAKE_PRIVATE_KEY
: The private key for the user -
SNOWFLAKE_PRIVATE_KEY_PASSPHRASE
: The key passphrase -
SNOWFLAKE_DATABASE
: The target database -
SNOWFLAKE_SCHEMA
: The target schema -
TOPIC_LIST
: Comma-separated list of Kafka topics to sink
If using Avro format:
APACHE_KAFKA_HOST
SCHEMA_REGISTRY_PORT
SCHEMA_REGISTRY_USER
SCHEMA_REGISTRY_PASSWORD
-
For a full list of configuration options, see the Snowflake Kafka Connector documentation.
Configure Snowflake
Set up Snowflake to authenticate the connector using key pair authentication.
Generate a key pair
Use OpenSSL to generate a 2048-bit RSA key pair:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
rsa_key.p8
contains the private key, secured with a passphrase.rsa_key.pub
contains the public key.
Create a user
-
Open the Snowflake UI and go to the Worksheets tab.
-
Use a role with
SECURITYADMIN
orACCOUNTADMIN
privileges. -
Create a Snowflake user for the Aiven connector. Run the following SQL command:
CREATE USER aiven;
-
Copy the contents of
rsa_key.pub
, excluding the-----BEGIN
and-----END
lines. Remove any line breaks.noteWhen copying the public key, exclude the
-----BEGIN PUBLIC KEY-----
and-----END PUBLIC KEY-----
lines. Remove all line breaks so the key is on a single line. -
Set the public key for the user:
ALTER USER aiven SET RSA_PUBLIC_KEY='PASTE_PUBLIC_KEY_HERE';
Create a role and assign it to the user
-
Create a role for the connector:
CREATE ROLE aiven_snowflake_sink_connector_role;
-
Grant the role to the
aiven
user:GRANT ROLE aiven_snowflake_sink_connector_role TO USER aiven;
-
Set the role as the user's default:
ALTER USER aiven SET DEFAULT_ROLE = aiven_snowflake_sink_connector_role;
Grant privileges on the target database and schema
The connector writes data to tables in a specific schema within a Snowflake database. Grant the necessary privileges to the role you created.
-
In the Snowflake UI, open the Worksheets tab.
-
Use a role with
SECURITYADMIN
orACCOUNTADMIN
privileges. -
Replace
TESTDATABASE
andTESTSCHEMA
with your database and schema names, then run the following SQL commands:GRANT USAGE ON DATABASE TESTDATABASE TO ROLE aiven_snowflake_sink_connector_role;
GRANT USAGE ON SCHEMA TESTDATABASE.TESTSCHEMA TO ROLE aiven_snowflake_sink_connector_role;
GRANT CREATE TABLE ON SCHEMA TESTDATABASE.TESTSCHEMA TO ROLE aiven_snowflake_sink_connector_role;
GRANT CREATE STAGE ON SCHEMA TESTDATABASE.TESTSCHEMA TO ROLE aiven_snowflake_sink_connector_role;
GRANT CREATE PIPE ON SCHEMA TESTDATABASE.TESTSCHEMA TO ROLE aiven_snowflake_sink_connector_role;
These privileges allow the connector to access the database, write to the schema, and manage tables, stages, and pipes.