SQLコンソール

SQLコンソール は、ClickHouse Cloud でデータベースを調べたりクエリを実行したりするための、最も迅速で簡単な方法です。SQLコンソール では、次のことができます。

• ClickHouse Cloud サービスに接続する
• テーブルデータを表示、フィルタリング、ソートする
• わずか数クリックでクエリを実行し、結果データを可視化する
• クエリをチームメンバーと共有し、より効果的に共同作業する

ClickHouseインスタンスに含まれるテーブルの概要は、左側のサイドバーで確認できます。特定のデータベース内のテーブルを表示するには、左側バー上部のデータベースセレクタを使用します

一覧内のテーブルは展開して、カラムとデータ型を表示することもできます

リスト内のテーブルをクリックすると、新しいタブで開きます。Table View では、データを簡単に表示、選択、コピーできます。Microsoft Excel や Google Sheets などの表計算アプリケーションにコピー＆ペーストしても、構造とフォーマットは保持されます。フッターのナビゲーションを使って、テーブルデータのページ (30行単位でページ分割) を切り替えられます。

Cell Inspector ツールを使用すると、1 つのセルに含まれる大量のデータを表示できます。開くには、セルを右クリックして「Inspect Cell」を選択します。Cell Inspector の内容は、内容ペインの右上にあるコピーアイコンをクリックするとコピーできます。

SQLコンソールでテーブルをソートするには、テーブルを開き、ツールバーの「Sort」ボタンを選択します。このボタンをクリックするとメニューが開き、ソートを設定できます。ソート対象のカラムを選択し、ソート順 (昇順または降順) を指定できます。テーブルをソートするには、「Apply」を選択するか、Enterキーを押します

SQLコンソールでは、テーブルに複数のソートを追加することもできます。別のソートを追加するには、もう一度「Sort」ボタンをクリックします。注: ソートは、ソートペインに表示されている順序 (上から下) で適用されます。ソートを削除するには、そのソートの横にある「x」ボタンをクリックするだけです。

SQLコンソールでテーブルをフィルタリングするには、テーブルを開いて 'Filter' ボタンを選択します。ソートと同様に、このボタンをクリックすると、フィルタを設定するためのメニューが開きます。フィルタ対象のカラムを選び、必要な条件を指定できます。SQLコンソールでは、カラムに含まれるデータ型に応じたフィルタオプションが自動的に表示されます。

フィルタの設定が完了したら、'Apply' を選択してデータをフィルタリングできます。以下のように、さらにフィルタを追加することもできます。

ソート機能と同様に、フィルタの横にある 'x' ボタンをクリックすると削除できます。

SQLコンソールでは、テーブルに対してフィルタリングとソートを同時に行えます。これを行うには、上記の手順に従って必要なフィルタとソートをすべて追加し、[Apply] ボタンをクリックします。

SQLコンソールでは、設定したソートやフィルターをワンクリックでそのままクエリに変換できます。必要なソートやフィルターのパラメーターを指定し、ツールバーの「Create Query」ボタンを選択してください。「Create Query」をクリックすると、新しいクエリタブが開き、Table View内のデータに対応するSQLコマンドがあらかじめ入力された状態で表示されます。

SQLコンソールでのクエリについて詳しくは、(link) クエリのドキュメントを参照してください。

SQLコンソールで新しいクエリを作成する方法は、2つあります。

• タブバーの「+」ボタンをクリックします
• 左側のサイドバーにあるクエリ一覧から「New Query」ボタンを選択します

クエリを実行するには、SQL コマンドを SQL エディタに入力し、'Run' ボタンをクリックするか、ショートカット cmd / ctrl + enter を使用します。複数のコマンドを順番に記述して実行する場合は、各コマンドの末尾にセミコロンを付けてください。

クエリ実行オプション デフォルトでは、実行ボタンをクリックすると SQL エディタ内のすべてのコマンドが実行されます。SQL コンソールでは、このほかに 2 つのクエリ実行オプションを利用できます。

• 選択したコマンドを実行
• カーソル位置のコマンドを実行

選択したコマンドを実行するには、対象のコマンドまたは一連のコマンドを選択して 'Run' ボタンをクリックします (または cmd / ctrl + enter ショートカットを使用します)。選択範囲がある場合は、SQL エディタのコンテキストメニュー (エディタ内の任意の場所を右クリックして開きます) から 'Run selected' を選択することもできます。

現在のカーソル位置にあるコマンドは、次の 2 つの方法で実行できます。

• 拡張実行オプションメニューから 'At Cursor' を選択する (または対応する cmd / ctrl + shift + enter キーボードショートカットを使用する

• SQL エディタのコンテキストメニューから 'Run at cursor' を選択する

クエリの実行中は、Query Editor のツールバーにある 'Run' ボタンが 'Cancel' ボタンに切り替わります。このボタンをクリックするか、Esc キーを押すだけでクエリをキャンセルできます。注意: すでに返された結果は、キャンセル後もそのまま保持されます。

まだ名前を付けていない場合、クエリ名は「Untitled Query」になっています。名前を変更するには、クエリ名をクリックします。クエリ名を変更すると、クエリは保存されます。

保存ボタンまたは cmd / ctrl + s のキーボードショートカットを使ってクエリを保存することもできます。

この機能を使うと、自然言語で質問を入力するだけで、利用可能なテーブルのコンテキストに基づいてクエリコンソールが SQL クエリを生成します。GenAI は、クエリのデバッグにも役立ちます。

GenAI の詳細については、ClickHouse Cloud で GenAI によるクエリ候補を発表したブログ記事をご覧ください。

UK Price Paid のサンプルデータセットをインポートし、それを使っていくつかの GenAI クエリを作成しましょう。

1. ClickHouse Cloud サービスを開きます。

2. + アイコンをクリックして、新しいクエリを作成します。

3. 次のコードを貼り付けて実行します。

   CREATE TABLE uk_price_paid
   (
       price UInt32,
       date Date,
       postcode1 LowCardinality(String),
       postcode2 LowCardinality(String),
       type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
       is_new UInt8,
       duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
       addr1 String,
       addr2 String,
       street LowCardinality(String),
       locality LowCardinality(String),
       town LowCardinality(String),
       district LowCardinality(String),
       county LowCardinality(String)
   )
   ENGINE = MergeTree
   ORDER BY (postcode1, postcode2, addr1, addr2);

   このクエリは完了まで約 1 秒かかります。完了すると、uk_price_paid という空のテーブルが作成されます。

4. 新しいクエリを作成し、次のクエリを貼り付けます。

   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;

このクエリは gov.uk の Web サイトからデータセットを取得します。このファイルは約 4 GB あるため、完了まで数分かかります。ClickHouse がクエリの処理を終えると、uk_price_paid テーブルにデータセット全体が格納されます。

自然言語を使ってクエリを作成してみましょう。

1. uk_price_paid テーブルを選択し、Create Query をクリックします。

2. Generate SQL をクリックします。クエリが Chat-GPT に送信されることへの同意を求められる場合があります。続行するには I agree を選択する必要があります。

3. これで、このプロンプトに自然言語でクエリを入力すると、ChatGPT が SQL クエリに変換します。この例では、次のように入力します。

   年ごとに、uk_price_paid のすべてのトランザクションについて合計価格と総件数を表示してください。

4. コンソールによって目的のクエリが生成され、新しいタブに表示されます。この例では、GenAI は次のクエリを作成しました。

   -- 年ごとに、uk_price_paid のすべてのトランザクションについて合計価格と総件数を表示してください。
   SELECT year(date), sum(price) as total_price, Count(*) as total_transactions
   FROM uk_price_paid
   GROUP BY year(date)

5. クエリが正しいことを確認したら、Run をクリックして実行します。

では、GenAI のクエリデバッグ機能を試してみましょう。

1. + アイコンをクリックして新しいクエリを作成し、次のコードを貼り付けます。

   -- 年ごとの uk_price_paid の全取引について、合計価格と総件数を表示します。
   SELECT year(date), sum(pricee) as total_price, Count(*) as total_transactions
   FROM uk_price_paid
   GROUP BY year(date)

2. Run をクリックします。price ではなく pricee を参照しようとしているため、クエリは失敗します。

3. Fix Query をクリックします。

4. GenAI がクエリの修正を試みます。この場合、pricee を price に変更します。また、このケースでは toYear のほうが適切な関数であることも判断します。

5. Apply を選択して提案された変更をクエリに反映し、Run をクリックします。

GenAI は実験的な機能である点に注意してください。GenAI が生成したクエリをデータセットに対して実行する際は、注意して使用してください。

クエリを実行すると、結果ペインの検索入力を使って、返された結果セットをすばやく検索できます。この機能は、追加の WHERE 句の結果をプレビューしたり、特定のデータが結果セットに含まれていることを確認したりするのに役立ちます。検索入力に値を入力すると、結果ペインが更新され、入力した値に一致するエントリを含むレコードが表示されます。この例では、ClickHouse を含むコメントのうち、hackernews テーブル内で breakfast が含まれる箇所をすべて探します (大文字と小文字は区別されません):

注: 入力した値に一致するフィールドがあれば、そのレコードが表示されます。たとえば、上のスクリーンショットの 3 件目のレコードは by フィールドでは 'breakfast' に一致していませんが、text フィールドでは一致しています:

既定では、クエリの結果ペインにはすべての結果レコードが1ページに表示されます。結果セットが大きい場合は、見やすくするために結果をページ分けして表示したほうがよいことがあります。これは、結果ペインの右下にあるページネーションセレクターで設定できます。

ページサイズを選択すると、結果セットにすぐにページネーションが適用され、結果ペインのフッター中央にページ移動用のオプションが表示されます

クエリの結果セットは、SQLコンソールから直接CSV形式で簡単にエクスポートできます。その場合は、結果ペインのツールバー右側にある ••• メニューを開き、'Download as CSV' を選択します。

一部のデータは、グラフ形式で表示するとより理解しやすくなります。SQLコンソール から数回クリックするだけで、クエリ結果データから直接可視化をすばやく作成できます。例として、NYC のタクシー乗車の週次統計を計算するクエリを使用します。

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

可視化しないと、これらの結果は解釈しづらくなります。チャートにしてみましょう。

可視化の作成を始めるには、クエリ結果ペインのツールバーから「Chart」オプションを選択します。チャート設定ペインが表示されます。

まずは、week ごとの trip_total を示すシンプルな棒グラフを作成します。そのため、week フィールドを x 軸に、trip_total フィールドを y 軸にドラッグします。

ほとんどのグラフタイプでは、数値軸に複数のフィールドを設定できます。これを示すため、fare_total フィールドを y 軸にドラッグします。

SQLコンソールでは 10 種類のチャートを利用でき、チャート設定ペインのチャートタイプセレクターから選択できます。たとえば、前のチャートタイプを棒グラフからエリアチャートに簡単に変更できます。

チャートのタイトルは、データを提供するクエリ名に対応しています。クエリ名を更新すると、チャートのタイトルも更新されます。

より高度なチャートの設定も、チャート設定ペインの「Advanced」セクションで調整できます。まずは、次の設定を変更します。

• サブタイトル
• 軸タイトル
• x 軸ラベルの向き

チャートは次のように更新されます。

状況によっては、各フィールドの軸スケールを個別に調整する必要があります。これも、チャート設定ペインの「Advanced」セクションで軸の範囲の最小値と最大値を指定することで行えます。たとえば、上のチャートは見やすいものの、trip_total フィールドと fare_total フィールドの相関関係を示すには、軸の範囲を少し調整する必要があります。

SQLコンソールでは、チームとクエリを共有できます。クエリを共有すると、チームの全メンバーがそのクエリを表示・編集できるようになります。共有クエリは、チームで共同作業するのに便利です。

クエリを共有するには、クエリツールバーの「Share」ボタンをクリックします。

ダイアログが開き、チームの全メンバーとクエリを共有できるようになります。複数のチームに所属している場合は、どのチームとクエリを共有するかを選択できます。

状況によっては、各フィールドの軸スケールを個別に調整する必要があります。これも、チャート設定ペインの「Advanced」セクションで、軸の範囲の最小値と最大値を指定することで行えます。たとえば、上のチャートは見た目には問題ありませんが、trip_total と fare_total フィールドの相関関係を示すには、軸の範囲を多少調整する必要があります。