MSsql은 산업 현장에서 SCADA 시스템 구축 할때, Local DB로 사용이 많이 된다. 그런 만큼 MSsql의 범용성이 매우 크다고 할 수 있다.
요새는 PowerBi 군을 사용하게 되면서, MSsql이 상당히 부각되었다. 1천만 건 이상의 데이터를 Visualization 할 수 있는 Tool이 되었다.
나는 20년 전에 ASP 웹 개발 하였을때, 주로 사용 했었다. 어찌 되었든 MSsql은 MS 답게 사용성은 다른 DB 엔진에 비하여 편리 하다.
 |
| MSSQL R 연결 |
MSsql 설치 Express 버전
지금 설치 할것은 MSsql 무료 버전인 Express을 사용할 것이다. 용량이 10G로 제한 되어 있고, Single CPU를 사용하는 제품 군이다. MSsql은 CPU 사용 갯수 별로 라이선스 비용을 받으니, CPU 갯수하고, Raid 만 병렬로 지속적으로 늘리면, 속도도 괞잖게 나온다. 문제는 돈이 많이 들 뿐이다.
아래 우분투 기준으로 설명 하겠다.
$ sudo apt-get update
$ sudo apt-get -y upgrade
우분투 시스템 업데이트 이후 재부팅을 권장 한다.
$ sudo reboot
공용 레포지트리 저장소에서 GPG Key를 가져온다.
$ sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2017.list)"
Micorsoft SQL 서버에 우분투 레포지트리 저장소를 추가 한다.
$ sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2017.list)"
MSsql 서버를 아래 명령어와 같이 설치 한다.
$ sudo apt-get update
$ sudo apt install libcurl3
$ sudo apt-get install -y mssql-server
정상적으로 설치 되었다면, Mssql 버전 설정과 패스워드 설정을 한다.
$ sudo /opt/mssql/bin/mssql-conf setup
이때 선택 창이 나오면, 무료 버전인 Express 버전을 선택하고 라이선스 동의 한후 에 패스워드를 설정 한다.
MSsql 서비스를 시작한다.
$ systemctl status mssql-server.service
MSsql TOOL 및 ubnixODBC 플러그인 설치
아래와 같이 unixODBC 플러그인 설치 한다.
$ curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
$ sudo apt-get update
$ sudo ACCEPT_EULA=Y apt-get -y install mssql-tools
$ sudo apt-get install unixodbc-dev
로그인 세션을 위해 bash 셀에서 sqlcmd/bcp에 액세스 할 수 있도록 설정 하려면 다음 명령을 사용하여 ~/.bash_profile 파일에서 PATH를 수정한다.
$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
$ source ~/.bashrc
sqlcmd 서버이름(-S), 사용자 이름 - U 및 암호 -P의 매계변수를 이용하여 실행 한다.
$ sqlcmd -S 127.0.0.1 -U SA
만약에 패스워드를 넣었는데 아래와 같은 문제가 발생 하면, 조치를 해야 한다.
$ sqlcmd -S 127.0.0.1 -U SA
Password:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.
SQL Server를 중지 한다.
$ sudo systemctl stop mssql-server
다음을 통해 서비스 구성을 위한 편집기를 연다.
$ sudo systemctl edit mssql-server
편집기에서 파일에 다음 행을 추가하고 저장 한다.
[Service]
Environment="LD_LIBRARY_PATH=/opt/mssql/lib"
SQL Sever에서 사용할 OpenSSL 1.0에 대한 기호 링크를 작성 한다.
$ sudo ln -s /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 /opt/mssql/lib/libssl.so
$ sudo ln -s /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /opt/mssql/lib/libcrypto.so
SQL 서버 시작
$ sudo systemctl start mssql-server
MSSQL 연결
$ sqlcmd -S 127.0.0.1 -U SA
1> select name from sys.databases;
2> go
name
----------------------------------------------------------------------------------------------------------------- ---------------
master
tempdb
model
msdb
(4 rows affected)
추가로 데이터 베이스를 만들어 준다.
1> CREATE DATABASE yongas;
2> go
위와 같은 명령어가 잘 실행 되어 있으면, MSsql에는 문제가 없는 것이다.
트러블 슈팅
위와 같이 MSsql을 설치 하면 R 깨짐 현상이 발생 한다. 만약에 깨짐 현상이 발생되었을대는 아래와 같이 R을 다시 설치 한다.
$ sudo apt install r-cran-littler
MS-SQL 과 R 연동
MS-SQL과 R을 연동 하려면 두가지 패키지가 필요하다. "DBI", "odbc" 여러가지 종류의 연결 툴이 있지만, 이 두가지가 DB 반응 속도가 빠르고, 사용하기가 편리 해서 사용을 한다.
패키지를 설치할때 일반적으로 install.packages(DBI) 식으로 스크립트를 만든다. 이러한 스크립트는 처음 사용자에게 위와 같은 방식으로 스크립트를 만들어 주면, 스크립트 돌릴때마다, 패키지를 설치 한다. 패키지 설치는 맨 처음 한번만 실행 하는 것이다. 패키지가 설치 하면 libary(DBI)로 패키지를 로딩 한다. 패키지 로딩은 사용 할때 마다 해야 한다.
# 패키지가 없으면 설치하고, 있으면 불러오기
pkg_fun <- function(pkg) {
if(!require(pkg, character.only = TRUE)) {
install.packages(pkg)
library(pkg, character.only = TRUE)
}
}
pkg_fun("DBI")
pkg_fun("odbc")# 데이터 베이스 연결
con <- dbConnect(odbc::odbc(),
Driver = "ODBC Driver 17 for SQL Server",
Server = '127.0.0.1',
Database = "yongas",
Uid = 'yongas',
Pwd = '%&$#$8797',
Port = 1433)아래의 건은 데이터 베이스가 잘 적용되었는지 테스트 하는 것으로 데이터 원자벡터(Atomic Vector)를 만들어 벡터를 data.frame으로 묶어 우리가 알고 있는 DataBase의 데이터 구조를 만든 것이다. 원자벡터는 기본적으로, 한개의 배열로 나열되어 있는 변수로 구성 되지만, 이를 data.frame으로 묶을 때는 하나의 필드가 되어 데이터 셋이 만들어 지게 된다.
아래 데이터셋은 국내 6대 은행 2018년도 지점 수를 벡터를 가지고 데이터 프레임을 만든것이다. 상용데이터 베이스를 적용할때, 일반적으로 한글이 깨지는 현상이 많이 일어나 한글 테스트도 같이 하였다.
데이터 베이스 입력
# 벡터 데이터 만들기
bankName <- c("농협은행", "국민은행", "신한은행",
"우리은행", "하나은행", "기업은행")
지점수 <- c(1149, 1055, 872, 880, 765, 612)
# 벡터 데이터 data.frame에 넣기
hangul_test <- data.frame(bankName, 지점수)# 데이터 있는지 확인하고 있으면 날리기
if(dbExistsTable(con,"hangul_test") == TRUE) dbRemoveTable(con, "hangul_test") MSSQL에서는 INSERT INTO 라는 명령어를 활용하여 데이터를 입력한다. 하지만 R에서 사용하는 odbc패키지는 데이터 프레임을 전체를 쓰기 한다. 물론 데이터가 존재 한다면, 프레임 전체 쓰기 하는 것은 불가능 하다. 프레임 전체를 쓰는 것은 한번만 가능 하다. 프레임 전체를 데이터베이스에 쓰는 이유는 R은 모델링 작업 이후에, 이전 데이베이스를 삭제하고, 다시 넣는게 대부분이기 때문이다. 실무에서는 모델링 한 결과가, 데이터 입력 및 모델에 의한 학습에 따라, 결과 값은 차이가 있기 때문에 테이블 전체를 지우고 새로 올리는 Case 가 많다.
# 데이터 프레임 전체 쓰기
data <- dbWriteTable(con, "hangul_test", hangul_test)
# 데이터 전체 가져오기
get_hangul <- dbReadTable(con, "hangul_test")
# 한글 깨졌는지 보기
get_hangul## bankName 지점수
## 1 농협은행 1149
## 2 국민은행 1055
## 3 신한은행 872
## 4 우리은행 880
## 5 하나은행 765
## 6 기업은행 612물론 이것은 MSSQL이기 때문에 쿼리를 이용하여 데이터를 가져올 수 있다. 쿼리로 데이터를 가져올때는 dbSendQuery() 함수를 이용한다.
# 쿼리를 이용하여 데이터 베이스 가져오기
query <- dbSendQuery(con, "SELECT bankName FROM hangul_test")
categori_name_df <- dbFetch(query)
dbClearResult(query)
댓글 없음:
댓글 쓰기