Snowflake Source
Provided by: "Apache Software Foundation"
Support Level for this Kamelet is: "Preview"
Runs an SQL query in a Snowflake database again and again, and sends each row it returns as a message, as a JSON object.
Use it to pick up the rows that another application adds to a table, such as new orders or events, and pass them on to another system. To write rows, use snowflake-sink.
Output: the body is one row as JSON text, an object with the column names as keys, such as {"id":1,"username":"oscerd","city":"Rome"}. The Content-Type header is application/json.
Good to know: the query runs again delay milliseconds after the previous run, so the same rows are sent again and again, unless consumedQuery marks or deletes each row once it is sent and query leaves such rows out: for example SELECT * FROM accounts WHERE processed = false with UPDATE accounts SET processed = true WHERE id = :#id. When the next step fails for a row, consumedQuery is not run for it, and the row is read again. A query that finds no rows sends nothing. query cannot contain ?. The format of date and time columns in the JSON depends on the Camel version. Log in with password, or with a key pair in privateKey or privateKeyFile: a Snowflake user of type SERVICE, meant for applications, cannot log in with a password. When a key is set, the password is not used. warehouse, schema and role choose what the session uses; when they are not set, the defaults of the user apply, and a user with no default warehouse cannot run a query that needs one. Each poll logs in to Snowflake anew, as the Kamelet keeps no connection open between polls.
Configuration Options
The following table summarizes the configuration options available for the snowflake-source Kamelet:
| Property | Name | Description | Type | Default | Example |
|---|---|---|---|---|---|
Account Host Name | Required The host name of your Snowflake account, without | string | myorg-myaccount.snowflakecomputing.com | ||
Query | Required The SQL query to run on each poll, usually a SELECT. Each row it returns becomes a message. | string | SELECT * FROM accounts WHERE processed = false | ||
Username | Required The Snowflake user to log in as. It needs the rights to run the queries of this Kamelet on their tables. | string | |||
Consumed Query | An SQL statement to run after each row has been sent on, to mark the row as read or to delete it, so that | string | UPDATE accounts SET processed = true WHERE id = :#id | ||
Database Name | The Snowflake database that holds the tables | string | SHOP | ||
Delay | How long to wait after one run of | integer | 500 | ||
Password | The password of the Snowflake user | string | |||
Private Key | The private key to log in with instead of a password: the key file in PEM format, encoded in base64 on a single line, such as the output of | string | |||
Private Key File | The path of the private key file to log in with instead of a password, in PEM format, such as | string | /etc/snowflake/rsa_key.p8 | ||
Private Key Passphrase | The passphrase of the key in | string | |||
Role | The Snowflake role to run the queries as, which must be granted to the user | string | READER | ||
Schema | The schema of | string | SALES | ||
Warehouse | The Snowflake virtual warehouse that runs the queries. When it is not set, the default warehouse of the user | string | COMPUTE_WH |
Dependencies
At runtime, the snowflake-source Kamelet relies upon the presence of the following dependencies:
-
camel:jackson3
-
camel:kamelet
-
camel:sql
-
mvn:net.snowflake:snowflake-jdbc:4.3.4
-
mvn:org.apache.commons:commons-dbcp2:2.14.0
Camel JBang usage
Prerequisites
-
You’ve installed JBang.
-
You have executed the following command:
jbang app install camel@apache/camel Supposing you have a file named route.yaml with this content:
- route:
from:
uri: "kamelet:snowflake-source"
parameters:
.
.
.
steps:
- to:
uri: "kamelet:log-sink" You can now run it directly through the following command
camel run route.yaml Snowflake Source Kamelet Description
Authentication methods
This Kamelet logs in to Snowflake as the Snowflake user username, either with password or with a key pair. A Snowflake user of type SERVICE, meant for applications, cannot log in with a password, and Snowflake is phasing out sign-in with a password alone for the other users too, so use a key pair for an integration. When a key is set, the password is not used.
-
privateKeyis the private key file in PEM format, encoded in base64 on a single line, such as the output ofbase64 -w0 rsa_key.p8. -
privateKeyFileis instead the path of that file, such as a Kubernetes Secret mounted as a file. Set one ofprivateKeyandprivateKeyFile, not both. -
privateKeyPassphraseis the passphrase of the key, when it is encrypted.
Snowflake’s guide to key-pair authentication shows how to make the key pair, such as with openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt, and how to set the public key on the user with ALTER USER … SET RSA_PUBLIC_KEY.
Output format
The Kamelet runs query again every delay milliseconds, and sends each row it returns as a message: a JSON object with the column names as keys. Without consumedQuery, the same rows are read again on every run.
Configuration
The Kamelet requires database connection parameters:
-
instanceUrl: the host name of your Snowflake account, such asmyorg-myaccount.snowflakecomputing.com -
databaseName(optional): the Snowflake database that holds the tables -
schema,warehouseandrole(optional): the schema ofdatabaseNamethat holds the tables, the virtual warehouse that runs the queries, and the role to run them as. See below. -
username: the Snowflake user to log in as, withpassword, or with a key pair inprivateKeyorprivateKeyFile -
query: the SQL query to run, such asSELECT * FROM accounts WHERE processed = false -
consumedQuery(optional): an SQL statement run for each row once it is sent, to mark the row as read or delete it, such asUPDATE accounts SET processed = true WHERE id = :#id
Usage example
The route below logs each account that is not processed yet, and marks it as processed.
- route:
from:
uri: "kamelet:snowflake-source"
parameters:
instanceUrl: "myorg-myaccount.snowflakecomputing.com"
username: "dbuser"
password: "dbpass"
databaseName: "SHOP"
query: "SELECT * FROM accounts WHERE processed = false"
consumedQuery: "UPDATE accounts SET processed = true WHERE id = :#id"
steps:
- to:
uri: "kamelet:log-sink" Warehouse, Schema and Role
The Kamelet sends these with each login, so the session starts with them:
-
warehouse: the virtual warehouse that runs the queries, such asCOMPUTE_WH. -
schema: the schema ofdatabaseNamethat holds the tables, such asSALES. -
role: the role to run the queries as. It must be granted to the user.
When one is not set, the default of the user applies, as set with ALTER USER … SET DEFAULT_WAREHOUSE, DEFAULT_NAMESPACE or DEFAULT_ROLE. A user with no default warehouse needs warehouse for a query that reads a table.
- route:
from:
uri: "kamelet:snowflake-source"
parameters:
instanceUrl: "myorg-myaccount.snowflakecomputing.com"
username: "ORDERS_SERVICE"
privateKeyFile: "/etc/snowflake/rsa_key.p8"
databaseName: "SHOP"
schema: "SALES"
warehouse: "REPORTING_WH"
role: "ORDERS_READER"
query: "SELECT * FROM accounts WHERE processed = false"
consumedQuery: "UPDATE accounts SET processed = true WHERE id = :#id"
steps:
- to:
uri: "kamelet:log-sink" Connections
The Kamelet keeps no connection to Snowflake open between polls: each poll logs in again, and starts a new session.
Example with a Key Pair
The same route, logged in with a key pair. The key file comes from a Kubernetes Secret mounted at /etc/snowflake, and the passphrase of the key from a property.
- route:
from:
uri: "kamelet:snowflake-source"
parameters:
instanceUrl: "myorg-myaccount.snowflakecomputing.com"
username: "ORDERS_SERVICE"
privateKeyFile: "/etc/snowflake/rsa_key.p8"
privateKeyPassphrase: "{{snowflake.key.passphrase}}"
databaseName: "SHOP"
query: "SELECT * FROM accounts WHERE processed = false"
consumedQuery: "UPDATE accounts SET processed = true WHERE id = :#id"
steps:
- to:
uri: "kamelet:log-sink"