In this recipe, we will guide you through the steps required to connect to your MongoDB instance via the JDBC-based SQuirreL SQL Client. We will be using the Thin Kettle JDBC Driver to make the connection to the MongoDB instance.
The SQuirreL SQL Client is a SQL client open source project, and it is possible to connect to any database that provides a JDBC driver, such as Thin Kettle.
To get ready for this recipe, you first need to start the MongoDB server with the same database as that of the last chapter. Then make sure that the Carte and DI servers are running. Download SQL Squirrel from http://squirrel-sql.sourceforge.net and install it in your computer by following the instructions on the website.
Once we have our Carte or DI Server up and running, we can configure a SQL client to fire some SQL queries and get some data back. Perform the following steps to configure a Squirrel SQL client:
- Open your Squirrel SQL Client.
- Define a new JDBC driver. To do this, you can perform the following steps:
- Click on the Drivers tab from left side of Squirrel SQL Client.
- Click on the button with the plus icon; or in the tools menu, click on Drivers and then on New Driver.... You will see an Add Driver dialog. This will allow you to define the Java Class Path and the Driver name on your Squirrel SQL Client.
- Set the Name property to
Kettle Thin. - Set Example URL to
jdbc:pdi://<server:port>/kettle?<option=value>. - Select the Extra Class Path tab. Click on the Add button and add the following .jar files from your
<pentaho-installation-path>/design-tools/data-integration/libfolder:kettle-core-<version>.jarkettle-engine-<version>.jarcommons-httpclient-3.1.jarcommons-codec-1.5.jarcommons-lang-2.6.jarcommons-logging-1.1.1.jarcommons-vfs-<version>-pentaho.jarlog4j-1.2.16.jarscannotation-1.0.2.jar
- Click on the List Drivers button.
- Select the org.pentaho.di.core.jdbc.ThinDriver option for the Class Name property. Your setup should look similar to the following screenshot:
- Click on the OK button to save the driver configuration.
- Configure the connection for the Carte server. To do this, perform the following steps:
- Select the Aliases tab from left side of Squirrel SQL Client.
- Click on the button with a plus icon; or in the tools menu, click on Aliases and then on New Alias....
- Once the Add Alias dialog opens, set the Name property to
Pentaho Carte Server Conn. - Select the In the Driver property, select Kettle Thin option.
- For the URL property, change the connection text to
jdbc:pdi://localhost:8082/kettle. - The default User Name and Password properties are both
cluster. - Click on the Test button to test the connection, and you should see the Connection successful dialog. Your setup should look similar to what is shown in the following screenshot:
- Click on the OK button to save the connection configuration.
With the connection defined, you can now connect to Carte using your Thin Kettle connection. Double-click on the Pentaho Carte Server Conn connection and click on the Connect button. You will have a new session tab opened, as shown in the following screenshot. In this session, you can execute basic operations such as running SQL queries, listing tables, and listing columns of a particular table.
- Run SQL queries:
- Select the SQL tab in the Pentaho Carte Server Conn session.
- Write
select * from AggregationTable, in the text area. - Execute the SQL query by any of these ways: click on Run SQL button; in the tools menu, select Session and then click on Run SQL, or just press Ctrl + Enter. You will get the query result as shown in the following screenshot:
- Define a connection using Thin Kettle to Data Integration server:
- Select the Aliases tab from the left-hand side of Squirrel SQL Client.
- Click on the button with a plus icon. Alternatively, in the tools menu, click on Aliases and then on New Alias....
- Once the Add Alias dialog opens, set the Pentaho DI Server Conn name for the Name property.
- In the Driver property, select the Kettle Thin option.
- Set the URL property to jdbc:pdi://localhost:9080/kettle?webappname=pentaho-di.
- The default User Name and Password properties are
adminandpassword, respectively. - Click on the Test button to test the connection, and you should get a Connection successful dialog. The connection configuration should be similar to what is shown in the following screenshot:
- Click on the OK button to save the connection configuration.
- With the connection defined, we are able to execute SQL queries against the connection. The steps are exactly the same as executing queries on the Carte server, the only difference being that you are running them on the DI Server.
The driver is registered in SQuirreL using a set of Java libraries. The kettle-core-<version>.jar file is the main library that is used to create the connection, and it contains the org.pentaho.di.core.jdbc.ThinDriver class. The other .jar files are dependencies of the kettle library. These Java libraries are required to make the connection to the Carte or DI server and execute the SQL queries that we described before. Think of the Carte or DI Server as the "Kettle Database."
This recipe uses the Squirrel SQL Client as an example. However, you can use some other SQL client and develop a solution using this JDBC or using any reporting solution that is JDBC-based.
The Thin Kettle JDBC Driver is limited to only performing select queries. It does not have the ability to execute inserts, updates, deletes, and so on. With the select ability, we can support connections to Mondrian. Mondrian is a ROLAP (short for Relational Online Analytical Processing) server used by the Pentaho BA server, which we will cover in more detail in the next couple of chapters.
If you want to learn more about what SQL is supported by the Thin Kettle JDBC Driver, you can consult this documentation website:
http://wiki.pentaho.com/display/EAI/JDBC+and+SQL+Reference#JDBCandSQLReference-SQLSupport