Microsoft PowerPoint - 8주차.pptx

Size: px

Start display at page:

Download "Microsoft PowerPoint - 8주차.pptx"

영민 양
5 years ago
Views:

1 실무사례구축 실무사례 1. DW 요구사항분석 Sales 전략을수립하고실행을담당하는 Sales Manager 는다양한분석을기반으로의사결정을수행하기위하여회사의자재 (materials), 고객 (customers), 판매조직 (sales organizations) 에대한정보와판매기록에관한정보를 (1)~(4) 의 sample data 와같이관리한다. (1) Material Tables CBU / MIS 1

2 실무사례 (2) Sale Organization data CBU / MIS 2

3 실무사례 (3) Customer Tables CBU / MIS 3

4 실무사례 (4) Sales Data 년 1월부터 2012년 12월까지월별 5개씩의레코드를추가하여총 36 * 5 = 180개정도의레코드를입력하시오 ( 각필드의값은랜덤하게 ). - 각 material 별로 unit of measure 값이선택될수있도록 Sales data에대한입력폼을만들어보시오 (form 만드는방법숙지 ) CBU / MIS 4

5 실무사례 CBU / MIS 5

6 실무사례 이상의데이터를사용하여다음과같은스타스키마로구축한다 CBU / MIS 6

7 실무사례 그림 1-1 의스타스키마에대하여다음과같은분석을실행한다 : SQL 로작성 Customer Name별로판매금액의합계는? 고객이거주하는도시별로판매금액의합계는? 제품카테고리별로판매금액의합계는? 제품이름별로판매금액의합계는? \ 매장이위치한도시별로판매금액의합계는? 매장별로판매금액의합계는? 년도별 Sales Revenue의합계는? 년도별, 월별 Sales Revenue의합계는? 년도별, 월별, 주별 Sales Revenue의합계는? ( 주 : 1주 ~ 53주 ) 제품별, 년도별 Sales Revenue의합계는? 비오는날에 Sales Revenue의합계는? 환율이높은시기 / 중간 / 낮은시기에제품별로 Sales Revenue의합계는? CBU / MIS 7

8 실무사례 그림 1-1에서다음과같은분석이불가능하다. 이러한분석이가능하도록 1-1의스타스키마를확장하고, 적절한데이터베이스를로딩한다음에분석을하시오. 고객이거주하는도시별로 Sales Revenue의합계는? 고객이거주하는도시를 EAST, MIDWEST, WEST로구분한지역으로묶는다고가정할때각지역별로 Sales Revenue의합계는? 고객의성별로 Sales Revenue의합계는? 고객의연령대별 Sales Revenue의합계는? (10대, 20대,..., 60대 ) Material 테이블에서각항목을 4개의 categories인 Food, Furniture, Electronics, Others 으로묶은다음에각 category별로 Sales Revenue의합계는? 앞에서각 category별로년도별 Sales Revenue의합계는? CBU / MIS 8

9 Database star schema Access DB CBU / MIS 9

10 분석예제 Customer Name 별로 Sales Revenue 의합계는? SELECT cust. 이름, count(*) as 구매회수, sum( 단가 * 판매량 ) as 구매금액 FROM cust INNER JOIN sales ON cust. 고객번호 = sales.cust Group By cust. 이름 ; CBU / MIS 10

11 분석예제 Customer 의고객거주도시별로 Sales Revenue 의합계는? SELECT cust. 주소시, count(*) as 구매회수, sum( 단가 * 판매량 ) as 구매금액 FROM cust INNER JOIN sales ON cust. 고객번호 = sales.cust Group By cust. 주소시 ; CBU / MIS 11

12 RDB-based approach BI 구축방안 DB 설계와분석용 DB 구축및응용프로그램개발에시간소요 변화에대한유연성부족 ( 예 : 대부분의프로세스를프로그램로직으로구현하므로환경변화를반영하는데노력이필요 ) 분석과리포팅을위한별도의작업을필요로함 Packaged (BI) solution 상기방식의단점은대부분해결되나도구자체의내부적인체계로인해그방법론과처리절차에맞추어야함 ( 교육필요 ) 초기가격적인부담 (SW, 컨설팅비용 ) Tool 에서제공하지않는기능을추가하는것이어려움 Multi-dimensional data model (vs. Star Schema) 활용

13 비즈니스인텔리전스 Hive 충북대학교조완섭

14 목차 개요 Hive 배경 Hive 장점과특징 Hive 설치 Hive Data Type과데이터저장소 HiveQL - 데이터정의와데이터로딩 HiveQL - 질의어

15 Cloud system 개요 다수의컴퓨터 (PC) 들을연결하여고성능의컴퓨터를실현하는기술 ( 제품 ) 저비용으로대규모데이터를처리하는데적합함 ( 빅데이터처리 )

16 Hadoop 개요 Cloud system 상에서빅데이터의분산저장및처리를효율적으로, 안정적으로, 편리하게제공하는파일시스템 (cf. 유닉스파일시스템, 윈도우파일시스템 ) Map-Reduce 는큰데이터를다루는 job 을여러개의 subtasks 로나누어다수의서버들로구성된클러스터 (cloud) 에서병렬처리하고, 각서버가처리한로컬결과를통합하여최종결과를만드는프로그래밍방식 Map-Reduce 예제

17 개요 Map-Reduce 예제 : 단어의빈도수를세는프로그램

18 Hive 배경 Yahoo 는 Hadoop 상에서비정형데이터를다루는응용을개발하고운영하기위해 Pig 를개발함 Facebook 은 Hadoop 상에서 data warehouse 솔루션을개발하기위해 Hive 를개발함 데이터량의급증으로기존 warehousing solution 을활용하여 BI 를실현하는데한계에직면함 BI 기존의 Data warehousing BI SQL-like Hive Hadoop/Cloud Pig 작은데이터정형화된데이터비실시간성 빅데이터

19 Hadoop 과 Hive Hive 배경 Hadoop 생태계는아주큰데이터를적은비용으로처리하기위해등장함 (Cloud 시스템에서분산저장및처리로해결 ) Hadoop 에서는분산파일시스템 (HDFS) 상에서 Map-Reduce 라는특별한프로그래밍모델을사용하여큰연산작업을다수의값싼서버들로구성된클러스터에서분산처리하는프로그래밍방식을사용함 ; 저비용으로고성능컴퓨팅실현, 수평확장성을제공함 그러나 (cloud-hadoop 기반의 ) Map-Reduce 프로그래밍은작성하기어렵다는단점이있음 Hive는 Map-Reduce 프로그래밍대신에 SQL을사용하여분산처리를실현함 : SQL => Map-Reduce 프로그램 node SQL Hive Map-Reduce Hadoop MetaStore node node node

20 Hive MetaStore Hive 배경 data explorations, query optimization, query compilation 등에유용한정보인 schemas, statistics 등이저장되어있음 Facebook 의 Hive warehouse 수만개의테이블과 700TB 크기의방대한데이터가있으며, 하루 10 억명의사용자에게데이터서비스를제공하고있음

21 Hive 의장점 Hive 장점과특징 Hive 는 SQL 과유사한고급질의어 (HQL, HiveQL) 를제공하며, 사용자가작성하는 HQL 의대부분을 Map-Reduce 프로그램으로변환해서 Hadoop 에서처리함 Map-Reduce 프로그래밍의부담을제거하면서빅데이터를감당함

22 Hive 의특징 Hive 장점과특징 데이터웨어하우스응용 (OLAP) 을작성하는데적합함 : 대용량정적 (static) 데이터, 빠른응답시간불필요, 의사결정에사용 레코드단위로갱신, 삽입, 삭제를할수없음 배치처리시스템으로응답시간은다소지연되나 ( 맵리듀스구동시간 ) 대규모데이터에대한연산에서성능유지, 확장성이뛰어남 트랜잭션을제공하지않음 (OLTP 보다는 OLAP 기능임 ) Hive 를 NoSQL 과연동시켜사용하는것도가능함 Facebook 에서개발함 (Yahoo 는 Pig 를개발함 )

23 관련도구들 Pig Hive 장점과특징 Hadoop 상에서 query 가아닌데이터흐름언어를제공함 (cf. Oracle PL/SQL) 외부에있는데이터를하둡으로가져오면서적절한변환을하여좀더사용하기쉬운형태로만드는 ETL 작업에주로사용됨 Hbase (NoSQL) Hive 에서제공하지못하는행단위갱신, 빠른응답시간, 트랜잭션기능등이필요하다면 DBMS 가필요함 HBase 는분산및확장가능한데이터저장소 (DBMS) 로써행단위갱신, 빠른응답시간, 트랜잭션기능, 컬럼지향저장소등 DB 기능을제공함 HBase 는 SQL 과같은고급질의어를제공하지않지만 HBase 는 Hive 와통합되어사용될수있음

24 Hive 장점과특징 Hive, Pig, and Hbase

25 설치가이드참고 별도자료 Hive 설치및시작하기

26 Hive Data Type 과데이터저장소 Primitive Types

27 Hive Data Type 과데이터저장소

28 Hive Data Type 과데이터저장소 Complex Types (or collection) Hive 는 struct, map, array 타입을지원함 관계형데이터베이스에서는이들타입들이정규화를위반하므로별도의테이블로모델링되었으나,Hive 에서는대용량데이터처리에서조인비용절감등을기대할수있으므로다소질의가복잡해지더라도이를지원함

29 Hive Data Type 과데이터저장소 Data 저장소 Hive data 는다음과같이구조화됨 Database Tables, views, partitions, columns 등에대한 naming conflicts 를방지하고, 사용자혹은사용자그룹에대한 security 강화방안임 Tables 동일한스키마를데이터집합 예 : page_views(timestamp, userid, pare_url, referrer_url, IP) Partitions 각테이블은데이터가어떻게저장되는지를결정하는하나이상의 partition Keys 를가질수있음 Buckets (or clusters) 각파티션의데이터는컬럼의해쉬함수값을기반으로 buckets 단위로나뉘어짐

30 Hive Database HiveQL - 데이터정의 hive> CREATE DATABASE financials hive> CREATE DATABASE IF NOT EXISTS financials hive> SHOW DATABASES default financials hive> CREATE DATABASE human_resources hive> SHOW DATABASES default financials human_resources hive> SHOW DATABASES LIKE 'h.*' human_resources hive>... - Hive 는각데이터베이스에대하여하나의디렉토리를생성하고 (: DB_directory), 그데이터베이스에서생성한테이블들은 DB_directory 의서브디렉토리에저장함 - 다만, default database 에속하는테이블들은자신의디렉토리를가지지않음 - Hive Database 는단지테이블들의카탈로그혹은 name space ( 테이블이름의충돌을막는방안 ) 로간주될수있음

31 HiveQL - 데이터정의 Managed table (or internal table) - Hive가테이블내의데이터대한수명을관리하는테이블로써internal table이라고도함 - 지금까지설명한테이블은모두 managed table 임 - Hive가데이터파일을관리하며, 테이블삭제시메타정보와파일을함께삭제함 - hive.metastore.warehouse.dir 에정의된디렉토리 (e.g., /user/hive/warehouse,by default) 의서브디렉토리로이들테이블데이터를저장함 - drop table 명령어로테이블내의모든데이터가삭제됨 External table - Hive가데이터를소유하지않고기존파일을테이블의데이터로사용하는테이블로써테이블생성시 External 이라는키워드를사용함 - Hive는그테이블에대한메타데이터만가지고있다가drop table 명령어가실행되면 데이터는 그대로두고 Hive meta data 내에서그테이블에대한정보만지움

32 HiveQL - 데이터정의 - 예제 : 테이블생성 CREATE TABLE employees ( name STRING, salary FLOAT, subordinates ARRAY<STRING>, deductions MAP<STRING, FLOAT>, address STRUCT<street:STRING, city:string, state:string, zip:int> );

33 HiveQL - 데이터정의 파티셔닝된 managed table (external table 도파티셔닝이가능함 ) - 특정한속성값을기준으로데이터를분할하여저장하는테이블로써그속성에대한조건을빠르게처리할수있게됨 - 데이터를첫번째 country, 두번째 state 의값을기준으로파티션하는예제 CREATE TABLE employees ( name STRING, salary FLOAT, subordinates ARRAY<STRING>, deductions MAP<STRING, FLOAT>, address STRUCT<street:STRING, city:string, state:string, zip:int> ) PARTITIONED BY (country STRING, state STRING); Hive는파티셔닝을위해다음과같은서브디렉토리들을생성하게됨 (country, state 값의조합 )....../employees/country=ca/state=ab.../employees/country=ca/state=bc....../employees/country=us/state=al.../employees/country=us/state=ak... Cloud/hadoop 의분산파일시스템과연계

34 HiveQL - 데이터정의 - 다음질의는실제로하나의디렉토리 (...country=us/state=il) 만탐색하면질의결과를구할수있게되므로성능이매우좋음 ( 필터링효과가큼 ) SELECT * FROM employees WHERE country = 'US' AND state = 'IL'; - 그러나모든파티션을탐색해야하는질의에서파티션의개수가많다면 (where 절의조건에서필터링효과가매우적은경우 ) 거대한 MR 작업을수반하므로속도가저하가우려됨 ; - map/reduce 모드를다음과같이 strict 로설정함으로써지나친오버헤더를유발하는질의를금지시킬수있음 hive> set hive.mapred.mode=strict; hive> SELECT e.name, e.salary FROM employees e LIMIT 100; FAILED: Error in semantic analysis: No partition predicate found for Alias "e" Table "employees" <== strict mode 인경우where 절에파티션에대한필터링조건을주어야함

35 Hive Data Loading 데이터로딩의예제 (1) create table statement : create table emp5( name string, salary float) partitioned by (country string, state string) row format delimited fields terminated by ','; (2) data file 준비 Cho, Park, 5000 Kim, Lee, (3) Hive 데이터로딩 hive> load data local inpath 'data_emp5' into table emp5 > partition (country = 'Korea', state = 'Chungbuk'); (4) 확인 hive> > select * from emp5; OK Cho Korea Chungbuk Park Korea Chungbuk Kim Korea Chungbuk Lee Korea Chungbuk Time taken: 0.1 seconds, Fetched: 4 row(s)\

36 Hive Data Loading 데이터값의텍스트파일인코딩 데이터파일에서필드구분자로쉼표나탭을사용할수있지만데이터자체에쉼표나탭이있을경우혼선 Hive에서는제어문자 ( 문자열로잘사용되지않음 ) 를필드구분자로지정할수있음 다음표는 Hive의기본구분기호

37 Hive Data Loading 데이터파일의예 Employee 테이블에로딩된모습

38 Hive Data Loading 테이블을생성할때필드구분자를별도로지정하는예 ^A ^B ^C 필드가콤마로구분되어있는경우의예 CREATE TABLE some_data ( first FLOAT, second FLOAT, third FLOAT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

39 HiveQL - 질의어 - 테이블생성의예 CREATE TABLE employees ( name STRING, salary FLOAT, subordinates ARRAY<STRING>, deductions MAP<STRING, FLOAT>, address STRUCT<street:STRING, city:string, state:string, zip:int> ) PARTITIONED BY (country STRING, state STRING); - 간단한질의의예 hive> SELECT name, salary FROM employees; John Doe Mary Smith Todd Jones Bill King

40 HiveQL - 질의어 - Collection type 을선택하는질의 (subordinates : array) : JSON (Java Script Object Notation) 구문으로결과를표시함 hive> SELECT name, subordinates FROM employees; John Doe ["Mary Smith","Todd Jones"] Mary Smith ["Bill King"] Todd Jones [] Bill King [] - Collection type 을선택하는질의 (deduction : map) hive> SELECT name, deductions FROM employees; John Doe Mary Smith Todd Jones Bill King {"Federal Taxes":0.2, "State Taxes":0.05, "Insurance":0.1} {"Federal Taxes":0.2, "State Taxes":0.05, "Insurance":0.1} {"Federal Taxes":0.15, "State Taxes":0.03, "Insurance":0.1} {"Federal Taxes":0.15, "State Taxes":0.03, "Insurance":0.1}

41 HiveQL - 질의어 - Collection type을선택하는질의 (address : struct) hive> SELECT name, address FROM employees; John Doe {"street":"1 Michigan Ave.", "city":"chicago","state":"il","zip":60600} Mary Smith {"street":"100 Ontario St.", "city":"chicago","state":"il","zip":60601} Todd Jones {"street":"200 Chicago Ave.", "city":"oak Park","state":"IL", "zip":60700} Bill King {"street":"300 Obscure Dr.","city":"Obscuria","state":"IL","zip":60100} - Collection type/array에서특정항목을참조하는방법 (index는 0-based); 결과가없으면 NULL로 display hive> SELECT name, subordinates[0]from employees; John Doe Mary Smith Mary Smith Bill King Todd Jones NULL Bill King NULL

42 HiveQL - 질의어 - Collection type/map 타입의컬럼에서항목참조하기 : index 대신에 key values 를사용하여특정항목을지정함 hive> SELECT name, deductions["state Taxes"] FROM employees; John Doe 0.05 Mary Smith 0.05 Todd Jones 0.03 Bill King Collection type/struct 타입의컬럼에서항목참조하기 : "dot" 표기법사용 hive> SELECT name, address.cityfrom employees; John Doe Chicago Mary Smith Chicago Todd Jones Oak Park Bill King Obscuria

43 HiveQL - 질의어 Nested SQL

44 HiveQL - 질의어 - 예제 : select list 의세번째항목을 case 문에서조건으로결정함 hive> SELECT name, salary, > CASE > WHEN salary < THEN 'low' > WHEN salary >= AND salary < THEN 'middle' > WHEN salary >= AND salary < THEN 'high' > ELSE 'very high' > END AS bracket FROM employees; John Doe very high Mary Smith high Todd Jones high Bill King middle Boss Man very high Fred Finance very high Stacy Accountant middle

45 HiveQL - 질의어 실무예제 - stocks ( 증시 ) 테이블과 dividend ( 배당 ) 테이블을생성하고, 데이터를로딩함 - stocks 테이블구조 CREATE EXTERNAL TABLE IF NOT EXISTS stocks ( exchange STRING, symbol STRING, ymd STRING, price_open FLOAT, price_high FLOAT, price_low FLOAT, price_close FLOAT, volume INT, price_adj_close FLOAT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/data/stocks';

46 HiveQL - 질의어 - 배당금 (dividends) 테이블을생성하고, stocks 테이블과조인 CREATE EXTERNAL TABLE IF NOT EXISTS dividends ( ymd STRING, dividend FLOAT ) PARTITIONED BY (exchangestring, symbolstring) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

47 stocks data HiveQL - 질의어 exchange stock_symbol date stock_price_open stock_price_high stock_price_low stock_price_close stock_volume stock_price_adj_close NASDAQ KINS NASDAQ KINS NASDAQ KINS NASDAQ KINS NASDAQ KINS NASDAQ KINS NASDAQ KINS NASDAQ KINS NASDAQ KINS NASDAQ KINS NASDAQ KINS dividends data exchange stock_symbol date dividends NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB NASDAQ KMGB

48 HiveQL - 질의어 GROUP BY 절 - 하나이상의컬럼들에대하여그값 ( 들 ) 을기준으로그룹을만든후집계를수행 hive> SELECT year(ymd), avg(price_close) FROM stocks > WHERE exchange = 'NASDAQ' AND symbol = 'AAPL' > GROUP BY year(ymd);

49 HiveQL - 질의어 HAVING 절 - 예제 : NASDAQ 자료에서 AAPL 사의년도별로종가평균이 50 달러이상인년도와종가평균을 display hive> SELECT year(ymd), avg(price_close) > FROM stocks > WHERE exchange = 'NASDAQ' AND symbol = 'AAPL' > GROUP BY year(ymd) > HAVING avg(price_close) > 50.0;

50 HiveQL - 질의어 Inner Join hive> SELECT a.ymd, a.price_close, b.price_close > FROM stocks a JOIN stocks b ON a.ymd = b.ymd > WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM'; stocks 테이블의구조 ; AAPL( 애플 ) 과 IBM 의날짜별주식종가비교 - 애플의회사설립일이최근 (2010 년 ) 이라결과는 2010 년이후부터나타남 a b

51 HiveQL - 질의어 - stocks 테이블과 dividends 테이블의조인 ( 애플사의종가와배당금정보 ) hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend > FROM stocks s JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol > WHERE s.symbol = 'AAPL'; AAPL AAPL AAPL AAPL AAPL AAPL AAPL

52 HiveQL - 질의어 - 예제 AAPL 사의날짜별종가와배당금이지급된날짜의경우배당률까지 display hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend > FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol > WHERE s.symbol = 'AAPL'; AAPL 80.0 NULL AAPL NULL AAPL NULL AAPL 80.0 NULL AAPL NULL AAPL 79.0 NULL AAPL AAPL 75.5 NULL AAPL 78.5 NULL AAPL NULL AAPL NULL AAPL NULL AAPL NULL AAPL 74.5 NULL

53 HiveQL - 질의어 Outer-Join 개념

추가학습자료 http://www.tutorialspoint.com/hive/ https://cwiki.

54 추가학습자료

빅데이터분산컴퓨팅-5-수정

빅데이터분산컴퓨팅-5-수정 Apache Hive 빅데이터분산컴퓨팅 박영택 Apache Hive 개요 Apache Hive 는 MapReduce 기반의 High-level abstraction HiveQL은 SQL-like 언어를사용 Hadoop 클러스터에서 MapReduce 잡을생성함 Facebook 에서데이터웨어하우스를위해개발되었음 현재는오픈소스인 Apache 프로젝트 Hive 유저를위한