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.
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.
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
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
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
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
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:
There are three principal issues that we’ll be doing on this tutorial:
- First we’ll add Materialize as an information supply to LogicLoop.
- Then we’ll write an SQL rule that can verify our
vipusersbadreviewsmaterialized view which accommodates the unhealthy critiques left by VIP customers.
- 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.
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
- 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
materializeas that is the default one.
Lastly, you may click on the “Create” button to create the info supply.
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:
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.
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:
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;
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:
Let’s replace the rule to appear like this:
SELECT * FROM analytics.vipusersbadreviews LIMIT 10;
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.
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:
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:
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:
For extra data on the above steps, please confer with the documentation:
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.
If in case you have any questions or feedback, please be a part of the Materialize Slack Community!