SELECT * FROM my_table
INTO OUTFILE 'my_table.csv'
CHARACTER SET euckr
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
SELECT * FROM (
(
SELECT
'필드1' AS 'filed_1',
'필드2' AS 'filed_2'
) UNION (
SELECT
filed_1,
filed_2
FROM my_table
)
) AS mysql_query
INTO OUTFILE 'my_table.csv'
CHARACTER SET euckr
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
mysql -p my_db -e "SELECT * FROM my_table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > my_table.csv
#!/bin/bash
db=YOUR_DB
user=YOUR_USER
pass=YOUR_PASS
for table in $(mysql -u$user -p$pass $db -Be "SHOW tables" | sed 1d); do
echo "exporting $table.."
mysql -u$user -p$pass $db -e "SELECT * FROM $table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > $table.csv
done
저는 mysql 8를 사용 중입니다. 1번 쿼리를 사용해보니 'my_table.csv' 파일명에 my.ini의 secure-file-priv 경로를 같이 적어야 정상적으로 파일이 export 되네요.
데이터 건 수(몇 십만건)가 많을 경우에는 3번 방법을 사용하면 안될 것 같아요. 데이터 건마다 치환 작업을 해주어야 하니 오랜 시간이 걸립니다.
기계 학습 모델을 구축하고 훈련하는 과정에서 각 실험의 결과를 추적하는 것은 매우 중요합니다. 딥 러닝 모델의 경우 TensorBoard는 훈련 성능을 기록하고, 기울기를 추적하고, 모델을 디버그하는 등 매우 강력한 도구입니다. 또한 관련 소스 코드를 추적해야합니다. Jupyter Notebook은 버전을 지정하기가 어렵지만 git과 같은 VCS를 사용하여 도움을 줄 수 있습니다. 그러나 실험 컨텍스트, 하이퍼 파라미터 선택, 실험에 사용 된 데이터 세트, 결과 모델 등을 추적하는 데 도움이되는 도구도 필요합니다. MLflow는 웹 사이트에 명시된대로 해당 목적을 위해 명시 적으로 개발되었습니다.
MLflow는 실험, 재현성 및 배포를 포함하여 ML 수명주기를 관리하기위한 오픈 소스 플랫폼입니다.
이를 위해 MLflow는MLflow Tracking실험 / 실행을 추적 할 수있는 웹 서버 인 구성 요소 를 제공합니다 .
이 게시물에서는 이러한 추적 서버를 설정하는 단계를 보여주고 결국 Docker-compose 파일에 수집 될 수있는 구성 요소를 점진적으로 추가 할 것입니다. Docker 접근 방식은 MLflow를 원격 서버 (예 : EC2)에 배포해야하는 경우 특히 편리합니다. 새 서버가 필요할 때마다 서버를 직접 구성 할 필요가 없습니다.
기본 로컬 서버
MLflow 서버를 설치하는 첫 번째 단계는 간단하며 python 패키지 만 설치하면됩니다. 나는 파이썬이 컴퓨터에 설치되어 있고 가상 환경을 만드는 데 익숙하다고 가정합니다. 이를 위해 pipenv보다 conda가 더 편리하다고 생각합니다.
이제 실험과 실행을 추적 할 실행중인 서버가 있지만 더 나아가려면 아티팩트를 저장할 서버를 지정해야합니다. 이를 위해 MLflow는 몇 가지 가능성을 제공합니다.
아마존 S3
Azure Blob 저장소
구글 클라우드 스토리지
FTP 서버
SFTP 서버
NFS
HDFS
(mlflow-env)$ mlflow server — default-artifact-root s3://mlflow_bucket/mlflow/ — host 0.0.0.0
MLflow는 시스템의 IAM 역할, ~ / .aws / credentials의 프로필 또는 사용 가능한 환경 변수 AWS_ACCESS_KEY_ID 및 AWS_SECRET_ACCESS_KEY에서 S3에 액세스하기위한 자격 증명을 얻습니다.
— h ttps : //www.mlflow.org/docs/latest/tracking.html
따라서 더욱 실용적인 방법은 특히 AWS EC2 인스턴스에서 서버를 실행하려는 경우 IAM 역할을 사용하는 것입니다. 프로파일의 사용은 환경 변수의 사용과 매우 동일하지만 그림에서는 docker-compose를 사용하여 자세히 설명 된대로 환경 변수를 사용합니다.
백엔드 저장소 사용
SQLite 서버
따라서 추적 서버는 S3에 아티팩트를 저장합니다. 그러나 하이퍼 파라미터, 주석 등은 여전히 호스팅 시스템의 파일에 저장됩니다. 파일은 틀림없이 좋은 백엔드 저장소가 아니며 우리는 데이터베이스 백엔드를 선호합니다. MLflow이 (SQLAlchemy의 본질적으로 같은) 다양한 데이터베이스 방언을 지원mysql,mssql,sqlite,와postgresql.
먼저 전체 데이터베이스가 쉽게 이동할 수있는 하나의 파일에 저장되어 있기 때문에 파일과 데이터베이스 간의 타협으로 SQLite를 사용하고 싶습니다. 구문은 SQLAlchemy와 동일합니다.
Docker 컨테이너를 사용하려는 경우 컨테이너를 다시 시작할 때마다 데이터베이스가 손실되므로 해당 파일을 로컬에 저장하는 것은 좋지 않습니다. 물론 EC2 인스턴스에 볼륨과 EBS 볼륨을 계속 마운트 할 수 있지만 전용 데이터베이스 서버를 사용하는 것이 더 깨끗합니다. 이를 위해 MySQL을 사용하고 싶습니다. 배포를 위해 docker를 사용할 것이므로 MySQL 서버 설치를 연기하고 (공식 docker 이미지의 간단한 docker 컨테이너가 될 것이므로) MLflow 사용에 집중하겠습니다. 먼저 MySQL과 상호 작용하는 데 사용할 Python 드라이버를 설치해야합니다.pymysql설치가 매우 간단하고 매우 안정적이며 잘 문서화되어 있기 때문에 좋아 합니다. 따라서 MLflow 서버 호스트에서 다음 명령을 실행합니다.
이제 모든 설정이 완료되었으므로 모든 것을 도커 작성 파일에 모을 시간입니다. 그런 다음 명령만으로 MLflow 추적 서버를 시작할 수 있으므로 매우 편리합니다. docker-compose 파일은 세 가지 서비스로 구성됩니다. 하나는 백엔드, 즉 MySQL 데이터베이스, 하나는 역방향 프록시 용, 다른 하나는 MLflow 서버 자체 용입니다. 다음과 같이 보입니다.
version: '3.3'
services:
db:
restart: always
image: mysql/mysql-server:5.7.28
container_name: mlflow_db
expose:
- "3306"
networks:
- backend
environment:
- MYSQL_DATABASE=${MYSQL_DATABASE}
- MYSQL_USER=${MYSQL_USER}
- MYSQL_PASSWORD=${MYSQL_PASSWORD}
- MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
volumes:
- dbdata:/var/lib/mysql
web:
restart: always
build: ./mlflow
image: mlflow_server
container_name: mlflow_server
expose:
- "5000"
networks:
- frontend
- backend
environment:
- AWS_ACCESS_KEY_ID=${AWS_ACCESS_KEY_ID}
- AWS_SECRET_ACCESS_KEY=${AWS_SECRET_ACCESS_KEY}
- AWS_DEFAULT_REGION=${AWS_DEFAULT_REGION}
command: mlflow server --backend-store-uri mysql+pymysql://${MYSQL_USER}:${MYSQL_PASSWORD}@db:3306/${MYSQL_DATABASE} --default-artifact-root s3://mlflow_bucket/mlflow/ --host 0.0.0.0
먼저 주목할 점은 프런트 엔드 (MLflow UI)를 백엔드 (MySQL 데이터베이스)로 분리하기 위해 두 개의 사용자 지정 네트워크를 구축했습니다.web서비스, 즉 MLflow 서버 만 둘 다와 통신 할 수 있습니다. 둘째, 컨테이너가 다운 될 때 모든 데이터가 손실되는 것을 원하지 않으므로 MySQL 데이터베이스의 콘텐츠는dbdata. 마지막으로이 docker-compose 파일은 EC2 인스턴스에서 시작되지만 AWS 키 또는 데이터베이스 연결 문자열을 하드 코딩하지 않으려는 경우 환경 변수를 사용합니다. 이러한 환경 변수는 호스트 시스템에 직접 위치하거나.envdocker-compose 파일과 동일한 디렉토리에 있는 파일 내에있을 수 있습니다 . 남은 것은 컨테이너를 구축하고 실행하는 것입니다.
$ docker-compose up -d --build
그리고 그게 전부입니다 ! 이제 팀간에 공유 할 수있는 완벽하게 실행되는 원격 MLflow 추적 서버가 있습니다. 이 서버는 docker-compose 덕분에 하나의 명령으로 어디서나 쉽게 배포 할 수 있습니다.
SELECT DISTINCT ON(FIRSTNAME) FIRSTNAME, LASTNAME FROM MEMBER_TBL ORDER BY FIRSTNAME, LASTNAME DESC;
SELECT MEMBERID, MEMBERNAME, PHONENO FROM MEMBER_TBL ORDER BY MEMBERID LIMIT 4 OFFSET 0; # 0번째 행(=첫 행) 부터 4 개행 가져오기
Oracle 계층 쿼리 ) SELECTA.CONTS_ID, A.CONTS_NM, A.UP_CONTS_ID, A.MENU_ORD, LEVEL/*계층구조에서 단계,레벨을 나타내주는 함수*/ FROMCLT_MENU A WHEREA.MENU_INCL_YN = 'Y' ANDLEVELIN (2,4) START WITHA.CONTS_ID = 'voc'/*계층구조의 시작조건을 주는 조건절*/ CONNECT BY PRIORA.CONTS_ID = A.UP_CONTS_ID/*계층구조의 상,하위 간의 관계 조건*/ ORDER SIBLINGS BYA.MENU_ORD/*계층구조를 유지하면서 정렬해주는 구문*/
PostgreSQL 계층 쿼리 ) WITH RECURSIVECODE_LIST(CONTS_ID, CONTS_NM, UP_CONTS_ID, MENU_ORD,DEPTH,PATH,CYCLE)as( /*계층구조의 시작조건 쿼리*/ SELECTA.CONTS_ID, A.CONTS_NM, A.UP_CONTS_ID, A.MENU_ORD, 1, ARRAY[A.CONTS_ID::text], false FROMCLT_MENU A WHEREA.CONTS_ID = 'voc' ANDA.MENU_INCL_YN = 'Y' UNION ALL /*하위 데이터를 찾아가기 위한 반복조건 쿼리*/ SELECTA.CONTS_ID, A.CONTS_NM, A.UP_CONTS_ID, A.MENU_ORD, B.DEPTH + 1, ARRAY_APPEND(B.PATH, A.CONTS_ID::text), A.CONTS_ID =any(B.PATH) FROMCLT_MENU A, CODE_LIST B WHEREA.UP_CONTS_ID = B.CONTS_ID ANDA.MENU_INCL_YN = 'Y' ANDNOTCYCLE ) /*View쿼리*/ SELECTCONTS_ID, CONTS_NM, UP_CONTS_ID, MENU_ORD, DEPTH AS A_MENU_LEVEL, PATH FROMCODE_LIST WHEREDEPTH IN (2,4) ORDER BYPATH
-CYCLE은 RECURSIVE를 통한 재귀 쿼리 수행 시 성능 상의 문제를 해결하기 위함 UNION ALL다음의 반복조건 쿼리가 수행되면 CYCLE이 false이기 때문에 SELECT문이 수행 되고 검색된 자식 node의 ID 값이 배열(ARRAY[A.CONTS_ID::text])에 추가(ARRAY_APPEND(B.PATH, A.CONTS_ID::text)) 됨. -ANY(B.PATH)는 PATH배열에 자신의 ID값이 있는 지를 검사하여, 이미 찾은 값에 대해서는 더 이상 데이터 검색을 수행하지 않도록 함. - 배열에는 DataType이 int, text인 형태만 담을 수 있으므로 배열에 담을 varchar타입의 컬럼 뒤에 ::text 를 붙여 형태를 변환 해줌.
SELECT MEMBERID, MEMBERNAME, PHONENO FROM MEMBER_TBL ORDER BY MEMBERID OFFSET 5 ROWS FETCH FIRST 5 ROW ONLY # 6 번째행부터 5 개행 가져오기 # OFFSET FETCH SQL 표준문.
Seq Scan방식 ▪Seq Scan은 테이블을Full Scan하면서 레코드를 읽는 방식이다. ▪인덱스가 존재하지 않거나,인덱스가 존재하더라도 읽어야 할 범위가 넓은 경우에 선택한다. Index Scan방식 ▪Index Scan은 인덱스Leaf블록에 저장된 키를 이용해서 테이블 레코드를 액세스하는 방식이다. ▪인덱스 키 순서대로 출력된다. ▪레코드 정렬 상태에 따라서 테이블 블록 액세스 횟수가 크게 차이 난다. Bitmap Index Scan방식 ▪테이블 랜덤 액세스 횟수를 줄이기 위해 고안된 방식이다. ▪Index Scan방식과Bitmap Index Scan방식을 결정하는 기준은 인덱스 칼럼의Correlation값이다. ▪Correlation이란 인덱스 칼럼에 대한 테이블 레코드의 정렬 상태이다. (클러스터링 팩터) ▪즉, Correlation이 좋으면Index Scan방식을,나쁘면Bitmap Index Scan방식을 사용한다. ▪Bitmap Index Scan방식은 액세스할 블록들을 블록 번호 순으로 정렬한 후에 액세스한다. ▪이로 인해,테이블 랜덤 액세스 횟수가 크게 줄어든다. (블록당1회) ▪테이블 블록 번호 순으로 액세스하므로,인덱스 키 순서대로 출력되지 않는다. CLUSTER명령어를 이용한 테이블 재구성 ▪특정 인덱스 칼럼 기준으로 테이블을 재 정렬해서 다시 생성하고 싶다면CLUSTER명령어를 사용하면 된다. ▪다만,이때도Vacuum FULL과 동일하게SELECT와도 락이 호환되지 않는다는 점을 유의해야 한다. Lossy모드 ▪Bitmap Index Scan방식은 비트맵을 이용해서 처리된다. ▪이때,비트맵 정보는Backend프로세스 메모리 내에 저장된다. ▪만일 메모리 공간이 부족하면exact모드에서lossy모드로 전환한다. ▪lossy모드는exact모드에 비해서 느리다. exact모드는 비트맵 내의1개의 비트가1개의 레코드를 가리킨다. lossy모드는 비트맵 내의1개의 비트가1개의 블록을 가리킨다. Index Only Scan방식 ▪Covering Index를 이용하는 것을Index Only Scan방식이라고 한다. ▪Covering Index란SELECT칼럼 및WHERE조건을 모두 포함하는 인덱스를 의미한다. ▪Covering Index의 장점은 테이블 랜덤 액세스를제거할 수 있다는 것이다. ▪단, Vacuum을 수행해야만IndexOnlyScan방식으로 동작한다. 액세스 방식을 제어하는 방법 ▪SET절을 이용해서 액세스 방식을제어할수 있다
# Join 방식
PostgreSQL에서 지원하는 조인 방법 ▪Nested Loop조인 ▪Sort Merge조인 ▪해시 조인(Hybrid해시 조인 지원)
Nested Loop Join
NL조인 시에Materialize가 발생하면 인덱스 생성을 고려해야 한다.
▪NL조인 시에 연결 고리에 인덱스가 없으면Materialize오퍼레이션이 발생할 수 있다.
▪이는 보완책이지 해결책이 아니다.따라서 인덱스 생성을 고려해야 한다
Hash Join
▪해시 조인은 해시 함수를 이용한다.해시 함수(h)는 다음과 같은 특성을 갖는다.
1. X=Y이면 반드시h(X)=h(Y)이다.
2. h(X)≠h(Y)이면 반드시X≠Y이다.
3. X≠Y이면h(X)≠h(Y)인 것이 가장 이상적이다.
4. X≠Y이면h(X)=h(Y)일 수도 있다.이것을 해시 충돌이라고 한다.
In-Memory해시 조인
▪In-Memory해시 조인은 해시Build작업을work_mem공간 내에서 모두 처리할 수 있을 때 사용하는 방식이다.
In-Memory로 처리할 수 없을 때 사용되는 해시 조인 방식들
▪해시Build작업을work_mem공간 내에서 모두 처리할 수 없을 때 사용하는 방식은 크게3가지이다.
Outer조인 개요
▪Outer조인은 조인 성공 여부와 무관하게 기준 집합의 레코드를 모두 출력하는 방식이다.
▪PostgreSQL은ANSI-SQL형태의Outer조인 문법만을 지원한다.
▪Outer조인은 결과 건수에 영향을 미치므로 사용시에 주의해야 한다.
▪NL Outer조인은 기준 집합을 항상 먼저Driving한다.
▪Hash Outer조인은SWAP INPUT기능을 제공한다
Hash Right Join, Hash Left Join의 의미
▪Explain결과의Hash Right Join은 기준 테이블이RIGHT,즉Build테이블이라는 사실을 알려준다.
▪Explain결과의Hash Left Join은 기준 테이블이LEFT,즉Probe테이블이라는 사실을 알려준다.
# Query Rewrite
▪사용자가 작성한 쿼리를Optimizer가 더 좋은 실행계획을 수립할 수 있는 형태로 변경하는 것을Query Rewrite라고 한다.
MLproject라는 파일이 존재하는데, 이 파일을 통해 이 프로젝트가 MLflow Project 임을 알 수 있다. MLflow Projet 는mlflow run명령어로 실행이 가능하다. 위의 경우mlflow run sklearn_logistic_regression으로 실행할 수 있다.
MLProject 살펴보기
MLflow Project 를mlflow run으로 실행할 때 무엇을 어떻게 실행할 것인지를 알아야하는데, 이에 대한 내용을MLproject파일이 담고있다.
간단한 예시를 보자. 다음은examples/sklearn_elasticnet_wine/MLproject파일의 내용이다.
이 MLProject 를 실행할 때 conda 환경을 만든 뒤 실행하게 되는데, 이 때 참고할 conda 환경에 대한 파일이름을 값으로 가진다. 여기서는 이 프로젝트 내conda.yaml에 이 설정 값들이 있다.
conda가 아닌 docker를 사용할 수 있는데, 이 때docker_env라는 키를 사용하면 된다. 이에 대한 내용은 아래에서 다시 설명하겠다.
entry_points
mlflow run으로 실행할 때-e옵션으로 프로젝트 실행에 여러 진입점을 둘 수 있는데, 이 때 사용되는 값이다.
예를 들면, 위의 경우mlflow run -e main sklearn_elastic_wine으로main이라는 진입점으로 실행할 수 있다.
위 명령어가 실행되면python train.py {alpha} {l1_ratio}명령어를 실행한다. 이 때{alpha}와{l1_ratio}는 위mlflow run시 받는 파라미터 값이다. 이는parameters에 정의가 되어있다.
다시 정리해보면 위와 같이 정의된 MLflow Project는 다음 명령어로 실행할 수 있다. (코드에서는mlflow.projects.run()명령어로 실행할 수 있다. 이에 대한 내용은여기를 참고하자.)
$ mlflow run -e main sklearn_elastic_wine -P alpha=0.1 -P l1_ratio=0.5
이 명령어는 다시 내부적으로 아래 명령어를 실행하게 된다.
$ python train.py 0.1 0.5
Docker 환경 사용하여 실행하기
위MLproject파일 내에서conda_env를 사용하였다. 이럴 경우 MLflow Project를 실행시키는 환경에 conda가 미리 깔려있어야 실행이 가능하다. conda가 깔려있지 않으면mlflow run에서 에러를 뱉을 것이다. (--no-conda옵션을 주는 경우도 있지만, 이럴 경우 또virtuanlenv등으로 가상 환경을 세팅하고 필요한 라이브러리르 일일이 설치해주어야 해서 번거롭다.)
conda를 쓰지 않고 docker 컨테이너 환경으로 실행이 가능한데 이 방법을 살펴보자. 이번에도 역시 공식 예제를 활용한다. 다음은examples/docker에 있는 예시다.
# Dockerfile
FROM python:3.8.8-slim-buster
RUN pip install mlflow>=1.0 \
&& pip install numpy \
&& pip install pandas \
&& pip install scikit-learn
# 예제 파일을 그대로 실행하면 오류가 생겨서 약간 손을 보았다.
# 베이스 이미지 수정, 필요없는 패키지 및 버전을 지운 정도다.
# 아마 2년 전 예제라 업데이트가 잘 안된 듯 싶다.
이렇게 실행하면mlflow는mlflow-docker-example이름의 도커 이미지를 찾아 그 위에 mlflow 코드를 실행하는 도커 이미지를 하나 더 만들고 이 이미지를 실행한다. 결과적으로 이미지를 하나 더 만드는 셈이다. 아래 사진을 보면docker-example이라는 이미지가 만들어 진 것을 볼 수 있다.
이미 감이 온 사람은 알겠지만,MLProject의docker_env.image값은 로컬 도커 이미지가 아니여도 된다. Dockerhub나 GCR, ECR 등에 미리 만들어두고 사용해도 된다. 또한 컨테이너 내부의 환경 변수 설정 등도 가능하다. 자세한 내용은여기를 참고하자.
Github에 있는 프로젝트 실행하기
지금까지 로컬에 있는 MLflow Project를 실행했다면 다음처럼 github에 올려둔 MLProject 를 실행시킬 수 있다. 예를 들면 다음과 같다.
mlflow run git@github.com:mlflow/mlflow-example.git -P alpha=0.5 --no-conda
쿠버네티스에서 실행하기
mlflow run을 할 때--backend와--backend-config파라미터 설정으로 MLflow Project를 쿠버네티스 상에서 실행시킬 수 있다. (Job 리소스로 실행된다.) 예를 들면 다음과 같다.
$ mlflow run <project_uri> \
--backend kubernetes \
--backend-config kubernetes_config.json
위 명령어를 실행하게 되면 다음의 과정이 일어난다.
MLflow Project 실행을 도커 이미지로 만든다.
이 이미지를 사용자가 설정해둔 도커 컨테이너 레지스트리에 푸시한다.
쿠버네티스에서 이 이미지를 Job으로 배포한다.
위 명령어가 실행이 되려면 푸시할 도커 이미지 레지스트리와 쿠버네티스 접속 컨텍스트가 필요한데, 이를 위해 MLflow Project 내에 다음과 같은 파일이 있어야 한다.
누군가가 만들어 놓은 MLflow 모델을 보게되면 보통MLmodel을 통해 모델에 대한 정보를 먼저 파악하려고 할 것이다. 그런데 위MLmodel파일만 봐서는 이 모델이 어떤 입력을 받고 어떤 출력을 뱉는지 알수가 없다. 입출력에 대한 정보는 시그니처라고 말하는데, 이를 쉽게 알 수 있도록 시그니처를 추가해주자.
예시 코드는 다음과 같다.
import pandas as pd
from sklearn import datasets
from sklearn.ensemble import RandomForestClassifier
import mlflow
import mlflow.sklearn
from mlflow.models.signature import infer_signature
iris = datasets.load_iris()
iris_train = pd.DataFrame(iris.data, columns=iris.feature_names)
clf = RandomForestClassifier(max_depth=7, random_state=0)
clf.fit(iris_train, iris.target)
# 입출력 정보를 정해주는 부분. 이런 정보를 시그니처라고 한다.
signature = infer_signature(iris_train, clf.predict(iris_train))
# 위에서 정한 시그니처 값을 인자로 넘긴다.
mlflow.sklearn.log_model(clf, "iris_rf", signature=signature)
위에서는infer_signature()함수를 사용하여 시그니처를 정해주었다. 이 함수는 실제 입력데이터와 출력 데이터를 파라미터로 넘기면, 시그니처를 알아서 추론해준다. 구체적인 모양새는여기를 참고하자.
