As I learned the hard way (and has already been implied), design your tables to support your queries.
We have, for example, 9 tables storing the same data, because users wish to query in different ways. Could be several more tables (if one was being a purist), but indexes get us the rest of the way there. On Tue, Apr 21, 2020 at 8:20 AM Marc Richter <m...@marc-richter.info> wrote: > Hi everyone, > > I'm very new to Cassandra. I have, however, some experience with SQL. > > I need to extract some information from a Cassandra database that has > the following table definition: > > CREATE TABLE tagdata.central ( > signalid int, > monthyear int, > fromtime bigint, > totime bigint, > avg decimal, > insertdate bigint, > max decimal, > min decimal, > readings text, > PRIMARY KEY (( signalid, monthyear ), fromtime, totime) > ) > > The database is already of round about 260 GB in size. > I now need to know what is the most recent entry in it; the correct > column to learn this would be "insertdate". > > In SQL I would do something like this: > > SELECT insertdate FROM tagdata.central > ORDER BY insertdate DESC LIMIT 1; > > In CQL, however, I just can't get it to work. > > What I have tried already is this: > > SELECT insertdate FROM "tagdata.central" > ORDER BY insertdate DESC LIMIT 1; > > But this gives me an error: > ERROR: ORDER BY is only supported when the partition key is restricted > by an EQ or an IN. > > So, after some trial and error and a lot of Googling, I learned that I > must include all rows from the PRIMARY KEY from left to right in my > query. Thus, this is the "best" I can get to work: > > > SELECT > * > FROM > "tagdata.central" > WHERE > "signalid" = 4002 > AND "monthyear" = 201908 > ORDER BY > "fromtime" DESC > LIMIT 10; > > > The "monthyear" column, I crafted like a fool by incrementing the date > one month after another until no results could be found anymore. > The "signalid" I grabbed from one of the unrestricted "SELECT * FROM" - > query results. But these can't be as easily guessed as the "monthyear" > values could. > > This is where I'm stuck! > > 1. This does not really feel like the ideal way to go. I think there is > something more mature in modern IT systems. Can anyone tell me what is a > better way to get these informations? > > 2. I need a way to learn all values that are in the "monthyear" and > "signalid" columns in order to be able to craft that query. > How can I achieve that in a reasonable way? As I said: The DB is round > about 260 GB which makes it next to impossible to just "have a look" at > the output of "SELECT *".. > > Thanks for your help! > > Best regards, > Marc Richter > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscr...@cassandra.apache.org > For additional commands, e-mail: user-h...@cassandra.apache.org > >
