How to join a table and a table with a foreign key

Problem:

you need to join two tables, but they have different primary keys

Edit this page

Example use case:

Suppose you are running an internet streaming music service where you offer albums or individual music tracks for sale. You'd like to track trends in listener preference by joining the track purchases against the table of albums. The issue is that the track purchase key doesn't align with the primary key for the album table. However, since the value of the track purchase contains the id of the album, you can extract the album id from the track purchase and complete a foreign key join against the album table.

Code example:

Try it

1
Initialize the project

To get started, make a new directory anywhere you’d like for this project:

mkdir fk-joins && cd fk-joins

2
Get Confluent Platform

Next, create the following docker-compose.yml file to obtain Confluent Platform:

---
version: '2'

services:
  zookeeper:
    image: confluentinc/cp-zookeeper:5.5.0
    hostname: zookeeper
    container_name: zookeeper
    ports:
      - "2181:2181"
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000

  broker:
    image: confluentinc/cp-enterprise-kafka:5.5.0
    hostname: broker
    container_name: broker
    depends_on:
      - zookeeper
    ports:
      - "29092:29092"
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: 'zookeeper:2181'
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://broker:9092,PLAINTEXT_HOST://localhost:29092
      KAFKA_METRIC_REPORTERS: io.confluent.metrics.reporter.ConfluentMetricsReporter
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_GROUP_INITIAL_REBALANCE_DELAY_MS: 0
      KAFKA_TOOLS_LOG4J_LOGLEVEL: ERROR
      CONFLUENT_METRICS_REPORTER_BOOTSTRAP_SERVERS: broker:9092
      CONFLUENT_METRICS_REPORTER_ZOOKEEPER_CONNECT: zookeeper:2181
      CONFLUENT_METRICS_REPORTER_TOPIC_REPLICAS: 1
      CONFLUENT_METRICS_ENABLE: 'true'
      CONFLUENT_SUPPORT_CUSTOMER_ID: 'anonymous'

  schema-registry:
    image: confluentinc/cp-schema-registry:5.5.0
    hostname: schema-registry
    container_name: schema-registry
    depends_on:
      - zookeeper
      - broker
    ports:
      - "8081:8081"
    environment:
      SCHEMA_REGISTRY_HOST_NAME: schema-registry
      SCHEMA_REGISTRY_KAFKASTORE_CONNECTION_URL: 'zookeeper:2181'
      SCHEMA_REGISTRY_LOG4J_ROOT_LOGLEVEL: WARN

And launch it by running:

docker-compose up -d

3
Configure the project

Create the following Gradle build file, named build.gradle for the project:

buildscript {
    repositories {
        jcenter()
    }
    dependencies {
        classpath "com.commercehub.gradle.plugin:gradle-avro-plugin:0.15.1"
        classpath "com.github.jengelman.gradle.plugins:shadow:4.0.2"
    }
}

plugins {
    id "java"
    id "com.google.cloud.tools.jib" version "1.1.1"
}

sourceCompatibility = "1.8"
targetCompatibility = "1.8"
version = "0.0.1"

repositories {
    mavenCentral()
    jcenter()

    maven {
        url "http://packages.confluent.io/maven"
    }
}

apply plugin: "com.commercehub.gradle.plugin.avro"
apply plugin: "com.github.johnrengelman.shadow"

dependencies {
    compile "org.apache.avro:avro:1.8.2"
    implementation "org.slf4j:slf4j-simple:1.7.26"
    implementation "org.apache.kafka:kafka-streams:2.4.0"
    implementation "io.confluent:kafka-streams-avro-serde:5.4.0"
    testCompile "org.apache.kafka:kafka-streams-test-utils:2.4.0"
    testCompile "junit:junit:4.12"
}

test {
    testLogging {
        outputs.upToDateWhen { false }
        showStandardStreams = true
        exceptionFormat = "full"
    }
}

jar {
  manifest {
    attributes(
      "Class-Path": configurations.compile.collect { it.getName() }.join(" "),
      "Main-Class": "io.confluent.developer.FkJoinTableToTable"
    )
  }
}

shadowJar {
    archiveName = "ktable-fkjoins-standalone-${version}.${extension}"
}

And be sure to run the following command to obtain the Gradle wrapper:

gradle wrapper

Next, create a directory for configuration data:

mkdir configuration

Then create a development file at configuration/dev.properties:

application.id=fk-joining-app
bootstrap.servers=localhost:29092
schema.registry.url=http://localhost:8081

album.topic.name=albums
album.topic.partitions=1
album.topic.replication.factor=1

tracks.purchase.topic.name=purchases
tracks.purchase.topic.partitions=1
tracks.purchase.topic.replication.factor=1

music.interest.topic.name=music-interest
music.interest.topic.partitions=1
music.interest.topic.replication.factor=1

4
Create a schema for the events

This tutorial uses three streams: one called albums that holds album reference data, one called trackPurchases that holds an update-stream of inbound music track purchases, and one called musicInterestTable that holds the result of a foreign-key join between trackPurchases and albums.

In this case the inbound keys are different, but the trackPurchases stream has the id of the album in its value. We will use the KTable foreign-key join functionality to extract the album id and perform the join.

Let’s create schemas for all three.

Create a directory for the schemas that represent the events in the stream:

mkdir -p src/main/avro

Then create the following Avro schema file at src/main/avro/album.avsc for the album lookup table:

{
  "namespace": "io.confluent.developer.avro",
  "type": "record",
  "name": "Album",
  "fields": [
    {"name": "id", "type": "long"},
    {"name": "title", "type": "string"},
    {"name": "genre", "type": "string"},
    {"name": "artist", "type": "string"}
  ]
}

Next, create another Avro schema file at src/main/avro/track-purchase.avsc for the update-stream of ratings:

{
  "namespace": "io.confluent.developer.avro",
  "type": "record",
  "name": "TrackPurchase",
  "fields": [
    {"name": "id", "type": "long"},
    {"name": "song_title", "type": "string"},
    {"name": "album_id", "type": "long"},
    {"name": "price", "type": "double"}
  ]
}

And finally, create another Avro schema file at src/main/avro/music-interest.avsc for the result of the join:

{
  "namespace": "io.confluent.developer.avro",
  "type": "record",
  "name": "MusicInterest",
  "fields": [
    {"name": "id", "type": "string"},
    {"name": "genre", "type": "string"},
    {"name": "artist", "type": "string"}
  ]
}

Because we will use this Avro schema in our Java code, we’ll need to compile it. The Gradle Avro plugin is a part of the build, so it will see your new Avro files, generate Java code for them, and compile those and all other Java sources. Run this command to get it all done:

./gradlew build

5
Create the Kafka Streams topology

Create a directory for the Java files in this project:

mkdir -p src/main/java/io/confluent/developer

Then create the following file at src/main/java/io/confluent/developer/FkJoinTableToTable.java. Let’s take a close look at the buildTopology() method, which uses the Kafka Streams DSL.

The first thing the method does is create an instance of StreamsBuilder, which is the helper object that lets us build our topology. With our builder in hand, there are three things we need to do. First, we call the table() method to create a KStream<Long, Album> object. In this case, we can use a KTable as each we know the topic is keyed by the album id and each album-id is unique.

Now that we have our table of albums, we’ll move on to the tracks available for purchase stream. While it may seem that purchasing music tracks would end up in a KStream, there are a couple of circumstances that allow us to represent the track purchases as a table instead.

First, each track purchase has a simple Long key, representing a increasing sequence number for each purchase. This means each purchase is unique, and we don’t need to worry about later sales replacing earlier purchases by the same customer. Second, we need to join each track purchase with an existing album so we can create a trend of artists and genres gaining or losing popularity.

But if the key is a monotonically increasing number, how can we join against the album table? The trackPurchases table has the album id as part of its value payload, so we can use the KTable.join method with a ForeignKeyExtractor parameter to extract the album id for the join comparison.

Creating the KTable<Long,TrackPurchase> of track purchases looks just like our first step with the albums: we create a table from the topic. Note that we must choose the same key—the album id—for our join to work. You can accomplish this by providing a Java 8 method handle TrackPurchase::getAlbumId to extract the id.

At this point we should discuss the importance of the order in which we use the KTable parameters in the join(). The trackPurchases table is the calling or left-side-table, and it is the table where the primary key is embedded in its value. The left-side-table always provides the ForeignKeyExtractor function.

The albums table is the right-side-table and always has the primary key for the join. This is where order matters, for example: if you tried albums.join(trackPurchases..) the join would never work as the albums table has no knowledge of the trackPurchases table details.

If you have a situation where you have two tables for which the primary keys don’t match, yet each table has a reference to the other’s primary key, then the order of the tables in the join method won’t matter. This scenario is probably unlikely in practice.

With the trackPurchases table and the albums table in hand, all that remains is to join them using the join() method. It’s a wonderfully simply one-liner, but we have concealed a bit of complexity in the form of the MusicInterestJoiner class. More on that in a moment.

For more background on KTable foreign key joins you can read the original KIP-213 proposal.

package io.confluent.developer;


import io.confluent.common.utils.TestUtils;
import io.confluent.developer.avro.Album;
import io.confluent.developer.avro.MusicInterest;
import io.confluent.developer.avro.TrackPurchase;
import io.confluent.kafka.serializers.AbstractKafkaAvroSerDeConfig;
import io.confluent.kafka.serializers.KafkaAvroDeserializer;
import io.confluent.kafka.serializers.KafkaAvroSerializer;
import io.confluent.kafka.streams.serdes.avro.SpecificAvroSerde;
import org.apache.avro.specific.SpecificRecord;
import org.apache.kafka.clients.admin.AdminClient;
import org.apache.kafka.clients.admin.NewTopic;
import org.apache.kafka.common.serialization.Serde;
import org.apache.kafka.common.serialization.Serdes;
import org.apache.kafka.streams.KafkaStreams;
import org.apache.kafka.streams.StreamsBuilder;
import org.apache.kafka.streams.StreamsConfig;
import org.apache.kafka.streams.Topology;
import org.apache.kafka.streams.kstream.Consumed;
import org.apache.kafka.streams.kstream.KTable;
import org.apache.kafka.streams.kstream.Produced;

import java.io.FileInputStream;
import java.io.IOException;
import java.time.Duration;
import java.util.*;
import java.util.concurrent.CountDownLatch;

public class FkJoinTableToTable {


	public Properties buildStreamsProperties(Properties envProps) {
        Properties props = new Properties();

        props.put(StreamsConfig.APPLICATION_ID_CONFIG, envProps.getProperty("application.id"));
        props.put(StreamsConfig.BOOTSTRAP_SERVERS_CONFIG, envProps.getProperty("bootstrap.servers"));
        props.put(StreamsConfig.STATE_DIR_CONFIG, TestUtils.tempDirectory().getPath());
        props.put(AbstractKafkaAvroSerDeConfig.SCHEMA_REGISTRY_URL_CONFIG, envProps.getProperty("schema.registry.url"));

        return props;
    }

    public Topology buildTopology(Properties envProps) {
        final StreamsBuilder builder = new StreamsBuilder();
        final String albumTopic = envProps.getProperty("album.topic.name");
        final String userTrackPurchaseTopic = envProps.getProperty("tracks.purchase.topic.name");
        final String musicInterestTopic = envProps.getProperty("music.interest.topic.name");

        final Serde<Long> longSerde = getPrimitiveAvroSerde(envProps, true);
        final Serde<MusicInterest> musicInterestSerde = getSpecificAvroSerde(envProps);
        final Serde<Album> albumSerde = getSpecificAvroSerde(envProps);
        final Serde<TrackPurchase> trackPurchaseSerde = getSpecificAvroSerde(envProps);

        final KTable<Long, Album> albums = builder.table(albumTopic, Consumed.with(longSerde, albumSerde));

        final KTable<Long, TrackPurchase> trackPurchases = builder.table(userTrackPurchaseTopic, Consumed.with(longSerde, trackPurchaseSerde));
        final MusicInterestJoiner trackJoiner = new MusicInterestJoiner();

        final KTable<Long, MusicInterest> musicInterestTable = trackPurchases.join(albums,
                                                                             TrackPurchase::getAlbumId,
                                                                             trackJoiner);

        musicInterestTable.toStream().to(musicInterestTopic, Produced.with(longSerde, musicInterestSerde));

        return builder.build();
    }

    @SuppressWarnings("unchecked")
    static <T> Serde<T> getPrimitiveAvroSerde(final Properties envProps, boolean isKey) {
        final KafkaAvroDeserializer deserializer = new KafkaAvroDeserializer();
        final KafkaAvroSerializer serializer = new KafkaAvroSerializer();
        final Map<String, String> config = new HashMap<>();
        config.put(AbstractKafkaAvroSerDeConfig.SCHEMA_REGISTRY_URL_CONFIG,
                envProps.getProperty("schema.registry.url"));
        deserializer.configure(config, isKey);
        serializer.configure(config, isKey);
        return (Serde<T>)Serdes.serdeFrom(serializer, deserializer);
    }

    static <T extends SpecificRecord> SpecificAvroSerde<T> getSpecificAvroSerde(final Properties envProps) {
        final SpecificAvroSerde<T> specificAvroSerde = new SpecificAvroSerde<>();

        final HashMap<String, String> serdeConfig = new HashMap<>();
        serdeConfig.put(AbstractKafkaAvroSerDeConfig.SCHEMA_REGISTRY_URL_CONFIG,
                envProps.getProperty("schema.registry.url"));

        specificAvroSerde.configure(serdeConfig, false);
        return specificAvroSerde;
    }

    public void createTopics(final Properties envProps) {
        final Map<String, Object> config = new HashMap<>();
        config.put("bootstrap.servers", envProps.getProperty("bootstrap.servers"));
        final AdminClient client = AdminClient.create(config);

        final List<NewTopic> topics = new ArrayList<>();

        topics.add(new NewTopic(
                envProps.getProperty("album.topic.name"),
                Integer.parseInt(envProps.getProperty("album.topic.partitions")),
                Short.parseShort(envProps.getProperty("album.topic.replication.factor"))));

        topics.add(new NewTopic(
                envProps.getProperty("tracks.purchase.topic.name"),
                Integer.parseInt(envProps.getProperty("tracks.purchase.topic.partitions")),
                Short.parseShort(envProps.getProperty("tracks.purchase.topic.replication.factor"))));

        topics.add(new NewTopic(
                envProps.getProperty("music.interest.topic.name"),
                Integer.parseInt(envProps.getProperty("music.interest.topic.partitions")),
                Short.parseShort(envProps.getProperty("music.interest.topic.replication.factor"))));

        client.createTopics(topics);
        client.close();
    }

    public Properties loadEnvProperties(String fileName) throws IOException {
        final Properties envProps = new Properties();
        final FileInputStream input = new FileInputStream(fileName);
        envProps.load(input);
        input.close();

        return envProps;
    }

    public static void main(String[] args) throws Exception {

        if (args.length < 1) {
            throw new IllegalArgumentException("This program takes one argument: the path to an environment configuration file.");
        }

        final FkJoinTableToTable tableFkJoin = new FkJoinTableToTable();
        final Properties envProps = tableFkJoin.loadEnvProperties(args[0]);
        final Properties streamProps = tableFkJoin.buildStreamsProperties(envProps);
        final Topology topology = tableFkJoin.buildTopology(envProps);

        tableFkJoin.createTopics(envProps);

        final KafkaStreams streams = new KafkaStreams(topology, streamProps);
        final CountDownLatch latch = new CountDownLatch(1);

        // Attach shutdown handler to catch Control-C.
        Runtime.getRuntime().addShutdownHook(new Thread("streams-shutdown-hook") {
            @Override
            public void run() {
                streams.close(Duration.ofSeconds(5));
                latch.countDown();
            }
        });

        try {
            streams.start();
            latch.await();
        } catch (Throwable e) {
            System.exit(1);
        }
        System.exit(0);
    }

}

6
Implement a ValueJoiner class

For the ValueJoiner class, create the following file at src/main/java/io/confluent/developer/MusicInterestJoiner.java.

When you join two tables in a relational database, by default, you get a new table containing all of the columns of the left table plus all of the columns of the right table. In Kafka Streams, when you join two tables, you still get a new table, but you must be explicit about which value(s) are included from both tables.

The ValueJoiner interface in the Streams API does this work. The single apply() method takes the "left" table and the "right" table values as parameters, and returns the value of the joined table as output. (Their keys are not a part of the equation, because they are equal by definition and do not change in the result.) As you can see here, this is just a matter of creating a MusicInterest object and populating it with the relevant fields of the input album and track purchase.

You can do this in a Java Lambda in the call to the join() method where you’re building the stream topology, but the joining logic may become complex, and breaking it off into its own trivially testable class is a good move.

package io.confluent.developer;

import io.confluent.developer.avro.MusicInterest;
import io.confluent.developer.avro.TrackPurchase;
import io.confluent.developer.avro.Album;
import org.apache.kafka.streams.kstream.ValueJoiner;

public class MusicInterestJoiner implements ValueJoiner<TrackPurchase, Album, MusicInterest> {
    public MusicInterest apply(TrackPurchase trackPurchase, Album album) {
        return MusicInterest.newBuilder()
                .setId(album.getId() + "-" + trackPurchase.getId())
                .setGenre(album.getGenre())
                .setArtist(album.getArtist())
                .build();
    }
}

7
Compile and run the Kafka Streams program

In your terminal, run:

./gradlew shadowJar

Now that you have an uberjar for the Kafka Streams application, you can launch it locally. When you run the following, the prompt won’t return, because the application will run until you exit it. There is always another message to process, so streaming applications don’t exit until you force them.

java -jar build/libs/ktable-fkjoins-standalone-0.0.1.jar configuration/dev.properties

8
Load in some movie reference data

In a new terminal, run:

docker exec -i schema-registry /usr/bin/kafka-avro-console-producer --topic albums --broker-list broker:9092\
  --property "parse.key=true"\
  --property 'key.schema={"type":"long"}'\
  --property "key.separator=:"\
  --property value.schema="$(< src/main/avro/album.avsc)"

When the console producer starts, it will log some messages and hang, waiting for your input. Copy and paste one line at a time and press enter to send it. Note that these lines contain a : between the key and the value, so retyping them without the colon will not work.

Each line represents an album that has individual tracks for purchase. To send all of the events below, paste the following into the prompt and press enter:

5:{"id": 5, "title": "Physical Graffiti", "artist": "Led Zeppelin", "genre": "Rock"}
6:{"id": 6, "title": "Highway to Hell",   "artist": "AC/DC", "genre": "Rock"}
7:{"id": 7, "title": "Radio", "artist": "LL Cool J",  "genre": "Hip hop"}
8:{"id": 8, "title": "King of Rock", "artist": "Run-D.M.C", "genre": "Rap rock"}

Before you start producing track purchases, it’s a good idea to set up the consumer on the output topic. This way, as soon as you produce track purchases (and they’re joined to albums creating a music interest trend), you’ll see the results right away. Run this to get ready to consume the music interest trends:

docker exec -it schema-registry /usr/bin/kafka-avro-console-consumer --topic music-interest --bootstrap-server broker:9092 --from-beginning

You won’t see any results until the next step.

10
Produce some track purchases to the input topic

Run the following in a new terminal window. This process is the most fun if you can see this and the previous terminal (which is consuming the music interest results) at the same time. If your terminal program lets you do horizontal split panes, try it that way:

docker exec -i schema-registry /usr/bin/kafka-avro-console-producer --topic purchases --broker-list broker:9092\
  --property "parse.key=true"\
  --property 'key.schema={"type":"long"}'\
  --property "key.separator=:"\
  --property value.schema="$(< src/main/avro/track-purchase.avsc)"

When the producer starts up, copy and paste these lines into the terminal. Then you can observe the results in the consumer terminal:

100:{"id": 100, "album_id": 5, "song_title": "Houses Of The Holy", "price": 0.99}
101:{"id": 101, "album_id": 8, "song_title": "King Of Rock", "price": 0.99}
102:{"id": 102, "album_id": 6, "song_title": "Shot Down In Flames", "price": 0.99}
103:{"id": 103, "album_id": 7, "song_title": "Rock The Bells", "price": 0.99}
104:{"id": 104, "album_id": 8, "song_title": "Can You Rock It Like This", "price": 0.99}
105:{"id": 105, "album_id": 6, "song_title": "Highway To Hell", "price": 0.99}
106:{"id": 106, "album_id": 5, "song_title": "Kashmir", "price": 0.99}

Please note that do to the nature of how a KTable works, you won’t see results simultaneously. You need to wait roughly 30 seconds or so after pasting the lines above to see any results in the consumer terminal.

Speaking of that consumer terminal, these are the results you should see there if you paste in all the albums and track-purchases as shown in this tutorial:

{"id": "5-100", "genre": "Rock", "artist": "Led Zeppelin"}
{"id": "8-101", "genre": "Rap rock", "artist": "Run-D.M.C"}
{"id": "6-102", "genre": "Rock", "artist": "AC/DC"}
{"id": "7-103", "genre": "Hip hop", "artist": "LL Cool J"}
{"id": "8-104", "genre": "Rap rock", "artist": "Run-D.M.C"}
{"id": "6-105", "genre": "Rock", "artist": "AC/DC"}
{"id": "5-106", "genre": "Rock", "artist": "Led Zeppelin"}

You have now joined a table to a table with a foreign key! Well done.

Test it

1
Create a test configuration file

First, create a test file at configuration/test.properties:

application.id=fk-joining-app
bootstrap.servers=localhost:29092
schema.registry.url=mock://fk-join-test

album.topic.name=albums
album.topic.partitions=1
album.topic.replication.factor=1

tracks.purchase.topic.name=purchases
tracks.purchase.topic.partitions=1
tracks.purchase.topic.replication.factor=1

music.interest.topic.name=music-interest
music.interest.topic.partitions=1
music.interest.topic.replication.factor=1

You should take note of the schema.registry.url configuration. The config is using a special pseudo-protocol mock://.. which means our test code doesn’t need to have an actual Schema Registry instance running. The test uses a MockSchemaRegistry instead, specifically for unit testing.

2
Test the MusicInterestJoiner class

Create a directory for the tests to live in:

mkdir -p src/test/java/io/confluent/developer

Create the following file at src/test/java/io/confluent/developer/MusicInterestJoinerTest.java. This tests the helper class that merges the value of the album and the track purchase as each purchase is joined to an album. The class has a dependency on the ValueJoiner interface, but otherwise does not depend on anything external to our domain; it just needs Album, TrackPurchase, and MusicInterest` domain objects. As such, it’s about as testable as code gets:

package io.confluent.developer;

import io.confluent.developer.avro.Album;
import io.confluent.developer.avro.MusicInterest;
import io.confluent.developer.avro.TrackPurchase;
import org.junit.Test;

import static org.junit.Assert.*;

public class MusicInterestJoinerTest {

    @Test
    public void apply() {

        MusicInterest returnedMusicInterest;

        Album theAlbum = Album.newBuilder().setTitle("Album Title").setId(100).setArtist("the artist").setGenre("testing").build();
        TrackPurchase theTrackPurchase = TrackPurchase.newBuilder().setId(5000).setAlbumId(100).setPrice(1.25).setSongTitle("song-title").build();
        MusicInterest expectedMusicInterest = MusicInterest.newBuilder().setArtist("the artist").setId("100-5000").setGenre("testing").build();

        MusicInterestJoiner joiner = new MusicInterestJoiner();
        returnedMusicInterest = joiner.apply(theTrackPurchase, theAlbum);

        assertEquals(returnedMusicInterest, expectedMusicInterest);
    }
}

3
Test the streams topology

Now create the following file at src/test/java/io/confluent/developer/FkJoinTableToTableTest.java. Testing a Kafka streams application requires a bit of test harness code, but happily the org.apache.kafka.streams.TopologyTestDriver class makes this much more pleasant than it would otherwise be.

There is only one method in FkJoinTableToTableTest annotated with @Test, and that is testJoin(). This method actually runs our Streams topology using the TopologyTestDriver and some mocked data that is set up inside the test method.

package io.confluent.developer;

import static org.junit.Assert.assertEquals;

import io.confluent.developer.avro.Album;
import io.confluent.developer.avro.MusicInterest;
import io.confluent.developer.avro.TrackPurchase;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.apache.kafka.common.serialization.Deserializer;
import org.apache.kafka.common.serialization.Serializer;
import org.apache.kafka.common.serialization.StringDeserializer;
import org.apache.kafka.streams.TestInputTopic;
import org.apache.kafka.streams.TestOutputTopic;
import org.apache.kafka.streams.Topology;
import org.apache.kafka.streams.TopologyTestDriver;
import org.junit.Test;

public class FkJoinTableToTableTest {

    private final static String TEST_CONFIG_FILE = "configuration/test.properties";

    @Test
    public void testJoin() throws IOException {
        final FkJoinTableToTable fkJoin = new FkJoinTableToTable();
        final Properties envProps = fkJoin.loadEnvProperties(TEST_CONFIG_FILE);

        final Properties streamProps = fkJoin.buildStreamsProperties(envProps);

        final String albumInputTopic = envProps.getProperty("album.topic.name");
        final String userPurchaseTopic = envProps.getProperty("tracks.purchase.topic.name");
        final String joinedResultOutputTopic = envProps.getProperty("music.interest.topic.name");

        final Topology topology = fkJoin.buildTopology(envProps);
        try (final TopologyTestDriver testDriver = new TopologyTestDriver(topology, streamProps)) {

            final Serializer<Long> keySerializer = FkJoinTableToTable.<Long>getPrimitiveAvroSerde(envProps, true).serializer();
            final Serializer<Album> albumSerializer = FkJoinTableToTable.<Album>getSpecificAvroSerde(envProps).serializer();
            final Serializer<TrackPurchase> trackPurchaseSerializer = FkJoinTableToTable.<TrackPurchase>getSpecificAvroSerde(envProps).serializer();

            final Deserializer<MusicInterest> musicInterestDeserializer = FkJoinTableToTable.<MusicInterest>getSpecificAvroSerde(envProps).deserializer();

            final TestInputTopic<Long, Album>  albumTestInputTopic = testDriver.createInputTopic(albumInputTopic, keySerializer, albumSerializer);
            final TestInputTopic<Long, TrackPurchase> trackPurchaseInputTopic = testDriver.createInputTopic(userPurchaseTopic, keySerializer, trackPurchaseSerializer);
            final TestOutputTopic<String, MusicInterest> outputTopic = testDriver.createOutputTopic(joinedResultOutputTopic, new StringDeserializer(), musicInterestDeserializer);


            final List<Album> albums = new ArrayList<>();
            albums.add(Album.newBuilder().setId(5L).setTitle("Physical Graffiti").setArtist("Led Zeppelin").setGenre("Rock").build());
            albums.add(Album.newBuilder().setId(6L).setTitle("Highway to Hell").setArtist("AC/DC").setGenre("Rock").build());
            albums.add(Album.newBuilder().setId(7L).setTitle("Radio").setArtist("LL Cool J").setGenre("Hip hop").build());
            albums.add(Album.newBuilder().setId(8L).setTitle("King of Rock").setArtist("Run-D.M.C").setGenre("Rap rock").build());

            final List<TrackPurchase> trackPurchases = new ArrayList<>();
            trackPurchases.add(TrackPurchase.newBuilder().setId(100).setAlbumId(5L).setSongTitle("Houses Of The Holy").setPrice(0.99).build());
            trackPurchases.add(TrackPurchase.newBuilder().setId(101).setAlbumId(8L).setSongTitle("King Of Rock").setPrice(0.99).build());
            trackPurchases.add(TrackPurchase.newBuilder().setId(102).setAlbumId(6L).setSongTitle("Shot Down In Flames").setPrice(0.99).build());
            trackPurchases.add(TrackPurchase.newBuilder().setId(103).setAlbumId(7L).setSongTitle("Rock The Bells").setPrice(0.99).build());
            trackPurchases.add(TrackPurchase.newBuilder().setId(104).setAlbumId(8L).setSongTitle("Can You Rock It Like This").setPrice(0.99).build());
            trackPurchases.add(TrackPurchase.newBuilder().setId(105).setAlbumId(6L).setSongTitle("Highway To Hell").setPrice(0.99).build());
            trackPurchases.add(TrackPurchase.newBuilder().setId(106).setAlbumId(5L).setSongTitle("Kashmir").setPrice(0.99).build());

            final List<MusicInterest> expectedMusicInterestJoinResults = new ArrayList<>();
            expectedMusicInterestJoinResults.add(MusicInterest.newBuilder().setId("5-100").setGenre("Rock").setArtist("Led Zeppelin").build());
            expectedMusicInterestJoinResults.add(MusicInterest.newBuilder().setId("8-101").setGenre("Rap rock").setArtist("Run-D.M.C").build());
            expectedMusicInterestJoinResults.add(MusicInterest.newBuilder().setId("6-102").setGenre("Rock").setArtist("AC/DC").build());
            expectedMusicInterestJoinResults.add(MusicInterest.newBuilder().setId("7-103").setGenre("Hip hop").setArtist("LL Cool J").build());
            expectedMusicInterestJoinResults.add(MusicInterest.newBuilder().setId("8-104").setGenre("Rap rock").setArtist("Run-D.M.C").build());
            expectedMusicInterestJoinResults.add(MusicInterest.newBuilder().setId("6-105").setGenre("Rock").setArtist("AC/DC").build());
            expectedMusicInterestJoinResults.add(MusicInterest.newBuilder().setId("5-106").setGenre("Rock").setArtist("Led Zeppelin").build());

            for (final Album album : albums) {
                albumTestInputTopic.pipeInput(album.getId(), album);
            }

            for (final TrackPurchase trackPurchase : trackPurchases) {
                trackPurchaseInputTopic.pipeInput(trackPurchase.getId(), trackPurchase);
            }

            final List<MusicInterest> actualJoinResults = outputTopic.readValuesToList();

            assertEquals(expectedMusicInterestJoinResults, actualJoinResults);
        }
    }
}

4
Invoke the tests

Now run the test, which is as simple as:

./gradlew test

Take it to production

1
Create a production configuration file

First, create a new configuration file at configuration/prod.properties with the following content. Be sure to fill in the addresses of your production hosts and change any other parameters that make sense for your setup.

application.id=fk-joining-app
bootstrap.servers=<<FILL ME IN>>
schema.registry.url=<<FILL ME IN>>

album.topic.name=<<FILL ME IN>>
album.topic.partitions=<<FILL ME IN>>
album.topic.replication.factor=<<FILL ME IN>>

tracks.purchase.topic.name=<<FILL ME IN>>
tracks.purchase.topic.partitions=<<FILL ME IN>>
tracks.purchase.topic.replication.factor=<<FILL ME IN>>

music.interest.topic.name=<<FILL ME IN>>
music.interest.topic.partitions=<<FILL ME IN>>
music.interest.topic.replication.factor=<<FILL ME IN>>

2
Build a Docker image

In your terminal, execute the following to invoke the Jib plugin to build an image:

gradle jibDockerBuild --image=io.confluent.developer/ktable-fkjoins:0.0.1

3
Launch the container

Finally, launch the container using your preferred container orchestration service. If you want to run it locally, you can execute the following:

docker run -v $PWD/configuration/prod.properties:/config.properties io.confluent.developer/ktable-fkjoins:0.0.1 config.properties