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.

Stock Price NeCTAR Project

Stock Price NeCTAR Project

Task Manager Performance Properties

Task Manager Performance Properties

Presentation Slides

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. Watch a presentation of the Stock Price Systems

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!

See: http://stackoverflow.com/questions/3488536/mysql-create-time-and-update-time-timestamp.

This is an example SQL command to add an entry to the timestamplog table.

Example Time Stamp Log SQL Command

MySQL Time Stamp Log Table Definition

MySQL Time Stamp Log Table Definition

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.

PowerShell script to generate SQL scripts - GenerateScripts.ps1

This is the template stock year script.

Template Before - RunAStockYearTemplate.sql

This is how the template script looks after conversion.

Script After

MySQL Configuration Files

For each MySQL database, we create a configuration file that points to separate and dedicated folders.

Example MySQL Configuration File -MySQL42013.cnf

MySQL Startup

This is an example Powershell script to start up the MySQL databases.

PowerShell script to start up MySQL - Start.ps1

MySQL Shutdown

This is an example Powershell script to shut down the MySQL databases.

PowerShell script to shutdown MySQL - Stop.ps1

Load Table Data in to MySQL

For each database we created a script to load table data in to the MySQL databases.

SQL Script to load table data in to MySQL - Load2013.sql

This is an example Powershell script to load data in to the MySQL databases.

PowerShell script to load data in to MySQL - Load.ps1

Here is a graphic display of the resulting tables as stored on disc.

WinDirStat of MySQL Database Storage

WinDirStat of MySQL Database Storage

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.

PowerShell script to execute SQL command scripts on MySQL - Execute.ps1

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 Extract Stocks

Pentaho Job - Web Example Extract Stocks

Web Example Extract Stocks - Executing A Job

Web Example Extract Stocks - Execute A Job

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 Job - Web Example Process A Stock Year

Pentaho Job - Web Example Process A Stock Year

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 Set Stock Year

Pentaho Transformation - Web Example Set Stock Year

Web Example Set Stock Year - CSV Input

Web Example Set Stock Year - CSV Input

Web Example Set Stock Year - Script Values

Web Example Set Stock Year - Script Values

Pentaho Transformation - Web Example Extraction

This transformation queries the database and extracts the table data.

Pentaho Transformation - Web Example Extraction

Pentaho Transformation - Web Example Extraction

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.

Web Example Extraction - Table Input

Web Example Extraction - Table Input

Web Example Extraction - Calculator

Web Example Extraction - Calculator

Web Example Extraction - Script Values

Web Example Extraction - Script Values