Rocode >> Contracting >> StockPrice
A MySQL database using a Microsoft Windows Server 2012 virtual machine was hosted on NeCTAR with 64 cores.
Using Powershell with MySQL on a 64 core Windows virtual machine
During the second half of 2014 we were given access to and were delegated the administration of a NeCTAR virtual machine with 920GigaBytes of RAM and 64 Cores.
The virtual machine booted using Windows Server 2012 and had over 4TeraBytes of volume storage.
A few slides are shown from a PowerPoint presentation about the Stock Price project given in front of CSIRO during 2014.
The slides give details about the technologies implemented using MySQL on a NeCTAR 64 core Microsoft Windows Server virtual machine.
The outputs from the virtual machine were then studied and used for Econometrics research.
The slides may be access via this link.
A simple timestamp table
A Time Stamp Table was used within the MySQL SQL Scripts to log execution progress. The implementation was inspired by a discussion on stackoverflow.com!
This is an example SQL command to add an entry to the timestamplog table.
Pools of Stock Year Scripts
We use PowerShell to generate a pool of scripts that will be combined and scheduled to the correct MySQL database for execution.
The scripts may be run in Parallel or Sequentially.
This is the template stock year script.
This is how the template script looks after conversion.
MySQL Configuration Files
For each MySQL database, we create a configuration file that points to separate and dedicated folders.
This is an example Powershell script to start up the MySQL databases.
This is an example Powershell script to shut down the MySQL databases.
Load Table Data in to MySQL
For each database we created a script to load table data in to the MySQL databases.
This is an example Powershell script to load data in to the MySQL databases.
Here is a graphic display of the resulting tables as stored on disc.
Generation and Execution of MySQL Scripts
This is the execution Powershell script to run the MySQL SQL command scripts.
Powershell generates aggregated scripts that are then executed against the corresponding year.
The definition at the top of the script allows us to change the window title of the opened script windows.
Extracting the query results
The query results were finally extracted using Pentaho.
Pentaho Job - Web Example Extract Stocks
A Pentaho Job is executed to grab the execution results one year and stock at a time. Because we have a dynamic table name, we output each table name from a transformation and pass it a job as the parameter TABLENAMEVARIABLE. This is then used as a variable input to the SQL query.
Pentaho Job - Web Example Process A Stock Year
This Pentaho Job is used to extract one stock year data from the database. This is done as a job so that we may use variable replacement on the SQL query to perform a dynamic table name query.
Pentaho Transformation - Web Example Set Stock Year
This Pentaho Job reads in a file of stocks and years and outputs a stock year filename as a resulting field TABLENAMEVARIABLE.
Pentaho Transformation - Web Example Extraction
This transformation queries the database and extracts the table data.
We see here the TABLENAMEVARIABLE input variable on the SQL Select. Also note that we have selected the 2013 database. Unfortunately Pentaho does not allow for selecting the database connection dynamically.
For this example we manually selected each database connection. It should be possible to have a Table Input for each database connection and merge the outputs. We have not yet done this.