Yugabyte-db: Missing functionality not available in YQL compared to YEDIS

[ddorian]

Hi,

So you support some sorted set commands. The point of it making efficient (even in redis when stuff == 100% memory), is that you keep items stored by both key + value. So like a local index.

But this can't be done in YQL. So it's either inneficient (as just a simple map with no index by value) or not doable in YQL.

Am I correct ? I think YQL also doesn't have STRLEN or GETRANGE function, right ?

[rkarthik007]

Hi @ddorian,

The sorted sets in YugaByte are supported efficiently. It does have a score -> value as well as the value -> score dual mapping.

The local index support for Cassandra tables are not yet implemented - the syntax for these is more generic, and that is a work in progress.

[rkarthik007]

I think YQL also doesn't have STRLEN or GETRANGE function, right ?

Actually, YB (Redis) supports these. Here is a snippet:

20:23 $ ./bin/redis-cli
127.0.0.1:6379> SET mykey "This is a string"
OK
127.0.0.1:6379> GETRANGE mykey 0 3
"This"
127.0.0.1:6379> SET key1 "Redis"
OK
127.0.0.1:6379> STRLEN key1
(integer) 5
127.0.0.1:6379>

[kmuthukk]

@rkarthik007 : I think by YQL, @ddorian is referring to YCQL (Cassandra flavored API).

We don't yet support those builtins on the string type in YCQL. But we'll be gradually improving upon YCQL with more handy support for built-ins. Welcome contributions in those areas if that's something of interest. In YCQL, we'll probably implement GETRANGE as the SUBSTR function to keep it closer to SQL language.

[kmuthukk]

@ddorian

Regarding the local index support in YCQL to be able to lookup efficiently, we do have that in the plans.

For example, if the table is something like:

create table reviews (prod_id text, reviewer_id text, score int, review text,
                      PRIMARY KEY ((prod_id), reviewer_id));

You'll be able to create an index for each prod_id where things are clustered by score.

create index reviews_by_score ON reviews ((prod_id), score) COVERING (review);

Sample DML into the table would be like:

insert into reviews(prod_id, reviewer_id, score, review) VALUES ('fan', 'jack', 5, 'awesome fan');
insert into reviews(prod_id, reviewer_id, score, review) VALUES ('fan', 'jill', 1, 'fan blades very fragile');
insert into reviews(prod_id, reviewer_id, score, review) VALUES ('yugabytedb', 'jill', 5, 'super database');
insert into reviews(prod_id, reviewer_id, score, review) VALUES ('yugabytedb', 'jack', 3, 'nice db, but YCQL needs support for strlen!');

Given that the index needed is local to the same partition key ("prod_id"), this case will essentialyl be an index that's local to the same shard/tablet and will have a fast-path to use the single-shard ACID txns code path rather than the distributed (multi-shard) ACID txns code path of YugaByte.

We will keep you posted when this feature is ready. Do you have a specific timeline you are hoping to have this feature by in YugaByte?

CC: @robertpang

[kmuthukk]

made a few edits/fixes to my response above... but I don't think it resends a email notification. So please re-read above comment.

[ddorian]

Issues that I need are the local index + copartitioned tables + conditional transactions. (subscribed to them)

But this issue was more a reminder to that redis client has more features than cql client (which is supposed to be the feature-complete based on https://github.com/YugaByte/yugabyte-db/issues/50#issuecomment-364776545

[rkarthik007]

Issues that I need are the local index + co-partitioned tables + conditional transactions.
Yes, thats the magic combo!

Currently, YCQL has a few features more than vanilla CQL:

• Distributed transactions
• Secondary indexes
• Native JSONB data type for storing documents

Currently, we are planning to implement local indexes as a co-partitioned table. This makes the data local to the tablets, and also enables operations like dropping the index (which is effectively dropping the co-partitioned table) re-use a common code path.

For conditional transactions - don't remember where we left the discussion. I remember we were discussing how to accurately detect/express the "failure" of a conditional, as that can get complex sometimes. cc @kmuthukk @robertpang

[ddorian]

@rkarthik007 Yes, since transactions are single query autocommit, you can do something like in pg:
with (delete from t1 limit 1 returning id) as s: update t2 set a=a where id=s.id;
So if you could build that, it would enable the conditional transaction since they won't change anything if the row in the first table doesn't exist. That would also enable for insert,update,etc.

[kmuthukk]

@ddorian - closing this "question" issue out for now. We have action items on our end for these capabilities.