Forum: VoltDB Architecture

Post: Migrating from MSSQL

Migrating from MSSQL
MrDaniil
Aug 8, 2012
Hi all -


I'm looking to migrate financial application from MSSQL to VoltDB and there are some question that have come up. Hopefully this is the right place to ask them:


* It is frequently necessary to search for orders by date and/or perform date computations for our app. There are no DATE/DATETIME datatypes in VoltDB. How are date functions handled? The guide suggest avoiding Date objects.


* Are there any aggregate functions in VoltDB? We're frequently doing SUM, AVG, and COUNT.


* Is it safe to make an assumption that I can call stored procedure B from stored procedure A?


Many thanks,
Daniil
pmartel
Aug 9, 2012
Hi Daniil,


Thanks for your questions.


The TIMESTAMP type in VoltDB represents all date and time values. It is convertible to the Java Date and Timestamp types.
Support for the EXTRACT function is in active development and will be released soon.


For aggregate functions, VoltDB supports SUM, AVG, COUNT, MIN, and MAX.


We currently do NOT support invoking one stored procedure from another as a stored procedure -- that is, as a java client would invoke it.
VoltDB stored procedures are transactional and we do not support nested transactions.


Yet, since the stored procedures are java methods it is possible for stored procedures to call each other directly, with the following restrictions:


Both the caller and the called procedures must have the same partitioning annotation -- both multi-partition or, for improved performance, both single-partition.
If the stored procedures are single-partition, the caller procedure's run method must always pass the value of its own partitioning parameter through to the partitioning parameter of the called procedure's run method.


This is considered an "advanced" use case and requires some careful attention. The voltcompiler will not detect violations of these restrictions and the result of running a stored procedure that violates either restriction is "undefined behavior".


Otherwise, stored procedures are generally free to reuse java components for anything EXCEPT the queuing and execution of database statements.
Other behaviors, such as pre-processing of parameters and post-processing of results can be freely "factored out" and reused by stored procedures.


I hope this was helpful.
Let us know if you have any other questions.


Paul


VoltDB Inc.
Thank you, Paul. This
MrDaniil
Aug 16, 2012
Hi Daniil,


Thanks for your questions.


The TIMESTAMP type in VoltDB represents all date and time values. It is convertible to the Java Date and Timestamp types.
Support for the EXTRACT function is in active development and will be released soon.


For aggregate functions, VoltDB supports SUM, AVG, COUNT, MIN, and MAX.


We currently do NOT support invoking one stored procedure from another as a stored procedure -- that is, as a java client would invoke it.
VoltDB stored procedures are transactional and we do not support nested transactions.


Yet, since the stored procedures are java methods it is possible for stored procedures to call each other directly, with the following restrictions:


Both the caller and the called procedures must have the same partitioning annotation -- both multi-partition or, for improved performance, both single-partition.
If the stored procedures are single-partition, the caller procedure's run method must always pass the value of its own partitioning parameter through to the partitioning parameter of the called procedure's run method.


This is considered an "advanced" use case and requires some careful attention. The voltcompiler will not detect violations of these restrictions and the result of running a stored procedure that violates either restriction is "undefined behavior".


Otherwise, stored procedures are generally free to reuse java components for anything EXCEPT the queuing and execution of database statements.
Other behaviors, such as pre-processing of parameters and post-processing of results can be freely "factored out" and reused by stored procedures.


I hope this was helpful.
Let us know if you have any other questions.


Paul


VoltDB Inc.


Thank you, Paul. This information is really helpful. I'm scouring through the documents to determine whether we can go ahead with VolDB at this point. Our current application (stock trading platform) has its business logic done entirely in stored procedures (MSSQL). It is simply not feasible for me to rewrite all our applications to move logic to the application layer. Therefore, I would be adopting MSSQL stored procedures into VoltDB. Which is a good thing since VoltDB's procedures are in Java. However, this means that single stored procedure would need to load data from many different tables and/or modify data in different tables and also call other stored procedures which will modify their own set of tables. The fact that all this will be done in single transaction is good news to me (really good news), but can VoltDB's stored procedures be so complicated? Is it possible to use Java's collections framework from within VoltDB? Can I create arbitrary classes within VoltDB to perform calculations?
Very few restrictions
pmartel
Aug 18, 2012
Thank you, Paul. This information is really helpful. I'm scouring through the documents to determine whether we can go ahead with VolDB at this point. Our current application (stock trading platform) has its business logic done entirely in stored procedures (MSSQL). It is simply not feasible for me to rewrite all our applications to move logic to the application layer. Therefore, I would be adopting MSSQL stored procedures into VoltDB. Which is a good thing since VoltDB's procedures are in Java. However, this means that single stored procedure would need to load data from many different tables and/or modify data in different tables and also call other stored procedures which will modify their own set of tables. The fact that all this will be done in single transaction is good news to me (really good news), but can VoltDB's stored procedures be so complicated? Is it possible to use Java's collections framework from within VoltDB? Can I create arbitrary classes within VoltDB to perform calculations?


There are very few restrictions on what stored procedures are allowed to do or to call.


I already mentioned previously in this thread the restrictions around calling other stored procedures -- that is, having the run method of a class derived from VoltProcedure call another run method, whether directly or indirectly, to delegate database access.


The other class of restrictions is related to determinism of inputs to database write operations (DML statements). VoltDB is designed to support both k-safety ("live" redundancy) and WAN replication. To ensure the integrity of the database when one of a set of redundant servers is taken down, or when the database must switch over to a remote backup, it is imperative that each stored procedure invocation have an identical effect on the database wherever/whenever it is run, based solely on the procedure's input parameters. The executed SQL statements and their parameters must be a result (direct or indirect) of the stored procedure's input arguments and not influenced by other factors that might be available to a java program -- like the current time, or the results of a random number generator, or a reading from a temperature sensor or a local file. Basing DML statement choices and/or their parameters on such "variables" is likely to run afoul of VoltDB's internal integrity checking, which can have serious consequences. Such "external data" must be provided by "the database clients" through stored procedure parameters. Aside from this determinism restriction, stored procedures have full access to the power of the jvm and anything that can be packaged into jar files and run on the architecture of the host machines.
bas
Jul 2, 2013
Even i have the same question. Can we import data which is already present in mysql or HBase or Oracle to Volt DB ? Is it possible ?
jpiekos
Jul 2, 2013
Hi Bas,

VoltDB can import CSV files (see http://voltdb.com/docs/UsingVoltDB/clicsvloader.php). You can dump the data in those repositories to CSV then use the csvloader to import into VoltDB.

John
bas
Jul 2, 2013
Thank you, got it. Any more info in case of nosql dbs like Hbase data to import to volt db ?