Connecting Weka and SQL Server 2016
This tutorial will help you connect Weka to SQL Server 2016. This tutorial will also provide some solutions to possible issues you may encounter when attempting this tutorial.
Weka 3.8 Setup
If you haven’t already, and I don’t know why you wouldn’t have, download Weka 3.8 (the latest stable version) and install it.
Go to the Weka home directory (where you had Weka install) and locate the weka.jar file (In this tutorial it is F:\Program Files\WEKA\Weka-3-8). Right-click this and extract here. If you do not have the “extract here” option, download and install either 7Zip or WinRar and the come back to this step and try again.
We should now have the folder weka in this is a folder called experiment (.\weka\experiment). In this example, the full path is F:\Program Files\WEKA\Weka-3-8\weka\experiment.
In this folder, we are looking for a lovely little file called DatabaseUtils.prop. This is a properties file for Weka (if you want to learn more about Weka property files click here). Otherwise copy this file to make a backup. We are going to be editing this. Weka will only look directly at one DatabaseUtils.prop file (hsql, msaccess, mssqlserver, etc are not accessible by Weka)
Open your DatabaseUtils.props.mssqlserver2005 (using a text editor like Notepad/Notepad++) and save it as DatabaseUtils.props file. We now have the groundworks for our properties file, we just need to make a few more changes.
The current file has the database URL as the following:
# database URL jdbcURL=jdbc:sqlserver://localhost;databaseName=blahblah
Change this to your own, if your SQL Server 2016 is on your local machine it will most likely be (databaseName is not required):
# database URL jdbcURL=jdbc:sqlserver://localhost:1433
There are some data type mappings:
varchar=0 float=2 tinyint=3 int=5
After these data type mappings, add some additional ones (to save you some headaches in the future)
nvarchar=9 bigint=6 decimal=2 bit=1
Congrats your database property file is now set up! But we aren’t done yet, now we need to set up our classpath.
Setting Up the Classpath
Weka is written in Java and when Java connects to a database it needs to load a JDBC Driver, which then performs the connection. So we need to help Weka find the driver to talk to SQL Server 2016.
Download the Microsoft JDBC Driver 4.2. Install the driver (right-click -> Run as administrator), preferably you should install the driver to C:\Program Files\Microsoft JDBC Driver 4.2 but any location works. In this example, the driver will be installed at F:\Program Files\Microsoft JDBC Driver 4.2.
Installing this in your program files helps with remembering the location and also puts it in a more memorable place. (Basically what I am saying is don’t install this in your downloads folder).
Did you finish? Good! Now we need to modify (or create) the CLASSPATH system environment variable.
Go to Control Panel -> System -> Advanced System Settings -> Environment Variables.
Or press your windows keys and start typing Environment Variables (I don’t care how you get here just get here)
Look for the CLASSPATH variable in the System Variables box, most likely you will not have this variable but if it does exist click on CLASSPATH and then Edit. In the Variable Value box, append a semicolon and the full path to the JDBC jar file to the existing CLASSPATH value. Make sure to include the file name. In this example, the full path is F:\Program Files\Microsoft JDBC Driver 4.2 for SQL Server\sqljdbc_4.2\enu\jre8\sqljdbc42.jar Click on OK to close the Edit System Variable window.
If CLASSPATH does not exist click on New. In the Variable Name box type CLASSPATH and in the Variable Value box enter the full path and file name to the JDBC jar file (or click Browse File and navigate to it). Click OK.
You should now see the CLASSPATH Variable in the Systems variables box.
Click OK to close the Environment Variables window. Add then click OK again to close the System Properties window.
Test the Setup
Now let’s test the setup. Startup the Weka 3.8 application, and choose Explorer:
Click on Open DB
Check if your URL has the value you placed in your DatabaseUtils.props.
If it doesn’t you need to copy your DatabaseUtils.props file and place it in your user’s home folder. By default, this will be in your user home directory /wekafiles/props. In order to find out where your user home directory is going to your command prompt and type the following command.
In my case, I needed to copy DatabaseUtils.props to C:\Users\Joanne\wekafiles\props. After doing this restart Weka and try again, your URL should now reflect what’s in your DatabaseUtils.props file.
Now click on the little person button to display the Database Connection Parameters window, here you will enter the database login and password. At the moment SQL Server Authentication is the only mode available now windows authentication this means you MUST create a SQL Server User Login that has permissions to your server and read from your databases. You also need to enable SQL Server Authentication) Click OK.
After this, you can then click the Plug Icon with the Lightning Bolt to test your connection and everything “should” work. If you get a “Login failed for user” error make sure “SQL Server and Windows authentication mode” is enabled. If so you need to enable TCP/IP Connection in SQL Configuration Manager.
Press the button again and everything should work.
Now you are connected and ready to start querying from your database. Have fun analyzing your data!
Some Problems That Can Occur
Unable to find a suitable driver
- Double-check your CLASSPATH
- Double-check your DatabaseUtils.props this JDBC driver should be
# JDBC driver (comma-separated list) jdbcDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver
- Make sure your login name and password are correct. If you get a “Login
- Make sure “SQL Server and Windows authentication mode” is enabled.
- Check is the TCP/IP Connection is enabled in SQL Configuration Manager.