A while ago I started working on a web based utility for Pivotal SQLFire known as SQLFire*Web. Today it was finally released as an open source project. See the link below for more details.
http://blog.gopivotal.com/products/new-open-source-download-browser-based-schema-management-for-pivotal-sqlfire
Search This Blog
Friday, 26 July 2013
Friday, 19 July 2013
Pivotal SQLFire - Shutting down members when authentication is on
In a previous blog entry I detailed how to use the DEV BULIT IN authentication mode for SQLFire.
http://theblasfrompas.blogspot.com.au/2013/07/securing-schemas-in-pivotal-sqlfire.html
When you need to shutdown members with authentication on you would do the following.
1. Create a directory called "shutdown".
2. Copy sqlfire.properties into that directory
sqlfire.auth-provider=BUILTIN
sqlfire.sql-authorization=true
sqlfire.user.admin=adminpassword
sqlfire.user.locatoradmin=adminpassword
sqlfire.user.serveradmin=adminpassword
3. Change into the shutdown directory and run a script as follows.
sqlf shut-down-all -locators=localhost[41111] -user=admin -password=adminpassword
This will only shutdown the members you would still need to stop the locator if you wanted to do that.
http://theblasfrompas.blogspot.com.au/2013/07/securing-schemas-in-pivotal-sqlfire.html
When you need to shutdown members with authentication on you would do the following.
1. Create a directory called "shutdown".
2. Copy sqlfire.properties into that directory
sqlfire.auth-provider=BUILTIN
sqlfire.sql-authorization=true
sqlfire.user.admin=adminpassword
sqlfire.user.locatoradmin=adminpassword
sqlfire.user.serveradmin=adminpassword
sqlf shut-down-all -locators=localhost[41111] -user=admin -password=adminpassword
This will only shutdown the members you would still need to stop the locator if you wanted to do that.
Wednesday, 17 July 2013
Securing Schemas in Pivotal SQLFire
Authentication/authorization is off by default in SQLFire if servers have not specified it. That is why SQLFire connections don't need a user/password if not configured explicitly on server. Once authorization has been configured on server, users will have access to only their database objects. This is illustrated below. In this example we use PLAIN text for the passwords just to illustrate how we secure a schema
1. Create a locator script as follows
sqlf locator start -user=admin -password=adminpassword -sqlfire.sql-authorization=true -peer-discovery-address=Pas-Apicellas-MacBook-Pro.local -peer-discovery-port=41111 -client-bind-address=Pas-Apicellas-MacBook-Pro.local -client-port=1527 -dir=locator
2. Create a server start script as follows
sqlf server start $JVM_OPTS -user=admin -password=adminpassword -sqlfire.sql-authorization=true -max-heap=$MAXHEAP -initial-heap=$MINHEAP -critical-heap-percentage=85 -eviction-heap-percentage=75 -server-groups=MYGROUP -conserve-sockets=false -locators=Pas-Apicellas-MacBook-Pro.local[41111] -client-bind-address=Pas-Apicellas-MacBook-Pro.local -client-port=1528 -dir=server1
Note: Ensure that a sqlfire.properties file exists for the locator/server as shown below.
sqlfire.properties
sqlfire.auth-provider=BUILTIN
sqlfire.sql-authorization=true
sqlfire.user.admin=adminpassword
sqlfire.user.locatoradmin=adminpassword
sqlfire.user.serveradmin=adminpassword
sqlf> connect client '127.0.0.1:1527;user=admin;password=adminpassword';
sqlf> CALL SYS.CREATE_USER('pas', 'pas');
Statement executed.
4. Connect with a JDBC client as shown below.
private Connection getConnection() throws SQLException
{
Connection conn = null;
conn = DriverManager.getConnection
("jdbc:sqlfire://127.0.0.1:1527/", "pas", "pas");
return conn;
}
When the JDBC connection connects successfully it will then be in the context of the schema "pas" and will have full rights / access to that schema to be able to create database objects etc.
If we provide an invalid username/password we get an error as follows, hence securing access to the schema with a built in username we created at step #3 above.
Exception in thread "main" java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: userid 'pas' or password invalid.
Pivotal HD - Talking true SQL on HDFS
Pivotal HD is an Apache Hadoop distribution that natively integrates
the industry-leading Pivotal Greenplum massively parallel processing (MPP)
database technology with the Apache Hadoop framework. See this link for it's release information. http://www.gopivotal.com/pivotal-products/pivotal-data-fabric/pivotal-hd
In this blog entry I want to detail, how we bring a full compliant ansi standard SQL into hadoop. The two components of Pivotal HD critical for this are as follows.
HAWQ - SQL query processor based on GPDB running on HDFS
HAWQ is a parallel SQL query engine that combines the merits of the Greenplum Database Massively Parallel Processing (MPP) relational database engine and the Hadoop parallel processing framework. HAWQ supports SQL and native querying capability against various data sources in different popular formats. It provides linear scalable storage solution for managing terabytes or petabytes of data at low cost
GFXF - Extension Framework component of HAWQ to create external tables
GPXF is the external table interface to HAWQ that enables querying data stored in HDFS, HBase, and HIVE. In addition, it exposes an API that enables a parallel connection to additional data sources.
Example
So here is a very simple example of how this works. We already have an existing Pivotal HD install with the HAWQ and GPXF components.
1. First lets generate some data using a simple script as follows
for i in {1..1000000}
do
echo "$i|person$i"
done
2. Create the file person.txt as shown below and verify we have 1 million person entries
hadoop fs -put person.txt /
4. At this point we have the data file on HDFS so we can create an external table using HAWQ so we can query the data using SQL. To create an external table we would use SQL as follows
GPXF enables External table interface inside HAWQ to read data stored in Hadoop ecosystem which enables loading and querying data stored in
Note: GPXF has an extensible framework API to enable custom connector development for other data sources and custom formats
5. Now at this point we can query data sitting on our HDFS using SQL , some examples below.
- Getting a count(*)
- Querying the first 10 records only
http://www.gopivotal.com/pivotal-products/pivotal-data-fabric/pivotal-hd
http://www.greenplum.com/products/pivotal-hd
http://pivotalhd.cloudfoundry.com/index.html
In this blog entry I want to detail, how we bring a full compliant ansi standard SQL into hadoop. The two components of Pivotal HD critical for this are as follows.
HAWQ - SQL query processor based on GPDB running on HDFS
HAWQ is a parallel SQL query engine that combines the merits of the Greenplum Database Massively Parallel Processing (MPP) relational database engine and the Hadoop parallel processing framework. HAWQ supports SQL and native querying capability against various data sources in different popular formats. It provides linear scalable storage solution for managing terabytes or petabytes of data at low cost
GFXF - Extension Framework component of HAWQ to create external tables
GPXF is the external table interface to HAWQ that enables querying data stored in HDFS, HBase, and HIVE. In addition, it exposes an API that enables a parallel connection to additional data sources.
Example
So here is a very simple example of how this works. We already have an existing Pivotal HD install with the HAWQ and GPXF components.
1. First lets generate some data using a simple script as follows
for i in {1..1000000}
do
echo "$i|person$i"
done
2. Create the file person.txt as shown below and verify we have 1 million person entries
[gpadmin@pivhdsne pas]$ ./data2.sh > person.txt [gpadmin@pivhdsne pas]$ cat person.txt | wc -l 1000000 [gpadmin@pivhdsne pas]$ tail person.txt 999991|person999991 999992|person999992 999993|person999993 999994|person999994 999995|person999995 999996|person999996 999997|person999997 999998|person999998 999999|person999999 1000000|person10000003. Now lets copy the file person.txt onto the HDFS as shown below
hadoop fs -put person.txt /
4. At this point we have the data file on HDFS so we can create an external table using HAWQ so we can query the data using SQL. To create an external table we would use SQL as follows
demo=# CREATE EXTERNAL TABLE person ( id int, name text)
demo-# LOCATION ('gpxf://pivhdsne:50070/person.txt?Fragmenter=HdfsDataFragmenter') FORMAT 'TEXT' (DELIMITER = '|');
CREATE EXTERNAL TABLE
Time: 3.843 ms
GPXF enables External table interface inside HAWQ to read data stored in Hadoop ecosystem which enables loading and querying data stored in
- HDFS
- HBase
- Hive
- Text
- Avro
- Hive - Text, Sequence and RCFile formats
- HBase
Note: GPXF has an extensible framework API to enable custom connector development for other data sources and custom formats
5. Now at this point we can query data sitting on our HDFS using SQL , some examples below.
- Getting a count(*)
demo=# select count(*) from person; count --------- 1000000 (1 row) Time: 1396.451 ms
- Querying the first 10 records only
demo=# select * from person limit 10; id | name ----+---------- 1 | person1 2 | person2 3 | person3 4 | person4 5 | person5 6 | person6 7 | person7 8 | person8 9 | person9 10 | person10 (10 rows) Time: 263.435 ms
More Information
Fore more information on Pivotal HD see the links below.http://www.gopivotal.com/pivotal-products/pivotal-data-fabric/pivotal-hd
http://www.greenplum.com/products/pivotal-hd
http://pivotalhd.cloudfoundry.com/index.html
Tuesday, 25 June 2013
Greenplum GPLOAD with updates/merge ability
“gpload” is a data loading utility that acts as an interface to Greenplum Database’s external table parallel loading feature. The Greenplum EXTERNAL TABLE feature allows us to define network data sources as tables that we can query to speed up the data loading process. Using a load specification defined in a YAML formatted control file, “gpload” executes a load by invoking the Greenplum parallel file server (gpdist) – Greenplum’s parallel file distribution program, creating an external table definition based on the source data defined, and executing an INSERT, UPDATE or MERGE operation to load the source data into the target table in the database.
In this example we show how we can load 500,000 records and then update those 500,000 records with GPLOAD.
1. Create a table to load the data into as shown below.
drop table if exists apples.people cascade;
create table apples.people (id int, name text)
DISTRIBUTED BY (id);
2. The data we are loading is defined as follows
[Tue Jun 25 22:39:24 gpadmin@:~/demos/gpload/inserts ] $ head person.txt
1,person1
2,person2
3,person3
4,person4
5,person5
6,person6
7,person7
8,person8
9,person9
10,person10
test.yml
7. Run a script to update the POEPLE table data as shown below.
Script:
gpload -f test.yml
Output:
[Tue Jun 25 22:54:21 gpadmin@:~/demos/gpload/upserts ] $ ./do-gpload.sh
2013-06-25 22:54:34|INFO|gpload session started 2013-06-25 22:54:34
2013-06-25 22:54:34|INFO|started gpfdist -p 8100 -P 8101 -f "/Users/gpadmin/demos/gpload/upserts/people.txt" -t 30
2013-06-25 22:54:39|INFO|running time: 5.13 seconds
2013-06-25 22:54:39|INFO|rows Inserted = 0
2013-06-25 22:54:39|INFO|rows Updated = 500000
2013-06-25 22:54:39|INFO|data formatting errors = 0
2013-06-25 22:54:39|INFO|gpload succeeded
In this example we show how we can load 500,000 records and then update those 500,000 records with GPLOAD.
1. Create a table to load the data into as shown below.
drop table if exists apples.people cascade;
create table apples.people (id int, name text)
DISTRIBUTED BY (id);
2. The data we are loading is defined as follows
[Tue Jun 25 22:39:24 gpadmin@:~/demos/gpload/inserts ] $ head person.txt
1,person1
2,person2
3,person3
4,person4
5,person5
6,person6
7,person7
8,person8
9,person9
10,person10
3. Create a YAML formatted control file to load the data into the table PEOPLE as shown below.
test.yml
VERSION: 1.0.0.1
DATABASE: gpadmin
USER: pas
HOST: 127.0.0.1
PORT: 5432
GPLOAD:
INPUT:
- SOURCE:
LOCAL_HOSTNAME:
- 127.0.0.1
PORT: 8100
FILE: [ /Users/gpadmin/demos/gpload/inserts/person.txt ]
- COLUMNS:
- "id":
- "name":
- FORMAT: text
- DELIMITER: ','
- ENCODING: 'UTF8'
- NULL_AS: ''
- ERROR_LIMIT: 100000
- ERROR_TABLE: apples.err_people
OUTPUT:
- TABLE: apples.people
- MODE: INSERT
4. Run a script to load the data as shown below.
Script:
gpload -f test.yml
Output:
[Tue Jun 25 22:44:29 gpadmin@:~/demos/gpload/inserts ] $ ./do-gpload.sh
2013-06-25 22:44:35|INFO|gpload session started 2013-06-25 22:44:35
2013-06-25 22:44:35|INFO|started gpfdist -p 8100 -P 8101 -f "/Users/gpadmin/demos/gpload/inserts/person.txt" -t 30
2013-06-25 22:44:35|INFO|running time: 0.73 seconds
2013-06-25 22:44:35|INFO|rows Inserted = 500000
2013-06-25 22:44:35|INFO|rows Updated = 0
2013-06-25 22:44:35|INFO|data formatting errors = 0
2013-06-25 22:44:35|INFO|gpload succeeded
Script:
gpload -f test.yml
Output:
[Tue Jun 25 22:44:29 gpadmin@:~/demos/gpload/inserts ] $ ./do-gpload.sh
2013-06-25 22:44:35|INFO|gpload session started 2013-06-25 22:44:35
2013-06-25 22:44:35|INFO|started gpfdist -p 8100 -P 8101 -f "/Users/gpadmin/demos/gpload/inserts/person.txt" -t 30
2013-06-25 22:44:35|INFO|running time: 0.73 seconds
2013-06-25 22:44:35|INFO|rows Inserted = 500000
2013-06-25 22:44:35|INFO|rows Updated = 0
2013-06-25 22:44:35|INFO|data formatting errors = 0
2013-06-25 22:44:35|INFO|gpload succeeded
Now at this point lets update the same data set with new updated data , once again using gpload BUT this time the YAML control file is a little different here.
5. The data we are UPDATING is defined as follows
[Tue Jun 25 22:54:39 gpadmin@:~/demos/gpload/upserts ] $ head people.txt
1,person1-updated
2,person2-updated
3,person3-updated
4,person4-updated
5,person5-updated
6,person6-updated
7,person7-updated
8,person8-updated
9,person9-updated
10,person10-updated
6. Create a YAML formatted control file to merge/update the data into the table PEOPLE as shown below.5. The data we are UPDATING is defined as follows
[Tue Jun 25 22:54:39 gpadmin@:~/demos/gpload/upserts ] $ head people.txt
1,person1-updated
2,person2-updated
3,person3-updated
4,person4-updated
5,person5-updated
6,person6-updated
7,person7-updated
8,person8-updated
9,person9-updated
10,person10-updated
test.yml
VERSION: 1.0.0.1
DATABASE: gpadmin
USER: pas
HOST: 127.0.0.1
PORT: 5432
GPLOAD:
INPUT:
- SOURCE:
LOCAL_HOSTNAME:
- 127.0.0.1
PORT: 8100
FILE: [ /Users/gpadmin/demos/gpload/upserts/people.txt ]
- COLUMNS:
- id: int
- name: text
- FORMAT: text
- DELIMITER: ','
- ENCODING: 'UTF8'
- NULL_AS: ''
- ERROR_LIMIT: 100000
- ERROR_TABLE: apples.err_people
OUTPUT:
- TABLE: apples.people
- MODE: MERGE
- MATCH_COLUMNS:
- id
- UPDATE_COLUMNS:
- name
- MAPPING:
id: id
name: name
7. Run a script to update the POEPLE table data as shown below.
Script:
gpload -f test.yml
Output:
[Tue Jun 25 22:54:21 gpadmin@:~/demos/gpload/upserts ] $ ./do-gpload.sh
2013-06-25 22:54:34|INFO|gpload session started 2013-06-25 22:54:34
2013-06-25 22:54:34|INFO|started gpfdist -p 8100 -P 8101 -f "/Users/gpadmin/demos/gpload/upserts/people.txt" -t 30
2013-06-25 22:54:39|INFO|running time: 5.13 seconds
2013-06-25 22:54:39|INFO|rows Inserted = 0
2013-06-25 22:54:39|INFO|rows Updated = 500000
2013-06-25 22:54:39|INFO|data formatting errors = 0
2013-06-25 22:54:39|INFO|gpload succeeded
8. Finally verified we indeed have updated the data in the PEOPLE table.
gpadmin=# select * from apples.people limit 10; id | name ----+------------------ 1 | person1-updated 2 | person2-updated 3 | person3-updated 4 | person4-updated 5 | person5-updated 6 | person6-updated 7 | person7-updated 8 | person8-updated 9 | person9-updated 10 | person10-updated (10 rows)
Thursday, 13 June 2013
Creating a multi threaded insert client for SQLFire 1.1
In this example below we show how to create a multi threaded insert client to insert 1,000,000 records into SQLFire table. In this example below the table is partitioned with synchronous persistence turned on.The distributed system includes one locator and 5 data members.
1. Create Table as shown below
Note: This was run on my MAC laptop which had 5 cache servers running on it. This would perform much better if I had 5 physical machines for each of the SQLFire cache server members.
[Fri Jul 05 22:33:47 papicella@:~/vmware/ant-demos/sqlfire/performance-test ] $ ant
Buildfile: /Users/papicella/vmware/ant-demos/sqlfire/performance-test/build.xml
init:
compile:
package:
run-insert-client:
[echo] Starting insert client with jvm args : -server -showversion -Xms512m -Xmx512m
[java] java version "1.6.0_37"
[java] Java(TM) SE Runtime Environment (build 1.6.0_37-b06-434-11M3909)
[java] Java HotSpot(TM) 64-Bit Server VM (build 20.12-b01-434, mixed mode)
[java]
[java] Starting 8 threads for 1000000 records connecting to jdbc:sqlfire://127.0.0.1:1527/;single-hop-enabled=true
[java] Start: 875000 End: 1000000
[java] Start: 125000 End: 250000
[java] Start: 625000 End: 750000
[java] Start: 500000 End: 625000
[java] Start: 750000 End: 875000
[java] Start: 375000 End: 500000
[java] Start: 250000 End: 375000
[java] Start: 0 End: 125000
[java] Insert batch of 10000 records took | 1541 | milliseconds
[java] Insert batch of 10000 records took | 1544 | milliseconds
[java] Insert batch of 10000 records took | 1548 | milliseconds
[java] Insert batch of 10000 records took | 1551 | milliseconds
[java] Insert batch of 10000 records took | 1552 | milliseconds
[java] Insert batch of 10000 records took | 1554 | milliseconds
[java] Insert batch of 10000 records took | 1560 | milliseconds
[java] Insert batch of 10000 records took | 1568 | milliseconds
[java] Insert batch of 10000 records took | 552 | milliseconds
[java] Insert batch of 10000 records took | 559 | milliseconds
[java] Insert batch of 10000 records took | 563 | milliseconds
[java] Insert batch of 10000 records took | 576 | milliseconds
[java] Insert batch of 10000 records took | 581 | milliseconds
[java] Insert batch of 10000 records took | 618 | milliseconds
[java] Insert batch of 10000 records took | 600 | milliseconds
[java] Insert batch of 10000 records took | 600 | milliseconds
[java] Insert batch of 10000 records took | 630 | milliseconds
[java] Insert batch of 10000 records took | 649 | milliseconds
[java] Insert batch of 10000 records took | 643 | milliseconds
[java] Insert batch of 10000 records took | 656 | milliseconds
[java] Insert batch of 10000 records took | 687 | milliseconds
[java] Insert batch of 10000 records took | 705 | milliseconds
[java] Insert batch of 10000 records took | 705 | milliseconds
[java] Insert batch of 10000 records took | 665 | milliseconds
[java] Insert batch of 10000 records took | 682 | milliseconds
[java] Insert batch of 10000 records took | 612 | milliseconds
[java] Insert batch of 10000 records took | 712 | milliseconds
[java] Insert batch of 10000 records took | 630 | milliseconds
[java] Insert batch of 10000 records took | 646 | milliseconds
[java] Insert batch of 10000 records took | 656 | milliseconds
[java] Insert batch of 10000 records took | 661 | milliseconds
[java] Insert batch of 10000 records took | 686 | milliseconds
[java] Insert batch of 10000 records took | 475 | milliseconds
[java] Insert batch of 10000 records took | 497 | milliseconds
[java] Insert batch of 10000 records took | 499 | milliseconds
[java] Insert batch of 10000 records took | 501 | milliseconds
[java] Insert batch of 10000 records took | 534 | milliseconds
[java] Insert batch of 10000 records took | 530 | milliseconds
[java] Insert batch of 10000 records took | 534 | milliseconds
[java] Insert batch of 10000 records took | 505 | milliseconds
[java] Insert batch of 10000 records took | 534 | milliseconds
[java] Insert batch of 10000 records took | 513 | milliseconds
[java] Insert batch of 10000 records took | 535 | milliseconds
[java] Insert batch of 10000 records took | 550 | milliseconds
[java] Insert batch of 10000 records took | 504 | milliseconds
[java] Insert batch of 10000 records took | 517 | milliseconds
[java] Insert batch of 10000 records took | 528 | milliseconds
[java] Insert batch of 10000 records took | 534 | milliseconds
[java] Insert batch of 10000 records took | 523 | milliseconds
[java] Insert batch of 10000 records took | 531 | milliseconds
[java] Insert batch of 10000 records took | 517 | milliseconds
[java] Insert batch of 10000 records took | 571 | milliseconds
[java] Insert batch of 10000 records took | 598 | milliseconds
[java] Insert batch of 10000 records took | 641 | milliseconds
[java] Insert batch of 10000 records took | 652 | milliseconds
[java] Insert batch of 10000 records took | 650 | milliseconds
[java] Insert batch of 10000 records took | 498 | milliseconds
[java] Insert batch of 10000 records took | 518 | milliseconds
[java] Insert batch of 10000 records took | 542 | milliseconds
[java] Insert batch of 10000 records took | 577 | milliseconds
[java] Insert batch of 10000 records took | 557 | milliseconds
[java] Insert batch of 10000 records took | 529 | milliseconds
[java] Insert batch of 10000 records took | 550 | milliseconds
[java] Insert batch of 10000 records took | 552 | milliseconds
[java] Insert batch of 10000 records took | 525 | milliseconds
[java] Insert batch of 10000 records took | 541 | milliseconds
[java] Insert batch of 10000 records took | 544 | milliseconds
[java] Insert batch of 10000 records took | 522 | milliseconds
[java] Insert batch of 10000 records took | 537 | milliseconds
[java] Insert batch of 10000 records took | 511 | milliseconds
[java] Insert batch of 10000 records took | 554 | milliseconds
[java] Insert batch of 10000 records took | 556 | milliseconds
[java] Insert batch of 10000 records took | 513 | milliseconds
[java] Insert batch of 10000 records took | 503 | milliseconds
[java] Insert batch of 10000 records took | 468 | milliseconds
[java] Insert batch of 10000 records took | 539 | milliseconds
[java] Insert batch of 10000 records took | 581 | milliseconds
[java] Insert batch of 10000 records took | 569 | milliseconds
[java] Insert batch of 10000 records took | 541 | milliseconds
[java] Insert batch of 10000 records took | 544 | milliseconds
[java] Insert batch of 10000 records took | 626 | milliseconds
[java] Insert batch of 10000 records took | 613 | milliseconds
[java] Insert batch of 10000 records took | 662 | milliseconds
[java] Insert batch of 10000 records took | 594 | milliseconds
[java] Insert batch of 10000 records took | 644 | milliseconds
[java] Insert batch of 10000 records took | 637 | milliseconds
[java] Insert batch of 10000 records took | 675 | milliseconds
[java] Insert batch of 10000 records took | 658 | milliseconds
[java] Insert batch of 10000 records took | 608 | milliseconds
[java] Insert batch of 10000 records took | 624 | milliseconds
[java] Insert batch of 10000 records took | 538 | milliseconds
[java] Insert batch of 10000 records took | 511 | milliseconds
[java] Insert batch of 10000 records took | 484 | milliseconds
[java] Insert batch of 10000 records took | 440 | milliseconds
[java] Insert batch of 10000 records took | 462 | milliseconds
[java] Insert batch of 10000 records took | 476 | milliseconds
[java] Elapsed time in seconds 8.502000
BUILD SUCCESSFUL
Total time: 9 seconds
1. Create Table as shown below
drop diskstore store1; CREATE DISKSTORE STORE1; drop table person; create table person (id int primary key, name varchar(40)) PARTITION BY COLUMN (id) REDUNDANCY 1 PERSISTENT 'STORE1' SYNCHRONOUS;2. Multi Threaded Insert client Code.
package com.pivotal.fe.test;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
public class MultiThreadInsert
{
private String url;
private String driverClassName;
private int RECORDS;
private int COMMIT_POINT;
private int nThreads;
public MultiThreadInsert() throws IOException
{
loadProperties();
}
public void loadProperties() throws IOException
{
Properties props = new Properties();
URL propertiesUrl = ClassLoader.getSystemResource("sqlfiretest.properties");
props.load(propertiesUrl.openStream());
url = (String) props.getProperty("url");
driverClassName = (String) props.getProperty("driverclassname");
RECORDS = Integer.parseInt((String) props.getProperty("records"));
COMMIT_POINT = Integer.parseInt((String) props.getProperty("commit_point"));
nThreads = Integer.parseInt((String) props.getProperty("nThreads"));
}
@SuppressWarnings("unchecked")
public void start() throws InterruptedException, SQLException
{
System.out.printf("Starting %d threads for %d records connecting to %s\n", nThreads, RECORDS, url);
final ExecutorService executorService = Executors.newFixedThreadPool(nThreads);
ArrayList list = new ArrayList();
for (int i = 0; i < nThreads; i++) {
list.add(new RunData(url, driverClassName, i+1));
}
long start = System.currentTimeMillis();
List<Future<?>> tasks = executorService.invokeAll(list, 5, TimeUnit.MINUTES);
for(Future<?> f : tasks){
try {
f.get();
} catch (ExecutionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
long end = System.currentTimeMillis() - start;
float elapsedTimeSec = end/1000F;
System.out.println(String.format("Elapsed time in seconds %f", elapsedTimeSec));
executorService.shutdown();
System.exit(0);
}
private class RunData implements Callable
{
int counter = 0;
int increment;
Connection conn;
String url;
String driverClassName;
private RunData(String url, String driverClassName, int increment)
{
this.increment = increment;
this.url = url;
this.driverClassName = driverClassName;
}
public Connection getConnection() throws SQLException, ClassNotFoundException
{
Class.forName(driverClassName);
Connection conn = null;
conn = DriverManager.getConnection(url);
//System.out.println("auto commit = " + conn.getAutoCommit());
return conn;
}
public void run()
{
PreparedStatement stmt = null;
String sql = "insert into person values (?, ?)";
int counter = 0, size = 0;
long startTime, endTime;
int dataSize = RECORDS / nThreads;
try
{
conn = getConnection();
}
catch (Exception e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
System.out.printf("Start: %d End: %d \n",(dataSize * (increment - 1)), (dataSize * increment));
try
{
stmt = conn.prepareStatement(sql);
startTime = System.currentTimeMillis();
for (int i = (dataSize * (increment - 1)); i < (dataSize * increment); i++)
{
counter = counter + 1;
size = size + 1;
stmt.setInt(1, i);
stmt.setString(2, "Person" + i);
stmt.addBatch();
if (counter % COMMIT_POINT == 0)
{
stmt.executeBatch();
endTime = System.currentTimeMillis();
System.out.printf("Insert batch of %d records took | %d | milliseconds\n", size, (endTime - startTime));
startTime = System.currentTimeMillis();
size = 0;
}
}
/* there might be more records so call stmt.executeBatch() prior to commit here */
stmt.executeBatch();
//System.out.printf("Number of records submitted %d.\n", counter);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public Object call() throws Exception
{
run();
return counter;
}
}
/**
* @param args
* @throws InterruptedException
* @throws SQLException
* @throws IOException
*/
public static void main(String[] args) throws InterruptedException, SQLException, IOException
{
// TODO Auto-generated method stub
MultiThreadInsert test = new MultiThreadInsert();
test.start();
}
}
3. Output when run as follows.Note: This was run on my MAC laptop which had 5 cache servers running on it. This would perform much better if I had 5 physical machines for each of the SQLFire cache server members.
[Fri Jul 05 22:33:47 papicella@:~/vmware/ant-demos/sqlfire/performance-test ] $ ant
Buildfile: /Users/papicella/vmware/ant-demos/sqlfire/performance-test/build.xml
init:
compile:
package:
run-insert-client:
[echo] Starting insert client with jvm args : -server -showversion -Xms512m -Xmx512m
[java] java version "1.6.0_37"
[java] Java(TM) SE Runtime Environment (build 1.6.0_37-b06-434-11M3909)
[java] Java HotSpot(TM) 64-Bit Server VM (build 20.12-b01-434, mixed mode)
[java]
[java] Starting 8 threads for 1000000 records connecting to jdbc:sqlfire://127.0.0.1:1527/;single-hop-enabled=true
[java] Start: 875000 End: 1000000
[java] Start: 125000 End: 250000
[java] Start: 625000 End: 750000
[java] Start: 500000 End: 625000
[java] Start: 750000 End: 875000
[java] Start: 375000 End: 500000
[java] Start: 250000 End: 375000
[java] Start: 0 End: 125000
[java] Insert batch of 10000 records took | 1541 | milliseconds
[java] Insert batch of 10000 records took | 1544 | milliseconds
[java] Insert batch of 10000 records took | 1548 | milliseconds
[java] Insert batch of 10000 records took | 1551 | milliseconds
[java] Insert batch of 10000 records took | 1552 | milliseconds
[java] Insert batch of 10000 records took | 1554 | milliseconds
[java] Insert batch of 10000 records took | 1560 | milliseconds
[java] Insert batch of 10000 records took | 1568 | milliseconds
[java] Insert batch of 10000 records took | 552 | milliseconds
[java] Insert batch of 10000 records took | 559 | milliseconds
[java] Insert batch of 10000 records took | 563 | milliseconds
[java] Insert batch of 10000 records took | 576 | milliseconds
[java] Insert batch of 10000 records took | 581 | milliseconds
[java] Insert batch of 10000 records took | 618 | milliseconds
[java] Insert batch of 10000 records took | 600 | milliseconds
[java] Insert batch of 10000 records took | 600 | milliseconds
[java] Insert batch of 10000 records took | 630 | milliseconds
[java] Insert batch of 10000 records took | 649 | milliseconds
[java] Insert batch of 10000 records took | 643 | milliseconds
[java] Insert batch of 10000 records took | 656 | milliseconds
[java] Insert batch of 10000 records took | 687 | milliseconds
[java] Insert batch of 10000 records took | 705 | milliseconds
[java] Insert batch of 10000 records took | 705 | milliseconds
[java] Insert batch of 10000 records took | 665 | milliseconds
[java] Insert batch of 10000 records took | 682 | milliseconds
[java] Insert batch of 10000 records took | 612 | milliseconds
[java] Insert batch of 10000 records took | 712 | milliseconds
[java] Insert batch of 10000 records took | 630 | milliseconds
[java] Insert batch of 10000 records took | 646 | milliseconds
[java] Insert batch of 10000 records took | 656 | milliseconds
[java] Insert batch of 10000 records took | 661 | milliseconds
[java] Insert batch of 10000 records took | 686 | milliseconds
[java] Insert batch of 10000 records took | 475 | milliseconds
[java] Insert batch of 10000 records took | 497 | milliseconds
[java] Insert batch of 10000 records took | 499 | milliseconds
[java] Insert batch of 10000 records took | 501 | milliseconds
[java] Insert batch of 10000 records took | 534 | milliseconds
[java] Insert batch of 10000 records took | 530 | milliseconds
[java] Insert batch of 10000 records took | 534 | milliseconds
[java] Insert batch of 10000 records took | 505 | milliseconds
[java] Insert batch of 10000 records took | 534 | milliseconds
[java] Insert batch of 10000 records took | 513 | milliseconds
[java] Insert batch of 10000 records took | 535 | milliseconds
[java] Insert batch of 10000 records took | 550 | milliseconds
[java] Insert batch of 10000 records took | 504 | milliseconds
[java] Insert batch of 10000 records took | 517 | milliseconds
[java] Insert batch of 10000 records took | 528 | milliseconds
[java] Insert batch of 10000 records took | 534 | milliseconds
[java] Insert batch of 10000 records took | 523 | milliseconds
[java] Insert batch of 10000 records took | 531 | milliseconds
[java] Insert batch of 10000 records took | 517 | milliseconds
[java] Insert batch of 10000 records took | 571 | milliseconds
[java] Insert batch of 10000 records took | 598 | milliseconds
[java] Insert batch of 10000 records took | 641 | milliseconds
[java] Insert batch of 10000 records took | 652 | milliseconds
[java] Insert batch of 10000 records took | 650 | milliseconds
[java] Insert batch of 10000 records took | 498 | milliseconds
[java] Insert batch of 10000 records took | 518 | milliseconds
[java] Insert batch of 10000 records took | 542 | milliseconds
[java] Insert batch of 10000 records took | 577 | milliseconds
[java] Insert batch of 10000 records took | 557 | milliseconds
[java] Insert batch of 10000 records took | 529 | milliseconds
[java] Insert batch of 10000 records took | 550 | milliseconds
[java] Insert batch of 10000 records took | 552 | milliseconds
[java] Insert batch of 10000 records took | 525 | milliseconds
[java] Insert batch of 10000 records took | 541 | milliseconds
[java] Insert batch of 10000 records took | 544 | milliseconds
[java] Insert batch of 10000 records took | 522 | milliseconds
[java] Insert batch of 10000 records took | 537 | milliseconds
[java] Insert batch of 10000 records took | 511 | milliseconds
[java] Insert batch of 10000 records took | 554 | milliseconds
[java] Insert batch of 10000 records took | 556 | milliseconds
[java] Insert batch of 10000 records took | 513 | milliseconds
[java] Insert batch of 10000 records took | 503 | milliseconds
[java] Insert batch of 10000 records took | 468 | milliseconds
[java] Insert batch of 10000 records took | 539 | milliseconds
[java] Insert batch of 10000 records took | 581 | milliseconds
[java] Insert batch of 10000 records took | 569 | milliseconds
[java] Insert batch of 10000 records took | 541 | milliseconds
[java] Insert batch of 10000 records took | 544 | milliseconds
[java] Insert batch of 10000 records took | 626 | milliseconds
[java] Insert batch of 10000 records took | 613 | milliseconds
[java] Insert batch of 10000 records took | 662 | milliseconds
[java] Insert batch of 10000 records took | 594 | milliseconds
[java] Insert batch of 10000 records took | 644 | milliseconds
[java] Insert batch of 10000 records took | 637 | milliseconds
[java] Insert batch of 10000 records took | 675 | milliseconds
[java] Insert batch of 10000 records took | 658 | milliseconds
[java] Insert batch of 10000 records took | 608 | milliseconds
[java] Insert batch of 10000 records took | 624 | milliseconds
[java] Insert batch of 10000 records took | 538 | milliseconds
[java] Insert batch of 10000 records took | 511 | milliseconds
[java] Insert batch of 10000 records took | 484 | milliseconds
[java] Insert batch of 10000 records took | 440 | milliseconds
[java] Insert batch of 10000 records took | 462 | milliseconds
[java] Insert batch of 10000 records took | 476 | milliseconds
[java] Elapsed time in seconds 8.502000
BUILD SUCCESSFUL
Total time: 9 seconds
Tuesday, 14 May 2013
Naming Members in vFabric SQLFire
I find it useful to give a member a meaningful name. In SQLFire you could simply give each member a name by adding a property "name" as follows to the sqlfire.properties file for the member.
sqlfire.properties
# sqlfire.properties for data store or accessor member
license-serial-number=XXXXXXXXXXX
name=server1
Note: The same can be done with GemFire as well.
Then when the system is up the ID for each system member includes the given name as shown below.
sqlfire.properties
# sqlfire.properties for data store or accessor member
license-serial-number=XXXXXXXXXXX
name=server1
Note: The same can be done with GemFire as well.
Then when the system is up the ID for each system member includes the given name as shown below.
sqlf> select substr(id, 1 , 35) as "Member" from sys.members; Member ----------------------------------- 172.16.62.1(server2:38971)<v2>:4265 172.16.62.1(server1:38970)<v1>:1660 127.0.0.1(38744):29535 3 rows selected
Thursday, 2 May 2013
JMX access to vFabric SQLFire
With the release of vFabric SQLFire 11 we can now start a JMX manager with the locator itself. To do that we add the following to the sqlfire.properties file of the locator itself.
jmx-manager=true
jmx-manager-start=true
jmx-manager-ssl=false
jmx-manager-http-port=8083
Then with the locator started we can verify we have it running on the default port of 1099 as shown below.
[Thu May 02 09:45:49 papicella@:~/sqlfire/vFabric_SQLFire_11_b40332/pasdemos/agent-test/locator ] $ netstat -an | grep 1099
tcp4 0 0 127.0.0.1.1099 127.0.0.1.64803 ESTABLISHED
tcp4 0 0 127.0.0.1.1099 127.0.0.1.64801 ESTABLISHED
tcp4 0 0 127.0.0.1.64801 127.0.0.1.1099 ESTABLISHED
tcp4 0 0 127.0.0.1.64803 127.0.0.1.1099 ESTABLISHED
tcp4 0 0 127.0.0.1.1099 127.0.0.1.64799 ESTABLISHED
tcp4 0 0 127.0.0.1.64799 127.0.0.1.1099 ESTABLISHED
tcp46 0 0 *.1099 *.* LISTEN
Finally start jconsole and connect using a service URL as follows
Format:
service:jmx:rmi://{hotname}/jndi/rmi://{hostname}:1099/jmxrmi
Example:
service:jmx:rmi://Pas-Apicellas-MacBook-Pro.local/jndi/rmi://Pas-Apicellas-MacBook-Pro.local:1099/jmxrmi
Once connected you can browse the MBean as shown in the image below.
More Information
http://pubs.vmware.com/vfabric53/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/manage_guide/jmx/jmx_intro.html
jmx-manager=true
jmx-manager-start=true
jmx-manager-ssl=false
jmx-manager-http-port=8083
Then with the locator started we can verify we have it running on the default port of 1099 as shown below.
[Thu May 02 09:45:49 papicella@:~/sqlfire/vFabric_SQLFire_11_b40332/pasdemos/agent-test/locator ] $ netstat -an | grep 1099
tcp4 0 0 127.0.0.1.1099 127.0.0.1.64803 ESTABLISHED
tcp4 0 0 127.0.0.1.1099 127.0.0.1.64801 ESTABLISHED
tcp4 0 0 127.0.0.1.64801 127.0.0.1.1099 ESTABLISHED
tcp4 0 0 127.0.0.1.64803 127.0.0.1.1099 ESTABLISHED
tcp4 0 0 127.0.0.1.1099 127.0.0.1.64799 ESTABLISHED
tcp4 0 0 127.0.0.1.64799 127.0.0.1.1099 ESTABLISHED
tcp46 0 0 *.1099 *.* LISTEN
Finally start jconsole and connect using a service URL as follows
Format:
service:jmx:rmi://{hotname}/jndi/rmi://{hostname}:1099/jmxrmi
Example:
service:jmx:rmi://Pas-Apicellas-MacBook-Pro.local/jndi/rmi://Pas-Apicellas-MacBook-Pro.local:1099/jmxrmi
Once connected you can browse the MBean as shown in the image below.
More Information
http://pubs.vmware.com/vfabric53/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/manage_guide/jmx/jmx_intro.html
Wednesday, 1 May 2013
Explain Plan in vFabric SQLFire improved
With the recently released vFabric SQLFire 11 version the query execution plan is much easier to read then previously. An example below.
[Wed May 01 11:32:11 papicella@:~/sqlfire/vFabric_SQLFire_11_b40332/pasdemos/sqlfire ] $ sqlf
sqlf version 10.4
sqlf> connect peer 'bind-address=localhost;mcast-port=12333;host-data=false' as peerClient;
sqlf> explain select * from emp where deptno = 20;
MEMBER_PLAN
--------------------------------------------------------------------------------------------------------------------------------
ORIGINATOR 192.168.14.167(73118)<v6>:61492 BEGIN TIME 2013-05-01 11:32:39.735 END TIME 2013-05-01 11:32:39.777
DISTRIBUTION to &
Slowest Member Plan:
member 192.168.14.167(72048)<v1>:42223 begin_execution 2013-05-01 11:32:39.74 end_execution 2013-05-01 11:&
Fastest Member Plan:
member 192.168.14.167(72048)<v1>:42223 begin_execution 2013-05-01 11:32:39.74 end_execution 2013-05-01 11:&
3 rows selected
sqlf> select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
STMT_ID |STMT_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
00000001-ffff-ffff-ffff-000400000016| select * from emp where deptno = <?>
1 row selected
sqlf> explain '00000001-ffff-ffff-ffff-000400000016';
stmt_id 00000001-ffff-ffff-ffff-000400000016 SQL_stmt select * from emp where deptno = <?> begin_execution 2013-05-01 11:32:39.735 end_execution 2013-05-01 11:32:39.777
QUERY-SCATTER execute_time 0.0 ms
QUERY-SEND
RESULT-RECEIVE
SEQUENTIAL-ITERATION (0.38%) execute_time 0.136 ms returned_rows 5 no_opens 1
RESULT-HOLDER returned_rows 5 no_opens 1
DISTRIBUTION-END (99.61%) execute_time 35.073 ms returned_rows 5
member 192.168.14.167(72048)<v1>:42223 begin_execution 2013-05-01 11:32:39.74 end_execution 2013-05-01 11:32:39.774
QUERY-RECEIVE
RESULT-SEND
RESULT-HOLDER returned_rows 5 no_opens 1
ROWIDSCAN (1.71%) execute_time 0.148 ms returned_rows 5 no_opens 1 node_details EMP :
CONSTRAINTSCAN (98.28%) execute_time 8.482 ms returned_rows 5 no_opens 1 scan_qualifiers None scanned_object APP.6__EMP__DEPTNO:base-table:APP.EMP scan_type node_details WHERE : ((DEPTNO = CONSTANT:20) and true)
Monday, 15 April 2013
Handling DML Events Synchronously with vFabric SQLFire
SQLFire provides synchronous cache plug-in mechanisms to handle cache events. This example is a synchronous listener. A listener
enables you to receive after-event notifications of changes to a table (insert,
update and delete). Any number of listeners can be defined for the same table.
Listener callbacks are called synchronously, so they will cause the DML
operation to block if the callback blocks.
CommandTableEventCallBackListenerImpl.java
Attach Listener to a table.
More Information
http://pubs.vmware.com/vfabricNoSuite/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/caching_database/cache-plug-ins.html
CommandTableEventCallBackListenerImpl.java
package pivotal.au.demo.poc.listener;
import java.sql.ResultSet;
import java.sql.SQLException;
import pivotal.au.demo.poc.domain.Command;
import pivotal.au.demo.poc.executor.ExecutorCommand;
import pivotal.au.demo.poc.executor.ExecutorFactory;
import com.vmware.sqlfire.callbacks.Event;
import com.vmware.sqlfire.callbacks.Event.Type;
import com.vmware.sqlfire.callbacks.EventCallback;
public class CommandTableEventCallBackListenerImpl implements EventCallback
{
public void close() throws SQLException
{
}
public void init(String configuration) throws SQLException
{
System.out.println("configuration = " + configuration);
System.out.println("CommandTableEventCallBackListenerImpl.init");
}
public void onEvent(Event event) throws SQLException
{
if (event.getType() == Type.AFTER_INSERT)
{
ResultSet rset = event.getNewRowsAsResultSet();
Command cmd =
new Command(rset.getInt(1),
rset.getString(2),
rset.getString(3),
rset.getString(4),
rset.getString(5));
System.out.println("Table[" + event.getTableName() + "] Command = " + cmd.toString());
handleEvent(cmd);
}
else
{
System.out.println("Not processing event " + event.getType().toString());
}
}
private void handleEvent (Command cmd)
{
System.out.println("Handling event for Command with id = " + cmd.getId());
ExecutorCommand execCommand = null;
if (cmd.getType().equalsIgnoreCase("OS"))
{
execCommand = ExecutorFactory.getOSExecutorImpl();
execCommand.runCommand(cmd.getCommand(), null);
}
else
{
// expecting to execute SQL so check if firing on sqlfire or greenplum at this stage
execCommand = ExecutorFactory.getSQLExecutorImpl();
if (cmd.getExecuteOnGreenplum().equalsIgnoreCase("Y"))
{
execCommand.runCommand(cmd.getCommand(), "GP");
}
if (cmd.getExecuteOnSqlfire().equalsIgnoreCase("Y"))
{
execCommand.runCommand(cmd.getCommand(), "SQLFIRE");
}
}
}
}
Attach Listener to a table.
CREATE TABLE command_table
(ID INT generated always as identity NOT NULL,
EXECUTE_ON_SQLFIRE VARCHAR(1) default 'N',
EXECUTE_ON_GREENPLUM VARCHAR(1) default 'Y',
command_type varchar(10),
COMMAND VARCHAR(200) not null
)
SERVER GROUPS (MYGROUP);
call sys.ADD_LISTENER('CommandTableEventCallBackListenerImpl', 'apples', 'command_table', 'pivotal.au.demo.poc.listener.CommandTableEventCallBackListenerImpl', '', 'MYGROUP');
More Information
http://pubs.vmware.com/vfabricNoSuite/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/caching_database/cache-plug-ins.html
Thursday, 11 April 2013
ADO .NET c# Connection Pooling with vFabric SQLFire
Whether it's accessing a database using JAVA or in this case c# I always want to use a connection pool and in this example I show and simple way to do this with a c# ADO .NET client accessing SQLFire.
1. Add a reference to your Visual Studio project in the VMware.Data.SQLFire.dll. This DLL is installed in the
vFabric_SQLFire_11_bNNNNN\adonet\lib directory.
2. Reference the driver namespace in each source file where you want to use SQLFire components. For example, include this directive with all other references required in your application:
3. Create a c# console application as follows
Output as follows
Dept[deptno=10, dname=ACCOUNTING]
Dept[deptno=20, dname=RESEARCH]
Dept[deptno=30, dname=SALES]
Dept[deptno=40, dname=OPERATIONS]
Dept[deptno=50, dname=MARKETING]
Dept[deptno=60, dname=DEV]
Dept[deptno=70, dname=SUPPORT]
Press any key to continue . . .
1. Add a reference to your Visual Studio project in the VMware.Data.SQLFire.dll. This DLL is installed in the
vFabric_SQLFire_11_bNNNNN\adonet\lib directory.
2. Reference the driver namespace in each source file where you want to use SQLFire components. For example, include this directive with all other references required in your application:
using VMware.Data.SQLFire;
3. Create a c# console application as follows
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VMware.Data.SQLFire;
using System.Data;
namespace SQLFireDemo
{
class QueryDemo
{
private string sqlfHost = "192.168.1.4";
private int sqlfPort = 1527;
public QueryDemo()
{
}
private string GetConnectionString()
{
return string.Format(@"server={0}:{1}", sqlfHost, sqlfPort);
}
public void run()
{
using (SQLFClientConnection conn = new SQLFClientConnection(GetConnectionString()))
{
conn.Open();
SQLFCommand command = new SQLFCommand
(string.Format("SELECT * FROM dept"), conn);
SQLFDataReader reader = command.ExecuteReader();
try
{
StringBuilder row = new StringBuilder();
while (reader.Read())
{
Console.WriteLine(string.Format("Dept[deptno={0}, dname={1}]",
reader.GetString(0),
reader.GetString(1)));
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
reader.Close();
}
}
}
static void Main(string[] args)
{
QueryDemo test = new QueryDemo();
test.run();
}
}
}
Output as follows
Dept[deptno=10, dname=ACCOUNTING]
Dept[deptno=20, dname=RESEARCH]
Dept[deptno=30, dname=SALES]
Dept[deptno=40, dname=OPERATIONS]
Dept[deptno=50, dname=MARKETING]
Dept[deptno=60, dname=DEV]
Dept[deptno=70, dname=SUPPORT]
Press any key to continue . . .
Monday, 8 April 2013
vFabric GemFire and the Native Client World using c#
Recently I had to step out of my comfort zone and learn how to create a c# client to access a GemFire 7 distributed system for a demo to a customer. OIf course there was more to it then just that but this outlines what you need to do to connect as a c# client to GemFire. I was using the following here.
https://my.vmware.com/web/vmware/info/slug/application_platform/vmware_vfabric_gemfire/7_0
2. Once installed setup an ENV variable as shown below pointing to the location of the native client install.
C:\Windows\system32>echo %GFCPP%
C:\vFabric_NativeClient_32bit_7010
3. In your Visual Studio 2012 Project / Solution add a reference to GemFire DLL as shown below.
4. In Visual Studio 2012 create a cache.xml as shown below. This client cache is going to use a locator to connect to a cache server instance for the client itself.
xml/cache.xml
5. Create 2 c# classes as shown below.
GemFireClient.cs
GemFireTest.cs
Output omitted but this should give you the general idea.
- Visual Studio 2012
- GemFire 32 bit Naive Client
https://my.vmware.com/web/vmware/info/slug/application_platform/vmware_vfabric_gemfire/7_0
2. Once installed setup an ENV variable as shown below pointing to the location of the native client install.
C:\Windows\system32>echo %GFCPP%
C:\vFabric_NativeClient_32bit_7010
3. In your Visual Studio 2012 Project / Solution add a reference to GemFire DLL as shown below.
xml/cache.xml
<?xml version="1.0"?>
<!DOCTYPE client-cache PUBLIC
"-//GemStone Systems, Inc.//GemFire Declarative Caching 7.0//EN"
"http://www.gemstone.com/dtd/cache7_0.dtd">
<client-cache>
<pool name="client" subscription-enabled="true">
<locator host="172.16.62.1" port="10334" />
</pool>
<region name="CommandRegion">
<region-attributes refid="PROXY" pool-name="client">
</region-attributes>
</region>
<region name="changeTrackingRegion">
<region-attributes data-policy="normal" pool-name="client">
</region-attributes>
</region>
</client-cache>
5. Create 2 c# classes as shown below.
GemFireClient.cs
using GemStone.GemFire.Cache.Generic;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace pivotal.au.company.poc
{
class GemFireClient
{
private static bool isStarted = false;
private static GemFireClient instance = new GemFireClient();
private string configFileLocation = "xml/cache.xml";
private Properties<string, string> properties = Properties<string, string>.Create<string, string>();
private CacheFactory cacheFactory;
private Cache cache;
IRegion<string, string> ctrRegion;
private GemFireClient()
{
Console.WriteLine("Reading properties file xml/cache.xml...");
string clientCacheXml = getCacheConfigLocation(configFileLocation);
properties.Insert("cache-xml-file", clientCacheXml);
Serializable.RegisterPdxSerializer(new ReflectionBasedAutoSerializer());
cacheFactory = CacheFactory.CreateCacheFactory(properties);
cache = cacheFactory.Create();
ctrRegion = cache.GetRegion<string, string>("changeTrackingRegion");
ctrRegion.GetSubscriptionService().RegisterRegex(".");
Console.WriteLine("ctrRegion size = " + ctrRegion.Count);
}
public static GemFireClient getInstance()
{
return instance;
}
public void closeClientCache()
{
cache.Close();
Console.WriteLine("Client Cache closed...");
}
public Cache getCache()
{
return cache;
}
private static string getCacheConfigLocation(string cacheXml)
{
var directoryName = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (File.Exists(System.Environment.GetEnvironmentVariable("COMPANY_CONFIG") + "/" + cacheXml) == true)
{
return System.Environment.GetEnvironmentVariable("COMPANY_CONFIG") + "/" + cacheXml;
}
else if (File.Exists(Path.Combine(directoryName, cacheXml)) == true)
{
return Path.Combine(directoryName, cacheXml);
}
else
{
throw new SystemException("Unable to find /" + cacheXml);
}
}
}
}
GemFireTest.cs
using pivotal.au.company.poc.domain;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GemStone.GemFire.Cache.Generic;
namespace pivotal.au.company.poc
{
class GemFireTest
{
GemFireClient gfClient;
public void doInsert()
{
gfClient = GemFireClient.getInstance();
// get command region
IRegion<string, Command> commandRegion = gfClient.getCache().GetRegion<string, Command>("CommandRegion");
// insert a Command Object into the region
Command command = new Command();
command.eventType = "INSERT";
command.tableName = "Holiday";
command.tableKey = "1";
command.sequence = 31;
command.payload = new Dictionary<object, object>()
{
{"Id", "1"},
{"name", "apples"},
{"createdate", "10-10-2009"}
};
Console.WriteLine(command.ToString());
commandRegion[command.tableKey] = command;
}
public void queryCommandRegion()
{
gfClient = GemFireClient.getInstance();
Console.WriteLine("about to query commandRegion");
QueryService<string, Command> queryService = gfClient.getCache().GetQueryService<string, Command>();
Query<Command> qry = queryService.NewQuery("SELECT * FROM /CommandRegion");
ISelectResults<Command> results = qry.Execute();
SelectResultsIterator<Command> iter = results.GetIterator();
while (iter.MoveNext())
{
Console.WriteLine(iter.Current.ToString());
}
}
public void closeCache()
{
gfClient.closeClientCache();
}
public void run()
{
GemFireTest test = new GemFireTest();
test.doInsert();
test.queryCommandRegion();
test.closeCache();
}
}
}
Output omitted but this should give you the general idea.
Monday, 4 March 2013
Implementing an AsyncEventListener for Write-Behind Cache Event Handling
As part of GemFire 70 release they have introduced as AsyncEventListener for write behind capability which is more or less very similar to the Gateway Listener in GemFire 6.x
An AsyncEventListener receives callbacks for events that change region data. You can use an AsyncEventListener implementation as a write-behind cache event handler to synchronize region updates with a database.
It documented as follows.
http://pubs.vmware.com/vfabricNoSuite/index.jsp?topic=/com.vmware.vfabric.gemfire.7.0/developing/events/implementing_write_behind_event_handler.html
So how would my cache.xml file for a member look like here.
Finally the code to write an AsyncEventListener would be as follows.
An AsyncEventListener receives callbacks for events that change region data. You can use an AsyncEventListener implementation as a write-behind cache event handler to synchronize region updates with a database.
It documented as follows.
http://pubs.vmware.com/vfabricNoSuite/index.jsp?topic=/com.vmware.vfabric.gemfire.7.0/developing/events/implementing_write_behind_event_handler.html
So how would my cache.xml file for a member look like here.
<?xml version="1.0"?>
<!DOCTYPE cache PUBLIC
"-//GemStone Systems, Inc.//GemFire Declarative Caching 7.0//EN"
"http://www.gemstone.com/dtd/cache7_0.dtd">
<cache>
<async-event-queue id="GreenplumQueue" parallel="true" batch-size="500">
<async-event-listener>
<class-name>vmware.pivotal.example.listener.GreenplumGatewayListener</class-name>
</async-event-listener>
</async-event-queue>
<cache-server port="40001" notify-by-subscription="true"/>
<region name="greenplumRegion">
<region-attributes refid="PARTITION_REDUNDANT" async-event-queue-ids="GreenplumQueue"/>
</region>
</cache>
Finally the code to write an AsyncEventListener would be as follows.
package vmware.pivotal.example.listener;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import vmware.pivotal.example.dao.jdbcbatch.JdbcBatch;
import vmware.pivotal.example.dao.jdbcbatch.JdbcBatchDAO;
import com.gemstone.gemfire.cache.Declarable;
import com.gemstone.gemfire.cache.Operation;
import com.gemstone.gemfire.cache.asyncqueue.AsyncEvent;
import com.gemstone.gemfire.cache.asyncqueue.AsyncEventListener;
public class GreenplumGatewayListener implements AsyncEventListener, Declarable
{
private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
private ApplicationContext context;
private static final String BEAN_NAME = "jdbcBatchDAOImpl";
private JdbcBatchDAO jdbcBatchDAO;
public GreenplumGatewayListener()
{
context = new ClassPathXmlApplicationContext("application-context.xml");
jdbcBatchDAO = (JdbcBatchDAO) context.getBean(BEAN_NAME);
logger.log (Level.INFO, "GreenplumGatewayListener started...");
}
@Override
public boolean processEvents(@SuppressWarnings("rawtypes") List<AsyncEvent> list)
{
logger.log (Level.INFO, String.format("Size of List<GatewayEvent> = %s", list.size()));
List<JdbcBatch> newEntries = new ArrayList<JdbcBatch>();
List<JdbcBatch> updatedEntries = new ArrayList<JdbcBatch>();
List<String> destroyedEntries = new ArrayList<String>();
int possibleDulicates = 0;
for (@SuppressWarnings("rawtypes") AsyncEvent ge: list)
{
if (ge.getPossibleDuplicate())
possibleDulicates++;
if ( ge.getOperation().equals(Operation.UPDATE))
{
updatedEntries.add((JdbcBatch) ge.getDeserializedValue());
}
else if ( ge.getOperation().equals(Operation.CREATE))
{
newEntries.add((JdbcBatch) ge.getDeserializedValue());
}
else if ( ge.getOperation().equals(Operation.DESTROY))
{
destroyedEntries.add(ge.getKey().toString());
}
}
if (newEntries.size() > 0)
{
jdbcBatchDAO.storeInsertBatch(newEntries);
}
if (updatedEntries.size() > 0)
{
jdbcBatchDAO.storeUpdateBatch(updatedEntries);
}
if (destroyedEntries.size() > 0)
{
jdbcBatchDAO.storeDeleteBatch(destroyedEntries);
}
logger.log (Level.INFO,
String.format("New Entries = [%s], Updated Entries = [%s], Destroyed Entries = [%s], Possible Duplicates = [%s]",
newEntries.size(),
updatedEntries.size(),
destroyedEntries.size(),
possibleDulicates));
return true;
}
public void init(Properties arg0) {
// TODO Auto-generated method stub
}
public void close() {
// TODO Auto-generated method stub
}
}
Wednesday, 20 February 2013
MyBatis-Spring with vFabric SQLFire
MyBatis-Spring helps you integrate your MyBatis code seamlessly with
Spring. Using the classes in this library, Spring will load the
necessary MyBatis factory and session classes for you. This library also
provides an easy way to inject MyBatis data mappers into your service
beans. Finally, MyBatis-Spring will handle transactions and translate
MyBatis exceptions into Spring DataAccessExceptions.
Here is a simple exampled based on the classic DEPT table.
1. We are working with a DEPT table in SQLFire defined as follows.
DEPT table
2. Setup your pom.xml to include the following.
pom.xml
3. Create the domain modelclass Dept.java, with the standard getter/setters for each attribute. Omitting the full class here.
Dept.java
4.Create a DeptMapper.xml file. I prefer to keep SQL out of the java class wheever I can , hence avoided using annotations here.
DeptMapper.xml
5. Create a DeptMapper interface file. Notice how this is a clean interface and no annotations to specify the SQL we are using as that's in the DeptMapper.xml file above.
DeptMapper.java
6. Create a service class for the DEPT table as shown below.
DeptService.java
7. Create a spring application context file as shown below.
application-context.xml
8. Finally create a test class to verify the setup as shown below.
SpringDeptTest.java
9. Run SpringDeptTest.java and verfy output as shown below.
Feb 20, 2013 9:14:39 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1fff7a1e: startup date [Wed Feb 20 21:14:39 EST 2013]; root of context hierarchy
Feb 20, 2013 9:14:39 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [application-context.xml]
Feb 20, 2013 9:14:40 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@57ac3379: defining beans [dataSource,sqlSessionFactory,transactionManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,deptService,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,org.mybatis.spring.mapper.MapperScannerConfigurer#0,org.springframework.context.annotation.ConfigurationClassPostProcessor$ImportAwareBeanPostProcessor#0,deptMapper]; root of factory hierarchy
New DEPT added to SQLFire
Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
Dept [deptno=30, dname=SALES, loc=CHICAGO]
Dept [deptno=40, dname=OPERATIONS, loc=BRISBANE]
Dept [deptno=50, dname=MARKETING, loc=ADELAIDE]
Dept [deptno=60, dname=DEV, loc=PERTH]
Dept [deptno=70, dname=SUPPORT, loc=SYDNEY]
Dept [deptno=99, dname=MYBATIS-TEST, loc=BUNDOORA]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
The full project structure is as follows showing where the XML files exists in this setup.
Here is a simple exampled based on the classic DEPT table.
1. We are working with a DEPT table in SQLFire defined as follows.
DEPT table
sqlf> describe dept; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ DEPTNO |INTEGER |0 |10 |10 |NULL |NULL |NO DNAME |VARCHAR |NULL|NULL|14 |NULL |28 |YES LOC |VARCHAR |NULL|NULL|13 |NULL |26 |YES 3 rows selected
2. Setup your pom.xml to include the following.
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>sqlfire-mybatis</groupId>
<artifactId>sqlfire-mybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sqlfire-mybatis</name>
<properties>
<spring.version>3.1.2.RELEASE</spring.version>
<mybatis.version>3.1.1</mybatis.version>
<mybatis.spring.version>1.1.1</mybatis.spring.version>
<dbcp.version>1.4</dbcp.version>
<cglib.version>2.2.2</cglib.version>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>${mybatis.spring.version}</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>${dbcp.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>${cglib.version}</version>
</dependency>
</dependencies>
<repositories>
<repository>
<id>mybatis-snapshot</id>
<name>MyBatis Snapshot Repository</name>
<url>https://oss.sonatype.org/content/repositories/snapshots</url>
</repository>
</repositories>
</project>
3. Create the domain modelclass Dept.java, with the standard getter/setters for each attribute. Omitting the full class here.
Dept.java
package pas.au.vmware.se.mybatis.domain;
public class Dept
{
private int deptno;
private String dname;
private String loc;
public Dept()
{
}
public Dept(int deptno, String dname, String loc)
{
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
......
4.Create a DeptMapper.xml file. I prefer to keep SQL out of the java class wheever I can , hence avoided using annotations here.
DeptMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="pas.au.vmware.se.mybatis.mapper.DeptMapper">
<resultMap id="result" type="pas.au.vmware.se.mybatis.domain.Dept">
<result property="deptno" column="DEPTNO"/>
<result property="dname" column="DNAME"/>
<result property="loc" column="LOC"/>
</resultMap>
<select id="getAll" resultMap="result">
SELECT * FROM DEPT
</select>
<select id="getById" parameterType="int" resultMap="result">
SELECT * FROM DEPT WHERE DEPTNO = #{deptno}
</select>
<insert id="insertDept" parameterType="pas.au.vmware.se.mybatis.domain.Dept">
INSERT INTO dept (deptno, dname, loc)
VALUES (#{deptno}, #{dname}, #{loc})
</insert>
</mapper>
5. Create a DeptMapper interface file. Notice how this is a clean interface and no annotations to specify the SQL we are using as that's in the DeptMapper.xml file above.
DeptMapper.java
package pas.au.vmware.se.mybatis.mapper;
import java.util.List;
import pas.au.vmware.se.mybatis.domain.Dept;
public interface DeptMapper
{
public List<Dept> getAll();
public Dept getById (int deptno);
public void insertDept (Dept dept);
}
6. Create a service class for the DEPT table as shown below.
DeptService.java
package pas.au.vmware.se.mybatis.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import pas.au.vmware.se.mybatis.domain.Dept;
import pas.au.vmware.se.mybatis.mapper.DeptMapper;
@Service("deptService")
public class DeptService
{
@Autowired
private DeptMapper deptMapper;
public DeptMapper getDeptMapper() {
return deptMapper;
}
public void setDeptMapper(DeptMapper deptMapper) {
this.deptMapper = deptMapper;
}
public List<Dept> getAll()
{
return getDeptMapper().getAll();
}
public Dept getById(int deptno)
{
return getDeptMapper().getById(deptno);
}
@Transactional
public void insertDept (Dept dept)
{
getDeptMapper().insertDept(dept);
}
}
7. Create a spring application context file as shown below.
application-context.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.vmware.sqlfire.jdbc.ClientDriver" />
<property name="url" value="jdbc:sqlfire://localhost:1527/" />
<property name="username" value="app" />
<property name="password" value="app" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<tx:annotation-driven />
<context:component-scan base-package="pas.au.vmware.se.mybatis.service" />
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="pas.au.vmware.se.mybatis.mapper" />
</bean>
</beans>
8. Finally create a test class to verify the setup as shown below.
SpringDeptTest.java
package pas.au.vmware.se.mybatis.test;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import pas.au.vmware.se.mybatis.domain.Dept;
import pas.au.vmware.se.mybatis.service.DeptService;
public class SpringDeptTest
{
/**
* @param args
*/
public static void main(String[] args)
{
ApplicationContext context = new ClassPathXmlApplicationContext(
"application-context.xml");
DeptService deptService = (DeptService) context.getBean("deptService");
Dept dept = new Dept(99, "MYBATIS-TEST", "BUNDOORA");
deptService.insertDept(dept);
System.out.println("New DEPT added to SQLFire\n");
List<Dept> deps = deptService.getAll();
for (Dept d: deps)
{
System.out.println(d);
}
System.out.println("\n" + deptService.getById(20));
}
}
9. Run SpringDeptTest.java and verfy output as shown below.
Feb 20, 2013 9:14:39 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1fff7a1e: startup date [Wed Feb 20 21:14:39 EST 2013]; root of context hierarchy
Feb 20, 2013 9:14:39 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [application-context.xml]
Feb 20, 2013 9:14:40 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@57ac3379: defining beans [dataSource,sqlSessionFactory,transactionManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,deptService,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,org.mybatis.spring.mapper.MapperScannerConfigurer#0,org.springframework.context.annotation.ConfigurationClassPostProcessor$ImportAwareBeanPostProcessor#0,deptMapper]; root of factory hierarchy
New DEPT added to SQLFire
Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
Dept [deptno=30, dname=SALES, loc=CHICAGO]
Dept [deptno=40, dname=OPERATIONS, loc=BRISBANE]
Dept [deptno=50, dname=MARKETING, loc=ADELAIDE]
Dept [deptno=60, dname=DEV, loc=PERTH]
Dept [deptno=70, dname=SUPPORT, loc=SYDNEY]
Dept [deptno=99, dname=MYBATIS-TEST, loc=BUNDOORA]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
The full project structure is as follows showing where the XML files exists in this setup.
Wednesday, 13 February 2013
Adding JSON Documents into GemFire Cache
The
JSONFormatter API allows you to put JSON formatted
documents into regions and retrieve them later by storing the documents
internally as PdxInstances. vFabric GemFire now supports the use of JSON formatted documents
natively. When you add a JSON document to a GemFire cache, you call the
JSONFormatter APIs to transform them into the PDX format (as a
PdxInstance), which enables GemFire to understand the
JSON document at a field level.
In terms of querying and indexing, because the documents are stored internally as PDX, applications can index on any field contained inside the JSON document including any nested field (within JSON objects or JSON arrays.) Any queries run on these stored documents will return PdxInstances as results. To update a JSON document stored in GemFire, you can execute a function on the PdxInstance.
You can then use the JSONFormatter to convert the PdxInstance results back into the JSON document.
Here is a simple example.
1. server side cache.xml file, in this demo we just start up one cache server
cache.xml
2. Client side cache.xml
client.xml
3. Java class to insert some JSON formatted documents into the cache then how to query them back using a field from the JSON document itself.
JSONGemFireClient.java
4. Run the java class above and verify output as follows
....
[info 2013/02/13 09:41:15.464 EST tid=0x1] Defining: PdxType[
id=1, name=__GEMFIRE_JSON, fields=[
age:byte:0:idx0(relativeOffset)=0:idx1(vlfOffsetIndex)=0
name:String:1:idx0(relativeOffset)=1:idx1(vlfOffsetIndex)=-1]]
Number of entries where age = 30 is -> 5
** JSON data **
Name = Person1
Full JSON data ->
{
"age" : 30,
"name" : "Person1"
}
** JSON data **
Name = Person4
Full JSON data ->
{
"age" : 30,
"name" : "Person4"
}
** JSON data **
Name = Person2
Full JSON data ->
{
"age" : 30,
"name" : "Person2"
}
** JSON data **
Name = Person5
Full JSON data ->
{
"age" : 30,
"name" : "Person5"
}
** JSON data **
Name = Person3
Full JSON data ->
{
"age" : 30,
"name" : "Person3"
}
....
5. Finally use GFSH to query the region itself and you wouldn't even know your using JSON stored documents and would rely on using JSONFormatter to convert the data back into JSON document.
More information can be found on the link below.
http://pubs.vmware.com/vfabricNoSuite/index.jsp?topic=/com.vmware.vfabric.gemfire.7.0/developing/data_serialization/jsonformatter_pdxinstances.html
In terms of querying and indexing, because the documents are stored internally as PDX, applications can index on any field contained inside the JSON document including any nested field (within JSON objects or JSON arrays.) Any queries run on these stored documents will return PdxInstances as results. To update a JSON document stored in GemFire, you can execute a function on the PdxInstance.
You can then use the JSONFormatter to convert the PdxInstance results back into the JSON document.
Here is a simple example.
1. server side cache.xml file, in this demo we just start up one cache server
cache.xml
<?xml version="1.0"?>
<!DOCTYPE cache PUBLIC
"-//GemStone Systems, Inc.//GemFire Declarative Caching 7.0//EN"
"http://www.gemstone.com/dtd/cache7_0.dtd">
<cache>
<cache-server port="40404"/>
<region name="jsonregion">
<region-attributes refid="REPLICATE" />
</region>
</cache>
2. Client side cache.xml
client.xml
<?xml version="1.0"?>
<!DOCTYPE client-cache PUBLIC
"-//GemStone Systems, Inc.//GemFire Declarative Caching 7.0//EN"
"http://www.gemstone.com/dtd/cache7_0.dtd">
<!--
| Client.xml
|
| Configures a region as a client region in a client/server cache. The
| region's pool connects to a cacheserver listening on port 40404.
-->
<client-cache>
<pool name="client" subscription-enabled="true">
<server host="localhost" port="40404" />
</pool>
<region name="jsonregion">
<region-attributes refid="PROXY">
</region-attributes>
</region>
</client-cache>
3. Java class to insert some JSON formatted documents into the cache then how to query them back using a field from the JSON document itself.
JSONGemFireClient.java
package vmware.au.gemfire7.json.client;
import java.util.List;
import org.json.simple.JSONObject;
import com.gemstone.gemfire.cache.Region;
import com.gemstone.gemfire.cache.client.ClientCache;
import com.gemstone.gemfire.cache.client.ClientCacheFactory;
import com.gemstone.gemfire.cache.query.SelectResults;
import com.gemstone.gemfire.pdx.JSONFormatter;
import com.gemstone.gemfire.pdx.PdxInstance;
public class JSONGemFireClient
{
public static final String REGION_NAME = "jsonregion";
public ClientCache cache = null;
public JSONGemFireClient()
{
cache = new ClientCacheFactory()
.set("name", "JSONClient")
.set("cache-xml-file", "xml/client.xml")
.create();
}
@SuppressWarnings("unchecked")
public void run() throws Exception
{
JSONObject obj = null;
System.out.println("Connecting to the distributed system and creating the cache.");
// Get the exampleRegion
Region<String, PdxInstance> jsonregion = cache.getRegion(REGION_NAME);
System.out.println("Example region \"" + jsonregion.getFullPath() + "\" created in cache.");
// add 5 entries with age = 30
for (int i = 1; i <= 5; i++)
{
obj = new JSONObject();
obj.put("name", String.format("Person%s", i));
obj.put("age", 30);
jsonregion.put(String.valueOf(i), JSONFormatter.fromJSON(obj.toJSONString()));
}
// add 5 entries with age = 20
for (int i = 6; i <= 10; i++)
{
obj = new JSONObject();
obj.put("name", String.format("Person%s", i));
obj.put("age", 20);
jsonregion.put(String.valueOf(i), JSONFormatter.fromJSON(obj.toJSONString()));
}
// Query region
SelectResults<PdxInstance> sr = jsonregion.query("age = 30");
System.out.println("Number of entries where age = 30 is -> " + sr.size());
List<PdxInstance> entries = sr.asList();
for (PdxInstance val: entries)
{
System.out.println("\n** JSON data ** ");
System.out.println("Name = " + val.getField("name"));
System.out.println("Full JSON data -> \n" + JSONFormatter.toJSON(val));
}
cache.close();
}
/**
* @param args
*/
public static void main(String[] args)
{
// TODO Auto-generated method stub
JSONGemFireClient test = new JSONGemFireClient();
try
{
test.run();
}
catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
4. Run the java class above and verify output as follows
....
[info 2013/02/13 09:41:15.464 EST
id=1, name=__GEMFIRE_JSON, fields=[
age:byte:0:idx0(relativeOffset)=0:idx1(vlfOffsetIndex)=0
name:String:1:idx0(relativeOffset)=1:idx1(vlfOffsetIndex)=-1]]
Number of entries where age = 30 is -> 5
** JSON data **
Name = Person1
Full JSON data ->
{
"age" : 30,
"name" : "Person1"
}
** JSON data **
Name = Person4
Full JSON data ->
{
"age" : 30,
"name" : "Person4"
}
** JSON data **
Name = Person2
Full JSON data ->
{
"age" : 30,
"name" : "Person2"
}
** JSON data **
Name = Person5
Full JSON data ->
{
"age" : 30,
"name" : "Person5"
}
** JSON data **
Name = Person3
Full JSON data ->
{
"age" : 30,
"name" : "Person3"
}
....
5. Finally use GFSH to query the region itself and you wouldn't even know your using JSON stored documents and would rely on using JSONFormatter to convert the data back into JSON document.
gfsh>connect --jmx-manager=localhost[1099] Connecting to Manager at [host=localhost, port=1099] .. Successfully connected to: [host=localhost, port=1099] gfsh>list regions; List of regions --------------- jsonregion gfsh>query --query="select * from /jsonregion"; Result : true startCount : 0 endCount : 20 Rows : 10 age | name --- | -------- 30 | Person1 30 | Person4 20 | Person8 20 | Person10 20 | Person6 30 | Person2 30 | Person5 20 | Person9 20 | Person7 30 | Person3 NEXT_STEP_NAME : END
More information can be found on the link below.
http://pubs.vmware.com/vfabricNoSuite/index.jsp?topic=/com.vmware.vfabric.gemfire.7.0/developing/data_serialization/jsonformatter_pdxinstances.html
Subscribe to:
Posts (Atom)




