From user-return-39017-apmail-cassandra-user-archive=cassandra.apache.org@cassandra.apache.org Sat Feb 15 21:08:56 2014 Return-Path: X-Original-To: apmail-cassandra-user-archive@www.apache.org Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id F1B0110ED7 for ; Sat, 15 Feb 2014 21:08:56 +0000 (UTC) Received: (qmail 98006 invoked by uid 500); 15 Feb 2014 21:08:53 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 97982 invoked by uid 500); 15 Feb 2014 21:08:52 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 97973 invoked by uid 99); 15 Feb 2014 21:08:52 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 15 Feb 2014 21:08:52 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_NONE,SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of a.e.cobley@dundee.ac.uk designates 213.199.154.10 as permitted sender) Received: from [213.199.154.10] (HELO emea01-am1-obe.outbound.protection.outlook.com) (213.199.154.10) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 15 Feb 2014 21:08:48 +0000 Received: from AMSPR04MB082.eurprd04.prod.outlook.com (10.242.83.19) by AMSPR04MB081.eurprd04.prod.outlook.com (10.242.83.12) with Microsoft SMTP Server (TLS) id 15.0.878.16; Sat, 15 Feb 2014 21:08:23 +0000 Received: from AMSPR04MB082.eurprd04.prod.outlook.com ([169.254.2.51]) by AMSPR04MB082.eurprd04.prod.outlook.com ([169.254.2.51]) with mapi id 15.00.0878.008; Sat, 15 Feb 2014 21:08:23 +0000 From: Andrew Cobley To: "user@cassandra.apache.org" Subject: RE: CQL get unique row keys ? Thread-Topic: CQL get unique row keys ? Thread-Index: AQHPKnAwQI6RFYUHlEicTkjb6kir4pq2qQcAgAAmGvQ= Date: Sat, 15 Feb 2014 21:08:22 +0000 Message-ID: <93a6e1eee9af44a68ab7e34edb9b6eb5@AMSPR04MB082.eurprd04.prod.outlook.com> References: <2799B3D6-0506-4289-B06D-B15B27F69D92@dundee.ac.uk>, In-Reply-To: Accept-Language: en-GB, en-US Content-Language: en-GB X-MS-Has-Attach: X-MS-TNEF-Correlator: x-forefront-prvs: 012349AD1C x-forefront-antispam-report: SFV:NSPM;SFS:(10009001)(164054003)(24454002)(189002)(199002)(377454003)(95666001)(33646001)(85306002)(81686001)(81816001)(74316001)(47976001)(4396001)(65816001)(74366001)(49866001)(86362001)(94946001)(15975445006)(50986001)(80022001)(47736001)(94316002)(79102001)(93516002)(81342001)(95416001)(63696002)(87266001)(54356001)(46102001)(93136001)(59766001)(74662001)(74502001)(85852003)(74482001)(74706001)(69226001)(83322001)(81542001)(47446002)(76482001)(2656002)(87936001)(74876001)(77096001)(80976001)(51856001)(56776001)(92566001)(19580405001)(19580395003)(16236675002)(77982001)(53806001)(83072002)(76576001)(56816005)(76786001)(76796001)(54316002)(31966008)(90146001)(24736002);DIR:OUT;SFP:1101;SCL:1;SRVR:AMSPR04MB081;H:AMSPR04MB082.eurprd04.prod.outlook.com;CLIP:81.155.228.89;FPR:B5FCFE1D.2C0394D8.ADD99C3B.8EEAFD31.2031A;PTR:InfoNoRecords;MX:1;A:1;LANG:en; Content-Type: multipart/alternative; boundary="_000_93a6e1eee9af44a68ab7e34edb9b6eb5AMSPR04MB082eurprd04pro_" MIME-Version: 1.0 X-OriginatorOrg: dundee.ac.uk X-Virus-Checked: Checked by ClamAV on apache.org --_000_93a6e1eee9af44a68ab7e34edb9b6eb5AMSPR04MB082eurprd04pro_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Thanks, worked a treat ! Andy ________________________________ From: DuyHai Doan Sent: 15 February 2014 18:51 To: user@cassandra.apache.org Subject: Re: CQL get unique row keys ? Hello Andy Since C* 2.0.1 it is possible to list all distinct partition keys (not clu= stering keys) with: SELECT DISTINCT pk FROM .... More info on the feature here: https://issues.apache.org/jira/browse/CASSA= NDRA-4536 This query is quite efficient because the key cache is used most of the ti= me. Regards Duy Hai DOAN On Sat, Feb 15, 2014 at 6:05 PM, Andrew Cobley > wrote: I may be missing something here, but is there a way in cql to get all uniqu= e row keys in a column family(table) ? I've created a table like this: CREATE TABLE totp ( artist varchar, track varchar, appearance_type varchar, PRIMARY KEY ((artist),track) ) WITH CLUSTERING ORDER BY (track asc); and populated it with data. As you can imagine, each artist can have multi= ple tracks . Doing: Select * from totp; will produce something like (with more artists of cours= e) The Tony Rich Project | Nobo= dy Knows | P/A Telly Savalas | = If | PROMO Alice Cooper | = Elected | DISC Alice Cooper | Hel= lo Hooray | PROMO Alice Cooper | H= ey Stupid | Promo Alice Cooper | Lost I= n America | P/A Alice Cooper | No More Mr= .Nice Guy | DISC Alice Cooper | = Poison | Promo Alice Cooper | Sch= ool's Out | P/A V/T Alice Cooper | Sc= hools Out | P/A Alice Cooper | Teenage L= ament '74 | DISC Blackstreet Feat Dr.Dre | N= o Diggity | P/A Nena | 99 Lu= ftballons | Promo Nena | 99 Red= Balloons | P/A and Select artist from totp; would produce: The Tony Rich Project Telly Savalas Alice Cooper Alice Cooper Alice Cooper Alice Cooper Alice Cooper Alice Cooper Alice Cooper Alice Cooper Alice Cooper Blackstreet Feat Dr.Dre Nena Nena But of course what I really want is just the row keys The Tony Rich Project Telly Savalas Alice Cooper Blackstreet Feat Dr.Dre Nena Any idea if I can do this in CQL ? Andy The University of Dundee is a registered Scottish Charity, No: SC015096 The University of Dundee is a registered Scottish Charity, No: SC015096 --_000_93a6e1eee9af44a68ab7e34edb9b6eb5AMSPR04MB082eurprd04pro_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Thanks,


worked a treat !

Andy



From: DuyHai Doan <doa= nduyhai@gmail.com>
Sent: 15 February 2014 18:51
To: user@cassandra.apache.org
Subject: Re: CQL get unique row keys ?
 
Hello Andy

 Since C* 2.0.1 it is possible to list all distinct partition keys (no= t clustering keys) with: SELECT DISTINCT pk FROM ....

 More info on the feature here: https://issues.apache.org/jira/browse/CASSANDRA-4536

 This query is quite efficient because the key cache is used most= of the time.

 Regards

 Duy Hai DOAN



On Sat, Feb 15, 2014 at 6:05 PM, Andrew Cobley <= span dir=3D"ltr"> <a.e.cobley= @dundee.ac.uk> wrote:
I may be missing something here, but = is there a way in cql to get all unique row keys in a column family(table) = ?

I’ve created a table like this:

CREATE TABLE totp (
artist varchar,
track varchar,
 appearance_type varchar, 
 PRIMARY KEY ((artist),track)
) WITH CLUSTERING ORDER BY (track asc);

and populated it with data.  As you can imagine, each artis= t can have multiple tracks .  Doing:

Select * from totp; will produce something like (wit= h more artists of course)

  &nb= sp;                The Tony Rich Project |       =                      = ; Nobody Knows |     =         P/A
  &nb= sp;                     &= nbsp;   Telly Savalas |    =                     &nbs= p;             If |       &nb= sp;   PROMO
 &nbs= p;                     &n= bsp;     Alice Cooper |    &= nbsp;                    =         Elected |     =       DISC
 &nbs= p;                     &n= bsp;     Alice Cooper |     =                      = ;   Hello Hooray |     =       PROMO
 &nbs= p;                     &n= bsp;     Alice Cooper |     =                      = ;     Hey Stupid |     &n= bsp;     Promo
 &nbs= p;                     &n= bsp;     Alice Cooper |    &= nbsp;                     Lost In America |   &nbs= p;         P/A
 &nbs= p;                     &n= bsp;     Alice Cooper |    &= nbsp;                 No More Mr.Nice Guy |           = ; DISC
 &nbs= p;                     &n= bsp;     Alice Cooper |     =                      = ;         Poison |      =     Promo
 &nbs= p;                     &n= bsp;     Alice Cooper |     =                      = ;   School's Out |     =     P/A V/T
 &nbs= p;                     &n= bsp;     Alice Cooper |    &= nbsp;                    =     Schools Out |     &= nbsp;       P/A
 &nbs= p;                     &n= bsp;     Alice Cooper |     =                   Teenage Lament '74 |           = DISC
  &nb= sp;               Blackstreet Feat Dr.Dre |           =                     No Diggity |             P/A
 &nbs= p;                     &n= bsp;             Nena |       &= nbsp;                   99 Luftballons |           Promo
 &nbs= p;                     &n= bsp;             Nena |      &n= bsp;                   99 Red Balloons |             <= span style=3D"color: #afad24;">P/A
and Select artist from totp; would = produce:

          &n= bsp;         The Tony Rich Project
  &nb= sp;                     &= nbsp;   Telly Savalas
 &nbs= p;                     &n= bsp;     Alice Cooper
 &nbs= p;                     &n= bsp;     Alice Cooper
 &nbs= p;                     &n= bsp;     Alice Cooper
 &nbs= p;                     &n= bsp;     Alice Cooper
 &nbs= p;                     &n= bsp;     Alice Cooper
 &nbs= p;                     &n= bsp;     Alice Cooper
 &nbs= p;                     &n= bsp;     Alice Cooper
 &nbs= p;                     &n= bsp;     Alice Cooper
 &nbs= p;                     &n= bsp;     Alice Cooper
          &n= bsp;       Blackstreet Feat Dr.Dre
 &nbs= p;                     &n= bsp;             Nena
 &nbs= p;                     &n= bsp;             Nena

But o= f course what I really want is just the row keys 
    T= he Tony Rich Project
  &nb= sp;                     &= nbsp;   Telly Savalas
 &nbs= p;                     &n= bsp;     Alice Cooper=               
          &n= bsp;       Blackstreet Feat Dr.Dre
 &nbs= p;                     &n= bsp;             Nena

Any idea if I can do this in CQL ?<= /b>

Andy


The University of Dundee is a registered S= cottish Charity, No: SC015096


The University of Dundee is a registered Sc= ottish Charity, No: SC015096 --_000_93a6e1eee9af44a68ab7e34edb9b6eb5AMSPR04MB082eurprd04pro_--