R 과 BigQuery 연계 (bigrquery, DBI)

본 포스팅에서는 R 과 BigQuery를 연계하는 방법을 다룬다.

구체적으로, R 에서 함수를 호출해 BigQuery에 쿼리를 날리고
해당 쿼리 결과 값을 로컬에 있는 R 로 가져와
dataframe object로 저장하기까지의 과정을 다룬다.

이를 위해 사용되는 R 패키지는 DBI, bigrquery, dbplyr 총 3가지다.
각 패키지의 역할은 간략하게 다음과 같다.

1. DBI
BigQuery를 비롯한 포괄적인 R/DBMS database 와의 인터페이스를 제공하는 패키지

2. bigrquery
BigQuery에 특정된 인터페이스를 취급하는 패키지

3. dbplyr
dplyr를 SQL 로 번역해주는 역할을 하는 패키지. 데이터 조작을 위해 필요하다.

아래 코드에서는 이해를 목적으로, 함수 별로 앞에 ‘패키지명’:: 을 명시함으로써 구분을 해 놓았다.
예외 적으로, dbplyr 의 경우는 명시하지 않았고 다만 ‘%>%’ 가 포함된 코드는 dbplyr 가 사용된 것이다.

#패키지 설치 및 로드

install.packages("bigrquery")
install.packages("DBI")
install.packages("dbplyr")

library(bigrquery)
library(DBI)
library(dbplyr)

##연결 셋팅

#요금 청구 대상이 되는 프로젝트 ID 입력
billing <- "project id"
#쿼리하고자 하는 프로젝트 ID 입력
projectName <- "project id"
#쿼리하고자 하는 데이터셋 입력
datasetName <- "dataset"

con <- DBI::dbConnect(
  bigrquery::bigquery(),
  project = "project id",
  dataset = "dataset",
  billing = billing
)

ds <- bigrquery::bq_dataset(projectName , datasetName)

#인증
bigrquery::bq_auth()

#사용하고자 하는 sql문 입력, 이때 syntax는 BigQuery
sql_tmp <- paste0("SELECT column
                  FROM `project id.`.dataset.table;")

#sql 호출
tb <- bigrquery::bq_dataset_query(ds,
                       query = sql_tmp,
                       billing = billing,
                       quiet = NA)

#테이블을 dataframe 형태로 다운로드
tb_df <- bigrquery::bq_table_download(tb) %>% as.data.frame()

Reference
https://cloud.google.com/architecture/data-science-with-r-on-gcp-eda

PostgreSQL 다른 user로 접속 설정 (Windows 10)

1. pg_hba.conf 파일 수정
경로 : path\PostgreSQL\11\data
수정 내용 : 아래 레코드 추가 (빨간색)postgresql pghbaconfig.png

*구체적인 METHOD 에 대해서는 다음 링크 참조

 

2. user 생성 (pgAdmin4)

pgAdmin4 > Tree Control > Server > Login/Group Roles > Create

postgresql create user.png

user 정보 입력
postgresql create user def

*user password 변경 필요 시 다음 쿼리 사용
ALTER USER user_name WITH PASSWORD ‘password‘;

 

3. 고정 IP 설정
시작 > cmd > ipconfig/all
위 경로에서 보여지는 IP 관련 정보를 아래에 입력
네트워크 및 인터넷 > 설정 > 속성 > 수동

 

참고
https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

 

PostgreSQL Data type

  1. Boolean
    1. True
    2. False
    3. NULL
  2. Character
    1. Char(n)
    2. Varchar(n)
    3. Text : 문자길이 제한이 없음
  3. Number
    1. Integers
      1. Small integer (smallint)
      2. Integer (int)
      3. Serial
    2. Floating-point number
      1. Float(n)
      2. Real or Float8
      3. Numeric or Numeric(p,s)
  4. Temporal Data Type
    1. Date : date value only
    2. Time : day value
    3. Timestamp : date + time
    4. Interval : periods of time
    5. Timestamptz : timestamp + time zone data
  5. Special Data Type
    1. Box : a rectangular box
    2. Line : a set of points
    3. Point :  a geometric pair of numbers
    4. Lseg : a line segment
    5. Polygon : a closed geometric
    6. Inet : an IP4 address
    7. Macaddr :a  MAC address
  6. Arrays Data Type

 

출처 : https://www.youtube.com/watch?v=aUfPf-clLLs

DB 서버가 해외에 있을 때 발생 가능한 실수

2018년 11월 기준, 국내 외산 데이터센터 현황은 다음과 같다.

2016년 1월 (설립) – AWS
2017년 2월 (설립) – MS
2019년 5월 (예정) – Oracle
2019년 중   (추진) – Google

외산 클라우드 솔루션을 사용 시 데이터센터가 해외에 위치할 경우,
쿼리 과정에서 식별한 (사소하지만 critical한) 문제가 있어 공유한다.

타겟팅 추출하는 과정에서 현 시간 날짜 속성을 자주 사용한다.
가령 ‘최근 2주’라는 조건이 있다고 가정해보자.

참고사항으로
1. 데이터센터의 소재지는 한국과 16시간 차이나는 미국이며
2. 클라우드 솔루션의 VM이 데이터센터와 같은 곳에 위치해있다.
3. 달력을 보니 오늘 날짜가 2018년 5월 1일이다.

이 경우 쿼리는 다음과 같이 구성할 수 있다.

WHERE …. 날짜컬럼 BETWEEN SYSTIMESTAMP-14+16/24 AND SYSTIMESTAMP+16/24

(참고사항 2번으로 인해 CURRENT_TIMESTAMP와 SYSTIMESTAMP는 같으므로
SYSTIMESTAMP 대신 CURRENT_TIMESTAMP를 사용해도 결과는 같다)

문제가 없어 보인다. 문제를 찾으려고 하지 않을 만큼 매우 쉬운 쿼리다.

자, 이제 몇 달이 흘러 11월 28일이다. 쿼리 결과를 다시 확인해보았다.

그런데?

1시간이 오차가 있는 것이다. 왜일까?

Summer Time을 고려하지 않아서이다. 다음은 2018년 기준 미국 LA의 Summer Time 적용 시간 변화다.

LA_SUMMER_TIME

맨 처음 쿼리가 작성된 시점이 PDT (UTC-7h)이고, 마지막으로 쿼리 결과를 확인한 시점이 PST (UTC-9h)이다.  이 Summer Time 적용을 고려했을 때, 쿼리는 다음과 같이 수정되어야 한다.

수정 전
WHERE …. 날짜컬럼 BETWEEN SYSTIMESTAMP-14+16/24 AND SYSTIMESTAMP+16/24

수정 후
WHERE …. 날짜컬럼 BETWEEN SYS_EXTRACT_UTC(SYSTIMESTAMP)-14+9/24 AND SYS_EXTRACT_UTC(SYSTIMESTAMP)+9/24

 

결론
국가 간 시간 차를 반영할 때 표준시간을 기준으로 생각하고
SYS_EXTRACT_UTC() function을 사용하자!

 

참고
https://www.timeanddate.com/time/zone/usa/los-angeles

Oracle Database SQL Certified Associate자격증 취득 (‘18.09.18)

Oracle Database SQL Certified Associate

Oracle Database SQL Certified Associate

An Oracle Database SQL Certified Associate demonstrates understanding of fundamental SQL concepts needed to undertake any database project. Candidates have illustrated a depth of knowledge of SQL and its use when working with the Oracle Database server, and a working knowledge of queries, insert, update and delete SQL statements as well as some Data Definition language and Data Control Language, the optimizer, tables and indexes, data modeling and normalization.

ISSUED BY

Oracle

ISSUED TO

Sion Lee

ISSUED ON

18 Sep 2018

SKILLS

WHAT IT TAKES TO EARN THIS BADGE

Oracle DB 12c Client 설치 및 DB 접속

1. Oracle DB version 확인

Oracle DB 12c

2. 다운로드

link

3. 설치

*INS-30131 에러 발생 시
ins-30131
cmd 에서 다음과 같이 실행
‘client 설치 경로’\setup.exe -ignorePrereq -J”-Doracle.install.client.validate.clientSupportedOSCheck=false”

4. 개발 DB 접속정보 수령 (tnsnames.oRA 파일)

– 접속계정(사용자 이름)
– 비밀번호
– tnsnames.oRA 파일

*tnsnames.oRA 파일 내에 IP, Port, DB Name (SID) 정보가 있음.***=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=**.**.**.**)
(PORT=****)
)
(CONNECT_DATA=
(SERVICE_NAME=***)
)
)

 

5. tnsnames.oRA 파일 복사/붙여넣기

C:\app\client\Admin\product\12.1.0\client_1\network\admin\sample

위 경로에 tnsnames.oRA 파일 복사/붙여넣기

6. tnsnames 디렉토리 설정

도구 – 환경설정 – 데이터베이스 – 고급 – Tnsnames 디렉토리
C:\app\client\Admin\product\12.1.0\client_1\network\admin\sample 해당 경로 설정
tnsoraconfigure


7. SQL Developer 실행

 

8. DB 접속 정보 입력

dbaccess.png
– 접속 이름: 임의
– 사용자 이름 : 접속 계정 입력
– 비밀번호 : 비밀번호 입력
-Oracle : 접속 유형 – TNS – 네트워크 별칭 선택 – 테스트 – 접속

*접속 유형을 기본으로 설정 후 일일히 입력하는 방법도 가능
– 호스트 이름 : IP 주소
– 포트 : 포트
– SID : DB Names (SID)