How to Join MongoDB and SQL Server Tables in Bold Data Hub

Please find the Step-by-Step Guide to Join SQL Server and MongoDB Data Using Bold Data Hub

Step 1: Access the Data Hub Module

1. Click on the Data Hub icon in the navigation pane.

Step 2: Integrate Bold Data Hub and Create a New Project

Integrate Bold Data Hub using the following documentation: Integrated Bold Data Hub Architecture

1. Once the integration is complete, click Add Pipeline and provide a name for the new pipeline.

Step 3: Configure SQL Server Connection

1. Select your project and add the MSSQL template.
2. Configure the SQL Server connection with the following parameters:
   • Host
   • Port (default is 1433)
   • Username and Password
   • Database Name
   • Driver Name and Driver
   • Table to be extracted.

3. Click the Save button to save the configuration. Check the Logs tab to confirm whether the data was extracted and the pipeline was created successfully.

Step 4: Configure MongoDB Connection

1. In the same project, add the MongoDB template.
2. Configure the MongoDB connection with the following parameters:
   • Connection URL in the format: mongodb://dbuser:passwd@host?ssl=true
   • Database Name
   • Table to be extracted
3. Click the Save button to save the configuration.

Step 5: Transform and Join Data

1. Navigate to the Transform tab and create a new transform table.
2. Use the SQL Code Editor to write a custom query to perform the join between the SQL Server and MongoDB data sources.
3. Click Save and Transform to save and execute the transform table.
4. Ensure that data from sources has been properly loaded into the destination database.

Ensure that data from sources has been properly loaded into the destination database. You can now use the integrated data for building dashboards in Bold BI

Sample template:

version: 1.0.1
encrypt_credentials: false
direct_target_import: false
union_all_tables: true
add_dbname_column: false
direct_load_to_destination: true
plugins:
  extractors:
    - name: MSSQL
      connectorname: MSSQL
      schemaname: dbo
      config:
        host: SYNCLAPN-24833
        port: 1433
        username: {user_name}
        database: {database_name}
        password: {password}
        drivername: mssql+pyodbc
        driver: ODBC+Driver+17+for+SQL+Server
      properties: 
      metadata: 
      select:
      - {table_name}
    - name: MongoDB
      connectorname: MongoDB
      schemaname: 
      config:
        connection_url: mongodb://{username}:{password}{host}?ssl=true
        database: {database_name}
      properties: 
      metadata:
      select:
      - {table_name}

Additional reference:

Please refer to the Bold BI documentation for Bold Data Hub: Working with ETL in Bold BI – Embedded BI | Bold BI Learning

Please refer to the documentation on joining data in the Bold Data Hub: Transformation Join External Customer Data | Bold Data Hub