로그를 해석해 보는 것부터
데이터 분석을 주 업무로 하지 않는 분들도 이제는 SQL을 통해 직접 필요한 데이터를 추출하는 경우가 늘어나고 있습니다. SQL이 작성하기 어려운 것은 아니지만 그래도 처음 다루는 분들은 어려움을 겪을만한데요. SQL을 어떻게 하면 쉽게 작성하는지에 대해서는 제가 쓴 책에서 다루었기 때문에 이 부분을 여기서 나누지는 않겠습니다. 오늘 나눌 내용은 에러를 해결하는 방법입니다.
SQL 작성 후에 에러가 발생하고 에러를 고치면서 실력이 늘어나는 게 보통입니다. 따라서 에러가 났다고 해서 너무 당황하지 말고 누구나 겪는 과정으로 생각하고 이것을 통해 실력이 늘 수 있다는 생각을 갖는 것이 먼저 중요합니다. 사실 에러 수정이 어렵지도 않기 때문이죠. 책이나 강의에서는 잘 다루어주지 않는 SQL 상습 에러를 고쳐보는 활동을 해 봅시다.
먼저 로그를 봐야 합니다. SQL이 제대로 돌아가지 않고 아래 이상한 글자들만 나오면 당황하지 마시고 이상한 글자라고 생각되는 이 로그를 해석하면 에러를 고칠 수 있습니다. 이상한 글자들이 어디서 에러가 났는지 알려주고 심지어는 어떻게 고치면 좋을지도 제안하기 때문이죠. 그러기에 에러 로그를 어떻게든 보고 무슨 말을 하는지 이해하려고 해야 합니다. 이해가 되지 않는다면 특수한 테이블 명, 칼럼 명 등을 에러 로그에서 제외하고 에러 로그 첫 문장 자체를 구글링 하는 것도 방법입니다.
하지만 누구나 자주 실수하는 FAQ 같은 에러 로그들을 알고 있다면 문제 해결은 보다 쉬워집니다. 일종의 체크리스트를 통해 정형화된 에러 유형을 파악하는 것이죠. 어떤 환경에서 어떤 SQL을 사용하는지에 따라 조금씩 다르기는 하지만 오늘은 제 책에서도 다루었던 PostgeSQL을 DBeaver에서 다루는 환경에서 많은 초심자분들이 자주 하는 에러들을 다루어 보겠습니다. 혹시 여기 해당하는 에러였는지 에러 메시지를 보고 오신 분들은 찾아보시면 문제 해결에 도움이 될 것 같네요.
쉼표 누락 혹은 마침표 기입
돌아보면 당연한 것인데 작성한 쿼리가 너무 길면 작은 부분의 실수를 찾는데 너무 많은 시간이 걸립니다. 위 이미지의 에러에서 핵심은 Error position 부분입니다. 일단 어디서 에러가 났는지 알아야죠. line 13, 즉 13번째 줄에서 에러가 났다는 것입니다. 월리를 찾아라 수준의 복잡한 쿼리에서도 위치를 찾았다면 일단 절반은 한 것입니다. 13번째 줄은 그럼 어디일까요? 라인을 알려주는 기능을 켜지 않았다면 찾아서 라인을 찾아봅시다.
이제 13번째 줄이 어디인지 보입니다. select절에 rating 칼럼을 지정한 부분인데요. 자세히 보면 칼럼과 칼럼 사이에 쉼표가 누락된 것을 알 수 있습니다. rating 앞에 쉼표가 있어야 하죠. 이렇게 누군가의 에러를 보면 쉬운데요. 막상 내 쿼리에 난 쉼표는 당황해서 찾지 못할 때가 있습니다. 특히 철썩 같이 믿고 어딘가에서 복사해서 쓴 쿼리에 쉼표가 누락되었다면 해당 부분을 의심하기는 쉽지 않은데요. 로그를 보고 모든 부분에 의심하면서 고치는 게 중요합니다.
그런데 이번에는 고쳤는데도 에러가 나옵니다. 쉼표가 아니라 마침표를 잘못 쓴 경우죠. 바쁘다 보면 타이핑을 잘못해서 이렇게 쉬운 에러가 나지만 한동안 찾지 못해 찾고 나면 헛웃음 날 때가 생기곤 합니다. 누구나 그런 시기를 지납니다.
띄어쓰기
syntax error. 문법 오류에서 쉼표만큼 허탈하게 많이 나오는 것이 띄어쓰기입니다. SQL은 중요한 부분을 정확하게 끊어서 표현하지 않으면 사람처럼 대강 이런 단어를 쓰려고 했구나 하면서 인식하지 못합니다. 다행인 점은 Python처럼 대소문자 구분을 하지 않는 점 정도네요.
위 이미지에서는 테이블명과 제한된 관측치만 보여주는 limit가 띄어쓰기가 제대로 되지 않아 에러가 나왔습니다. 급하게 타이핑하다가 자주 생기는 에러인데요. 로그에서는 에러가 난 위치만 나올 뿐 띄어쓰기인지 쉼표인지 에러의 원인은 반복된 패턴으로 스스로 찾아야 합니다.
순서와 규칙
SQL 작성이 처음인 분들이 많이 겪는 에러인데요. select, from, where, group by, order by, having 등의 순서를 바꿔서 쓰면서 에러가 나는 경우입니다. 아래 이미지를 보시죠.
11번째 줄에 에러가 발생했다고 알려줍니다. SQL 작성의 순서를 모른다면 group by의 철자가 틀렸는지, 어딘가 구두점을 누락했는지를 생각하다가 답을 찾지 못할 것입니다. 여기서 에러의 원인은 order by가 group by 보다 뒤에 쓰여야 하는데 둘의 순서가 바뀌어서 나온 에러입니다. SQL은 작성에 지켜야 할 순서가 있는데요. from절을 쓰고 그 뒤에 조건절인 where를 쓰는 것 등이죠.
순서를 고쳤지만 여전히 에러가 나옵니다. order by 즉, 출력 순서를 나타내는 기준을 hp 칼럼으로 지정했는데 7행에서 보이듯 select 절에는 정작 hp가 없군요. hp가 출력되지도 않는데 hp의 내림차순(desc)으로 보여줄 순 없겠죠. 그러기에 order by를 select절에 있는 years로 바꾸어봅니다.
하지만 이번에도 에러가 나왔습니다. desc 부근에 오류가 있다고 로그에 나오는데요. 가만히 보니 10행에 desc가 불필요하게 들어가 있군요. group by에는 asc, desc 등 순서가 필요하지 않으니까요. group by에는 그룹으로 보여줄 칼럼만 입력하면 되는데 desc가 들어가서 에러가 나온 것이었습니다. 다시 수정해 보죠.
앗, 여전히 에러가 나옵니다. 이번에는 로그에 집계함수는 group by 절에 사용할 수 없다고 나옵니다. 그러고 보니 group by에 2라고 쓰여 있는 부분, 즉 select 결과에 두 번째로 나오는 칼럼을 기준으로 그룹으로 묶으라고 했지만 두 번째는 count(*)로 묶이는 값이지 묶는 기준이 될 수 없는 값입니다. 여기서 묶을 수 있는 것은 첫 칼럼인 years 뿐이죠. group by 뒤에 years를 쓰거나 1로 고칩니다.
이렇게 수정하고 다시 실행을 하니 이번에는 위 이미지처럼 결과가 나왔습니다. 이것은 에러인가요? 아닙니다. 기입한 조건에 해당하는 행이 없다는 것이죠. 이것이 원하는 결과가 아니었다면 where절에 있는 조건인 hp > 200 and torque > 10을 수정하면서 원하는 값을 찾아봅니다. 에러와 결측과 0은 모두 다른 것입니다.
alias와 on
여러 테이블을 모아서 처리를 할 때 테이블마다 별명을 붙여줍니다. order라는 테이블을 o, customer라는 테이블을 c로 별명을 붙이는 것처럼요. 하지만 여러 테이블을 join 하면서 데이터를 처리하다가 테이블 별명에 그 칼럼이 없는 경우 에러가 발생하기도 합니다. 비슷한 구절이 반복되는 쿼리에서 복사와 붙여 넣기로 쿼리를 작성하다가 o.price라는 칼럼을 썼는데 다른 쿼리에서는 o가 별명인 테이블이 이미 있어서 중복 문제가 벌어지거나 여기서는 order가 o가 아닌 or로 별명으로 되어 o.price가 무엇인지 컴퓨터가 모르는 상황이 벌어지는 것이죠. 많은 테이블을 붙이면서 긴 작업을 할 때 그래서 테이블 별명을 되도록이면 모두 같게 만들도록 다소 복잡하게 작성하는 것이 좋습니다. as a, as b, as c…. 이런 식으로 별명을 붙이면 나중에 뒤범벅이 되어 버리죠.
데이터 타입
데이터 타입이 문제가 되는 경우도 있습니다. 사람 눈에는 같게 보이지만 어떤 테이블에서는 그게 문자로, 어떤 테이블에서는 숫자로 데이터 타입이 지정되어 있다면 join이 되지 않겠죠. 예를 들어, 고객번호를 나타내는 cust_no라는 칼럼이 customer 테이블에는 varchar로 order 테이블에는 integer로 지정되어 있다면 같은 ‘7809081221’이지만 테이블마다 이걸 문자, 즉 모양으로 인식하거나 숫자인 더하고 빼는 것이 가능한 것으로 인식하는 차이가 있습니다.
from customer as c
left join order as o on c.customer = o.customer
이렇게 두 테이블을 join 하려고 하면 제대로 작동되지 않습니다. ‘7809081221’ = 7809081221 가 성립할 수 없기 때문이죠. 그럴 때는 함수를 통해 두 칼럼의 데이터 타입을 동일하게 바꾸어야 작업이 진행됩니다. convert, cast, to_char 등과 같은 함수를 사용해 같은 타입으로 바꾸고 작업을 진행하면 에러가 나지 않습니다.
가끔 데이터 변경
드문 일이기는 하지만 쿼리에서 다루는 테이블명이 바뀌거나 칼럼이 수정 삭제되면서 쿼리가 작동하지 않는 일들이 있습니다. order 테이블이 order_adj 테이블로 6/1일 자로 모두 바뀐다면 order 테이블에서 6/1일 이후 작업하는 값은 제대로 된 값이 있지 않아 어려움에 처할 수 있죠. 이런 문제는 회사 내부의 데이터 관리 이슈때문인데요. 아무리 테이블이나 칼럼의 변경에 대해 공지한다고 해도 너무 많기도 하고 관련이 그 당시에는 없었을 때는 무관심했던 내용들이 갑자기 관련 일을 할 때 떠올라 문제가 생기기도 합니다. 이를 어떻게 바꾸어야 하는지는 별개의 아티클에서 다루기로 하고 이런 문제도 있어서 안 돌아가는 점을 나눕니다.
연산 함수 검증은 raw data로
여러 테이블을 join해서 sum, count 같은 연산 함수를 사용해 결과를 얻는 경우 join이 의도대로 되지 않으면서 오류가 나는 경우가 있습니다. 1:1로 join되어야 하는데 알고보니 여러 개의 행과 join되면서 과대하게 연산되는 경우 등 말이죠. 테이블에 대해 자세히 알지 못하면 고유값을 기준으로 join 하는 것이 제대로 구햔되지 않을 수 있고 그러면 값이 터무니 없이 나오게 되죠. 이럴 경우 연산을 풀고 개별 케이스를 조건에 넣어 join이 어떻게 되고 있는지 살펴보는 것으로 검정을 해 봅니다.
select ord_date, rgn, count(b.cust_id) as cust_cnt
from order as a
left join cust as b on a.cust_id = b.cust_id
위와 같이 주문 일자별로 지역별 고객 수를 구하는 쿼리를 작성한다고 하면 여기 join에 해당하는 order와 cust 두 테이블에서 교집합이 되는 컬럼은 cust_id라는 컬럼 외에는 없어야 1:1 join이 됩니다. 만약 그렇지 않다면 과대 혹은 과소 집계 될 수 있죠. 그런데 결과가 일자별 지역별 주문 고객 수가 터무니 없이 높다면 join을 하는 key가 잘못 설정된 것입니다. 이럴 때는 연산함수를 풀고 각 행을 그대로 보는 게 도움이 됩니다.
select ord_date, rgn, cust_id
from order as a
left join cust as b on a.cust_id = b.cust_id
이렇게 연산 함수 없이 그대로 볼 때 만약 동일한 일자, 지역에 여러 개의 cust_id가 존재한다고 하면 고유한 값을 출력하기 위해 다른 조건을 join에 추가하거나 distinct 같은 함수를 사용해 의도한 값을 구현해 냅니다.
select ord_date, rgn, cust_id
from order as a
left join cust as b on a.cust_id = b.cust_id
where cust_id = ‘3490990911’
만약 행이 너무 많아서 눈에 잘 들어오지 않는다면 이렇게 하나의 조건으로 여러 개의 행이 존재해서 결합하는지 보는 것도 도움이 됩니다.
이 외에도 많은 케이스가 있을 것 같은데요. 먼저 생각나는 것으로만 나눠보았습니다. 중요한 것은 에러는 로그로 해결할 수 있고 로그는 정형화되어 있어서 평소 내가 자주 하는 실수가 있다면 그것을 중심으로 살펴보고 정말 모른다면 에러 로그의 고유명사를 제거하고 구글링 한다는 프로세스에 있는 것이죠. 작지만 도움 되기를 바랍니다.
PETER님이 브런치에 게재한 글을 편집한 뒤 모비인사이드에서 한 번 더 소개합니다.
