How to use read_kafka() SQL with secret()?

[rak_haq]

Hi,

I want to read data from the Azure Event Hub using SQL.

Can someone please give me an executable example where you can also use the connection string from the event hub using the SQL function secret(), for example?

This is what i tried but it Databricks can not create a KafkaAdmin:

SELECT

cast(value as STRING) as raw_json,

current_timestamp() as processing_time

FROM read_kafka(

 bootstrapServers => 'host:9093',

 subscribe => 'mytopic',

 `kafka.sasl.jaas.config` => secret('scope', 'secret')

)

LIMIT 10

[LRALVA]

Hi @rak_haq 

Here’s how you can read from an Azure Event Hub’s Kafka endpoint in Databricks SQL—using the built-in read_kafka table function and the secret() function to inject your connection string securely:

In summary, you must:
   1.  Point bootstrapServers at your Event Hubs Kafka endpoint (<NAMESPACE>.servicebus.windows.net:9093).
   2. Tell Kafka to use SASL_SSL with PLAIN and pass the full Event Hubs connection string (including EntityPath) via         kafka.sasl.jaas.config.
   3.  Retrieve that connection string from a Databricks Secret with secret('scope','key') and wrap it correctly in the               JAAS login module config.
   4.Include the required Kafka options (security.protocol, sasl.mechanism, sasl.jaas.config) in your read_kafka call.

Example SQL

SELECT
CAST(value AS STRING) AS raw_json,
current_timestamp() AS processing_time
FROM read_kafka(
bootstrapServers => '<YOUR_NAMESPACE>.servicebus.windows.net:9093',
subscribe => '<YOUR_EVENTHUB_NAME>',
`kafka.security.protocol`=> 'SASL_SSL',
`kafka.sasl.mechanism` => 'PLAIN',
`kafka.sasl.jaas.config` => concat(
'org.apache.kafka.common.security.plain.PlainLoginModule required ',
'username="$ConnectionString" ',
'password="', secret('myScope','eventHubConnection'), '";'
)
)
LIMIT 10;

[rak_haq]

@LRALVA Thank you, but already tried this and iam getten the following Error-Message:

I read somewhere you have to use "kafkashaded.org.apache...", do u know something about? (source)

[rak_haq · Accepted solution]

I found the solution und could successfully establish a connection to Event-Hub. 

SELECT
cast(value as STRING) as raw_json,
current_timestamp() as processing_time
FROM read_kafka(
  bootstrapServers => '<YOUR EVENT-HUB NAMESPACE>.servicebus.windows.net:9093',
  subscribe => '<YOUR TOPIC>',
  `kafka.sasl.mechanism` => 'PLAIN',
  `kafka.security.protocol` => 'SASL_SSL',
  `kafka.sasl.jaas.config` => concat(
    'kafkashaded.org.apache.kafka.common.security.scram.ScramLoginModule required ', 
    'username="$ConnectionString" ', 'password="', secret('<SCOPE>','<KEY>'), '";' -- Get your Credentials from Key Vault
)
)
Limit 10