In this recipe, we are going to execute a SQL query that will be passed down to the Thin Kettle driver. This special driver will convert the standard SQL query into something that MongoDB can understand. The Thin Kettle driver allows users to execute standard SQL against MongoDB.
To get ready for this recipe, you first need to start the MongoDB server with the same database as that of the last chapter. You will also have to start the Pentaho BA Server using the server control scripts. Once it is started, you can log in to the BI Server.
Perform the following steps:
- In the PUC, go to File | New | CDE Dashboard.
- Click on the Data Sources tab.
- Expand the SQL Queries data source category.
- Click on the sql over sqljndi data source.
- Set the Name property to QUERY4.
- Then set the JNDI property to Pentaho MongoDB Cookbook Kettle Thin.
- Open the Query Editor property, and copy and paste the following SQL query:
select customerCountry, SUM(totalPrice) as totalSales from Orders group by customerCountry
- Click on Save As under the CDE Main Menu.
- Save your dashboard at Public | Pentaho MongoDB Cookbook | Dashboards, with the name Kettle Thin Data Source.
To verify that this data source works, we are going to execute it directly from the accompanying CDA file:
- Click on the Opened menu on the Pentaho User Console and select Browse Files.
- Navigate to Public | Pentaho MongoDB Cookbook | Dashboards.
- Select the Kettle Thin Data Source.cda file, as shown in the next screenshot:
- Click on Open from the right-hand side menu.
- Click on the Data Access dropdown and select Data Access ID : QUERY4.
The query will execute once you have selected it from the Data Access drop-down menu. You will see the contents of the SQL query that is attached to the dashboard that you defined earlier.
In this recipe, we define a SQL statement that will be sent to MongoDB via the Thin Kettle JDBC driver. This driver converts SQL into a transformation that will run on top of MongoDB. In the background, the Kettle Thin JDBC driver (using the DI server or Carte server) generates a transformation that will take in an SQL statement and get data from the MongoDB database. After defining the data source in the dashboard and saving it, we're able to test this connection by running the data source in the CDA file.