使用python脚本从sql server中提取数百万条记录并加载到oracle数据库。

1 人关注

我正在从sql server中提取数以百万计的数据，并使用python插入到oracle db。它在1秒内将1条记录插入到oracle表中，而插入需要几个小时。什么是最快的加载方法？我的代码如下。

def insert_data(conn,cursor,query,data,batch_size = 10000):
    recs = []
    count = 1
    for rec in data:
        recs.append(rec)
        if count % batch_size == 0:
            cursor.executemany(query, recs,batcherrors=True)
            conn.commit()`enter code here`
            recs = []
        count = count +1
        cursor.executemany(query, recs,batcherrors=True)
        conn.commit()


         5
         
         个评论


           
            更快的方法是使用任何工业ETL工具，如SQL Server Integration Services、Informatica PowerCentre等。


           
            一个RBAR过程是
            
             始终
            
            将会比以集为单位操作并在批量操作中复制数据的人要慢得多（明显）。


           
            不幸的是，我没有使用任何工业工具的特权，所以不得不使用python。


           
            那么你可能想使用
            
             pandas
            
            或
            
             pyspark
            
            或其他可用的python大数据框架。


           
            @SQLpro说 "SSIS是最好的ETL之一"。  那是个笑话。  请看我提议的答案，在那里我还解释了oracle数据库中的pl/sql过程是如何胜过SSIS的几个数量级的。


         python

sql


         sql-server


         oracle


         batch-processing


        2
        
        个回答


          已采纳


         0
         
         人赞同


          
           也许你不能买一个3D Party ETL工具，但你肯定可以在oracle数据库中用PL/SQL写一个程序。
          
          
           首先，安装oracle Transparenet Gateway for ODBC。  不涉及许可证费用。
第二，在oracl db中，创建一个db链接，通过网关引用MSSQL数据库。
第三，编写一个PL/SQL过程，通过db链接从MSSQL数据库中提取数据。
          
          
           我曾经遇到过一个与你类似的问题。开发人员使用SSIS从mssql复制大约一百万行到oracle。  花了4个多小时。  我跟踪了他的进程，发现它是逐行复制的，很慢很慢。  我花了不到30分钟写了一个pl/sql程序来复制数据，在不到4分钟内完成。
          
          
           我对整个设置和过程给出了一个高层次的看法。
           
            here
           
           :
          
          我想你可能想看看实际程序到底有多简单。
          
          
          create or replace my_load_proc
begin
  insert into my_oracle_table (col_a,
                               col_b,
                               col_c)
  select sql_col_a,
          sql_col_b,
          sql_col_c
  from mssql_tbl@mssql_link;
我的实际程序有更多的内容，处理运行时的日志，用电子邮件通知完成情况等。  但上面是它的 "内脏"，把数据从mssql拉到oracle。


           
            
             谢谢你的有用建议，请你指导如何定义 "mssql_link"？谢谢。


          
           
            那么你可能想使用pandas或pyspark或其他python上的大数据框架。
那里有很多例子，这里是如何加载数据的。
            
             Microsoft Docs
            
            :
           
           import pyodbc
import pandas as pd
import cx_Oracle
server = 'servername' 
database = 'AdventureWorks' 
username = 'yourusername' 
password = 'databasename'  
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
query = "SELECT [CountryRegionCode], [Name] FROM Person.CountryRegion;"
df = pd.read_sql(query, cnxn)
# you do data manipulation that is needed here
# then insert data into oracle