[PHP] 보육정보공개API 연동 작업 후기 (시군구코드 mysql INSERT 문 첨부) , TRUNCATE, BULKINSERT, CRONTAB

사내에서 보유중인 데이터와 , 공개API 를 매칭하는 작업을 하였다.
아래 링크에서 API 에 대한 설명 및 메뉴얼을 볼 수 있다.

https://info.childcare.go.kr/info/oais/introduction/Intro.jsp

공개 API 개요 < 보육정보공개 API 소개 < 보육정보공개 API < 어린이집정보공개포털

회원가입은 필수이며, 개발계정 신청 및 심의가 있고 심의는 넉넉하게 하루정도 걸린다고 보면된다.

https://info.childcare.go.kr/info/oais/guide/Development.jsp

처음에 테스트 키로 발급받아서 호출할때 데이터들이 죄다 1,2,3,4 이런식으로 찍히는 문제가 있어 왜이런가 했더니
API 설명에 테스트 키는 원래 1,2,3,4... 식으로 찍힌다고 되있었다 -_- ;

여튼 운영계정 키 발급후 해당 키로 호출을 하니 정상적으로 데이터가 나오는것을 확인하였다.

# CHILDCARE_KEY = "발급받은 KEY";
# $arcode = "시군구코드";

#http://api.childcare.go.kr/mediate/rest/cpmsapi030/cpmsapi030/request%20?key=[발급받은 KEY]&arcode=11380
$url = 'http://api.childcare.go.kr/mediate/rest/cpmsapi030/cpmsapi030/request';
$url .= '?key='.CHILDCARE_KEY;
$url .= '&arcode='.$arcode;

위 형식의 링크이고 접속을 하면 아래처럼 각 동네별 원의 정보들을 쭉 보여준다.

시군구코드 테이블 정보와 데이터는 아래에 첨부해둔다
총 250개 행의 데이터가있다.

CREATE TABLE `sigunguCode` (
	`code` INT(5) NOT NULL,
	`name_do` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
	`name_sigun` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`zipcode_s` INT(5) NULL DEFAULT NULL,
	`zipcode_e` INT(5) NULL DEFAULT NULL,	
	PRIMARY KEY (`code`) USING BTREE,
	INDEX `zipcode_s` (`zipcode_s`) USING BTREE,
	INDEX `zipcode_e` (`zipcode_e`) USING BTREE	
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

시군구코드 mysql insert.txt

0.04MB

---

crontab 을 돌려 매일 밤 11시에 API 를 호출하여 우리쪽 테이블에 데이터를 쌓길 원했다.

#매시 20분 간격으로 실행 (20분, 40분)
20 */1 * * * curl -q [호출하고 싶은 페이지]

매시 20분마다 크론탭을 돌린건 맞지만 어차피 안에서 PHP Hour 별로 작업들이 다른것들도 돌아가기 때문에 상관없었다.

소스는 별거 없지만 본문을 그대로 첨부하긴 그래서 대충 요약하면 아래와 같다.

1. 시군구코드를 전부 한번씩 돌리며 (250번) api 를 호출해서 각 지역의 원 정보를 싹다 받아옴

2. 일일히 api 호출할때마다 insert 를 해주면 시간이 너무 오래 걸리기 때문에,
	루프가 돌때마다 $data 라는 배열에 array_push 를 해주고, 루프가 전부 다돌았을때 마지막에
    bulk insert 를 해줌

사실 위 2가지만 생각하면 별거 아닌데 그래도 중간중간에 로그를 쌓아서 기록을 해두거나, 정상적인 시군구코드라 하더라도, api 호출시에 해당 코드에 맞는 데이터가 없을수 있으니 예외처리 및 로그기록 그리고 continue 처리 필요

그리고 해당 API 를 매일매일 호출하면서 데이터를 받아오기 때문에 , 이미 쌓여있는 과거 데이터는 필요가없다.
처음엔 DELETE 를 생각하였으나 대략 5만개를 넘는 원의 정보를 받아오면서 하루에 auto_increment 가 5만씩 늘어나는셈, 데이터 형식은 INT 형이어서 최대값인 2147483647 에 도달하기까지는 한참뒤긴 하다만 그래도 어차피 한참뒤니까 상관없어 하고 방치하는건 내 스타일이 아니기도하고,  너저분한 시퀀스를 보느니 깔끔하게 매일매일 1 부터 들어가길 원했다.
그리하여 DELETE 가 아닌 TRUNCATE 로 정하였다. 테이블의 구조 (인덱스 등) 은 그대로 유지하되 데이터들은 초기화 되는게 딱 쓰이기 좋았다.

#DELETE FROM [테이블명];
TRUNCATE TABLE [테이블명];

이렇게 데이터는 잘 수집이 되었고 문제는 기존에 있는 데이터들의 원의 정보와 매칭을 하는거였는데 , 서브쿼리로도 조인해보고 그냥 일반 조인도 해보고 했었는데 (단순히 원명으로 매칭 시킬수 있는 조건이 절대 아니여서 , 원 전화번호 랑 우편번호로 매칭시킴) 속도가 너무 안나오길래 고민고민하다가 (대략 25~30초 걸림.. 의미없는 쿼리라 판단함)
번호가 중복으로 들어가있는 원도있어서 과감히 버릴 데이터는 버려야겠다 고 판단하였고 아예 조인을 시킬때 원 이름과 전화번호로 group by 를 시켜서 조인을 시키며 ON 절에 전화번호와 like 조건문을 추가하였다.

#기존에 있는 MEMBER 테이블과 JOIN
... 생략 ...

LEFT JOIN (
	SELECT crtelno,crcapat,crchcnt,crname
	FROM [API DATA 테이블]
	GROUP BY crname,crtelno
) AS C ON (
    crtelno = MEMBER.tel AND C.crname LIKE CONCAT("%", MEMBER.name, "%")
 )
 
 
 ... 생략 ...

ON 절에서 컬럼끼리 저렇게 like 로 비교해서 가져올 수 있는건 이번에 처음으로 사용 해본거였는데 나름 신선했다
좋은 경험한듯

group by 로 중복된 전화번호 데이터만 없애줘도 속도 개선에 큰 영향을 주었다. (0.3초 가량 소요)

아 마지막으로 API 에서 받아온 전화번호 형식은 '-' 하이픈이 추가된 상태였는데 (ex. 02-0000-0000)
기존에 내쪽에서 매칭해야되는 번호는 하이픈이 없는 상태라 .. 쿼리에서 replace 하면 되겠지 라고 생각했는데
이것도 상당한 시간이 소요되어서 , 아싸리 애초에 API 를 받아올때 bulk insert 문 만들때 str_replace 로 하이픈을 제거해서 진행하였다.

마지막으로 정말 당연한 이야기지만 확실히 쿼리 실행횟수는 줄일수록 좋다 ...