ClickHouse におけるストアドプロシージャとクエリパラメータ

従来のリレーショナルデータベースを使ってきた方は、ClickHouse にもストアドプロシージャやプリペアドステートメントがあるのか気になっているかもしれません。 このガイドでは、これらの概念に対する ClickHouse の考え方を説明し、推奨される代替手段を紹介します。

ClickHouse におけるストアドプロシージャの代替手段

ClickHouse は、IF/ELSE やループなどの制御フローを含む、従来型のストアドプロシージャをサポートしていません。 これは、分析データベースとしての ClickHouse のアーキテクチャに基づいた、意図的な設計上の判断です。 分析データベースでは、多数の単純なクエリを O(n) 回処理するよりも、少数の複雑なクエリとして処理する方が一般に高速であるため、ループは推奨されません。

ClickHouse は次の用途向けに最適化されています:

• 分析ワークロード - 大規模なデータセットに対する複雑な集約処理
• バッチ処理 - 大量データを効率的に処理
• 宣言的クエリ - データの処理方法ではなく、どのデータを取得するかを記述する SQL クエリ

手続き型ロジックを伴うストアドプロシージャは、これらの最適化に反します。その代わりに、ClickHouse は自らの強みと整合する代替手段を提供しています。

ユーザー定義関数 (UDF)

ユーザー定義関数を使うと、制御フローを用いずに再利用可能なロジックをカプセル化できます。ClickHouse は 2 種類のユーザー定義関数をサポートしています。

ラムダベースの UDF

SQL 式とラムダ構文を使って関数を作成します。

サンプルデータ（例で使用）

-- products テーブルを作成
CREATE TABLE products (
    product_id UInt32,
    product_name String,
    price Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY product_id;

-- サンプルデータを挿入
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 899.99),
(2, 'Wireless Mouse', 24.99),
(3, 'USB-C Cable', 12.50),
(4, 'Monitor', 299.00),
(5, 'Keyboard', 79.99),
(6, 'Webcam', 54.95),
(7, 'Desk Lamp', 34.99),
(8, 'External Hard Drive', 119.99),
(9, 'Headphones', 149.00),
(10, 'Phone Stand', 15.99);

-- 税額計算関数
CREATE FUNCTION calculate_tax AS (price, rate) -> price * rate;

SELECT
    product_name,
    price,
    calculate_tax(price, 0.08) AS tax
FROM products;

-- if()を使用した条件分岐
CREATE FUNCTION price_tier AS (price) ->
    if(price < 100, '低価格帯',
       if(price < 500, '中価格帯', '高価格帯'));

SELECT
    product_name,
    price,
    price_tier(price) AS tier
FROM products;

-- 文字列操作
CREATE FUNCTION format_phone AS (phone) ->
    concat('(', substring(phone, 1, 3), ') ',
           substring(phone, 4, 3), '-',
           substring(phone, 7, 4));

SELECT format_phone('5551234567');
-- 結果: (555) 123-4567

制限事項:

• ループや複雑な制御フローは使用できません
• データの変更（INSERT/UPDATE/DELETE）はできません
• 再帰関数は使用できません

完全な構文については CREATE FUNCTION を参照してください。

実行可能 UDF

より複雑なロジックには、外部プログラムを呼び出す実行可能 UDF を使用します。

<!-- /etc/clickhouse-server/sentiment_analysis_function.xml -->
<functions>
    <function>
        <type>executable</type>
        <name>sentiment_score</name>
        <return_type>Float32</return_type>
        <argument>
            <type>String</type>
        </argument>
        <format>TabSeparated</format>
        <command>python3 /opt/scripts/sentiment.py</command>
    </function>
</functions>

-- 実行可能なUDFを使用
SELECT
    review_text,
    sentiment_score(review_text) AS score
FROM customer_reviews;

実行可能な UDF は、任意の言語（Python、Node.js、Go など）で任意の処理ロジックを実装できます。

詳細については、実行可能 UDF を参照してください。

パラメーター化ビュー

パラメーター化ビューは、データセットを返す関数のように振る舞います。 動的フィルタリングを行う再利用可能なクエリに最適です。

例で使用するサンプルデータ

-- sales テーブルを作成
CREATE TABLE sales (
  date Date,
  product_id UInt32,
  product_name String,
  category String,
  quantity UInt32,
  revenue Decimal(10, 2),
  sales_amount Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY (date, product_id);

-- サンプルデータを挿入
INSERT INTO sales VALUES
('2024-01-05', 12345, 'Laptop Pro', 'Electronics', 2, 1799.98, 1799.98),
('2024-01-06', 12345, 'Laptop Pro', 'Electronics', 1, 899.99, 899.99),
('2024-01-10', 12346, 'Wireless Mouse', 'Electronics', 5, 124.95, 124.95),
('2024-01-15', 12347, 'USB-C Cable', 'Accessories', 10, 125.00, 125.00),
('2024-01-20', 12345, 'Laptop Pro', 'Electronics', 3, 2699.97, 2699.97),
('2024-01-25', 12348, 'Monitor 4K', 'Electronics', 2, 598.00, 598.00),
('2024-02-01', 12345, 'Laptop Pro', 'Electronics', 1, 899.99, 899.99),
('2024-02-05', 12349, 'Keyboard Mechanical', 'Accessories', 4, 319.96, 319.96),
('2024-02-10', 12346, 'Wireless Mouse', 'Electronics', 8, 199.92, 199.92),
('2024-02-15', 12350, 'Webcam HD', 'Electronics', 3, 164.85, 164.85);

-- パラメータ化ビューを作成
CREATE VIEW sales_by_date AS
SELECT
    date,
    product_id,
    sum(quantity) AS total_quantity,
    sum(revenue) AS total_revenue
FROM sales
WHERE date BETWEEN {start_date:Date} AND {end_date:Date}
GROUP BY date, product_id;

-- パラメータを使用してビューをクエリする
SELECT *
FROM sales_by_date(start_date='2024-01-01', end_date='2024-01-31')
WHERE product_id = 12345;

一般的なユースケース

• 動的な日付範囲によるフィルタリング
• ユーザーごとのデータスライス
• マルチテナント環境でのデータアクセス
• レポートテンプレート
• データマスキング

-- より複雑なパラメータ化ビュー
CREATE VIEW top_products_by_category AS
SELECT
    category,
    product_name,
    revenue,
    rank
FROM (
    SELECT
        category,
        product_name,
        revenue,
        rank() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank
    FROM (
        SELECT
            category,
            product_name,
            sum(sales_amount) AS revenue
        FROM sales
        WHERE category = {category:String}
            AND date >= {min_date:Date}
        GROUP BY category, product_name
    )
)
WHERE rank <= {top_n:UInt32};

-- 使用方法
SELECT * FROM top_products_by_category(
    category='Electronics',
    min_date='2024-01-01',
    top_n=10
);

詳しくは、Parameterized Views セクションを参照してください。

マテリアライズドビュー

マテリアライズドビューは、従来はストアドプロシージャで行っていたようなコストの高い集計処理を、事前に計算・集約しておくのに最適です。従来型のデータベースに慣れている場合、マテリアライズドビューは、ソーステーブルにデータが挿入されるタイミングで自動的にデータを変換・集計する INSERT トリガー と考えることができます。

-- ソーステーブル
CREATE TABLE page_views (
    user_id UInt64,
    page String,
    timestamp DateTime,
    session_id String
)
ENGINE = MergeTree()
ORDER BY (user_id, timestamp);

-- 集計統計を保持するマテリアライズドビュー
CREATE MATERIALIZED VIEW daily_user_stats
ENGINE = SummingMergeTree()
ORDER BY (date, user_id)
AS SELECT
    toDate(timestamp) AS date,
    user_id,
    count() AS page_views,
    uniq(session_id) AS sessions,
    uniq(page) AS unique_pages
FROM page_views
GROUP BY date, user_id;

-- ソーステーブルにサンプルデータを挿入
INSERT INTO page_views VALUES
(101, '/home', '2024-01-15 10:00:00', 'session_a1'),
(101, '/products', '2024-01-15 10:05:00', 'session_a1'),
(101, '/checkout', '2024-01-15 10:10:00', 'session_a1'),
(102, '/home', '2024-01-15 11:00:00', 'session_b1'),
(102, '/about', '2024-01-15 11:05:00', 'session_b1'),
(101, '/home', '2024-01-16 09:00:00', 'session_a2'),
(101, '/products', '2024-01-16 09:15:00', 'session_a2'),
(103, '/home', '2024-01-16 14:00:00', 'session_c1'),
(103, '/products', '2024-01-16 14:05:00', 'session_c1'),
(103, '/products', '2024-01-16 14:10:00', 'session_c1'),
(102, '/home', '2024-01-17 10:30:00', 'session_b2'),
(102, '/contact', '2024-01-17 10:35:00', 'session_b2');

-- 事前集計データをクエリ
SELECT
    user_id,
    sum(page_views) AS total_views,
    sum(sessions) AS total_sessions
FROM daily_user_stats
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY user_id;

リフレッシュ可能なマテリアライズドビュー

スケジュールされたバッチ処理（夜間に実行されるストアドプロシージャなど）の場合：

-- 毎日午前2時に自動更新
CREATE MATERIALIZED VIEW monthly_sales_report
REFRESH EVERY 1 DAY OFFSET 2 HOUR
AS SELECT
    toStartOfMonth(order_date) AS month,
    region,
    product_category,
    count() AS order_count,
    sum(amount) AS total_revenue,
    avg(amount) AS avg_order_value
FROM orders
WHERE order_date >= today() - INTERVAL 13 MONTH
GROUP BY month, region, product_category;

-- クエリは常に最新データを保持
SELECT * FROM monthly_sales_report
WHERE month = toStartOfMonth(today());

高度なパターンについては、カスケード型マテリアライズドビューを参照してください。

外部オーケストレーション

複雑なビジネスロジック、ETL ワークフロー、または複数ステップの処理が必要な場合は、ClickHouse の外側で 言語クライアントを使用してロジックを実装することも可能です。

アプリケーションコードを使用する

ここでは、MySQL のストアドプロシージャを、ClickHouse を用いたアプリケーションコードに書き換えた場合の対応関係を、左右の比較で示します。

MySQL ストアドプロシージャ

DELIMITER $$

CREATE PROCEDURE process_order(
    IN p_order_id INT,
    IN p_customer_id INT,
    IN p_order_total DECIMAL(10,2),
    OUT p_status VARCHAR(50),
    OUT p_loyalty_points INT
)
BEGIN
    DECLARE v_customer_tier VARCHAR(20);
    DECLARE v_previous_orders INT;
    DECLARE v_discount DECIMAL(10,2);

    -- トランザクションを開始
    START TRANSACTION;

    -- 顧客情報を取得
    SELECT tier, total_orders
    INTO v_customer_tier, v_previous_orders
    FROM customers
    WHERE customer_id = p_customer_id;

    -- ティアに基づいて割引を計算
    IF v_customer_tier = 'gold' THEN
        SET v_discount = p_order_total * 0.15;
    ELSEIF v_customer_tier = 'silver' THEN
        SET v_discount = p_order_total * 0.10;
    ELSE
        SET v_discount = 0;
    END IF;

    -- 注文レコードを挿入
    INSERT INTO orders (order_id, customer_id, order_total, discount, final_amount)
    VALUES (p_order_id, p_customer_id, p_order_total, v_discount,
            p_order_total - v_discount);

    -- 顧客統計を更新
    UPDATE customers
    SET total_orders = total_orders + 1,
        lifetime_value = lifetime_value + (p_order_total - v_discount),
        last_order_date = NOW()
    WHERE customer_id = p_customer_id;

    -- ロイヤルティポイントを計算（1ドルあたり1ポイント）
    SET p_loyalty_points = FLOOR(p_order_total - v_discount);

    -- ロイヤルティポイントトランザクションを挿入
    INSERT INTO loyalty_points (customer_id, points, transaction_date, description)
    VALUES (p_customer_id, p_loyalty_points, NOW(),
            CONCAT('Order #', p_order_id));

    -- 顧客のアップグレード要否を確認
    IF v_previous_orders + 1 >= 10 AND v_customer_tier = 'bronze' THEN
        UPDATE customers SET tier = 'silver' WHERE customer_id = p_customer_id;
        SET p_status = 'ORDER_COMPLETE_TIER_UPGRADED_SILVER';
    ELSEIF v_previous_orders + 1 >= 50 AND v_customer_tier = 'silver' THEN
        UPDATE customers SET tier = 'gold' WHERE customer_id = p_customer_id;
        SET p_status = 'ORDER_COMPLETE_TIER_UPGRADED_GOLD';
    ELSE
        SET p_status = 'ORDER_COMPLETE';
    END IF;

    COMMIT;
END$$

DELIMITER ;

-- ストアドプロシージャを呼び出す
CALL process_order(12345, 5678, 250.00, @status, @points);
SELECT @status, @points;

ClickHouse アプリケーションのコード

クエリパラメータ

以下の例では、ClickHouseのクエリパラメータを使用しています。 ClickHouseのクエリパラメータにまだ馴染みがない場合は、"ClickHouseにおけるプリペアドステートメントの代替手段"を参照してください。

# clickhouse-connectを使用したPythonの例
import clickhouse_connect
from datetime import datetime
from decimal import Decimal

client = clickhouse_connect.get_client(host='localhost')

def process_order(order_id: int, customer_id: int, order_total: Decimal) -> tuple[str, int]:
    """
    ストアドプロシージャに含まれるようなビジネスロジックで注文を処理します。
    戻り値: (status_message, loyalty_points)

    注意: ClickHouseは分析処理に最適化されており、OLTPトランザクションには適していません。
    トランザクション処理が必要な場合は、OLTPデータベース（PostgreSQL、MySQL）を使用し、
    分析データをClickHouseに同期してレポート作成を行ってください。
    """

    # ステップ1: 顧客情報を取得
    result = client.query(
        """
        SELECT tier, total_orders
        FROM customers
        WHERE customer_id = {cid: UInt32}
        """,
        parameters={'cid': customer_id}
    )

    if not result.result_rows:
        raise ValueError(f"顧客 {customer_id} が見つかりません")

    customer_tier, previous_orders = result.result_rows[0]

    # ステップ2: ティアに基づいて割引を計算（Pythonのビジネスロジック）
    discount_rates = {'gold': 0.15, 'silver': 0.10, 'bronze': 0.0}
    discount = order_total * Decimal(str(discount_rates.get(customer_tier, 0.0)))
    final_amount = order_total - discount

    # ステップ3: 注文レコードを挿入
    client.command(
        """
        INSERT INTO orders (order_id, customer_id, order_total, discount,
                           final_amount, order_date)
        VALUES ({oid: UInt32}, {cid: UInt32}, {total: Decimal64(2)},
                {disc: Decimal64(2)}, {final: Decimal64(2)}, now())
        """,
        parameters={
            'oid': order_id,
            'cid': customer_id,
            'total': float(order_total),
            'disc': float(discount),
            'final': float(final_amount)
        }
    )

    # ステップ4: 新しい顧客統計を計算
    new_order_count = previous_orders + 1

    # 分析データベースでは、UPDATEよりもINSERTを優先します
    # これはReplacingMergeTreeパターンを使用しています
    client.command(
        """
        INSERT INTO customers (customer_id, tier, total_orders, last_order_date,
                              update_time)
        SELECT
            customer_id,
            tier,
            {new_count: UInt32} AS total_orders,
            now() AS last_order_date,
            now() AS update_time
        FROM customers
        WHERE customer_id = {cid: UInt32}
        """,
        parameters={'cid': customer_id, 'new_count': new_order_count}
    )

    # ステップ5: ロイヤルティポイントを計算して記録
    loyalty_points = int(final_amount)

    client.command(
        """
        INSERT INTO loyalty_points (customer_id, points, transaction_date, description)
        VALUES ({cid: UInt32}, {pts: Int32}, now(),
                {desc: String})
        """,
        parameters={
            'cid': customer_id,
            'pts': loyalty_points,
            'desc': f'注文 #{order_id}'
        }
    )

    # ステップ6: ティアアップグレードを確認（Pythonのビジネスロジック）
    status = 'ORDER_COMPLETE'

    if new_order_count >= 10 and customer_tier == 'bronze':
        # シルバーにアップグレード
        client.command(
            """
            INSERT INTO customers (customer_id, tier, total_orders, last_order_date,
                                  update_time)
            SELECT
                customer_id, 'silver' AS tier, total_orders, last_order_date,
                now() AS update_time
            FROM customers
            WHERE customer_id = {cid: UInt32}
            """,
            parameters={'cid': customer_id}
        )
        status = 'ORDER_COMPLETE_TIER_UPGRADED_SILVER'

    elif new_order_count >= 50 and customer_tier == 'silver':
        # ゴールドにアップグレード
        client.command(
            """
            INSERT INTO customers (customer_id, tier, total_orders, last_order_date,
                                  update_time)
            SELECT
                customer_id, 'gold' AS tier, total_orders, last_order_date,
                now() AS update_time
            FROM customers
            WHERE customer_id = {cid: UInt32}
            """,
            parameters={'cid': customer_id}
        )
        status = 'ORDER_COMPLETE_TIER_UPGRADED_GOLD'

    return status, loyalty_points

# 関数を使用
status, points = process_order(
    order_id=12345,
    customer_id=5678,
    order_total=Decimal('250.00')
)

print(f"ステータス: {status}、ロイヤルティポイント: {points}")

主な違い

1. 制御フロー - MySQL のストアドプロシージャは IF/ELSE や WHILE ループを使用します。ClickHouse では、このロジックはアプリケーションコード（Python、Java など）側で実装します。
2. トランザクション - MySQL は ACID トランザクション向けに BEGIN/COMMIT/ROLLBACK をサポートします。ClickHouse は追記専用ワークロード向けに最適化された分析用データベースであり、トランザクション的な更新処理には向きません。
3. 更新処理 - MySQL は UPDATE 文を使用します。ClickHouse では、可変データには ReplacingMergeTree や CollapsingMergeTree と組み合わせて INSERT を用いることを推奨します。
4. 変数と状態 - MySQL のストアドプロシージャでは（DECLARE v_discount のように）変数を宣言できます。ClickHouse では、状態管理はアプリケーションコード側で行います。
5. エラー処理 - MySQL は SIGNAL や例外ハンドラをサポートします。アプリケーションコードでは、使用言語が備えるネイティブなエラー処理（try/catch）を利用します。

ヒント

それぞれのアプローチを使う場面:

• OLTP ワークロード（注文、決済、ユーザーアカウント） → ストアドプロシージャ付きの MySQL/PostgreSQL を使用
• 分析ワークロード（レポート、集計、時系列） → ClickHouse とアプリケーション側でのオーケストレーションを使用
• ハイブリッドアーキテクチャ → 両方を使用。OLTP から ClickHouse へトランザクションデータをストリーミングし、分析に利用

ワークフローオーケストレーションツールの利用

• Apache Airflow - 複雑な ClickHouse クエリの DAG のスケジューリングと監視を実行
• dbt - SQL ベースのワークフローでデータを変換
• Prefect/Dagster - モダンな Python ベースのオーケストレーション
• Custom schedulers - カスタムスケジューラ（Cron ジョブ、Kubernetes CronJob など）

外部オーケストレーションを利用する利点:

• プログラミング言語の機能をフルに活用できる
• より優れたエラー処理とリトライロジック
• 外部システム（API、他のデータベース）との連携
• バージョン管理とテスト
• モニタリングとアラート
• より柔軟なスケジューリング

ClickHouse におけるプリペアドステートメントの代替手段

ClickHouse には、RDBMS の意味での従来型の「プリペアドステートメント」はありませんが、同じ目的――SQL インジェクションを防ぐための安全なパラメータ化されたクエリ――を実現する クエリパラメータ が提供されています。

構文

クエリパラメータを指定する方法は 2 通りあります。

方法 1：SET を使用する

テーブルとデータの例

-- user_events テーブルを作成する (ClickHouse 構文)
CREATE TABLE user_events (
    event_id UInt32,
    user_id UInt64,
    event_name String,
    event_date Date,
    event_timestamp DateTime
) ENGINE = MergeTree()
ORDER BY (user_id, event_date);

-- 複数ユーザーおよびイベントのサンプルデータを挿入する
INSERT INTO user_events (event_id, user_id, event_name, event_date, event_timestamp) VALUES
(1, 12345, 'page_view', '2024-01-05', '2024-01-05 10:30:00'),
(2, 12345, 'page_view', '2024-01-05', '2024-01-05 10:35:00'),
(3, 12345, 'add_to_cart', '2024-01-05', '2024-01-05 10:40:00'),
(4, 12345, 'page_view', '2024-01-10', '2024-01-10 14:20:00'),
(5, 12345, 'add_to_cart', '2024-01-10', '2024-01-10 14:25:00'),
(6, 12345, 'purchase', '2024-01-10', '2024-01-10 14:30:00'),
(7, 12345, 'page_view', '2024-01-15', '2024-01-15 09:15:00'),
(8, 12345, 'page_view', '2024-01-15', '2024-01-15 09:20:00'),
(9, 12345, 'page_view', '2024-01-20', '2024-01-20 16:45:00'),
(10, 12345, 'add_to_cart', '2024-01-20', '2024-01-20 16:50:00'),
(11, 12345, 'purchase', '2024-01-25', '2024-01-25 11:10:00'),
(12, 12345, 'page_view', '2024-01-28', '2024-01-28 13:30:00'),
(13, 67890, 'page_view', '2024-01-05', '2024-01-05 11:00:00'),
(14, 67890, 'add_to_cart', '2024-01-05', '2024-01-05 11:05:00'),
(15, 67890, 'purchase', '2024-01-05', '2024-01-05 11:10:00'),
(16, 12345, 'page_view', '2024-02-01', '2024-02-01 10:00:00'),
(17, 12345, 'add_to_cart', '2024-02-01', '2024-02-01 10:05:00');

SET param_user_id = 12345;
SET param_start_date = '2024-01-01';
SET param_end_date = '2024-01-31';

SELECT
    event_name,
    count() AS event_count
FROM user_events
WHERE user_id = {user_id: UInt64}
    AND event_date BETWEEN {start_date: Date} AND {end_date: Date}
GROUP BY event_name;

方法 2：CLI パラメーターを使用する

clickhouse-client \
    --param_user_id=12345 \
    --param_start_date='2024-01-01' \
    --param_end_date='2024-01-31' \
    --query="SELECT count() FROM user_events
             WHERE user_id = {user_id: UInt64}
             AND event_date BETWEEN {start_date: Date} AND {end_date: Date}"

パラメータ構文

パラメータは次の構文で指定します: {parameter_name: DataType}

• parameter_name - パラメータ名（param_ プレフィックスを除いた部分）
• DataType - パラメータをキャストする ClickHouse のデータ型

データ型の例

例で使用するテーブルとサンプルデータ

-- 1. 文字列と数値のテスト用テーブルを作成
CREATE TABLE IF NOT EXISTS users (
    name String,
    age UInt8,
    salary Float64
) ENGINE = Memory;

INSERT INTO users VALUES
    ('John Doe', 25, 75000.50),
    ('Jane Smith', 30, 85000.75),
    ('Peter Jones', 20, 50000.00);

-- 2. 日付とタイムスタンプのテスト用テーブルを作成
CREATE TABLE IF NOT EXISTS events (
    event_date Date,
    event_timestamp DateTime
) ENGINE = Memory;

INSERT INTO events VALUES
    ('2024-01-15', '2024-01-15 14:30:00'),
    ('2024-01-15', '2024-01-15 15:00:00'),
    ('2024-01-16', '2024-01-16 10:00:00');

-- 3. 配列のテスト用テーブルを作成
CREATE TABLE IF NOT EXISTS products (
    id UInt32,
    name String
) ENGINE = Memory;

INSERT INTO products VALUES (1, 'Laptop'), (2, 'Monitor'), (3, 'Mouse'), (4, 'Keyboard');

-- 4. Map（構造体のような型）のテスト用テーブルを作成
CREATE TABLE IF NOT EXISTS accounts (
    user_id UInt32,
    status String,
    type String
) ENGINE = Memory;

INSERT INTO accounts VALUES
    (101, 'active', 'premium'),
    (102, 'inactive', 'basic'),
    (103, 'active', 'basic');

-- 5. Identifier のテスト用テーブルを作成
CREATE TABLE IF NOT EXISTS sales_2024 (
    value UInt32
) ENGINE = Memory;

INSERT INTO sales_2024 VALUES (100), (200), (300);

文字列と数値

SET param_name = 'John Doe';
SET param_age = 25;
SET param_salary = 75000.50;

SELECT name, age, salary FROM users
WHERE name = {name: String}
  AND age >= {age: UInt8}
  AND salary <= {salary: Float64};

日付と時刻

SET param_date = '2024-01-15';
SET param_timestamp = '2024-01-15 14:30:00';

SELECT * FROM events
WHERE event_date = {date: Date}
   OR event_timestamp > {timestamp: DateTime};

配列

SET param_ids = [1, 2, 3, 4, 5];

SELECT * FROM products WHERE id IN {ids: Array(UInt32)};

Map

SET param_filters = {'target_status': 'active'};

SELECT user_id, status, type FROM accounts
WHERE status = arrayElement(
    mapValues({filters: Map(String, String)}),
    indexOf(mapKeys({filters: Map(String, String)}), 'target_status')
);

Identifier

SET param_table = 'sales_2024';

SELECT count() FROM {table: Identifier};

言語クライアントでのクエリパラメータの使用方法については、利用したい特定の言語クライアントのドキュメントを参照してください。

クエリパラメータの制約事項

クエリパラメータは汎用的なテキスト置換ではありません。次のような特有の制約があります。

1. 主に SELECT 文向けに設計されています - 最も手厚くサポートされているのは SELECT クエリです
2. 識別子またはリテラルとして動作します - 任意の SQL フラグメントを置き換えることはできません
3. DDL のサポートは限定的です - CREATE TABLE ではサポートされていますが、ALTER TABLE ではサポートされていません

動作するケース:

-- ✓ WHERE句の値
SELECT * FROM users WHERE id = {user_id: UInt64};

-- ✓ テーブル名/データベース名
SELECT * FROM {db: Identifier}.{table: Identifier};

-- ✓ IN句の値
SELECT * FROM products WHERE id IN {ids: Array(UInt32)};

-- ✓ CREATE TABLE
CREATE TABLE {table_name: Identifier} (id UInt64, name String) ENGINE = MergeTree() ORDER BY id;

動作しないもの:

-- ✗ SELECT句内のカラム名(Identifierは慎重に使用すること)
SELECT {column: Identifier} FROM users;  -- サポートは限定的

-- ✗ 任意のSQLフラグメント
SELECT * FROM users {where_clause: String};  -- サポート対象外

-- ✗ ALTER TABLE文
ALTER TABLE {table: Identifier} ADD COLUMN new_col String;  -- サポート対象外

-- ✗ 複数のステートメント
{statements: String};  -- サポート対象外

セキュリティのベストプラクティス

ユーザーからの入力には必ずクエリパラメータを使用すること：

# ✓ 安全 - パラメータを使用
user_input = request.get('user_id')
result = client.query(
    "SELECT * FROM orders WHERE user_id = {uid: UInt64}",
    parameters={'uid': user_input}
)

# ✗ 危険 - SQLインジェクションのリスク!
user_input = request.get('user_id')
result = client.query(f"SELECT * FROM orders WHERE user_id = {user_input}")

入力の型を検証する：

def get_user_orders(user_id: int, start_date: str):
    # クエリ実行前に型を検証
    if not isinstance(user_id, int) or user_id <= 0:
        raise ValueError("user_idが無効です")

    # パラメータで型安全性を確保
    return client.query(
        """
        SELECT * FROM orders
        WHERE user_id = {uid: UInt64}
            AND order_date >= {start: Date}
        """,
        parameters={'uid': user_id, 'start': start_date}
    )

MySQL プロトコルのプリペアドステートメント

ClickHouse の MySQL インターフェイス は、プリペアドステートメント（COM_STMT_PREPARE、COM_STMT_EXECUTE、COM_STMT_CLOSE）に対して最小限のサポートのみを提供します。これは主に、クエリをプリペアドステートメントでラップする Tableau Online のようなツールとの接続性を確保するためのものです。

主な制限事項:

• パラメータのバインドはサポートされません - バインドパラメータ付きの ? プレースホルダは使用できません
• クエリは PREPARE 実行時に保存されますが、解析は行われません
• 実装は最小限で、特定の BI ツールとの互換性確保のみを目的としています

動作しない例:

-- このMySQLスタイルのパラメータ付きプリペアドステートメントはClickHouseでは動作しません
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
EXECUTE stmt USING @user_id;  -- パラメータバインディングには非対応

ヒント

代わりに ClickHouse ネイティブのクエリパラメータを使用してください。 これらは、すべての ClickHouse インターフェースで、完全なパラメータバインディングのサポート、型安全性、SQL インジェクションの防止を提供します。

-- ClickHouseネイティブクエリパラメータ（推奨）
SET param_user_id = 12345;
SELECT * FROM users WHERE id = {user_id: UInt64};

詳細については、MySQL インターフェイスのドキュメント と MySQL サポートに関するブログ記事 を参照してください。

概要

ストアドプロシージャに対する ClickHouse の代替手段

従来のストアドプロシージャのパターン	ClickHouse の代替手段
単純な計算と変換処理	ユーザー定義関数 (UDF)
再利用可能なパラメータ化クエリ	パラメータ化ビュー
事前計算された集計	マテリアライズドビュー
スケジュールされたバッチ処理	リフレッシュ可能なマテリアライズドビュー
複雑な多段階の ETL	チェーン構成のマテリアライズドビューまたは外部オーケストレーション (Python, Airflow, dbt)
制御フローを伴うビジネスロジック	アプリケーションコード

クエリパラメータの利用

クエリパラメータは次の用途に利用できます:

• SQLインジェクションの防止
• 型安全なパラメータ化されたクエリ
• アプリケーションでの動的なフィルタリング
• 再利用可能なクエリテンプレート

関連ドキュメント

• CREATE FUNCTION - ユーザー定義関数
• CREATE VIEW - パラメータ化ビューおよびマテリアライズドビューを含むビュー
• SQL 構文 - クエリパラメータ - パラメータ構文の完全なリファレンス
• カスケード型マテリアライズドビュー - 高度なマテリアライズドビューのパターン
• 実行可能な UDF - 外部関数の実行