DEV Community: Gustavo Gordillo

Connecting MongoDB with Tableau: A Step-by-Step Guide (Macbook client)

Gustavo Gordillo — Mon, 09 Sep 2024 17:49:58 +0000

Introduction

In today's data-driven world, visualizing data is crucial for understanding trends and making informed decisions. MongoDB, a powerful NoSQL database, offers flexibility and scalability, while Tableau is one of the top tools for data visualization. By combining these two, you can effectively represent unstructured or semi-structured data stored in MongoDB. In this tutorial, we’ll guide you through the process of connecting MongoDB with Tableau using the MongoDB BI Connector, helping you to visualize your data effortlessly.

Prerequisites
Downloading Drivers
Connecting MongoDB to Tableau Using the BI Connector

Step 1: Prerequisites

Before starting, ensure you have the following:

Tableau Desktop: If you don't already have Tableau installed, you can download it from here.
Connection String: The MongoDB connection string provided by your database service. It will look something like this:

mongodb://atlas-sql-[id]-yp1td.a.query.mongodb.net/[db]?ssl=true&authSource=admin

Your database provider should supply this, along with the appropriate username and password.

Step 2: Install MongoDB JDBC Driver

MongoDB offers official support for connecting to Tableau via the MongoDB BI Connector. Follow these steps to set up the connection and start visualizing your MongoDB data in Tableau.

Download the MongoDB JDBC Driver Download the latest version of the MongoDB JDBC driver from the official MongoDB documentation. You can find it here. In this case, the most recent version is mongodb-jdbc-2.2.0-all.jar.

Move the .jar File into Tableau Driver folder

After downloading the .jar file, move it to the appropriate directory.

Go to the home directory in the Finder app

Open Finder and press Shift + Command + . to reveal hidden folders.
Navigate to the Library folder.
Inside Library, go to Tableau/Drivers.
Copy and paste the downloaded .jar file into the Drivers folder.

Step 2: Install the Tableau Connector (`.taco` File)

Download the Tableau Connector (.taco) File The .taco file is required to complete the connection. Download it from here.

Add the .taco File to Tableau Repository

Open Tableau.
Navigate to File > Repository Location.

Find and open the Connectors folder in the Tableau repository.
Copy and paste the downloaded .taco file into this folder.

Once these steps are completed, MongoDB should appear now in Tableau as an optional connection.

Step 3: Connecting MongoDB to Tableau Using the BI Connector

To connect MongoDB Atlas to Tableau, follow these steps:

Select MongoDB Atlas by MongoDB from the available options.
Enter your MongoDB URI (ask your database administrator for this information).
Specify the database you want to access (ask your database administrator for this information).
Set Authentication Method: Use Username and Password.
Input Username (ask your database administrator for this information).
Input Password (ask your database administrator for this information).
Click Sign In.

And that's it! You are now successfully connected to your MongoDB database. Congratulations! 🥳🚀

References

MongoDB Atlas Tableau Connector Documentation: MongoDB Atlas Guide
Tableau Desktop MySQL Connection Documentation: Tableau Guide

Setup a GO Bazel Monorepo (using bzlmod)

Gustavo Gordillo — Fri, 28 Jun 2024 00:29:30 +0000

In this article, we’ll walk through setting up your own Bazel repository using bzlmod, the new external dependency system introduced in Bazel 5 and later. We’ll also add a small Go “Hello, World!” project to demonstrate how to include additional projects in a monorepo.

Getting Started

Create a new directory for your Bazel project. For this example, we’ll call it bazel-intro.
Inside the bazel-intro directory, create two files: MODULE.bazel and BUILD.

Directory structure so far:

bazel-intro
│
├─ MODULE.bazel
├─ BUILD

About the MODULE.bazel File

According to the official Bazel docs on bzlmod, the MODULE.bazel file should be at the root of your workspace (where the WORKSPACE file would normally be). Unlike WORKSPACE, you do not list all transitive dependencies here—only direct dependencies. Bazel will automatically process the MODULE.bazel files of your dependencies to find any transitive dependencies. In this example, we don’t need a WORKSPACE file.

Editing MODULE.bazel for Go Support

Since we’re going to use Go, we need to specify the necessary Bazel dependencies for Go:

bazel_dep(name = "gazelle", version = "0.37.0")
bazel_dep(name = "rules_go", version = "0.48.1")

gazelle: A build file generator that automatically creates or updates BUILD files for Go projects.
rules_go: Provides rules to build Go code with Bazel.

You can find more recent versions of these dependencies on the Bazel Registry.

Adding Go Extensions

According to the rules_go and gazelle docs, add the following lines to MODULE.bazel to set up the Go SDK:

go_sdk = use_extension("@rules_go//go:extensions.bzl", "go_sdk")

# Download a suitable Go SDK.
go_sdk.download(version = "1.20.3")

Adding Gazelle Integration and Dependencies

Next, integrate Gazelle:

go_deps = use_extension("@gazelle//:extensions.bzl", "go_deps")
go_deps.from_file(go_mod = "//:go.mod")

use_repo(go_deps,
         "com_github_gofiber_fiber_v2", 
)

Here’s what we did:

go_deps.from_file(go_mod = "//:go.mod"): Points Gazelle to our Go module file.
use_repo(go_deps, "com_github_gofiber_fiber_v2"): Registers a Go dependency (github.com/gofiber/fiber/v2) that we’ll use in our example HTTP server.

Note: If you need more dependencies, you can simply list them inside use_repo:

use_repo(go_deps, 
         "com_github_gofiber_fiber_v2", 
         "com_github_joho_godotenv"
)

Updating the BUILD File

Now let’s populate the top-level BUILD file to integrate Gazelle:

load("@gazelle//:def.bzl", "gazelle")

gazelle(name = "gazelle")

This sets up Gazelle at the root of our workspace.

Creating a Sample Go Project

Next, we’ll create a simple Go project within our repository to test everything.

Updated directory structure:

bazel-intro
│
├─ MODULE.bazel
├─ BUILD
└─ services
└─ app
└─ main.go

Steps:

Inside bazel-intro, create a services directory.
Inside services, create an app directory.
In the app directory, create a main.go file with the following code:

package main

import (
    "log"
    "github.com/gofiber/fiber/v2"
)

func main() {
    app := fiber.New()

    app.Get("/", func(c *fiber.Ctx) error {
        return c.SendString("Hello, World!")
    })

    log.Fatal(app.Listen(":3000"))
}

Initializing Go Modules

In your terminal, navigate to bazel-intro and initialize a Go module:

go mod init bazel-intro/services
go mod tidy

This creates a go.mod and a go.sum file in the root. Gazelle will use these to understand and update dependencies.

Running Gazelle and Building the Project

Run Gazelle to generate BUILD files for Go code:

bazel run //:gazelle

Build all targets:

bazel build //...

Run the application:

bazel run services/app

If everything goes correctly, you should see the Fiber HTTP server running on port 3000. Open your browser and navigate to http://localhost:3000 to see the “Hello, World!” message.

Conclusion

You’ve successfully set up a Bazel repository using bzlmod, integrated Go support, and created a small service using Fiber. From here, you can add more services, dependencies, and tooling to expand your monorepo.

Happy coding! 🎉

SQL Server & MongoDB sync (Kafka + Kubernetes + Debezium)

Gustavo Gordillo — Sun, 17 Dec 2023 21:17:28 +0000

The problem to solve

Some months back, I encountered a scenario where I needed to synchronize two databases: one based on SQL Server and the other on MongoDB. The SQL Server database was the primary source of information derived from the client's business logic, and the MongoDB was the source of information for our proprietary e-commerce sites.

We needed to synchronize some of the client SQL server operations on our Mongo DB.

The problem in pieces

After some research, we found a way to achieve this using an event-driven architecture pattern:

We must capture events from SQL Server (INSERT, DELETE, UPDATES, etc.. )
Stream those events
Consume events and update the MongoDB.

Solution

We used the change data capture (CDC) of SQL Server in combination with Debizium connectors to capture the SQL operations (such as inserts, deletes, updates, etc..) and send them to Apache Kafka, an open-source event streaming platform. And for the consumers part we develop our own using python.

The architecture will look like this:

Image from Debizium documentation

Tutorial

I will explain how to do it using MiniKube (local kubernetes) ideal for testing before production. You probably need to have some basic knowladge of Kubernetes.

The first part in this process is to enable change data capture for the table of your interest (Microsoft Docs)

-- Enable CDC for a table specifying filegroup
USE MyDB
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = N'MyRole',
    @filegroup_name = N'MyDB_CT',
    @supports_net_changes = 1
GO

Once you enable the CDC, we can start using minikube to deploy locally our cluster.

Deploying architecture in Minikube

To deploy the architecture locally I strongly suggest to follow the steps in the debizium documentation, It's straigh forward and the only thing I could do is rewrite it here, and its not worth it. Insted I will hightligth some key steps to use it with SQl server connector insted of Mysql connector (which is the current example in the docs)

Configuring Kafka connect

In order to connect to SQL Server you need to use the Kafka connect connector: debizium-sqlserver-connnector, you can find it here: https://repo1.maven.org/maven2/io/debezium/debezium-connector-sqlserver/

Then use the next kafka-connect-configuration file to create the connector

apiVersion: kafka.strimzi.io/v1beta2
kind: KafkaConnect
metadata:
  name: strimzi-cluster-operator
#  annotations:
#  # use-connector-resources configures this KafkaConnect
#  # to use KafkaConnector resources to avoid
#  # needing to call the Connect REST API directly
#    strimzi.io/use-connector-resources: "true"
spec:
  version: 3.5.1
  replicas: 1
  bootstrapServers: my-cluster-kafka-bootstrap:9093
  tls:
    trustedCertificates:
      - secretName: my-cluster-cluster-ca-cert
        certificate: ca.crt
  config:
    group.id: connect-cluster
    offset.storage.topic: connect-cluster-offsets
    config.storage.topic: connect-cluster-configs
    status.storage.topic: connect-cluster-status
    # -1 means it will use the default replication factor configured in the broker
    config.storage.replication.factor: -1
    offset.storage.replication.factor: -1
    status.storage.replication.factor: -1
  build:
    output: # (2)
      type: docker
      image: {here-go-your-container-repository-registry}
      pushSecret: {your-secret}
    plugins: # (3)
      - name: debezium-sqlserver-connector
        artifacts:
          - type: tgz
            url: https://repo1.maven.org/maven2/io/debezium/debezium-connector-sqlserver/2.3.3.Final/debezium-connector-sqlserver-2.3.3.Final-plugin.tar.gz

As you see on (2) in the image field of the yaml file, you need to write a container repository registry pointing to a docker image that will be use to build the kafka-connect container. Example: gustavo/kafka-connect-sql-server:latest, use your own. I use docker hub for this one.

And finally you need to create a kubernetes's secret, you can name it as you wish, for example: secret-kafka.

Here is an example:

kubectl create secret docker-registry secret-kafka -n kafka \
  --docker-email=your-user-email-goes-here \
  --docker-username=your-user-name-goes-here \
  --docker-password=your-password-goes-here

note that in this example we are pointing to the the kafka namespace (-n kafka) if you are following the debizium tutorial, you should use the same namespace.

Creating the Debizium SQL Server Connector

Finally this how your kafka-connection should look like:

apiVersion: kafka.strimzi.io/v1beta2
kind: KafkaConnector
metadata:
  name: debezium-connector-sqlserver
  labels:
    strimzi.io/cluster: debezium-connect-cluster
spec:
  class: io.debezium.connector.sqlserver.SqlServerConnector
  tasksMax: 1
  config:
    schema.history.internal.kafka.bootstrap.servers: debezium-cluster-kafka-bootstrap:9092
    database.hostname: your-sql-server-hostname
    database.port: you-sql-server-port
    database.user: your-sql-server-user
    database.password: your-sql-server-password
    database.dbname: your-sql-server-db
    database.server.name: your-sql-server-name
    topic.prefix: chose-a-prefix
    database.names: database-name
    database.encrypt: true
    database.trustServerCertificate: true
    database.include.list: database-list-comma-separated
    table.include.list: table-list-comma-separated

Look the sql server connector docs for more details.
Example:
table.include.list = dbo.table0, dbo.table1, dbo.table2
topic.prefix = dev-prefix
database.names = SQLENTERPRISE

Consumers

Through this step you should have a running cluster using minikube.

The last step is to make some consumers to capture kafka events. You can refert to confluent documentation to install the confluent kafka client, this will help us interact with the kafka cluster.

Let's create a file that will help us to listen kafka events

decorators.py


from confluent_kafka import Consumer, KafkaError

# Define the Kafka consumer configuration
consumer_config = {
    'bootstrap.servers': '10.10.1.1:9092',  # Replace with your Kafka broker(s) address
    'group.id': 'connect-cluster-id',
    'auto.offset.reset': 'earliest',  # Start consuming from the beginning of the topic
}

# Create a Kafka consumer instance
consumer = Consumer(consumer_config)

# Subscribe to a Kafka topic
consumer.subscribe(['dev-prefix.SQLENTERPRISE.dbo.table1', 'dev-prefix.SQLENTERPRISE.dbo.table2', 'dev-prefix.SQLENTERPRISE.dbo.table3'])  # Replace with the name of the topic you want to consume from
print("Subscribed to topic")

def loop(func):
    def wrapper():
        try:
            while True:
                msg = consumer.poll(1.0)
                if msg is None:
                    continue
                if msg.error():
                    # Handle any errors that occurred during consumption
                    if msg.error().code() == KafkaError._PARTITION_EOF:
                        # End of partition event
                        print(f"Reached end of partition for topic {msg.topic()} partition {msg.partition()}")

                    else:                
                        print(f"Error while consuming message: {msg.error()}")
                else:
                    # kafka logic
                    print(f"Consumed message: {msg.value()}")  
                    func(msg)
        except KeyboardInterrupt:
            pass
        finally:
            # Close the Kafka consumer when done
            consumer.close()

    return wrapper

main.py

from util.decorators import loop

import json

@loop
def main(msg):
    key = str(msg.topic()).replace("dev-prefix.SQLENTERPRISE.dbo.", '').lower()

    value = json.loads(msg.value().decode("utf-8"))
    payload = { key: value }

    if msg.topic() == "dev-prefix.SQLENTERPRISE.dbo.table0":
        print(payload)
    elif msg.topic() == "dev-prefix.SQLENTERPRISE.dbo.table1":
        print(payload)
    elif msg.topic() == "dev-prefix.SQLENTERPRISE.dbo.table2":
        print(payload)
    else:
        print("topic not found")


main()

Finally you could replace the print(payload) to a POST call pointing to a microservice that you want to delegate mongoDB updates or you could do it rigth there.

And that it all! 🎉🎉

In the future, I will explain how to deploy it on production using google cloud.