meetup cassandra sfo_jdbc

PowerPoint Presentation

How to Integrate Apache Cassandra's CQL with your Existing Java Application

Nate [email protected]@zznate

CQL via JDBC

(Personal) Motivations for Pushing this Approach:

- Make Cassandra useful to wide variety of developers

- Easily usable from most common application paradigms

* web/ejb containers

* service layers

- Integrate with existing APIs

* Spring Framework's JdbcTemplate in this case

CQL via JDBC: Status

You can start using it, but...

- Inflexible typing

- Almost no meta data (result or database level)

- Other rough edges

- Still going over the Thrift API (execute_cql_query)

Spend some time knowing what you are getting into

CQL JDBC Driver: What's Missing

Very limited typing support:

- Most type specific mutators on Statement interface.

Currently supported types:

* string

* short, int and long

* bytes

* object

* rowId


ResultSetMetaData and DatabaseMetadata

- Not available in CQL yet

- Lot's of tool APIs need are meta data driven


Audience Participation!Assuming these are the only 3 regex's used to parse statements:

...

private static final Pattern Select = Pattern.compile("SELECT...

private static final Pattern Update = Pattern.compile("UPDATE...

private static final Pattern Delete = Pattern.compile("DELETE...

CQL JDBC Driver

Current worst design warts (IMO):- Relies on CF metadata for *all* typing loaded statically at init

- Does not respect calls to typed method in Statement hierarchy (easy to fix)

- Creates cart-before-horse issue wrt test setup

CQL JDBC Driver: What Works

Driver implementation and setup

- Meaningful URL semantics

- Completely encapsulates Thrift

- Compression (specified from the Thrift API)

- Statement parsing (except insert!)


Working ResultSet implementation:

- CassandraResultSet convenience methods

* getColumn/findColumn methods * getKey (but only in bytes!)


Some useful controls on Statement hierarchy:

- Allows for fetchDirection and fetchSize on statement

- addBatch()

- clearParameters()

CQL JDBC: Making Use of the Driver

- Need to pool connections to be useful- No existing librariers out there were Cassandra flavored

- We knew a little bit about writing Cassandra clients

CQL JDBC: Cassandra-jdbc-pool (CJP)

Lots of functionality specific to Cassandra architecture:

- Cluster name, keyspace and at least 1 host required

- Additional settings for:

* fail over semantics

* automatic host discovery

* timeout counters and thresholds

CQL JDBC: CJP Configuration (JNDI)

CQL JDBC: CJP Configuration (Spring)

CQL JDBC and Spring Framework

JdbcTemplate FTW!

- mature: Since: May 3, 2001

- easy to understand with lots of examples

- well documented

http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html

CQL Spring Framework: JdbcTemplate

Easiest approach: manual string formatting- can do what you want

- BYO encoding

- String argument versions of:

- update

- execute

- batchUpdate: array of string statements

- queryForInt: count operations

- queryForList: multiple rows

- queryForMap: single row

CQL and JdbcTemplate

private static final String STOCK_CQL =

select price FROM Stocks WHERE KEY = ?";

jdbcTemplate.query(STOCK_CQL, stockTicker,

new RowMapper() {

public Stock mapRow(ResultSet rs, int row) throws SQLException {

CassandraResultSet crs = (CassandraResultSet)rs;

Stock stock = new Stock();

stock.setTicker(new String(crs.getKey()));

stock.setPrice(crs.getDouble("price"));

return stock;

}

});

Reading Data via RowMapper:


Updates using an object array and positional placeholders

private static String UPDATE_PORTOFOLIO_CQL =

"update Portfolios set ? = ? where KEY = ?";

jdbcTemplate.update(UPDATE_PORTFOLIO_CQL,

new Object[] {position.getTicker(),

position.getCount(),

portfolio.getName()});


Batch Update with BatchPreparedStatementSetter

private static final String UPDATE_PORT_CQL =

"update Portfolios set ? = ? where KEY = ?";

jdbcTemplate.batchUpdate(UPDATE_PORT_CQL,

new BatchPreparedStatementSetter() {

public void setValues(PreparedStatement ps, int index) throws SQLException {

Position pos = portfolio.getConstituents().get(index);

ps.setString(1, pos.getTicker());

ps.setLong(2, pos.getShares());

ps.setString(3,portfolio.getName());

}

public int getBatchSize() {

return portfolio.getConstituents().size();

}

});


Deleting with BatchPreparedStatementSetter

private static final String DELETE_PORT_COLUMN_CQL =

"delete ? From Portfolios where KEY = ?";

jdbcTemplate.batchUpdate(UPDATE_PORT_COLUMN_CQL,

new BatchPreparedStatementSetter() {

public void setValues(PreparedStatement ps, int index) throws SQLException {

Position pos = portfolio.getConstituents().get(index);

ps.setString(1, pos.getTicker());

ps.setString(2,portfolio.getName());

}

public int getBatchSize() {

return listOfTickersToDelete.size();

}

});

CQL and JDBC

Summary:

- More than a proof of concept

- Not quite production ready

- Rough edges remain

- Ready for experimentation, test drives and bug hunting

*** Lot's of folks are now/will always be happy with the existing Thrift API

Data Model

GOOG

AAPL

NFLX

NOK

price: 589.55

price: 401.76

price: 78.73

name: Google

name: Apple

name: Netflix

price: 6.90

name: Nokia

exchange: NYSE

Portfolio

Data Model

GOOG

AAPL

NFLX

NOK

price: 589.55

price: 401.76

price: 78.73

name: Google

name: Apple

name: Netflix

price: 6.90

name: Nokia

exchange: NYSE

Stocks

Data Model

StockHist

10/25/2011: 6.71

GOOG

AAPL

NFLX

NOK

10/24/2011: 6.76

10/21/2011: 6.61

10/25/2011: 77.37

10/24/2011: 118.84

10/21/2011: 117.04

10/25/2011: 397.77

10/24/2011: 405.77

10/21/2011: 392.87

10/25/2011: 583.16

10/24/2011: 596.42

10/21/2011: 590.49

CQL via JDBC: Components

- HCQLDataSource (from jdbc-pool)

- Spring Framework's JdbcTemplate

- DAO class with associated domain objects

- Junit

- Spring Framework's SpringJUnit4ClassRunner (context setup and injection)

- EmbededServerHelper from hector-test (manage Cassandra lifecycle, directories and configuration)

Development Resources

CQL Documentation (and CQL Shell)
http://www.datastax.com/docs/1.0/dml/using_cqlHector Documentation
http://hector-client.orgCassandra Maven Plugin (exec-cql goal)
http://mojo.codehaus.org/cassandra-maven-plugin/CCM localhost cassandra cluster
https://github.com/pcmanus/ccmOpsCenter
http://www.datastax.com/products/opscenter

Cassandra AMIs
https://github.com/riptano/CassandraClusterAMI

Questions

Nate [email protected]@zznate

meetup cassandra sfo_jdbc

Technology