Using GemfireXD with Clojure

Clojure is a dynamic programming language that targets the Java Virtual Machine See here for further details. I like the language for many reasons and some of them are Full access to Java’s rich, mature API, Clojure is a Lisp, so all of the advantages that apply to Lisps apply to Clojure, Because “code is data”, you can do really neat things with macros and many more see here for more of why Clojure is awesome language to learn.

GemFire XD®, built on over a decade of innovation in real-time distributed systems, integrates with Pivotal HD and HAWQ to provide the industry’s first platform for creating closed loop analytics solutions. for more details about GemFire XD® see here.

In this post I am going to show how to access GemFire XD® using Clojure.

Java uses an API called JDBC to access databases, each vendor provides drivers to access their database systems, GemFire XD® uses JDBC driver for access. Download the jar file and place it on your classpath before starting Clojure REPL or Clojure Script

Clojure java.jdbc contains an interface to SQL databases via JDBC, first import the SQL interface

( ns clojure.gemfireXD.jdbc
  (:require [clojure.java.jdbc :as JDBC]))

Define the connection Parameters

def db-spec {:classname "com.vmware.sqlfire.jdbc.ClientDriver"
                :subprotocol "sqlfire"
                :subname "//localhost:1527/"})

You can either use DSL by the java.jdbc package or you can just simple execute any queries you want like below

(defn using-simple-queries
  []  
  (JDBC/db-do-commands db-spec true 
         "create table customer (
        c_w_id         integer        not null,
        c_d_id         integer        not null,
        c_id           integer        not null,
        c_discount     decimal(4,4), c_credit       char(2),
        c_last         varchar(16), c_first        varchar(16),
        c_credit_lim   decimal(12,2), c_balance      decimal(12,2),
        c_ytd_payment  float, c_payment_cnt  integer,
        c_delivery_cnt integer, c_street_1     varchar(20),
        c_street_2     varchar(20), c_city         varchar(20),
        c_state        char(2), c_zip          char(9),
        c_phone        char(16), c_since        timestamp,
        c_middle       char(2), c_data         varchar(500)) partition by (c_w_id) redundancy 1")
  (JDBC/db-do-commands db-spec true 
          "create table new_order ( no_w_id  integer   not null,
        no_d_id  integer   not null,no_o_id  integer   not null) 
        partition by (no_w_id) colocate with (customer) redundancy 1")

  (JDBC/db-do-commands db-spec true "alter table customer add constraint pk_customer 
        primary key (c_w_id, c_d_id, c_id)")

  (JDBC/db-do-commands db-spec true "create index ndx_customer_name 
        on customer (c_w_id, c_d_id, c_last)")

  (JDBC/db-do-commands db-spec true "alter table new_order add constraint pk_new_order
        primary key (no_w_id, no_d_id, no_o_id)")

  (JDBC/db-do-commands db-spec true, "create index ndx_neworder_w_id_d_id
        on new_order (no_w_id, no_d_id)")

  (JDBC/db-do-commands db-spec true "create index ndx_neworder_w_id_d_id_o_id
        on new_order (no_w_id, no_d_id, no_o_id)")
)

Creating a table using java.jdbc of Clojure DSL.

(defn create-table-customer
  [db-spec]
  (JDBC/db-do-commands
    db-spec ( JDBC/create-table-ddl
              :customer
              [:c_w_id         :integer    "not null"]
              [:c_d_id         :integer    "not null"]
              [:c_id           :integer    "not null"]
              [:c_discount     :decimal    "(4,4)"]
              [:c_credit       :char       "(2)"]
              [:c_last         :varchar    "(16)"]
              [:c_first        :varchar    "(16)"]
              [:c_credit_lim   :decimal    "(12,2)"]
              [:c_balance      :decimal    "(12,2)"]
              [:c_ytd_payment  :float]
              [:c_payment_cnt  :integer]
              [:c_delivery_cnt :integer] 
              [:c_street_1     :varchar    "(20)"]
              [:c_street_2     :varchar    "(20)"] 
              [:c_city         :varchar    "(20)"]
              [:c_state        :char       "(2)"]
              [:c_zip          :char       "(9)"]
              [:c_phone        :char       "(16)"] 
              [:c_since        :timestamp]
              [:c_middle       :char       "(2)"]
              [:c_data         :varchar    "(500)"]
              :table-spec "partition by (c_w_id) redundancy 1")))

  (defn create-table-new-order
    [db-spec]
    (JDBC/db-do-commands 
      db-spec
      (JDBC/create-table-ddl
        :new_order
        [:no_w_id  :integer   "not null"]
        [:no_d_id  :integer   "not null"]
        [:no_o_id  :integer   "not null"]
        :table-spec "partition by (no_w_id) colocate with (customer) redundancy 1")))  

Here is the complete working code

( try
  (JDBC/db-do-commands db-spec (JDBC/drop-table-ddl :new_order))
  (JDBC/db-do-commands db-spec (JDBC/drop-table-ddl :customer))
  (catch Exception _ ;;ignore 
  ))
(create-table-customer db-spec)
(create-table-new-order db-spec)

(JDBC/db-do-commands db-spec true "alter table customer add constraint pk_customer primary key (c_w_id, c_d_id, c_id)")

(JDBC/db-do-commands db-spec true "create index ndx_customer_name on customer (c_w_id, c_d_id, c_last)")

(JDBC/db-do-commands db-spec true "alter table new_order add constraint pk_new_order primary key (no_w_id, no_d_id, no_o_id)")

(JDBC/db-do-commands db-spec true "create index ndx_neworder_w_id_d_id on new_order (no_w_id, no_d_id)")

(JDBC/db-do-commands db-spec true "create index ndx_neworder_w_id_d_id_o_id on new_order (no_w_id, no_d_id, no_o_id)")

(JDBC/db-do-prepared db-spec true "insert into new_order values (?, ?, ?)" [1 1 1][2 2 2][3 3 3][4 4 4][5 5 5][6 6 6][7 7 7][8 8 8][9 9 9][10 10 10])

(println (JDBC/query db-spec ["SELECT * FROM new_order"] :result-set-fn count))

and there you go start using GemFire XD® using Clojure. Clojure Java.jdbc also provides api for accesing Database Metadata.
One can also use Clojure Connecton pooling Clojure JDBC pool

Advertisements