[GCP] #05 분석을 위한 Google CloudSQL SQLServer 구축&GCE 파이썬 분석서버와 연결하기

<시리즈 목차> 

1화 : [GCP] #01 GCP Compute Engine VM 으로 분석서버 구축하기
2화 : [GCP] #02 GCP Compute Engine VM 인스턴스 초기 세팅하기
3화 : [GCP] #03 GCP Compute Engine VM에 파이썬(Python) 분석환경 구성하기
4화 : [GCP] #04 GCP Compute Engine VM에 R 분석환경 구성하기
5화 : [GCP] #05 분석을 위한 Google CloudSQL SQLServer 구축&GCE 파이썬 분석서버와 연결하기
6화 : [GCP] #06 Google Bigquery GCE 파이썬서버에서 활용하기
7화 : [GCP] #07 GCP Compute Engine VM에 아파치 에어플로우(Airflow) 설치하여 ETL 환경 구축하기
8화 : [GCP] #08 GCP Compute Engine VM에 제플린(zepplien), 스파크(Spark), 스칼라(Scala) 설치하기
9화 : [GCP] #09 Google Cloud Storage 저장소를 GCE 파이썬 서버에서 활용하기

1. Google Cloud SQL 인스턴스 생성하기

메뉴 > 데이터베이스 > SQL 를 클릭하여 SQL 인스턴스 페이지로 들어간다.

SQL인스턴스 페이지에서 인스턴스 만들기 버튼을 클릭한다.

STEP01 : SQL Server 생성하기

제일 우측 SQL Server 선택 버튼을 클릭하여 SQL Server 생성 페이지로 진입한다.

인스턴스 ID와 루트계정 sqlserver 의 비밀번호를 입력하고 리전을 asia-northeast3 (서울), 영역가용성을 여러영역 (고가용성) 으로 선택하고 스크롤을 내린다.

머신유형에서 SQL 인스턴스의 스펙을 선택한다. 필자의 경우 우선 연결만 테스트할 목적으로 “경량”옵션을 선택하였다. 저장용량에서 20GB을 선택하고 저장용량 자동증가 설정의 체크는 해제하였다.

연결을 클릭하여 공개 IP 옵션을 선택한다.

인스턴스 만들기를 통해 인스턴스를 만든다.

인스턴스 생성이 완료돼면 GCP Compute Engine 처럼 생성한 SQL 인스턴스명이 하단에 표시된다.

 

2. GCE VM인스턴스와 SQL Server 연결하기

Cloud SQL에 접속하는 방법은  크게 3가지로, 별도의 Proxy 를 구성하여 인스턴스에 접근하는 방법과 Cloud SQL에서 접속하는 IP를 허용하는 방법, CloudSDK 명령줄 gcloud를 통해 접속하는 방법이 있다.

이 중에 Cloud SDK인 gcloud를 활용한 방법은 JetBrain과 같은 외부 서비스를 통해 접근하기는 어려운 방법이라 실상 2가지가 존재한다고 보면된다.

(이번 튜토리얼에서는 공개IP에 대한 연결방법에 대해 작성하였으며, 비공개IP를 활용한 연결은  https://cloud.google.com/sql/docs/mysql/connect-compute-engine#connect-gce-private-ip 를 참조하여 확인할 수 있다.)

방법01 : CloudSQL Proxy 를 활용한 SQL server 연결

Cloud SQL 인증 프록시를 활용하면, 별도의 인스턴스내 설정 없이도 GCE나 로컬에서 Cloud SQL 인스턴스에 접근할 수 있다. Cloud 프록시에 대한 정보는 공식문서 링크에서 자세하게 확인 할 수 있다.

프록시를 통한 CloudSQL 공식 문서 링크: Cloud SQL 인증 프록시를 사용하여 연결 | SQL Server용 Cloud SQL | Google Cloud

Cloud SQL 인증 프록시를 사용하여 연결  |  SQL Server용 Cloud SQL  |  Google Cloud

CloudSQL Proxy에 대한 공식 문서 링크: Cloud SQL 인증 프록시 정보 | SQL Server용 Cloud SQL | Google Cloud

Cloud SQL 인증 프록시 정보  |  SQL Server용 Cloud SQL  |  Google Cloud

앞서 만든 Cloud Compute Engine의 인스턴스의 SSH 터미널에서 아래 명령어를 입력하여 Cloud SQL 인증 프록시를 다운로드후 실행파일을 만든다. (Compute Engine VM인스턴스 생성과 세팅은 [GCP] #01 GCP Compute Engine VM 으로 분석서버 구축하기 혹은 [GCP] #02 GCP Compute Engine VM 인스턴스 초기 세팅하기 에서 확인할 수 있다.)

$ wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
$ chmod +x cloud_sql_proxy

다운받은 인증 프록시를 통해 아래 명령어를 입력하여 CloudSQL 프록시를 구성완료한다. 

$ ./cloud_sql_proxy -instances=<인스턴스연결이름>=tcp:1433

 인스턴스 연결이름은 우리가 만든 analysis-db 인스턴스의 "개요 > 이 인스턴스에 연결" 항목의 연결이름을 채워 넣으면 된다.

필자의 경우 CloudSQL 인증 프록시를 만들기 위해 아래와 같이 입력하였고

$ ./cloud_sql_proxy -instances=practical-python-gcp-341111:asia-northeast3:analysis-db=tcp:1433

정상적으로 연결이 된다면, 아래와 같은 화면으로 CloudSQL 프록시는 실행된다.

참고 : 만약 처음 연결하는 경우 Cloud SQL Admin API 권한사용 필요로 아래와 같이 에러메시지가 나올수 있는데, 안내된 붉은색 영역링크에서 사용설정을 해준후 다시 위 프록시 명령어를 입력하면 된다.

방법 02 : CloudSQL 인스턴스 IP허용을 통한 VM인스턴스와 SQL server 연결

Cloud SQL 인스턴스 화면에서 생성한 analysis-db 인스턴스를 클릭한다.

연결 > 네트워킹 탭에서 네트워크 추가를 클릭하고, 연결을 허용할 GCE(google compute engine) 인스턴스의 고정 ip를 추가 한다.

GCE(google compute engine) 인스턴스의 고정 ip의 경우 Compute Engine 메뉴의 VM인스턴스 페이지에서 확인할 수 있다.

Cloud SQL 인스턴스네 네트워크 추가가 정상적으로 완료되었다면 아래와 같이 승인된 네트워크내 GCE IP주소가 등록이 될것이고 저장버튼을 클릭하여 Cloud SQL 인스턴스 연결설정을 완료한다.

 

 

Compute Engine 인스턴스에 Microsoft ODBC driver 설치하기

Cloud SQL 인스턴스를 생성하고 Compute Engine 인스턴스에 프록시 구성까지 마쳤다 하더라도, 근본적으로 SQLServer에 연결하기 위한 ODBC 드라이버를 설치하지 않았기 때문에 SQLServer 연결이 불가능하다 따라서 Microsoft ODBC driver 드라이버를 설치하도록 하자 설치에 관한 공식홈페이지 가이드 문서는 아래 링크를 통해 참고 할 수 있다.

아래와 같은 명령어를 입력하여 driver를 설치한다.

if ! [[ "16.04 18.04 20.04 21.04" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

해당 코드는 MS 공식문서에서 발췌하였으며, 만약 오류가 발생하여 설치가 완료되지 않을경우 아래 공식문서를 참고하여 최신버전에 맞는 가이드 문서를 확인하길 추천한다.

만약 위 Microsoft ODBC driver 를 Compute Engine 에 설치하지 않았다면 파이썬에서 아래와 같은 Error 메시지에 직면할 것이다. 아래와 같이 에러메시지가 나온다면 Microsoft ODBC driver 설치후에 Python을 통한 SQLServer 연결을 진행해야 한다.
Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

MS SQLServer 공식가이드문서 링크 : Microsoft ODBC Driver for SQL Server 설치(Linux) - ODBC Driver for SQL Server

Jupyter notebook 에서 Cloud SQL SQLServer연결하기

앞서 만든 Jupyter notebook 서버에서 생성한 SQLServer를 연결해보자. 사용하는 라이브러리는 pyodbc이며 아래 MS SQLServer 공식 홈페이지의 가이드문서를 통해 연결방법에 대해 자세하게 확인할 수 있다. (Jupyter notebook 서버생성은 [GCP] #03 GCP Compute Engine VM에 파이썬(Python) 분석환경 구성하기 에서 확인할 수 있다.)

CloudSQL 인증 프록시를 활용했기 때문에 연결은 localhost 인 127.0.0.1로 연결하지만 로컬에서 프로시서버로 연결하고 프록시서버는 CloudSQL과 인증을 자동으로 진행한다.

아래 코드와 같이 인증에 필요한 정보를 입력후 SQLServer와 python을 연결하여 쿼리를 진행하여 연결을 마무리한다.

# odbc 커넥터를 통해 SQLServer 연결
import pyodbc

server = 'tcp:127.0.0.1' 
username = 'sqlserver' 
password = '1234567890' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

SQLServer 에 대한 상세가이드는 공식가이드에서 추가로 확인할 수 있다.
*  MS SQLServer pyodbc 연결 공식가이드문서 링크 : 3단계: pyodbc를 사용하여 SQL에 연결 - Python driver for SQL Server

참고 : 가상환경을 통해서 진행하거나, 별도 다른 환경에서 진행할 경우 pyodbc 설치시 오류가 날수 있다.
error command 'gcc' failed with exit status 1 pyodbc in ubuntu 

이 경우 아래 패키지를 설치하여 pyodbc 설치시 발생오류를 해결할 수 있다. 

$ sudo apt-get install build-essential libssl-dev libffi-dev python3-dev
$ sudo apt-get install unixodbc-dev

 

위 코드처럼 중요 정보는 인라인 코드에 입력해서는 안된다. 인라인코드에 서버, ID/PW 정보, key 등 중요정보를 직접 입력하는 것은 보안상 굉장히 위험한 일이다. 따라서 보통은 별도의 파일로 key정보를 관리하고 인라인에서는 이 파일을 불러오는 방식으로 인증정보를 처리한다. 위 코드는 쉬운 설명을 위해 인라인에 인증정보를 입력하였지만 아래와 같이 작성하는 것을 권장한다.

이러한 과정을 쉽게 도와주는 python-decuple 이라는 라이브러리가 존재한다. 해당 라이브러리의 설치와 사용법은 하단의 공식가이드문서에서 확인할 수 있다.

간단하게 아래와 같이 .env 라는 별도 파일을 생성하고  

server = tcp:127.0.0.1
username = sqlserver
password = 1234567890

파이썬에서는 인라인에 중요정보를 적는것이 아닌 .env 파일에서 불러오는 형태로 작성하게 된다.

config = AutoConfig(search_path='./.env') 를 통해서 위에서 생성한 .env파일 경로를 정해주면 decouple 라이브러리가 자동으로 .env파일을 읽어와 파일내 server, username, password 등에 맞게 정보를 대체하게 된다.

import pyodbc
from decouple import config, RepositoryEnv, AutoConfig
# .env 파일 경로 지정
config = AutoConfig(search_path='./.env')

server = config('server')
username = config('username')
password = config('password')
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

 

* python-decouple 공식문서링크 : python-decouple

3. CloudSDK gcloud 명령줄 도구를 통한 SQL server 연결

그 외에 gcloud 명령줄 도구를 통해서 CloudSQL SQLServer 인스턴스에 연결하는 방법이 있지만, 분석시에는 잘 사용하지 않기 때문에  공식가이드문서로 갈음한다.
gcloud cloud sql 연결 공식 가이드문서 : gcloud sql connect | Google Cloud CLI Documentation

gcloud sql connect  |  Google Cloud CLI Documentation