SqlAlchemy 및 cx_Oracle을 사용하여 Pandas DataFrame을 Oracle 데이터베이스에 작성할 때 _sql()까지 속도 향상

SqlAlchemy 및 cx_Oracle을 사용하여 Pandas DataFrame을 Oracle 데이터베이스에 작성할 때 _sql()까지 속도 향상

pandas dataframe의 to_sql 메서드를 사용하여 Oracle 데이터베이스의 테이블에 적은 수의 행을 매우 쉽게 쓸 수 있습니다.

from sqlalchemy import create_engine
import cx_Oracle
dsn_tns = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=1521))\
       (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<servicename>)))"
pwd = input('Please type in password:')
engine = create_engine('oracle+cx_oracle://myusername:' + pwd + '@%s' % dsn_tns)
df.to_sql('test_table', engine.connect(), if_exists='replace')

그러나 일반적인 크기의 데이터 프레임(내 것은 60,000개의 행이 있고 그렇게 크지는 않음)을 사용하면 코드를 사용할 수 없게 됩니다. 제가 기다리는 시간(확실히 10분 이상) 내에 완료되지 않았기 때문입니다.제가 꽤 여러 번 검색해봤는데 가장 가까운 해결책은 이 질문에 대한 답변이었습니다.하지만 그것은 mysql에 관한 것이지 오라클에 관한 것이 아닙니다.지그 유니시엔이 지적했듯이 오라클에는 효과가 없었습니다.아이디어 있어요?

편집

다음은 데이터 프레임의 행 샘플입니다.

id          name            premium     created_date    init_p  term_number uprate  value   score   group   action_reason
160442353   LDP: Review     1295.619617 2014-01-20  1130.75     1           7       -42 236.328243  6       pass
164623435   TRU: Referral   453.224880  2014-05-20  0.00        11          NaN     -55 38.783290   1       suppress

다음은 df에 대한 데이터 유형입니다.

id               int64
name             object
premium          float64
created_date     object
init_p           float64
term_number      float64
uprate           float64
value            float64
score            float64
group            int64
action_reason    object

Pandas + SQL 기본값으로 모두 저장object(string) 열을 Oracle DB의 CLOB로 사용하므로 삽입 속도가 매우 느립니다.

다음은 몇 가지 테스트입니다.

import pandas as pd
import cx_Oracle
from sqlalchemy import types, create_engine

#######################################################
### DB connection strings config
#######################################################
tns = """
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my-db-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = my_service_name)
    )
  )
"""

usr = "test"
pwd = "my_oracle_password"

engine = create_engine('oracle+cx_oracle://%s:%s@%s' % (usr, pwd, tns))

# sample DF [shape: `(2000, 11)`]
# i took your 2 rows DF and replicated it: `df = pd.concat([df]* 10**3, ignore_index=True)`
df = pd.read_csv('/path/to/file.csv')

DF 정보:

In [61]: df.shape
Out[61]: (2000, 11)

In [62]: df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2000 entries, 0 to 1999
Data columns (total 11 columns):
id               2000 non-null int64
name             2000 non-null object
premium          2000 non-null float64
created_date     2000 non-null datetime64[ns]
init_p           2000 non-null float64
term_number      2000 non-null int64
uprate           1000 non-null float64
value            2000 non-null int64
score            2000 non-null float64
group            2000 non-null int64
action_reason    2000 non-null object
dtypes: datetime64[ns](1), float64(4), int64(4), object(2)
memory usage: 172.0+ KB

Oracle DB에 저장하는 데 시간이 얼마나 걸리는지 확인해 보겠습니다.

In [57]: df.shape
Out[57]: (2000, 11)

In [58]: %timeit -n 1 -r 1 df.to_sql('test_table', engine, index=False, if_exists='replace')
1 loop, best of 1: 16 s per loop

Oracle DB의 경우(CLOB에 주목):

AAA> desc test.test_table
 Name                            Null?    Type
 ------------------------------- -------- ------------------
 ID                                       NUMBER(19)
 NAME                                     CLOB        #  !!!
 PREMIUM                                  FLOAT(126)
 CREATED_DATE                             DATE
 INIT_P                                   FLOAT(126)
 TERM_NUMBER                              NUMBER(19)
 UPRATE                                   FLOAT(126)
 VALUE                                    NUMBER(19)
 SCORE                                    FLOAT(126)
 group                                    NUMBER(19)
 ACTION_REASON                            CLOB        #  !!!

이제 판다들에게 모든 것을 구하라고 지시합시다.object열(VARCHAR 데이터 유형):

In [59]: dtyp = {c:types.VARCHAR(df[c].str.len().max())
    ...:         for c in df.columns[df.dtypes == 'object'].tolist()}
    ...:

In [60]: %timeit -n 1 -r 1 df.to_sql('test_table', engine, index=False, if_exists='replace', dtype=dtyp)
1 loop, best of 1: 335 ms per loop

이번에는 약 48배 더 빨랐습니다.

Oracle DB 체크인:

 AAA> desc test.test_table
 Name                          Null?    Type
 ----------------------------- -------- ---------------------
 ID                                     NUMBER(19)
 NAME                                   VARCHAR2(13 CHAR)        #  !!!
 PREMIUM                                FLOAT(126)
 CREATED_DATE                           DATE
 INIT_P                                 FLOAT(126)
 TERM_NUMBER                            NUMBER(19)
 UPRATE                                 FLOAT(126)
 VALUE                                  NUMBER(19)
 SCORE                                  FLOAT(126)
 group                                  NUMBER(19)
 ACTION_REASON                          VARCHAR2(8 CHAR)        #  !!!

200,000 행 DF로 테스트해 보겠습니다.

In [69]: df.shape
Out[69]: (200000, 11)

In [70]: %timeit -n 1 -r 1 df.to_sql('test_table', engine, index=False, if_exists='replace', dtype=dtyp, chunksize=10**4)
1 loop, best of 1: 4.68 s per loop

테스트 환경에서 200K 행 DF에 대해 최대 5초가 걸렸습니다(가장 빠른 것은 아님).

결론: 다음 트릭을 사용하여 명시적으로 지정합니다.dtype의 모든 DF 열에 대해object데이터 프레임을 Oracle DB에 저장할 때 dtype을 입력합니다.그렇지 않으면 CLOB 데이터 유형으로 저장되므로 특별한 처리가 필요하고 매우 느립니다.

dtyp = {c:types.VARCHAR(df[c].str.len().max())
        for c in df.columns[df.dtypes == 'object'].tolist()}

df.to_sql(..., dtype=dtyp)

그냥 사용할 수 있습니다.method='multi'이렇게 하면 데이터 삽입 속도가 향상됩니다.

또한 조정할 수 있습니다.chunksize필요에 따라 데이터에 따라 달라집니다.

csv 파일/excel의 데이터를 데이터 프레임에 로드하는 기능이 있는 구글 클라우드 함수를 작성하려고 시도했을 때 이를 발견했으며 해당 데이터 프레임을 구글 클라우드 sql의 postgresql 데이터베이스에 저장하고 싶습니다.

데이터베이스 테이블과 유사한 구조를 데이터 프레임에 만들 수 있는 경우 이 도구를 사용할 수 있습니다.

df.to_sql(
    'table_name',
    con=engine, 
    if_exists='append', 
    index=False, 
    chunksize=2000,
    method='multi'
)

후세를 위해 여기에 논평하는 것뿐입니다.파이썬 3.6.8, 판다 1.1.3, sqlalchemy 1.3.20입니다.MaxU에서 솔루션을 구현하려고 할 때 처음에 다음과 같은 오류가 발생했습니다.

raise ValueError(f"{col} ({my_type}) not a string")

솔직히 왜 그런지 모르겠어요.몇 시간 동안 디버깅을 한 후에, 이것이 마침내 저에게 효과가 있었습니다.제 경우 CSV를 읽고 Oracle에 삽입하려고 했습니다.

import cx_Oracle
import numpy as np
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import create_engine

conn = create_engine('oracle://{}:{}@{}'.format(USERNAME, PASSWORD, DATABASE))

df = pd.read_csv(...)
object_columns = [c for c in df.columns[df.dtypes == 'object'].tolist()]
dtyp = {c:sa.types.VARCHAR(df[c].str.len().max()) for c in object_columns}

df.to_sql(..., dtype=dtyp)

솔직히 저는 별로 달라진 게 없어서 왜 원래 오류가 나오는지 100% 확신할 수는 없지만 도움이 될까봐 여기에 글을 올립니다.

다음 기능을 사용합니다.
이 함수는 데이터 프레임 이름과 길이(선택 사항)를 사용합니다.변환된 데이터 유형(개체 유형)을 Varchar(길이) 기본 길이 = 250(이 재미는 개체 유형만 처리함)으로 반환합니다.

def dtyp(df_name, length=250):
    cols = df_name.select_dtypes(include='object')
    dtyps = {col: VARCHAR2(length) for col in cols}
    return dtypsenter code here

호출 방법 예:

config.dtyp(dataframe, 300)

언급URL : https://stackoverflow.com/questions/42727990/speed-up-to-sql-when-writing-pandas-dataframe-to-oracle-database-using-sqlalch