GAMS GDX files are accessed via an a low-level API. One way to make GDX files more appealing to programmers is to use a higher-level API. One possibility is to use an SQL API. This allows us to use a very familiar API, Also we can apply certain transformations directly at the SQL level to make data more useful for the host application. Note that we don’t import GDX data into a database here, but rather access GDX data directly through an SQL engine.
GAMS data can often be mapped to SQL tables in a straightforward manner:
Interestingly, we have used SQLite and their virtual table mechanism (https://www.sqlite.org/vtab.html) for this exercise. SQLite allows to store strings in any column. In that sense it is more like Excel. This flexible mechanism would allow us to store normal numbers as DOUBLE values and those GAMS related special values as TEXT.
Here is a small example:
Results from prototype implementation:
Note that the GAMS code to get this result is more complicated:
Sorting is another area where SQL is much more convenient than GAMS code. Being able to do things where GAMS lacks expressiveness seems like a win to me.
With this SQL layer there are many new possibilities, such as:
- Use GDX files through ODBC allowing many applications to access GDX data without any programming effort
- The same ODBC layer also allows access to GDX data from many programming languages using a standard, well-known API
- Use GDX files through LINQ queries from .Net applications
- Writing GDX files (but there are severe restrictions in the GDX API in this respect – e.g. one symbol at the time and only one batch of inserts, no updates).
Of course the price we pay is performance. In many cases we need to do a full table scan. For extremely large data sets, programming directly against the GDX API is still the best solution. Note that in some cases we can increase performance by importing data into a local data base using a SELECT … INTO, after which we can add some clever SQL indices.
No comments:
Post a Comment