Bulk

Oracle 대량 Bulk Insert Tool 구성요소 Control File : sqlldr 명령행을 실행하기 위한 제어, 설정 파일 sqlldr : SQL*Loader, Oracle 데이터 Insert Tool Control File 기본 구성 OPTIONS (DIRECT=TRUE,ERRORS=100000,readsize=204800000) LOAD DATA CHARACTERSET AL32UTF8 INFILE 'infile/${CTL_INFILE}.csv' TRUNCATE INTO TABLE ${CTL_TABLE} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY " " TRAILING NULLCOLS ( ${CTL_TABLE_COLS} ) OPTIONS DIRECT ERRORS : 허용하는 에러수, default 50 DIRECT=TRUE : Direct Path, 쿼리를 실행하지 않고 메모리에 블록을 만들어 테이블에 저장 LOAD DATA INFILE : 데이터 파일, 다수의 파일 등록 가능 TRUNCATE INTO TABLE INSERT : 신규 데이터, 데이터 존재하면 에러 APPEND : 중복되지 않은 데이터 추가 REPLACE, TRUNCATE: 모든 행을 지우고 추가 FIELDS TERMINATED FIELDS TERMINATED : 필드 구분자 ENCLOSED BY " " : 텍스트 한정자 ( ${CTL_TABLE_COLS} ) 테이블 컬럼 리스트 DATE TIMESTAMP 이나 NULL 처리 등의 가공이 필요 할 수 있음 Control File Sample CREATE TABLE IPMAN ( IP varchar2(128), SERVERNAME varchar2(128), ETC varchar2(128) ) OPTIONS (DIRECT=TRUE,ERRORS=100000,readsize=204800000) LOAD DATA CHARACTERSET AL32UTF8 INFILE 'infile/IPMAN.csv' TRUNCATE INTO TABLE IPMAN FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY " " TRAILING NULLCOLS ( IP "NVL(:IP, ' ')" , SERVERNAME "NVL(:SERVERNAME, ' ')" , ETC "NVL(:ETC, ' ')" ) SQLLDR 실행 Control File 설정으로 SQL*Loader 실행 $ sqlldr 'dev/<PASSWD>@<SERVER>:1521/<SID>' control=ctl/IPMAN.ctl log=log/IPMAN.log bad=log/IPMAN.bad Control File 자동 생성 스크립트 ctl.sh : 테이블 이름으로 스키마를 쿼리하여 Control File 생성 및 sqlldr 명령어 출력 ctl.template : Control File 템플릿, envsubst 명령으로 내용 치환 ctl.sh #!/bin/bash if [[ -z "$1" ]]; then echo "$0 [TABLE_NAME]" exit -1 fi TABLE_NM="$1" echo "TABLE NAME: " $TABLE_NM FD_LIST=$(sqlplus -s ${CTL_CONN} << SQLEOF set pagesize 0 feedback off verify off heading off echo off SELECT (CASE WHEN DATA_TYPE = 'DATE' OR DATA_TYPE LIKE 'TIMESTAMP%' THEN COLUMN_NAME || ' TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF3" @' WHEN DATA_TYPE = 'NUMBER' THEN COLUMN_NAME || ' "NVL(:' || COLUMN_NAME || ', 0)" @' WHEN DATA_LENGTH > 255 THEN COLUMN_NAME || ' CHAR(65535) @' ELSE COLUMN_NAME || ' "NVL(:' || COLUMN_NAME || ', '' '')" @' END) COLUMN_NAME FROM user_tab_cols WHERE table_name = '${TABLE_NM}' AND column_id IS NOT NULL ORDER BY COLUMN_ID; exit; SQLEOF ) FD_LIST=`echo ${FD_LIST} | sed 's/@/,/g'` FD_LIST=${FD_LIST%*,*} export CTL_INFILE=$TABLE_NM export CTL_TABLE=$TABLE_NM export CTL_TABLE_COLS=$FD_LIST envsubst < ctl.template > ctl/$CTL_TABLE.ctl echo sqlldr \${CTL_CONN} control=ctl/${CTL_TABLE}.ctl log=log/${CTL_TABLE}.log bad=log/${CTL_TABLE}.bad ctl.template OPTIONS (DIRECT=TRUE,ERRORS=100000,readsize=204800000) LOAD DATA CHARACTERSET AL32UTF8 INFILE 'infile/${CTL_INFILE}.csv' TRUNCATE INTO TABLE ${CTL_TABLE} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY " " TRAILING NULLCOLS ( ${CTL_TABLE_COLS} ) 실행 예시 CTL_CONN DB 연결 환경변수 세팅 # infile : Data Directory / ctl : Control File / log : Log $ mkdir -p infile ctl log # DB 연결 변수 $ export CTL_CONN='dev/<PASSWD>@<SERVER>:1521/<SID>' 테이블 명으로 Control File 생성 및 sqlldr 명령어 출력 $ ./ctl.sh IPMAN TABLE NAME: IPMAN sqlldr ${CTL_CONN} control=ctl/IPMAN.ctl log=log/IPMAN.log bad=log/IPMAN.bad sqlldr 명령 실행 infile path : infile/테이블명.csv log= : 실행 로그 bad= : 실패 데이터 $ sqlldr ${CTL_CONN} control=ctl/IPMAN.ctl log=log/IPMAN.log bad=log/IPMAN.bad SQL*Loader: Release 21.0.0.0.0 - Production on Tue Oct 19 11:36:00 2021 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Path used: Direct Load completed - logical record count 200. Table IPMAN: 199 Rows successfully loaded. Check the log file: log/IPMAN.log for more information about the load.

SQL Server 대량 복사 프로그램 유틸리티(b ulk c opy p rogram utility, bcp) BCP https://docs.microsoft.com/ko-kr/sql/tools/bcp-utility{:target="_blank"} SQL Server 의 Bulk 대량 데이터 Export 및 Import 유틸리티 설치 Windows 다운로드 : Microsoft® Command Line Utilities 14.0 for SQL Server{:target="_blank"} Linux SQL Server 의 리눅스 지원으로 사용 가능 Linux에서 SQL Server 명령줄 도구 sqlcmd 및 bcp 설치{:target="_blank"} Export 기본 cvs 파일로 테이블(쿼리 데이터) Export $ bcp 사용법: bcp {dbtable | query} {in | out | queryout | format} 데이터 파일 [-m 최대 오류 수] [-f 서식 파일] [-e 오류 파일] [-F 첫 행] [-L 마지막 행] [-b 일괄 처리 크기] [-n 네이티브 유형] [-c 문자 유형] [-w 와이드 문자 유형] [-N 비텍스트 네이티브 유지] [-V 파일 형식 버전] [-q 따옴표 붙은 식별자] [-C 코드 페이지 지정자] [-t 필드 종결자] [-r 행 종결자] [-i 입력 파일] [-o 출력 파일] [-a 패킷 크기] [-S 서버 이름] [-U 사용자 이름] [-P 암호] [-T 트러스트된 연결] [-v 버전] [-R 국가별 설정 사용] [-k Null 값 유지] [-E ID 값 유지][-G Azure Active Directory 인증] [-h "힌트 로드"] [-x xml 서식 파일 생성] [-d 데이터베이스 이름] [-K 애플리케이션 의도] [-l 로그인 제한 시간] Export csv $ bcp "SELECT * FROM dbname.dbo.tablename" queryout tablename.csv -c -t "," -r "\n" -S <SERVER> -U <USER> -P <PASSWD> 테이블 지정 Export : out $ bcp "dbname.dbo.tablename" out output.csv ... 쿼리 실행 결과 Export : queryout $ bcp "query" queryout output.csv ... Export 데이터 형식 -n : 네이티브 포맷, SQL Server로 데이터 이전 할때 유용 (종결자 등의 이유로 데이터 이슈 해결) -c : 문자유형, OS의 기본 Charset을 따라감 한글 Windows : EUC-KR Linux : UTF-8 -w : 와이드 문자유형, 유니코드 UTF-16 LE Import $ bcp "dbname.dbo.tablename" in input.csv -c -t "," -r "\n" -S <SERVER> -U <USER> -P <PASSWD> 특이사항 길이가 0인 문자열의 경우 데이터 내에 NULL 문자를 삽입 SQL Server의 경우 문제는 없지만, 다른 DB 에 Import 의 경우 NULL 문자가 들어감 INSERT INTO tablename (filed) SELECT CHARACTER(0) -- MySQL 5.7 SELECT * FROM tablename WHERE filed = ''; -- Not Working SELECT * FROM tablename WHERE ASCII(filed) = 0; -- Working -- MySQL 8 SELECT * FROM tablename WHERE filed = ''; -- Working SELECT * FROM tablename WHERE ASCII(filed) = 0; -- Working