The usage of Useless Letter Queues with SQL Move Builder

Posted in Technical |
April 20, 2023 4 min learn

What’s a lifeless letter queue (DLQ)?

Cloudera SQL Move builder offers non-technical customers the facility of a unified circulate processing engine so they are able to combine, combination, question, and analyze each streaming and batch information resources in one SQL interface. This permits trade customers to outline occasions of passion for which they wish to often observe and reply briefly. A lifeless letter queue (DLQ) can be utilized if there are deserialization mistakes when occasions are fed on from a Kafka subject. DLQ turns out to be useful to peer if there are any screw ups because of invalid enter within the supply Kafka subject and makes it imaginable to file and debug issues associated with invalid inputs. 

Making a DLQ

We can use the instance schema definition supplied by way of SSB to reveal this option. The schema has two houses: “identify” and “temp” (for temperature) to seize sensor information in JSON structure. Step one is to create two Kafka subjects: “sensor_data” and “sensor_data_dlq” which will also be finished the next manner:

kafka-topics.sh --bootstrap-server <bootstrap-server> --create --topic sensor_data --replication-factor 1 --partitions 1

kafka-topics --bootstrap-server <bootstrap-server> --create --topic sensor_data_dlq --replication-factor 1 --partitions 1

As soon as the Kafka subjects are created, we will be able to arrange a Kafka supply in SSB. SSB supplies a handy solution to paintings with Kafka as we will be able to do the entire setup the use of the UI. In Challenge Explorer, open the Information Assets folder. Proper clicking on “Kafka” brings up the context menu the place we will be able to open the advent modal window.

We wish to supply a singular identify for this new information supply, the checklist of agents, and the protocol in use:

After the brand new Kafka supply is effectively registered, the next move is to create a brand new digital desk. We will do this from the Challenge Explorer by way of proper clicking “Digital Tables” and opting for “New Kafka Desk” from the context menu. Let’s fill out the shape with the next values:

• Desk Identify: Any distinctive identify; we can consumer “sensors” on this instance
• Kafka Cluster: Make a choice the Kafka supply registered within the earlier step
• Information Layout: JSON
• Subject Identify: “sensor_data” which we created previous

 

We will see beneath the “Schema Definition” tab that the instance supplied has the 2 fields, “identify” and “temp,” as mentioned previous. The closing step is to arrange the DLQ capability, which we will be able to do by way of going to the “Deserialization” tab. The “Deserialization Failure Handler Coverage” drop-down has the next choices:

• “Fail”: Let the task crash after which auto-restart atmosphere dictates what occurs subsequent
• “Forget about”: Ignores the message that would no longer be deserialized, strikes to the following
• “Forget about and Log”: Similar as forget about however logs every time it encounters a deserialization failure
• “Save to DLQ”: Sends the invalid message to the desired Kafka subject

Let’s make a selection “Save to DLQ” and select the prior to now created “sensor_data_dlq” subject from the “DLQ Subject Identify” drop-down. We will click on “Create and Assessment” to create the brand new digital desk.

Trying out the DLQ

First, create a brand new SSB task from the Challenge Explorer. We will run the next SQL question to eat the information from the Kafka subject:

SELECT * from sensors;

In the next move we can use the console manufacturer and user command line gear to engage with Kafka. Let’s ship a sound enter to the “sensor_data” subject and test whether it is fed on by way of our working task.

kafka-console-producer.sh --broker-list <dealer> --topic sensor_data

>{"identify":"sensor-1", "temp": 32}

Checking again at the SSB UI, we will be able to see that the brand new message has been processed:

Now, ship an invalid enter to the supply Kafka subject: 

kafka-console-producer.sh --broker-list <dealer> --topic sensor_data >invalid information

We gained’t see any new messages in SSB because the invalid enter can’t be deserialized. Let’s test at the DLQ subject we arrange previous to peer if the invalid message used to be captured:

kafka-console-consumer.sh --bootstrap-server <server> --topic sensor_data_dlq --from-beginning invalid information

The invalid enter is there which verifies that the DLQ capability is operating as it should be, permitting us to additional examine any deserialization error.

Conclusion

On this weblog, we coated the features of the DLQ function in Flink and SSB. This option may be very helpful to gracefully maintain a failure in a knowledge pipeline because of invalid information. The usage of this capacity, it is extremely simple and fast to determine if there are any dangerous information within the pipeline and the place the foundation explanation for the ones dangerous information are.

Any one can check out SSB the use of the Move Processing Neighborhood Version (CSP-CE). CE makes growing circulate processors simple, as it may be finished proper out of your desktop or some other building node. Analysts, information scientists, and builders can now review new options, broaden SQL-based circulate processors in the neighborhood the use of SQL Move Builder powered by way of Flink, and broaden Kafka Shoppers/Manufacturers and Kafka Attach Connectors, all in the neighborhood ahead of transferring to manufacturing in CDP.