Hive Data Management Join in Hive 빅데이터분산컴퓨팅박영택
Hive 에서의 Joins Hive 에서서로다른데이터간의 Join 은빈번하게발생 Hive 에서지원하는 Join 의종류 Inner joins Outer joins(left, right, and full) Cross joins( Hive 0.1 이상버전 ) Left semi joins equality ( = ) 조건은 join 에서만허용 Valid : customers.cust_id = orders.cust_id Invalid : customers.cust_id <> orders.cust_id Output 의각 records 는각 table 의지정된 key(e.g. customers.cust_id) 로부터찾은데이터 최고의질의응답성능을위해서는가장큰테이블을가장나중에질의하기
Join 문법 Hive 는아래의문법을사용 위의예제는 inner join 임 JOIN 키워드대신에다른타입의 JOIN 사용가능 (e.g. RIGHT OUTER JOIN)
Inner Join 예제 customers 테이블 query 결과 orders 테이블
Left Outer Join 예제 customers 테이블 query 결과 orders 테이블
Right Outer Join 예제 customers 테이블 orders 테이블 query 결과
Full Outer Join 예제 customers 테이블 query 결과 orders 테이블
Outer Join 예제 customers 테이블 orders 테이블 query 결과
Cross Join 예제 disks 테이블 query 결과 sizes 테이블
Sentiment 분석예제 : hive 기반 목적 : 소비자들의 (customers) 제품평가가가장불만족한제품을찾아내고, 그제품에대해다양한키워드들을분석하여문제점의요인을찾아내기위함 Step #1 : 제품들에대한 Ratings 의수치를분석 사용자로부터 Comment 의개수가많고, 실제제품도만족한제품 (Comment 개수 > 50, 평점 high) Comment 의개수는많지만, 제품에대한만족도가낮은제품 (Comment 개수 > 50, 평점 low) Step #2 : 가장평가가안좋은제품에대한평점 Comments 분석 그제품의 Comments 에서가장많이나타난문장추출 (e.g. 2-gram, 3-gram) 가장많이나타난문장들을포함하고있는 Comments 추출 (e.g. ten times more, red, 16 GB USB Flash Drive ) 문제점발견 : 16 GB USB Flash Driver(red) 제품이다른유사제품에비해가격이 10 배이상이었음
Sentiment 분석 (step #1) Sentiment 분석예제를위한 ratings 테이블생성 hive> CREATE TABLE ratings (posted TIMESTAMP, cust_id INT, prod_id INT, rating TINYINT, message STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t ; HDFS 에경로생성후파일업로드 $ Hadoop fs mkdir ratings $ Hadoop fs put ratings_2013.txt ratings 테이블에데이터로드 hive> LOAD DATA INPATH ratings INTO TABLE ratings;
Sentiment 분석 (step #1) ratings 테이블의 schema 와 data 확인
제품의평점분석 1 (step #1) 평가횟수가 50 개이상인제품 평점이높은순으로정렬 hive> SELECT prod_id, FORMAT_NUMBER(avg_rating, 2) AS avg_rating FROM (SELECT prod_id, AVG(rating) AS avg_rating, COUNT(*) AS num FROM ratings GROUP BY prod_id) rated WHERE num >= 50 ORDER BY avg_rating DESC LIMIT 1;
제품의평점분석 1 (step #1) 평가횟수가 50 개이상인제품 평점이높은순으로정렬 hive> SELECT prod_id, FORMAT_NUMBER(avg_rating, 2) AS avg_rating FROM (SELECT prod_id, AVG(rating) AS avg_rating, COUNT(*) AS num FROM ratings WHERE num >= 50 GROUP BY prod_id) rated ORDER BY avg_rating DESC LIMIT 1; Inner query(= sub query, nested query) Inner query 는 from 절에서많이쓰이며, SELECT 의결과를 Outer query 에서사용될수있다. from 절에서괄호형태로묶어서사용되며괄호끝에 alias 를정의함으로서 alias 를통해서접근가능 (e.g. rated.prod_id) prod_id 를통해상품을그룹화하고, 각상품별로평점의평균, 평점의개수를출력
제품의평점분석 1 (step #1) 평가횟수가 50 개이상인제품 평점이높은순으로정렬 hive> SELECT prod_id, FORMAT_NUMBER(avg_rating, 2) AS avg_rating FROM (SELECT prod_id, AVG(rating) AS avg_rating, COUNT(*) AS num FROM ratings GROUP BY prod_id) rated WHERE num >= 50 ORDER BY avg_rating DESC LIMIT 1;
제품의평점분석 1 (step #1) 평가횟수가 50 개이상인제품 평점이높은순으로정렬 hive> SELECT prod_id, FORMAT_NUMBER(avg_rating, 2) AS avg_rating FROM (SELECT prod_id, AVG(rating) AS avg_rating, COUNT(*) AS num FROM ratings GROUP BY prod_id) rated WHERE num >= 50 ORDER BY avg_rating DESC LIMIT 1;
제품의평점분석 1 (step #1) 평가횟수가 50 개이상인제품 평점이높은순으로정렬 FORMAT_NUMBER(number, int D) = number 를소수점 D 자릿수만큼표현 hive> SELECT prod_id, FORMAT_NUMBER(avg_rating, 2) AS avg_rating FROM (SELECT prod_id, AVG(rating) AS avg_rating, COUNT(*) AS num FROM ratings GROUP BY prod_id) rated WHERE num >= 50 ORDER BY avg_rating DESC LIMIT 1;
제품의평점분석 1 (step #1) 평가횟수가 50 개이상인제품 평점이높은순으로정렬 hive> SELECT prod_id, FORMAT_NUMBER(avg_rating, 2) AS avg_rating FROM (SELECT prod_id, AVG(rating) AS avg_rating, COUNT(*) AS num FROM ratings GROUP BY prod_id) rated WHERE num >= 50 ORDER BY avg_rating DESC LIMIT 1; 결과 prod_id avg_rating
제품의평점분석 2 (step #1) 가장문제가되는제품을알아내기위해서 평가횟수가 50개이상인제품 평점이낮은순으로정렬 hive> SELECT prod_id, FORMAT_NUMBER(avg_rating, 2) AS avg_rating FROM (SELECT prod_id, AVG(rating) AS avg_rating, COUNT(*) AS num FROM ratings GROUP BY prod_id) rated WHERE num >= 50 ORDER BY avg_rating LIMIT 1;
제품의평점분석 2 (step #1) 평가횟수가 50 개이상인제품 평점이낮은순으로정렬 hive> SELECT prod_id, FORMAT_NUMBER(avg_rating, 2) AS avg_rating FROM (SELECT prod_id, AVG(rating) AS avg_rating, COUNT(*) AS num FROM ratings GROUP BY prod_id) rated WHERE num >= 50 ORDER BY avg_rating LIMIT 1;
제품의평점분석 2 (step #1) 평가횟수가 50 개이상인제품 평점이낮은순으로정렬 hive> SELECT prod_id, FORMAT_NUMBER(avg_rating, 2) AS avg_rating FROM (SELECT prod_id, AVG(rating) AS avg_rating, COUNT(*) AS num FROM ratings GROUP BY prod_id) rated WHERE num >= 50 ORDER BY avg_rating LIMIT 1; 결과 prod_id avg_rating
EXPLODE 를사용하여 Array 를 Records 변환 (step #2) EXPLODE 함수는 array 의각 element 마다하나의 record 생성 SPLIT 과같은함수는 table generating function 임 table generating function 을 EXPLODE 의파라매터로사용할때는 alias 필요 (e.g. AS x)
n-grams (step #2) n-gram 은단어의조합 (n = number of words) Bigram 은연속된두단어의조합 (n = 2) n-gram frequency analysis 는많은 application 에서중요한과정 검색엔진과같은 application 에서검색결과의 spelling 교정에사용 웹페이지에서가장중요한 topics 찾는데사용 social media message등에서트랜드 topics 검색에사용
HIVE 에서의 n-grams(step #2) Hive 에서는 n-grams 를계산하기위한 NGRAMS 함수제공 NGRAMS 함수는 3개의파라매터필요 String 타입의 Array of array 형태, 각 element는 word (e.g. [[ is, great ]]) n-gram 에서의 n 숫자 (number of words) 결과값의출력개수 (top-n, based on frequency) Output 은 2 개의속성을가진 STRUCT 구조의 array 리턴 ngram : n-gram 자체값 (an array of words) estfrequency : n-gram 의각값이몇번나타났는지에대한 count 값
Sentences 를 Words 로변경 (step #2) HIVE 의 SENTENCES 함수는 sentences 를 array of words 로변환 Input 값은하나이상의 sentences 가될수있음 2 개의 Sentences 를 Input 값으로받을경우, 2-dimensional 로변환
Calculating n-grams in HIVE(step #2) NGRAMS 함수는 SENTENCES 함수와자주함께사용 아래의예제에서는 normalize case 를위해 LOWER 사용 그리고 EXPLODE 함수를사용하여 array 형태의결과를 row 형태로변환
제품의평점코멘트분석 1 (step #2) 제품 1274673 의평점코멘트를 ngram 을통해 2 개의단어조합으로구성된 bigrams 를생성 가장빈번하게출력되는 5 개의 bigram 을출력 hive> SELECT EXPLODE(NGRAMS (SENTENCES (LOWER (message)), 2, 5) ) AS bigrams FROM ratings WHERE prod_id = 1274673; message 를모두소문자로변경한후, 문장을단어형태의리스트로변경 <ratings 테이블 >
제품의평점코멘트분석 1 (step #2) 제품 1274673 의평점코멘트를 ngram 을통해 2 개의단어조합으로구성된 bigrams 를생성 가장빈번하게출력되는 5 개의 bigram 을출력 hive> SELECT EXPLODE(NGRAMS (SENTENCES (LOWER (message)), 2, 5) ) AS bigrams FROM ratings WHERE prod_id = 1274673; 2 개의단어조합으로구성된 bigrams 생성 5 개의 most common bigrams 출력 array 형태로 frequency 와함께리턴 [{"ngram":["this","is"],"estfrequency":161.0},{"ngram":["too","expensive"],"estfrequency":100.0}, {"ngram":["the","others"],"estfrequency":67.0},{"ngram":["times","more"],"estfrequency":67.0}, {"ngram":["why","does"],"estfrequency":67.0}]
제품의평점코멘트분석 1 (step #2) 제품 1274673 의평점코멘트를 ngram 을통해 2 개의단어조합으로구성된 bigrams 를생성 가장빈번하게출력되는 5 개의 bigram 을출력 array 의각 element 를순서대로출력 hive> SELECT EXPLODE(NGRAMS (SENTENCES (LOWER (message)), 2, 5) ) AS bigrams FROM ratings WHERE prod_id = 1274673;
제품의평점코멘트분석 1 (step #2) 제품 1274673 의평점코멘트를 ngram 을통해 2 개의단어조합으로구성된 bigrams 를생성 가장빈번하게출력되는 5 개의 bigram 을출력 hive> SELECT EXPLODE(NGRAMS (SENTENCES (LOWER (message)), 2, 5) ) AS bigrams FROM ratings 결과 WHERE prod_id = 1274673;
제품의평점코멘트분석 2 (step #2) 제품 1274673 의평점코멘트를 ngram 을통해 3 개의단어조합으로구성된 bigrams 를생성 가장빈번하게출력되는 5 개의 bigram 을출력 hive> SELECT EXPLODE(NGRAMS (SENTENCES (LOWER (message)), 3, 5) ) AS bigrams FROM ratings WHERE prod_id = 1274673;
제품의평점코멘트분석 2 (step #2) 제품 1274673 의평점코멘트를 ngram 을통해 3 개의단어조합으로구성된 bigrams 를생성 가장빈번하게출력되는 5 개의 bigram 을출력 hive> SELECT EXPLODE(NGRAMS (SENTENCES (LOWER (message)), 3, 5) ) AS bigrams FROM ratings WHERE prod_id = 1274673; 결과
제품의평점코멘트분석 3 (step #2) 지난결과의 ten times more 는상품이너무비싸다는불만과관련이있을가능성이존재 따라서 ten times more 를포함한문장을확인 hive> SELECT message FROM ratings WHERE prod_id = 1274673 AND message LIKE %ten times more% LIMIT 3; LIKE 키워드와함께사용하며, message 문장에서 ten times more 문장이포함된 String 검색 e.g. This is ten times more expensive!
제품의평점코멘트분석 3 (step #2) 지난결과의 ten times more 는상품이너무비싸다는불만과관련이있을가능성이존재 따라서 ten times more 를포함한문장을중복제거후확인 hive> SELECT DISTINCT message FROM ratings WHERE prod_id = 1274673 AND message LIKE %ten times more% LIMIT 3; 결과 red 키워드와뭔가관련성이있어보임
제품의평점코멘트분석 4 (step #2) 지난결과를통해 red 제품에고객들의불만이많다는것을확인했지만, 충분한정보를제공하지못함 따라서 red 가포함되고, 중복이제거된결과를출력 결과 hive> SELECT DISTINCT message FROM ratings WHERE prod_id = 1274673 AND message LIKE %red% LIMIT 3; 1274673 제품이 red 와관련성이있어보이며, 실제로 1274673 이어떤제품인지확인이필요.( 뒷장의 products 테이블활용 )
제품의평점코멘트분석 5 (step #2) product_id 에해당하는제품을확인하기위해 products 테이블생성 hive> CREATE TABLE products (prod_id STRING, brand STRING, name STRING, price INT, cost INT, shipping_wt INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t ; HDFS 에경로생성후파일업로드 $ Hadoop fs mkdir products_dualcore $ Hadoop fs put products_dualcore.txt products_dualcore 테이블에데이터로드 hive> LOAD DATA INPATH products_dualcore INTO TABLE products;
제품의평점코멘트분석 5 (step #2) 데이터샘플출력
제품의평점코멘트분석 5 (step #2) 문제가되고있는 red 제품의상세정보확인을위해 prod_id=1274673 을확인 hive> SELECT * FROM products WHERE prod_id = 1274673; 결과
제품의평점코멘트분석 6 (step #2) Brand 가 Orion 이고, 제품명이 16GB USB Flash Drive (Red) 과비슷한제품을출력 hive> SELECT * FROM products WHERE name LIKE %16 GB USB Flash Drive% AND brand= Orion ; 결과 다른제품과비교하여 Red 제품의가격이잘못설정되어 10 배정도비싼가격에올려져있음을발견!!