SQL to CQL: Dynamic vs. Static Schema

SQL CQL

  • Schema defined before data is loaded

  • Queries fail if they reference non-existent columns

  • Schema changes require ALTER TABLE operations

  • Schema can be discovered at query time

  • Fields can be referenced even if they don't exist in all events

  • New fields can be created on the fly

  • Data can be ingested and parsed into fields, including to the CrowdStrike Parsing Standard (CPS) 1.2

For example, within SQL the schema for the data must exist first, and the data is inserted into the table according to the structure:

sql
ALTER TABLE logs ADD COLUMN parsed_user VARCHAR(100);
UPDATE logs SET parsed_user = SUBSTRING_INDEX(user_string, '@', 1);
SELECT parsed_user, COUNT(*) FROM logs GROUP BY parsed_user;

With LogScale the string can be extracted from the original @rawstring:

logscale
regex("(?:<user_string>*@*)")
| groupby(parsed_user)