Distributed SQL queries are cool and accessible to people but I feel like projects that apply relational languages to event data don't make much sense. If I have click stream data then I'm more interested in knowing what users are doing after they performed action "A", "B" & "C" than rolling up how many users performed a single action "A". SQL falls flat on its face for this type of analysis.
Also, the name also threw me off. I thought it was "Citrus" and not "Citus".
There's value in both types of analyses. For knowing what users do after they perform action "A", "B" & "C", many people currently rely on implementing Map/Reduce programs.
That can be a bit heavyweight if you want to simply compare people who did action "A" or "B", filter based on complex criteria, or apply simple analytic functions. Also, apart from standard relational algebra operators, SQL provides a lot of convenience functions for math operations, string manipulations, date and time formatting, pattern matching, and so forth. These may come in handy to users who want to quickly gather insights out of their data.
You're right, there is value from SQL over event data, however, I feel like it's a missed opportunity to simply apply the same paradigms to a different type of data. I'm not suggesting that SQL be thrown out but a new language needs to be available specifically for event data.
I'm working outside the bounds of my understanding here, but why can't that result be formed from a query that pulls from the user_history table and a subquery that pulls from the user_history table with different conditions on each one?
Self joining is a good thought. It is possible to write a query like this but handling n+ steps (A..B, A..B..C, A..B..C..n) is eventually going to make your query optimizer sh*t all over itself in all likeliness. You're not simply joining two tables but you also have a temporal relationship between each event. For example, if you're looking for users who performed events A then B then C, then you need to self join B to A making sure that all events in B are after A and then all events in C are after B.
Beyond that you have to worry about whether A, B and C are all within the same session. Trying to define a session such as "all events that occurred until there is 30 minutes of idle time" is going to be damn near impossible in the SQL query.
Couldn't a recursive query help here? Oracle has had CONNECT BY for years, and SQL Server has had recursive queries since 2005 (not sure about open source).
And, if SQL isn't the best way, what is? Map-reduce seems hellish to write to me, requiring very advanced developer skills - maybe some Python code?
Oracle's CONNECT BY is a good idea. I forgot about that. CONNECT BY is technically for hierarchical queries but you could probably hack something together between connect by and windowing in Oracle. I'd be curious to see how the performance is.
Part of my behavioral database is a query language for event data. It's called EQL (Event Query Language). I don't mean to slam SQL to try to make my solution sound better. I wrangled event data with SQL at a company years ago and it was awful. Data was denormalized and stored in rows but we ran into issues of max column lengths and row chaining not to mention a ton of custom processing.
I've also tried using Redis as an event store but you really need your data processing on the same box as the data to get good performance. For example, Redis supports ~100K calls per second (depending on the command used) on a single box. You can retrieve multiple events at one time but you're still going to hit a network performance and CPU bottleneck serializing all that data, not to mention that you still need to process it. I built my behavioral database to compile EQL to optimized machine code using LLVM and then iterating over events in memory-mapped data files. As a comparison, I'm traversing about 50 million events per core per second.
I'm not trying to knock RDBMS systems or Redis. I used to be an Oracle DBA and I've used Redis on plenty of projects. Behavioral data is just a different beast though. It needs fundamentally different tools.
It's doable, but the paradigm doesn't naturally fit into SQL. Users typically need to use sub-selects and self-joins for this kind of "A", "B" & "C" analysis; and that introduces inefficiencies.
This is where a good analytical (as opposed to operational) data model comes into play. Something less normalized that is intuitive and efficient to use.
Event data is difficult to denormalize within a relational database. You can group events by actor (e.g. the user performing the events) and stuff it all into a single row. You'll get great performance but you'll have to custom process your data which is in a custom format. SQL functions will become useless.
You can also store every event as a row and pull it out through Hadoop to process (as another commenter mentioned) but you're going to get huge performance bottlenecks just in extracting the data from the database, serializing it, transferring to another server for processing, and then deserializing it. Not to mention that MapReduce is batch-oriented so it's not going to be real-time.
In a typical data environment for an event table, you can have a user_history table with the unique_primary_key, timestamp, user_id, attribute_column_name, Previous_value, and Subsequent_value.
If you want to know what users who did X and later did Y, couldn't you select all of the users who did X in a subquery and then find out how many of those user_id's match user_id's of people who did Y, where the timestamp on Y is between the X's timestamp and X's timestamp + some_predetermined_amount_of_time?
I am out of my depth, but isn't there some database best practice for tracking user session start and stop times?
Edit: I wrote this comment before I saw a different comment of yours above, which I think answers my question.
Comments
Distributed SQL queries are cool and accessible to people but I feel like projects that apply relational languages to event data don't make much sense. If I have click stream data then I'm more interested in knowing what users are doing after they performed action "A", "B" & "C" than rolling up how many users performed a single action "A". SQL falls flat on its face for this type of analysis.
Also, the name also threw me off. I thought it was "Citrus" and not "Citus".
[Full disclosure: I am writing an open source, distributed, behavioral database - https://github.com/skylandlabs/sky]
There's value in both types of analyses. For knowing what users do after they perform action "A", "B" & "C", many people currently rely on implementing Map/Reduce programs.
That can be a bit heavyweight if you want to simply compare people who did action "A" or "B", filter based on complex criteria, or apply simple analytic functions. Also, apart from standard relational algebra operators, SQL provides a lot of convenience functions for math operations, string manipulations, date and time formatting, pattern matching, and so forth. These may come in handy to users who want to quickly gather insights out of their data.
You're right, there is value from SQL over event data, however, I feel like it's a missed opportunity to simply apply the same paradigms to a different type of data. I'm not suggesting that SQL be thrown out but a new language needs to be available specifically for event data.
I'm working outside the bounds of my understanding here, but why can't that result be formed from a query that pulls from the user_history table and a subquery that pulls from the user_history table with different conditions on each one?
Self joining is a good thought. It is possible to write a query like this but handling n+ steps (A..B, A..B..C, A..B..C..n) is eventually going to make your query optimizer sh*t all over itself in all likeliness. You're not simply joining two tables but you also have a temporal relationship between each event. For example, if you're looking for users who performed events A then B then C, then you need to self join B to A making sure that all events in B are after A and then all events in C are after B.
Beyond that you have to worry about whether A, B and C are all within the same session. Trying to define a session such as "all events that occurred until there is 30 minutes of idle time" is going to be damn near impossible in the SQL query.
Couldn't a recursive query help here? Oracle has had CONNECT BY for years, and SQL Server has had recursive queries since 2005 (not sure about open source).
And, if SQL isn't the best way, what is? Map-reduce seems hellish to write to me, requiring very advanced developer skills - maybe some Python code?
Oracle's CONNECT BY is a good idea. I forgot about that. CONNECT BY is technically for hierarchical queries but you could probably hack something together between connect by and windowing in Oracle. I'd be curious to see how the performance is.
Part of my behavioral database is a query language for event data. It's called EQL (Event Query Language). I don't mean to slam SQL to try to make my solution sound better. I wrangled event data with SQL at a company years ago and it was awful. Data was denormalized and stored in rows but we ran into issues of max column lengths and row chaining not to mention a ton of custom processing.
I've also tried using Redis as an event store but you really need your data processing on the same box as the data to get good performance. For example, Redis supports ~100K calls per second (depending on the command used) on a single box. You can retrieve multiple events at one time but you're still going to hit a network performance and CPU bottleneck serializing all that data, not to mention that you still need to process it. I built my behavioral database to compile EQL to optimized machine code using LLVM and then iterating over events in memory-mapped data files. As a comparison, I'm traversing about 50 million events per core per second.
I'm not trying to knock RDBMS systems or Redis. I used to be an Oracle DBA and I've used Redis on plenty of projects. Behavioral data is just a different beast though. It needs fundamentally different tools.
It's doable, but the paradigm doesn't naturally fit into SQL. Users typically need to use sub-selects and self-joins for this kind of "A", "B" & "C" analysis; and that introduces inefficiencies.
This is where a good analytical (as opposed to operational) data model comes into play. Something less normalized that is intuitive and efficient to use.
Event data is difficult to denormalize within a relational database. You can group events by actor (e.g. the user performing the events) and stuff it all into a single row. You'll get great performance but you'll have to custom process your data which is in a custom format. SQL functions will become useless.
You can also store every event as a row and pull it out through Hadoop to process (as another commenter mentioned) but you're going to get huge performance bottlenecks just in extracting the data from the database, serializing it, transferring to another server for processing, and then deserializing it. Not to mention that MapReduce is batch-oriented so it's not going to be real-time.
In a typical data environment for an event table, you can have a user_history table with the unique_primary_key, timestamp, user_id, attribute_column_name, Previous_value, and Subsequent_value.
If you want to know what users who did X and later did Y, couldn't you select all of the users who did X in a subquery and then find out how many of those user_id's match user_id's of people who did Y, where the timestamp on Y is between the X's timestamp and X's timestamp + some_predetermined_amount_of_time?
I am out of my depth, but isn't there some database best practice for tracking user session start and stop times?
Edit: I wrote this comment before I saw a different comment of yours above, which I think answers my question.