MySQL

MySQL is a flexible, widely used database server. The client tools are installed on all compute and submit nodes for access to your database. More information can be found at: http://dev.mysql.com/

MySQL in Wharton’s HPC Environment

MySQL is available for all users of our HPC environment, but requires activation. Please e-mail research-computing@wharton.upenn.edu with a request for MySQL access.

For These Demos: Request ‘patstat2015b’ Access

E-mail research-computing@wharton.upenn.edu and ask for access to the MySQL database: ‘patstat2015b’.

Set Up ODBC Connector

Create a .odbc.ini file in your home directory (Linux) on the cluster, like so:

Change both instances of ‘DATABASE’ to the correct DB name – usually your username, or another available DB like PATSTAT or IMDb. Change ‘USERNAME’ to your username, and ‘PASSWORD’ to your MySQL password (not your Wharton or Penn password!!!).

For example, setting up your ‘patstat2015b’ access:

Change ‘USERNAME’ to your username, and ‘PASSWORD’ to your MySQL password (not your Wharton or Penn password!!!).

Test DSN Configuration and Connectivity

From an SSH session, test connectivity with ‘isql DATABASE’. For example:

MySQL Server Access

Connect to our MySQL server hpcc-sql from any HPCC node (compute or login) with your provided MySQL account information.

MySQL and Python

Set Up and Test ODBC Connector

Create an .odbc.ini file and test connectivity per the instructions at the top of this page.

Testing Python to MySQL Connectivity

As usual, you’ll want to setup a virtualenv for your Python work:

OR if you already have one setup, activate it:

You’ll need to install the pyodbc package. The following works well:

Once you have the package installed, create and run (python scriptname) the following script:

MySQL and R

It’s possible to work with your (or other available) MySQL database directly from within R, either in the HPCC environment (preferred and documented here), from another R installation on Penn’s campus (less preferred but possible), or remotely with the VPN active (highly discouraged, but again possible).

Generally, instructions are from https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf

Set Up and Test ODBC Connector

Create a .odbc.ini file and test connectivity per the instructions at the top of this page.

Testing R to MySQL Connectivity

Now you’re ready to test connectivity from within R.

MySQL and MATLAB

It’s possible to work with your (or other available) MySQL database directly from within MATLAB, either in the HPCC environment (preferred and documented here), from another MATLAB installation on Penn’s campus (less preferred but possible), or remotely with the VPN active (highly discouraged, but again possible).

Generally, instructions are from http://www.mathworks.com/help/database/ug/importing-data-from-databases-into-matlab.html

Testing MATLAB to MySQL Connectivity

There is no particular ‘setup’ required for MATLAB + JDBC connectivity, so you’re ready to test connectivity from within MATLAB.

MySQL and Stata

It’s possible to work with your (or other available) MySQL database directly from within Stata, either in the HPCC environment (preferred and documented here), from another Stata installation on Penn’s campus (less preferred but possible), or remotely with the VPN active (highly discouraged, but again possible).

Generally, instructions are from http://www.stata.com/manuals13/dodbc.pdf

Set Up and Test ODBC Connector

Create a .odbc.ini file and test connectivity per the instructions at the top of this page.

Testing Stata to MySQL Connectivity

Now you’re ready to test connectivity from within Stata.

MySQL and SAS

For an overview of SAS/ACCESS Interface for MySQL, see the appropriate SAS Documentation

A Wharton HPC and WRDS specific example — put some SAS data in MySQL: