From user-return-62937-archive-asf-public=cust-asf.ponee.io@cassandra.apache.org Fri Jan 4 19:06:07 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id B06C3180660 for ; Fri, 4 Jan 2019 19:06:06 +0100 (CET) Received: (qmail 63819 invoked by uid 500); 4 Jan 2019 18:06:00 -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 63809 invoked by uid 99); 4 Jan 2019 18:06:00 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 04 Jan 2019 18:06:00 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id B7EEEC2614 for ; Fri, 4 Jan 2019 18:05:59 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.998 X-Spam-Level: * X-Spam-Status: No, score=1.998 tagged_above=-999 required=6.31 tests=[DKIMWL_WL_MED=-0.001, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, HEADER_FROM_DIFFERENT_DOMAINS=0.001, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H2=-0.001, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd4-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=jonhaddad-com.20150623.gappssmtp.com Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id QNcPx0aoF7nr for ; Fri, 4 Jan 2019 18:05:57 +0000 (UTC) Received: from mail-it1-f181.google.com (mail-it1-f181.google.com [209.85.166.181]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTPS id 3BB945FAEB for ; Fri, 4 Jan 2019 18:05:57 +0000 (UTC) Received: by mail-it1-f181.google.com with SMTP id b5so2559294iti.2 for ; Fri, 04 Jan 2019 10:05:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=jonhaddad-com.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to; bh=+UsGk941pmGQI7pktn1xxsh9pKFLNuI/6loiDvDu1pw=; b=BrXKf3077GnanIb9CjeNZj80URhp2Gb3G44QeS5Tw2snd1pHY2zEZ8hKxEBo1XtX9Z Ewc0f5ORTxqcTYFmqD2vK3elV2dk2M+MN9yPu/8WQrCY/GrbgMQAPB6xtB8PbNaoibW/ +tB50zHhzAYnyLQEWsMWStf7MZc5xAMhq5SbPfeA8Zn+sMiPIme8opXYVGHZGGq2wTa9 II40+dtJ0aO3mNrnOtMx2suthWmZGrTvOq5lmD9bIvv1nhdeqbzB67sJymGXm8cZG8Nz nc7zrCX8NBKAEtyCMttdkYnk4uejOA4tWGkap94Qrng4XoCNyhj2elDRcWZAyjhnuVRZ PsUQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to; bh=+UsGk941pmGQI7pktn1xxsh9pKFLNuI/6loiDvDu1pw=; b=e9cChFnORYJ47FcfEa90v7OFvyuSYH/ZNIBEsS8bK5fKPxVbs/ECaL8eiA7o80uzcQ wbAsaxkD090qM8WexNqHtAr9QTp4DCRTe+U4kb0oV2czJs/4MOaMaZ8CDPbc9uk+GEjm QU4KTrPtlLNQ3uO8i0guFBCJECwuC3Nh06cvRwxXC1Bf0yld27MLU9b9O+LBE/J4wBtS xZrS1cgkoYE2u3PCfTEjmIbzbkfXfpGX9uf7r0uOHl2w267mTWYPKjnY5hjdaRRxjVbU EYnKmljen1RTd4aIKlOnWIN09t94OJdBUvl666EtFPsWmwh2p7fLVY4XPSdYSQeC0esE oWtQ== X-Gm-Message-State: AJcUukf01cx7n9bW3g5yHo5oyhhui1IQwsFaIYx21GLSfzN1O5PmFAwn 5yXDoGm/YiVSjzCnqjW6QHr5CiUD8G8k7j/8aXjnyw== X-Google-Smtp-Source: ALg8bN4K4eoxVXLnoyNSt7zzwZvgq+dd6GXBW7Nr3MtyIdd8lmpsp+OfrugqNFFwIG7LDBmcclSDLPMoXJnvJiUbzu0= X-Received: by 2002:a24:ac62:: with SMTP id m34mr1498052iti.129.1546625156208; Fri, 04 Jan 2019 10:05:56 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Jonathan Haddad Date: Fri, 4 Jan 2019 10:05:44 -0800 Message-ID: Subject: Re: [EXTERNAL] Howto avoid tombstones when inserting NULL values To: user Content-Type: multipart/alternative; boundary="000000000000095d90057ea5bb2e" --000000000000095d90057ea5bb2e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable If you're overwriting values, it really doesn't matter much if it's a tombstone or any other value, they still need to be compacted and have the same overhead at read time. Tombstones are problematic when you try to use Cassandra as a queue (or something like a queue) and you need to scan over thousands of tombstones in order to get to the real data. You're simply overwriting a row and trying to avoid a single tombstone. Maybe I'm missing something here. Why do you think overwriting a single cell with a tombstone is any worse than overwriting a single cell with a value? Jon On Fri, Jan 4, 2019 at 9:57 AM Tomas Bartalos wrote: > Hello, > > I beleive your approach is the same as using spark with " > spark.cassandra.output.ignoreNulls=3Dtrue" > This will not cover the situation when a value have to be overwriten with > null. > > I found one possible solution - change the schema to keep only primary ke= y > fields and move all other fields to frozen UDT. > create table (year, month, day, id, frozen, primary key((year, > month, day), id) ) > In this way anything that is null inside event doesn't create tombstone, > since event is serialized to BLOB. > The penalty is in need of deserializing the whole Event when selecting > only few columns. > Can anyone confirm if this is good solution performance wise? > > Thank you, > > On Fri, 4 Jan 2019, 2:20 pm DuyHai Doan >> "The problem is I can't know the combination of set/unset values" --> >> Just for this requirement, Achilles has a working solution for many year= s >> using INSERT_NOT_NULL_FIELDS strategy: >> >> https://github.com/doanduyhai/Achilles/wiki/Insert-Strategy >> >> Or you can use the Update API that by design only perform update on not >> null fields: >> https://github.com/doanduyhai/Achilles/wiki/Quick-Reference#updating-all= -non-null-fields-for-an-entity >> >> >> Behind the scene, for each new combination of INSERT INTO table(x,y,z) >> statement, Achilles will check its prepared statement cache and if the >> statement does not exist yet, create a new prepared statement and put it >> into the cache for later re-use for you >> >> Disclaiment: I'm the creator of Achilles >> >> >> >> On Thu, Dec 27, 2018 at 10:21 PM Tomas Bartalos >> wrote: >> >>> Hello, >>> >>> The problem is I can't know the combination of set/unset values. From m= y >>> perspective every value should be set. The event from Kafka represents = the >>> complete state of the happening at certain point in time. In my table I >>> want to store the latest event so the most recent state of the happenin= g >>> (in this table I don't care about the history). Actually I used wrong >>> expression since its just the opposite of "incremental update", every e= vent >>> carries all data (state) for specific point of time. >>> >>> The event is represented with nested json structure. Top level elements >>> of the json are table fields with type like text, boolean, timestamp, l= ist >>> and the nested elements are UDT fields. >>> >>> Simplified example: >>> There is a new purchase for the happening, event: >>> {total_amount: 50, items : [A, B, C, new_item], purchase_time : >>> '2018-12-27 13:30', specials: null, customer : {... }, fare_amount,...} >>> I don't know what actually happened for this event, maybe there is a ne= w >>> item purchased, maybe some customer info have been changed, maybe the >>> specials have been revoked and I have to reset them. I just need to sto= re >>> the state as it artived from Kafka, there might already be an event for >>> this happening saved before, or maybe this is the first one. >>> >>> BR, >>> Tomas >>> >>> >>> On Thu, 27 Dec 2018, 9:36 pm Eric Stevens >> >>>> Depending on the use case, creating separate prepared statements for >>>> each combination of set / unset values in large INSERT/UPDATE statemen= ts >>>> may be prohibitive. >>>> >>>> Instead, you can look into driver level support for UNSET values. >>>> Requires Cassandra 2.2 or later IIRC. >>>> >>>> See: >>>> Java Driver: >>>> https://docs.datastax.com/en/developer/java-driver/3.0/manual/statemen= ts/prepared/#parameters-and-binding >>>> Python Driver: >>>> https://www.datastax.com/dev/blog/python-driver-2-6-0-rc1-with-cassand= ra-2-2-features#distinguishing_between_null_and_unset_values >>>> Node Driver: >>>> https://docs.datastax.com/en/developer/nodejs-driver/3.5/features/data= types/nulls/#unset >>>> >>>> On Thu, Dec 27, 2018 at 3:21 PM Durity, Sean R < >>>> SEAN_R_DURITY@homedepot.com> wrote: >>>> >>>>> You say the events are incremental updates. I am interpreting this to >>>>> mean only some columns are updated. Others should keep their original >>>>> values. >>>>> >>>>> You are correct that inserting null creates a tombstone. >>>>> >>>>> Can you only insert the columns that actually have new values? Just >>>>> skip the columns with no information. (Make the insert generator a bi= t >>>>> smarter.) >>>>> >>>>> Create table happening (id text primary key, event text, a text, b >>>>> text, c text); >>>>> Insert into table happening (id, event, a, b, c) values >>>>> ("MainEvent","The most complete info we have right now","Priceless","= 10 >>>>> pm","Grand Ballroom"); >>>>> -- b changes >>>>> Insert into happening (id, b) values ("MainEvent","9:30 pm"); >>>>> >>>>> >>>>> Sean Durity >>>>> >>>>> >>>>> -----Original Message----- >>>>> From: Tomas Bartalos >>>>> Sent: Thursday, December 27, 2018 9:27 AM >>>>> To: user@cassandra.apache.org >>>>> Subject: [EXTERNAL] Howto avoid tombstones when inserting NULL values >>>>> >>>>> Hello, >>>>> >>>>> I=E2=80=99d start with describing my use case and how I=E2=80=99d lik= e to use >>>>> Cassandra to solve my storage needs. >>>>> We're processing a stream of events for various happenings. Every >>>>> event have a unique happening_id. >>>>> One happening may have many events, usually ~ 20-100 events. I=E2=80= =99d like >>>>> to store only the latest event for the same happening (Event is an >>>>> incremental update and it contains all up-to date data about happenin= g). >>>>> Technically the events are streamed from Kafka, processed with Spark >>>>> an saved to Cassandra. >>>>> In Cassandra we use upserts (insert with same primary key). So far s= o >>>>> good, however there comes the tombstone... >>>>> >>>>> When I=E2=80=99m inserting field with NULL value, Cassandra creates t= ombstone >>>>> for this field. As I understood this is due to space efficiency, Cass= andra >>>>> doesn=E2=80=99t have to remember there is a NULL value, she just dele= tes the >>>>> respective column and a delete creates a ... tombstone. >>>>> I was hoping there could be an option to tell Cassandra not to be so >>>>> space effective and store =E2=80=9Cunset" info without generating tom= bstones. >>>>> Something similar to inserting empty strings instead of null values: >>>>> >>>>> CREATE TABLE happening (id text PRIMARY KEY, event text); insert into >>>>> happening (=E2=80=981=E2=80=99, =E2=80=98event1=E2=80=99); =E2=80=94 = tombstone is generated insert into happening >>>>> (=E2=80=981=E2=80=99, null); =E2=80=94 tombstone is not generated ins= ert into happening (=E2=80=981=E2=80=99, '=E2=80=99); >>>>> >>>>> Possible solutions: >>>>> 1. Disable tombstones with gc_grace_seconds =3D 0 or set to reasonabl= e >>>>> low value (1 hour ?) . Not good, since phantom data may re-appear 2. = ignore >>>>> NULLs on spark side with =E2=80=9Cspark.cassandra.output.ignoreNulls= =3Dtrue=E2=80=9D. Not >>>>> good since this will never overwrite previously inserted event field = with >>>>> =E2=80=9Cempty=E2=80=9D one. >>>>> 3. On inserts with spark, find all NULL values and replace them with >>>>> =E2=80=9Cempty=E2=80=9D equivalent (empty string for text, 0 for inte= ger). Very inefficient >>>>> and problematic to find =E2=80=9Cempty=E2=80=9D equivalent for some d= ata types. >>>>> >>>>> Until tombstones appeared Cassandra was the right fit for our use >>>>> case, however now I=E2=80=99m not sure if we=E2=80=99re heading the r= ight direction. >>>>> Could you please give me some advice how to solve this problem ? >>>>> >>>>> Thank you, >>>>> Tomas >>>>> --------------------------------------------------------------------- >>>>> To unsubscribe, e-mail: user-unsubscribe@cassandra.apache.org >>>>> For additional commands, e-mail: user-help@cassandra.apache.org >>>>> >>>>> >>>>> ________________________________ >>>>> >>>>> The information in this Internet Email is confidential and may be >>>>> legally privileged. It is intended solely for the addressee. Access t= o this >>>>> Email by anyone else is unauthorized. If you are not the intended >>>>> recipient, any disclosure, copying, distribution or any action taken = or >>>>> omitted to be taken in reliance on it, is prohibited and may be unlaw= ful. >>>>> When addressed to our clients any opinions or advice contained in thi= s >>>>> Email are subject to the terms and conditions expressed in any applic= able >>>>> governing The Home Depot terms of business or client engagement lette= r. The >>>>> Home Depot disclaims all responsibility and liability for the accurac= y and >>>>> content of this attachment and for any damages or losses arising from= any >>>>> inaccuracies, errors, viruses, e.g., worms, trojan horses, etc., or o= ther >>>>> items of a destructive nature, which may be contained in this attachm= ent >>>>> and shall not be liable for direct, indirect, consequential or specia= l >>>>> damages in connection with this e-mail message or its attachment. >>>>> >>>>> --------------------------------------------------------------------- >>>>> To unsubscribe, e-mail: user-unsubscribe@cassandra.apache.org >>>>> For additional commands, e-mail: user-help@cassandra.apache.org >>>>> >>>> --=20 Jon Haddad http://www.rustyrazorblade.com twitter: rustyrazorblade --000000000000095d90057ea5bb2e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
If you're overwriting values, it really doesn't=C2= =A0matter much if it's a tombstone or any other value, they still need = to be compacted and have the same overhead at read time.=C2=A0=C2=A0
Tombstones are problematic when you try to use Cassandra as a = queue (or something like a queue) and you need to scan over thousands of to= mbstones in order to get to the real data.=C2=A0 You're simply overwrit= ing a row and trying to avoid a single tombstone.=C2=A0=C2=A0
Maybe I'm missing something here.=C2=A0 Why do you think ov= erwriting a single cell with a tombstone is any worse than overwriting a si= ngle cell with a value?

Jon


On Fri, Jan 4, 2019 at = 9:57 AM Tomas Bartalos <toma= s.bartalos@gmail.com> wrote:
Hello,

I beleive your approach is the same as using spark=C2=A0with=C2=A0"spark.cassandra.output.ignore= Nulls=3Dtrue"<= /span>
This will not cover= the situation when a value have to be overwriten with null.=C2=A0

I found one possible solution - change = the schema to keep only primary key fields and move all other fields to fro= zen UDT.
create tab= le (year, month, day, id, frozen<Event>, primary key((year, month, da= y), id) )
In this w= ay anything that is null inside event doesn't create tombstone, since e= vent is serialized to BLOB.
The penalty is in need of deserializing the whole Event when selec= ting only few columns.=C2=A0
Can anyone confirm if this is good solution performance wise?

Thank you,=C2=A0
<= br>
On Fri, 4 Jan 2019, 2:20 pm = DuyHai Doan <d= oanduyhai@gmail.com wrote:
"The= problem is I can't know the combination of set/unset values" --&g= t; Just for this requirement, Achilles has a working solution for many year= s using INSERT_NOT_NULL_FIELDS strategy:


Or you can use the Update API that by desi= gn only perform update on not null fields:=C2=A0https://github.com/doandu= yhai/Achilles/wiki/Quick-Reference#updating-all-non-null-fields-for-an-enti= ty


Behind the scene, for each n= ew combination of INSERT INTO table(x,y,z) statement, Achilles will check i= ts prepared statement cache and if the statement does not exist yet, create= a new prepared statement and put it into the cache for later re-use for yo= u

Disclaiment: I'm the creator of Achilles
=



On Thu, Dec 27, 2018 at 10:21 PM Tomas Bartalos = <tomas.bartalos@gmail.com> wrote:
Hello,

The problem is I can't know t= he combination of set/unset values. From my perspective every value should = be set. The event from Kafka represents the complete state of the happening= at certain point in time. In my table I want to store the latest event so = the most recent state of the happening (in this table I don't care abou= t the history). Actually I used wrong expression since its just the opposit= e of "incremental update", every event carries all data (state) f= or specific point of time.=C2=A0

The event is represented with nested json structure. Top level el= ements of the json are table fields with type like text, boolean, timestamp= , list and the nested elements are UDT fields.=C2=A0

Simplified example:
The= re is a new purchase for the happening, event:
{tota= l_amount: 50, items : [A, B, C, new_item], purchase_time : '2018-12-27 = 13:30', specials: null, customer : {... }, fare_amount,...}=C2=A0
=
I don't know what actually happened for this event, m= aybe there is a new item purchased, maybe some customer info have been chan= ged, maybe the specials have been revoked and I have to reset them. I just = need to store the state as it artived from Kafka, there might already be an= event for this happening saved before, or maybe this is the first one.

BR,
= Tomas


On Thu, 27 Dec 2018, 9:36 pm Eric Stevens <mightye@gma= il.com wrote:
Depen= ding on the use case, creating separate prepared statements for each combin= ation of set / unset values in large INSERT/UPDATE statements may be prohib= itive.=C2=A0=C2=A0


On Thu, Dec 27, 2018 at 3:21 PM Durity, Sean R <SEAN_R_DURITY@homedepot.com> wrote:
You say the events are incremental updates= . I am interpreting this to mean only some columns are updated. Others shou= ld keep their original values.

You are correct that inserting null creates a tombstone.

Can you only insert the columns that actually have new values? Just skip th= e columns with no information. (Make the insert generator a bit smarter.)
Create table happening (id text primary key, event text, a text, b text, c = text);
Insert into table happening (id, event, a, b, c) values ("MainEvent&qu= ot;,"The most complete info we have right now","Priceless&qu= ot;,"10 pm","Grand Ballroom");
-- b changes
Insert into happening (id, b) values ("MainEvent","9:30 pm&q= uot;);


Sean Durity


-----Original Message-----
From: Tomas Bartalos <tomas.bartalos@gmail.com><= br> Sent: Thursday, December 27, 2018 9:27 AM
To: user@cassandra.apache.org
Subject: [EXTERNAL] Howto avoid tombstones when inserting NULL values

Hello,

I=E2=80=99d start with describing my use case and how I=E2=80=99d like to u= se Cassandra to solve my storage needs.
We're processing a stream of events for various happenings. Every event= have a unique happening_id.
One happening may have many events, usually ~ 20-100 events. I=E2=80=99d li= ke to store only the latest event for the same happening (Event is an incre= mental update and it contains all up-to date data about happening).
Technically the events are streamed from Kafka, processed with Spark an sav= ed to Cassandra.
In Cassandra we use upserts (insert with same primary key).=C2=A0 So far so= good, however there comes the tombstone...

When I=E2=80=99m inserting field with NULL value, Cassandra creates tombsto= ne for this field. As I understood this is due to space efficiency, Cassand= ra doesn=E2=80=99t have to remember there is a NULL value, she just deletes= the respective column and a delete creates a ... tombstone.
I was hoping there could be an option to tell Cassandra not to be so space = effective and store =E2=80=9Cunset" info without generating tombstones= .
Something similar to inserting empty strings instead of null values:

CREATE TABLE happening (id text PRIMARY KEY, event text); insert into happe= ning (=E2=80=981=E2=80=99, =E2=80=98event1=E2=80=99); =E2=80=94 tombstone i= s generated insert into happening (=E2=80=981=E2=80=99, null); =E2=80=94 to= mbstone is not generated insert into happening (=E2=80=981=E2=80=99, '= =E2=80=99);

Possible solutions:
1. Disable tombstones with gc_grace_seconds =3D 0 or set to reasonable low = value (1 hour ?) . Not good, since phantom data may re-appear 2. ignore NUL= Ls on spark side with =E2=80=9Cspark.cassandra.output.ignoreNulls=3Dtrue=E2= =80=9D. Not good since this will never overwrite previously inserted event = field with =E2=80=9Cempty=E2=80=9D one.
3. On inserts with spark, find all NULL values and replace them with =E2=80= =9Cempty=E2=80=9D equivalent (empty string for text, 0 for integer). Very i= nefficient and problematic to find =E2=80=9Cempty=E2=80=9D equivalent for s= ome data types.

Until tombstones appeared Cassandra was the right fit for our use case, how= ever now I=E2=80=99m not sure if we=E2=80=99re heading the right direction.=
Could you please give me some advice how to solve this problem ?

Thank you,
Tomas
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@cass= andra.apache.org
For additional commands, e-mail: user-help@cassandra= .apache.org


________________________________

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 prohibited 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 t= erms of business or client engagement letter. The Home Depot disclaims all = responsibility and liability for the accuracy and content of this attachmen= t and for any damages or losses arising from any inaccuracies, errors, viru= ses, e.g., worms, trojan horses, etc., or other items of a destructive natu= re, which may be contained in this attachment and shall not be liable for d= irect, indirect, consequential or special damages in connection with this e= -mail message or its attachment.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@cass= andra.apache.org
For additional commands, e-mail: user-help@cassandra= .apache.org


--
Jon Haddad
http://www.rustyrazorblade.com
twitter: r= ustyrazorblade
--000000000000095d90057ea5bb2e--