So you’ve got your MySQL database up and running and you’ve started to either download historical prices from Quandl, Google or Yahoo, or you are scraping information from websites. But now you want to actually do some analytics. One of the best places to do this would be MatLab. So in this post I describe how you get Matlab to read your MySQL database.
I assume you have both these installed:
- MySQL Workbench 6.2 64-bit
- Matlab R2010a 64-bit v7.10
Get the correct ODBC driver
Matlab uses the MySQL Connector/J ODBC Driver to communicate with MySQL. In particular we will be using the MySQL Connector/J 5.1.34 direct from the MySQL website. You must first log in to your Oracle account to get this driver. Go and download and install it if you haven’t.
http://dev.mysql.com/downloads/connector/j/
Open Matlab R2010a and type “prefdir” in the command window. The output of this command is a file path to a folder on you computer. Copy this path and paste it in to Explorer. Close Matlab.
In this folder create a new file called javaclasspath.txt.
Open javaclasspath.txt and copy and paste the full file name of the MySQL Connector Java 5.1.34 file that you just downloaded and save and close the text file. Restart Matlab.
Creating an ODBC Connection
Follow these steps:
- Open up your Data Sources (ODBC) administrator (type “Data Sources” in the Run textbox in the Start Menu).
- Go to the System DSN tab and click the Add button.
- Select your MySQL ODBC 5.3 ANSI Driver and click Finish.
- In the Connection Parameters enter a
- Data Source Name (this is free text and becomes the label for the DSN connection. Call it something descriptive).
- Select TCP/IP Server and type in “localhost” (to be sure, open MySQL Workbench, log in as Admin, go to Users and Privileges, and check the Host Name for the user you want to log in as.).
- Enter the Username (note that this could be “root” if you want to log in as and administrator but this is not advised. Rather you should have a separate user already set up with user privileges).
- Enter the appropriate password for this user (should have already been set up when the user was created).
- Click TEST.
- After testing, pick the schema you wish to connect to by default from the Database drop-down menu.
- Click OK and exit ODBC administrator.
Connecting Matlab to MySQL
Now that we have a MySQL ODBC Connection set up go back to Matlab and create a new script. In this script type in the following code:
conn=database('futures_master','trader','password');
curs=exec(conn,'SELECT * FROM stg_futures_import');
curs=fetch(curs);
curs.Data;
In this example, I have a staging table called stg_futures_import which holds all my Quandl data before it is transformed in to my Production schema. This staging table resides in a schema called futures_master. I want Matlab to read this table. Because of this I have set my MySQL ODBC connection up so that it points to ‘futures_master’ by default. I also have a non-admin user called ‘trader’ with some priviliges whom is using Matlab to read this table. Thus, we begin by setting up a connection object (called conn) and specifying ‘futures_master’ as the ODBC DSN name, trader as the user of Matlab and whom is also a MySQL user, and ‘password’ as the corresponding password.
Then we create a cursor object (called curs) and set it equal to the execution of a standard SQL statement which is just a SELECT ALL statement from the staging table.
The we reset the cursor object to fetch the data and then run the Data method of the Cursor object to retrieve the data.
Running this code should generate a Matlab array of dates and data.
References
Connecting to a Database Using the Native ODBC Interface:
http://au.mathworks.com/help/database/ug/connecting-to-a-database-using-the-native-odbc-interface.html
The Matlab Database Function:
http://au.mathworks.com/help/database/ug/database.html
The MySQL JDBC For Windows:
http://au.mathworks.com/help/database/ug/mysql-jdbc-windows.html
MySQL Download Connector/J For MySQL:
http://dev.mysql.com/downloads/connector/j/
From First Principles 