Loading data into SQLFire can be done various ways including using Spring Batch with CSV files, Apache DDLUtils or direct JDBC connections pulling data into SQLFire. The approach below is yet another way. In this example we load the Oracle HR schema table "departments" into a SQLFire distributed system.
1. Create a java class with code that simple queries the table data and returns it as aJDBC ResultSet.
package vmware.au.se.sqlfire.derby;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleDriver;
public class DepartmentTable
{
public static String SQL = "select * from departments";
public static ResultSet read() throws SQLException
{
Connection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(SQL);
return rset;
}
public static Connection getConnection() throws SQLException
{
String username = "hr";
String password = "hr";
String thinConn = "jdbc:oracle:thin:@172.16.101.70:1521/linux11gr2";
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(thinConn,username,password);
conn.setAutoCommit(false);
return conn;
}
}
2. Add the JAR file with the class above as well as the Oracle JDBC jar file to the system CLASSPATH as shown below.
export CUR_DIR=`pwd`
export CLASSPATH=$CUR_DIR/lib/ojdbc6.jar:$CUR_DIR/lib/derby-funct.jar
export CLASSPATH=$CUR_DIR/lib/ojdbc6.jar:$CUR_DIR/lib/derby-funct.jar
Note: This ensures when we start our SQLFire nodes they will have the classes avaiable on the classpath
3. Start the SQLFire servers as shown below.
> sqlf server start -server-groups=MYGROUP -locators=localhost[41111] -client-bind-address=localhost -client-port=1528 -dir=server1 -classpath=$CLASSPATH &
> sqlf server start -server-groups=MYGROUP -locators=localhost[41111] -client-bind-address=localhost -client-port=1529 -dir=server2 -classpath=$CLASSPATH &
4. Log into the distributed system using the CLI and run the SQL as follows to create the table in SQLFire which will store the same dataset from the Oracle "departments" table.
create diskstore STORE1; call sys.set_eviction_heap_percentage_sg (85, 'MYGROUP'); drop table departments; create table departments (department_id int NOT NULL CONSTRAINT department_id_PK PRIMARY KEY, department_name varchar(40), manager_id int, location_id int) partition by column (department_id) SERVER GROUPS (MYGROUP) persistent 'STORE1' REDUNDANCY 1;
5. Log into the distributed system using the CLI and run the SQL below to create a function
CREATE FUNCTION externalDepartments () RETURNS TABLE ( DEPARTMENT_ID INT, DEPARTMENT_NAME VARCHAR( 40 ), MANAGER_ID INT, LOCATION_ID INT ) LANGUAGE JAVA PARAMETER STYLE DERBY_JDBC_RESULT_SET READS SQL DATA EXTERNAL NAME 'vmware.au.se.sqlfire.derby.DepartmentTable.read';
6. Log into the distributed system using the CLI and run the SQL below to insert data into the "departments" table in SQLFire using the function we created at #5.
insert into departments select s.* FROM TABLE (externalDepartments()) s;
7. Verify we now have our departments table in SQLFire with data.
[Fri Mar 16 08:55:40 papicella@:~/sqlfire/vFabric_SQLFire_101/pasdemos/oraclehr ] $ sqlf sqlf version 10.4 sqlf> connect client 'localhost:1527'; sqlf> select * from departments; DEPARTMENT&|DEPARTMENT_NAME |MANAGER_ID |LOCATION_ID ---------------------------------------------------------------------------- 230 |IT Helpdesk |NULL |1700 120 |Treasury |NULL |1700 10 |Administration |200 |1700 240 |Government Sales |NULL |1700 130 |Corporate Tax |NULL |1700 20 |Marketing |201 |1800 250 |Retail Sales |NULL |1700 140 |Control And Credit |NULL |1700 30 |Purchasing |114 |1700 260 |Recruiting |NULL |1700 150 |Shareholder Services |NULL |1700 40 |Human Resources |203 |2400 270 |Payroll |NULL |1700 160 |Benefits |NULL |1700 50 |Shipping |121 |1500 170 |Manufacturing |NULL |1700 60 |IT |103 |1400 180 |Construction |NULL |1700 70 |Public Relations |204 |2700 190 |Contracting |NULL |1700 80 |Sales |145 |2500 200 |Operations |NULL |1700 90 |Executive |100 |1700 210 |IT Support |NULL |1700 100 |Finance |108 |1700 220 |NOC |NULL |1700 110 |Accounting |205 |1700 27 rows selected
More info on derby-style functions can be found here.
http://db.apache.org/derby/docs/10.4/devguide/cdevspecialtabfuncs.html
