참조 : https://gist.github.com/gaerae/6219678

SELECT * FROM my_table
INTO OUTFILE 'my_table.csv'
CHARACTER SET euckr
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'

SELECT * FROM (
    (
        SELECT
            '필드1' AS 'filed_1',
            '필드2' AS 'filed_2'
    ) UNION (
        SELECT
            filed_1,
            filed_2
        FROM my_table
    )
) AS mysql_query
INTO OUTFILE 'my_table.csv'
CHARACTER SET euckr
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'

mysql -p my_db -e "SELECT * FROM my_table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > my_table.csv

#!/bin/bash
 
db=YOUR_DB
user=YOUR_USER
pass=YOUR_PASS
 
for table in $(mysql -u$user -p$pass $db -Be "SHOW tables" | sed 1d); do
  echo "exporting $table.."
  mysql -u$user -p$pass $db -e "SELECT * FROM $table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > $table.csv
done

저는 mysql 8를 사용 중입니다.
1번 쿼리를 사용해보니 'my_table.csv' 파일명에 my.ini의 secure-file-priv 경로를 같이 적어야 정상적으로 파일이 export 되네요.

데이터 건 수(몇 십만건)가 많을 경우에는 3번 방법을 사용하면 안될 것 같아요. 데이터 건마다 치환 작업을 해주어야 하니 오랜 시간이 걸립니다.

# 참조 : https://cafe-de-java.tistory.com/4

# 참조 : https://tommypagy.tistory.com/80

한글문제 ) locale -a export LANG="ko_KR.UTF-8" 

티베로에서 쿼리 결과를 CSV 파일로 만드는 스크립트이다.

Shell Script에서 Query String 에 "*" Asterisk 문자가 들어가는 경우 주의해서 처리가 필요하다.

(다운로드 : csv_conver_shell파일.txt

※ 주의 : Asterisk 처리할 때 <더블 쿼테이션><싱글 쿼테이션><아스테리크><싱글 쿼테이션><더블 쿼테이션> 순서로 작성할 것

$ cat csv_convert.sh

#!/bin/sh

CSV_FILE="/home/tibero/work/out.csv"

QUERY_STR="select "'*'" from all_tables;"

SQL_COMMAND="tbsql -s sys/tibero"

RESULT=`$SQL_COMMAND <<EOF

set pagesize 0 feedback off ver off heading off echo off

set pages 0 colsep ',' trimspool on lines 30000 termout off

spool $CSV_FILE

$QUERY_STR

spool off

quit

/

EOF

`

참고로 Asterisk 문자를 Shell Script에서 Query String안에 들어있는 경우 echo 처리 부분은 아래 박스를 참고할 것

$ QUERY_STR2="SELECT "'*'" FROM ALL_TABLES"

$ echo $QUERY_STR2

SELECT csv_convert.sh out.csv FROM ALL_TABLES  ==> Asterisk문자가 현재 디렉토리의 파일 목록으로 대체된다.

$ echo "'$QUERY_STR2'"

'SELECT * FROM ALL_TABLES'

$ echo "$QUERY_STR2"

SELECT * FROM ALL_TABLES

select 문장에 asterisk 가 들어가는 경우 shell script안에 변수로 처리하기가 까다토울 수 있다.

그래서 tbsql 툴을 사용해서 shell script를 짤 경우에는 @를 이용해서 외부 파일을 실행하는 방법으로 스크립트 작성이 편리해 보인다.

# 참조 : https://cbwstar.tistory.com/110

티베로에서 postgresql로 데이타 이관하는 소스 입니다.

파이션으로 만들었습니다.

80만건 되는 데이터를 엑셀로 다운받아서 해볼려고 했는데 엑셀로 80만건 내려받는데 20분이 넘게 걸려서 포기하고

파이쎤으로 만들어 볼까 하고 만들었는데 외상예로 처리 속도가 빨라서 jdbc만 지원하는 모든 데이터 베이스 이관은 이것 하나 응용하면 끝날것 같아서 필요 하신분 있을것 같아서 소스 공개합니다.

80만건 티베로에서 postgresql로 이관시 2분도 안 걸렸습니다.

소스 로직도 간단 합니다. db접속해서 원하는 데이터 쿼리로 가져와서 그냥 sqlalchemy 라이브러리 사용해서 밀어 넣으면 끝입니다.

from sqlalchemy import create_engine
import sqlalchemy.types as sql_types
import jaydebeapi as jp
import pandas as pd
import time

tibero_JDBC_Driver = 'd:\import_trans_erd\python\jdbc\\tibero6-jdbc.jar'
drivers = [tibero_JDBC_Driver]


def postgres_connect(user, password, db, host, port=5432):
    url = 'postgresql://{}:{}@{}:{}/{}'.format(user, password, host, port, db)
    engine = create_engine(url, client_encoding='utf8',
                           executemany_mode='batch')
    return engine


def tibero_connect():
    conn = jp.connect('com.tmax.tibero.jdbc.TbDriver',
                      'jdbc:tibero:thin:@192.168.1.19:9027:naqs', ['id', 'password'], jars=drivers)
    return conn


user = 'id'
password = 'pw'
db = 'naqs'
host = '192.168.1.26'

query = """
    SELECT *
      FROM TABLE
    """

start_time = time.time()
tb_conn = tibero_connect()
df = pd.read_sql_query(query, tb_conn)

pg_engine = postgres_connect(user, password, db, host)
start_time1 = time.time()

print(df.columns)

df.to_sql('table_name_tmp',
          schema='test',
          con=pg_engine,
          if_exists='replace',  # {'fail', 'replace', 'append'), default 'fail'
          chunksize=1000,
          index=False,
          method='multi',
          dtype={
              'LGN_ID': sql_types.VARCHAR(20),
              'REG_TM': sql_types.TIMESTAMP,
              "UPD_TM": sql_types.TIMESTAMP,
              "LAST_LOGIN_DT": sql_types.TIMESTAMP,
              "CONFIRM_DT": sql_types.TIMESTAMP,
              "CANCEL_DT": sql_types.TIMESTAMP,
              "SECSN_DT": sql_types.TIMESTAMP,
              "INFO_AGREE_DT": sql_types.TIMESTAMP

          }
          )

tb_conn.close()

print("Total  : %s seconds" % (time.time() - start_time))
print("to_sql : %s seconds" % (time.time() - start_time1))

오류.참고.리눅스 파이썬 psycopg2 패키지 설치 오류 해결 )

파이썬 3.8버전 기준 리눅스에서 테스트

관련 의존성 라이브러리를 pip로 설치한다.

sudo pip3.8 install sqlalchemy

sudo pip3.8 install jaydebeapi

sudo pip3.8 install pandas

sudo pip3.8 install psycopg2

Error: pg_config executable not found. 

에러 발생시 postgresql과 설정에 문제가 있어서 발생 

sudo yum install postgresql postgresql-devel python3-devel

설치 후

다시 설치

sudo pip3.8 install psycopg2

gcc 관려 에러가 발생하면 gcc도 설치 해 준다.

sudo yum install gcc

/* 리눅스에서 대량의 데이타 sqlalchemy를 사용하여 처리하기 */

/* jdbc 폴더를 만든후에 jdbc드라이브 파일을 올린다 */

파이쎤 파일을 생성한다.

vi test.py

#!/usr/bin/python3.8

from sqlalchemy import create_engine
import sqlalchemy.types as sql_types
import jaydebeapi as jp
import pandas as pd
import time

tibero_JDBC_Driver = './jdbc/tibero6-jdbc.jar'
drivers = [tibero_JDBC_Driver]


def postgres_connect(user, password, db, host, port=5432):
    url = 'postgresql://{}:{}@{}:{}/{}'.format(user, password, host, port, db)
    engine = create_engine(url, client_encoding='utf8',
                           executemany_mode='batch')
    return engine


def tibero_connect():
    conn = jp.connect('com.tmax.tibero.jdbc.TbDriver',
                      'jdbc:tibero:thin:@192.168.1.19:9027:test', ['test', 'test'], jars=drivers)
    return conn



user = 'postgres'
password = 'postgres'
db = 'naqs'
host = '192.168.1.26'

query2 = """
    SELECT *
      FROM tibero.table
    """

start_time = time.time()
tb_conn = tibero_connect()
df = pd.read_sql_query(query2, tb_conn)

pg_engine = postgres_connect(user, password, db, host)
start_time1 = time.time()

print(df.columns)

df.to_sql('test_tmp',
          schema='public',
          con=pg_engine,
          if_exists='replace',  # {'fail', 'replace', 'append'), default 'fail'
          chunksize=1000,
          index=False,
          method='multi',
          dtype={
              'LGN_ID': sql_types.VARCHAR(20),
              'REG_TM': sql_types.TIMESTAMP,
              "UPD_TM": sql_types.TIMESTAMP,
              "LAST_LOGIN_DT": sql_types.TIMESTAMP,
              "CONFIRM_DT": sql_types.TIMESTAMP,
              "CANCEL_DT": sql_types.TIMESTAMP,
              "SECSN_DT": sql_types.TIMESTAMP,
              "INFO_AGREE_DT": sql_types.TIMESTAMP,
              "CD_LEN": sql_types.NUMERIC,
              "ARA_ORDER": sql_types.NUMERIC,
              "LATITUDE": sql_types.NUMERIC,
              "LONGITUDE": sql_types.NUMERIC,
              "REG_DTTM": sql_types.TIMESTAMP,
              "UPD_DTTM": sql_types.TIMESTAMP,
              "HLDY_DT": sql_types.TIMESTAMP

          }
          )

tb_conn.close()

print("Total  : %s seconds" % (time.time() - start_time))
print("to_sql : %s seconds" % (time.time() - start_time1))

저장후 테스트 실행한다.

실행 권한을 부여후 실행

[python@localhost python]$ chmod 755 test.py

[python@localhost python]$ ll
합계 4
drwxrwxr-x. 2 python python   78 12월 23 01:03 jdbc
-rwxr-xr-x. 1 python python 3113 12월 23 01:10 test.py

[python@localhost python]$ ./test.py
Index(['LAWD_CD', 'SD_NM', 'SD_SHORT_NM', 'SGG_NM', 'EMD_NM', 'RI_NM', 'SD_CD',
       'SGG_CD', 'EMD_CD', 'RI_CD', 'EFTV_YN', 'RMK', 'REGR_ID', 'REG_TM',
       'UPDR_ID', 'UPD_TM', 'DT', 'END_DT'],
      dtype='object')
Total  : 15.065675735473633 seconds
to_sql : 9.883891820907593 seconds

/* 대량의 데이타 처리 끝 */

출처: https://cbwstar.tistory.com/entry/파이썬-psycopg2-패키지-설치-오류-해결?category=1021765 [C.B.W 블로그]

# 참조 : https://cafe-de-java.tistory.com/7?category=757531

DB LINK를 이용해서 ITAS로 Oracle의 데이타를 Tibero로 이관 작업을 하는 중에

ITAS 다음에 바로 COMMIT을 주고 싶었는데, 

INSERT INTO TB0001 SELECT * FROM TB0001@ORA_DEV; COMMIT;

이런식으로 한 줄로 출력할 경우 문법 에러가 발생하게 된다.

그래서 짝수 번째 줄에 COMMIT; 문장을 출력해주는 스크립트를 꼼수로 작성해 보았다.

아래는 ITAS로 이루어진 itas_gen_sql.sql 파일

꼼수로 맨 마지막 글자가 세미콜론(;) 임을 이용해서 세미콜론뒤에 콜론을 하나더 붙여서, 콜론을 개행처리한 후 COMMIT; 을 추가하였다.

그럼 itas_gen_sql_commit.sql 파일은 아래와 같이 작성되어 있다.

TIP 으로 참고하길 바람