ユーザー別アクションの上位抽出

ウィンドウ関数を用いて、各ユーザーごとに最も長時間視聴したコンテンツを特定するクエリ例です。サブクエリ内で ROW_NUMBER を使用し、パーティションごとに順位付けを行います。

SELECT user_id, content_id
FROM (
    SELECT 
        user_id,
        content_id,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total_duration DESC) AS rank
    FROM (
        SELECT user_id, content_id, SUM(view_duration) AS total_duration
        FROM access_logs
        GROUP BY user_id, content_id
    ) aggregated
) ranked
WHERE rank = 1;

週次集計のための日付計算

Hive において、基準日からの経過日数を計算し、週頭（月曜日）の日付を導出する方法です。基準日を変更することで、任意の週の始点を算出できます。

-- 指定日の曜日を計算 (0=月曜日〜6=日曜日)
SELECT pmod(datediff('2023-10-01', '2020-01-01'), 7);

-- 当該週の月曜日
SELECT date_sub('2023-10-01', pmod(datediff('2023-10-01', '2020-01-01'), 7));

-- 前週の月曜日
SELECT date_sub('2023-10-01', pmod(datediff('2023-10-01', '2020-01-01'), 7) + 7);

スキーマ変更操作 (DDL)

既存テーブルの列名、データ型、および列の順序を変更する ALTER 文の構文です。列の位置を最初にする FIRST や、特定の列の後に配置する AFTER オプションが利用可能です。

-- 列名とタイプの変更
ALTER TABLE customer_master CHANGE COLUMN old_phone new_phone STRING;

-- 列を先頭に移動
ALTER TABLE customer_master CHANGE COLUMN new_phone new_phone STRING FIRST;

-- 特定の列の後に移動
ALTER TABLE customer_master CHANGE COLUMN address address STRING AFTER new_phone;

-- 新規列の追加（末尾に追加される）
ALTER TABLE customer_master ADD COLUMNS (membership_level INT);

-- テーブル名の変更
ALTER TABLE customer_master RENAME TO customer_profile;

パーティション管理

パーティションの追加、位置変更、削除操作です。HDFS 上のディレクトリ構造に対応するため、_location を指定することでデータ格納先を制御できます。

-- パーティション追加
ALTER TABLE event_logs ADD PARTITION (event_date='20231001');

-- 場所を指定してパーティション追加
ALTER TABLE event_logs ADD PARTITION (event_date='20231002') 
LOCATION '/data/events/20231002';

-- パーティション一覧の確認
SHOW PARTITIONS event_logs;

-- パーティション名の変更（日付変更など）
ALTER TABLE event_logs PARTITION (event_date='20231001') 
RENAME TO PARTITION (event_date='20231003');

-- 保存場所の変更
ALTER TABLE event_logs PARTITION (event_date='20231002') 
SET LOCATION '/data/events/moved/20231002';

-- パーティションの削除
ALTER TABLE event_logs DROP IF EXISTS PARTITION (event_date='20231001');

JSON データの解析

文字列形式の JSON データから特定のフィールドを抽出するには、get_json_object または json_tuple を使用します。配列形式の JSON を行展開するには、explode 関数と正規表現を組み合わせます。

-- 単一フィールドの抽出
SELECT get_json_object('{"product_id":"P100","category":"Electronics"}', '$.product_id');

-- 複数フィールドの同時抽出
SELECT json_tuple('{"product_id":"P100","category":"Electronics"}', 'product_id', 'category');

-- JSON 配列の展開処理
SELECT json_tuple(json_str, 'product_id', 'category') 
FROM (
    SELECT explode(
        split(
            regexp_replace(
                regexp_replace(
                    '[{"product_id":"P100"},{"product_id":"P200"}]', 
                    '\\},\\{', '\\};\\{'
                ), 
                '\\[|\\]', ''
            ), 
            '\\;'
        )
    ) AS json_str
) exploded_data;

上記の正規表現処理は、JSON 配列の区切り文字を置換し、配列括弧を除去してから split 関数で行ごとに分解しています。

演算子の優先順位と NULL 値の扱い

論理演算子の優先順位は NOT > AND > OR です。WHERE 句で OR を使用する場合、条件が独立して評価される点に注意が必要です。また、不等号演算子（<> や !=）は NULL 値を結果から除外するため、NULL を含める場合は IS NULL を明示的に記述します。

-- NULL 値を保持するための条件記述
WHERE (status_code <> 'ERROR' OR status_code IS NULL);

ソートと分散の仕組み

Hive にはデータ並べ替えに関する複数の句があり、それぞれ Reduce タスクの挙動が異なります。

• ORDER BY: 全局ソート。すべてのデータが単一の Reduce タスクに集められるため、データ量が多い場合は注意が必要です。厳密モードでは LIMIT の指定が必須となります。
• SORT BY: 局所ソート。各 Reduce タスク内でソートが行われます。全体としての順序は保証されませんが、処理効率は高いです。
• DISTRIBUTE BY: 特定の列値を同じ Reduce タスクに送信するためのハッシュ分散制御を行います。SORT BY と組み合わせて使用されることが多いです。
• CLUSTER BY: DISTRIBUTE BY と SORT BY を同じ列に対して指定した場合の簡略記法です。

-- 分散とソートの組み合わせ
SELECT user_id, amount, transaction_date 
FROM transactions 
DISTRIBUTE BY user_id 
SORT BY user_id;

-- CLUSTER BY による簡略化
SELECT user_id, amount, transaction_date 
FROM transactions 
CLUSTER BY user_id;

内部表と外部表の差異

EXTERNAL キーワードの有無により、表の管理方式が異なります。内部表（Managed Table）は Hive がデータ lifecycle を管理し、DROP 時にデータごと削除されます。外部表（External Table）はメタデータのみが削除され、HDFS 上の実データは残存します。

ストレージ形式については、行ストアは 1 ブロックに複数行を格納するのに対し、列ストアは列ごとにデータを格納し、分析クエリでの I/O 効率を向上させます。

静的および動的パーティション

パーティション値を挿入時に明示的に指定するのが静的パーティションです。一方、クエリ結果に基づいて自動的にパーティションを決定するのが動的パーティションです。大量のパーティションを扱う場合、動的パーティションが効率的ですが、設定変更が必要です。

-- 動的パーティションの有効化
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

-- 動的パーティションへのデータ挿入
INSERT INTO TABLE staff_records PARTITION (department_id)
SELECT emp_id, emp_name, role, manager_id, hire_date, salary, department_id
FROM source_employee_data;

INSERT 文の SELECT 句の最後の列が、PARTITION 句で指定した列に対応している必要があります。これにより、department_id の値ごとに自動的にディレクトリが作成されます。