sqlserver sink Microsoft SQL Server Sink

Provided by: "Apache Software Foundation"

Support Level for this Kamelet is: "Preview"

Send data to a Microsoft SQL Server Database.

In you KameletBinding file you’ll need to explicitly declare the SQL Server driver dependency in spec→integration→dependencies

  • "mvn:com.microsoft.sqlserver:mssql-jdbc:<version>"

This Kamelet expects a JSON as body. The mapping between the JSON fields and parameters is done by key, so if you have the following query:

'INSERT INTO accounts (username,city) VALUES (:#username,:#city)'

The Kamelet needs to receive as input something like:

'{ "username":"oscerd", "city":"Rome"}'

Configuration Options

The following table summarizes the configuration options available for the sqlserver-sink Kamelet:

Property Name Description Type Default Example

databaseName *

Database Name

The Database Name we are pointing

string

password *

Password

The password to use for accessing a secured SQL Server Database

string

query *

Query

The Query to execute against the SQL Server Database

string

"INSERT INTO accounts (username,city) VALUES (:#username,:#city)"

serverName *

Server Name

Server Name for the data source

string

"localhost"

username *

Username

The username to use for accessing a secured SQL Server Database

string

serverPort

Server Port

Server Port for the data source

string

1433
Fields marked with (*) are mandatory.

Usage

This section summarizes how the sqlserver-sink can be used in various contexts.

Knative Sink

The sqlserver-sink Kamelet can be used as Knative sink by binding it to a Knative object.

sqlserver-sink-binding.yaml
apiVersion: camel.apache.org/v1alpha1
kind: KameletBinding
metadata:
  name: sqlserver-sink-binding
spec:
  source:
    ref:
      kind: Channel
      apiVersion: messaging.knative.dev/v1
      name: mychannel
  sink:
    ref:
      kind: Kamelet
      apiVersion: camel.apache.org/v1alpha1
      name: sqlserver-sink
    properties:
      databaseName: "The Database Name"
      password: "The Password"
      query: "INSERT INTO accounts (username,city) VALUES (:#username,:#city)"
      serverName: "localhost"
      username: "The Username"

Make sure you have Camel K installed into the Kubernetes cluster you’re connected to.

Save the sqlserver-sink-binding.yaml file into your hard drive, then configure it according to your needs.

You can run the sink using the following command:

kubectl apply -f sqlserver-sink-binding.yaml

Dependencies

The Kamelet needs the following dependencies:

  • camel:jackson

  • camel:kamelet

  • camel:sql

  • mvn:org.apache.commons:commons-dbcp2:2.8.0

Binding to Knative using the Kamel CLI:

The procedure described above can be simplified into a single execution of the kamel bind command:

kamel bind channel:mychannel sqlserver-sink -p "sink.databaseName=The Database Name" -p "sink.password=The Password" -p "sink.query=INSERT INTO accounts (username,city) VALUES (:#username,:#city)" -p "sink.serverName=localhost" -p "sink.username=The Username"

This will create the KameletBinding under the hood and apply it to the current namespace in the cluster.

Kafka Sink

The sqlserver-sink Kamelet can be used as Kafka sink by binding it to a Kafka topic.

sqlserver-sink-binding.yaml
apiVersion: camel.apache.org/v1alpha1
kind: KameletBinding
metadata:
  name: sqlserver-sink-binding
spec:
  source:
    ref:
      kind: KafkaTopic
      apiVersion: kafka.strimzi.io/v1beta1
      name: my-topic
  sink:
    ref:
      kind: Kamelet
      apiVersion: camel.apache.org/v1alpha1
      name: sqlserver-sink
    properties:
      databaseName: "The Database Name"
      password: "The Password"
      query: "INSERT INTO accounts (username,city) VALUES (:#username,:#city)"
      serverName: "localhost"
      username: "The Username"

Ensure that you’ve installed Strimzi and created a topic named my-topic in the current namespace. Make also sure you have Camel K installed into the Kubernetes cluster you’re connected to.

Save the sqlserver-sink-binding.yaml file into your hard drive, then configure it according to your needs.

You can run the sink using the following command:

kubectl apply -f sqlserver-sink-binding.yaml

Binding to Kafka using the Kamel CLI:

The procedure described above can be simplified into a single execution of the kamel bind command:

kamel bind kafka.strimzi.io/v1beta1:KafkaTopic:my-topic sqlserver-sink -p "sink.databaseName=The Database Name" -p "sink.password=The Password" -p "sink.query=INSERT INTO accounts (username,city) VALUES (:#username,:#city)" -p "sink.serverName=localhost" -p "sink.username=The Username"

This will create the KameletBinding under the hood and apply it to the current namespace in the cluster.

Kamelet source file

Have a look at the following link:

https://github.com/apache/camel-kamelets/blob/main/sqlserver-sink.kamelet.yaml

Back to top