Cassandra equivalent of group by

I'm in initial stages of data modeling in Cassandra for an application. This application has existing relational persistence layer, which has to be replaced with Cassandra.

Application uses a table named login_log for users, which provides last login time for any users in all applications.

Here's the CQL that I'm using to create this table

create table login_log (

    user_id int,

    application_name text,

    login_date timestamp,

    primary key (user_id, application_name, login_date)

) with clustering order by (application_name asc, login_date desc)

user_id is partition key. application_name and login_date are clustering keys. This table maintains a history of logins, data is never deleted in it.

I'm trying to retrieve last login date of given user_id for all applications in one query.

If I were writing a SQL query to do the same it would look something like below

select user_id, application_name, max(login_date) from login_log group by user_id, application_name

However it cannot be done in Cassandra, since there's no group by clause or aggregation functions. Arguably clustering columns are already grouped but I'm unable to create a query which retrieves latest login_date for all applications in one query.

If were doing it for one application CQL would look like below

select * from login_log where user_id = ? and application_name = ? limit 1

Since cluster are already ordered by login_date, order by is not needed. I need to extend the same query to retrieve data for all applications in one go.

Is it possible to do this in Cassandra? If not is there a data modeling technique that will allow me to do this?

Any hint is appreciated.

asked Nov 23 '18 at 22:09

11thdimension

7,49711236

select user_id, application_name, max(login_date) from login_log group by user_id, application_name would work with an appropriate max function.

– Chris Lohfink
Nov 24 '18 at 1:47

add a comment |

I'm in initial stages of data modeling in Cassandra for an application. This application has existing relational persistence layer, which has to be replaced with Cassandra.

Application uses a table named login_log for users, which provides last login time for any users in all applications.

Here's the CQL that I'm using to create this table

create table login_log (

    user_id int,

    application_name text,

    login_date timestamp,

    primary key (user_id, application_name, login_date)

) with clustering order by (application_name asc, login_date desc)

user_id is partition key. application_name and login_date are clustering keys. This table maintains a history of logins, data is never deleted in it.

I'm trying to retrieve last login date of given user_id for all applications in one query.

If I were writing a SQL query to do the same it would look something like below

select user_id, application_name, max(login_date) from login_log group by user_id, application_name

If were doing it for one application CQL would look like below

select * from login_log where user_id = ? and application_name = ? limit 1

Since cluster are already ordered by login_date, order by is not needed. I need to extend the same query to retrieve data for all applications in one go.

Is it possible to do this in Cassandra? If not is there a data modeling technique that will allow me to do this?

Any hint is appreciated.

asked Nov 23 '18 at 22:09

11thdimension

7,49711236

select user_id, application_name, max(login_date) from login_log group by user_id, application_name would work with an appropriate max function.

– Chris Lohfink
Nov 24 '18 at 1:47

add a comment |

I'm in initial stages of data modeling in Cassandra for an application. This application has existing relational persistence layer, which has to be replaced with Cassandra.

Application uses a table named login_log for users, which provides last login time for any users in all applications.

Here's the CQL that I'm using to create this table

create table login_log (

    user_id int,

    application_name text,

    login_date timestamp,

    primary key (user_id, application_name, login_date)

) with clustering order by (application_name asc, login_date desc)

user_id is partition key. application_name and login_date are clustering keys. This table maintains a history of logins, data is never deleted in it.

I'm trying to retrieve last login date of given user_id for all applications in one query.

If I were writing a SQL query to do the same it would look something like below

select user_id, application_name, max(login_date) from login_log group by user_id, application_name

If were doing it for one application CQL would look like below

select * from login_log where user_id = ? and application_name = ? limit 1

Since cluster are already ordered by login_date, order by is not needed. I need to extend the same query to retrieve data for all applications in one go.

Is it possible to do this in Cassandra? If not is there a data modeling technique that will allow me to do this?

Any hint is appreciated.

asked Nov 23 '18 at 22:09

11thdimension

7,49711236

I'm in initial stages of data modeling in Cassandra for an application. This application has existing relational persistence layer, which has to be replaced with Cassandra.

Application uses a table named login_log for users, which provides last login time for any users in all applications.

Here's the CQL that I'm using to create this table

create table login_log (

    user_id int,

    application_name text,

    login_date timestamp,

    primary key (user_id, application_name, login_date)

) with clustering order by (application_name asc, login_date desc)

user_id is partition key. application_name and login_date are clustering keys. This table maintains a history of logins, data is never deleted in it.

I'm trying to retrieve last login date of given user_id for all applications in one query.

If I were writing a SQL query to do the same it would look something like below

select user_id, application_name, max(login_date) from login_log group by user_id, application_name

If were doing it for one application CQL would look like below

select * from login_log where user_id = ? and application_name = ? limit 1

Since cluster are already ordered by login_date, order by is not needed. I need to extend the same query to retrieve data for all applications in one go.

Is it possible to do this in Cassandra? If not is there a data modeling technique that will allow me to do this?

Any hint is appreciated.

cassandra cql

asked Nov 23 '18 at 22:09

11thdimension

7,49711236

asked Nov 23 '18 at 22:09

11thdimension

7,49711236

asked Nov 23 '18 at 22:09

11thdimension

7,49711236

asked Nov 23 '18 at 22:09

11thdimension

7,49711236

asked Nov 23 '18 at 22:09

11thdimension

7,49711236

select user_id, application_name, max(login_date) from login_log group by user_id, application_name would work with an appropriate max function.

– Chris Lohfink
Nov 24 '18 at 1:47

add a comment |

select user_id, application_name, max(login_date) from login_log group by user_id, application_name would work with an appropriate max function.

– Chris Lohfink
Nov 24 '18 at 1:47

select user_id, application_name, max(login_date) from login_log group by user_id, application_name would work with an appropriate max function.

– Chris Lohfink
Nov 24 '18 at 1:47

add a comment |

1 Answer
1

active

oldest

votes

GROUP BY is supported from version 3.10 check improvement ticket here and Cassandra official documentation.

answered Nov 23 '18 at 22:37

Dalibor Grudenic

18117

Thank you, it works with latest version.

– 11thdimension
Nov 24 '18 at 3:06

When using UDAs or UDFs make sure you're staying within a partition. Cross partiiton UDAs / UDFs will not perform at scale.

– phact
Nov 24 '18 at 17:22

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53453405%2fcassandra-equivalent-of-group-by%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

GROUP BY is supported from version 3.10 check improvement ticket here and Cassandra official documentation.

answered Nov 23 '18 at 22:37

Dalibor Grudenic

18117

Thank you, it works with latest version.

– 11thdimension
Nov 24 '18 at 3:06

When using UDAs or UDFs make sure you're staying within a partition. Cross partiiton UDAs / UDFs will not perform at scale.

– phact
Nov 24 '18 at 17:22

add a comment |

GROUP BY is supported from version 3.10 check improvement ticket here and Cassandra official documentation.

answered Nov 23 '18 at 22:37

Dalibor Grudenic

18117

Thank you, it works with latest version.

– 11thdimension
Nov 24 '18 at 3:06

When using UDAs or UDFs make sure you're staying within a partition. Cross partiiton UDAs / UDFs will not perform at scale.

– phact
Nov 24 '18 at 17:22

add a comment |

GROUP BY is supported from version 3.10 check improvement ticket here and Cassandra official documentation.

answered Nov 23 '18 at 22:37

Dalibor Grudenic

18117

GROUP BY is supported from version 3.10 check improvement ticket here and Cassandra official documentation.

answered Nov 23 '18 at 22:37

Dalibor Grudenic

18117

answered Nov 23 '18 at 22:37

Dalibor Grudenic

18117

answered Nov 23 '18 at 22:37

Dalibor Grudenic

18117

answered Nov 23 '18 at 22:37

Dalibor Grudenic

18117

Thank you, it works with latest version.

– 11thdimension
Nov 24 '18 at 3:06

When using UDAs or UDFs make sure you're staying within a partition. Cross partiiton UDAs / UDFs will not perform at scale.

– phact
Nov 24 '18 at 17:22

add a comment |

Thank you, it works with latest version.

– 11thdimension
Nov 24 '18 at 3:06

When using UDAs or UDFs make sure you're staying within a partition. Cross partiiton UDAs / UDFs will not perform at scale.

– phact
Nov 24 '18 at 17:22

Thank you, it works with latest version.

– 11thdimension
Nov 24 '18 at 3:06

When using UDAs or UDFs make sure you're staying within a partition. Cross partiiton UDAs / UDFs will not perform at scale.

– phact
Nov 24 '18 at 17:22

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk