VerdictDB on MySQL¶
On this page, we will demonstrate an example of a Java application with a VerdictDB library that creates a scrambled table for your database, then executes a query that enable VerdictDB to utilize the scrambled table.
Here we assume 1) your MySQL database is running and 2) TPC-H data has been loaded into your database following the instructions in the previous Setup TPC-H Data page.
Getting VerdictDB Example Application¶
Before we start, you need to get our example Java application that we will be referring to throughout this entire tutorial. To do so, you need
git and run the following command in your working directory:
$ git clone firstname.lastname@example.org:verdictdb/verdictdb-tutorial.git
This will clone the current VerdictDB tutorial application into
Move into the
verdictdb_on_mysql directory, where the example we will use is located.
Compiling the Application¶
The tutorial application has been set up with Apache Maven and you should be able to build a runnable jar file by running the following command:
$ mvn package
This will create a runnable jar file under
Running the Application¶
We included a shell script
run.sh in the example application that you can invoke to execute the example application.
The script takes 4 arguments:
name of database/schema, and
Creating a Scrambled Table¶
In this tutorial, we are going to create a scrambled table on
With the example application, you can create one by simply running the following command (assuming you are running the MySQL database following our Setup MySQL page):
$ ./run.sh localhost 3306 tpch1g create Creating a scrambled table for lineitem... Scrambled table for lineitem has been created. Time Taken = 67 s
This command executes the following SQL statement via VerdictDB:
CREATE SCRAMBLE tpch1g.lineitem_scramble FROM tpch1g.lineitem
A corresponding Java source in the example application is following (simplified for brevity):
... Statement stmt = verdictConn.createStatement(); String createQuery = String.format( "CREATE SCRAMBLE %s.lineitem_scramble " + "FROM %s.lineitem", database, database); System.out.println("Creating a scrambled table for lineitem..."); stmt.execute(createQuery); stmt.close(); System.out.println("Scrambled table for lineitem has been created."); ...
As it is clear from the above snippet, it is just like running SQL statement via any JDBC connection.
After the scrambled table is created, VerdictDB will automatically utilize this scrambled table for any query that involves the
Running a Sample Query¶
The example application can execute one very simple aggregation query with and without VerdictDB, which lets you to see the performance difference between the two cases.
The actual query is as follows:
SELECT avg(l_extendedprice) FROM tpch1g.lineitem
To run this query with/without VerdictDB, you can simply run the following command:
$ ./run.sh localhost 3306 tpch1g run Without VerdictDB: average(l_extendedprice) = 38255.138485 Time Taken = 43 s With VerdictDB: average(l_extendedprice) = 38219.8871659 Time Taken = 10 s
You can see that VerdictDB achieves more than 4x speedup with a 99.9% accurate result even with a relatively small dataset that we use in this tutorial (i.e., 1GB).