[YSQL] Access YCQL tables via the YSQL API

[YourTechBud]

Jira Link: DB-699 Is it possible to access a cassandra table (created using cqlsh) via the postgres api?

[rkarthik007]

Hi @noorainp,

It is not possible to access a YCQL (Cassandra) table directly using Postgres API. This is because the mapping of data types between the two APIs is not always possible (although the storage underneath is the same in the case of YugaByte DB and it would be possible to achieve). As examples:

• YCQL supports collection types like map, list, set - these do not have PG equivalents
• YCQL tables and rows can have a "TTL" based data expiry - SQL does not support this
• There are various other constructs that do not map over such as static columns

However, it should be possible to import the YCQL table using FDW (foreign data wrappers) into Postgres as an external table and subsequently access it using PG. This approach has pro's and con's, but happy to support you in exploring this option if it is of interest to you.

One higher level question: what are you trying to achieve with the cross-access so that we can figure out the optimal solution together.

[YourTechBud]

Hey. I totally understand that the datatypes of those two protocols are incompatible.

I was just hoping to get some postgres features like joins to Cassandra.

I know I can stick to Postgres entirely for such a trivial task, but the psql api lacks basic features like secondary indexes at this point.

[rkarthik007]

Ah got it! Support for secondary indexes and unique constraints is right around the corner - I would say a couple of weeks away. Does that work for you timeline wise?

[YourTechBud]

That works! Awesome.

Would it be possible to index an existing table (containing data)? I read somewhere it isn't possible as of now. If not, when can we expect that feature?

Thanks once again buddy. I appreciate the warm welcome.

[rkarthik007]

Would it be possible to index an existing table (containing data)? I read somewhere it isn't possible as of now. If not, when can we expect that feature?

Yes, we are just starting to look at the design for this. Covered by #448 - but looks like you already found that 😄

Great questions btw @noorainp! And most welcome, look forward to working with you!

[YourTechBud]

Thanks a ton.

[schoudhury]

Reopening issue for tracking purposes.

[diegov]

There is a slightly different scenario from the OP's that I was hoping to evaluate if this feature became available, which is to have data whose canonical storage is behind the PostgreSQL API, but that is some times used in Spark jobs that would benefit from data locality features available through the Cassandra API. Read-only access would be good, r/w would be even better.

As an alternative, it would be good to have a built-in, optimised way to transfer data from one API to the other, with user-provided mapping functions if necessary.

[ymahajan]

We have one additional request from community - The documentation depicts 2 API mechanisms to interact with Yugabytedb - YSQL and YCQL (faster API). Is there a single underlying storage format stored in DocDB which can be accessed by any or mix of the API? i.e can we have the OLTP txns use the YSQL APIs and during READ txns, can we use YCQL APIs for accessing the SAME database tables?

[lefay1982]

Hi @noorainp,

It is not possible to access a YCQL (Cassandra) table directly using Postgres API. This is because the mapping of data types between the two APIs is not always possible (although the storage underneath is the same in the case of YugaByte DB and it would be possible to achieve). As examples:

• YCQL supports collection types like map, list, set - these do not have PG equivalents
• YCQL tables and rows can have a "TTL" based data expiry - SQL does not support this
• There are various other constructs that do not map over such as static columns

However, it should be possible to import the YCQL table using FDW (foreign data wrappers) into Postgres as an external table and subsequently access it using PG. This approach has pro's and con's, but happy to support you in exploring this option if it is of interest to you.

One higher level question: what are you trying to achieve with the cross-access so that we can figure out the optimal solution together.

I have the same request: OLTP use YCQL for performance and OLAP use YSQL to support join tables to query data.

• map, list, set - can use PG json datatype equivalent
• TTL not use
• static columns not use

If YSQL can Access the YCQL table, my request can achieve.