HiveQL

テーブル操作

一覧・詳細

-- 一覧表示
show tables;

-- テーブル詳細情報表示
describe my_table;

作成

create table if not exists my_db.my_table (
  user_id     int,
  name        string,
  actions     array<string>,
  other_attr  map<string, string>
)
-- 処理効率化のためのパーティショニング
partitioned by (year int, month int, day int)

row format delimited
fields terminated by '\t'           -- フィールド区切り文字
collection items terminated by ','  -- コレクションの要素の区切り文字
map keys terminated by '='          -- Mapのkey-valueの区切り文字
lines terminated by '\n'            -- 行の区切り文字
;

削除

drop table if exists my_db.my_table;

名前変更

alter table my_table rename to my_new_table;

文字列の結合

select
  key,
  value,
  concat(key, '/', value)
from
  my_table
;
A a A/a
A b A/b
B a B/a
...

条件分岐による値の設定

select
  key1,
  key2,
  -- 単純な値による分岐
  case key1
    when 'A' then 'a'
    when 'B' then 'b'
    else 'other'
  end,
  -- より複雑な条件による分岐
  case
    when key1 = 'A' then 'Good'
    when key1 = 'B' and key2 = 'OK' then 'Normal'
    else 'Bad'
  end
from
  my_table
;
A OK a     Good
B OK b     Normal
B NG b     Bad
C OK other Bad

WITH 句

  • 誤:テンポラリテーブルを作成して処理結果を格納し、何度も使い回せる機能
  • 正:定義した処理を都度呼び出して実行できる機能

WITH で定義した処理を複数回呼び出すと、その回数だけ同じ処理が実行される(= 効率化はされない)
→ 同じ処理結果を使い回したいのであれば、WITH ではなく別にテンポラリテーブルを作る

with
proccessed1 as (
  select
    *
  from
    my_table1
  where
    ...
),
proccessed2 as (
  select
    *
  from
    my_table2
  where
    ...
)

select
  *
from
  processed1 join processed2
  on processed1.key = processed2.key
;

GROUP BY の際に複数の値を文字列として結合

select
  key,
  collect_list(value),
  concat_ws(',', collect_list(value)),
  collect_set(value),
  concat_ws(',', collect_set(value))
from
  my_table
group by
  key
;
A  ['a', 'a', 'b']       a,a,b    ['a', 'b']       a,b
B  ['a', 'c']            a,c      ['a', 'c']       a,c
C  ['a', 'c', 'b', 'c']  a,c,b,c  ['a', 'c', 'b']  a,c,b

GROUP BY の際にキー全ての合計も出力

with rollup句を使う。

select
  key1,
  key2,
  count(distinct value)
from
  my_table
group by
  key1,
  key2
with rollup
;
A    a    10
A    b    20
A    c    5
B    a    30
B    b    15
A    NULL 35
B    NULL 45
NULL NULL 80

正規表現による置換・抽出

置換

select
  regexp_replace('aA1 bB2 cC3', '[A-Z]', '_')
;
a_1 b_2 c_3

抽出

select
  regexp_extract('BirthDate: 2000/1/1', ': ([0-9/]+)')
;
2000/1/1

複数要素の展開

select
  key,
  values,
  value
from
  my_table
  literal view explode(values) t as value
A  ['a', 'b', 'c']  a
A  ['a', 'b', 'c']  b
A  ['a', 'b', 'c']  c
B  ['a', 'c']       a
B  ['a', 'c']       c