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 6590511878 for ; Tue, 22 Jul 2014 15:30:04 +0000 (UTC) Received: (qmail 56495 invoked by uid 500); 22 Jul 2014 15:29:59 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 56324 invoked by uid 500); 22 Jul 2014 15:29:59 -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 56186 invoked by uid 99); 22 Jul 2014 15:29:59 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 Jul 2014 15:29:59 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of arodrime@gmail.com designates 209.85.217.172 as permitted sender) Received: from [209.85.217.172] (HELO mail-lb0-f172.google.com) (209.85.217.172) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 Jul 2014 15:29:57 +0000 Received: by mail-lb0-f172.google.com with SMTP id z11so6176178lbi.31 for ; Tue, 22 Jul 2014 08:29:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :content-type; bh=cr5VJYKd1aA266iuSXrx4EhXVloQ3KpKypHFLsET2KU=; b=ZcK21bXb/n3RtMx40z28zXLj8amZc3J6zW+sd7Aoh6AXTBHmtajccG2PZLVeO+pa8L GeSW0aXovdxTf5iiVpFPmm4Ppa8r4WDswqxFD+P/BWfkShm1SlDeEJf9IG/GzhrXEgZ9 lQ8Sx00aXBsWv/c0uI7zX3LtHYopKdujMeQRrl56RAIm52u+aVShSyGKtJ+6EN8lPhxe ao8/nc4D5S7m6CKZ9D1XHTipwVtJ5lAHzN72/EaZ2e1RI33k1EIbeLsph93S5exuyoXT J93lNY3efFIFF42aRBZjWOouwBXbDqbhoKsudzwJsmw8qULcChhsdS3Gt/mKxvWDIA97 tDMA== X-Received: by 10.152.5.105 with SMTP id r9mr35265483lar.37.1406042972719; Tue, 22 Jul 2014 08:29:32 -0700 (PDT) MIME-Version: 1.0 Received: by 10.112.37.236 with HTTP; Tue, 22 Jul 2014 08:29:12 -0700 (PDT) In-Reply-To: References: From: Alain RODRIGUEZ Date: Tue, 22 Jul 2014 17:29:12 +0200 Message-ID: Subject: Re: JSON to Cassandra ? To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=089e013d161eed8ed204fec9e324 X-Virus-Checked: Checked by ClamAV on apache.org --089e013d161eed8ed204fec9e324 Content-Type: text/plain; charset=ISO-8859-1 Hi, This seems to fit, even if I would need to have to look on how these fields can be queried and indexed. Also, I would need to see if those UDF can be modified once created and how they behave in this use case. Yet, 2.1 is currently in beta, and we won't switch to this version immediately (even if we could take profit of this and improved counters also...) since we are using C*1.2 and are giving a try at DSE 4.5. In both cases, we are far from using 2.1. How does people use to do this without UDF ? Thanks for the pointer though, will probably help someday :-). 2014-07-22 16:30 GMT+02:00 Jack Krupansky : > Sounds like user-defined types (UDF) in Cassandra 2.1: > https://issues.apache.org/jira/browse/CASSANDRA-5590 > > But... be careful to make sure that you aren't using this powerful (and > dangerous) feature as a crutch merely to avoid disciplined data modeling. > > -- Jack Krupansky > > *From:* Alain RODRIGUEZ > *Sent:* Tuesday, July 22, 2014 9:56 AM > *To:* user@cassandra.apache.org > *Subject:* JSON to Cassandra ? > > Hi guys, I know this topic as already been spoken many times, and I read > a lot of these discussions. > > Yet, I have not been able to find a good way to do what I want. > > We are receiving messages from our app that is a complex, dynamic, nested > JSON (can be a few or thousands of attributes). JSON is variable and can > contain nested arrays or sub-JSONs. > > Please, consider this example: > > JSON > > { > "struct-id": 141241321, > "nested-1-1": { > "value-1-1-1": "36d1f74d-1663-418d-8b1b-665bbb2d9ecb", > "value-1-1-2": 5, > "value-1-1-3": 0.5, > "value-1-1-4": ["foo", "bar", "foobar"], > "nested-2-1": { > "test-2-1-1": "whatever", > "test-2-1-2": 42 > } > }, > "nested-1-2": { > "value-1-2-1": [{ > "id": 1, > "deeply-nested": { > "data-1": "test", > "data-2": 4023 > } > }, > { > "id": 2, > "data-3": "that's enough data" > }] > } > } > > We would like to store those messages to Cassandra and then run SPARK jobs > over it. Basically, storing it as a text (full JSON in one column) would > work but wouldn't be optimised since I might want to count how many times > "value-1-1-3" is bigger or equal to 1, I would have to read all the JSON > before answering this. I read a lot of things about people using composite > columns and dynamic composite columns, but no precise example. I am also > aware of collections support, yet nested collections are not supported > currently. > > I would like to have: > > - 1 column per attribute > - typed values > - something that would be able to parse and store any valid JSON (with > nested arrays of JSON or whatever). > - The most efficient model to use alongside with spark to query anything > inside. > > What would be the possible CQL schemas to create such a data structure ? > > What are the defaults of the following schema ? > > Cassandra > > CREATE TABLE test-schema ( > struct-id int, > nested-1-1#value-1-1-1 string, > nested-1-1#value-1-1-2 int, > nested-1-1#value-1-1-3 float, > nested-1-1#value-1-1-4#array0 string, > nested-1-1#value-1-1-4#array1 string, > nested-1-1#value-1-1-4#array2 string, > nested-1-1#nested-2-1#test-2-1-1 string, > nested-1-1#nested-2-1#test-2-1-2 int, > nested-1-2#value-1-2-1#array0#id int, > nested-1-2#value-1-2-1#array0#deeply-nested#data-1 string, > nested-1-2#value-1-2-1#array0#deeply-nested#data-2 int, > nested-1-2#id int, > nested-1-2#data-3 string, > PRIMARY KEY (struct-id) > ) > > I could use: > > nested-1-1#value-1-1-4 list, > > instead of: > > nested-1-1#value-1-1-4#array0 string, > nested-1-1#value-1-1-4#array1 string, > nested-1-1#value-1-1-4#array2 string, > > yet it wouldn't work here: > > nested-1-2#value-1-2-1#array0#deeply-nested#data-1 string, > nested-1-2#value-1-2-1#array0#deeply-nested#data-2 int, > nested-1-2#value-1-2-1#array1#id int, > nested-1-2#value-1-2-1#array1#data-3 string, > > since this is a nested structure inside the list. > > > > To create this schema, could we imagine that the app logging this try to > write to the corresponding column, for each JSON attribute, and if the > column is missing, catch the error, create the column and reprocess write ? > > This exception would happen for each new field, only once and would modify > the schema. > > Any thought that would help us (and probably more people) ? > > Alain > --089e013d161eed8ed204fec9e324 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi,

This seems to fit, even if I would = need to have to look on how these fields can be queried and indexed. Also, = I would need to see if those UDF can be modified once created and how they = behave in this use case.

Yet, 2.1 is currently in beta, and we won't switch = to this version immediately (even if we could take profit of this and impro= ved counters also...) since we are using C*1.2 and are giving a try at DSE = 4.5. In both cases, we are far from using 2.1. How does people use to do th= is without UDF ?

Thanks for the pointer though, will probably help somed= ay :-).


2014-07-22 16:30 GMT+02:00 Jack Krupansky <jack@basetechnology= .com>:
Sounds like user-defined types (UDF) in Cassandra 2.1:
 
But... be careful to make sure that you aren’t using this powerf= ul (and=20 dangerous) feature as a crutch merely to avoid disciplined data modeling.
 
-= - Jack=20 Krupansky
 
Sent: Tuesday, July 22, 2014 9:56 AM
To: user@cassandra.apache.org
Subject: JSON to Cassandra ?
 
Hi guys, I know this topic as already been spoken many tim= es, and I=20 read a lot of these discussions.=20
 
Yet, I have not been able to find a good way to do what I want.
 
We are receiving messages from our app that is a complex, dynamic, nes= ted=20 JSON (can be a few or thousands of attributes). JSON is variable and can co= ntain=20 nested arrays or sub-JSONs.
 
Please, consider this example:
 
JSON
 
{
    "struct-id": 141241321,
    "nested-1-1": {
        "value-1-1-1":=20 "36d1f74d-1663-418d-8b1b-665bbb2d9ecb",
        "value-1-1-2": 5,=
        "value-1-1-3": 0.= 5,
        "value-1-1-4": [&= quot;foo", "bar",=20 "foobar"],
        "nested-2-1": {
           =20 "test-2-1-1": "whatever",
           =20 "test-2-1-2": 42
        }
    },
    "nested-1-2": {
        "value-1-2-1": [{=
            &qu= ot;id":=20 1,
           =20 "deeply-nested": {
           &nbs= p;   =20 "data-1": "test",
           &nbs= p;   =20 "data-2": 4023
            }
        },
        {
            &qu= ot;id":=20 2,
           =20 "data-3": "that's enough data"
        }]
    }
}
 
We would like to store those messages to Cassandra and then run SPARK = jobs=20 over it. Basically, storing it as a text (full JSON in one column) would wo= rk=20 but wouldn't be optimised since I might want to count how many times=20 "value-1-1-3" is bigger or equal to 1, I would have to read all t= he JSON before=20 answering this. I read a lot of things about people using composite columns= and=20 dynamic composite columns, but no precise example. I am also aware of=20 collections support, yet nested collections are not supported currently.
 
I would like to have:
 
- 1 column per attribute
- typed values
- something that would be able to parse and store any valid JSON (with= =20 nested arrays of JSON or whatever).
- The most efficient model to use alongside with spark to query anythi= ng=20 inside.
 
What would be the possible CQL schemas to create such a data structure= =20 ?
 
What are the defaults of the following schema ?
 
Cassandra
 
CREATE TABLE test-schema (
    struct-id int,
    nested-1-1#value-1-1-1 string,
    nested-1-1#value-1-1-2 int,
    nested-1-1#value-1-1-3 float,
    nested-1-1#value-1-1-4#array0 string,
    nested-1-1#value-1-1-4#array1 string,
    nested-1-1#value-1-1-4#array2 string,
    nested-1-1#nested-2-1#test-2-1-1 string,
    nested-1-1#nested-2-1#test-2-1-2 int,
    nested-1-2#value-1-2-1#array0#id int,
    nested-1-2#value-1-2-1#array0#deeply-nested#data-1= =20 string,
    nested-1-2#value-1-2-1#array0#deeply-nested#data-2= =20 int,
    nested-1-2#id int,
    nested-1-2#data-3 string,
    PRIMARY KEY (struct-id)
)
 
I could use:
 
    nested-1-1#value-1-1-4 list<string>,
 
instead of:
 
    nested-1-1#value-1-1-4#array0 string,
    nested-1-1#value-1-1-4#array1 string,
    nested-1-1#value-1-1-4#array2 string,
 
yet it wouldn't work here:
 
    nested-1-2#value-1-2-1#array0#deeply-nested#data-1= =20 string,
    nested-1-2#value-1-2-1#array0#deeply-nested#data-2= =20 int,
    nested-1-2#value-1-2-1#array1#id int,
    nested-1-2#value-1-2-1#array1#data-3 string,
<= /div>
 
since this is a nested structure inside the list.
 
 
 
To create this schema, could we imagine that the app logging this try = to=20 write to the corresponding column, for each JSON attribute, and if the colu= mn is=20 missing, catch the error, create the column and reprocess write ?
 
This exception would happen for each new field, only once and would mo= dify=20 the schema.
 
Any thought that would help us (and probably more people) ?
 
Alain

--089e013d161eed8ed204fec9e324--