如何在AWS Glue Python Shell作业中快速向MSSQL批量插入数据

你当前使用的pymssql默认executemany实现没有做批量优化，本质是循环生成单条INSERT语句逐行发送到数据库执行，和手写循环调用execute没有区别。10万行、180列的场景下，SQL解析、网络往返、事务提交的开销会被放大上百倍，这就是加载耗时长达20分钟的核心原因。

• 替换写入接口，使用数据库原生批量写入能力（收益10~50倍）
  这是最核心的优化，改造成本极低，优先实施：

  1. 推荐将驱动从pymssql换成pyodbc，开启fast_executemany参数。该参数会将所有待插入数据打包为二进制参数流，调用SQL Server原生批量插入接口一次提交，完全避免逐行生成SQL、逐行解析的开销。
     连接配置示例：

     import pyodbc
     # Glue环境默认已预装ODBC Driver 17 for SQL Server，无需额外安装
     conn = pyodbc.connect(
         'DRIVER={ODBC Driver 17 for SQL Server};SERVER=你的数据库地址;DATABASE=库名;UID=账号;PWD=密码'
     )
     db_cursor = conn.cursor()
     # 核心配置，开启批量写入
     db_cursor.fast_executemany = True
     

     原有插入逻辑仅需修改两处：一是把SQL语句里的%s/%d占位符全部替换为pyodbc要求的?，二是插入完成后手动commit：

     # 占位符全部换成?，数量和列数对齐
     sql = "insert into [db].[schema].[table](列名列表) values(?,?,?,?,?......)"
     db_cursor.executemany(sql, sql_data_tuple)
     conn.commit()
     

  2. 如果必须保留pymssql，可以使用其内置的BCP（批量复制）API写入，性能比pyodbc的fast_executemany还要高，也可以直接用bcpandas库封装好的方法，直接传入DataFrame即可完成写入，无需手动转元组。

• 关闭自动提交，单事务批量写入（收益5~10倍）
  pymssql、pyodbc默认开启自动提交，相当于每执行一条INSERT就触发一次事务提交，磁盘刷盘开销极高。不管使用哪种驱动，都要在连接建立后关闭自动提交，全量数据插入完成后统一提交一次：

  conn.autocommit = False
  try:
      # 执行批量插入逻辑
      db_cursor.executemany(sql, sql_data_tuple)
      conn.commit()
      print("loading completed on {}".format(datetime.datetime.now()))
  except Exception as e:
      conn.rollback()
      print(e)
  

• 分块清洗写入，避免全量数据攒内存（收益20%~50%）
  你当前的代码会把所有CSV分块全部读入内存后concat成完整DataFrame，10~20万行180列的数据会占用数GB内存，很容易触发Glue作业的GC停顿，拖慢整体速度。可以直接在读取的分块上完成清洗，洗完一个分块就写入一次数据库，无需攒全量数据，同时可以让S3读取、数据清洗、数据库写入三个环节流水并行：

  file = s3.get_object(Bucket=S3_BUCKET_NAME, Key=each_file)
  # 按3~5万行的大小分块，兼顾内存占用和批量写入效率
  for chunk in pd.read_csv(file['Body'], sep=",", header=None, low_memory=False, chunksize=30000):
      cols = chunk.select_dtypes(object).columns
      chunk[cols] = chunk[cols].apply(lambda x: x.str.strip())
      chunk.replace(to_replace='', value=np.nan, inplace=True)
      chunk.fillna(value=np.nan, inplace=True)
      chunk.insert(0, 'New-column', 1111)
      sql_data_array = chunk.replace({np.nan: None}).to_numpy()
      sql_data_tuple = tuple(map(tuple, sql_data_array))
      db_cursor.executemany(sql, sql_data_tuple)
  # 所有分块写入完成后统一提交
  conn.commit()
  

• 临时调整目标表配置（收益30%~60%）
  如果目标表上存在多个非聚集索引、行级触发器，批量插入时每写入一行都会触发索引维护、触发器逻辑，额外开销极高。可以在写入前先禁用非聚集索引、停用触发器，写入完成后统一重建索引、恢复触发器，大数据量下优化效果非常明显。

按上述方案优化完成后，10万行180列的数据写入耗时通常会从20分钟压缩到30秒以内，20万行数据也可稳定控制在1分钟左右。

内容的提问来源于stack exchange，提问作者srikanth sanapathi