Getting started with SQL Stream Builder

As a first step to use SQL Stream Builder, learn how to create a project with git synchronization where you can create and manage jobs, resources and experience the SDLC of SSB applications.

After accessing Streaming SQL Console using http://localhost:18121 from your browser and signing in, you are redirected to the Projects page of Streaming SQL Console.

As an example, you can create a transaction_monitoring project using the Community Edition in your local environment, set up the resources such as tables, data sources and jobs, and then migrate the project using a Git repository to a CDP cluster or environment.

When you access the Streaming SQL Console, there are already existing projects on the My Projects page. The ssb_default and [***USERNAME***]_default projects are automatically generated projects. The ssb_default project and its resources are visible to every user, while the [***USERNAME***]_default project and its resources are only visible to that user. Every user has its own project generated by default where members can be invited.

To create a project in Streaming SQL Console, you need to click on New Project on the Projects page and provide the Name of the project, add a description if necessary and set up the dedicated Git repository for synchronization. For example, you can name the project transaction_monitoring, add Monitoring orders and transactions as description, and use a dedicated Git repository with a main branch.

Click Create, and click Switch > Switch Project to be redirected to the Explorer view of the created project.

Within the created project, you can use the tree structure on the left side to review and manage the resources in a project. You can also use the home page of the project on the right side, and manage the resources of the project in a tab view.

When you created your project and set up Git for version control, learn how to create a job and a table that generates random data using the included Faker connector after accessing the Streaming SQL Console.

You can easily create a new job using the Explorer view of your project by clicking on the button next to Jobs and select New Job.

As a first step in executing a SQL query, you need to create a table. At this point, you can use the predefined templates under the SQL Editor.

In the following CREATE TABLE statement, the faker template is used, with the properties already filled out. You can copy and paste it to the SQL Editor.

DROP TABLE IF EXISTS orders;
CREATE TABLE  orders (
    order_id INTEGER,
    city STRING,
    street_address STRING,
    amount INTEGER,
    order_time TIMESTAMP(3),
    order_status STRING,
    WATERMARK FOR `order_time` AS order_time - INTERVAL '15' SECOND
) WITH (
    'connector' = 'faker',
    'rows-per-second' = '1',
    'fields.order_id.expression' = '#{number.numberBetween ''0'',''99999999''}',
    'fields.city.expression' = '#{Address.city}',
    'fields.street_address.expression' = '#{Address.street_address}',
    'fields.amount.expression' = '#{number.numberBetween ''0'',''100''}',
    'fields.order_time.expression' = '#{date.past ''15'',''SECONDS''}',
    'fields.order_status.expression' = '#{regexify ''(RECEIVED|PREPARING|DELIVERING|DELIVERED|CANCELED){1}''}'
);

To create the table, you need to click on the Execute button under the SQL Editor.

Optional: Synchronizing your changes to Git

Now that you have created a job and a table, you can push your changes to your Git repository using the Source Control view and selecting the Push tab. Provide a commit message that describes the changes, and click Push to synchronize your changes to Git.

If someone else would work on this project in their local SSB Community Edition instance or SSB on a CDP cluster, they can use the Import button to pull the changes and have the latest version of the project in their SSB.

After creating a table, learn how to run a SELECT query that outputs a sample of the resulting data in Streaming SQL Console.

You have created your table using the Faker template that currently generates data. As a next step, you can run a SELECT query and output the results at the SQL Editor under the Results tab.

SELECT * FROM orders;

To execute the query, you need to click on the Execute button under the SQL Editor.

After a short amount of time, the samples of the result continuously show up under the Results tab.

When you execute a query, a Flink job is created in the background. You can see more details about the created Flink job by clicking on the Flink Dashboard button for the running job. You can also reload the SQL job into the SQL Editor to edit the job, or stop the job from the Jobs tab that lists all of the running and finished jobs in a project.

Now that you know the basics of creating a table and querying data from a table, learn how to create a more complex query using views.

In the following example, you will use the columns from the created Faker table. In this derived computation that can be saved as a view, the orders are aggregated in half minute intervals based on their status.

Copy and paste the following CREATE VIEW statement to the SQL Editor:

DROP VIEW IF EXISTS summaries;
CREATE VIEW summaries AS
SELECT window_start, window_end, order_status, COUNT(*) as order_count, SUM(amount) as total_amount
FROM TABLE(TUMBLE(TABLE orders, DESCRIPTOR(order_time), INTERVAL '20' SECONDS))
GROUP BY window_start, window_end, order_status;

If your previous query is still running, click on the Stop button under the SQL Editor to stop the job. Afterwards, to create the view, you need to click on the Execute button under the SQL Editor.

When you execute a query, not all the records produced by the query are shown in the Results window. By default, 1 record is sampled every second to be displayed. Before selecting data from this view, you can change the Sample Behavior to Sample all messages. You need to click on the Job Settings button to open the settings that can be updated for a job. This way the Results window will contain all messages, up to the specified count.

SELECT * FROM summaries

To execute the query, you need to click on the Execute button under the SQL Editor.

After a short amount of time, the samples of the aggregated result continuously show up under the Results tab. As it is an aggregated view, the results can appear slower after another.

As you already know how to query tables and views, the next step is to write the queried results into a sink or create a Materialized View.