DBD::Cassandra(3pm)

💖 DBD::Cassandra 💖

okay bestie, gather round, because this is the Perl driver that lets you talk to Cassandra using CQL3, and honestly? it's giving database royalty. the module is literally called DBD::Cassandra — a DBI database backend for Cassandra. 🫶

Version: 0.57 ✨ (yes, we're on 0.57, iconic behavior)

---

🌸 DESCRIPTION 🌸

DBD::Cassandra is a Perl5 Database Interface driver for Cassandra, and it speaks CQL3 (the Cassandra Query Language, version 3) like it was born doing it. no cap.

---

✨ EXAMPLE ✨

here's the whole vibe, start to finish:

use DBI;

my $dbh = DBI->connect("dbi:Cassandra:host=localhost;keyspace=test", $user, $password, { RaiseError => 1 });
my $rows = $dbh->selectall_arrayref("SELECT id, field_one, field_two FROM some_table");

for my $row (@$rows) {
    # Do something with your row
}

$dbh->do("INSERT INTO some_table (id, field_one, field_two) VALUES (?, ?, ?)",
    { Consistency => "quorum" },
    1, "String value", 38962986
);

$dbh->disconnect;

connect, select, loop, insert, disconnect. the full main character arc. 👑

---

🎀 Configuration

Database handles

use DBI;

$dsn = "dbi:Cassandra:database=$database";
$dsn = "dbi:Cassandra:keyspace=$keyspace;host=$hostname;port=$port";
$dsn = "dbi:Cassandra:keyspace=$keyspace;consistency=local_quorum";

my $dbh = DBI->connect($dsn, $username, $password);

look at those DSNs. so many ways to say hello to your database. 💅

now the options, one by one, no skipping, we don't do that here:

• keyspace, database, db — optionally, a keyspace to use by default. and if you don't set this? then literally every single query you write has to include the keyspace name. so like, set it. save yourself the pain. 🙈
• hostname, hosts — the hostname to initially connect to. defaults to "localhost". and plot twist: you can make it comma-separated to specify multiple hosts. ✨
• port — the port number to connect to. defaults to 9042.
• compression — the compression method we should use for the connection. right now Cassandra allows "lz4" and "snappy". it defaults to the algorithm with the best compression ratio, but only if the server supports it. wanna turn compression off completely? set compression=none and she's off. 🦋 also important: this is only used for data frames longer than 512 bytes — smaller frames get sent uncompressed. size matters, apparently.
• cql_version — there are several versions of the CQL language and this option lets you pick one. defaults to the highest available version. consult your Cassandra manual to see which versions your database actually supports (do your research bestie 📚).
• consistency — see the chapter on consistency levels 👇
• request_timeout — maximum amount of time (in seconds) to wait for a Cassandra network operation to finish. patience is a virtue but so is a timeout.
• read_timeout, write_timeout — deprecated. ⚠️ these two get summed together and used as request_timeout. they're old news, don't build your life around them.
• tls — Boolean (1|0); whether to use TLS. defaults to off.

Statement handles

my $sth= $dbh->prepare('SELECT "id", "field1", "field2" FROM table_name WHERE id=?', { Consistency => 'one' });

per-statement options, because sometimes you want different vibes for different queries:

• async — see "asynchronous queries". 😴
• consistency — see "consistency levels".
• per_page — Cassandra supports pagination through result sets, so you don't have to hold the entire result set in memory at once. queen of not stressing your RAM.

my $sth = $dbh->prepare('SELECT id FROM tablename', { PerPage => 1000 });
$sth->execute;
while (my $row = $sth->fetchrow_arrayref()) {
    print "$row->[0]\n";
}

important bestie: keep in mind that this mode can cause errors while fetching rows, because extra queries may be executed by the driver internally. so like, it's cute but it's got ✨layers✨.

---

🌈 COLLECTION TYPES 🌈

Cassandra supports collection types natively — things like "list" and "map" — and DBD::Cassandra translates them to native Perl types, like hashes and arrays. the crossover event we deserved. 🔥

when doing queries, placeholders can be substituted by these collections. for example, inserting a map into a table is done by passing a Perl hash:

my $sth= $dbh->prepare('INSERT INTO some_table (id, value) VALUES (?,?);');
$sth->execute(5, { days => 15 });

and this also works for "IN" queries, which accept an array:

my $sth= $dbh->prepare('SELECT id, value FROM some_table WHERE id IN ?');
$sth->execute([1, 2, 3]);
my $rows= $sth->fetchall_arrayref();

arrays go in, arrays go in. hash goes in, map goes in. it just works. 💜

---

😴 ASYNCHRONOUS QUERIES 😴

my $sth= $dbh->prepare("SELECT id FROM some_table WHERE x=?",
    { async => 1 });
$sth->execute(5);

some_other_function();

while (my $row = $sth->fetchrow_arrayref()) {
    print "$row->[0]\n";
}

DBD::Cassandra supports asynchronous queries in an easy to use form. when async => 1 is passed to prepare(), any subsequent executes on the handle are not read back immediately. instead, these are delayed until the result is actually needed. it's the "i'll deal with it later" of database drivers and honestly? relatable.

for inserts and other writes, there's a convenience method called x_finish_async, which returns an approximation to what execute() would have returned in a non-asynchronous context. this method also raises errors, if needed. so it's not just ignoring your problems, it's reporting them. 🫡

my $sth= $dbh->prepare("INSERT INTO table (a, b) VALUES (?, ?)",
    { async => 1 });
$sth->execute(5, 6);

some_other_function_that_takes_a_while();

$sth->x_finish_async;

⚡ Performance considerations ⚡

when using asynchronous queries, some previously premature optimizations become relevant (the way that procrastination-adjacent code suddenly becomes good code 💀). for example, it is very helpful to re-use statement handles in large volumes of inserts:

my @dataset_to_insert= ([1, 2, 3, 4], [5, 6, 7, 8]);
my (@pending, @reusable);

while (my $row= shift @dataset_to_insert) {
    my $sth= (shift @reusable) || $dbh->prepare(
        "INSERT INTO some_table (a, b, c, d) VALUES (?, ?, ?, ?)"
    );
    $sth->execute(@$row);
    push @pending, $sth;

    if (@pending > 500) { # Tune this number!
        my $pending_sth= shift @pending;
        $pending_sth->x_finish_async;
        push @reusable, $pending_sth;
    }
}

$_->x_finish_async for @pending;

recycle those handles. earth-friendly, database-friendly. 🌱

---

💅 CONSISTENCY LEVELS 💅

$dbh->do("INSERT INTO some_table (id, field_name) VALUES (?, ?)",
    { Consistency => "quorum" },
    @values
);

DBD::Cassandra accepts a Consistency attribute for statements. the supported consistency levels are: "any", "one", "two", "three", "quorum", "all", "local_quorum", "each_quorum", "serial", "local_serial" and "local_one". pick your fighter. 🥊

this attribute is ignored on statements that do not support it, such as "CREATE". she simply doesn't apply there, no hard feelings.

a global consistency level can also be defined as part of the DSN. set it once, forget it. ✨

---

💀 CAVEATS, BUGS, TODO 💀

• there is currently no support for transactions. begin_work will die if you try to use it. like, straight up. don't try it. 😭
• Thread support is untested. use at your own risk. you have been warned, bestie. ⚠️
• the "timestamp" format is implemented naively by returning milliseconds since the UNIX epoch. in Perl you get this number through time() * 1000. trying to save times as DateTime objects or strings will not work, and will likely result in warnings and unexpected behavior. it's giving chaos. 🙃
• when using asynchronous queries, more functions than just execute() may throw errors. it is recommended that you enable RaiseError. if this is not possible, it should also suffice to call $sth->x_finish_async and check its return value before reading any data from the handle. check before you read, always. 👑
• Cassandra/CQL3 is strict about the queries you write. when switching from other databases, such as MySQL, this may come as a surprise (a rude one 🫠). this module supports quote(..), but try to use prepared statements instead. they will save you a lot of trouble. fr fr.

---

🌱 UPGRADING 🌱

From versions 0.25 and lower

as of DBD::Cassandra 0.51, this module uses Cassandra::Client internally. the unit tests from the previous release all still pass, but there are subtle changes:

• read_timeout/write_timeout are deprecated, use request_timeout instead
• the driver now manages a pool of connections internally
  • instead of only connecting to the one specified host, multiple hosts can be passed as seed-hosts. these are then used to bootstrap the actual internal pool of connections. it's giving glow-up. ✨

From versions 0.24 and lower

prior to version 0.25 there was a bug corrupting float and double values as they were stored in the database. the endianness on these values was wrong, which only shows when reading stored data back in an application written using a different driver. sneaky little bug. 🐛

if you were writing float or double values using a DBD::Cassandra prior to 0.25, please be careful with this upgrade. a way to rewrite your values between the two formats is:

my $good_float = unpack('f>', pack('f', $bad_float));
my $good_double= unpack('d>', pack('d', $bad_double));

if you never used a DBD::Cassandra version prior to 0.25, or do not use floats or doubles, this bug does not affect you and upgrading to 0.25 is safe. you're fine, babe. 🫶

---

👑 AUTHOR 👑

Tom van der Woerdt <tvdw@cpan.org>

📜 COPYRIGHT AND LICENSE 📜

This software is copyright (c) 2017 by Tom van der Woerdt.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

---

and that's the whole page, bestie!! go forth and query responsibly. this masterpiece was rendered for perl v5.36.0 and dated 2023-07-25, and it's the DBD::Cassandra(3pm) manual — slay, periodt, 💅✨🦋💖