データベースメタ情報の管理

データベースにプロパティ情報を追加

create database analytics_db 
with dbproperties('owner'='YamadaTaro','created_date'='20240101')

プロパティ情報の確認

describe database extended analytics_db

プロパティ情報の更新

alter database analytics_db 
set dbproperties('owner'='SatoHanako')

詳細情報の表示

desc database extended analytics_db

データベースの削除

drop database analytics_db
drop database analytics_db cascade

テーブル操作の基本

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] テーブル名
(カラム名 データ型 [COMMENT カラムコメント], ...)
[COMMENT テーブルコメント]
[PARTITIONED BY (カラム名 データ型, ...)]
[CLUSTERED BY (カラム名, ...) 
INTO バケット数 BUCKETS]
[ROW FORMAT 行形式]
[STORED AS ファイル形式]
[LOCATION HDFSパス]

内部テーブルの作成と操作

区切り文字を指定してテーブル作成

create table employees(emp_id int, emp_name string)
row format delimited
fields terminated by '|'

格納パスを指定してテーブル作成

create table products(prod_id int, prod_name string)
row format delimited
fields terminated by ','
location '/data/products'

クエリ結果からテーブル作成

create table emp_backup as select * from employees

既存テーブルの構造をコピーして新規作成

create table emp_archive like employees

テーブル構造の確認

desc formatted employees

外部テーブルの管理

部門テーブルの作成

create external table departments(
    dept_id string,
    dept_name string
)
row format delimited
fields terminated by '\t'

社員テーブルの作成

create external table staff(
    staff_id string,
    staff_name string,
    hire_date string,
    department string
)
row format delimited
fields terminated by '\t'

データの読み込み方法

load data local inpath '/local/data/departments.csv' 
into table departments

load data inpath '/hdfs/data/staff.csv' 
overwrite into table staff

パーティションテーブルの活用

単一パーティションテーブルの作成

create table sales_records(
    order_id string,
    customer_id string,
    amount decimal(10,2)
)
partitioned by (sale_date string)
row format delimited
fields terminated by '\t'

複数パーティションテーブルの作成

create table web_logs(
    session_id string,
    user_id string,
    action string
)
partitioned by(year string, month string, day string)
row format delimited
fields terminated by ','

パーティションへのデータ読み込み

load data local inpath '/data/sales_202401.csv' 
into table sales_records 
partition(sale_date='2024-01-15')

パーティション情報の管理

show partitions sales_records
alter table sales_records add partition(sale_date='2024-01-16')
alter table sales_records drop partition(sale_date='2024-01-01')

バケットテーブルの実装

バケット機能の有効化

set hive.enforce.bucketing=true
set mapreduce.job.reduces=4

バケットテーブルの作成

create table customer_segments(
    cust_id string,
    cust_name string,
    segment_code string
)
clustered by(cust_id) into 4 buckets
row format delimited
fields terminated by '\t'

データ集計とクエリ

基本的な集計関数

select count(*) from sales_records
select max(amount) from sales_records
select min(amount) from sales_records
select sum(amount) from sales_records
select avg(amount) from sales_records

比較演算子

select * from employees where emp_id = 100
select * from employees where salary <> 50000
select * from employees where age < 30
select * from employees where experience >= 5
select * from employees where name is not null
select * from employees where dept_name like 'Tech%'
select * from employees where name not like '%Admin%'

算術演算

select salary + bonus as total_comp from employees
select price * quantity as total_sales from orders
select revenue / 12 as monthly_avg from financials

論理演算

select * from employees where department = 'IT' and status = 'Active'
select * from orders where status = 'Pending' or priority = 'High'
select * from products where not discontinued = true

数値関数

select round(3.14159, 2) from dual
select floor(99.9) from dual
select ceil(45.1) from dual
select abs(-250) from dual
select rand() from dual
select rand(42) from dual

日時関数

select to_date('2024-03-15 14:30:00') from dual
select year('2024-03-15') from dual
select month('2024-03-15') from dual
select day('2024-03-15') from dual
select hour('2024-03-15 14:30:00') from dual
select date_add('2024-03-15', 7) from dual
select date_sub('2024-03-15', 30) from dual
select datediff('2024-03-20', '2024-03-15') from dual