How to Import CSV Data into a SQL Database (Step by Step)

저는 화요일 오후 11시에 동료가 저에게 전화했을 때 그녀의 목소리에서 느껴지는 공황을 아직도 기억합니다. "분기 판매 데이터가 데이터베이스에 로드되지 않아요. 그리고 이사회 회의는 내일 오전 8시입니다." 그녀는 47,000행 짜리 CSV 파일을 SQL Server 인스턴스로 가져오려고 3시간을 노력했지만, 모든 시도는 조용히 실패하거나 이상한 인코딩 문제로 데이터가 손상되었습니다. 제가 제대로 된 가져오기 프로세스를 안내했을 때(12분 밖에 걸리지 않았습니다) 그녀는 그렇게 중요한 것이 이렇게 이해하기 힘들 수 있다는 것에 안도하면서도 동시에 불만을 느꼈습니다.

그날 밤은 제가 데이터베이스 아키텍트로서의 14년 동안 관찰한 것을 명확히 해주었습니다. CSV 가져오기는 가장 일반적인 데이터베이스 작업 중 하나이자 가장 자주 실패하는 작업 중 하나입니다. 저는 포춘 500대 기업들이 가져오기 실패로 인해 오후의 대부분을 잃는 것을 보았고, 스타트업이 잘못된 데이터로 인해 운영 데이터베이스를 손상시키는 것을 보았으며, 뛰어난 개발자들이 네이티브 SQL 도구가 완벽히 작동했을 때 취약한 파이썬 스크립트를 작성해야 하는 경우를 보았습니다. 문제는 CSV 데이터를 가져오는 것이 본질적으로 어려운 것이 아니라, 대부분의 사람들이 95%의 일반적인 문제를 방지하는 체계적인 접근 방식을 배우지 못했다는 것입니다.

이 포괄적인 가이드에서는 MySQL, PostgreSQL, SQL Server, Oracle 데이터베이스를 통한 수천 건의 성공적인 가져리기를 통해 개선된 정확한 방법론을 공유하겠습니다. 고객 기록, 재무 거래 또는 IoT 센서 데이터를 가져가든지 간에 이 원칙들은 문제 해결에 소요되는 시간을 절약해 주고 아마추어 가져오기에서 발생하는 비용이 많이 드는 실수를 피하는 데 도움을 줄 것입니다.

CSV 가져오기가 실패하는 이유 및 이를 방지하는 방법 이해하기

기술적인 단계로 들어가기 전에 해결해야 할 큰 문제를 다루겠습니다. 왜 이렇게 많은 CSV 가져오기가 실패하거나 손상된 데이터를 생성할까요? 중소기업의 데이터베이스 운영을 감사하면서 발견한 바에 따르면 가져오기 실패의 약 68%가 적절한 준비로 완전히 방지할 수 있는 세 가지 근본 원인에서 비롯된다는 것입니다.

첫 번째 원인은 인코딩 불일치입니다. CSV 파일이 UTF-8로 인코딩되었지만 데이터베이스에서는 Latin1을 기대한다거나, 그 반대의 경우도 있을 수 있습니다. 저는 고객 이름이 프로덕션 데이터베이스에서 난해하게 보이는 이유를 디버깅하느라 이틀을 보낸 적이 있는데, 마케팅 팀의 Excel 내보내기가 Windows-1252 인코딩으로 기본 설정 되어 있었고 저희 PostgreSQL 데이터베이스는 UTF-8로 설정되어 있다는 것을 발견했습니다. 문제가 발견된 후 솔루션을 찾는 데 30초밖에 걸리지 않았지만, 이틀 동안의 시간은 회사에 약 8,000달러의 개발자 비용을 초래했고 주요 제품 출시가 지연되었습니다.

두 번째 주요 문제는 데이터 유형 불일치입니다. CSV에 "N/A"라는 문자열이 있지만, 데이터베이스에서는 해당 열이 정수로 되어야 합니다. 또는 데이터베이스가 "2023-12-25"의 형식일 때 "12/25/2023"로 날짜가 형식화되어 있을 수 있습니다. 이러한 불일치로 인해 가져오기 작업이 전혀 실패하거나, 더 나쁜 경우에는 부분적으로 성공하더라도 손상된 데이터를 가져옵니다. 한 경우에서는 40,000행이 성공적으로 가져왔지만, 3,000행은 조용히 실패하여 재무 조정 중 몇 주 후에 데이터의 공백을 발견하게 되었습니다.

세 번째 일반적인 실패 지점은 제약 조건 위반입니다. 데이터베이스에는 외래 키 제약, 고유 제약 조건 또는 체크 제약 조건이 있으며, CSV 데이터가 이를 위반합니다. 아마도 고객 테이블에 존재하지 않는 고객 ID를 참조하는 주문 기록을 가져오려고 하고 있거나, 이메일 주소를 가져오고 있지만 그 중 200개는 중복이며 이메일 열에 고유 제약 조건이 설정되어 있을 수 있습니다. 가져오기를 시도하기 전에 데이터베이스 스키마의 제약 조건을 이해하는 것은 절대적으로 중요합니다.

좋은 소식은 이러한 실패 모드를 이해하면 그러한 문제를 포착하고 우아하게 처리할 수 있는 가져오기 프로세스를 설계할 수 있다는 것입니다. 제가 곧 공유할 방법론은 데이터베이스를 손상시키기 전에 이러한 문제를 식별하는 검증 단계를 포함하고 있으며, 잘못된 가져기를 롤백해야 하는 악몽이나, 더 나쁜 경우에는 몇 달 후에 손상된 데이터를 발견하는 일을 피할 수 있습니다.

가져오기 성공을 위한 CSV 파일 준비하기

제가 CSV 가져오기에 대해 배운 가장 중요한 교훈은 이것입니다: CSV 파일을 준비하는 데 소모된 시간은 실패한 가져기를 문제 해결하는 데 소모되는 시간을 절약하는 것입니다. 적절한 CSV 준비에 투자하는 10분마다 최소 30분의 디버깅 시간을 절약할 수 있습니다. 수많은 재앙을 방지했던 제 사전 가져오기 체크리스트를 안내해 드리겠습니다.

첫째, CSV 구조를 신중하게 검토하십시오. Excel이 아닌 텍스트 편집기에서 파일을 열고(Excel은 데이터를 조용히 수정할 수 있음) 기본 사항을 확인하세요. 헤더 행이 있습니까? 필드가 일관되게 쉼표로 구분되어 있습니까, 아니면 탭, 파이프 또는 세미콜론을 사용해야 합니까? 저는 유럽의 한 고객과 작업할 때 그들의 "CSV" 파일이 세미콜론을 구분 기호로 사용하고 있었고, 이는 많은 유럽 지역에서 쉼표가 소수 구분 기호이기 때문에 발생한 일입니다. 쉼표 구분 기호를 가정함으로써 그들의 첫 세 가지 가져오기 시도가 극적으로 실패하게 되었습니다.

다음으로, 내포된 구분 기호와 줄바꿈을 확인하십시오. CSV에 쉼표나 줄 바꿈이 포함된 텍스트 필드가 있다면, 해당 필드는 적절하게 따옴표로 묶여 있어야 합니다. 예를 들어, "123 Main St, Apt 4"라는 주소 필드가 있다면, 해당 쉼표는 전체 값이 따옴표로 감싸지지 않는 한 필드 구분 기호로 해석됩니다: "123 Main St, Apt 4". 내포된 구분 기호가 포함된 모든 필드가 적절하게 따옴표로 묶여 있는지 확인하기 위해 CSV 검증 도구를 사용하거나 빠른 스크립트를 작성하는 것이 좋습니다. 기억에 남는 한 경우에는 고객의 제품 설명 필드에 따옴표가 없는 쉼표가 포함되어 있어서 모든 행의 열이 잘못 정렬되어 100% 가져오기 실패를 초래했습니다.

셋째, 가져오기 전에 데이터 형식을 표준화하십시오. 날짜는 파일 전반에 걸쳐 일관된 형식을 따라야 하며, 가능하면 모든 데이터베이스 시스템에서 모호하지 않은 ISO 8601 형식(YYYY-MM-DD)을 사용하는 것이 이상적입니다. 숫자는 일관된 소수 구분 기호를 사용해야 하며, 불리언 값은 TRUE/FALSE, 1/0 또는 데이터베이스가 기대하는 어떤 규칙든지 표준화해야 합니다. 저는 일반적으로 형식을 표준화하기 위해 15-20분 동안 찾기-바꾸기 작업을 합니다. 이는 가져오기 후 데이터 정리 시간을 방지할 수 있습니다.

넷째, NULL 값을 명시적으로 처리하십시오. 서로 다른 시스템은 NULL을 다르게 표현합니다 — 일부는 빈 문자열을 사용하고, 일부는 "NULL"이라는 리터럴 텍스트를 사용하며, 일부는 "N/A"나 "-"를 사용할 수 있습니다. 규칙을 결정하고 일관되게 적용하세요. 대부분의 데이터베이스 가져오기 도구는 어떤 문자열이 NULL로 해석되어야 하는지를 지정할 수 있습니다. 제 가져기 작업에서는 일반적으로 NULL에 대해 빈 문자열을 사용하고 가져오기 도구를 이에 맞게 설정하지만 특정 선택보다 일관성이 더 중요합니다.

마지막으로, 인코딩을 검증하십시오. Unix 시스템의 `file`와 같은 도구를 사용하거나 인코딩 정보를 표시하는 텍스트 편집기를 사용하여 CSV의 문자 인코딩을 확인하세요. 국제 데이터와 작업하는 경우 UTF-8은 거의 항상 올바른 선택입니다. 파일이 다른 인코딩이라면 가져오기 전에 변환하세요. 저는 리눅스에서 `iconv`를 사용하거나 윈도우에서는 PowerShell의 인코딩 변환 기능을 사용합니다. 이 단일 단계가 제 경력에서 다른 어떤 것보다 더 많은 가져오기 실패를 방지했습니다.

올바른 구조로 대상 테이블 만들기

많은 개발자들이 서두르기도 하여 대상 테이블을 만들고, 그 후에 잘못된 스키마 디자인으로 인한 가져오기 실패를 해결하는 데 몇 시간을 허비하는 실수를 저지릅니다. 저는 테이블 구조를 설계하는 데 충분한 시간을 투자하는 것이 중요하다는 것을 배웠고, 이는 가져오기 프로세스 및 장기 데이터 품질에 걸쳐 보상을 줍니다.