# Querying events & time series Create your first client-facing API in [Queries](https://betterstack.com/docs/warehouse/querying-data/queries/) or connect to Better Stack Warehouse to run exploratory queries with the [Ad-hoc SQL API](https://betterstack.com/docs/warehouse/querying-data/ad-hoc-sql-api/). Explore your events in **Warehouse** -> [Queries as APIs](https://warehouse.betterstack.com/team/t0/queries ";_blank") -> [Create query](https://warehouse.betterstack.com/team/t0/queries/new ";_blank"). Select a **JSON events source**, and use the following **SQL**: ```sql [label SQL query counting number of events by level] SELECT {{time}} AS time, COUNT(*) AS value, JSONExtract(raw, 'level', 'Nullable(String)') AS series FROM {{source}} WHERE time BETWEEN {{start_time}} AND {{end_time}} GROUP BY time, series ``` **[ClickHouse SQL](https://clickhouse.com/docs/en/sql-reference)** queries are largely similar to ANSI SQL you’re likely familiar with. In the query above, we use `COUNT(*)` to get the number of events for each `level`. ### Extracting event fields The easiest way to extract a particular event field is to use the JSON dot notation: ```sql [label Extracting JSON attribute using JSON dot notation] SELECT json.my_field.my_value FROM {{source}} ``` Alternatively, extract top-level or nested fields using `JSONExtract()` function: - **Top-level field** `level`: `JSONExtract(raw, 'level', 'Nullable(String)')` - **Nested field** `context.request.status`: `JSONExtract(raw, 'context', 'request', 'status', 'Nullable(String)')` #### Case-insensitive field access If the case of fields in your events can vary (for example, `level` and `Level`), you can use `JSONExtractCaseInsensitive`, in the same way as `JSONExtract`. `JSONExtractCaseInsensitive(raw, 'level', 'Nullable(String)')` would fetch either `level` or `Level` from an event, depending on which is present. ### Query variables On top of SQL, SQL queries feature variables like `{{source}}` or `{{time}}`. Variables conveniently insert selected source, current time range, and other selected values into your queries. Read more about [query variables](https://betterstack.com/docs/logs/dashboards/variables/). ## Video overview
## Examples for querying events ### Chart number of events by host Chart how many events your servers produce: ```sql [label SQL query counting number of events by host] SELECT {{time}} AS time, COUNT(*) AS value, JSONExtract(raw, 'context', 'hostname', 'Nullable(String)') AS series FROM {{source}} WHERE time BETWEEN {{start_time}} AND {{end_time}} GROUP BY time, series ``` [note] #### Want to use the extracted field fast APIs? Extract them as [time series](https://betterstack.com/docs/warehouse/ingesting-data/s3-vs-nvme/#time-series) to be stored on fast NVMe SSD drives. Once added, you can use `hostname` column in [Queries](https://betterstack.com/docs/warehouse/querying-data/queries/) or [Ad-hoc SQL API](https://betterstack.com/docs/warehouse/querying-data/ad-hoc-sql-api/) when you select `time series` in the source picker. [/note] ### Track specific error in your events Match specific errors in events `message` field using `CASE` and `LIKE`. The `LIKE` operator performs a case-sensitive search: ```sql [label SQL query to match a specific error] SELECT {{time}} AS time, COUNT(CASE WHEN JSONExtractString(raw, 'message') LIKE '%connection lost%' THEN TRUE ELSE FALSE END) AS value FROM {{source}} WHERE time BETWEEN {{start_time}} AND {{end_time}} GROUP BY time ``` ### Use IP-based geolocation If your event data includes IP addresses, you can [transform both IPv4 and IPv6 into coordinates](https://betterstack.com/docs/logs/visualizing-geographical-data/#using-sql-expression-with-ip-geolocation) and even visualize their distribution on a map. ## Querying time series Create a new time series query: ```sql [label Query counting the number of events by level] SELECT {{time}} AS time, countMerge(events_count) AS value, level AS series FROM {{source}} WHERE time BETWEEN {{start_time}} AND {{end_time}} GROUP BY time, series ``` ![Chart preview](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/f3c07c18-9580-4964-c7d5-82112f7e1b00/lg2x =1932x958) [warning] #### Getting `Missing columns` error? Try selecting a different source or [add a time series for the column](https://betterstack.com/docs/warehouse/ingesting-data/s3-vs-nvme/#time-series) to your source. [/warning] Time series queries use [ClickHouse SQL](https://clickhouse.com/docs/en/sql-reference), which is largely similar to ANSI SQL you’re likely familiar with. In the SQL query above, we leverage the `countMerge` [ClickHouse function](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-merge). [info] #### Want an in-depth guide on ClickHouse aggregations? Check out the official blog post about [ClickHouse aggregate combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states) [/info] On top of SQL, dashboard queries feature variables like `{{source}}` or `{{time}}`. Variables conveniently insert selected source, current time range, and other selected values into your queries. Read more about [query variables](https://betterstack.com/docs/logs/dashboards/variables/). Queries don't access your events directly. To make querying fast and efficient we process your events into time series and store them as [ClickHouse -State combinators](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-state). Time series queries use [ClickHouse -Merge functions](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-merge) to get the values of the time series. Read more about [querying time series](#how-to-query-time-series) below. [info] #### Not familiar with SQL? Follow our examples below or start with [SQL tutorial from W3Schools](https://www.w3schools.com/sql/sql_syntax.asp) [/info] [info] #### Curious about ClickHouse SQL? Read more about [ClickHouse SQL syntax](https://clickhouse.com/docs/en/sql-reference/syntax) and [compatibility with ANSI SQL](https://clickhouse.com/docs/en/sql-reference/ansi) [/info] ## How to query time series? [warning] #### Want to count the number of lines? Use `countMerge(events_count)`. Avoid using `count()` as it returns incorrect results. Time series tables don't have the same number of rows as the original event tables. [/warning] Query time series with no aggregations by their name just like regular database columns. For time series with aggregations, use `avgMerge(time_series_avg)` to get the average value of a time series. Similarly, use `minMerge(time_series_min)` and `maxMerge(time_series_max)` to get a time series' minimum and maximum values. Make sure to always match the -Merge function with the aggregation suffix correctly: - ✅ Correct: Using **sum**Merge with **_sum** suffix: `sumMerge(time_series_sum)` - ❌ Incorrect: Mixing **sum** and **count**: `sumMerge(time_series_count)` **Want to use percentiles?** Check out the [complete example on percentiles](#percentile-request-duration) below. ### Which time series can you query? You can [add more time series](https://betterstack.com/docs/warehouse/ingesting-data/s3-vs-nvme/#time-series) to use in your SQL queries. ![Screenshot of Logs to metrics tables](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/3f146f41-9e37-4862-bba1-cac0bd20e000/md1x =2052x1024) ## Examples for querying time series See the examples below for full queries using **Time series**. ### Number of errors Chart the number of events with the error event level: ```sql [label Query counting number of errors from NVMe SSD-stored time series] SELECT {{time}} AS time, countMerge(events_count) AS value, level AS series FROM {{source}} WHERE time BETWEEN {{start_time}} AND {{end_time}} AND level = 'error' GROUP BY time, series ``` Use `countMerge(events_count)` to count the number of events using time series. Avoid using `count(*)`. Filter error events using the `level` field. The time series has to be present in **Time series on NVMe SSDs** configuration with **No aggregation** selected. ### Average request duration Chart the average duration of request: ```sql [label Query for average request duration] SELECT {{time}} AS time, avgMerge(request_duration_avg) AS "Request duration" FROM {{source}} WHERE time BETWEEN {{start_time}} AND {{end_time}} GROUP BY time ``` Use `avgMerge(request_duration_avg)` to calculate an average for the `request_duration` time series. The time series has to be added in **Warehouse** -> [Sources](https://warehouse.betterstack.com/team/0/sources ";_blank") -> Your source -> **Time series on NVMe SSD** with `avg` aggregation. Column names `Request duration` will be used in chart tooltips and the legend. Select which column should be charted on the Y-axis on the right side in **Chart setting** → **Axes**. ### Percentile request duration Chart the 95th percentile request duration: ```sql [label Query for percentile request duration] SELECT {{time}} AS time, quantilesMerge(0.5, 0.9, 0.95, 0.99)(request_duration_quantiles)[3] AS "95th percentile duration" FROM {{source}} WHERE time BETWEEN {{start_time}} AND {{end_time}} GROUP BY time, series ``` Here we have the `request_duration` time series with `p50`, `p90`, `p95`, and `p99` percentiles set up in Time series expressions. Calculate the 95th percentile request duration using `quantilesMerge`. The `quantilesMerge` function gives us all four percentiles. We select the third percentile using `[3]`. [info] #### Want to learn how to use the aggregation functions? Check out the official guide on [ClickHouse Aggregate Combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states) [/info] ## Need help? Please let us know at hello@betterstack.com. We're happy to help! 🙏