SQL to CQL

Structured Query Language (SQL) is an international standard (ISO) for query structured information. If you are familiar with SQL and are moving to using CQL, this guide will cover the main differences of approach and how to translate existing SQL queries to CQL.

There are some fundamental differences in the way the two languages work and access information:

  • LogScale is designed for searching and analyzing log data, not a relational database.

  • LogScale queries operate on unstructured/semi-structured data vs. SQL's structured tables.

  • LogScale Time-series orientation vs. relational model.

Some of these differences are exposed within the querying at a philosophical level, and others at a practical level:

SQL CQL
Data Model and Purpose

  • Designed for structured, relational data with predefined schemas

  • Optimized for transactional operations and data integrity

  • Focused on precise data retrieval from normalized tables

  • Built around the concept of relations between tables

  • Designed for unstructured or semi-structured log data

  • Optimized for search, analysis, and pattern detection

  • Focused on exploring and extracting insights from large volumes of time-series data

  • Built around the concept of events occurring over time

Query Paradigm

  • Declarative approach: You specify WHAT data you want, not HOW to get it

  • Query optimizer determines execution plan

  • Set-based operations on tables

  • Static, predefined schema enforced at write time

  • Pipeline approach: Data flows through transformations

  • Each operation builds on the previous one

  • Stream-based processing of events

  • Dynamic schema discovered at query time

Data Extraction vs. Data Transformation

  • Primarily focused on data extraction and basic aggregation

  • Transformations often limited to what's expressible in SELECT clauses

  • Complex transformations may require multiple queries or stored procedures

  • Data formatting typically handled by application layer

  • Combines extraction, transformation, and formatting in a single query

  • Rich set of transformation functions built into the query language

  • Pipeline approach makes complex transformations more intuitive

  • Formatting capabilities (charts, tables, etc.) integrated into query results

To summarize:

  • SQL is primarily about structured data retrieval from relational databases with predefined schemas, using a declarative approach to specify what data you want.

  • CQL is about data exploration and analysis of time-series log data, using a pipeline approach to transform, correlate, and visualize data in a single query flow.

  • SQL excels at transactional operations and maintaining data integrity across related tables.

  • CQL excels at pattern detection and extracting insights from large volumes of unstructured or semi-structured log data.

  • SQL requires more application-level processing for complex transformations and visualizations.

  • CQL integrates transformation, analysis, and visualization capabilities directly into the query language.

This fundamental difference in approach means that SQL users transitioning to CQL need to shift their thinking from "tables and relations" to "events and transformations," and from "what data do I want" to "how do I want to process this data stream."