[Hive] 예제

# hive

> use kdatademo;

create table mobilephones(
id string,
title string,
cost float, 
colors array<string>,
screen_size array<float>);

 

INSERT INTO table mobilephones 
SELECT "galaxynote20", "galaxy note 20", 300, array("white","red","black"), array(float(4.5)) 
UNION ALL
SELECT "iphone10", "iphone plus", 500, array("gold","black"), array(float(4.5), float(5.5))

> select * from mobilephones;

> select title,colors from mobilephones;

> select title,colors[0] from mobilephones;

> drop table mobilephones;

dn02에서

# ssh dn01

# mkdir -p /home/hadoop/hive_data

# vi /home/hadoop/hive_data/mobilephones.csv

Samsungg7,Samsung G7,250,red#blue#black,5.5,camera:true#dualsim:false
IPhonePlus7,Iphone Plus 7,450,gold#white,4.5#5.5,autofocus:true

 

dn01에서

create table mobilephones
(id string, title string, cost float, colors array<string>, screen_size array<float>,
features map<string, Boolean> )
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';

#local디렉토리를 로딩하려면 local 하둡에있는 것은 아무것도 입력안함

 

> load data local inpath '/home/hadoop/hive_data/mobilephones.csv' into table mobilephones;

> select id, colors[0] from mobilephones;

> select id, colors[0], features["camera"] from mobilephones;

 

1

dn02로 이동 후

# ssh dn01

# su - root

# yum install unzip

# cd /home/hadoop/hive_data/

# www.grouplens.org/system/files/ml-100k.zip

# unzip ml-100k.zip

# su - hadoop

# cd ml-100k/

# head -10 u.item

# head -10 u.user

 

하둡에 데이터 넣기

# hdfs dfs -mkdir /user/hadoop/movies
# hdfs dfs -mkdir /user/hadoop/userinfo

# hdfs dfs -put hive_data/ml-100k/u.item /user/hadoop/movies
# hdfs dfs -put hive_data/ml-100k/u.user /user/hadoop/userinfo

# hdfs dfs -chown -R hadoop:hadoop /user/hadoop/

CREATE TABLE movies (
    movie_id INT,
    movie_title STRING,
    release_date STRING,
    video_release_date STRING,
    imdb_url STRING,
    unknown INT,
    action INT,
    adventure INT,
    animation INT,
    children INT,
    comedy INT,
    crime INT,
    documentary INT,
    drama INT,
    fantasy INT,
    film_noir INT,
    horror INT,
    musical INT,
    mystery INT,
    romance INT,
    sci_fi INT,
    thriller INT,
    war INT,
    Western INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;

CREATE EXTERNAL TABLE users (
 user_id INT,
 age INT,
 gender STRING,
 occupation STRING,
 zip_code STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION '/user/hadoop/hive_data/userinfo2';

>load data inpath '/user/hadoop/movies' into table movies;

>load data inpath '/user/hadoop/userinfo' into table users;

파일들이 사라지고 db로 올라감

> select * from movies limit 30;