Cassandra connector

The Cassandra connector allows querying data stored in Apache Cassandra.

Requirements

To connect to Cassandra, you need:

• Cassandra version 3.0 or higher.
• Network access from the Trino coordinator and workers to Cassandra. Port 9042 is the default port.

Configuration

To configure the Cassandra connector, create a catalog properties file etc/catalog/example.properties with the following contents, replacing host1,host2 with a comma-separated list of the Cassandra nodes, used to discovery the cluster topology:

connector.name=cassandra
cassandra.contact-points=host1,host2
cassandra.load-policy.dc-aware.local-dc=datacenter1

You also need to set cassandra.native-protocol-port, if your Cassandra nodes are not using the default port 9042.

Multiple Cassandra clusters

You can have as many catalogs as you need, so if you have additional Cassandra clusters, simply add another properties file to etc/catalog with a different name, making sure it ends in .properties. For example, if you name the property file sales.properties, Trino creates a catalog named sales using the configured connector.

Configuration properties

The following configuration properties are available:

:::{note} If authorization is enabled, cassandra.username must have enough permissions to perform SELECT queries on the system.size_estimates table. :::

The following advanced configuration properties are available:

Querying Cassandra tables

The users table is an example Cassandra table from the Cassandra Getting Started guide. It can be created along with the example_keyspace keyspace using Cassandra's cqlsh (CQL interactive terminal):

cqlsh> CREATE KEYSPACE example_keyspace
   ... WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
cqlsh> USE example_keyspace;
cqlsh:example_keyspace> CREATE TABLE users (
              ...   user_id int PRIMARY KEY,
              ...   fname text,
              ...   lname text
              ... );

This table can be described in Trino:

DESCRIBE example.example_keyspace.users;

 Column  |  Type   | Extra | Comment
---------+---------+-------+---------
 user_id | bigint  |       |
 fname   | varchar |       |
 lname   | varchar |       |
(3 rows)

This table can then be queried in Trino:

SELECT * FROM example.example_keyspace.users;

(cassandra-type-mapping)=

Type mapping

Because Trino and Cassandra each support types that the other does not, this connector {ref}modifies some types <type-mapping-overview> when reading or writing data. Data types may not map the same way in both directions between Trino and the data source. Refer to the following sections for type mapping in each direction.

Cassandra type to Trino type mapping

The connector maps Cassandra types to the corresponding Trino types according to the following table:

:::{list-table} Cassandra type to Trino type mapping :widths: 30, 25, 50 :header-rows: 1

• • Cassandra type
  • Trino type
  • Notes
• • BOOLEAN
  • BOOLEAN
• • TINYINT
  • TINYINT
• • SMALLINT
  • SMALLINT
• • INT
  • INTEGER
• • BIGINT
  • BIGINT
• • FLOAT
  • REAL
• • DOUBLE
  • DOUBLE
• • DECIMAL
  • DOUBLE
• • ASCII
  • VARCHAR
  • US-ASCII character string
• • TEXT
  • VARCHAR
  • UTF-8 encoded string
• • VARCHAR
  • VARCHAR
  • UTF-8 encoded string
• • VARINT
  • VARCHAR
  • Arbitrary-precision integer
• • BLOB
  • VARBINARY
• • DATE
  • DATE
• • TIME
  • TIME(9)
• • TIMESTAMP
  • TIMESTAMP(3) WITH TIME ZONE
• • LIST<?>
  • VARCHAR
• • MAP<?, ?>
  • VARCHAR
• • SET<?>
  • VARCHAR
• • TUPLE
  • ROW with anonymous fields
• • UDT
  • ROW with field names
• • INET
  • IPADDRESS
• • UUID
  • UUID
• • TIMEUUID
  • UUID

:::

No other types are supported.

Trino type to Cassandra type mapping

The connector maps Trino types to the corresponding Cassandra types according to the following table:

:::{list-table} Trino type to Cassandra type mapping :widths: 30, 25, 50 :header-rows: 1

• • Trino type
  • Cassandra type
  • Notes
• • BOOLEAN
  • BOOLEAN
• • TINYINT
  • TINYINT
• • SMALLINT
  • SMALLINT
• • INTEGER
  • INT
• • BIGINT
  • BIGINT
• • REAL
  • FLOAT
• • DOUBLE
  • DOUBLE
• • VARCHAR
  • TEXT
• • DATE
  • DATE
• • TIMESTAMP(3) WITH TIME ZONE
  • TIMESTAMP
• • IPADDRESS
  • INET
• • UUID
  • UUID

:::

No other types are supported.

Partition key types

Partition keys can only be of the following types:

• ASCII
• TEXT
• VARCHAR
• BIGINT
• BOOLEAN
• DOUBLE
• INET
• INT
• FLOAT
• DECIMAL
• TIMESTAMP
• UUID
• TIMEUUID

Limitations

• Queries without filters containing the partition key result in fetching all partitions. This causes a full scan of the entire data set, and is therefore much slower compared to a similar query with a partition key as a filter.
• IN list filters are only allowed on index (that is, partition key or clustering key) columns.
• Range (< or > and BETWEEN) filters can be applied only to the partition keys.

(cassandra-sql-support)=

SQL support

The connector provides read and write access to data and metadata in the Cassandra database. In addition to the {ref}globally available <sql-globally-available> and {ref}read operation <sql-read-operations> statements, the connector supports the following features:

• {doc}/sql/insert
• {doc}/sql/delete see {ref}sql-delete-limitation
• {doc}/sql/truncate
• {doc}/sql/create-table
• {doc}/sql/create-table-as
• {doc}/sql/drop-table

Procedures

Table functions

The connector provides specific {doc}table functions </functions/table> to access Cassandra. .. _cassandra-query-function:

query(varchar) -> table

The query function allows you to query the underlying Cassandra directly. It requires syntax native to Cassandra, because the full query is pushed down and processed by Cassandra. This can be useful for accessing native features which are not available in Trino or for improving query performance in situations where running a query natively may be faster.

As a simple example, to select an entire table:

SELECT
  *
FROM
  TABLE(
    example.system.query(
      query => 'SELECT
        *
      FROM
        tpch.nation'
    )
  );

DROP TABLE

By default, DROP TABLE operations are disabled on Cassandra catalogs. To enable DROP TABLE, set the cassandra.allow-drop-table catalog configuration property to true:

cassandra.allow-drop-table=true

(sql-delete-limitation)=

SQL delete limitation

DELETE is only supported if the WHERE clause matches entire partitions.