Hello, we have the following connector config setup to deploy the following config map:
apiVersion: v1
kind: ConfigMap
metadata:
name: voiteq-mssql-jdbc-source-connector
namespace: confluent-operator
labels:
destination: connect
enabled: "true"
data:
voiteq-mssql-jdbc-source-connector.json: |-
{
"name": "voiteq-mssql-jdbc-source-connector",
"config": {
"connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
"tasks.max": "1",
"key.converter": "org.apache.kafka.connect.storage.StringConverter",
"value.converter": "io.confluent.connect.avro.AvroConverter",
"connection.url": env.JDBC_CONNECTION_URL,
"connection.user": env.DB_CONNECTION_USER,
"connection.password": env.DB_CONNECTION_PASSWORD,
"dialect.name": "SqlServerDatabaseDialect",
"mode": "timestamp",
"timestamp.column.name": "TimeStamp",
"query": "WITH Lines AS (SELECT le.DocumentNo, l.ProductCode, l.Location, SUM(le.QuantityPicked) AS [QuantityPicked], IIF(h.UserDef1 = '1', 'b2c', 'b2b') AS [type], CAST(MAX(he.[TimeStamp]) AS datetime2(3)) AS [TimeStamp] FROM pickmanagerinterop.dbo.HeaderEvent AS he INNER JOIN pickmanagerinterop.dbo.Header AS h ON he.HeaderId=h.HeaderId INNER JOIN pickmanagerinterop.dbo.Line AS l ON he.HeaderId=l.HeaderId INNER JOIN pickmanagerinterop.dbo.LineExport AS le ON l.LineId=le.LineId WHERE he.EventId = 22 GROUP BY l.LineId, le.DocumentNo, l.ProductCode, l.Location, h.UserDef1) SELECT * FROM Lines",
"quote.sql.identifiers": "always",
"table.types": "TABLE",
"poll.interval.ms": "10000",
"topic.prefix": "extranet.voiteq-mssql-line-export-data",
"value.converter.schema.registry.basic.auth.user.info": "${file:/mnt/secrets/schema_registry_credentials:credentials}",
"value.converter.basic.auth.credentials.source": "USER_INFO",
"value.converter.schema.registry.url": "https://psrc-mvkrw.europe-west3.gcp.confluent.cloud"
}
}
This works almost perfectly, deploying the connector to the connect cluster managed by confluent operator. However, the deployed connect config has a malformed query:
WITH Lines AS (SELECT le.DocumentNo, l.ProductCode, l.Location, SUM(le.QuantityPicked) AS [QuantityPicked], IIF(h.UserDef1 = '1', 'b2c', 'b2b') AS [type], CAST(MAX(he.[TimeStamp]) AS datetime2(3)) AS [TimeStamp] FROM pickmanagerinterop.dbo.HeaderEvent AS he INNER JOIN pickmanagerinterop.dbo.Header AS h ON he.HeaderId=h.HeaderId INNER JOIN pickmanagerinterop.dbo.Line AS l ON he.HeaderId=l.HeaderId INNER JOIN pickmanagerinterop.dbo.LineExport AS le ON l.LineId=le.LineId WHERE he.EventId = 22 GROUP BY l.LineId, le.DocumentNo, l.ProductCode, l.Location, h.UserDef1) SELECT lib operator.sh FROM Lines
Any help to fix this would be much appreciated.