How to Run a SQL Query

26 January 2023 · Read time 3 min

Overview - Using the cluster Explore > Query

This example shows how to navigate from the cluster home page and how to use Explore > Query to run a query a ClickHouse database on the included demo cluster called github.

The following screenshot shows the step-by-step sequence of events, starting from your cluster home page (A), then selecting the ClickHouse database repository demo github cluster against which you will run the example SQL query in the Explore > Query screen (B).

To run a SQL query from the demo cluster named github:

1. Enter the URL into a browser: https://acm.altinity.cloud/clusters/
2. From the domain menu, select demo.
3. Within the cluster called github, select EXPLORE. Note that the URL changes to: https://acm.altinity.cloud/cluster/337/explore (see Figure 2 B-1) . Note that the menu title shows that CLUSTER:GITHUB is selected (see Figure 2 B-3).
4. Under the Query History, paste the text of the example SQL query.
5. Select EXECUTE to run the query.
6. Optionally, select and copy the results of the query.

Figure 1 – The home page showing all clusters.

Figure 2 – The Explore of the demo > github cluster viewing the Query tab.

SQL Query script

The following SQL script generates a 3-column report from a github database from 2019 to 2023 that the collates the number of pull requests (PRs) made by unique contributors.

SELECT toStartOfYear(merged_at) m, sum(merged) prs, uniq(creator_user_login) contributors
  FROM github_events
 WHERE merged_at>='2019-01-01'
   AND event_type = 'PullRequestEvent'
   AND repo_name in ('ClickHouse/ClickHouse', 'yandex/ClickHouse')
 GROUP BY m
 ORDER BY m

Code snippet 1 – The input, an example SQL query script to get 4 years of unique pull request contributor totals.

Query explanation

The SQL query visualization shows the Input (green) data sources and Output (red) 3 columns M, PRs and Contributors.

Figure 3 – The example SQL query script and visualization.

First select the start of the year for the merged_at column (line 1), the sum of the merged column (m), and the unique values in the creator_user_login column from the github_events table (line 2).

Include only the rows where the merged_at column is on or after January 1, 2019 (line 3) and the event_type column is ‘PullRequestEvent’ (line 4) and the repo_name column is either ‘ClickHouse/ClickHouse’ or ‘yandex/ClickHouse’ (line 5).

Group the results (line 6) by the start of the year for the merged_at column (from line 3)

Lastly, order the results (line 7) by the start of the year for the merged_at column.

SQL Query results

The results of the query appear below the EXECUTE button, listing 3 columns titled m (year), PRs and Contributors.

┌──────────m─┬──prs─┬─contributors─┐
│ 2019-01-01 │ 2278 │          232 │
│ 2020-01-01 │ 6000 │          300 │
│ 2021-01-01 │ 7766 │          366 │
│ 2022-01-01 │ 5639 │          392 │
│ 2023-01-01 │  157 │           41 │
└────────────┴──────┴──────────────┘

Code snippet 2 – The output, 3 columns of year (m), PRs and contributors showing 4 years of unique pull request contributor totals.