CSV sang SQL Server: cơn ác mộng nhập hàng loạt (T-SQL và / hoặc Pandas)

Question

Tôi đang cố gắng chèn hàng loạt .CSVtệp vào SQL Server nhưng không thành công.

Một chút thông tin cơ bản:

1. Tôi cần chèn 16 triệu bản ghi vào DB SQL Server (2017). Mỗi bản ghi có 130 cột. Tôi có một trường trong .CSVkết quả từ lệnh gọi API từ một trong những nhà cung cấp của chúng tôi mà tôi không được phép đề cập đến. Tôi có các kiểu dữ liệu số nguyên, số thực và chuỗi.

2. Tôi đã thử thông thường: BULK INSERTnhưng tôi không thể vượt qua lỗi kiểu dữ liệu. Tôi đã đăng một câu hỏi ở đây nhưng không thể làm cho nó hoạt động.

3. Tôi đã thử thử nghiệm với python và thử mọi phương pháp tôi có thể tìm thấy nhưng pandas.to_sqlmọi người cảnh báo rằng nó rất chậm. Tôi gặp khó khăn với lỗi kiểu dữ liệu và cắt bớt chuỗi. Khác với những người từ BULK INSERT.

4. Không có nhiều tùy chọn, tôi đã thử pd.to_sqlvà trong khi nó không phát sinh bất kỳ kiểu dữ liệu nào hoặc lỗi cắt bớt, nó không thành công do hết dung lượng trong cơ sở dữ liệu tmp SQL của tôi. Tôi cũng không thể vượt qua lỗi này mặc dù tôi có nhiều dung lượng và tất cả các tệp dữ liệu (và tệp nhật ký) của tôi đã được đặt thành tự động phát triển không giới hạn.

Tôi đã bị mắc kẹt ở điểm đó. Mã của tôi (cho pd.to_sqlmảnh) rất đơn giản:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://@myDSN")

df.to_sql('myTable', engine, schema='dbo', if_exists='append',index=False,chunksize=100)

Tôi thực sự không chắc chắn những gì khác để thử, bất kỳ lời khuyên nào đều được hoan nghênh. Tất cả các mã và ví dụ tôi đã thấy đều liên quan đến tập dữ liệu nhỏ (không nhiều cột). Tôi sẵn sàng thử bất kỳ phương pháp nào khác. Tôi sẽ đánh giá cao bất kỳ gợi ý nào.

Cảm ơn!

Answer 1

Tôi chỉ muốn chia sẻ đoạn mã bẩn thỉu này phòng khi nó giúp ích cho bất kỳ ai khác. Lưu ý rằng tôi rất biết rằng điều này không tối ưu chút nào, nó rất chậm nhưng tôi đã có thể chèn khoảng 16 triệu bản ghi trong mười phút mà không làm quá tải máy tính của mình.

Tôi đã thử làm điều đó thành nhiều đợt nhỏ với:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://@myDSN")

a = 1
b = 1001

while b <= len(df):
    try:
        df[a:b].to_sql('myTable', engine, schema='dbo', if_exists='append',index=False,chunksize=100)
        a = b + 1
        b = b + 1000
    except:
        print(f'Error between {a} and {b}')
        continue

Xấu xí như địa ngục nhưng có tác dụng với tôi.

Tôi cởi mở với tất cả các nhà phê bình và lời khuyên. Như tôi đã đề cập, tôi đăng bài này trong trường hợp nó giúp ích cho bất kỳ ai khác nhưng cũng mong nhận được một số phản hồi mang tính xây dựng.

Answer 2

Việc tải dữ liệu từ khung dữ liệu gấu trúc sang cơ sở dữ liệu SQL rất chậm và khi xử lý các tập dữ liệu lớn, việc hết bộ nhớ là trường hợp thường thấy. Bạn muốn thứ gì đó hiệu quả hơn nhiều so với khi xử lý các tập dữ liệu lớn.

d6tstack là thứ có thể giải quyết vấn đề của bạn. Bởi vì nó hoạt động với các lệnh nhập DB gốc. Nó là một thư viện tùy chỉnh được xây dựng đặc biệt để giải quyết các vấn đề liên quan đến lược đồ dưới dạng giếng như hiệu suất. Hoạt động cho XLS, CSV, TXT có thể được xuất sang CSV, Parquet, SQL và Pandas.

Answer 3

Tôi nghĩ df.to_sqllà khá tuyệt vời! Tôi đã sử dụng nó rất nhiều gần đây. Nó hơi chậm, khi tập dữ liệu thực sự rất lớn. Nếu bạn cần tốc độ, tôi nghĩ Bulk Insert sẽ là lựa chọn nhanh nhất. Bạn thậm chí có thể thực hiện công việc theo lô, vì vậy bạn không bị hết bộ nhớ và có thể khiến máy của bạn bị choáng ngợp.

BEGIN TRANSACTION
BEGIN TRY
BULK INSERT  OurTable 
FROM 'c:\OurTable.txt' 
WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', 
   ROWS_PER_BATCH = 10000, TABLOCK)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH