Overview

Knowi facilitates joins across multiple datasources to process, blend and store combined results seamlessly.

For example, if one datasource stores data about a supplier with a customer identifier, and another one stores the data about the customer and billing information, then in such a case, performing the lookups from one to another would require engineering efforts along with mapping storage for the results.

Knowi enables you to join across the same or disparate SQL or NoSQL databases to stitch the data back together into a single result along with the ability to store and track it.

Note: Joins can be set up across disparate datasources or within the same datasource.

Joins can be set up via the UI or using the Cloud9Agent (inside your network)

UI

In the following example, we'll connect to a MongoDB database to Query some data that includes a customer name and then perform a lookup against a MySQL database to pull customer addresses.

Step 1: Select the Queries from the left sidebar menu, click on the NEW DATASOURCE button at the top right corner of the interface, and select MongoDB as a datasource.

Accept the default configurations and click on Save (points to a MongoDB instance hosted by us).

Step 2: Repeat the above step but select MySQL as a datasource.

Accept the default configurations and click on Save

Step 3: Create a Query on MongoDB datasource via Visual Builder.

Select Queries from the left sidebar, click on the NEW QUERY button, and select MongoDB as a datasource (created in Step 1 above).

While generating the Query, select the following:

select "sendingActivity" from the collections drop-down menu.
select "sent" from the metrics drop-down menu.
select "customer" from the metrics drop-down menu.

Here we are tracking a simple metric of Sent messages grouped by a customer.

Step 4: Click on the Preview button to view the results.

Now, for the returned results from MongoDB, let's look up the customer details, which are stored in a MySQL table called customer.

Step 5: Click on Join Source and select MySQL datasource (created in Step 2 above) from the dropdown menu.

Step 6: Create a Query on MySQL datasource via Visual Builder.

select "customer" from the Tables drop-down menu

Step 7: Map fields from previous Queries to the current one using "join fields".

Enter "customer=customer" into the join fields box and select the Join Type using Join Builder.

Note: To specify multiple keys use 'AND': customer=customer AND joinAfield=joinBField

Step 8: Click "preview" to view the results.

The preview should now contain data from both MongoDB and MySQL that includes address and currency fields added to the MongoDB query for those customers.

Step 9: Click on Save Dataset and the dataset can now be added to a dashboard and shared easily.

The default join type is INNER JOIN when a join field is present. When a join field is not present, the data is blended together across the two datasets.

Other Join types supported: LEFT OUTER JOIN, FULL OUTER JOIN, RIGHT OUTER JOIN, LOOP JOIN.

To specify a different join type, you can proceed with any of the following two options:

Add the join type into the Join Fields. Example: FULL OUTER JOIN customer=customer
Click on Join Builder and make the selection of join type from the dropdown menu.

Data Manipulation after Joins: You can modify/transform the data returned in powerful ways using Cloud9QL at the bottom of the page. This section can be used to apply a Cloud9QL query as the very last step of processing. Alternatively, you can also interact with the grid directly for any manipulations of the data.

Cloud9Agent

As an alternative to the UI-based approach, joins can be specified on your agent directly.

For more details on agent setup and configuring datasource and query files, see Cloud9Agent.

Example query_xxx.json with joins:

[
    {
        "entityName":"Join Example",
        "dsName":"demoMongo",
        "queryStr":"db.pagehits.find({lastAccessTime: { $exists: true}}).limit(10)",
        "c9SQLFilter":"select date(lastAccessTime) as date, count(*) as hits_count group by date(lastAccessTime)",
        "joinQueries": [
            {
                "queryStr":"db.pagehits.find({lastAccessTime: { $exists: true}}).limit(10)",
                "c9SQLFilter":"select date(lastAccessTime) as date, sum(hits) as hits_total group by date(lastAccessTime)",
                "dsName":"demoMongo",
                "joinKeys": { "date": "date" }
            }
        ],
        "overrideVals":{
            "replaceAll":true
        }
    }
]

Join Types

Inner & Outer Joins

Knowi supports the following Inner & Outer Join types: INNER JOIN, LEFT OUTER JOIN, FULL OUTER JOIN, and RIGHT OUTER JOIN. The default join type is INNER JOIN when a join field is present.

Blending

When Join keys are not specified, data across the datasets will be blended together.

Loop Joins

Loop Join is a special join type where the first query result is fed into the second query one row at a time based on token(s) defined by the join condition. For each row of the first query's result, the second query will be executed and its result will be combined with the current row.

For example, consider 2 datasets:

id | user                      user | sex
 ---------                      ----------
  1 | xxx                       xxx  |  M
  2 | yyy                       yyy  |  F

First query:

 select *

Second query:

 select * where user = USER_TOKEN

Now the LOOP JOIN condition can be defined as:

 LOOP JOIN user = USER_TOKEN

With the above definition, the execution will be as follows:

Execute the first query which gives 2 rows as result.
For each row of the above result, execute the second query with the USER_TOKEN token replaced with the actual value of the user field of that row and join with it.

     select * where user = xxx 
     select * where user = yyy

The final result of the join will be:

id | user | sex 1 | xxx | M 2 | yyy | F

Note: For REST API datasources, any tokens defined in the endpoint or headers will be replaced.

Joins on Large Datasets

Knowi is architecturally designed to cater to large-scale joins across millions of records over disparate sources.

Joins are performed on the results of the two queries, where the join is performed in memory within Knowi, with a disk swap for larger datasets (with multi-threading and partitioning where applicable).

Note that data will be transferred over the network (into Knowi for cloud-based modes, or on the agent if using an agent) for the results of each query before the join is performed. For larger dataset joins that spans millions of records, use the agent.

For more insights into Knowi's Join processing technology for large-scale joins and benchmarks, contact your technical account manager.

Performance Optimizations

For Join processing on large-scale datasets, you can prefix the Join Type with a set of predefined "Hints" to help determine the algorithms and optimizations for faster processing.

Prefix	Description	Example
SORTED	Both sides of input data are sorted based on join keys. This will execute merge join but by-pass sorting	SORTED INNER JOIN a = b
LEFT SORTED	The left side of the input data is sorted. This will first sort the right side of input data then merge join	LEFT SORTED INNER JOIN a = b
RIGHT SORTED	The right side of the input data is sorted. This will first sort the left side of input data then merge join	RIGHT SORTED INNER JOIN a = b
HASH LEFT	Hash join will be used by hashing the left side data then join with the right-side data	HASH LEFT INNER JOIN a = b
HASH RIGHT	Hash join will be used by hashing the right-side data then join with the left side data	HASH RIGHT INNER JOIN a = b
MULTI HASH LEFT	Same as HASH LEFT but the join will be performed by multi-threads assuming multi-threads join is enabled (see note below)	MULTI HASH LEFT INNER JOIN a = b
MULTI HASH RIGHT	Same as HASH RIGHT but the join will be performed by multi-threads assuming multi-threads join is enabled (see note below)	MULTI HASH RIGHT INNER JOIN a = b

Note: On on-prem build, to enable multi-threads join, change the following property inside cloud9.properties to the desired number of join-threads. The more threads you specify here the more memory you will need.

  queryJoinThreads=10