CSV'den SQL Server'a: toplu içe aktarma kabusu (T-SQL ve / veya Pandalar)

Question

.CSVÇok başarılı olmadan SQL Server'a toplu bir dosya eklemeye çalışıyorum .

Biraz arka plan:

1. Bir SQL Server (2017) DB'sine 16 milyon kayıt eklemem gerekiyordu. Her kaydın 130 sütunu vardır. .CSVSatıcılarımızdan birinden gelen ve bahsetmeme izin verilmeyen bir API çağrısından kaynaklanan bir alan var . Tam sayı, kayan değer ve dizge veri türlerim vardı.

2. Her zamanki gibi denedim: BULK INSERTancak veri tipi hatalarını geçemedim. Bir sorum yayınlanmıştır burada ama işe gelemedi.

3. Python ile deneyler yapmayı denedim ve bulabildiğim her yöntemi denedim ama pandas.to_sqlherkes için bunun çok yavaş olduğu konusunda uyardım. Veri türü ve dize kesme hataları ile sıkışıp kaldım. Birinden farklı BULK INSERT.

4. Çok fazla seçenek olmadan denedim pd.to_sqlve herhangi bir veri türü veya kesme hatası oluşturmasa da, tmp SQL veritabanımda yer kalmadığı için başarısız oluyordu. Yeterli alanım olmasına ve tüm veri dosyalarım (ve günlük dosyalarım) sınırsız olarak otomatik büyümeye ayarlanmış olmasına rağmen bu hatayı geçemedim.

Ben o noktada sıkışıp kaldım. Kodum ( pd.to_sqlparça için) basitti:

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)

Başka ne deneyeceğime gerçekten emin değilim, herhangi bir tavsiye memnuniyetle karşılanır. Gördüğüm tüm kodlar ve örnekler küçük veri kümeleriyle ilgilidir (çok fazla sütun değil). Başka bir yöntemi denemeye hazırım. Herhangi bir ipucu için minnettar olurum.

Teşekkürler!

Answer 1

Sadece bu kirli kod parçasını başkalarına yardımcı olma ihtimaline karşı paylaşmak istedim. Bunun optimal olmadığının çok farkındayım, yavaş ama makinemi aşırı yüklemeden on dakikada yaklaşık 16 milyon kayıt ekleyebildim.

Bunu küçük gruplar halinde yapmayı denedim:

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

Cehennem kadar çirkin ama benim için çalıştı.

Tüm eleştirilere ve tavsiyelere açığım. Bahsettiğim gibi, bunu başka birine yardımcı olması durumunda gönderiyorum ama aynı zamanda bazı yapıcı geri bildirimler almayı da bekliyorum.

Answer 2

Pandas veri çerçevesinden SQL veritabanına veri yüklemek çok yavaştır ve büyük veri kümeleriyle uğraşırken belleğin bitmesi olağan bir durumdur. Büyük veri kümeleriyle uğraşırken bundan çok daha verimli bir şey istiyorsunuz.

d6tstack , sorunlarınızı çözebilecek bir şeydir. Çünkü yerel DB içe aktarma komutlarıyla çalışır. Şema ve performans sorunları ile ilgilenmek için özel olarak oluşturulmuş özel bir kitaplıktır. CSV, Parke, SQL ve Pandalara aktarılabilen XLS, CSV, TXT için çalışır.

Answer 3

Bence df.to_sqloldukça harika! Son zamanlarda çok kullanıyorum. Veri kümeleri gerçekten çok büyük olduğunda biraz yavaş. Hıza ihtiyacınız varsa, Toplu Ekleme en hızlı seçenek olacaktır. Hatta işi partiler halinde bile yapabilirsiniz, böylece hafızanız tükenmez ve belki de makinenizi bunaltmazsınız.

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