SQL Console

This query should take around 1 second to complete. Once it's done, you should have an empty table called `uk\_price\_paid.
4. Create a new query and paste the following query:

   ```
   INSERT INTO uk_price_paid
   WITH
      splitByChar(' ', postcode) AS p
   SELECT
       toUInt32(price_string) AS price,
       parseDateTimeBestEffortUS(time) AS date,
       p[1] AS postcode1,
       p[2] AS postcode2,
       transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
       b = 'Y' AS is_new,
       transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration,
       addr1,
       addr2,
       street,
       locality,
       town,
       district,
       county
   FROM url(
       'http://prod.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv',
       'CSV',
       'uuid_string String,
       price_string String,
       time String,
       postcode String,
       a String,
       b String,
       c String,
       addr1 String,
       addr2 String,
       street String,
       locality String,
       town String,
       district String,
       county String,
       d String,
       e String'
   ) SETTINGS max_http_get_redirects=10;
   ```

This query grabs the dataset from the `gov.uk` website. This file is ~4GB, so this query will take a few minutes to complete. Once ClickHouse has processed the query, you should have the entire dataset within the `uk_price_paid` table.

#### Query creation[​](#query-creation "Direct link to Query creation")

Let's create a query using natural language.

1. Select the **uk\_price\_paid** table, and then click **Create Query**.
2. Click **Generate SQL**. You may be asked to accept that your queries are sent to Chat-GPT. You must select **I agree** to continue.
3. You can now use this prompt to enter a natural language query and have ChatGPT convert it into an SQL query. In this example we're going to enter:

   > Show me the total price and total number of all uk\_price\_paid transactions by year.
4. The console will generate the query we're looking for and display it in a new tab. In our example, GenAI created the following query:

   ```
   -- Show me the total price and total number of all uk_price_paid transactions by year.
   SELECT year(date), sum(price) as total_price, Count(*) as total_transactions
   FROM uk_price_paid
   GROUP BY year(date)
   ```
5. Once you've verified that the query is correct, click **Run** to execute it.

### Debugging[​](#debugging "Direct link to Debugging")

Now, let's test the query debugging capabilities of GenAI.

1. Create a new query by clicking the *+* icon and paste the following code:

   ```
   -- Show me the total price and total number of all uk_price_paid transactions by year.
   SELECT year(date), sum(pricee) as total_price, Count(*) as total_transactions
   FROM uk_price_paid
   GROUP BY year(date)
   ```
2. Click **Run**. The query fails since we're trying to get values from `pricee` instead of `price`.
3. Click **Fix Query**.
4. GenAI will attempt to fix the query. In this case, it changed `pricee` to `price`. It also realised that `toYear` is a better function to use in this scenario.
5. Select **Apply** to add the suggested changes to your query and click **Run**.

Keep in mind that GenAI is an experimental feature. Use caution when running GenAI-generated queries against any dataset.

## Advanced querying features[​](#advanced-querying-features "Direct link to Advanced querying features")

### Searching query results[​](#searching-query-results "Direct link to Searching query results")

After a query is executed, you can quickly search through the returned result set using the search input in the result pane. This feature assists in previewing the results of an additional `WHERE` clause or simply checking to ensure that specific data is included in the result set. After inputting a value into the search input, the result pane will update and return records containing an entry that matches the inputted value. In this example, we'll look for all instances of `breakfast` in the `hackernews` table for comments that contain `ClickHouse` (case-insensitive):

Note: Any field matching the inputted value will be returned. For example, the third record in the above screenshot doesn't match 'breakfast' in the `by` field, but the `text` field does:

### Adjusting pagination settings[​](#adjusting-pagination-settings "Direct link to Adjusting pagination settings")

By default, the query result pane will display every result record on a single page. For larger result sets, it may be preferable to paginate results for easier viewing. This can be accomplished using the pagination selector in the bottom right corner of the result pane:

Selecting a page size will immediately apply pagination to the result set and navigation options will appear in the middle of the result pane footer

### Exporting query result data[​](#exporting-query-result-data "Direct link to Exporting query result data")

Query result sets can be easily exported to CSV format directly from the SQL console. To do so, open the `•••` menu on the right side of the result pane toolbar and select 'Download as CSV'.

## Visualizing query data[​](#visualizing-query-data "Direct link to Visualizing query data")

Some data can be more easily interpreted in chart form. You can quickly create visualizations from query result data directly from the SQL console in just a few clicks. As an example, we'll use a query that calculates weekly statistics for NYC taxi trips:

```
SELECT
   toStartOfWeek(pickup_datetime) AS week,
   sum(total_amount) AS fare_total,
   sum(trip_distance) AS distance_total,
   count(*) AS trip_total
FROM
   nyc_taxi
GROUP BY
   1
ORDER BY
   1 ASC
```

Without visualization, these results are difficult to interpret. Let's turn them into a chart.

### Creating charts[​](#creating-charts "Direct link to Creating charts")

To begin building your visualization, select the 'Chart' option from the query result pane toolbar. A chart configuration pane will appear:

We'll start by creating a simple bar chart tracking `trip_total` by `week`. To accomplish this, we'll drag the `week` field to the x-axis and the `trip_total` field to the y-axis:

Most chart types support multiple fields on numeric axes. To demonstrate, we'll drag the fare\_total field onto the y-axis:

### Customizing charts[​](#customizing-charts "Direct link to Customizing charts")

The SQL console supports ten chart types that can be selected from the chart type selector in the chart configuration pane. For example, we can easily change the previous chart type from Bar to an Area:

Chart titles match the name of the query supplying the data. Updating the name of the query will cause the Chart title to update as well:

A number of more advanced chart characteristics can also be adjusted in the 'Advanced' section of the chart configuration pane. To begin, we'll adjust the following settings:

- Subtitle
- Axis titles
- Label orientation for the x-axis

Our chart will be updated accordingly:

In some scenarios, it may be necessary to adjust the axis scales for each field independently. This can also be accomplished in the 'Advanced' section of the chart configuration pane by specifying min and max values for an axis range. As an example, the above chart looks good, but in order to demonstrate the correlation between our `trip_total` and `fare_total` fields, the axis ranges need some adjustment:

## Sharing queries[​](#sharing-queries "Direct link to Sharing queries")

The SQL console enables you to share queries with your team. When a query is shared, all members of the team can see and edit the query. Shared queries are a great way to collaborate with your team.

To share a query, click the 'Share' button in the query toolbar.

A dialog will open, allowing you to share the query with all members of a team. If you have multiple teams, you can select which team to share the query with.