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 EB9A518E00 for ; Fri, 12 Jun 2015 14:30:11 +0000 (UTC) Received: (qmail 58101 invoked by uid 500); 12 Jun 2015 14:30:08 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 58062 invoked by uid 500); 12 Jun 2015 14:30:08 -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 58052 invoked by uid 99); 12 Jun 2015 14:30:08 -0000 Received: from Unknown (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 12 Jun 2015 14:30:08 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 5F0C518221F for ; Fri, 12 Jun 2015 14:30:08 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 5.08 X-Spam-Level: ***** X-Spam-Status: No, score=5.08 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, FREEMAIL_REPLY=1, HTML_MESSAGE=3, KAM_LINEPADDING=1.2, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-us-east.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id YsLqJrF6_w4L for ; Fri, 12 Jun 2015 14:30:00 +0000 (UTC) Received: from mail-wg0-f66.google.com (mail-wg0-f66.google.com [74.125.82.66]) by mx1-us-east.apache.org (ASF Mail Server at mx1-us-east.apache.org) with ESMTPS id 03B01429A8 for ; Fri, 12 Jun 2015 14:30:00 +0000 (UTC) Received: by wggy19 with SMTP id y19so6757629wgg.0 for ; Fri, 12 Jun 2015 07:29:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=y7YveN9uU2z3C4CCefYJ4TZuWxS/kkeMQrsOlKrJYRY=; b=tLoSLiQ+IFvlDwjO71ZtB4EICaVHlyotQLMsctrIg0TCIZbSzCExUjb0WkOUXFDLMH kh+MrT3/1e7P96vegZ+wCfyIopE8andVJ1ZHZ6+q5MVvT8Mzm23/UyedbT7Lrr1eeCIM TjfdfHMVSzhiR40CjacLMPUhtCbF3tbjkiB0Ln0SmjLKbRJkkdKJhustUCDScxN2f+BA yVKxsepq8Cja5GrzExnX2vVID65pHRauLDJWTWUDUzRomTkb4Sr3ykh6XLc1UF2HD4eC n4vGNhJpC+aCNsoJT4hBHmqVsewg5bUx33Xzv4h459MVXAVzOX/XFBNgoldb+21kSvN4 oJ4g== MIME-Version: 1.0 X-Received: by 10.194.176.165 with SMTP id cj5mr27353865wjc.72.1434119393213; Fri, 12 Jun 2015 07:29:53 -0700 (PDT) Received: by 10.27.157.193 with HTTP; Fri, 12 Jun 2015 07:29:53 -0700 (PDT) In-Reply-To: <156AA069D74CB042AA529A69006652531D560B4E@CO1PRD6102MB001.025d.mgd.msft.net> References: <156AA069D74CB042AA529A69006652531D560B4E@CO1PRD6102MB001.025d.mgd.msft.net> Date: Fri, 12 Jun 2015 10:29:53 -0400 Message-ID: Subject: Re: Support for ad-hoc query From: Jack Krupansky To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=089e013d15d8ff8202051852f08b --089e013d15d8ff8202051852f08b Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable No dispute about that. But the main design requirement Cassandra strives to meet is to be a blazing fast transactional database - here's the key, give me the data, and here's the key, write this data. Any additional query requirements are a distant second at best. A big part of that transactional speed requirement is achieved by jettisoning the overhead required for ad hoc queries. I think it is inevitable that Cassandra will eventually address the requirement for ad hoc queries when it finally decides what it wants to be when it grows up (i.e., whether to just be a niche or to subsume all of SQL), but in the meantime DSE Search/Solr, Stratio, and TupleJump Stargate, as well as extraction and indexing in Elasticsearch, are moderately reasonable near-term solutions. And I agree that having to fully model eventual (and evolving!) data requirements and emergent anomalous conditions upfront is too big a burden for many enterprises. -- Jack Krupansky On Fri, Jun 12, 2015 at 10:07 AM, wrote: > I will note here that the limitations on ad-hoc querying (and > aggregates) make it much more difficult to deal with data quality problem= s, > QA testing, and similar efforts, especially where people are used to a mo= re > relational, ad-hoc model. We have often had to extract data from Cassandr= a > to Hadoop for querying by hive. > > > > Example: =E2=80=9CWe found a few records with incorrect data. How many mo= re > records like that are out there?=E2=80=9D > > > > > > Sean Durity > > > > *From:* Peter Lin [mailto:woolfel@gmail.com] > *Sent:* Wednesday, June 10, 2015 8:17 AM > *To:* user@cassandra.apache.org > *Subject:* Re: Support for ad-hoc query > > > > > > I'll second Jack's detailed response and add that you really should do > some discovery to figure out what kinds of queries you may need to suppor= t. > > It might not be possible and often that is the case, but it's worth while > to ask the end users what kind of reports they need to run. Allowing > arbitrary ad-hoc queries is a known anti-pattern for cassandra. If the > system needs to query multiple cf to derive/calculate some result, using > Cassandra alone isn't going to do it. You'll need some other system to gi= ve > you better query capabilities like Hive. > > If you need data warehouse like features, look at http://www.kylin.io/ . > They are doing some interesting things. > > peter > > > > On Wed, Jun 10, 2015 at 7:58 AM, Jack Krupansky > wrote: > > Knowing your queries in advance is a hard-core requirement for effective > deployment of Cassandra. Ad hoc queries are a very clear anti-pattern for > Cassandra. DSE Search does provide support for advanced, complex, and ad > hoc queries. Stratio and TupleJump Stargate can also be used. > > > > Back to the question of what you mean by ad hoc queries: > > > > 1. Do you expect real-time results, like sub-second, or are these > long-running queries that might take seconds, 10 seconds or more, or even > minutes to run? > > 2. Will they be very rare or quite frequent - how much load do you expect > them to place on the cluster? > > 3. How complex do you expect them to be - how many clauses and operators? > > 4. What is their net cardinality - are they selecting just a few rows or > many rows? > > 5. Do they have individual query clauses that select many rows even if th= e > net combination of all select clauses is not so many rows? > > > > The requirement to perform advanced, complex, and ad hoc queries using DS= E > Search or the other techniques will almost certainly require that you use > moderately more capable hardware, especially more RAM, for each node, and > probably more nodes as well to reduce the row count per node since ad hoc > queries will tend to be compute-intensive based on number of rows on the > node. > > > > Yes, it can be done. No, it is not free or cheap. And, no, it does not > come out of the box for a non-DSE Cassandra release. And, yes, you must > address this requirement before deployment, not after deployment. > > > > > -- Jack Krupansky > > > > On Wed, Jun 10, 2015 at 1:18 AM, Srinivasa T N wrote: > > Thanks guys for the inputs. > > By ad-hoc queries I mean that I don't know the queries during cf design > time. The data may be from single cf or multiple cf. (This feature mayb= e > required if I want to do analysis on the data stored in cassandra, do you > have any better ideas)? > > Regards, > > Seenu. > > > > On Tue, Jun 9, 2015 at 5:57 PM, Peter Lin wrote: > > > > what do you mean by ad-hoc queries? > > Do you mean simple queries against a single column family aka table? > > Or do you mean MDX style queries that looks at multiple tables? > > if it's MDX style queries, many people extract data from Cassandra into a > data warehouse that support multi-dimensional cubes. This works well when > the extracted data is a small subset and fits neatly in a data warehouse. > > As others have stated, Cassandra isn't great at ad-hoc. For MDX style > queries, Cassandra wasn't designed for it. One thing we've done for our o= wn > project is to combine solr with our own fuzzy index to make ad-hoc querie= s > against a single table more friendly. > > > > On Tue, Jun 9, 2015 at 2:38 AM, Srinivasa T N wrote: > > Hi All, > > I have an web application running with my backend data stored in > cassandra. Now I want to do some analysis on the data stored which > requires some ad-hoc queries fired on cassandra. How can I do the same? > > Regards, > > Seenu. > > > > > > > > > > ------------------------------ > > The information in this Internet Email is confidential and may be legally > privileged. It is intended solely for the addressee. Access to this Email > by anyone else is unauthorized. If you are not the intended recipient, an= y > disclosure, copying, distribution or any action taken or omitted to be > taken in reliance on it, is prohibited and may be unlawful. When addresse= d > to our clients any opinions or advice contained in this Email are subject > to the terms and conditions expressed in any applicable governing The Hom= e > Depot terms of business or client engagement letter. The Home Depot > disclaims all responsibility and liability for the accuracy and content o= f > this attachment and for any damages or losses arising from any > inaccuracies, errors, viruses, e.g., worms, trojan horses, etc., or other > items of a destructive nature, which may be contained in this attachment > and shall not be liable for direct, indirect, consequential or special > damages in connection with this e-mail message or its attachment. > --089e013d15d8ff8202051852f08b Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
No dispute about that. But the main design requirement Cas= sandra strives to meet is to be a blazing fast transactional database - her= e's the key, give me the data, and here's the key, write this data.= Any additional query requirements are a distant second at best. A big part= of that transactional speed requirement is achieved by jettisoning the ove= rhead required for ad hoc queries.

I think it is inevita= ble that Cassandra will eventually address the requirement for ad hoc queri= es when it finally decides what it wants to be when it grows up (i.e., whet= her to just be a niche or to subsume all of SQL), but in the meantime DSE S= earch/Solr, Stratio, and TupleJump Stargate, as well as extraction and inde= xing in Elasticsearch, are moderately reasonable near-term solutions.
=

And I agree that having to fully model eventual (and ev= olving!) data requirements and emergent anomalous conditions upfront is too= big a burden for many enterprises.


-- Jack Krupansky

On Fri, Jun 12, 2015 at 10:07 AM, <SEAN_R_DURITY@homedepot.com> wrote:

I will note here that the= limitations on ad-hoc querying (and aggregates) make it much more difficul= t to deal with data quality problems, QA testing, and similar efforts, especially where people are used to a more relational, ad-hoc mod= el. We have often had to extract data from Cassandra to Hadoop for querying= by hive.

=C2=A0

Example: =E2=80=9CWe foun= d a few records with incorrect data. How many more records like that are ou= t there?=E2=80=9D

=C2=A0

=C2=A0

Sean Durity=

=C2=A0

From: Peter Li= n [mailto:woolfel@gm= ail.com]
Sent: Wednesday, June 10, 2015 8:17 AM
To: u= ser@cassandra.apache.org
Subject: Re: Support for ad-hoc query

<= div class=3D"h5">

=C2=A0

=C2=A0

I'll second Jack&= #39;s detailed response and add that you really should do some discovery to= figure out what kinds of queries you may need to support.

It might not be possi= ble and often that is the case, but it's worth while to ask the end use= rs what kind of reports they need to run. Allowing arbitrary ad-hoc queries= is a known anti-pattern for cassandra. If the system needs to query multiple cf to derive/calculate some result, = using Cassandra alone isn't going to do it. You'll need some other = system to give you better query capabilities like Hive.

If you need data ware= house like features, look at http://www.kylin.io/= . They are doing some interesting things.

peter

=C2=A0

On Wed, Jun 10, 2015 at 7:58 AM, Jack Krupansky <= jack.krupansk= y@gmail.com> wrote:

Knowing your queries in advance is a hard-core requi= rement for effective deployment of Cassandra. Ad hoc queries are a very cle= ar anti-pattern for Cassandra. DSE Search does provide support for advanced= , complex, and ad hoc queries. Stratio and TupleJump Stargate can also be used.

=C2=A0

Back to the question of what you mean by ad hoc quer= ies:

=C2=A0

1. Do you expect real-time results, like sub-second,= or are these long-running queries that might take seconds, 10 seconds or m= ore, or even minutes to run?

2. Will they be very rare or quite frequent - how mu= ch load do you expect them to place on the cluster?

3. How complex do you expect them to be - how many c= lauses and operators?

4. What is their net cardinality - are they selectin= g just a few rows or many rows?

5. Do they have individual query clauses that select= many rows even if the net combination of all select clauses is not so many= rows?

=C2=A0

The requirement to perform advanced, complex, and ad= hoc queries using DSE Search or the other techniques will almost certainly= require that you use moderately more capable hardware, especially more RAM= , for each node, and probably more nodes as well to reduce the row count per node since ad hoc queries will t= end to be compute-intensive based on number of rows on the node.<= /u>

=C2=A0

Yes, it can be done. No, it is not free or cheap. An= d, no, it does not come out of the box for a non-DSE Cassandra release. And= , yes, you must address this requirement before deployment, not after deplo= yment.

=C2=A0


-- Jack Krupansky

=C2=A0

On Wed, Jun 10, 2015 at 1:18 AM, Srinivasa T N <<= a href=3D"mailto:seenutn@gmail.com" target=3D"_blank">seenutn@gmail.com= > wrote:

Thanks guys for the i= nputs.

By ad-hoc queries I m= ean that I don't know the queries during cf design time.=C2=A0 The data= may be from single cf or multiple cf.=C2=A0 (This feature maybe required i= f I want to do analysis on the data stored in cassandra, do you have any better ideas)?

Regards,

Seenu.

=C2=A0

On Tue, Jun 9, 2015 at 5:57 PM, Peter Lin <woolfel@gmail.com> = wrote:

=C2=A0

what do you mean by a= d-hoc queries?

Do you mean simple qu= eries against a single column family aka table?

Or do you mean MDX st= yle queries that looks at multiple tables?

if it's MDX style= queries, many people extract data from Cassandra into a data warehouse tha= t support multi-dimensional cubes. This works well when the extracted data = is a small subset and fits neatly in a data warehouse.

As others have stated= , Cassandra isn't great at ad-hoc. For MDX style queries, Cassandra was= n't designed for it. One thing we've done for our own project is to= combine solr with our own fuzzy index to make ad-hoc queries against a single table more friendly.

=C2=A0

On Tue, Jun 9, 2015 at 2:38 AM, Srinivasa T N <seenutn@gmail.com&= gt; wrote:

Hi All,

=C2=A0=C2=A0 I have a= n web application running with my backend data stored in cassandra.=C2=A0 N= ow I want to do some analysis on the data stored which requires some ad-hoc= queries fired on cassandra.=C2=A0 How can I do the same?

Regards,

Seenu.

=C2=A0

=C2=A0

=C2=A0

=C2=A0



The information in this Internet Email is confidential and may be legally p= rivileged. It is intended solely for the addressee. Access to this Email by= anyone else is unauthorized. If you are not the intended recipient, any di= sclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibite= d and may be unlawful. When addressed to our clients any opinions or advice= contained in this Email are subject to the terms and conditions expressed = in any applicable governing The Home Depot terms of business or client engagement letter. The Home Depot d= isclaims all responsibility and liability for the accuracy and content of t= his attachment and for any damages or losses arising from any inaccuracies,= errors, viruses, e.g., worms, trojan horses, etc., or other items of a destructive nature, which may be contain= ed in this attachment and shall not be liable for direct, indirect, consequ= ential or special damages in connection with this e-mail message or its att= achment.

--089e013d15d8ff8202051852f08b--