Using LogicLoop and Materialize with dbt and Redpanda/Kafka



Introduction

LogicLoop lets you write guidelines in SQL after which run them in opposition to your knowledge and set off completely different actions based mostly on the outcomes. LogicLoop additionally lets you create and share dashboards and visualizations simply through their net interface.

Materialize is a source-available streaming database that takes knowledge coming from sources like Kafka/Redpanda, Postgres, S3, and extra, and permits customers to jot down views that mixture knowledge in your occasion stream. The magic is that the views are translated to dataflows which permits Materialize to take care of the views incrementally in real-time. A traditional materialized view would do a full scan of the info each time it must be up to date, Materialize solely does the work to take care of the view based mostly on occasions that are available in, so it’s a lot sooner and extra environment friendly.

On this tutorial, we’ll stroll by way of methods to use LogicLoop with Materialize.



Stipulations

Ensure to join a LogicLoop account first.

Additionally, for this tutorial, we’ll lengthen upon the earlier article on how to use dbt with Materialize and Redpanda.

The structure of the earlier article is as follows:

If you wish to comply with alongside, be sure that to learn the earlier article first and have the undertaking up and operating in your server.

LogicLoop additionally works with Materialize Cloud.



Beginning the demo undertaking

A fast abstract of the steps from the “how to use dbt with Materialize and Redpanda” tutorial that you should take to get the undertaking up and operating are as follows:

# Clone the repository:
git clone https://github.com/bobbyiliev/materialize-tutorials.git

# Entry the listing:
cd materialize-tutorials/mz-user-reviews-dbt-demo

# Begin by operating the Redpanda container:
docker-compose up -d redpanda

# Construct the photographs:
docker-compose construct

# Then pull the entire different Docker photos:
docker-compose pull

# Lastly, begin the entire companies:
docker-compose up -d
Enter fullscreen mode

Exit fullscreen mode

As soon as all of the companies are operating, you may run the next instructions to configure the dbt half:

# Set up dbt:
pip3 set up dbt-core==1.1.0
pip3 set up dbt-materialize==1.1.0
Enter fullscreen mode

Exit fullscreen mode

After that, together with your favourite textual content editor, open the ~/.dbt/undertaking.yml file and add the next traces:

user_reviews:
  outputs:
    dev:
      kind: materialize
      threads: 1
      host: localhost
      port: 6875
      person: materialize
      go: go
      dbname: materialize
      schema: analytics

  goal: dev
Enter fullscreen mode

Exit fullscreen mode

Lastly, we will use dbt to create materialized views on prime of the three Redpanda/Kafka matters. To take action simply run the next dbt command:

dbt debug
dbt run
dbt check
Enter fullscreen mode

Exit fullscreen mode

With that, all of the materialized views are created and we will begin utilizing them in our LogicLoop account.

For extra particulars on the above steps, please confer with the earlier article:

How to use dbt with Materialize and Redpanda



Overview

There are three principal issues that we’ll be doing on this tutorial:

  1. First we’ll add Materialize as an information supply to LogicLoop.
  2. Then we’ll write an SQL rule that can verify our vipusersbadreviews materialized view which accommodates the unhealthy critiques left by VIP customers.
  3. Subsequent we’ll create an motion vacation spot in order that we will get a notification when the rule is triggered. That approach we will keep on prime of the unhealthy critiques and make it possible for our VIP customers are taken care of.

Due to LogicLoop we will have all this with out writing any customized or integrations. And because of Materialize we will get the info we want in real-time.



Add Materialize as a supply to LogicLoop

Begin by logging into LogicLoop and navigating to the “Knowledge Sources” web page and clicking on the “New Knowledge Supply” button.

Subsequent, as Materialize is wire-compatible with Postgres, you should use LogicLoop’s Postgres driver to connect with your Materialize occasion.

After selecting the Postgres driver, you will have to enter the next data:

  • Identify: A descriptive identify for the info supply, e.g. “My Materialize occasion”.
  • Host: The hostname of your Materialize occasion.
  • Port: The port that your Materialize occasion is listening on. Normally 6875.
  • Person: The username you utilize to connect with your Materialize occasion.
  • Password: The password you utilize to connect with your Materialize occasion.
  • Database Identify: set this to materialize as that is the default one.

Lastly, you may click on the “Create” button to create the info supply.



Add an motion vacation spot

LogicLoop has a listing of built-in motion locations that you should use like Slack, Webhooks, Electronic mail, and extra.

This lets you ship completely different sorts of notifications based mostly on the rule that’s triggered.

So as to add a brand new motion vacation spot, navigate to the “Locations” web page and click on on the “New Motion Vacation spot” button.

In there you may select the kind of motion vacation spot you wish to create:

LogicLoop destinations

For the sake of simplicity, for this demo, let’s create an e-mail motion vacation spot and set it to our e-mail handle. Nonetheless, you may as well ship emails to your end-facing clients as described within the documentation here.

After you have created the motion vacation spot, you may click on on the “Save” button.



Create a rule

As soon as the info supply is created, you may create a rule that can verify the vipusersbadreviews materialized view.

To take action, navigate to the “Guidelines” web page and click on on the “New Rule” button.

From the dropdown menu, choose your Materialize knowledge supply:

Materialize data source

It is possible for you to to see all of the views which might be obtainable in your Materialize occasion, which we created within the earlier article.

Within the SQL editor, you may write queries that might be run in opposition to the materialized views:

SELECT COUNT(*) FROM analytics.vipusersbadreviews;
Enter fullscreen mode

Exit fullscreen mode

Based mostly on the question, we can see what number of unhealthy critiques there are for the VIP customers.

We are able to additionally generate completely different visualizations for the info:

Total bad reviews counter

Let’s replace the rule to appear like this:

SELECT * FROM analytics.vipusersbadreviews LIMIT 10;
Enter fullscreen mode

Exit fullscreen mode

This may return the final 10 unhealthy critiques that we’ve for the VIP customers. Be happy to vary the question to your liking and edit the visualization as nicely.

Lastly, click on on the “Save” button to create the rule.

Subsequent, let’s create an motion in order that we will get a notification when the rule is triggered.



Add an motion

As soon as the rule is created, you may add an motion to the rule. Whereas on the “Guidelines” web page, click on on the “Add Motion” button on the backside of the web page.

The motion could be based mostly on completely different sorts of situations as follows:

Action conditions

Configure the motion based mostly in your wants and click on on the “Save” button.

Subsequent, allow the vacation spot that you simply created earlier in order that when the rule is triggered, you’re going to get a notification.

Lastly, click on on the “Run” button to run the motion:

Run an action

Alternatively, you may as well set the rule to run mechanically based mostly on a particular time interval. The intervals supported by LogicLoop are: Minutes (1-30), Hours, Days, Weeks.

When you run the rule, you’re going to get a notification for every time the rule is matched:

Notification

For extra data on the above steps, please confer with the documentation:

Actions documentation



Conclusion

That is it! Now you can use the materialized views in your LogicLoop account. This can be a nice solution to get real-time knowledge visualizations and notifications for what you are promoting.

As a subsequent step you may look into utilizing temporal filters in order that your Materialize views are solely preserving the info for a particular time interval somewhat than the whole knowledge set. And in addition run your LogicLoop guidelines on a schedule of 1-minute intervals to get the newest knowledge from Materialize.



Helpful hyperlinks



Neighborhood

If in case you have any questions or feedback, please be a part of the Materialize Slack Community!

Add a Comment

Your email address will not be published. Required fields are marked *