Kafka to BigQuery MSK Connector BigQuerySinkConnector Failing with Invalid Configuration Error

Question

I am facing a challenge with the setup of a Kafka to BigQuery connector using MSK (Managed Streaming for Kafka). The connector is failing with an InvalidInput.InvalidConnectorConfiguration error. Below is the primary error message:

Code: InvalidInput.InvalidConnectorConfiguration
Message: The connector configuration is invalid. 
Message: Connector configuration is invalid and contains the following 2 error(s): 
Failed to construct GCS client: 
An unexpected error occurred while validating credentials for BigQuery:

This error suggests two main issues:

1. Failure in constructing the GCS client.
2. An unexpected error in validating BigQuery credentials.

Notably, I have successfully used these credentials with a Kafka Connect setup on my local machine. Additionally, when I intentionally format the keyfile incorrectly (for example, not escaping quotes properly), I receive a different error:

Failed to construct GCS client: Failed to create credentials from input stream 
An unexpected error occurred while validating credentials for BigQuery: Failed to create credentials from input stream 

This leads me to believe that the format and handling of the keyfile in MSK might be the issue. The relevant configuration for the GCS keyfile is:

keySource=JSON
keyfile="{"type":"service_account","project_id":"...","private_key_id":"...","private_key":"-----BEGIN PRIVATE KEY-----\\nABCD123...}"

Notice the escaped newline characters (\n) and the quotation marks at the beginning and end of the keyfile value.

I am looking for insights or suggestions on:

1. Could the issue be related to how MSK handles the JSON keyfile format, particularly with the escape characters?
2. Are there any specific configurations or formatting requirements in Kafka, MSK, or BigQuery for the keyfile that I might be missing?
3. Has anyone encountered a similar problem and found a solution?

Your assistance and guidance would be greatly appreciated. Thank you!

Answer 1

1. remove new line (\n) with escape backslash. You don't need to add new lines to the key content;
2. remove wrapping quote characters, the entire value of JSON doesn't need to be in quotes.

It should look like this:

keySource=JSON
keyfile={"type":"service_account","project_id":"...","private_key_id":"...","private_key":"-----BEGIN PRIVATE KEY-----ABCD123...}