I changed the nature of my personal sprint objective, moving away slightly from AI concerns to reconnect with more general considerations about Data and its processing: from collection to its visualization through its “processing”. Ultimately, of course, this treatment could be injected with AI or ML. As always, when we start thinking about such a vast subject, the first question is what do we want to land on to define the scope? In this case, I wondered how to approach the question of data.<\/p>\n
So, according to me and after compiling several resources, finally the two things that seem most important to me for now:<\/p>\n
For this post, you can find all files for each project on my GitHub account. See https:\/\/github.com\/bflaven\/BlogArticlesExamples\/tree\/master\/how_to_use_superset_kafka_agile2<\/a><\/b><\/p>\n Like always, I read some stuff that I found inspiring and note everything down. Sometimes, I found some consolations and some advice that act like mantras when you start a POC to avoid the WIP’s hell.<\/p>\n Learning is an active process. We learn by doing. Only knowledge that is used sticks in your mind. – Dale Carnegie<\/p><\/blockquote>\n For records, some good stuff from Agile2<\/b><\/p>\n Apparently, Agile is dead long live to Agile2.<\/p>\n I just read this article: https:\/\/medium.com\/developer-rants\/agile-has-failed-officially-8136b0522c49<\/a><\/p>\n Funny, this reminds me of several things: A good reminder to meditate. <\/b><\/p>\n At some point, a project must produce a final product. <\/p><\/blockquote>\n Some other excerpts from the Agile2 core values on some specific aspects for project management. <\/b><\/p>\n (i) Planning, Transition & Transformation<\/b><\/p>\n (ii) Product, Portfolio & Stakeholders<\/b><\/p>\n (iii) Continuous Improvement<\/b><\/p>\n Anyway, let’s get back to the main course. I was about to make a complete benchmark among the market solutions: Superset, Redash and even ClickHouse. But, finally Superset is enough for my product discovery.<\/p>\n I was looking for some ideas on how to improve data analytics flow. Indeed, it is always useful “to set up a system that allows you to get a deeper understanding of the behaviour of your customers”.<\/p>\n Source: https:\/\/xebia.com\/blog\/real-time-analytics-divolte-kafka-druid-superset\/<\/a><\/p>\n Having an alternative pipeline that could be called “real-time analytics platform” can help to better perform:<\/p>\n Descriptive analysis:<\/b> either the act of analyzing the data and describing what they say at a given time;<\/p>\n Predictive analysis:<\/b> predicting a potential result based on data extracted from past or current activities.<\/p>\n Source: https:\/\/betterprogramming.pub\/building-an-order-delivery-analytics-application-with-fastapi-kafka-apache-pinot-and-dash-part-ca276a3ee631<\/a><\/p>\n Based on these 2 posts, I decide to go for :<\/p>\n Superset is the most intuitive tool that I found to improve visualisation. Here is a quick way to install and manage Superset with Docker.<\/p>\n You need to have some databases e.g. On mac, the best way to do so: install databases, it is to use homebrew. Here are the commands to install databases.<\/p>\n To Install Homebrew if you haven’t already: https:\/\/brew.sh\/<\/p>\n 2.1 MARIADB<\/b> Advantages of MariaDB:<\/b><\/p>\n 2.2 POSTGRES<\/b><\/p>\n PostgreSQL (Postgres) is a powerful, open-source object-relational Advantages of PostgreSQL:<\/b><\/p>\n This time, for this POC, I did not install Customization with Here are some key purposes of Kafka within an analytics application:<\/p>\n Source: https:\/\/kafka.apache.org\/<\/a><\/i><\/p>\n We are going to take the most straightforward way on mac, meaning using homebrew like we did previously for database. The commands will almost the same as we will use Homebrew.<\/b><\/p>\n Again, if you need to install homebrew, type in the console, the following command.<\/p>\n To install kafka with Homebrew<\/b><\/p>\n To list the services from Homebrew<\/b><\/p>\n If you’ve installed Kafka and Zookeeper using Homebrew on your macOS system, you can use the following commands to interact with Kafka and gain an understanding of its general principles.<\/b><\/p>\n 1. Start Zookeeper:<\/b> 2. Start Kafka Server:<\/b><\/p>\n Now, start the Kafka server using Homebrew:<\/p>\n This command will start the Kafka server as a background service.<\/p>\n 3. Create a Topic:<\/b><\/p>\n Kafka organizes data into topics. Create a Kafka topic to publish and subscribe messages:<\/p>\n Replace `brunotopic1` with the desired topic name.<\/p>\n 4. List Topics:<\/b><\/p>\n List the existing Kafka topics:<\/p>\n 5. Produce Messages:<\/b><\/p>\n Produce some messages to the topic:<\/p>\n This command opens a console where you can type messages. Press `Ctrl + D` to exit.<\/p>\n 6. Consume Messages:<\/b><\/p>\n Open a new terminal and consume messages from the topic:<\/p>\n This command subscribes to the topic and prints incoming messages.<\/p>\n 7. Describe a Topic:<\/b><\/p>\n Describe the properties of a Kafka topic:<\/p>\n 8. Kafka Commands Documentation:<\/b><\/p>\n Explore additional Kafka commands and options by checking the official documentation:<\/p>\n 9. Stop Kafka:<\/b><\/p>\n When you’re done, you can stop the Kafka server:<\/p>\n This will stop the Kafka server running as a background service.<\/p>\n These commands provide a basic overview of Kafka’s functionalities. You can experiment further and refer to the [official documentation](https:\/\/kafka.apache.org\/documentation\/) for more in-depth understanding and configuration options.<\/p>\n Using faststream<\/b><\/p>\n To go further, you can leverage on FastStream<\/b>. A kind of FastAPI<\/b> for Kafka. Indeed, FastStream simplifies the process of writing producers and consumers for message queues, handling all the parsing, networking and documentation generation automatically.<\/p>\n Source: https:\/\/faststream.airt.ai\/latest\/faststream\/<\/a><\/i><\/p>\nKey-ideas that should drives a POC<\/h2>\n
\nLike any belief or ideology, it is beneficial to question\/criticize any “dominant” model of thought. Quite provocative and indeed, sensible for two reasons:<\/p>\n\n
\n
\n
\n
Streamline the data analytics flow…<\/h2>\n
\n
1. Superset with Docker<\/h2>\n
\r\n# go to path\r\ncd \/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/how_to_use_superset\/\r\n\r\n\r\n# command to open docker\r\nopen -a docker\r\n\r\n# clone the dir\r\ngit clone https:\/\/github.com\/apache\/superset.git superset\r\n\r\n\r\n# get into the dir\r\ncd superset\r\n\r\n\r\n# get the superset stuff \r\ndocker compose -f docker-compose-non-dev.yml pull\r\n\r\n# start the superset stuff \r\ndocker compose -f docker-compose-non-dev.yml up\r\n\r\n# start using Superset\r\n# http:\/\/localhost:8088\r\n# username: admin\r\n# password: admin\r\n<\/pre>\n
2. Connecting Superset to Databases<\/h2>\n
mariadb<\/code>, mongodb<\/code>, mysql<\/code>, postgresql<\/code> installed on your machine to leverage on a database. To be sure to connect Superset to your local database, you need to use the hostname docker.for.mac.host.internal<\/code> instead of localhost<\/code>.<\/p>\n\r\n\r\n# list services for database\r\nbrew services list\r\n\r\n\r\n# classical commands before install\r\nbrew update\r\nbrew upgrade\r\nbrew doctor\r\n<\/pre>\n
\r\n\/bin\/bash -c \"$(curl -fsSL https:\/\/raw.githubusercontent.com\/Homebrew\/install\/main\/install.sh)\"\r\n\r\n# Update the Homebrew formulae:\r\nbrew update\r\n<\/pre>\n
\nMariaDB is an open-source, community-developed relational database
\nmanagement system (RDBMS) that serves as a drop-in replacement for MySQL.
\nIt offers a robust and flexible SQL engine with features such as stored
\nprocedures, views, subqueries, and triggers. <\/p>\n\n
\nto migrate existing databases without having to rewrite code or modify
\napplications.<\/li>\n
\nthrough enhancements like optimistic optimization and asynchronous
\nreplication. This results in faster query processing and reduced latency
\nin real-time environments.<\/li>\n
\npartitioning that can help handle large datasets efficiently while
\nensuring optimal performance.<\/li>\n<\/ol>\n\r\n# Managing the mariadb database\r\n\r\n# mariadb\r\nbrew search mariadb\r\nbrew install mariadb\r\nbrew services start mariadb\r\nbrew services stop mariadb\r\n\r\n# connect to mariadb (no password)\r\nmysql\r\nmysql -u brunoflaven \r\n\r\n# create a root user with all privileges\r\nCREATE USER 'root'@'hostname' IDENTIFIED BY 'root';\r\n# CREATE USER 'root'@'%' IDENTIFIED BY 'root';\r\nSELECT USER,is_role,default_role FROM mysql.user;\r\nGRANT ALL PRIVILEGES ON *.* TO 'root'@localhost IDENTIFIED BY 'root';\r\nFLUSH PRIVILEGES;\r\nSHOW GRANTS FOR 'root'@localhost;\r\n\r\n# connection infos\r\n# select mysql\r\n# add port 3306\r\n# add host docker.for.mac.host.internal or 127.0.0.1\r\n# db_name : mydatabase_try_mariadb\r\n# user: root\r\n# pwd: root\r\n\r\n\r\n\r\n# useful commands\r\n# create databases\r\nCREATE DATABASE try_mariadb;\r\nUSE try_mariadb;\r\nCREATE TABLE testtable\r\n(\r\n id int not null primary key,\r\n name varchar(20) not null,\r\n lastupdate timestamp not null\r\n );\r\n\r\n# insert\r\nINSERT INTO testtable\r\n (id, name, lastupdate)\r\n values (1,'Sample name','2022-09-22 18:53');\r\n\r\nINSERT INTO testtable\r\n (id, name, lastupdate)\r\n values (2,'Sample name 2','2022-09-22 18:54');\r\n\r\n# update\r\nUPDATE testtable set name = 'updated name' where id=1;\r\n\r\n# delete one record with the id equal to 4\r\nDELETE FROM testtable where id = 4;\r\n\r\n# select all content from the table testtable \r\nSELECT * FROM testtable;\r\n\r\n# drop\r\nDROP TABLE testtable;\r\n\r\n# empty\r\nTRUNCATE testtable;\r\n\r\n\r\n\r\n<\/pre>\n
\ndatabase system with a strong emphasis on reliability, data integrity, and
\ncorrectness. It supports a wide range of data types, including
\ngeographical data, large objects such as images, JSON, and XML documents,
\nand advanced features like stored procedures, triggers, and rules.<\/p>\n\n
\nqueries, concurrency, and reliability. It follows the ACID (Atomicity,
\nConsistency, Isolation, Durability) principles to ensure data integrity.<\/li>\n
\nfor various languages and data types. Its plugin architecture allows for
\nseamless integration of new features and functionality without altering
\nthe core system.<\/li>\n
\ndatabase systems, including SQL Server, Oracle, MySQL, and DB2. This makes
\nit easy to migrate existing applications oruser<\/li>\n<\/ol>\n\r\n\r\n# install and start postgresql with homebrew \r\nbrew search postgresql\r\nbrew install postgresql\r\nbrew services start postgresql\r\nbrew services stop postgresql\r\n\r\n# connect to postgres\r\npsql postgres\r\n\r\n# way_1 to connect to postgresql\r\n# in the console\r\nCREATEUSER -s postgres\r\n# in the postgres client\r\nALTER USER postgres WITH PASSWORD 'password';\r\n\r\n# way_2 to connect to postgresql\r\n\r\n# in the postgres client\r\nCREATE ROLE root WITH LOGIN PASSWORD 'root';\r\nALTER ROLE root CREATEDB;\r\n\r\n# connect to postgres in a terminal\r\npsql postgres\r\n\r\n# your username should be listed\r\npostgres=# \\du\r\n\r\n# let's validate it\r\npostgres=# \\q;\r\n\r\n# and then:\r\npsql -U brunoflaven postgres;\r\npsql -U root postgres;\r\n\r\n# to quit\r\npostgres=# \\q;\r\n\r\n# list all databases\r\npostgres=# \\list;\r\npostgres=# \\l;\r\n\r\n# connect to a certain database\r\npostgres=# \\c;
mysql<\/code> and mongodb<\/code> as I want to have a “quick and dirty” csv conversion into superset.<\/i><\/p>\n.env<\/code> for Superset<\/b>
\nModify the .env files for environment-specific configurations and install additional Python packages by adding them to requirements-local.txt.
\nYou can use a combinaison of a docker-compose.yml file and .env file to install Superset with docker-compose.<\/p>\n\r\n# You can create some_random_base64_string using this command in shell\r\nopenssl rand -base64 42\r\n# OUTPUT: uKqlflwJGdDH\/+NpwuRhJh8mZrNsTGu45OMT7akZhGhaBlOqkkOR0xMP\r\n\r\n# in the mac terminal define the SUPERSET_SECRET_KEY\r\nexport SUPERSET_SECRET_KEY=\"uKqlflwJGdDH\/+NpwuRhJh8mZrNsTGu45OMT7akZhGhaBlOqkkOR0xMP\"\r\n\r\n<\/pre>\n
\r\n# Lists containers (and tells you which images they are spun from)\r\ndocker ps -a \r\n\r\n# Lists images \r\ndocker images \r\n\r\n# Removes a stopped container\r\ndocker rm
3. Collecting data: Using kafka, a corner stone<\/H2>
\nApache Kafka is a distributed streaming platform that is widely used for building real-time data pipelines and streaming applications. In the context of an analytics application, Kafka plays a crucial role in handling the flow of data between different components of the application. It provides a scalable, fault-tolerant, and high-throughput messaging system that allows seamless communication between various modules of the analytics application.<\/p>\n\n
\r\n\/bin\/bash -c \"$(curl -fsSL https:\/\/raw.githubusercontent.com\/Homebrew\/install\/HEAD\/install.sh)\"\r\n<\/pre>\n
\r\nbrew install kafka\r\n<\/pre>\n
\r\nbrew services list\r\n<\/pre>\n
\nZookeeper is a prerequisite for Kafka, and it manages distributed configurations and synchronization between nodes. Open a terminal and start Zookeeper:<\/p>\n\r\nbrew services start zookeeper\r\n<\/pre>\n
\r\nbrew services start kafka\r\n<\/pre>\n
\r\nkafka-topics --create --topic brunotopic1 --bootstrap-server localhost:9092 --partitions 1 --replication-factor 1\r\n<\/pre>\n
\r\nkafka-topics --list --bootstrap-server localhost:9092\r\n<\/pre>\n
\r\nkafka-console-producer --topic brunotopic1 --bootstrap-server localhost:9092\r\n<\/pre>\n
\r\nkafka-console-consumer --topic brunotopic1 --bootstrap-server localhost:9092 --from-beginning\r\n<\/pre>\n
\r\nkafka-topics --describe --topic brunotopic1 --bootstrap-server localhost:9092\r\n<\/pre>\n
\r\nkafka-topics --help\r\nkafka-console-producer --help\r\nkafka-console-consumer --help\r\n<\/pre>\n
\r\nbrew services stop kafka\r\nbrew services stop zookeeper\r\n<\/pre>\n
More infos<\/H2>
\nSuperset<\/H3><\/p>\n
\n
https:\/\/superset.apache.org\/<\/a><\/li>\n
https:\/\/www.startdataengineering.com\/post\/apache-superset-tutorial\/<\/a><\/li>\n
https:\/\/superset.apache.org\/docs\/intro\/<\/a><\/li>\n
https:\/\/hevodata.com\/learn\/superset-github\/#l12<\/a><\/li>\n
https:\/\/www.youtube.com\/@coursebytes<\/a><\/li>\n
https:\/\/preset.io\/product\/<\/a><\/li>\n
https:\/\/aiven.io\/blog\/get-started-with-apache-superset-and-postgresql<\/a><\/li>\n
https:\/\/apache-superset.readthedocs.io\/en\/0.28.1\/tutorial.html<\/a><\/li>\n
https:\/\/datacorner.fr\/introduction-a-apache-superset\/<\/a><\/li>\n
https:\/\/www.restack.io\/docs\/superset<\/a><\/li>\n
https:\/\/github.com\/apache\/superset<\/a><\/li>\n
https:\/\/censius.ai\/blogs\/apache-superset-tutorial<\/a><\/li>\n
https:\/\/www.restack.io\/store\/redash<\/a><\/li>\n
https:\/\/www.youtube.com\/watch?v=kGfUIOK87V8<\/a><\/li>\n
https:\/\/www.youtube.com\/watch?v=U33wA0gW01M<\/a><\/li>\n
https:\/\/www.edrawsoft.com\/fr\/ai-tools-tips\/ai-for-data-analysis.html<\/a><\/li>\n
https:\/\/online.edhec.edu\/fr\/blog\/le-role-de-lia-et-du-machine-learning\/<\/a><\/li>\n
https:\/\/geekflare.com\/fr\/ai-data-analysis-tools\/<\/a><\/li>\n
https:\/\/www.unite.ai\/ai-tools-data-analysts\/<\/a><\/li>\n
https:\/\/www.ibm.com\/products\/watson-studio\/autoai<\/a><\/li>\n
https:\/\/www.sitepoint.com\/openai-api-python-data-analysis\/<\/a><\/li>\n
https:\/\/github.com\/Apress\/python-data-analytics-2e<\/a><\/li>\n
https:\/\/wesmckinney.com\/book\/<\/a><\/li>\n
https:\/\/research.aimultiple.com\/ai-usecases\/#ai-use-cases-for-marketing<\/a><\/li>\n
https:\/\/devinterview.io\/<\/a><\/li>\n
https:\/\/github.com\/DerekKane\/Use-Cases-Data-Science<\/a><\/li>\n
https:\/\/clickhouse.com\/<\/a><\/li>\n
https:\/\/github.com\/ClickHouse\/ClickHouse<\/a><\/li>\n
https:\/\/github.com\/apache\/zeppelin<\/a><\/li>\n
https:\/\/github.com\/datafuselabs\/databend<\/a><\/li>\n
https:\/\/github.com\/apache\/spark<\/a><\/li>\n
https:\/\/github.com\/metabase\/metabase<\/a><\/li>\n
https:\/\/github.com\/getredash\/redash<\/a><\/li>\n
https:\/\/github.com\/microsoft\/ML-For-Beginners<\/a><\/li>\n
https:\/\/clickhouse.com\/<\/a><\/li>\n
https:\/\/www.robinwieruch.de\/postgres-sql-macos-setup\/<\/a><\/li>\n
https:\/\/www.restack.io\/docs\/superset-knowledge-apache-superset-mac-m1-guide<\/a><\/li>\n
https:\/\/www.restack.io\/docs\/superset-advanced-apache-superset-data-engineers<\/a><\/li>\n
https:\/\/github.com\/kkiaune\/emails-classification<\/a><\/li>\n
https:\/\/github.com\/JDiego199\/superset-docker-compose\/<\/a><\/li>\n
https:\/\/github.com\/mauricioobgo\/StreamingProject<\/a><\/li>\n
https:\/\/github.com\/insight-infrastructure\/superset-docker-compose<\/a><\/li>\n
https:\/\/medium.com\/airbnb-engineering\/supercharging-apache-superset-b1a2393278bd<\/a><\/li>\n
https:\/\/towardsdatascience.com\/running-apache-superset-at-scale-1539e3945093<\/a><\/li>\n
https:\/\/medium.com\/airbnb-engineering\/how-airbnb-achieved-metric-consistency-at-scale-f23cc53dea70<\/a><\/li>\n
https:\/\/medium.com\/airbnb-engineering\/how-airbnb-achieved-metric-consistency-at-scale-f23cc53dea70<\/a><\/li>\n
https:\/\/preset.io\/pricing\/<\/a><\/li>\n
https:\/\/docs.preset.io\/v1\/docs\/connecting-your-data<\/a><\/li>\n
https:\/\/medium.com\/streamthoughts\/how-to-build-a-real-time-analytical-platform-using-kafka-ksqldb-and-clickhouse-bfabd65d05e4<\/a><\/li>\n
https:\/\/towardsdatascience.com\/real-time-event-streaming-with-kafka-bigquery-69c3baebb51e<\/a><\/li>\n
https:\/\/cloud.google.com\/blog\/products\/data-analytics\/stream-data-with-open-source-kafka-by-aiven-analyze-with-bigquery<\/a><\/li>\n
https:\/\/www.youtube.com\/@Restackio<\/a><\/li>\n
https:\/\/www.youtube.com\/@BetterDataScience\/videos<\/a><\/li>\n
https:\/\/learn.microsoft.com\/en-us\/fabric\/real-time-analytics\/overview#what-makes-real-time-analytics-unique<\/a><\/li>\n
https:\/\/aiven.io\/blog\/build-a-real-time-analytics-pipeline<\/a><\/li>\n
https:\/\/dev.to\/tinybirdco\/what-is-real-time-analytics-5ah3<\/a><\/li>\n
https:\/\/dev.to\/tinybirdco\/building-real-time-analytics-into-your-next-project-3b6n<\/a><\/li>\n
https:\/\/www.cncf.io\/blog\/2023\/08\/08\/real-time-analytics-with-stream-processing-and-olap\/<\/a><\/li>\n
https:\/\/learn.microsoft.com\/en-us\/azure\/architecture\/solution-ideas\/articles\/real-time-analytics<\/a><\/li>\n
https:\/\/www.tinybird.co\/blog-posts\/real-time-databases-what-developers-need-to-know<\/a><\/li>\n
https:\/\/duynguyenngoc.com\/posts\/real-time-analytics-airflow-kafka-druid-superset\/<\/a><\/li>\n
https:\/\/medium.com\/@emergeit\/realtime-data-streaming-with-apache-kafka-apache-pinot-apache-druid-and-apache-superset-e67161eb9666<\/a><\/li>\n
https:\/\/levelup.gitconnected.com\/unlock-advanced-data-visualization-the-complete-guide-to-installing-and-using-apache-superset-on-afecb3c63889<\/a><\/li>\n
https:\/\/www.youtube.com\/watch?v=uvb00oaa3k8<\/a><\/li>\n
https:\/\/dev.to\/lyndsiwilliams\/apache-superset-installing-locally-is-easy-using-the-makefile-4ofi<\/a><\/li>\n
https:\/\/sahansera.dev\/setting-up-kafka-locally-for-testing\/<\/a><\/li>\n
https:\/\/eggerapps.at\/postico2\/<\/a><\/li>\n
https:\/\/dbeaver.io\/<\/a><\/li>\n
https:\/\/www.chrisjmendez.com\/2017\/02\/04\/homebrew-services\/<\/a><\/li>\n
https:\/\/www.chrisjmendez.com\/2016\/05\/09\/easy-mongodb-and-mysql-management-on-a-mac\/<\/a><\/li>\n
https:\/\/github.com\/datablist\/sample-csv-files<\/a><\/li>\n
https:\/\/www.kaggle.com\/datasets\/ahmedshahriarsakib\/tweet-sample<\/a><\/li>\n