3 ms·

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 hav

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 https://github.com/skylandlabs/sky]

ozgune 14y ago
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.
- benbjohnson 14y ago
  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.
bgilroy26 14y ago
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?
- ozgune 14y ago
  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.
  - bkj123 14y ago
    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.
    - benbjohnson 14y ago
      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.
      - bgilroy26 14y ago
        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.

ozgune 14y ago

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.

benbjohnson 14y ago
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.

bgilroy26 14y ago

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?

ozgune 14y ago
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.
- bkj123 14y ago
  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.
  - benbjohnson 14y ago
    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.
    - bgilroy26 14y ago
      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.