For a project where I needed to write a solution data set from a GAMS GDX file into MySQL database, I developed a tool gdx2mysql. The call is:
> gdx2mysql –i results.gdx –s tmp –u username –p password
Here tmp is the target schema (or database). The default server is localhost which is what we use in this application. The tool will try to pick the latest ODBC driver installed. When using the trnsport model from the GAMS model library (yes, transport without the ‘a’, a relic from 8.3 filenames we used to be limited to), we see:
--- Job trnsport.gms Start 04/21/16 11:46:43 24.6.1 r55820 WEX-WEI x86 64bit/MS Windows IBM ILOG CPLEX 24.6.1 r55820 Released Jan 18, 2016 WEI x86 64bit/MS Windows Reading data... Iteration Dual Objective In Variable Out Variable Optimal solution found. --- Restarting execution GDX Library 24.6.1 r55820 Released Jan 18, 2016 VS8 x86 32bit/MS Windows |
To see what is happening behind the scenes use the –v (verbose) flag:
GDX2MySQL v 0.1 GDX Library 24.6.1 r55820 Released Jan 18, 2016 VS8 x86 32bit/MS Windows |
There are a few interesting issues we deal with:
- scalar parameters, variables and equations are not stored as individual tables but rather collected in tables scalarparameters, scalarvariables and scalarequations.
- I delete and recreate the tables to make sure they have the correct structure. This looks easier than checking the structure (columns and their types) and only doing a truncate (truncate is faster than using delete). Obviously the user needs to have permissions to do so.
- I try to insert many rows before doing a commit.
- I try to map GAMS special values to reasonable things we can actually store in the database:
- All names are protected by ` in case we use a keyword as a name.
- The string columns are getting a width just enough to store the largest string. E.g. for set ‘i’ this is 9, while for set ‘j’ this is 8.
- All values are stored using type double.
- This version uses a prepared statement and then does N calls to sqlexecute. We could speed this up a little by doing one sqlexecute per (small) batch. This would require some arrays to hold the data. Another alternative is to forget about prepare, and just create a long string containing the insert statement (MySQL allows several records to be inserted using one insert statement).
- For large data: special consideration. Typically we would like to employ some bulk insert method. In MySQL this is load data infile. A next post will have some timings.
No comments:
Post a Comment