Last Updated: 2024-08-28

Background

Starburst is a powerful data lake analytics platform that lets you discover, govern, and query your data whether it resides in the lake or not. The new streaming ingest feature in Starburst Galaxy unlocks your data from behind slow, complex pipeline configurations, providing a simple user experience for setting up a streaming pipeline.

Today, more than ever, organizations know it is critical to be able to use all their data when making critical business decisions. All too often data is left out of the equation due to the complexity of harnessing it. Data engineers know that setting up and maintaining a streaming pipeline is not only complicated but brittle due to all the components that must be strung together. This problem is exacerbated by the fact that more and more streaming sources are coming online every day.

With this issue in mind, our team at Starburst knew that it would be essential to add a streaming ingest feature to our platform. In fact, it is considered one of the core pillars of an Icehouse architecture, as outlined in the Icehouse Manifesto.

By leveraging Starburst Galaxy as your data platform for streaming ingest, you give your data engineers the tool they need to ensure no data is left behind!

Scope of tutorial

In this tutorial, you will take on the role of a Data Engineer to build out a streaming ingest pipeline.

You will complete the following steps:

1. Use Confluent Cloud to configure a Kafka topic and connector to generate some fictional stock trading data.
2. Configure a connection to the topic in Galaxy.
3. Configure a live table to stream your data into.
4. Complete a simple SQL statement on the live table data.

Prerequisites

• You need a Starburst Galaxy account to complete this tutorial. Please see Starburst Galaxy: Getting started for instructions on setting up a free account.
• You need an AWS S3 catalog in Starburst Galaxy that connects to an S3 bucket location for which you have read and write access. This catalog must be configured with a cross account IAM role and must be in the us-east-1 region.
• You need a Confluent Cloud account to complete this tutorial. If you don't have an account, you can take advantage of their free, 30-day trial. Please note that Confluent Cloud requires you to input your payment information before you can create anything.

Learning outcomes

Once you've completed this tutorial, you will be able to:

• Use Confluent Cloud to configure a cluster, topic, and connector.
• Configure streaming ingest in Starburst Galaxy.

About Starburst tutorials

Starburst tutorials are designed to get you up and running quickly by providing bite-sized, hands-on educational resources. Each tutorial explores a single feature or topic through a series of guided, step-by-step instructions.

As you navigate through the tutorial you should follow along using your own Starburst Galaxy account. This will help consolidate the learning process by mixing theory and practice.

Background

You'll begin your work in Confluent Cloud by setting up a cluster. As written in the Confluent documentation, "A Kafka cluster is a group of interconnected Kafka brokers that manage and distribute real-time data streaming, processing, and storage as if they are a single system." A basic cluster will be sufficient for use in this tutorial.

Step 1: Add cluster

When you log in to Confluent Cloud, you should be on the home page, which has a shortcut to create a cluster.

• Log in to your Confluent Cloud account.
• Select the Add Cluster button.
• Select Begin configuration on the Basic tile.

Step 2: Select region

For this tutorial, you must use AWS as the cloud provider and N. Virginia (us-east-1) as the region.

• Select the aws tile.
• From the Region dropdown menu, select N. Virginia (us-east-1).
• Click the Continue button.

Step 3: Provide payment information (if needed)

If this is your first time using Confluent Cloud, you'll be asked to provide payment information to be used after your free credits expire. The account used for this tutorial was given the option to bypass this step, but that may not be the case for your account.

• Enter your payment information, or select the Skip payment option.

Step 4: Review and launch

• Provide a meaningful Cluster name (for example, Galaxy).
• Click the Launch cluster button.

Background

A Kafka topic is a user-defined category that acts as a repository for event messages. Topics allow producers to send data and consumers to read data from them.

In this section, you will create a topic that will later be used by Galaxy to continuously read messages from and write the parsed messages into an Iceberg table.

Step 1: Create new topic

At the end of the previous section, you should have been directed to your new cluster. We'll start from there.

• From the left-hand navigation menu, select Topics.
• Click the Create topic button.

Step 2: Provide name and partitions

• Under Topic name, type stock_trades. You are free to choose any name you'd like, but we recommend using this name to make it easier to follow along in the instructions.
• Under Partitions, type 1. The default number of partitions is 6, which is far more than what we will need for this simple tutorial. Additionally, creating more partitions than needed will create unnecessary infrastructure costs in our Galaxy demo environment.
• Click the Create with defaults button.

Step 3: Skip schema creation

You're going to skip the next section as there is no need to create a schema for this tutorial. The streaming ingestion feature does not currently support schema registry. If you accidentally create a schema in Confluent, the streaming ingestion feature will not work.

• Click the Skip link at the bottom of the Define a data contract tile.

Background

A Kafka Connector is a component of Kafka Connect, a framework in Apache Kafka that enables the integration of Kafka with external systems. Kafka Connect simplifies the process of streaming data between Kafka topics and other data systems, such as databases, message queues, or file systems, without the need for custom code.

In this section, you will create a connector that generates synthetic data to publish to the Kafka topic you just created. This synthetic data is what will be ingested into the Iceberg table as part of the streaming ingestion you set up later in Starburst Galaxy.

Step 1: Navigate to Connector plugins

• From the left-hand navigation menu, select Connectors.
• Select Sample data.

Step 2: Select the topic

In this step, you'll select the topic you just created as part of your connector configuration.

• Click Additional configuration. Do not click Launch.
• Select the stock_trades topic.
• Click Continue.

Step 3: Create a Service Account

You're now ready to create a Service Account that your connector will use to securely communicate with your Kafka cluster.

• Select the Service account tile.
• Provide a meaningful Service account name (ex. galaxy).
• Provide a meaningful Description.
• Check the box in front of Add all required ACLs (4), inherited from the current cluster and selected topics, to this service account.
• Click Continue.

Step 4: Configure connector

In this step, you'll select the desired output record value format and schema.

• For output record value format, select JSON.
• For schema, select Stock trades.
• Click Continue.

Step 5: Review connector sizing

• Review the information on the screen.
• Notice this connector will consume $0.035/hr. That will mean roughly $25.55 per month (730 * $0.035 = $25.55) from your $400 of free credits, if you are using a free trial account.
• Click Continue.

Step 6: Complete connector configuration

• Provide a meaningful Connector name (ex. sample_data_stock_trades).
• Click Show advanced configurations.
• Scroll down and notice the details under advanced configurations.
• Scroll down further and see the JSON configuration for the connector. Also notice at the very bottom both the Task and Usage fees are listed.
• Click Continue.
• The connector will move from Provisioning to Running within a few minutes.

Background

In this section, you will create an API key that Starburst Galaxy can use to authenticate to your Kafka cluster. When you configure streaming ingestion later in this tutorial, you will input this API key as part of the configuration

Step 1: Create new key

• Using the left-hand navigation menu, select API keys.
• Click the Create key button.

Step 2: Select service account

The API key you create will be specifically for the service account you created in the last section of this tutorial.

• Select Service account.
• Click Next.
• From the dropdown menu, select the service account you created earlier.
• Click Next.
• Scroll to the bottom of the ​​Add ACLs to service account page, and click Next.

Step 3: Provide description and copy key

It's very important to provide a meaningful description so that you know the purpose of the key. In addition, it is important to copy and save the key and secret in a safe place, as this will be your only chance to do so.

• Provide a meaningful Description for your key. (ex. Key for Galaxy lesson created on 8.24.2024)
• Copy both the key and secret, and save them in a safe place.
• Click Download and continue. This will download a text file with the key and secret. If this were a production environment, it would be advisable to delete the file or move it to a credential vault.
• Open the file that was downloaded, and review its contents. Notice the file not only contains the API key and secret but also the Bootstrap server settings.
• We suggest you keep the file open, as it will provide an easy place to retrieve the information you'll need next when you configure streaming ingest in Starburst Galaxy.

Background

You're almost finished with the Kafka configuration. You just need to add a few more permissions to your topic to allow Starburst Galaxy to interact with it.

Step 1: Select API key

• Click the link for the API key you just created.
• Select the Access tab.
• Select the Topic tab.
• Select stock_trades from the list of topics.

Step 2: Add first topic ACL

• Click the Edit topics ACLs button.
• Scroll down to the bottom of the existing ACLs, and click the Add ACLs link.
• For Topic name, select stock_trades.
• For Pattern type, select LITERAL.
• For Operation, select DESCRIBE.
• For Permission, select ALLOW.

Step 3: Add second topic ACL

• Click the Add ACLs link again.
• For Topic name, select stock_trades.
• For Pattern type, select LITERAL.
• For Operation, select READ.
• For Permission, select ALLOW.
• Click the Save button.

Step 4: Review cluster settings

This step is just to show you where you can go for Bootstrap server information in the future.

• Using the left-hand navigation menu, select Cluster Settings.
• Review the information in the Endpoints section, taking note of the Bootstrap server.

Background

It's time to switch over to Starburst Galaxy, where you'll remain for the rest of this tutorial. In this section, you'll begin by creating a schema to hold your ingest tables. Then, you'll connect to the Kafka topic you created earlier and create a live table for the data.

Step 1: Create a schema

When you create your live table, you'll be required to select a catalog and schema for the table. You should already have the catalog set up. This step will show you how to create a schema within that catalog.

• Log in to your Starburst Galaxy account.
• Click to expand the left-hand navigation menu, and select Query>>Query editor.
• Run the following SQL to create the schema. Be sure to replace with your actual catalog name.

CREATE SCHEMA <your-catalog>.streams;

Step 2: Begin data ingest connection

• From the left-hand navigation menu, select Data>>Data ingest.
• Click the Connect new source button.

Step 3: Select stream source

Note that you have three options. More connection options are planned in future Starburst Galaxy updates.

• Select the Confluent Cloud tile.
• Click Next.

Step 4: Add source details

It's time to input the API key you created in Confluent Cloud. Make sure to have it handy.

• Provide a meaningful Source name (ex. confluent_cloud_galaxy_cluster).
• Provide a meaningful Description.
• Under Kafka brokers, enter the Bootstrap server and port. (Recall that this information can be found in Confluent Cloud or in the API key document that you downloaded earlier in this tutorial.)
• Select the radio button for Username/password.
• Paste your API key and API secret in the corresponding boxes.
• Click the Test connection button.
• Confirm you receive the message Hooray! Able to successfully connect.
• Click Save new source.
• Click Yes, select topic.

Step 5: Create live table

Now you're going to connect to the stock_trades topic you created in Confluent Cloud to continuously ingest the data into a managed Iceberg table.

• Use the Catalog dropdown menu to select your catalog. Recall that this must be the catalog you chose for the schema you created earlier.
• Use the Schema dropdown menu to select your schema.
• Provide a meaningful Table name. We suggest you use the topic name (ex. stock_trades).
• Use the Topic name dropdown menu to select your Topic (ex. stock_trades). This dropdown will automatically populate the topics available from your Confluent Cloud connection.
• Scroll down and click Test connection.
• Confirm you receive the message Hooray! Able to successfully connect.
• Click Create target.

Step 6: Map to columns

Starburst Galaxy automatically suggests a table schema by inferring from the Kafka messages on the topic. You have the option to edit these, if you'd like. For this tutorial, you can leave them as they are.

• Click Map stream to columns.
• Your live table is now starting!

Background

Now that you've completed your streaming ingest setup, it's time to query the data! Note that it can take a few minutes before the data initially lands. New data will be continuously written to the table every 1-2 minutes.

Step 1: Explore new table and view

It's time to head back to the Query editor. We'll begin by expanding the streams schema to see not just a new table but a view as well.

• Navigate to the Query editor.
• The catalog explorer is on the left side of the Query editor. Expand the catalog you are using for streaming ingest and then expand the schema that you created for this tutorial.
• You should see a table and a view listed under the schema. The view is denoted by an eye icon. In our example, the table is called stock_trades_raw, while the view is called stock_trades.

Step 2: Learn about the raw table

The raw table includes the parsed message along with additional metadata information. This metadata information is useful for debugging either by the customer or Starburst support.

The following is a breakdown of the raw table columns and their descriptions:

$system: A unique ID for each row plus the ingested timestamp.

raw_key: The Kafka key in varbinary.

raw_value: The message value in varbinary.

headers: Kafka events can have multiple header fields in them.

timestamp: Timestamp for the Kafka record. (different from the ingested timestamp in the $system column). This can be empty.

timestamp_type: Denotes the type above the timestamp. Options could be no timestamp, create_time, or log_append_time.

leader_epoch: Kafka offset is a combination of the actual offset number and the leader epoch field.

topic: The topic name.

partition: The partition index the message came from.

offset: The Kafka offset the message came from.

parsed: The parsed message (this is the row type and is expanded into the view).

Step 3: Learn about the view

The view is the table name you chose during the configuration. It is a view on top of the raw table to hide the metadata information. In the future, this will be an actual table instead of a view.

The following is a breakdown of the view columns:

$system: A unique ID for each row plus the ingested timestamp.

The remaining columns come from the parse messages. They are derived from the parsed column in the raw table.

Step 4: Query the view

Now it's time to see how easy it is to query the ingested data.

• Click the ellipses next to the view, and select the menu option to SELECT * FROM the view with LIMIT 10.
• Run the query.

Step 5: Explore dead letter table

The streaming ingest feature also includes a table that is used as a dead letter table. This is where messages that cannot be parsed using the table schema are written. You can query this table like any other table, and it can be used to understand why the messages could not be ingested. The table is of the format __raw$errors and must be encapsulated in quotations when querying. The messages in this tutorial should always be parsed so you won't find any data in this table, but in a real production scenario, bad messages can make their way onto a topic and end up in this table.

The following is a breakdown of the dead letter table columns and their descriptions:

$system: A unique ID for each row plus the ingested timestamp.

error: The error message.

topic: The topic name.

partition: The partition index the message came from.

offset: The Kafka offset the message came from.

timestamp: Timestamp for the Kafka record. (different from the ingested timestamp in the $system column). This can be empty.

timestamp_type: Denotes the type above the timestamp. Options could be no timestamp, create_time, or log_append_time.

leader_epoch: Kafka offset is a combination of the actual offset number and the leader epoch field.

Tutorial complete

Congratulations! You have reached the end of this tutorial, and the end of this stage of your journey.

Now that you've completed this tutorial, you should have a better understanding of just how easy it is to use streaming ingest in Starburst Galaxy. Stay tuned for more tutorials using this feature!

Continuous learning

At Starburst, we believe in continuous learning. This tutorial provides the foundation for further training available on this platform, and you can return to it as many times as you like. Future tutorials will make use of the concepts used here.

Next steps

Starburst has lots of other tutorials to help you get up and running quickly. Each one breaks down an individual problem and guides you to a solution using a step-by-step approach to learning.

Tutorials available

Visit the Tutorials section to view the full list of tutorials and keep moving forward on your journey!