Monday, 14 October 2013

Pivotal SQLFire Rowloader for Greenplum

When you use SQLFire as a cache, you can configure a SQL data loader that is triggered to load data from a backend repository on a miss in SQLFire. When an incoming query request for a uniquely-identified row cannot be satisfied by the distributed cache, the loader is invoked to retrieve the data from an external source. SQLFire locks the associated row and prevents concurrent readers that are trying to fetch the same row from overloading the backend database.

The example below is using Greenplum as the main source database. The example is based on this MYSQL example

http://blogs.vmware.com/vfabric/2012/01/using-sqlfire-as-a-read-only-cache-for-mysql.html

1. In this example we use a Greenplum table as follows, showing first 10 rows only, 500K of rows exist.

  
gpadmin=# SELECT * FROM apples.person limit 10;
 id |   name   
----+----------
  2 | person2
  4 | person4
  6 | person6
  8 | person8
 10 | person10
 12 | person12
 14 | person14
 16 | person16
 18 | person18
 20 | person20
(10 rows)

Time: 11.947 ms

2. Add Postgress JDBC driver to the classpath of the SQLFire server nodes as well as the examples shipped with SQLFire in this case called "examples.jar". Example shown in the post above.


[Mon Oct 14 22:17:33 papicella@:~/sqlfire/final11build/vFabric_SQLFire_111_b42624/pasdemos/fire-rowloader/lib ] $ d
total 1248
-rw-r--r--@  1 papicella  staff  579785 13 Oct 20:01 postgresql-9.2-1002.jdbc4.jar
-rw-r--r--   1 papicella  staff   57203 13 Oct 20:05 examples.jar
drwxr-xr-x   4 papicella  staff     136 13 Oct 20:05 ./
drwxr-xr-x  16 papicella  staff     544 13 Oct 22:01 ../



3. From the table above that exists in a schema called "apples" so we must create the same table in SQLFire using the same schema name as shown below.
  
create schema apples;

set schema apples;

CREATE TABLE person
(id int, name varchar(200), primary key (id))
PARTITION BY PRIMARY KEY
EVICTION BY LRUCOUNT 500000 EVICTACTION DESTROY;

show tables in apples;

4. Create a row loader as shown below.
  
set schema apples;

call sys.attach_loader('APPLES', 'PERSON', 'examples.JDBCRowLoader', '|url=jdbc:postgresql://127.0.0.1:5432/gpadmin|query-string=SELECT * FROM apples.person WHERE id=?|user=pas|password=pas|min-connections=5|max-connections=100'); 

5. Run a test as shown below.
  
sqlf> set schema apples;
0 rows inserted/updated/deleted
sqlf> select * from person where id = 100;
ID         |NAME                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------
100        |person100                                                                                                                       

1 row selected
sqlf> select * from person;
ID         |NAME                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------
100        |person100                                                                                                                       

1 row selected  

6. Finally the log file will show the connections being established , in this case 5 as a Minimum.
  
[info 2013/10/14 21:47:16.600 EST  <Pooled Waiting Message Processor 2> tid=0x52] (tid=11 msgId=1) JDBCRowLoader initialized.

[info 2013/10/14 21:47:16.600 EST  <Pooled Waiting Message Processor 2> tid=0x52] (tid=11 msgId=2)    user: pas

[info 2013/10/14 21:47:16.600 EST  <Pooled Waiting Message Processor 2> tid=0x52] (tid=11 msgId=3)    password: xxx

[info 2013/10/14 21:47:16.600 EST  <Pooled Waiting Message Processor 2> tid=0x52] (tid=11 msgId=4)    url: jdbc:postgresql://127.0.0.1:5432/gpadmin

[info 2013/10/14 21:47:16.601 EST  <Pooled Waiting Message Processor 2> tid=0x52] (tid=11 msgId=5)    min-connections: 5

[info 2013/10/14 21:47:16.601 EST  <Pooled Waiting Message Processor 2> tid=0x52] (tid=11 msgId=6)    max-connections: 100

[info 2013/10/14 21:47:16.601 EST  <Pooled Waiting Message Processor 2> tid=0x52] (tid=11 msgId=7)    query-string: SELECT * FROM apples.person WHERE id=?

[info 2013/10/14 21:47:16.604 EST  <Pooled Waiting Message Processor 2> tid=0x52] AbstractSqlfReplayableMessage: Successfully executed message with fields: SqlfSetLoaderMessage@6127ffd7(processorId=53; processorType=77;posDup=false; replayKey=201; schema = APPLES; table = PERSON; implementation = examples.JDBCRowLoader; initInfoStr = |url=jdbc:postgresql://127.0.0.1:5432/gpadmin|query-string=SELECT * FROM apples.person WHERE id=?|user=pas|password=pas|min-connections=5|max-connections=100)

[info 2013/10/14 21:48:55.513 EST  <Pooled Waiting Message Processor 3> tid=0x53] Initializing region _B__APPLES_PERSON_100

[info 2013/10/14 21:48:55.530 EST  <PartitionedRegion Message Processor2> tid=0x56] (tid=12 msgId=8) JDBCRowLoader invoked to fetch from schema <APPLES> on table <PERSON>.

[info 2013/10/14 21:48:55.530 EST  <PartitionedRegion Message Processor2> tid=0x56] (tid=12 msgId=9)  primary key element 0: 100

[info 2013/10/14 21:48:55.566 EST  <pool-1-thread-6> tid=0x5c] (tid=13 msgId=10)  Successful connection to target database: jdbc:postgresql://127.0.0.1:5432/gpadmin

[info 2013/10/14 21:48:55.566 EST  <pool-1-thread-1> tid=0x57] (tid=15 msgId=12)  Successful connection to target database: jdbc:postgresql://127.0.0.1:5432/gpadmin

[info 2013/10/14 21:48:55.566 EST  <pool-1-thread-2> tid=0x58] (tid=18 msgId=15)  Successful connection to target database: jdbc:postgresql://127.0.0.1:5432/gpadmin

[info 2013/10/14 21:48:55.566 EST  <pool-1-thread-5> tid=0x5b] (tid=14 msgId=11)  Successful connection to target database: jdbc:postgresql://127.0.0.1:5432/gpadmin

[info 2013/10/14 21:48:55.566 EST  <pool-1-thread-3> tid=0x59] (tid=16 msgId=13)  Successful connection to target database: jdbc:postgresql://127.0.0.1:5432/gpadmin

[info 2013/10/14 21:48:55.566 EST  <pool-1-thread-4> tid=0x5a] (tid=17 msgId=14)  Successful connection to target database: jdbc:postgresql://127.0.0.1:5432/gpadmin

[info 2013/10/14 21:48:55.791 EST  <PartitionedRegion Message Processor2> tid=0x56] (tid=12 msgId=16) Executing query SELECT * FROM apples.person WHERE id=100

[info 2013/10/14 21:48:55.829 EST  <PartitionedRegion Message Processor2> tid=0x56] (tid=12 msgId=17) Query succeeded 

For more information refer to the link below.

http://pubs.vmware.com/vfabric53/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/developers_guide/topics/cache/rowloader.html

No comments: