FTP에서 데이터 받아서 DB입력후 Redash로 보여주기

 

예전에 회사에서 타팀요청에 의해 Redash를 설치를 한 내용을 기입한적이 있습니다. 링크는 요기

 

이번에 또 다른 타팀에서 요청이 와서는... 신용업체 관리하는 회사에서 고객 신용정보를 제공 받는데 받는 조건이 웹사이트 조회 그리고 txt(원데이터) 제공이었습니다.

아마 그업체에서는 txt로 원본데이터를 주닌까 너네들이 지지고 볶고 하면 다 볼 수 있다...라고 약을 팔아서 계약한듯 싶었습니다.

 

왜냐? 계약한 팀에서 우리팀(IT)에 와서 이거 주면 다된다고 하더라...라고 보여주는게 txt 원본데이터........

계약을 진행한 팀에서는 이거 엑셀에서 열었는데 너무 커서 안열리더라 엑세스에서 해보고 있는데 너무 자료가 크다....난리였네요. 웹사이트에서 조회되는 정보가 너무 한정적이라 웹사이트도 활용도 힘든 상황...

 

여튼 업체에서 주는 데이터를 확인해 보니 우리쪽에서 작업을 크게 3가지로 하면 되겠더군요.(모든것이 스크립트를 이용한 자동화 구성)

 

1. 원데이터 자료(txt)를 FTP에서 다운로드

2. 받은 자료를 DB에 입력하기 위한 인코딩 변환

3. 인코딩된 자료를 DB에 입력하기

 

위 3가지 처리를 위해서 한 일주일동안 매달렸는데 머리속으로 구상만 했었지 실사용으로 해본적이 없어서 이번에 기회인지라 한번 구성을 해보았습니다.

 

일단 위 3가지를 하기 전에 Redash설치를 진행합니다. 예전에는 가상 Ubuntu를 구해서 설치 했습니다만 이번에는 Bitnami안에 Redash가 설치된 가상화 파일을 esxi에 올려서 부팅만 해서 설치완료. 버전은 8.x 이네요.

세상 참 편해졌습니다. 아 로컬에 mysql DB를 사용하시려면 mariadb 설치를 추가로 하시면 됩니다.

 

일단 여기까지 하면 Redash 설치는 끝납니다. 그외 redash 설정은 간단하닌까 보시면 금세 확인 가능할겁니다.

아래부터는 위의 3가지 스크립트에 대해서 간단히 붙여봅니다.

 

디렉토리 구조는 아래와 같이 생성했습니다.

 

/home/abc/ : 홈디렉토리

/home/abc/pub/ : ftp에서 받은 파일을 저장(Remote FTP에 있는 동일폴더명 입니다)

/home/abc/csv/ : ftp에서 받은 파일을 인코딩후 저장하는 폴더

/home/abc/inputDB/ : DB에 입력하는 스크립트를 저장하는 폴더

/home/abc/log/ : crontab 실행로그를 저장하는 폴더

 

 

1.자료를 FTP에서 다운로드

이후 FTP에서 자료를 다운로드 받는 스크립트를 아래와 같이 붙여봅니다.  예전에는 rsa키 복사하고 귀찮았는데 sshpass라를 프로그램을 설치하닌까 아주쉽게 sfp 접속도 가능하네요.(암호는 별도파일 기입하여 사용)

#!/bin/bash

 

REMOTE_FTP_ADDR="FTP서버주소"

REMOTE_USER="서버아이디"

REMOTE_DOWNLOAD_PATH=$(date +%Y%m%d)

LOCAL_DOWNLOAD_PATH="/home/abc/pub/"

 

mkdir /home/abc/pub/$REMOTE_DOWNLOAD_PATH

sshpass -f /home/bitnami/password sftp $REMOTE_USER@$REMOTE_FTP_ADDR << EOF

 

cd pub

cd $REMOTE_DOWNLOAD_PATH

lcd $LOCAL_DOWNLOAD_PATH

lcd $REMOTE_DOWNLOAD_PATH

 

mget *.*

quit

EOF

스크립트는 여기저기서 줏어다가 만든거라 깔끔하진 않습니다. 

간단히 설명을 붙이자면 sshpass를 이용해서 sftp접속후 파일을 갖고 옵니다만 그전에 해당 년월일로 된 폴더를 만들어서 저장하는 방식입니다. 매일 같이 파일을 받아야되서 구분을 하기위해 폴더 생성을 합니다.

 

 

2. 받은 자료를 DB에 입력하기 위한 인코딩 변환

다운받은 자료중 한글이 들어간 파일의 경우 아래와 같이 변환하여 저장을 진행합니다.

#!/bin/bash

TODAY=$(date +%Y%m%d)

#mkdir /home/abc/csv/$TODAY

 

 iconv -f euc-kr -t UTF-8 /home/acb/pub/$TODAY/ab01.txt > /home/misumi/csv/ab01_utf8.csv

 iconv -f euc-kr -t UTF-8 /home/abc/pub/$TODAY/ab02.txt > /home/misumi/csv/ab02_utf8.csv

매일같이 인코딩을 저장하여 DB입력하기 때문에 csv폴더에 이름변경 및 확장자 변환해서 저장해 둡니다.

 

 

3. 인코딩된 자료를 DB에 입력하기(2개로 이루어짐/위 : con_db.sh, 아래 insert_ab01)

#!/bin/bash

 

/usr/bin/mysql -h127.0.0.1 -P3307 -uUSER -pPASSWORD DB_Name

 

ALTER TABLE `ab01` DISABLE KEYS;\

LOAD DATA LOCAL INFILE '/home/abc/csv/ab01_utf8.csv' \

REPLACE INTO TABLE `ab01` \

FIELDS TERMINATED BY '|'\

LINES TERMINATED BY '\n'\

(ugubun, upchecd, bizno, crpno,......)\;

ALTER TABLE `ab01` ENABLE KEYS;

위 DB입력 방법은 LOAD DATA LOCAL을 사용하여 입력을 진행합니다. 

만약 데이터 입력시 업데이트인 경우 REPLACE를 INTO TABLE 앞에 써주어야 됩니다!

추가 : 용량이 큰 파일을 INFILE 할 경우 인덱싱해둔 key 때문에 입력이 늦어질 수 있으니 맨 앞에 alter table disable을 그리고 마지막에 enable 해주면 좋습니다. 

추가2 : LINES TERMINATED 에서 '\r'을 해주면 입력 파일의 csv에 공백엔터를 삭제해서 입력 해줍니다. 

추가3. ENCLOSED BY '\"' 을 사용하는 경우 한줄만 처리하고 빠져버릴수 있으니 제대로 안돌아가면 해당 옵션 체크

 

입력 방법은 파랑색 박스의 파일명이 con_db.sh 이고 아래 빨간색 박스의 파일명이 insert_ab01 인경우

$> /home/abc/inputDB/con_db.sh < /home/abc/inputDB/insert_ab01   

위와같은 방법으로 명령어를 입력하여 DB에 입력을 진행하였습니다.(스크립트를 잘 못해서 ㅠㅠ)

 

여튼 이런식으로 입력을 하면 FTP에서 파일 받고 인코딩 하고 DB입력하는 식으로 움직이게 됩니다.

자료중에 업데이트가 있는 경우 꼭 기본 데이터를 넣어주시고 REPLACE 명령어를 써주셔야 됩니다.

 

설치 완료후 Redash에서 스케쥴을 돌린결과 UTC기준으로 화면이 보여줘서 그거 수정하느라고 KST 로 바꾸니 스크립트들이 안움직여서 고생을 살짝 했었지만 일단은 UTC기준으로 전부 바꿔서 제대로 돌아가는거 확인하였고

Redash에서 스케쥴 부분은 UTC기준이므로 시간표현등을 9시간 전으로 확인하시면 될 듯 합니다.

(서버시간을 KST로 바꾼다고 해결되지 않음 ㅠㅠ)

 

 

추가. DB입력에 문제가 있는거 같아서 show porcesslist; 로 확인하니 lock time 같은 문구가 보여서 아래 블로그 참고(lock_time 시간을 기본 50초에서 길게 늘림)

URL : https://brunch.co.kr/@cg4jins/8

 

추가2. DB입력시 ERROR 1366 (22007) at line 1: Incorrect decimal value: '' for column 에러가 보이길래 확인해보니 값이 있어야 하는곳에 값을 넣지 않을 경우 생기는 문제라고 함 해결책은 기본값을 NULL 을 넣던가 0으로 치환해서 넣으라는 말이 있는데 보닌까 자동으로 0 값을 넣은거 같아서 무시하려고 함.(STRICT_TRANS_TABLES 모드)

참고URL : https://m.blog.naver.com/PostView.nhn?blogId=jevida&logNo=221578951138&proxyReferer=https:%2F%2Fwww.google.com%2F