데이터 이관
데이터 이관은 크게 이기종간, mariadb 간 이관할 때의 방법을 기술하였다. oracle에서 mariadb로 데이터 이관시에 사용했던 방법은 mariadb의 connect engine을 활용한 방법과 pentaho를 활용하는 방법이 있다. connect engine은 테이블별로 엔진을 설정하여 간단히 사용할 수 있지만 테이블에 text, blob type이 있는 경우엔 생성이 되지 않는다. 이때엔 pentaho를 활용하면 된다. 추가적으로 mariadb 간에 데이터 이관의 경우 오라클처럼 dblink가 없지만 대신 federate를 활용하여 이관할 수 있다.
1) 이관할 대상 선정(Table, Index, fk, procedure, function 등)
2) 대상에 대한 sql 추출(패키지 혹은 툴 이용)
3) 데이터 타입 매핑표에 따라 MariaDB에 맞게 테이블 변환
4) MariaDB에 database 생성
- oracle의 owner가 mariadb에선 database(스키마)로 생성이 됩니다.
5) 테이블+pk 생성
6) 데이터 이관
- connect engine 활용
- pentaho 활용
- sql loader 이용하여 excel 형태를 mariadb로 이관
7) index 생성, fk 생성
- 소규모의 경우 table에 index 있는 상태에서 data 넣어도 괜찮지만, 데이터가 크면 테이블+PK만 만든 상태에서 DATA 넣고 이후에 INDEX 생성하는게 더 빨리 생성됩니다.
8) 기타 오브젝트(함수,프로시져) 생성의 경우 MariaDB에 문법으로 전환하기 때문에 이 부분은 협의가 필요하며, 성능, 관리의 측면상 프로시져나 함수는 응용에서 처리하는 것으로 가이드하고 있습니다.
connect engine 구성 방법
1) instantclient 파일 작업
[masvc@TESTDB] unzip instantclient-basic-linux.x64-12.1.0.1.0.zip
[masvc@TESTDB] unzip instantclient-odbc-linux.x64-12.1.0.1.0.zip
[masvc@TESTDB] unzip instantclient-sdk-linux.x64-12.1.0.1.0.zip
2) odbc 설치
[root@TESTDB ~] yum install unixODBC
[root@TESTDB ~] yum install unixODBC-devel
[root@TESTDB unixODBC-2.3.4]/engn001/masvc/scripts] odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /engn001/masvc/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
3) /etc/odbc.ini 설정
[root@TESTDB unixODBC-2.3.4]# cat /etc/odbc.ini
[ORCL]
Driver = Oracle 12c ODBC driver
ServerName = ORCL
DSN = ORCL
UserName = system
Password = oracle
4) /etc/odbcinst.ini 설정
[root@TESTDB unixODBC-2.3.4]# cat /etc/odbcinst.ini
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1
아래 내용 추가
[Oracle 12c ODBC driver]
Description = Oracle ODBC driver for Oracle 12c
Driver = /engn001/masvc/maria/connect/instantclient_12_1/libsqora.so.12.1
5) /etc/oracle/tnsnames.ora 파일 생성 및 설정
[root@TESTDB unixODBC-2.3.4]# cat /etc/oracle/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORCL)
)
)
6) mariadb os 유저 .bash_profile 에 아래 내용 추가
(masvc user)
export CLIENT_HOME=/engn001/masvc/maria/connect/instantclient_12_1
export LD_LIBRARY_PATH=/usr/lib:/usr/local/lib:/usr/lib64:$CLIENT_HOME
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export TNS_ADMIN=/etc/oracle
7) oracle db 접속 테스트
[masvc@TESTDB script]$ isql -v ORCL system oracle
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
8) library link
$ cd /usr/lib64
$ ln -s libodbc.so.2.0.0 libodbc.so.1
9) mariadb connect plugin 설치 및 확인
MariaDB [(none)]> INSTALL SONAME 'ha_connect';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | NULL | NULL | NULL |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CONNECT | YES | Management of External Data (SQL/MED), including many file formats | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | NO | Archive storage engine | NULL | NULL | NULL |
| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.01 sec)
10) 재기동
11) 테이블 생성 및 확인
MariaDB [testdb]> create table test_Table2(name varchar(100)
-> ) ENGINE=CONNECT DEFAULT CHARSET=utf8 COLLATE=utf8_bin CONNECTION='DSN=ORCL;UID=system;PWD=oracle' `TABLE_TYPE`=ODBC `tabname`='test_Table2'
->
-> ;
Query OK, 0 rows affected (0.04 sec)
MariaDB [testdb]> select * from test_table2;
+-----------+
| name |
+-----------+
| 홍길동 |
| name |
| 춘향이 |
+-----------+
3 rows in set (0.11 sec)
참고1) mariadb의 테이블에 text, blob 컬럼이 없을 경우 사용한다.
사용 시 아래의 파라미터를 적용하면 조건절에 해당하는 row만 찾아서 가지고 온다.
SET global optimizer_switch='engine_condition_pushdown=ON';
참고2) 플러그인 삭제
MariaDB> UNINSTALL PLUGIN CONNECT;
참고3) connect engine 이용 my.cnf 설정 파일 연결
create table mysql_config (
section varchar(64) flag=1,
keyname varchar(64) flag=2,
value varchar(256))
engine=connect table_type=ini file_name='/engn001/masvc01/mysql/my.cnf'
option_list='Layout=Row;seclen=90000';
참고4) connect engine 이용 운영체제의 디렉터리(/data001/masvc01 이하) 연결
create table temp_dir (
path varchar(256) not null flag=1,
fname varchar(256) not null,
ftype char(4) not null,
size unsigned double(12,0) not null flag=5)
engine=connect table_type=dir file_name='/data001/masvc01/*'
option_list='subdir=1';
federate engine 구성 방법
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | YES | YES |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| Mroonga | YES | CJK-ready fulltext search, column store | NO | NO | NO |
| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| CONNECT | YES | Management of External Data (SQL/MED), including many file formats | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
13 rows in set (0.00 sec)
source DB :
create server 'server_one' foreign data wrapper 'mysql' options
(HOST 'remoteip',
DATABASE 'testdb',
USER 'username',
PASSWORD 'password',
PORT 3310,
SOCKET '',
OWNER 'username');
CREATE TABLE testdb.test_federate (
`id` int(20) NOT NULL,
`name` varchar(64) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='server_one';
excel upload?
한글입력시.. csv 파일 메모장으로 열어서 utf8로 바꾸고 넣어야 합니다.
example :
LOAD DATA LOCAL INFILE 'C:\\Users\\Desktop\\employee_20160303.csv' INTO TABLE TB_EMPLOYEE
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;