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
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
