Ben Bromhead sent an email to me directly and expressed an interest in seeing
some of my queries. I may as well post them for everyone. Here are my queries
for the part of my code that reads and cleans up browse trees.
@NamedCqlQueries({
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_CHECK_TREE_EXISTS,
query = "SELECT tree FROM tree WHERE tree = :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_QUORUM
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_ALL_TREES,
query = "SELECT tree, atime, pub, rhpath FROM tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_QUORUM
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_ALL_DOC_BROWSE_TREE,
query = "SELECT tree FROM tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_QUORUM
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_ALL_DOC_BROWSE_NODE,
query = "SELECT hpath, tree FROM node",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_ALL_DOC_BROWSE_INDEX_PAGE,
query = "SELECT page, tree FROM path_by_page",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_ALL_DOC_BROWSE_INDEX_PUB,
query = "SELECT distinct tree, bucket FROM path_by_pub",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_ALL_DOC_BROWSE_INDEX_CHILD,
query = "SELECT distinct phpath, bucket, tree FROM path_by_parent",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_CLEAN_DOC_BROWSE_TREE,
query = "DELETE FROM tree WHERE tree IN :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_CLEAN_DOC_BROWSE_NODE,
query = "DELETE FROM node WHERE hpath IN :hpath AND tree = :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_CLEAN_DOC_BROWSE_INDEX_PAGE,
query = "DELETE FROM path_by_page WHERE page IN :page AND tree = :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_CLEAN_DOC_BROWSE_INDEX_PUB,
query = "DELETE FROM path_by_pub WHERE tree = :tree AND bucket IN :bucket",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_CLEAN_DOC_BROWSE_INDEX_CHILD,
query = "DELETE FROM path_by_parent WHERE phpath = :phpath AND bucket = :bucket
AND tree IN :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_MAX_ORDINAL,
query = "SELECT pord FROM path_by_pub WHERE tree = :tree AND bucket = :bucket
ORDER BY pord DESC",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_QUORUM
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_PAGE,
query = "SELECT page, tree, ord, hpath FROM path_by_page WHERE page = :page AND
tree = :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_PAGE_ALL_TREES,
query = "SELECT page, tree, ord, hpath FROM path_by_page WHERE page = :page",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_NODE,
query = "SELECT tree, hpath, node, ccount FROM node WHERE hpath = :hpath AND
tree = :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_NODE_ALL_TREES,
query = "SELECT tree, hpath, node, ccount FROM node WHERE hpath = :hpath",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_TREE_FOR_HASHPATH,
query = "SELECT tree, node FROM node WHERE hpath = :hpath",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_CHILDREN,
query = "SELECT hpath FROM path_by_parent WHERE phpath = :phpath AND bucket =
:bucket AND tree = :tree AND ord >= :ord",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_ALL_CHILDREN,
query = "SELECT hpath FROM path_by_parent WHERE phpath = :phpath AND bucket =
:bucket AND tree = :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_NEIGHBORS_NEXT,
query = "SELECT hpath FROM path_by_pub WHERE tree = :tree AND bucket = :bucket
AND pord > :pord ORDER BY pord",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_NEIGHBORS_PREVIOUS,
query = "SELECT hpath FROM path_by_pub WHERE tree = :tree AND bucket = :bucket
AND pord < :pord ORDER BY pord DESC",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_HASHPATHS_FOR_TREE,
query = "SELECT hpath FROM path_by_pub WHERE tree = :tree AND bucket = :bucket
ORDER BY pord",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_GET_PAGE_INFO_FOR_TREE,
query = "SELECT page, hpath, ord, pord FROM path_by_pub WHERE tree = :tree AND
bucket = :bucket ORDER BY pord",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_ONE
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_DELETE_NODE,
query = "DELETE FROM node WHERE hpath = :hpath AND tree = :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_QUORUM
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_DELETE_PAGE,
query = "DELETE FROM path_by_page WHERE page = :page AND tree = :tree",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_QUORUM
),
@NamedCqlQuery(
name = DocumentBrowseDaoImpl.Q_DELETE_PUB,
query = "DELETE FROM path_by_pub WHERE tree = :tree AND bucket = :bucket AND
pord = :pord",
keyspace = KeyspaceFamilyImpl.BROWSE,
consistencyLevel = ConsistencyLevel.LOCAL_QUORUM
)
})
The annotations are inspired by JPA, but the design and implementation are my
own. It allows me execute statements with code that looks like this:
ResultSet rs = cassandraUtil.executeNamedQuery(Q_GET_CHILDREN, new
StatementOption(StatementOption.Type.FETCH_SIZE, count),
"phpath", parent.getHashPath(),
"bucket", getBucket(start),
"tree", parent.getTreeId(),
"ord", start
);
cassandraUtil executes statements synchronously or asynchronously, logs errors
and queries that take a long time, and creates histograms of query times for
each statement. It also provides a convenient way of limiting the number of
concurrent asynchronous queries and aggregating the results of parallel
queries. It took some time to write, but is really nice. And in case you’re
wondering, sorry, but I won’t donate it to the open-source community. My
employer has onerous policies governing that, and it’s not really worth the
hassle or my time.
A number of you seemed to have appreciated seeing my data model. I have a
number of upcoming projects to migrate data from MySQL to Cassandra that I’ll
be working on over the next several months. If I have any modeling exercises
that seem unique or particularly clever, I’ll post them for all to enjoy.
Robert
On Mar 28, 2015, at 4:41 PM, Ben Bromhead
<b...@instaclustr.com<mailto:b...@instaclustr.com>> wrote:
Thanks for that Robert. I would love to hear more (e.g. various queries etc)
and even clean it up and publish it on instaclustr.com<http://instaclustr.com/>
We are starting to work with some of our customers as well as those in the
community to collect solutions to common problems that people have implemented
in Cassandra and publish it in a single easily accessible format.
The other people that would probably love to see more on this would be the folk
over at planet cassandra (http://planetcassandra.org/).
Cheers
Ben
