Hiveの基本SQL構文解説

HiveはHadoop上に構築されたデータウェアハウスシステムであり、構造化データファイルをデータベーステーブルにマッピングし、SQLクエリをMapReduceジョブに変換して実行する。SQLに似たクエリ言語（HiveQL）を提供し、MapReduceに不慣れなユーザーでもデータの集計・分析が行える。ただし、OLTPやリアルタイムクエリには不向きで、大規模な不変データのバッチ処理に最適である。

1. DDL操作

1.1 テーブル作成

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  (col_name data_type [COMMENT col_comment], ...)
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...)
   [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [ROW FORMAT row_format]
  [STORED AS file_format]
  [LOCATION hdfs_path]
  • EXTERNAL: 外部テーブルを作成。データの実体はHDFS上の指定パスに存在。
  • LIKE: 既存テーブルの構造をコピー（データはコピーしない）。
  • ROW FORMAT: デリミタやSerDeを指定。
  • STORED AS: ファイル形式（TEXTFILE, SEQUENCEFILE, RCFILE等）。

シンプルなテーブル

CREATE TABLE sample_table (id INT, name STRING);

外部テーブル

CREATE EXTERNAL TABLE page_stats (
  view_time INT,
  user_id BIGINT,
  page_url STRING,
  referer STRING,
  ip STRING COMMENT 'ユーザーIP'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/data/page_stats';

パーティションテーブル

CREATE TABLE sales_log (
  product_id INT,
  amount DOUBLE,
  region STRING
)
PARTITIONED BY (dt STRING, hour STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS SEQUENCEFILE;

バケットテーブル

CREATE TABLE user_actions (
  user_id INT,
  action STRING,
  timestamp BIGINT
)
CLUSTERED BY (user_id) SORTED BY (timestamp) INTO 16 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

1.2 テーブル表示・削除

SHOW TABLES;
SHOW TABLES '.*sales.*';  -- 正規表現でフィルタ
DROP TABLE sample_table;

1.3 テーブル変更

  • 列追加: ALTER TABLE sample_table ADD COLUMNS (new_col INT);
  • 列名変更: ALTER TABLE sample_table CHANGE old_col new_col STRING COMMENT '新しいコメント';
  • テーブル名変更: ALTER TABLE sample_table RENAME TO new_name;
  • パーティション追加: ALTER TABLE sales_log ADD PARTITION (dt='2025-04-01', hour='10');
  • パーティション削除: ALTER TABLE sales_log DROP PARTITION (dt='2025-04-01');

1.4 ビュー作成・削除

CREATE VIEW active_users AS
SELECT user_id, COUNT(*) AS cnt
FROM user_actions
GROUP BY user_id;

DROP VIEW active_users;

1.5 データベース

CREATE DATABASE analytics_db;
SHOW DATABASES;

2. DML操作

2.1 LOAD DATA

LOAD DATA LOCAL INPATH '/home/user/data.csv' OVERWRITE INTO TABLE sample_table;
LOAD DATA INPATH '/user/hive/input/data.csv' INTO TABLE sales_log PARTITION (dt='2025-04-01', hour='12');

LOCAL指定時はクライアントのローカルファイルシステム、未指定時はHDFSから移動（コピーではない）。

2.2 INSERTによるデータ挿入

クエリ結果をテーブルに挿入

INSERT OVERWRITE TABLE aggregated_sales
SELECT region, SUM(amount) AS total
FROM sales_log
WHERE dt = '2025-04-01'
GROUP BY region;

複数テーブルへの挿入（マルチインサート）

FROM raw_data
INSERT OVERWRITE TABLE dest1 SELECT * WHERE key < 100
INSERT OVERWRITE TABLE dest2 SELECT key, value WHERE key >= 100 AND key < 200;

HDFSディレクトリへの出力

INSERT OVERWRITE DIRECTORY '/output/summary'
SELECT region, AVG(amount) FROM sales_log GROUP BY region;

INSERT INTO（0.8以降）

INSERT INTO TABLE sample_table VALUES (1, 'Alice');

3. DQL操作

3.1 SELECT基本

SELECT [ALL|DISTINCT] col1, col2, ...
FROM table_ref
[WHERE condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY|ORDER BY col_list]]
[LIMIT n];
  • ORDER BY: グローバルソート（1Reducer）。
  • SORT BY: 各Reducer内でソート。
  • LIMIT: 結果行数を制限。

例

SELECT name, age FROM users WHERE age > 20 ORDER BY age DESC LIMIT 10;

-- 正規表現で列選択（dsとhr以外の全列）
SELECT `(ds|hr)?+.+` FROM sales_log;

3.2 パーティションプルーニング

WHERE句でパーティション列を条件指定すると、スキャンするパーティションが限定される。

SELECT * FROM sales_log WHERE dt = '2025-04-01';

3.3 JOIN

Hiveは等価JOIN、OUTER JOIN、LEFT SEMI JOINをサポート。非等価JOINは不可。

-- 内部結合
SELECT a.id, b.name
FROM table_a a JOIN table_b b ON a.id = b.id;

-- LEFT OUTER JOIN
SELECT a.id, b.value
FROM a LEFT OUTER JOIN b ON a.key = b.key AND b.dt = '2025-04-01';

-- LEFT SEMI JOIN（IN/EXISTSの代替）
SELECT a.key, a.value FROM a LEFT SEMI JOIN b ON a.key = b.key;

-- UNION ALL
SELECT id, name FROM users WHERE age < 20
UNION ALL
SELECT id, name FROM users WHERE age >= 20;

4. HiveQLの注意点

  • 等価JOINのみ: SQLの暗黙結合（FROM t1, t2 WHERE t1.id = t2.id）は使えず、JOIN ... ONを必須。
  • セミコロンのエスケープ: concat(';', key)のように文字列内のセミコロンは8進数\073で記述。
  • NULLと空文字: 空文字列('')はIS NULLでFALSEとなる。
  • 既存テーブルへの追記: INSERT INTOは0.8以降で可能。それ以前はINSERT OVERWRITEのみ。
  • UPDATE/DELETE非対応: 通常の更新・削除は不可。
  • MapReduceスクリプトの埋め込み: TRANSFORM ... USING 'script.py'でMapper/Reducerを記述。
  • マルチインサート: 一つのFROM句から複数のテーブルやディレクトリに出力可能。

5. 実践例

5.1 テーブル作成とデータロード

CREATE TABLE movie_ratings (
  user_id INT,
  movie_id INT,
  rating INT,
  timestamp STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/data/ml-100k/u.data' OVERWRITE INTO TABLE movie_ratings;

5.2 基本集計

SELECT COUNT(*) FROM movie_ratings;

5.3 曜日ごとの評価分布

Pythonスクリプト weekday_mapper.py を用意（入力: userid, movieid, rating, unixtime → 出力: userid, movieid, rating, weekday）。

ADD FILE weekday_mapper.py;

CREATE TABLE ratings_with_weekday (
  user_id INT,
  movie_id INT,
  rating INT,
  weekday INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

INSERT OVERWRITE TABLE ratings_with_weekday
SELECT TRANSFORM (user_id, movie_id, rating, timestamp)
USING 'python weekday_mapper.py'
AS (user_id, movie_id, rating, weekday)
FROM movie_ratings;

SELECT weekday, COUNT(*), AVG(rating)
FROM ratings_with_weekday
GROUP BY weekday;

5.4 Apache Webログの解析（正規表現SerDe）

ADD JAR /path/to/hive-contrib.jar;

CREATE TABLE apache_logs (
  host STRING,
  identity STRING,
  user STRING,
  time STRING,
  request STRING,
  status STRING,
  size STRING,
  referer STRING,
  agent STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[([^\\]]*)\\] \"([^\"]*)\" (-|[0-9]*) (-|[0-9]*) \"([^\"]*)\" \"([^\"]*)\"",
  "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE;

5.5 配列の展開（LATERAL VIEW EXPLODE）

配列waybill_noを複数行に展開する例。

SELECT t.order_id, single_waybill
FROM shipment_orders t
LATERAL VIEW explode(t.waybill_numbers) exploded AS single_waybill
WHERE size(t.waybill_numbers) > 1
LIMIT 100;

タグ: Hive SQL DDL DML DQL

7月4日 21:52 投稿

ホットタグ

©2026 異端開発室