python - 使用 Python 和 SQL Server 的 ETL 过程需要很长时间才能加载

Question

我正在寻找一种可以提高 csv 文件 SQL Server 数据库加载过程性能的技术。我尝试了各种方法，但似乎无法突破 5.5 小时的障碍。这只是测试加载一年的数据，即大约 200 万条记录。我最终要加载 20 年的数据，因此连续 4 天加载数据是行不通的。

挑战在于，必须在加载时丰富数据。我必须添加一些列，因为该信息不是文件的 native 信息。到目前为止，我已经尝试过:

1. 使用 petl 将列附加到数据，然后将其刷新到数据库。
2. 使用 pandas 将列附加到数据，然后将数据框刷新到数据库。
3. 使用批量加载加载中间暂存表，然后使用 T-SQL 填充额外的列，然后将其推送到最终暂存表。

批量加载工作得非常快，但随后我必须为额外的列添加数据，我们又回到了行级操作，我认为这是这里的瓶颈。我正准备尝试:

1. 使用 Pandas 附加数据。
2. 将数据写回 CSV。
3. 批量加载 CSV。

这让我很困扰，因为我现在有两个 I/O 操作。将文件读入 pandas 并再次写回文件。

我在某处读到 Pandas 是用 C 或其他语言编写的，所以它应该非常快。将数据帧刷新到数据库并不是那么快。在这一点上，我想问是否有人有他们在现实世界中使用的更快的方法。到目前为止，我所拥有的如下:

import pypyodbc
conn_str = "DSN=[dsn name];"
cnxn = pypyodbc.connect(conn_str)
crsr = cnxn.cursor()
sql = "BULK INSERT pre_stage_view FROM '[file path]' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')"
crsr.execute(sql)
cnxn.commit()
crsr.close()
cnxn.close()

这是删除 header 的存储过程:

DELETE FROM pre_stage_table WHERE Symbol = 'Symbol'


INSERT INTO stage_table(
[Symbol],
[Exchange],
[Date],
[Open],
[High],
[Low],
[Close],
[Volume],
[SourceSystem],
[RunDate]
)
SELECT
[Symbol],
@exchange, --passed in proc parameter
[Date],
[Open],
[High],
[Low],
[Close],
[Volume],
'EODData',
CURRENT_TIMESTAMP
FROM pre_stage_table


TRUNCATE TABLE pre_stage_table

Answer 1

Bulk load works REALLY fast but then I have to add the data for the extra columns and we're back to row level operations which I think is the bottleneck here.

抱歉，我不明白为什么要进行行级操作。尝试:

1) 批量加载到阶段表

2) MERGE带有目标表的阶段表

您仍将获得基于集合的方法，并且性能可能不错。请记住禁用触发器(如果可能的话)，而且您可以删除索引、加载数据并在之后重建它们。