Using Kafka Connect to Capture Data from a Relational Database

Hands-On Lab

 

Photo of Chad Crowell

Chad Crowell

DevOps Training Architect II in Content

Length

00:30:00

Difficulty

Intermediate

Kafka Connect can be used for a variety of databases, given it has a multitude of built-in connectors. In this hands-on lab, we set up Kafka Connect in a way allowing us to continuously stream data from a SQLite database directly into Kafka. This lab demonstrates how easy it is to use Kafka Connect to get data into Kafka for further analysis.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Using Kafka Connect to Capture Data from a Relational Database

Introduction

Kafka Connect can be used for a variety of databases, given it has a multitude of built-in connectors. In this hands-on lab, we set up Kafka Connect in a way allowing us to continuously stream data from a SQLite database directly into Kafka. This lab demonstrates how easy it is to use Kafka Connect to get data into Kafka for further analysis.

Solution

  1. Begin by logging in to the lab server using the credentials provided on the hands-on lab page.

    ssh cloud_user@PUBLIC_IP_ADDRESS

Install SQLite3

  1. Run the Docker Container and open a new shell in it.

    sudo docker run -it --rm --name sqlite-test --network host confluentinc/docker-demo-base:3.3.0
  2. Enter the password from the lab instructions.

  3. Navigate to the tmp directory.

    cd /tmp
  4. Start Kafka.

    confluent start
  5. Install SQLite3.

    apt-get install sqlite3
  6. If you receive an error message about being unable to locate package, update the packages and then attempt to install SQLite3 again.

    apt-get update
    apt-get install sqlite3

Create a New Database and Populate It with a Table and Some Data

  1. Create a new database called "test.db".

    sqlite3 test.db
  2. Create a new table in the SQLite database called "accounts".

    CREATE TABLE accounts (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        name VARCHAR (255));
  3. Insert values into the table to begin populating it.

    INSERT INTO accounts(name) VALUES('chad');
    INSERT INTO accounts(name) VALUES('terry');
  4. Exit SQLite.

    .quit

    Start Kafka

  5. Stop Kafka Connect.

    confluent stop connect
  6. Start Kafka Connect in standalone mode.

    connect-standalone -daemon /etc/schema-registry/connect-avro-standalone.properties /etc/kafka-connect-jdbc/source-quickstart-sqlite.properties
  7. Verify that the connector was created.

    cat /logs/connectStandalone.out | grep -i "finished"
  8. Use an alternative method to verify the connector was created.

    curl -s localhost:8083/connectors
  9. Examine the Kafka topic created.

    kafka-topics --list --zookeeper localhost:2181 | grep test-sqlite-jdbc

Start a Kafka Consumer and Write New Data to the Database

  1. Open a Kafka consumer.

    kafka-avro-console-consumer --new-consumer --bootstrap-server localhost:9092 --topic test-sqlite-jdbc-accounts --from-beginning
  2. Open a new tab to a new terminal session.

  3. Open a new shell in this session.

    sudo docker exec -it sqlite-test //bin//bash
  4. Transfer to the tmp directory.

    cd /tmp
  5. Access the SQLite database test.db.

    sqlite3 test.db
  6. Insert a new value into the accounts table.

    INSERT INTO accounts(name) VALUES('william');
  7. Return to the previous session with the consumer and verify the data has been written.

  8. Return back to the new terminal session and add new values to the table.

    INSERT INTO accounts(name) VALUES('karen');
    INSERT INTO accounts(name) VALUES('lisa');
    INSERT INTO accounts(name) VALUES('roger');
  9. Return to the consumer and verify the new data.

Conclusion

Congratulations — you've completed this hands-on lab!