Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

MongoDB Connector For QlikView & Qlik Sense

RSS
Modified on 2016/10/17 15:06 by darrenb Categorized as connector



Features

This Connector allows you to:
  • Connect to MongoDB by either:
    • Specifying a MongoDB server, port, username, password and database name.
    • Specifying a mongodb://.... connection string (version 1.5.5.7 of QVSource and later only). Note that this allows you to, for example, authenticate with one database then extract data from another, connect to slaves.
  • List all of the available collections.
  • Load the data from from a collection into QlikView.
  • As well as specifying a maximum number of rows to return you may also specify a JSON query, for example: {\"Sales\":{ $gte: 5000}}
  • By default the connector will convert the top level keys of each document in the collection into a column. However, you can also specify parameters to extract sub documents from each document and link them on a particular field.
    • Additionally, for array based items the connector will take care of creating a row for each value in the array.

Supported Versions

The connector currently uses version 1.11 of the c# Driver - you can see the supported versions of MongoDB from this page (2.4, 2.6, 3.0 and 3.2 at time of writing).

Limitations

Unfortunately, the driver which QVSource uses does not support connecting to MongoDB instances over SSH - you can view the open feature request for this here and a related post on the forums here.

MongoDB Quick Start

Here are some quick simple steps to get MongoDB set up and running on your local machine with a sample data set.

  • Download MongoDB from here.
  • Unzip the download to somewhere on your local machine, e.g. 'C:\Users\You\Downloads\mongodb-win32-x86_64-2.4.5\mongodb-win32-x86_64-2.4.5\'
  • Create a directory named 'c:\mongo' and copy the contents of the bin folder in the above into it.
  • Create a directory named 'c:\mongo\data'.
  • Run a windows command prompt and navigate to the the mongo directory (e.g. using 'cd c:/mongo').
  • Run the following in the command prompt: 'mongod --dbpath c:\mongo\data'.
  • You should now see some messages in the command line suggesting that mongoDB is running.
  • You should now be able to connect to the MongoDB instance using something like MongoVUE or Robomongo.

If you would like to import some data you can place a csv file (e.g. data.csv) in c:\mongo and run the following command: 'mongoimport --type csv --file data.csv --collection youcollectionname --headerline'

you can see more details on mongoimport here.

If you would like some sample data to start with you might like to try the Baseball database available here.

Usage

When you view the MongoDB connector you should be presented with something similar to the following.

Image

You should enter as a minimum the server name or IP address and name of the Database you wish to connect to. You can also enter additionally a port number, username and password.

Once you have done this you should be able to run the Collections table and get a list of tables which the MongoDB Database contains:

Image

In this example we are connecting to MongoDB running on the local machine and no credentials are needed so we leave them blank.

Once you have decided which collection you would like to extract move to the Collection table in the Connector and enter the collection name. You should then be able to run the table and extract the data.

Image

If you collection contains a large number of rows, you may wish to use the Max Number Of Rows parameter whilst using the QVSource UI to limit the number returned.

NOTE: It is not recommended to load millions of rows into the QVSource UI at this point. The QVSource datagrid is not designed for this - we would recommend you load a few thousand rows and then stop the load, and the copy the generated load script in to your QlikView or Qlik Sense application for a full reload.

Image

This will appear as a parameter in the request URL to QVSource in the generated QlikView load script.

Notice the maxRows parameter in the following load script example:


[http://localhost:5555/QVSource/MongoDBConnectorV2/?table=Collection&appID=&server=localhost&database=test&collection=test&maxRows=10]
(qvx);

You can then simply delete this parameter after pasting the load script into QlikView.

Running Queries

The Connector supports JSON queries using the operators detailed here. You enter the query into the connector as a JSON string. For example entering the following:
{"Sales":{ $gte: 5000}}

As shown:

Image

Will only extract the items where the Sales figure is greater than or equal to 5000.

For dates you can using something like: {"published": { "$gte": { "$date": "2016-07-20T23:23:50Z" }}}

You can also combine filters (e.g. and, or) - for example: { "$or": [{"restaurant_id": { "$eq": "30112340"}}, {"restaurant_id": { $eq: "30075445"}}] }

The SQL to MongoDB Mapping Chart also contains a list of example SQL select statements and how they map to MongoDB.

You could also specify the query in the QlikView or Qlik Sense load script, remembering that it should be url encoded before being sent to QVSource.

Notice the jsonQuery parameter in the following load script example:


Sub urlEncode(str)
  
    let str=replace(str, '%', '%25'); // should be first
    let str=replace(str, '#', '%23');
    let str=replace(str, ' ', '%20');
    let str=replace(str, '$', '%24');
    let str=replace(str, '&', '%26');
    let str=replace(str, '+', '%2B');
    let str=replace(str, ',', '%2C');
    let str=replace(str, '/', '%2F');
    let str=replace(str, '\', '%5C');
    let str=replace(str, ':', '%3A');
    let str=replace(str, ';', '%3B');
    let str=replace(str, '=', '%3D');
    let str=replace(str, '?', '%3F');
    let str=replace(str, '@', '%40');
    let str=replace(str, '[', '%5B');
    let str=replace(str, ']', '%5D');
    let str=replace(str, '>', '%3E');
    let str=replace(str, '<', '%3C');
    let str=replace(str, chr(39), '%27');
      
    call=str;
          
End sub

let query = '{"Sales":{ $gte: 5000}}';

call urlEncode(query);

// query will now be: %7b%22Sales%22%3a%7b+%24gte%3a+5000%7d%7d

YourTable:
LOAD
FROM
[http://localhost:5555/QVSource/MongoDBConnectorV2/?table=Collection&appID=&server=localhost&database=test&collection=test&jsonQuery=$(query)]
(qvx);

Extracting Sub Documents & Arrays

Extracting Sub Documents

Lets say you have nested sub documents as illustrated below:

Image

If you wanted to extract the subdoc1 and link it on the main id field you could add the following configuration:

Image

As another example, you could extract the subdoc2 and link it to the string field of the document above using:

Image

Extracting Arrays

NOTE: Version 1.0.1.3 of this Connector and later includes support for including array indexes in sub key document path, for example you should be able to enter '/subkey1/subkey2[3]/subkey3'

This connector has limited support for extracting arrays from MongoDB to QlikView and Qlik Sense. As an example, let's say we have created a MongoDB collection with the following JSON.


{
    "_id": 1,
    "key": "a",
    "items": [
        {
            "name": "value",
            "number": 1
        },
        {
            "name": "value2",
            "number": 2
        }
    ]
}
{
    "_id": 2,
    "key": "b",
    "items": [
        {
            "name": "value3",
            "number": 3
        },
        {
            "name": "value4",
            "number": 4
        }
    ]
}

Creating something which looks like this:

Image

We can make use of the sub document and sub document key parameters as shown below and the connector will discover that the sub document we are referring to is an array and create a row for each item in the array with a corresponding key value to link it to the parent row/document.

Image

It is still possible for MongoDB to have more complex nested array structures which QVSource is not able to deal with effectively but the above should allow users to get the required data in a large number of cases.

Default Parameters

Note that although the following input parameters are placed in the generated request URL/QlikView load script:
  • Server
  • Port
  • Database Name
  • Username
  • Password

If you remove these parameters from the request URL, QVSource will 'fall back' on the values stored in the QVSource Settings (i.e. those shown when you interact with the Connector's UI).

Removing these from your load script means that you can easily update them globally for all applications which go through a particular instance of QVSource.

Troubleshooting

Authentication

If you are having trouble with authenticating, please double check that your username, password and other connection details work successfully from another tool such as MongoVUE. Please also be prepared to demonstrate this over a screen share with us in order to receive support.

Kerberos Authentication

QVSource uses the official c# driver which does support Kerberos as noted here.

We don't add any specific code or functionality into the QVSource connector in this regard but from the MongoDB docs it appears that should be able to specify something like the following:
mongodb://user%40REALM.COM@localhost/?authMechanism=GSSAPI

In the connection string.

Support Forum

You might also find the MongoDB Support Forum useful, for example in working out how to build a certain connection string or searching for specific error messages. You can of course also contact us for support.

Change Log

1.0.3 - 10/12/15
  • Please note that as a new official BI Connector (https://docs.mongodb.org/manual/products/bi-connector/) is now available for MongoDB 3.2 and later you may wish to first consider this. Because of this we are also now considering retiring this connector if it does not offer any advantages over this.
  • Upgraded to MongoDB Driver 1.11.0. This should now support version 3.0 of MongoDB.
  • Fixed bug where if 'Max Number Of Rows' input parameter was set, the connector would still first inspect all documents in collection to establish column structure for table, now it will only inspect the first 'Max Number Of Rows' documents.

1.0.2 - 31/07/15
  • Moved to 3 digit version number in line with other connectors.

1.0.1.5 - 01/04/2015
  • All documents in collection are now interrogated (instead of only first 10,000) to build up collection of column names for result table.

1.0.1.4 - 28/02/2015
  • Updated Collections table description.
  • Added 'table buffer size' input parameter to Collection table.

1.0.1.3 - 21/10/2014
  • Now supports including array indexes in sub key document path, for example you should be able to enter '/subkey1/subkey2[3]/subkey3'

1.0.1.2 - 29/09/2014
  • Fixed bug where document fields seen in a given batch of documents are not properly remembered on subsequent batches which can break QlikView load.

1.0.1.1 - 19/09/2014
  • If server value begins with mongodb:// it will now be used as a standard connection string (i.e. port, username/password and database inputs will be ignored).

1.0.1.0 - 24/07/2014
  • Upgraded to v1.9.2.235 of MongoDB Driver.

1.0.0.0 - 06/05/2014
  • Out of beta.

0.9.6.7 - 11/03/2014
  • Initial release.
  Name Size
- Collection.png 64.48 KB
- Collections.png 55.37 KB
- extract mongodb array in qvsource.png 93.50 KB
- JSON Query.png 12.63 KB
- Max Rows.png 55.33 KB
- mongodb array.png 15.68 KB
- MongoDB Inputs.png 29.56 KB
- subdoc 1.png 4.15 KB
- subdoc 2.png 4.40 KB


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd