Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 3A0B6200C16 for ; Thu, 9 Feb 2017 11:45:06 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 3873E160B50; Thu, 9 Feb 2017 10:45:06 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 6E617160B4C for ; Thu, 9 Feb 2017 11:45:04 +0100 (CET) Received: (qmail 45436 invoked by uid 500); 9 Feb 2017 10:45:03 -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 45426 invoked by uid 99); 9 Feb 2017 10:45:03 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 09 Feb 2017 10:45:03 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 7CBBC1A0424 for ; Thu, 9 Feb 2017 10:45:02 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.399 X-Spam-Level: ** X-Spam-Status: No, score=2.399 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H2=-0.001, RCVD_IN_SORBS_SPAM=0.5, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (1024-bit key) header.d=jaumo.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id ffYGnvMPY_S5 for ; Thu, 9 Feb 2017 10:44:59 +0000 (UTC) Received: from mail-ua0-f171.google.com (mail-ua0-f171.google.com [209.85.217.171]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 8BAA15F39F for ; Thu, 9 Feb 2017 10:44:58 +0000 (UTC) Received: by mail-ua0-f171.google.com with SMTP id 96so131379667uaq.3 for ; Thu, 09 Feb 2017 02:44:58 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=jaumo.com; s=google; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=+11ECFBWGox7IRM9ijdk+m9rv41UW+c+zYaVNbSbmlU=; b=FSEmRu7SqfxJISDd+iX3VSYmuYpvImJmv1piZCx9l2PwYYtXSGTTrGoncW+QT0KSbB qipMRL/hgOhd9q02CGzX135Ra8n8kJTW6NsG3Fgd5lpZ+EWITwHaa3sgxtxQ+RsFuPfm KEId7KHzLEEMoySn7TIU3++rQgddTyYYPdVxc= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=+11ECFBWGox7IRM9ijdk+m9rv41UW+c+zYaVNbSbmlU=; b=Hbwio/NcnXdA3nHQ9qGRLuAXiyHg+BbY6Gq8jYSiy8iWUN/0I8DhB+z+XovHYzw1hl SZg3oZfvxq/Th89l2j11pDv2TPNrwj7sm/p2fdtY+/7Lj2w9WEA/EY0zNd0JF/qIkHtC KgReTB5rTlYiFwzAJb26C24+co4cQAafJG5N5UiRbT7d50EqPeDBLWzWhTgXv+ZZoGWn GotC+gh1HAoyC01Hdcz+hfoy2GXaDWF6yfbAqWYjOzSn9wrcS132MdXPFA6G5vn8WvpA D4ajL6OnTCtSwdcl0ATJcPJTzr4D5slFyRqsj+mOx+KthK+vBKUr4+5wJGTIQAIl7uDM fAig== X-Gm-Message-State: AMke39k4KpkpVs+uyis9vYS++6fUiIpo/CEMeMhgoOiitJVyPW6WihN7uR70OXK7+qdhEIa9zhH2742E768/TUCU X-Received: by 10.176.83.153 with SMTP id k25mr1044160uaa.141.1486637096841; Thu, 09 Feb 2017 02:44:56 -0800 (PST) MIME-Version: 1.0 Received: by 10.176.0.175 with HTTP; Thu, 9 Feb 2017 02:44:56 -0800 (PST) In-Reply-To: References: From: Benjamin Roth Date: Thu, 9 Feb 2017 11:44:56 +0100 Message-ID: Subject: Re: DELETE/SELECT with multi-column PK and IN To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=f403045dd7ec114c76054816acc8 archived-at: Thu, 09 Feb 2017 10:45:06 -0000 --f403045dd7ec114c76054816acc8 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Ok now I REALLY got it :) Thanks Sylvain! 2017-02-09 11:42 GMT+01:00 Sylvain Lebresne : > On Thu, Feb 9, 2017 at 10:52 AM, Benjamin Roth > wrote: > >> Ok got it. >> >> But it's interesting that this is supported: >> DELETE/SELECT FROM ks.cf WHERE (pk1) IN ((1), (2), (3)); >> >> This is technically mostly the same (Token awareness, >> coordination/routing, read performance, ...), right? >> > > It is. That's what I meant by "there is something to be said for the > consistency of the CQL language in general". In other words, look for no > externally logical reason for this being unsupported, it's unsupported > simply due to how the CQL code evolved. But as I said, we didn't fix that > inconsistency because we're all busy and it's not really that important i= n > practice. The project of course welcome any contributions though :) > > >> >> 2017-02-09 10:43 GMT+01:00 Sylvain Lebresne : >> >>> This is a statement on multiple partitions and there is really no >>> optimization the code internally does on that. In fact, I strongly advi= se >>> you to not use a batch but rather simply do a for loop client side and = send >>> statement individually. That way, your driver will be able to use prope= r >>> token-awareness for each request (while if you send a batch, one >>> coordinator will be picked up and will have to forward most statement, >>> doing more network hops at the end of the day). The only case where usi= ng a >>> batch is indeed legit is if you care about all the statement being atom= ic, >>> but in that case it's a logged batch you want. >>> >>> That's btw more or less why we never bothered implementing that: it's >>> totally doable technically, but it's not really such a good idea >>> performance wise in practice most of the time, and you can easily work = it >>> around with a batch if you need atomicity. >>> >>> Which is not saying it will never be and shouldn't be supported btw, >>> there is something to be said for the consistency of the CQL language i= n >>> general. But it's why no-one took time to do it so far. >>> >>> On Thu, Feb 9, 2017 at 10:36 AM, Benjamin Roth >>> wrote: >>> >>>> Yes, thats the workaround - I'll try that. >>>> >>>> Would you agree it would be better for internal optimizations to >>>> process this within a single statement? >>>> >>>> 2017-02-09 10:32 GMT+01:00 Ben Slater : >>>> >>>>> Yep, that makes it clear. I think an unlogged batch of prepared >>>>> statements with one statement per PK tuple would be roughly equivalen= t? And >>>>> probably no more complex to generate in the client? >>>>> >>>>> On Thu, 9 Feb 2017 at 20:22 Benjamin Roth >>>>> wrote: >>>>> >>>>>> Maybe that makes it clear: >>>>>> >>>>>> DELETE FROM ks.cf WHERE (partitionkey1, partitionkey2) IN ((1, 2), >>>>>> (1, 3), (2, 3), (3, 4)); >>>>>> >>>>>> If want to delete or select a bunch of records identified by their >>>>>> multi-partitionkey tuples. >>>>>> >>>>>> 2017-02-09 10:18 GMT+01:00 Ben Slater : >>>>>> >>>>>> Are you looking this to be equivalent to (PK1=3D1 AND PK2=3D2) or ar= e you >>>>>> looking for (PK1 IN (1,2) AND PK2 IN (1,2)) or something else? >>>>>> >>>>>> Cheers >>>>>> Ben >>>>>> >>>>>> On Thu, 9 Feb 2017 at 20:09 Benjamin Roth >>>>>> wrote: >>>>>> >>>>>> Hi Guys, >>>>>> >>>>>> CQL says this is not allowed: >>>>>> >>>>>> DELETE FROM ks.cf WHERE (pk1, pk2) IN ((1, 2)); >>>>>> >>>>>> 1. Is there a reason for it? There shouldn't be a performance >>>>>> penalty, it is a PK lookup, the same thing works with a single pk co= lumn >>>>>> 2. Is there a known workaround for it? >>>>>> >>>>>> It would be much of a help to have it for daily business, IMHO it's = a >>>>>> waste of resources to run multiple queries just to fetch a bunch of = records >>>>>> by a PK. >>>>>> >>>>>> Thanks in advance for any reply >>>>>> >>>>>> -- >>>>>> Benjamin Roth >>>>>> Prokurist >>>>>> >>>>>> Jaumo GmbH =C2=B7 www.jaumo.com >>>>>> Wehrstra=C3=9Fe 46 =C2=B7 73035 G=C3=B6ppingen =C2=B7 Germany >>>>>> Phone +49 7161 304880-6 <+49%207161%203048806> =C2=B7 Fax +49 7161 >>>>>> 304880-1 <+49%207161%203048801> >>>>>> AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing Director: Jens Kammerer >>>>>> >>>>>> -- >>>>>> =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2= =80=94 >>>>>> Ben Slater >>>>>> Chief Product Officer >>>>>> Instaclustr: Cassandra + Spark - Managed | Consulting | Support >>>>>> +61 437 929 798 <+61%20437%20929%20798> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Benjamin Roth >>>>>> Prokurist >>>>>> >>>>>> Jaumo GmbH =C2=B7 www.jaumo.com >>>>>> Wehrstra=C3=9Fe 46 =C2=B7 73035 G=C3=B6ppingen =C2=B7 Germany >>>>>> Phone +49 7161 304880-6 <+49%207161%203048806> =C2=B7 Fax +49 7161 >>>>>> 304880-1 <+49%207161%203048801> >>>>>> AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing Director: Jens Kammerer >>>>>> >>>>> -- >>>>> =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94 >>>>> Ben Slater >>>>> Chief Product Officer >>>>> Instaclustr: Cassandra + Spark - Managed | Consulting | Support >>>>> +61 437 929 798 <+61%20437%20929%20798> >>>>> >>>> >>>> >>>> >>>> -- >>>> Benjamin Roth >>>> Prokurist >>>> >>>> Jaumo GmbH =C2=B7 www.jaumo.com >>>> Wehrstra=C3=9Fe 46 =C2=B7 73035 G=C3=B6ppingen =C2=B7 Germany >>>> Phone +49 7161 304880-6 <+49%207161%203048806> =C2=B7 Fax +49 7161 304= 880-1 >>>> <+49%207161%203048801> >>>> AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing Director: Jens Kammerer >>>> >>> >>> >> >> >> -- >> Benjamin Roth >> Prokurist >> >> Jaumo GmbH =C2=B7 www.jaumo.com >> Wehrstra=C3=9Fe 46 =C2=B7 73035 G=C3=B6ppingen =C2=B7 Germany >> Phone +49 7161 304880-6 <+49%207161%203048806> =C2=B7 Fax +49 7161 30488= 0-1 >> <+49%207161%203048801> >> AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing Director: Jens Kammerer >> > > --=20 Benjamin Roth Prokurist Jaumo GmbH =C2=B7 www.jaumo.com Wehrstra=C3=9Fe 46 =C2=B7 73035 G=C3=B6ppingen =C2=B7 Germany Phone +49 7161 304880-6 =C2=B7 Fax +49 7161 304880-1 AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing Director: Jens Kammerer --f403045dd7ec114c76054816acc8 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Ok now I REALLY got it :)=C2=A0
Thanks Sylvain!
<= /div>

2017-02-09 1= 1:42 GMT+01:00 Sylvain Lebresne <sylvain@datastax.com>:
On Thu, Feb 9, 2017 at 10:52 = AM, Benjamin Roth <benjamin.roth@jaumo.com> wrote:
=
Ok got i= t.

But it's interesting that this is supported:
DELETE/SELECT=C2=A0FROM=C2=A0ks.cf=C2=A0WHERE=C2=A0(pk1)=C2=A0IN=C2=A0((1), (2), (3));

This is technically mostl= y the same (Token awareness, coordination/routing, read performance, ...), = right?

It is. Tha= t's what I meant by "there is something to be said for the consist= ency of the CQL language in general". In other words, look for no exte= rnally logical reason for this being unsupported, it's unsupported simp= ly due to how the CQL code evolved. But as I said, we didn't fix that i= nconsistency because we're all busy and it's not really that import= ant in practice. The project of course welcome any contributions though :)<= /div>
=C2=A0

2017-02-09 10:43 GMT+01:00 Sylvain Lebresne <sy= lvain@datastax.com>:
This is a statement on multiple partitions an= d there is really no optimization the code internally does on that. In fact= , I strongly advise you to not use a batch but rather simply do a for loop = client side and send statement individually. That way, your driver will be = able to use proper token-awareness for each request (while if you send a ba= tch, one coordinator will be picked up and will have to forward most statem= ent, doing more network hops at the end of the day). The only case where us= ing a batch is indeed legit is if you care about all the statement being at= omic, but in that case it's a logged batch you want.

That's btw more or less why we never bothered implementing that: it= 9;s totally doable technically, but it's not really such a good idea pe= rformance wise in practice most of the time, and you can easily work it aro= und with a batch if you need atomicity.=C2=A0

Whic= h is not saying it will never be and shouldn't be supported btw, there = is something to be said for the consistency of the CQL language in general.= But it's why no-one took time to do it so far.

On Thu, Feb 9, 2017 at 10:36 AM, Benjam= in Roth <benjamin.roth@jaumo.com> wrote:
Yes, thats the wor= karound - I'll try that.

Would you agree it would be= better for internal optimizations to process this within a single statemen= t?
2017-02-09 10:32 GMT+01:00 Ben Slater <ben.slater@instaclustr.com>:
Yep, that makes it clear. I think = an unlogged batch of prepared statements with one statement per PK tuple wo= uld be roughly equivalent? And probably no more complex to generate in the = client?

On Thu, 9 Feb= 2017 at 20:22 Benjamin Roth <benjamin.roth@jaumo.com> wrote:
Maybe that makes it clear:

DELETE=C2=A0FROM=C2=A0ks.cf=C2=A0WHERE= =C2=A0(partitionkey1,=C2=A0partitionkey2)=C2=A0IN=C2=A0((= 1, 2), (1, = 3), (2, 3), (3, 4));

If want to delete or select a bunch of records identified by their mul= ti-partitionkey tuples.

2017-02-09 10:18 GMT+01:00 Ben Slater <= ;ben.slater@instaclustr.com= >:
Are you looking this to be equivalent to (PK1= =3D1 AND PK2=3D2) or are you looking for (PK1 IN (1,2) AND PK2 IN (1,2)) or= something else?

<= div class=3D"m_-3700226197386356252gmail-m_6630490623953871772m_-3449622012= 333787542m_-1107466318225639182m_8458775556515303793gmail_msg">Cheers
=
Ben

On Thu, 9 Feb 2017 at 20:09 Benjamin Roth <= benjamin.roth@jaumo.com>= ; wrote:
Hi Guys,

= CQL says this is not allowed:

DELETE = FROM ks.cf WHERE (pk1, pk2) IN ((1, 2));


1. Is there a reason for it? There shouldn&#= 39;t be a performance penalty, it is a PK lookup, the same thing works with= a single pk column
2. Is there a known workaround for it?
=

It would be much of a help to have it for daily busi= ness, IMHO it's a waste of resources to run multiple queries just to fe= tch a bunch of records by a PK.

Thanks in advan= ce for any reply

--
Benjamin Roth
Prokurist

Jaumo GmbH =C2=B7 www.jaumo.com
Wehrstra=C3=9Fe 46= =C2=B7 73035 G=C3=B6ppingen =C2=B7 Germany
Phone +49 7161 304880= -6 =C2=B7 Fax +49 7161 304880-1
AG Ulm =C2=B7 HRB 731058 = =C2=B7 Managing Director: Jens Kammerer
--
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80= =94=E2=80=94=E2=80=94
Ben Slater
Chief Product Officer
Instaclustr: Cassandra + Spark - Managed | Consult= ing | Support
+61= 437 929 798



=
--
Benjamin Ro= th
Prokurist<= br class=3D"m_-3700226197386356252gmail-m_6630490623953871772m_-34496220123= 33787542m_-1107466318225639182m_8458775556515303793gmail_msg">
Jaumo GmbH =C2=B7 www.jaumo.com
Wehrstra=C3=9Fe 46 =C2=B7 73035 G=C3=B6ppi= ngen =C2=B7 Germany
Phone +49 7161 304880-6 =C2=B7 Fax +49 7161 304880-1
AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing D= irector: Jens Kammerer
--
=E2= =80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94
B= en Slater
Chief Product Officer
Instaclustr: Cassandra + Spar= k - Managed | Consulting | Support
<= /div>



--
=
Benjamin = Roth
Prokurist

Jaumo GmbH =C2=B7 www.jaumo.com
Wehrstra=C3=9Fe 46 =C2=B7 73035 G= =C3=B6ppingen =C2=B7 Germany
Phone +49 7161 304880-6 =C2=B7 Fax= +49 7161 304880-1
AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing Dir= ector: Jens Kammerer




--
=
Benjamin Roth
Prokurist

Jaumo GmbH =C2=B7 <= a href=3D"http://www.jaumo.com" target=3D"_blank">www.jaumo.com
Wehr= stra=C3=9Fe 46 =C2=B7 73035 G=C3=B6ppingen =C2=B7 Germany
Phone +4= 9 7161 304880-6 =C2=B7 Fax +49 7161 304880-1
AG Ulm =C2= =B7 HRB 731058 =C2=B7 Managing Director: Jens Kammerer




--
Be= njamin Roth
Prokurist

Jaumo GmbH =C2=B7 www.jaumo.com
Wehrstra=C3=9Fe 46 =C2=B7 73= 035 G=C3=B6ppingen =C2=B7 Germany
Phone +49 7161 304880-6 =C2=B7 Fax +49= 7161 304880-1
AG Ulm =C2=B7 HRB 731058 =C2=B7 Managing Director: Jens K= ammerer
--f403045dd7ec114c76054816acc8--