question

Hi @Ryan Quey

thank you for clarifying your question, we really misunderstood what you needed to know.

So the question is: Is there any documentation how to map Cassandra tables to Graph tables?

I see that this is not obvious in our documentation, and will investigate this further with the docs team and will come back to you.

In the meantime, here is more explanation.

Vertex tables:

Any CQL table can be a vertex table. And any DSE Graph vertex table can be used just like any other CQL table.

The structure is no different from other CQL tables: We have a primary key, composed of partition key and clustering keys, and one row per primary key. One row corresponds to one vertex.

(Note that this was different in the previous graph implementations (6.7 and earlier), where we could have had several rows per vertex, and thus access via CQL was not so straight forward.)

Graph tables are annotated with the vertex label ( AND VERTEX LABEL ... ) to ensure they are seen as part of the graph:

Here is an example:

CREATE TABLE community_8319.person (
    person_id text PRIMARY KEY,
    age int,
    name text
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE' AND VERTEX LABEL person;

For comparison, this is the equivalent table set up purely as CQL. It is created with exactly the same default options.

The creation statement:

CREATE TABLE community_8319_2.person (
    person_id text PRIMARY KEY,
    age int,
    name text
);

is implemented as here:

CREATE TABLE community_8319_2.person (
    person_id text PRIMARY KEY,
    age int,
    name text
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

The only difference is that the table is not annotated with the vertex label.

Edge tables:

For a valid edge table, we need to observe a couple of things:

• We need to be able to map an edge precisely to the two vertices it connects. This requires the knowledge of the complete primary key of both vertices.
• And the edge in DSE Graph is directed: We have the concept of the "out-Vertex" and the "in-Vertex".

In an edge of the type "created" that connects "person" and "software", ie. the relation person -> created -> software, person is the out-Vertex label and software is the in-Vertex label.

So an edge needs to have both full primary keys of both vertices in its table structure, and DSE Graph 6.8 implements the table structure in such a way that

• the partition key of the out-Vertex is added to the partition key of the edge,
• and the clustering key of the out-Vertex and the full primary key of the in-Vertex are added as clustering keys of the edge.
• All vertex key columns are necessary to uniquely identify the relevant vertices.

You could add your own edge-specific partitioning and additional clustering keys to the edge table to allow multiple edges between the two vertices.

Here is a minimum edge table connecting two vertices that have single column primary keys.

CREATE TABLE community_8319.person__created__software (
    person_person_id text,
    software_software_id text,
    PRIMARY KEY (person_person_id, software_software_id)
)

Here is an example where we also have a weight column in the edge, which records a non-keyed edge property. Note the annotation in the end:

CREATE TABLE community_8319.person__created__software (
    person_person_id text,
    software_software_id text,
    weight float,
    PRIMARY KEY (person_person_id, software_software_id)
) WITH CLUSTERING ORDER BY (software_software_id ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE' AND EDGE LABEL created FROM person(person_person_id) TO software(software_software_id);

You see that the primary key column names do not correspond exactly to the primary keys of the vertex tables. When generated through the Gremlin schema API they are prefixed with the name of the respective vertex label, because the vertex tables could have columns of the same name.

More general rules for mapping a CQL table to an edge table:

When you create your own edge table, note that while the column names are not required to match, the data type and the number of mapping properties must match the data type and the number of primary key columns on the vertex label in order.

In addition, we might have edge label specific partition keys and clustering keys.

When creating an edge label table, its primary key needs to be composed and internally ordered by the following categories:

1. Partitioning columns specific to the edge label, in definition order
2. Partition key columns on the from(..) vertex label's table, in they order they appear there
3. Clustering columns defined on the from(..) vertex label's table, in the order they appear there
4. Clustering columns specific to the edge label, in definition order
5. Primary key columns (both partition key and clustering) defined in the to(..) vertex label's table, in the order they appear there

The first two categories become partition key columns in the created edge table. The last three categories become clustering columns in the created edge table.

I hope this helps for now, I will follow up with more documentation when available.