= Debezium SQL Server Connector Component
:doctitle: Debezium SQL Server Connector
:shortname: debezium-sqlserver
:artifactid: camel-debezium-sqlserver
:description: Capture changes from an SQL Server database.
:since: 3.0
:supportlevel: Stable
:component-header: Only consumer is supported
//Manually maintained attributes
:group: Debezium
:camel-spring-boot-name: debezium-sqlserver

*Since Camel {since}*

*{component-header}*

The Debezium SQL Server component is wrapper around https://debezium.io/[Debezium] using https://debezium.io/documentation/reference/0.10/operations/embedded.html[Debezium Embedded], which enables Change Data Capture from SQL Server database using Debezium without the need for Kafka or Kafka Connect.

*Note on handling failures:* Per https://debezium.io/documentation/reference/0.10/operations/embedded.html#_handling_failures[Debezium Embedded Engine] documentation, the engines is actively recording source offsets and periodically flushes these offsets to a persistent storage, so when the application is restarted or crashed, the engine will resume from the last recorded offset.
Thus, at normal operation, your downstream routes will receive each event exactly once, however in case of an application crash (not having a graceful shutdown), the application will resume from the last recorded offset,
which may result in receiving duplicate events immediately after the restart. Therefore, your downstream routes should be tolerant enough of such case and deduplicate events if needed.

*Note:* The Debezium SQL Server component is currently not supported in OSGi

Maven users will need to add the following dependency to their `pom.xml`
for this component.

[source,xml]
----
<dependency>
    <groupId>org.apache.camel</groupId>
    <artifactId>camel-debezium-sqlserver</artifactId>
    <version>x.x.x</version>
    <!-- use the same version as your Camel core version -->
</dependency>
----

== URI format

---------------------------
debezium-sqlserver:name[?options]
---------------------------

// component-configure options: START

// component-configure options: END

// component options: START
include::partial$component-configure-options.adoc[]
include::partial$component-endpoint-options.adoc[]
// component options: END

// endpoint options: START

// endpoint options: END

For more information about configuration:
https://debezium.io/documentation/reference/0.10/operations/embedded.html#engine-properties[https://debezium.io/documentation/reference/0.10/operations/embedded.html#engine-properties]
https://debezium.io/documentation/reference/0.10/connectors/sqlserver.html#connector-properties[https://debezium.io/documentation/reference/0.10/connectors/sqlserver.html#connector-properties]

== Message headers

=== Consumer headers

The following headers are available when consuming change events from Debezium.
[width="100%",cols="2m,2m,1m,5",options="header"]
|===
| Header constant                           | Header value                                   | Type        | Description
| DebeziumConstants.HEADER_IDENTIFIER       | "CamelDebeziumIdentifier"                      | String      | The identifier of the connector, normally is this format "+++{server-name}.{database-name}.{table-name}+++".
| DebeziumConstants.HEADER_KEY              | "CamelDebeziumKey"                             | Struct      | The key of the event, normally is the table Primary Key.
| DebeziumConstants.HEADER_SOURCE_METADATA  | "CamelDebeziumSourceMetadata"                  | Map         | The metadata about the source event, for example `table` name, database `name`, log position, etc, please refer to the Debezium documentation for more info.
| DebeziumConstants.HEADER_OPERATION        | "CamelDebeziumOperation"                       | String      | If presents, the type of event operation. Values for the connector are `c` for create (or insert), `u` for update, `d` for delete or `r` in case of a snapshot event.
| DebeziumConstants.HEADER_TIMESTAMP        | "CamelDebeziumTimestamp"                       | Long        | If presents, the time (using the system clock in the JVM) at which the connector processed the event.
| DebeziumConstants.HEADER_BEFORE           | "CamelDebeziumBefore"                          | Struct     | If presents, contains the state of the row before the event occurred.
|===

== Message body
The message body if is not `null` (in case of tombstones), it contains the state of the row after the event occurred as `Struct` format or `Map` format if you use the included Type Converter from `Struct` to `Map` (please look below for more explanation).

== Samples

=== Consuming events

Here is a very simple route that you can use in order to listen to Debezium events from SQL Server connector.

[source,java]
----
from("debezium-sqlserver:dbz-test-1?offsetStorageFileName=/usr/offset-file-1.dat&databaseHostname=localhost&databaseUser=debezium&databasePassword=dbz&databaseServerName=my-app-connector&databaseHistoryFileFilename=/usr/history-file-1.dat")
    .log("Event received from Debezium : ${body}")
    .log("    with this identifier ${headers.CamelDebeziumIdentifier}")
    .log("    with these source metadata ${headers.CamelDebeziumSourceMetadata}")
    .log("    the event occured upon this operation '${headers.CamelDebeziumSourceOperation}'")
    .log("    on this database '${headers.CamelDebeziumSourceMetadata[db]}' and this table '${headers.CamelDebeziumSourceMetadata[table]}'")
    .log("    with the key ${headers.CamelDebeziumKey}")
    .log("    the previous value is ${headers.CamelDebeziumBefore}")
----

By default, the component will emit the events in the body and `CamelDebeziumBefore` header as https://kafka.apache.org/22/javadoc/org/apache/kafka/connect/data/Struct.html[`Struct`] data type, the reasoning behind this, is to perceive the schema information in case is needed.
However, the component as well contains a xref:manual::type-converter.adoc[Type Converter] that converts
from default output type of https://kafka.apache.org/22/javadoc/org/apache/kafka/connect/data/Struct.html[`Struct`] to `Map` in order to leverage Camel's rich xref:manual::data-format.adoc[Data Format] types which many of them work out of box with `Map` data type.
To use it, you can either add `Map.class` type when you access the message e.g: `exchange.getIn().getBody(Map.class)`, or you can convert the body always to `Map` from the route builder by adding `.convertBodyTo(Map.class)` to your Camel Route DSL after `from` statement.

We mentioned above about the schema, which can be used in case you need to perform advance data transformation and the schema is needed for that. If you choose not to convert your body to `Map`,
you can obtain the schema information as https://kafka.apache.org/22/javadoc/org/apache/kafka/connect/data/Schema.html[`Schema`] type from `Struct` like this:

[source,java]
----
from("debezium-sqlserver:[name]?[options]])
    .process(exchange -> {
        final Struct bodyValue = exchange.getIn().getBody(Struct.class);
        final Schema schemaValue = bodyValue.schema();

        log.info("Body value is :" + bodyValue);
        log.info("With Schema : " + schemaValue);
        log.info("And fields of :" + schemaValue.fields());
        log.info("Field name has `" + schemaValue.field("name").schema() + "` type");
    });
----

*Important Note:* This component is a thin wrapper around Debezium Engine as mentioned, therefore before using this component in production, you need to understand how Debezium works and how configurations can reflect the expected behavior, especially in regards to https://debezium.io/documentation/reference/0.9/operations/embedded.html#_handling_failures[handling failures].


include::spring-boot:partial$starter.adoc[]
