대용량 데이터를 관계형 DB나 HDFS 파일로 저장해놓고 필요한 대상만 데이터 전처리, 샘플링해서 R에서 통계분석, 기계학습을 하는 경우가 많이 있습니다. 


이전 포스팅에서 R로 Hive와 PostgreSQL, MySQL 등에 DB connect 해서 사용하는 방법을 소개했었습니다. 


이번 포스팅에서는 


 - R로 Oracle DB connect 하고 데이터 query 하는 방법

 - R로 Presto DB connect 하고 데이터 query 하는 방법


을 소개하겠습니다. 





 1. R로 Oracle DB connect 하고 데이터 query 하는 방법


- rJava, DBI, RJDBC 패키지는 의존성이 있으므로 순서대로 설치하시기 바랍니다. (순서가 바뀌면 에러 발생)

- 혹시 Java 가 설치 안되어 있거나, 버전이 안맞아서 새로 설치해야 하면 이곳 참조 => http://rfriend.tistory.com/232

- Oracle DB의 SQL query문 마지막에 세미콜론(';')을 사용하면 에러가 나므로 유의하세요. 



# R Oracle DB Connect

 

install.packages('rJava')
install.packages('DBI')
install.packages('RJDBC')

 

library(rJava)
library(DBI)
library(RJDBC)

 


# setting driver and connection configuration


drv_Oracle <- JDBC(driverClass="oracle.jdbc.driver.OracleDriver", classPath="C:/download/ojdbc7.jar")

 

con_Oracle <- dbConnect(drv_Oracle, 
                                         "jdbc:oracle:thin:@//12.34.567.89:8888/DB_name",  # ip:port
                                         "id", 
                                         "password")

 

# SQL query
query <- "
       SELECT *
            FROM db.table
            WHERE var1 = 'aaa'
                  AND var2 = 'bbb' " # Do not use ';' (semicolon)

 

# executing DB connect & query
my_data <- dbGetQuery(con_Oracle, query)

 

# DB disconnect
dbDisconnect(con_Oracle)

 

# delete temp objects
rm(drv, con_Oracle, query)

 




 2. R로 Presto DB connect 하고 데이터 query 하는 방법


Presto는 Facebook에서 개발해서 오픈소스로 푼 분산 SQL 쿼리 엔진(distributed SQL query engine for big data)입니다. 메모리 기반으로 실행이 되다 보니 Hive 보다 속도가 많이 빠르기도 하구요, 또 Hive, phoenix, mysql 등 이기종 DB에 접속해서 데이터를 취합하고 가공하는 작업을 할 수 있어서 매우 편리하기도 합니다. 



# R Presto DB Connect


# install and request package

install.packages("RPresto")

library(RPresto)


# setting dbConnect

con_RPresto <- dbConnect(

                                           RPresto::Presto(), 

                                           host='http://12.34.56.789', 

                                           port=8080,

                                           user='user_id',

                                           schema='schema_nm',

                                           catalog='hive', # mysql, phoenix..

                                           session.timezone='UTC' 

                                           )


query <- paste0("

    SELECT var1, var2, count(*) as cnt

        FROM db.table

        WHERE var1 = 'aa'

            AND var2 = 'bb'

        GROUP BY var1, var2

")


# executing query

my_data <- dbGetQuery(con_RPresto, query)


# disconnecting DB

dbDisconnect(conn = con_Postgres)



많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾸욱 눌러주세요. ^^



Posted by R Friend R_Friend

지난번 포스팅에서는 웹으로 간단하게 SQL 을 연습할 수 있는 온라인 사이트로서 


w3schools.com/sql 과 테이블을 쿼리해서 데이터 항목을 알아보았습니다. 


주문, 주문상세 테이블과 고객, 상품, 상품카테고리, 상품제공업체, 배송업체, 종업원의 기준정보 테이블이 있는 것으로 봐서 유통업체의 데이터임을 알 수 있습니다. 이벤트나 프로모션 정보 테이블, 온라인이나 모바일 등의 채널 이용 정보 테이블, 고객등급/고객세분화 정보 테이블,  결제수단 정보 테이블 등... 뭐, 유통업체라면 더 많은 테이블이 있어야 겠지만서도, SQL 연습하라고 만든 가상의 약식 데이터 DB 테이블이므로 '이 정도도 어디야'하고 감사하면 사용하면 좋겠습니다. 


ERD (Entity Relationship Diagram)이 없어서 테이블, 데이터 간의 관계를 한 눈에 파악하는 것이 어려웠는데요, 시간 좀 내서 아래처럼 ERD 그려보았습니다. 


[ 유통업체 ERD (Entity Relationship Diagram) ]


* https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all  에 있는 테이블별 칼럼을 보고 추측해서 ERD 그린 것임.  SQL 연습하려는 분은 이 ERD 참고해서 테이블 간 join 해서 분석하면 됨. 



테이블을 여러개 Join 해서 통계량 집계하고 정렬하는 예를 들어보겠습니다. 



 
[SQL Query 문제] 


"제품 카테고리 중 'Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments' 카테고리에 대해서 카테고리별로 판매가 일어난(주문이 발생한) 제품들의 가격의 합계, 총 주문 발생 회수, 제품들의 가격의 평균을 구하시오.  


단, 카테고리별 제품 가격의 합계가 1,100 이상인 경우만 집계 결과를 제시하되, 

가격의 합계를 기준으로 내림차순으로 정렬하여 제시하시오."


SELECT e.CategoryName AS CategoryName, 

        SUM(e.Price) AS Price_sum, 

        COUNT(*) AS Order_cnt, 

        AVG(e.Price) AS Price_avg

    FROM 

    (SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName  -- sub query 2

     FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID  -- sub query 1

           FROM OrderDetails a

           INNER JOIN Products b ON a.ProductID = b.ProductID) c

     LEFT JOIN Categories d ON c.CategoryID = d.CategoryID) e

     WHERE e.CategoryName IN ('Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments')

     GROUP BY e.CategoryName

     HAVING Price_sum > 1100

     ORDER BY Price_sum DESC;   

 

 

Number of Records: 3

====================================================

CategoryName        Price_sum        Order_cnt           Price_avg

====================================================

Dairy Products         2863.2                  100               28.63

Seafood                  1345.17                    67               20.07

Condiments           1121.5                    49               22.88




위의 문제가 너무 복잡하고, SQL Query도 SUM(), COUNT(), AVG() 등의 aggregation 함수, FROM 절에 Sub Query 랑 INNER JOIN, LEFT JOIN 이 들어가 있고, WHERE 조건절, GROUP BY, HAVING, ORDER BY 등 어지간한 SQL 기능이 망라되어 있어서 복잡하긴 합니다. 


Query가 잘 이해가 안되면 Sub Query를 하나씩 순차적으로 실행시켜보면서 결과를 확인해보면 한결 이해하기가 쉽습니다. 


예를 들어보자면, 위의 Query를 가장 안에 위치한 Sub Query 부터 하나씩 아래에 풀어보겠습니다. 


=========================================================================


[ sub query 1]


OrderDetails 테이블(a)Products 테이블(b)ProductID key를 기준으로 INNER JOIN으로 교집합을 구해서 Products 테이블에서 상품의 가격과 카테고리ID 데이터를 가져왔습니다. (테이블 구분하기 편하라고 a, b 라는 alias name 별명을 부여해서 변수 앞에 b.Price 처럼 붙여서 사용합니다)  상위 5개만 예시로 가져오겠습니다. 



SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID

           FROM OrderDetails a

           INNER JOIN Products b ON a.ProductID = b.ProductID

           LIMIT 5;

 

 

OrderID   ProductID   Price   CategoryID

10248 11                 21         4

10248 42                 14         5

10248 72                 34.8         4

10249 14                 23.25 7

10249 51                 53         7





=========================================================================


[sub query 2]


위의 'sub query 1' 결과 테이블(c)에다가 CategoryID key를 기준으로 Categories 테이블(d)을 LEFT JOIN 하여 Categories 테이블에 있는 CategoryName 칼럼을 붙여서 가져왔습니다.  CategoryName 을 붙여 와야지 원래의 SQL Query 문제에 나와있는 'CategoryName별 판매상품 가격의 합계, 판매(주문)회수, 평균판매가격'을 구할 수 있겠지요?



SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName

     FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID

           FROM OrderDetails a

           INNER JOIN Products b ON a.ProductID = b.ProductID) c

     LEFT JOIN Categories d ON c.CategoryID = d.CategoryID

     LIMIT 5;

 


c.OrderID   c.ProductID  c.Price  c.CategoryID   d.CategoryName

10248 11                 21         4                 Dairy Products

10248 42                 14         5                 Grains/Cereals

10248 72                 34.8         4                 Dairy Products

10249 14                 23.25 7                 Produce

10249 51                 53         7                 Produce

 




========================================================================


아래 Query 는 CASE WHEN ~ THEN ... ELSE ... END 문으로 연속형 변수(continuous variable)를 범주형 변수(categorical variable) 로 변환하는 예제 Query 입니다.  아래처럼 '가격대(Price_grp)' 변수를 만든 후에 위에 'SQL Query' 문제에서 사용했던 Query 를 사용해서 다른 응용을 할 수 있습니다. 



SELECT a.OrderID, a.ProductID, b.Price, 

       CASE WHEN b.Price >= 40 THEN '1_over_40'

                WHEN b.Price < 40 AND b.Price >= 20 THEN '2_20_40'

                ELSE '3_under_20' END Price_grp

    FROM OrderDetails a

    INNER JOIN Products b ON a.ProductID = b.ProductID

    LIMIT 10;

 


OrderID ProductID Price Price_grp

10248 11       21         2_20_40

10248 42       14         3_under_20

10248 72       34.8         2_20_40

10249 14       23.25         2_20_40

10249 51       53         1_over_40

10250 41       9.65         3_under_20

10250 51       53         1_over_40

10250 65       21.05         2_20_40

10251 22       21         2_20_40

10251 57       19.5         3_under_20




많은 도움이 되었기를 바랍니다. 


이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾸욱 눌러주세요. ^^



Posted by R Friend R_Friend

집에서 개인 컴퓨터로 SQL 연습을 하고 싶은데 


- 상용 DBMS 평가판 혹은 오픈 소스 DBMS 설치하자니 힘들고

- DB, Table 생성하고, 데이터 파일을 구해서 import 하거나 

  혹은 건건이 insert 하기에 힘들고


할 때 아주 쉽고 빠르게, 간편하게 웹 상에서 SQL 연습할 수 있는 사이트가 있어서 소개합니다. 


w3schools.com 이라는 곳에서 다양한 언어의 튜토리얼을 제공하는데요, 그 중에서 SQL도 튜토리얼과 함께 연습할 수 있는 웹 환경도 제공하고 있습니다. MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres 등의 다양한 DB에 대한 SQL 튜토리얼을 제공하니 이곳만 잘 이용해도 특정 DB를 염두에 두고 쓰여진 SQL 책보다 더 유용할 수도 있겠습니다. Data 도 준비가 다 되어있어서 그냥 웹에 접속해서 연습하면 됩니다. 


단, Chrome, Safari, FireFox 브라우저만 지원하고, Internet Explorer 는 지원하지 않습니다. 


접속할 주소는요, 


https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all 


이며, 아래와 같은 화면이 나타납니다. 


[ w3schools.comSQL 연습할 수 있는 초기 화면 ]




순서대로 살펴보면 

(1) 왼쪽 상단에 SQL 을 입력할 수 있는 'SQL Statement: ' 창이 있습니다. 

(2) 왼쭉 중간에 'Run SQL >' 이라는 네모 단추가 있는데요, 이를 커서로 클릭하면 SQL이 실행됩니다. 

(3) 왼쪽 하단에 'Reslut: ' 란에 SQL 실행 결과가 나타납니다. 

(4) 우측 상단에 보면 Database에 들어있는 Table 이름과 Record 수가 나옵니다. 


아래의 이름으로 총 8개의 Table에 있는 데이터를 SQL 연습하는데 사용할 수 있습니다! 


No.

Tablename

Records 

1

Customers

91 

 Categories

 Employees

10 

 OrderDetails

518 

Orders

196 

 Products

77 

 Shippers

 Suppliers

29 



ERD (Entity Relationship Diagram) 이 있으면 좋을 텐데요, 그게 없는지... 못 찾겠네요. 


각 Table 별로 상위 5개씩 Select 해서 조회를 해보면 아래와 같습니다. 

각 Table 이름을 봐도 그렇고, 상위 5개 records 조회를 해서 봐도 그렇고, 유통업체에서 사용하는 DB table 들을 예로 간단한 예제 DB를 제공한다고 보면 되겠습니다. 


SELECT

    FROM Customers

    LIMIT 5; 


CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden



SELECT

    FROM Categories

    LIMIT 5;


 CategoryID CategoryName Description

1                 Beverage                 Soft drinks, coffees, teas, beers, and ales

2                 Condiments         Sweet and savory sauces, relishes, spreads, and seasonings

3                 Confections         Desserts, candies, and sweet breads

4                 Dairy Products    Cheeses

5                 Grains/Cereals         Breads, crackers, pasta, and cereal



SELECT

    FROM Employees

    LIMIT 1; 

 

EmployeeID LastName FirstName BirthDate Photo         Notes

1                 Davolio         Nancy         1968-12-08 EmpID1.pic Education includes a BA in psychology from Colorado State University. She also completed (The Art of the Cold Call). Nancy is a member of 'Toastmasters International'.



SELECT

    FROM OrderDetails

    LIMIT 5;


OrderDetailID OrderID ProductID Quantity

1                 10248 11                 12

2                 10248 42                 10

3                 10248 72                 5

4                 10249 14                 9

5                 10249 51                 40 



SELECT

    FROM Orders

    LIMIT 5;

 

OrderID CustomerID EmployeeID OrderDate ShipperID

10248 90                 5                 1996-07-04 3

10249 81                 6                 1996-07-05 1

10250 34                 4                 1996-07-08 2

10251 84                 3                 1996-07-08 1

10252 76                 4                 1996-07-09 2



SELECT

    FROM Products

    LIMIT 5;


ProductID ProductName SupplierID CategoryID Unit Price

1 Chais 1 1 10 boxes x 20 bags 18

2 Chang 1 1 24 - 12 oz bottles 19

3 Aniseed Syrup 1 2 12 - 550 ml bottles 10

4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22

5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35



SELECT

    FROM Shippers; 


ShipperID ShipperName         Phone

1                 Speedy Express (503) 555-9831

2                 United Package (503) 555-3199

3                 Federal Shipping (503) 555-9931

 


SELECT

    FROM Suppliers

    LIMIT 2; 

 

SupplierID SupplierName ContactName Address City PostalCode Country Phone

1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD UK (171) 555-2222

2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822




다음번 포스팅에서는 ERD 한번 그려서 올려보겠습니다. 그리고 table 간 Join 도 해보고, aggregation 함수도 몇 개 예를 들어서 한번 더 포스팅해보겠습니다. 


SQL 집에서 간단하게 공부하시려는 분들에게 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되셨다면 아래의 '공감~'를 꾸욱 눌러주세요. ^^



Posted by R Friend R_Friend

 

이번 포스팅에서는 R로 Hive에 바로 접속(dbConnect)해서 바로 Hive 내의 데이터를 추출해서 R의 데이터 프레임으로 만드는 방법에 대해서 소개하겠습니다.

 

dbConnect 를 사용하지 않을 때와 사용할 때를 비교해보면 아무래도 dbConnect를 하는 것이 편합니다. 

 

그리고 혹시 R로 Hive table의 데이터를 사용해서 자동화하는 프로그램을 짰다고 할 경우 관리 측면에서도 직접 Hive에 연동해서 batch job 실행시키는 것이 관리 측면에서도 용이합니다.

 

------------------------------------------- 

[ R 따로, Hive 따로 사용할 경우 Hive에서 데이터 추출 및 R에서 사용하기 절차]

 

  1) Hive 로 데이터 처리/추출하여 text file, 또는 csv file 로 내리기

    => 2) R 에서 read.table() 혹은 read.csv() 함수로 데이터 불러오기

 -------------------------------------------

 

vs.

 

------------------------------------------- 

[ R로 Hive에 dbConnect 할 경우 Hive에서 데이터 추출 및 R에서 사용하기 절차 ]

 

  1) R에서 Hive Query로 직접 데이터 처리하여 R로 바로 data.frame으로 내리기 (끝!)
     (별도로 불러오기 필요 없음!)

-------------------------------------------

 

 

 

대신에 사용환경 설정해주는게 처음에 좀 번거로운게 있습니다. ^^;

한번 고생하고 나중에 두고 두고 편할 거냐, 아니면 당장의 수고스러움을 피하고 조금 불편한거 감수할거냐의 선택인데요, 이왕 이번 글 읽기 시작하셨으니 아래 설명 참고해서 R로 Hive 접근할 수 있는 환경 세팅해보심이 어떨런지요. 

 

차근차근 설명해보겠습니다.

 

 

(1) Java 최신 버전 설치하기

 

R로 Hive 연동해서 쓸 때 Java를 사용하므로 먼저 Java를 설치해야 합니다.  이미 Java를 설치했다고 하더라도 최신 버전이 아니면 에러가 나더라구요.

 

Java 설치 확인하는 방법은 cmd 창에서 아래 처럼 'where /R C:\ java.exe' 라고 입력했는데 '정보 : 제공된 패턴에 해당되는 파일을 찾지 못했습니다'라는 메시지가 뜨면 설치가 안되어 있는 겁니다.

 

 

 

설치를 하려면 아래의 링크된 주소로 접속해서 Java SE download 를 합니다.

 

http://www.oracle.com/technetwork/java/javase/downloads/index.html

 

 

아래 그림이 화면에 나오면 Java DOWNLOAD 이미지 클릭하세요.

 

 

그러면 아래의 화면이 나오는데요, 각자의 OS, bit 체계 확인해서 본인의 것에 맞는거 다운로드 하시면 됩니다.   (참고로, 저는 window7 OS 64bix 운영체제에 R x64 3.3.1 version 사용하고 있습니다. )

 

중간에 ( ) Accept License Agreement 에 체크하고 다운로드 받으세요.

 

 

 

[참고 : Windows 운영체제 확인하는 방법]

 

(방법 1) 제어판 > 시스템  선택

(방법 20) 시작메뉴 오른쪽의) 컴퓨터 > (마우스 오른쪽 누르고) 속성 선택

 

하면 아래의 화면 나옵니다. 

 

 

 

 

jdk-8u101-windows-x64.exe 파일을 다운로드 완료하였다면 클릭해서 설치해주세요.

(계속'Next' 누르시면 됩니다)

 

 

 

C:\Program Files\Java\jre1.8.0_101  폴더에 Java가 설치되었을 겁니다.

cmd 창에서 확인해보니 잘 설치가 되었군요.

 

 

 

 

 

(2) 메모리 늘리기(increase heap size of rJava) : options(java.parameters = "-Xmx8g")

 

Java는 virtual machine을 사용하므로, 만약 메모리가 부족하면 'java.lang.OutOfMemoryError: Java heap space' 에러 메시지가 뜹니다. 

 

R에서는 default memory setting 값이 512 MB 입니다.  Hive에서 512 MB 넘는 파일 추출하기라도 하면 에러납니다.  메모리 문제를 피하기 위해서 아래처럼 options(java.parameters = "-Xmx4g" 라는 명령문으로 메모리를 4G 까지 쓸 수 있도록 설정해보겠습니다.

(만약 컴퓨터의 메모리가 빠방하다면 options(java.parameters = "-Xmx8g" 로 해서 8G 로 설정할 수도 있습니다)

 

> ##---------------------------- > ## R - Hive connect > ##---------------------------- > > # R: Increase heap size for rJava > # : assign 4 gigabytes of heap space to the Java environment > # (<-> default setting : 512 MB) > > options(java.parameters = "-Xmx4g" )

 

 

 

 

(3) rJava, RJDBC 패키지 설치 및 로딩 (rJava, RJDBC RJDBC package installation and loading)

 

RJDBC가 rJava에 의존적이므로 반드시 library(rJava)를 실행한 후에 library(RJDBC) 순서로 로딩을 해야 합니다.

 

> # rJava, RJDDB package installation and loading
> install.packages("rJava")
trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.3/rJava_0.9-8.zip'
Content type 'application/zip' length 713501 bytes (696 KB)
downloaded 696 KB

package ‘rJava’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
	C:\Users\Administrator\AppData\Local\Temp\Rtmpc9SiL3\downloaded_packages

> install.packages("RJDBC") trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.3/RJDBC_0.2-5.zip' Content type 'application/zip' length 66405 bytes (64 KB) downloaded 64 KB package ‘RJDBC’ successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\Administrator\AppData\Local\Temp\Rtmpc9SiL3\downloaded_packages

> library(DBI)
> library(rJava) > library(RJDBC)

 

 

 

 

(4) R에 Hive Class Path, Dirver, dbConnect 설정

 

Hive/Hadoop Class Path, dbConnect 설정은 하둡 엔지니어에게 물어서 사용하시기 바랍니다.

(아래는 그냥 예시임...)

 

# setting class path

hive.class.path = list.files(path=c("/usr/lib/hive/lib"), pattern="jar", full.names=T);
hadoop.lib.path = list.files(path=c("/usr/lib/hadoop/lib"), pattern="jar", full.names=T);
hadoop.class.path = list.files(path=c("/usr/lib/hadoop"), pattern="jar", full.names=T); 
class.path = c(hive.class.path, hadoop.lib.path, hadoop.class.path);
.jinit(classpath = class.path)

 

# setting driver

drv <- JDBC("org.apache.hive.jdbc.HiveDriver")

 

# setting dbConnect : driver, ip, port, userID, password

conn <- dbConnect(drv, "jdbc:hive2://xxx/xxx", "userID", "password")

 

 

(* 주의 : dbConnect 할 때요, Hive 설치된 것이 jdbc:hive인지 jdbc:hive2 인지 확인해서 사용하세요.)

 

 

 

(5) R에서 Hive Query 날리기 : dbGetQuery()

 

 

# Hive Query

db_qry <- dbGetQuery(conn,
      "SELECT *
          FROM db.table
          WHERE ymd = '2016-09-03'
          LIMIT 100;")

 

db_qry # query result

 

 

 

이렇게 R에서 Hive로 Query를 날리면 R에 데이터 프레임(data.frame) 이 생성됩니다.

 

 

 

[참고] hive 2.0.1 설치 및 metastore 설정 =>  http://www.gooper.com/ss/bigdata/271507 

 

 


 

참고로, RPostgreSQL package를 사용해서 R로 PostgreSQL 사용하는 방법도 아래에 소개합니다.  

 

 

# installation and loading RPostgreSQL package

install.packages("RPostgreSQL")
library(RPostgreSQL)

 

# setting Driver : dbDriver()

drv <- dbDriver("PostgreSQL")

 

# setting dbConnect
con <- dbConnect(drv, dbname = "dbname",
                               host = "xx.xx.xxx.xx",
                               port = xxxx,
                               user = "userID",
                               password = "password")

 

# DB SQL Query

r_sql <- c("SELECT var1, sum(var2) as sum_var2
    FROM db.table
    WHERE var3 = 'xxx' AND var4 = 'xxx'
    GROUP BY var1
    ORDER BY var1;")

 

sql_result <- dbGetQuery(con, r_sql)

 

sql_result # query result

 

dbDisconnect(conn = con)

 

 

 


 

 

RMySQL package를 사용해서 R로 MySQL 접속해서 사용하는 방법은 아래와 같습니다. 

위의 RPostgreSQL과 비슷해요.

 

 

# install and loading RMySQL package

install.packages("RMySQL")
library(RMySQL)

 

# setting dbDriver, dbConnect()

drv <- dbDriver("MySQL")

con <- dbConnect(drv,
                 dbname = "dbname",
     user = "userID",
     host = "xx.xx.xxx.xx",
     port = xxxx,
     password = "password")

 

# MySQL query : dbGetQuery

r_sql <- c("SELECT var1, sum(var2) as sum_var2
    FROM db.table
    WHERE var3 = 'xxx' AND var4 = 'xxx'
    GROUP BY var1
    ORDER BY var1;")
 
test.table <- dbGetQuery(con, r_sql)

 

# disconnect MySQL DB : dbDisconnect()

dbDisconnect(conn = con)

 

 

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

 

 

Posted by R Friend R_Friend

 

 

 

 

--------------------------------
-- HiveQL : View
--------------------------------

 

-- (Before) 중첩 구조를 가지는 복잡한 구조 (complicated multi-layer structure without VIEW)

FROM (
 SELECT *
  FROM table_1 a JOIN table_2 b ON (a.prd_id = b.prd_id)
  WHERE year = '2016' AND month = '06'
) ab
SELECT ab.prd_id, prd_cnt WHERE ab.price >= 10000;

 


-- (After) View를 사용해 간소화한 구조 (simplified structure with VIEW (encapsulation))
CREATE VIEW table_view AS
SELECT *
 FROM table_1 a JOIN table_2 b ON (a.prd_id = b.prd_id)
 WHERE year = '2016' AND month = '06';

SELECT prd_id, prd_cnt FROM table_view
 WHERE price >= 10000;

 


-- 보안을 위해 조건을 기반으로 데이터를 제한하는 view (condition-based view for security)
CREATE TABLE prd_master (prd_id string, prd_nm string, prd_price INT);

CREATE VIEW prd_master_view AS
SELECT prd_id, prd_nm, prd_price FROM prd_master
 WHERE prd_nm = 'ABC';

 

 

 

-- MAP 데이터형과 VIEW

 -- 데이터 형태
ts=1298598378404/code=403/message=bad referrer: bizo.com/...


 -- MAP 테이블 생성
CREATE EXTERNAL TABLE api_logs(d map&lt;string,string&gt;)
  PARTITIONED BY (...)
  ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\004'
    COLLECTION ITEMS TERMINATED BY '\001'
    MAP KEYS TERMINATED BY '\002'
  STORED AS TEXTFILE;


 -- reorder view 생성
CREATE VIEW IF NOT EXISTS api_errors(ts, code, message)
COMMENT 'api error messages'
TBLPROPERTIES ('creator' = 'HongKilDong')
AS SELECT d["ts"], d["code"], d["message"]
    FROM api_logs
    WHERE d["code"] >= 400;

 

 

 

-- VIEW 확인
SHOW TABLES; -- 'SHOW VIEWS;' is NOT WORKING
DESCRIBE api_errors;

 

-- VIEW 삭제
DROP VIEW IF EXISTS api_errors; 
 

Posted by R Friend R_Friend

빅데이터 환경에서 사이즈가 어마무시하게 큰 테이블 간 JOIN은 부하가 매우 크므로 되도록이면 데이터 스키마 설계를 할 때 JOIN을 하지 않고도 분석을 할 수 있도록 요건을 반영하곤 합니다. ARRAY, MAP, STRUCT 등의 컬렉션 데이터형이 정규화를 깨더라도 빅데이터 환경에서 사용되는 이유입니다.

 

하지만, 아무리 스키마 설계를 잘 한다고 해도 분석이 복잡해지면 JOIN을 안하고 모든 것을 처리하기가 힘든 것도 사실입니다.

 

이번 포스팅에서는 두 개 이상의 테이블을 좌, 우로 서로 연결해서 분석할 때 사용하는 Hive 조인 문으로

 

 - INNER JOIN

 - LEFT OUTER JOIN

 - RIGHT OUTER JOIN

 - FULL OUER JOIN

 - CROSS JOIN(CARTESIAN PRODUCT JOIN)

 - MAP-SIDE JOIN  과

 

두 개 이상의 테이블을 상, 하로 서로 붙여서 분석할 때 사용하는

 - UNION ALL

 

문에 대해서 알아보겠습니다.

 

기존에 관계형 DB를 사용했던 분이라면 아마 쉽게 이해할 수 있을 내용들입니다.

 

 

  • 내부 조인 (INNER JOIN)

 

[쿼리 예시]


SELECT a.var1, a.var2, b.var2
    FROM mytable_1 a JOIN mytable_2 b 
    ON a.var1 = b.var1 -- EQUI-JOIN 만 가능하며, NON-EQUI JOIN은 불가
    WHERE a.prd_cd = 'ABC' AND b.prd_cd = 'DEF';

 


SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2 -- 2 JOIN keys
    WHERE a.prd_cd = 'ABC';

 

   

SELECT a.var1, a.var2, b.var3, c.var4
    FROM mytable_1 a JOIN mytable_1 b ON a.var1 = b.var1
                            JOIN mytable_1 c ON a.var1 = c.var1
    WHERE (a.prd_cd = 'ABC' AND b.prd_cd = 'DEF')

              AND (c.prd_cd = 'GHI');

 

 

 

 

  • 왼쪽 세미 조인 (LEFT SEMI-JOIN)

--   : 오른쪽 테이블에서 ON의 술어를 만족하는 레코드를 찾으면 왼쪽 테이블의 레코드를 반환

--   : 내부 조인(INNER JOIN)을 최적화한 특수한 형태로, 내부 조인보다 빠름

--   : 왼쪽 세미 조인만 지원하며, 오른쪽 세미 조인은 미지원

 

SELECT a.var1, a.var2, a.var3
    FROM mytable_1 a LEFT SEMI JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2;

 

 

 

  • 왼쪽 외부 조인 (LEFT OUTER JOIN)

 

[ 쿼리 예시 ]

 

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a LEFT OUTER JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

 

 

 

  • 오른쪽 외부 조인 (RIGHT OUTER JOIN)

 

[ 쿼리 예시 ]

 

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a RIGHT OUTER JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

 

 

 

  • 완전 외부 조인 (FULL OUTER JOIN)

 

[ 쿼리 예시 ]

 

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a FULL OUTER JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

 

 

 

  • 교차 조인 (CROSS JOIN (CARTESIAN PRODUCT JOIN))

 

[ 쿼리 예시]

 

SELECT * FROM mytable_1 JOIN mytable_2;

 

 

쿼리 문이 JOIN 문 중에서 제일 간단합니다.  그런데 속도는 제일 느리고 또 위험하기도 합니다. ^^;

 

A*B 개의 ROW를 반환(가령 A 테이블이 100개 row, B 테이블이 1000개 row를 가지고 있다면 A JOIN B 는 100 * 1,000 = 100,000 개 row 출력)하므로 출력량도 제일 많습니다. 뿐만 아니라 다른 JOIN문과 달리 CORSS JOIN (CARTESIAN PRODUCT JOIN)은 병렬처리도 안되므로 느리기까지 합니다.

 

SELECT 문을 가지고 시스템을 다운시키기가 쉽지 않은데요, SELECT 문으로 혹시 HIVE가 다운되거나 성능이 현격히 저하되었다면 JOIN 걸 때 ON, WHERE 절을 빼먹고 CROSS JOIN (Cartesian Product JOIN)을 실수로 쿼리를 날린게 아닌가 하고 범인 후보로 의심을 해볼 수 있습니다.

 

CROSS JOIN 걸 때는 '내가 원하는 output/분석 목적이 CROSS JOIN 맞나?'를 재차 확인하고 쿼리를 날리기 바랍니다.  특히 'BIG~ 데이터' 환경에서는 말이지요.

 

 

 

  • Map-Side JOIN

 

[ 쿼리 예시 ]

 

hive> set hive.auto.convert.join=true; -- default is false

 

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a JOIN mytable_2 b -- 큰 테이블은 왼쪽, 크기가 작은 테이블은 오른쪽!
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

 

 

 

맵 사이드 조인을 하려면 먼저 set hive.auto.convert.join=true; 로 설정을 바꿔줘야 합니다.

맵 사이드 조인은 이름에서 알 수 있는 것처럼, 크기가 작은 테이블을 메모리에 캐시하고, 큰 테이블은 맵퍼로 보내 후에, 캐시한 작은 테이블로부터 일치하는 모든 것을 찾아낼 수 있기 때문에 맵에서 모든 조인을 할 수 있습니다. 

  => 일반 JOIN 과 달리 리듀스 단계를 건너뛸 수 있어서 속도가 빠른 장점이 있습니다. 성능 최적화를 위해 알아두면 좋은 JOIN 방법입니다.  

 

 

이상으로 JOIN 문을 마칩니다.

 


 

 

두 개 이상의 테이블을 합칠 때 사용하는 UNION ALL 입니다.

  • UNION ALL

 

-- 두 개 이상의 테이블을 위, 아래로 합치기
-- subquery 는 같은 수의 칼럼, 각 칼럼의 Data Type 동일해야 함

 

[ 쿼리 예시 ]

 

SELECT a.var1, a.var2, a.var3
    FROM (
        SELECT a1.var1, a1.var2, a1.var3, 'A1' as source
        FROM mytable a1
    UNION ALL
        SELECT a2.var1, a2.var2, a2.var3, 'A2' as source
        FROM mytable a2
    ) a
SORT BY a.var1 ASC, a.var2 DESC;

 

 

이상으로 HIVE에서 테이블 연결하거나 합쳐서 분석할 때 사용하는 JOIN문, UNION ALL에 대해서 알아보았습니다.

 

다음번 포스팅에서는 하이브 뷰(VIEW)에 대해서 알아보겠습니다.

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

 

 

Posted by R Friend R_Friend

이번 포스팅에서는 HiveQL의 

 

 - (1) 중첩 SELECT 문

 

 - (2) LIKE

       : substring 문자열 일치 여부 확인

 

 - (3) CASE ... WHEN ... THEN ... ELSE ... END

       : 범주형 변수 생성

 

 - (4) GROUP BY, HAVING 절

       : 집계 (aggregation), 그룹 내 조건절(HAVING)

 

 - (5) ORDER BY

       : 전체 정렬 (total ordering)

 

 - (6) SORT BY, DISTRIBUTE BY 

       : 부분 정렬 (local ordering)

 

 

에 대해서 알아보겠습니다.

 

 

 

 

--------------------------------
-- HiveQL : 중첩 SELECT 문
--------------------------------

 

FROM (
    SELECT var_1, var_2, (var_1*var_2) AS var_1_2_multi 
    FROM my_table
) a
SELECT a.var_1, a.var_2
    WHERE a.var_1_2_multi > 100

    LIMIT 10; -- 반환하는 행 상한치 10개로 제한

 

 

 

---------------------------------------------------
-- HiveQL : LIKE
---------------------------------------------------
-- substring 문자열 일치 여부 확인

 

SELECT var_1, var_2, var_3
    FROM my_table
    WHERE var_2 LIKE '%CC'; -- var_2에서 'CC'로 끝나는 문자열이 들어있는 row 선택
 
 
SELECT var_1, var_2, var_3
    FROM my_table
    WHERE var_2 LIKE 'AA%'; -- var_2에서 'AA'로 시작하는 문자열이 들어있는 row 선택
 
 
SELECT var_1, var_2, var_3
    FROM my_table
    WHERE var_2 LIKE '%BB%'; -- var_2에서 'BB'를 포함하는 문자열이 들어있는 row 선택

 

 

 

--------------------------------------------------------------
-- HiveQL : CASE ... WHEN ... THEN ... ELSE ... END

--------------------------------------------------------------

-- 범주형 변수 생성

 

SELECT var_1, var_2, 
    CASE
        WHEN var_3 < 100.0 THEN 'L'
        WHEN var_3 >= 100 AND var_3 < 1000 THEN 'M'
        ELSE 'H'
    END AS var_3_cd
    FROM my_table;

 

 

 

-----------------------------------------
-- HiveQL : GROUP BY, HAVING
-----------------------------------------

-- 집계 (aggregation), 그룹 내 조건절(HAVING)

 

SELECT prd_cd, max(prd_price) AS prd_max_price
    FROM prd_tr
    WHERE prd_cd = 'ABC'
    GROUP BY prd_cd -- aggregation by group
    HAVING max(prd_price) >= 10000; -- GROUP BY에 의해 생성된 그룹에 대한 SUBQUERY
 

 

 

---------------------------------------------------------
-- HiveQL : ORDER BY
---------------------------------------------------------

-- => 쿼리 결과 집합에 대한 전체 정렬(TOTAL ORDERING), 오랜 시간 소요

SELECT prd_cd, prd_id, sum(prd_rev) AS prd_rev_sum
    FROM prd_tr
    WHERE prd_cd IN ('ABC', 'DEF', 'GHI')
    GROUP BY prd_cd, prd_id
    ORDER BY prd_cd ASC, prd_id DESC; -- total ordering, ASC 오름차순, DESC 내림차순


 


---------------------------------------------------
-- HiveQL : SORT BY, DISTRIBUTE BY
---------------------------------------------------

-- => 각 REDUCER에서 데이터 정렬(LOCAL ORDERING), 상대적으로 빠름

SELECT a.prd_cd, a.prd_id, sum(a.prd_price) AS prd_rev_sum
    FROM prd_tr a
    WHERE prd_cd IN ('ABC', 'DEF', 'GHI')
    DISTRIBUTE BY a.prd_cd -- 같은 prd_cd 를 가진 데이터를 같은 reducer로 보냄
    GROUP BY a.prd_cd, a.prd_id
    SORT BY a.prd_cd ASC, a.prd_id DESC; -- local ordering

 

다음번 포스팅에서는 테이블 JOIN에 대해서 알아보겠습니다.

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

 

 

Posted by R Friend R_Friend

이번 포스팅에서는 Hive에 내장되어 있는

 

 - (1) 내장함수(Hive built-in functions)

 

 - (2) 수학함수(Hive arithmetic functions)

 

 - (3) 집계함수(Hive aggregate functions)

 

에 대해서 알아보겠습니다.

 

기본 SELECT ... FROM ... 에 더해서 지난번 포스팅에서 다루었던 연산자와 함께 이번 포스팅에서 다룰 다양한 함수를 잘 알고 있으면 "Hive 좀 하네" 소리 들을 수 있을 겁니다. 

 

다행스러운 것은 SQL에서 사용하는 함수와 매우 비슷하다는 점입니다. ( <= 이점이 Hive 진입 장벽을 낮추어서 Hive가 많이 사용되는 이유이지요)

 

자주 쓰는 함수 외에는 다 외우기 힘들 수 있으므로, 필요할 때마다 검색해서 사용하시기 바랍니다.

 

 

 

 

먼저 다양한 내장 함수 먼저 살펴보겠습니다.

 

 

(1) 내장 함수 (Hive Built-in functions)

 

반환 데이터형

내장 함수 

설명 

 BIGINT

round(double a)

double 데이터형 a의 반올림한 값을

BIGINT 데이터형으로 반환

예) round(4.8) = 5

 DOUBLE

round(double a, N) 

double 데이터형 a의 N개 소수점 이하의 값까지를

반올림하여 double 데이터형으로 반환

예) round(4.816, 2) = 4.82

 DOUBLE

floor(double a)

double 데이터형 a보다 같거나 작은 값 중

가장 큰 BIGINT 값을 반환

예) floor(4.8) = 4

 BIGINT

ceil(double a)

double 데이터형 a보다 크거나 같은 값 중

가장 작은 BIGINT 값을 반환

예) ceil(4.8) = 5

 DOUBLE

rand(),

rand(int seed)

행에서 행으로 변하는 무작위 숫자를 반환

 string

concat(string A,

string B, ...)

string A와 string B를 연속으로 붙여서 반환

 string

concat_ws(seperator, string A, string B, ...) 

string A와 string B를 구분자(seperator)를 두어서

연속으로 붙여서 반환

 string

substr(string A,

int start)

string A를 시작 색인 지점부터 끝까지 잘라서 반환.

* 색인은 1부터 시작함.

예) substr('20160625', 5) = '0625'

 string

substr(string A,

int start, int length)

string A를 시작 색인 지점부터 int length만큼 잘라서 반환.

* 색인은 1부터 시작함.

예) substr('20160625', 5, 2) = '06'

 ARRAY

<string>

split(string A, pattern)

pattern이 나타날 때마다 string A를 분할하여

문자열의 배열을 반환.

 string

repeat(string A, n) 

 string A를 n회 반복하여 반환.

 string

upper(string A)

or ucase(string A)

string A의 모든 문자를 대문자로 반환.

예) upper('Love') = 'LOVE'

 string

lower(string A)

or lcase(string A)

string A의 모든 문자를 소문자로 반환.

예) lower('Love') = 'love'

 string

trim(string A)

string A의 양끝의 공백을 제거하여 반환.

예) trim('  Love  ') = 'Love'

 string

ltrim(string A)

string A의 왼쪽 시작부분의 공백을 제거하여 반환.

예) ltrim('  Love  ') = 'Love  '

 string

rtrim(string A)

string A의 오른쪽 끝부분의 공백을 제거하여 반환.

예) rtrim('  Love  ') = Love'

 string space(n) 

n개의 공백을 반환.

 string

regexp_replace(string A, string B, string C)

string A에서 string B의 부분 문자열(substring)과 일치하는

Java 정규표현식을 string C로 교체하여 반환.

예) regexp_replace('Love', 'oe', 'i') => returns 'Livi'

 int length(string A) 

string A의 길이를 반환.

예) length('Love') = 4

 int

size(Map<K.V>)

Map<K.V> 의 요소 개수를 반환.

 int

size(Array<T>)

Array<T> 의 요소 개수를 반환.

value of <type>

cast(<expr> as <type>)

표현 <expr>을 <type>으로 형변환하며,

변환에 실패하면 NULL을 반환함.

예) cast('1' as BIGINT) => 문자열 '1'을 BIGINT로 형변환

 string

from_unixtime(int unixtime)

Unix 기준시간(1970-01-01 00:00:00 UTC)을

=> 현재 시스템 시간대의 초단위 시간 표현식 포맷

     ("1970-01-01 00:00:00")으로 변환.

 string

to_date(string timestamp)

timestamp 문자열에서 년/월/날짜를 반환.

예) to_date("1970-01-01 00:00:00") = "1970-01-01"

 int

year(string date)

timestamp 문자열에서 년(year)을 반환.

예) year("1970-01-01 00:00:00") = 1970,

     year("1970-01-01") = 1970

 int

month(string date)

timestamp 문자열에서 월(month)을 반환.

예) month("1970-11-01 00:00:00") = 11,

     month("1970-11-01") = 11

 int

day(string date)

timestamp 문자열에서 일(day)을 반환.

예) day("1970-11-01 00:00:00") = 1,

     day("1970-11-01") = 1

 string

get_json_object(string json_string, string path)

json string으로부터 json 객체를 추출하며,

추출한 json 객체로부터 json string을 반환함.

json string이 유효하지 않으면 NULL을 반환함.

 MAP<string, string>

str_to_map(string A, delim1, delim2)

delim1을 Key-Value 쌍의 구분자로 사용하고 

delim2를 Key와 Value의 구분자로 사용하여

string A를 parsing 한 후에 Map을 생성함.

 ARRAY<ARRAY

<string>>

sentences(string A, lang, locale)

string A를 단어의 배열로 이루어진 문장의 배열로 반환.

lang, locale 옵션을 설정하지 않을 경우 기본 locale 사용.

 BOOLEAN

in_file(string A,

file B) 

filen B에 string A가 들어 있으면 TRUE를 반환.

 

 

 

(2) 수학 함수 (Hive Arithmetic functions)

 

아래의 수학 함수들의 full name을 알고 있는 분들이라면 축약어 형식의 함수이름을 보고 '아, 이거 뭐겠구나...'하고 짐작할 수 있을 것입니다.

 

 반환 데이터형

수학함수

설명 

 DOUBLE

exp(double A) 

 double A의 지수(exponential) 함수값을 반환

 DOUBLE

ln(double A) 

 double A의 자연로그(natural logarithm)값을 반환

* 자연로그 : e로 밑으로 하는 로그 (e=2.71828182846)

 DOUBLE

log10(double A) 

 double A의 상용로그(common logarithm) 값을 반환

* 상용로그 : 10을 밑으로 하는 로그

 DOUBLE

log2(double A) 

 double A의 밑을 2로 하는 로그값을 반환

 DOUBLE

log(base, double A) 

 double A의 밑을 base로 하는 로그값을 반환

(base는 double 데이터형)

 DOUBLE

pow(double A, p)

or

power(double A, p)

 double A의 p 거듭제곱(power)한 값을 반환

(p는 double 데이터형)

 DOUBLE

sqrt(double A)

 double A의 제곱근(square root)을 반환

 string

bin(BIGINT i)

 BIGINT i 의 이진수(binary)값을 문자열로 반환

 string

hex(BIGINT i) 

 BIGINT i 의 16진수(hexadecimal)값을 문자열로 반환

 string

hex(string S) 

 string S의 각 문자를 16진수값으로 변환하여 문자열로 반환

 string unhex(i) 

 hex(string S) 함수와 반대로 동작

 string

conv(BIGINT i, from_base, to_base)

 BIGINT i 를 from_base 진수에서 to_base 진수로 변환하여

문자열로 반환

(from_base와 to_base는 정수형)

 string

conv(string S, from_base, to_base) 

 string S 를 from_base 진수에서 to_base 진수로 변환하여

문자열로 반환

(from_base와 to_base는 정수형)

 DOUBLE

abs(double A

 double A의 절대값(absolute value)을

DOUBLE 데이터형으로 반환

 INT

pmod(INT 1, INT 2) 

 INT 1을 INT 2로 모듈러 연산(modular arithmetic)한 후

양수(positive) INT 값을 반환 (음이 아닌 나머지를 반환)

 DOUBLE pmod(double 1, double 2

 double 1을 double 2로 모듈러 연산한 후

양수 DOUBLE 값을 반환

 DOUBLE

sin(double A) 

 double A의 sine 값을

DOUBLE 데이터형의 radian 값으로 반환

 

* 1 radian : 원둘레 위에서 반지름의 길이(r)와 같은 길이를 가지는 호(s)에 대응하는 중심각의 크기, 약 57.2958˚)

 DOUBLE

asin(double A) 

 double A의 arc-sine 값을

DOUBLE 데이터형의 radian 값으로 반환

 

* arc-sine : sine 함수의 역함수(inverse trigonometric function)

 DOUBLE cos(double A) 

 double A의 cosine 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE acos(double A) 

 double A의 arc-cosine 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE

tan(double A) 

 double A의 tangent 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE atan(double A) 

 double A의 arc-tangent 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE

degrees(double A) 

 double A radian 값을 각도로 변환하여 반환

 DOUBLE radians(double A) 

 double A 각도 값을 radian 값으로 변환하여 반환

 INT positive(INT i) 

 INT i의 양의 INT 데이터형 값을 반환

 INT

negative(INT i) 

 INT i의 음의 INT 데이터형 값을 반환

 DOUBLE positive(double A) 

 double A의 양의 DOUBLE 데이터형 값을 반환

 DOUBLE negative(double A)   double A의 음의 DOUBLE 데이터형 값을 반환

 FLOAT

sign(double A) 

 double A가 양수이면 부동소수점형 1.0을 반환,

                음수이면 부동소수점형 -1.0을 반환,

                모두 아니면 부동소수점형 0.0을 반환

 DOUBLE e() 

 지수형 상수값 2.718281828459045를

DOUBLE 데이터형으로 반환

 DOUBLE pi 

 파이 상수값 3.141592653589793을

DOUBLE 데이터형으로 반환

 

 

 

 

(3) 집계 함수 (Hive Aggregate functions)

 

특정 열을 기준으로 그룹 별로 행의 값들을 요약, 집계해서 하나의 값으로 반환해주는 함수가 집계 함수입니다.  GRUOP BY 와 함께 쓰여서

 

hive > SET hive.map.aggr = true; -- 집계 처리 성능 향상

 

SELECT count(DISTINCT var_1) as var_1_dist_cnt, avg(var_2) as var_2_avg 

FROM my_table 

GROUP BY var_group

 

형식으로 아주 아주 아주 많이 사용하는 함수들입니다.  R 학습할 때 초반부에 기초 요약통계량에서 다루는 함수들에 해당합니다.

 

 반환 데이터형

집계 함수

설명 

 BIGINT

count(*)

NULL값을 포함한 행의 총 개수를 반환

 BIGINT

count(expr)

주어진 표현식이 NULL이 아닌 행의 개수를 반환

 BIGINT

count(DISTINCT expr[, expr_.]) 

주어진 표현식의 값이 유일하면서 NULL이 아닌

행의 개수를 반환

 DOUBLE

sum(col)

그룹 내 요소들의 합을 반환

 

 DOUBLE

sum(DISTINCT col)

그룹 내 열의 고유한 값들의 합을 반환

 DOUBLE

avg(col)

그룹 내 요소들의 평균을 반환

 DOUBLE avg(DISTINCT col)

그룹 내 열의 고유한 값들의 평균을 반환

 DOUBLE

min(col)

그룹 내 열의 최소값을 반환

 DOUBLE

max(col)

그룹 내 열의 최대값을 반환

 DOUBLE

variance(col)

or var_pop(col)

특정 열의 모집단 분산을 반환

 DOUBLE

var_samp(col)

특정 열의 표본 분산을 반환

 DOUBLE

stddev_pop(col)

특정 열의 모집단 표준편차를 반환

 DOUBLE

stddev_samp(col)

특정 열의 표본 표준편차를 반환

 DOUBLE

covar_pop(col1, col2)

두개 열(col1, col2)의 공분산을 반환

 DOUBLE

covar_samp(col1, col2)

두개 열(col1, col2)의 표본 공분산을 반환

 DOUBLE

corr(col1, col2)

두개 열(col1, col2)의 상관계수를 반환

 DOUBLE

percentile(int_expr, p)

p percent에 해당하는 int_expr 백분위수 값을 반환.

p는 0과 1사이의 DOUBLE 데이터형 값임.  

예) p가 0.25이면 전체 값을 크기 순으로 오름차순 정렬하였을 때 25%에 해당하는 값을 반환

 

참고) 백분위수(percentile)는 총값의 백분율이 해당 값보다

        작거나 같은 측정치임

 ARRAY(DOUBLE)

percentile(int_expr, [p1, ...])

p1, p2, ... percent에 해당하는 int_expr 백분위수 값을

ARRAY 데이터형으로 반환함

 DOUBLE

percentile_approx(int_expr, p, NB)

p percent에 해당하는 int_expr 의 근사 백분위수 값을 반환.

NB는 추정을 위한 Histogram bins 의 수임

(NB default 값 : 10,000)

 DOUBLE

percentile_approx

(int_expr, [p1, ...], NB) 

p1, p2, ... percent에 해당하는 int_expr 의

근사 백분위수 값을 반환

 

 

 

(4) 테이블 생성 함수 (Hive table creation functions)

 

 반환 데이터형

테이블 생성 함수 

설명 

 N개의 row

explode(ARRAY)

 ARRAY 의 각 요소별로 한 개의 행(row)을 만들며,

0개 이상의 row를 반환

 N개의 row

explode(MAP)

 MAP의 Key와 Value에 대해 각각 하나의 열(field)을 생성.

각 Key-Value 쌍에 대해 하나의 행(row)이 생성되며,

0개 이상의 row를 반환

 TUPLE

json_tuple(jsonStr,
p1, p2, ..., pn)

 get_json_object 와 같은 기능,

반면 여러 이름을 받아들이고 하나의 tuple을 반환.

(모든 입력 인자와 출력 행의 데이터형은 문자열)

 TUPLE

N>=1 일 때 parse_url_tuple(url, part name1,

part name2, ..,

part nameN)

 한 URL로부터 N개의 part를 추출하고,

1, 2, ..., N개의 part 이름을 부여함.

 

(모든 입력 인자와 출력 행의 데이터형은 문자열.

유효한 part 이름은 대소문자를 구분, 공백은 하나만 포함)

 N개의 row

stack(n, col1, ..., colM)

 M개의 행을 N개의 열로 반환.

각 행은 M/N의 크기를 가짐.

 

[예제]

SELECT parse_rul_tuple(url, 'HOST', 'PATH', 'QUERY') as (host, path, query)

FROM url_table;

 

 

[Reference]

1) Programing Hive, Edward Capriolo, Dean Wampler, Jason Rutherglen, O'REILLY

2) http://www.tutorialspoint.com/hive/hive_built_in_functions.htm

 

 

다음 포스팅에서는 중첩 SELECT 문, LIKE, CASE... WHEN ... THEN 문, GROUP BY, HAVING절, ORDER BY, SORT BY, DISTRIBUTE BY에 대해서 알아보겠습니다.

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요. ^^

 

 

 

Posted by R Friend R_Friend

 

 

이번 포스팅에서는 Hive 에 내장되어 있는

 

 - (1) 프로젝션 연산자 SELECT ... FROM 

 

 - (2) 비교 연산자 (Relational operators)

 

 - (3) 산술 연산자 (Arithmetic operators)

 

 - (4) 논리 연산자 (Logical operators)

 

 - (5) 복합 연산자 (Complex operators)

 

에 대해서 알아보겠습니다.

 

 

[ HiveQL Operators : 프로젝션 연산자, 비교 연산자, 산술 연산자, 논리 연산자, 복합 연산자 ]

 

 

 

 

(1) 프로젝션 연산자 SELECT ... FROM

 

아래와 같은 테이블을 생성했다고 했을 때,

 

CREATE TABLE IF NOT EXISTS mydb.my_table (
    var_1 STRING COMMENT 'var_1 is this',
    var_2 FLOAT COMMENT 'var_2 is that',
    var_3 INT COMMENT 'var_3 is those',
    var_4 ARRAY<STRING> COMMENT 'arrary is...',
    var_5 MAP<STRING, FLOAT> COMMENT 'map is...',
    var_6 STRUCT<aa:STRING, bb:FLOAT, cc:INT> COMMENT 'structure is...'
COMMENT 'my_table in mydb'
TBLPROPERTIES ('creator'='HongKilDong', 'data'='2016-06-17', ...)
--LOCATION '/user/hive/warehouse/mydb.db/my_table'
)
PARTITIONED BY (year INT, month INT, day INT);

 

 

 

-- SELECT var_nm FROM table

SELECT var_1, var_2 FROM my_table;

 

 

-- ARRAY : ["aaaaaaa", "bbbbbbbb"]

SELECT var_4 FROM my_table;

 

 

-- MAP : {"key1":xx, "key2":yy}

SELECT var_5 FROM my_table;

 

 

-- STRUCT : JSON map {"aa":"xxx", "bb":yy.y, "cc":zzz}
SELECT var_6 FROM my_table;

 

 

 

(2) HIVE 비교 연산자 (Relational Operators)

 

연산자

데이터형

설명 

 A = B

all primitive types

 A와  B가 같으면 TRUE, 그렇지 않으면 FALSE

A != B

all primitive types

 A와 B가 같지 않으면 TRUE, 그렇지 않으면 FALSE

A < B

all primitive types

 A가 B보다 작으면 TRUE, 그렇지 않으면 FALSE

A <= B

all primitive types

 A가 B보다 작거나 같으면 TRUE, 그렇지 않으면 FALSE

A > B

all primitive types

 A가 B보다 크면 TRUE, 그렇지 않으면 FALSE

A >= B

all primitive types

 A가 B보다 크거나 같은면 TRUE, 그렇지 않으면 FALSE

A IS NULL

all types

 A가 NULL 이면 TRUE, 그렇지 않으면 FALSE

A IS NOT NULL

all types

 A가 NULL이 아니면 TRUE, 그렇지 않으면 FALSE

A LIKE B

Strings

 String 패턴 A가 B와 동일하면 TRUE, 그렇지 않으면 FALSE

A RLIKE B

(A REGEXP B)

Strings

 A 또는 B가 NULL이면 NULL,

 A의 어떤 substring이라도 B와 동일하면 TRUE,

 그렇지 않으면 FALSE

 

 

 

-- Retrieve details from DB using relational operators

SELECT * FROM my_table WHERE var_1='HongKilDong';
SELECT * FROM my_table WHERE var_3>=100;
SELECT * FROM my_table WHERE BETWEEN var_2 >= 0.1 and var_2 <= 2.0;
SELECT * FROM my_table WHERE var_1 IS NOT NULL;

 

 

 

(3) HIVE 산술 연산자 (Arithmetic Operators)

 

 연산자

데이터형 

설명 

 A + B

all number types

 더하기 (adding A and B)

 A - B

all number types 

 빼기 (substracting B from A)

 A*B

all number types

 곱하기 (multiplying A and B)

 A/B

all number types

 나누기 (dividing B from A)

 A%B

all number types

 나눈 후의 나머지 반환

 (reminder resulting from diving A by B)

A&B 

all number types

 비트 AND 연산 (bitwise AND of A and B)

A|B 

all number types

 비트 OR 연산 (bitwise OR of A and B)

A^B 

all number types

 비트 XOR 연산 (bitwise XOR of A and B)

~A 

all number types

 비트 NOT 연산 (bitwise NOT of A)

 

 

 

-- 산술 연산자

SELECT (var_1 + var_2) as var_1_2_sum FROM my_table;

SELECT var_1*var_2 as var_1_2_multi FROM my_table;

 

 

 

(4) HIVE 논리 연산자 (Logical Operators)

 

 연산자

데이터형

설명 

 A AND B

(A && B)

boolean 

 A와 B 모두 TRUE이면 TRUE, 그렇지 않으면 FALSE

 A OR B

(A || B)

boolean

 A 또는 B, 또는 A와 B가 모두 TRUE이면 TRUE,

 그렇지 않으면 FALSE

 NOT A

(!A)

boolean

 A가 FALSE이면 TRUE, 그렇지 않으면 FALSE

 

 

-- 논리 연산자

 

SELECT * FROM my_table WHERE var_3>=100 && var_2 <= 2.0;

 

 

 

 

(5) HIVE 복합 연산자 (Complex Operators)

 

 연산자

데이터형

설명 

 A[n]

A : Array

n : int 

 Array A에서 n번째 값을 출력.

 첫번째 값은 index 0 부터 시작함.

 (* 참고 : R은 index 1 부터 시작함)

 M[key]

 M : Map<K, V>

Key has type K

 Key에 해당하는 Value를 출력함

 S.x

S : STRUCT

 STRUCT의 x field를 출력함

 

 


-- subset selection from ARRAY : [index number]

SELECT var_1, var_4[0] FROM my_table; -- index starts from '0' (not from '1')

 

 

-- subset selection from MAP : ["key"]

SELECT var_1, var_5["key1"] FROM my_table;

 

 

-- subset selection from STRUCT : dot notation

SELECT var_1, var_6.aa FROM my_table;

 

 

다음 포스팅에서는 Hive에 내장되어 있는 수학 함수와 집계함수에 대해서 알아보겠습니다.

 

[Reference]

1) Programing Hive, Edward Capriolo, Dean Wampler, Jason Rutherglen, O'REILLY

2) http://www.tutorialspoint.com/hive/hive_built_in_operators.htm

 

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

 

 

Posted by R Friend R_Friend

이번 포스팅에서는

 

 - (1) 테이블에 데이터를 넣기 (data loading into table)

 - (2) 쿼리 결과를 테이블에 넣기 (data loading from query result)

 - (3) 동적으로 파티션 해서 데이터 넣기

 - (4) 테이블 생성 & 데이터 로딩 한번에 하기

 - (5) 데이터를 Local로 내보내기

 - (6) 데이터를 동시에 다수의 디렉터리로 로딩하기

 

등에 사용하는 Hive 데이터 조작 언어 (HiveQL Data Manipulation Language)에 대해서 알아보겠습니다.

 

 

 

 

-- 데이터 로딩 (Data loading into managed table)
LOAD DATA LOCAL INPATH '${env:HOME}/2017-06'
OVERWRITE INTO TABLE my_table
PARTITION (year = '2016', month = '6');

 

 


-- 쿼리 결과로 부터 데이터 로딩 (inserting data from query result)


    -- INTO : adding data
INSERT INTO TABLE my_table
PARTITION (year = '2016', month = '6')
SELECT * FROM another_table a
WHERE a.year = '2016' AND a.month = '6';

 


    -- OVERWRITE : replacing data
INSERT OVERWRITE TABLE my_table
PARTITION (year = '2016', month = '6')
SELECT * FROM another_table a
WHERE a.year = '2016' AND a.month = '6';

 


    -- reading data once and replacing data at several tables
FROM another_table a
INSERT OVERWRITE TABLE my_table
    PARTITION (year = '2016', month = '6')
    SELECT * WHERE a.year = '2016' and a.month = '6'
INSERT OVERWRITE TABLE my_table
    PARTITION (year = '2016', month = '7')
    SELECT * WHERE a.year = '2016' and a.month = '7'
INSERT OVERWRITE TABLE my_table
    PARTITION (year = '2016', month = '8')
    SELECT * WHERE a.year = '2016' and a.month = '8';

 


    -- dynamic partition insert
set hive.exec.dynamic.partition = true; -- to use dynamic partition
set hive.exec.dynamic.partition.mode = nonstrict; -- for all partition column
set hive.exec.max.dynamic.partition.pernode = 500; -- maximum file number

INSERT OVERWRITE TABLE my_table
PARTITION (year, month)
SELECT ..., a.year, a.month
FROM another_table a;

 


    -- partial dynamic partition insert
INSERT OVERWRITE TABLE my_table
PARTITION (year = '2016', month) -- static partition key first, dynamic partition key second
SELECT ..., a.year, a.month
FROM another_table
WHERE a.year = '2016'

 

 


-- 테이블 생성 & 데이터 로딩 한번에 하기 (** 분석 MART 구성 시 유용 **)
CREATE TABLE my_table_201606  -- table creation
AS SELECT var_1, var_2, var_5 -- and data selection simultaneously
FROM my_table
WHERE year = '2016' and month = '6';

 

 

 

-- 데이터를 Local로 내보내기

 

 -- (1) 데이터 파일 복사
hadoop fs -cp source_path target_path -- copy
hive> !ls /tmp/my_table_201606; -- file check
hive> !cat /tmp/my_table_201606/000000_0; -- data check


 

 -- (2) INSERT OVERWRITE LOCAL DIRECTORY ...
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/my_table_201606'
SELECT var_1, var_2, var_5
FROM my_table
WHERE year = '2016' and month = '6';

 

 

 

-- 데이터를 동시에 다수의 디렉터리로 로딩
FROM my_table a
INSERT OVERWRITE DIRECTORY '/tmp/my_table_201606'
     SELECT * WHERE year = '2016' and month = '6'
INSERT OVERWRITE DIRECTORY '/tmp/my_table_201607'
     SELECT * WHERE year = '2016' and month ='7'
INSERT OVERWRITE DIRECTORY '/tmp/my_table_201608'
     SELECT * WHERE year = '2016' and month = '8';


 [Reference]

- Programing Hive, Edward Capriolo, Dean Wampler, Jason Rutherglen, O'REILLY

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

 

Posted by R Friend R_Friend