CSV para SQL Server: pesadelo de importação em massa (T-SQL e / ou Pandas)

Question

Estou tentando inserir um .CSVarquivo em massa no SQL Server sem muito sucesso.

Um pouco de contexto:

1. Eu precisei inserir 16 milhões de registros em um banco de dados SQL Server (2017). Cada registro possui 130 colunas. Tenho um campo .CSVresultante de uma chamada de API de um de nossos fornecedores que não posso mencionar. Eu tinha tipos de dados inteiros, flutuantes e strings.

2. Tentei o de costume: BULK INSERTmas não consegui passar os erros de tipo de dados. Eu postei uma pergunta aqui, mas não consegui fazer funcionar.

3. Eu tentei experimentar com python e tentei todos os métodos que pude encontrar, mas pandas.to_sqlpara todos avisaram que era muito lento. Fiquei preso com erros de tipo de dados e truncamento de string. Diferente dos de BULK INSERT.

4. Sem muitas opções, tentei pd.to_sqle, embora não levantasse nenhum tipo de dados ou erros de truncamento, estava falhando devido à falta de espaço em meu banco de dados SQL tmp. Também não consegui passar este erro, embora tivesse bastante espaço e todos os meus arquivos de dados (e arquivos de log) estivessem configurados para crescimento automático sem limite.

Eu fiquei preso naquele ponto. Meu código (para a pd.to_sqlpeça) era simples:

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)

Não tenho certeza do que mais tentar, qualquer conselho é bem-vindo. Todos os códigos e exemplos que vi lidam com pequenos conjuntos de dados (não muitas colunas). Estou disposto a tentar qualquer outro método. Eu apreciaria quaisquer sugestões.

Obrigado!

Answer 1

Eu só queria compartilhar esse código sujo para o caso de ajudar mais alguém. Observe que estou bem ciente de que isso não é o ideal de forma alguma, é lento, mas consegui inserir cerca de 16 milhões de registros em dez minutos sem sobrecarregar minha máquina.

Tentei fazer isso em pequenos lotes com:

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

Feio pra caramba, mas funcionou para mim.

Estou aberto a todas as críticas e conselhos. Como mencionei, estou postando isso para o caso de ajudar mais alguém, mas também estou ansioso para receber algum feedback construtivo.

Answer 2

Carregar dados do quadro de dados do pandas para o banco de dados SQL é muito lento e, ao lidar com grandes conjuntos de dados, ficar sem memória é um caso comum. Você deseja algo que seja muito mais eficiente do que ao lidar com grandes conjuntos de dados.

d6tstack é algo que pode resolver seus problemas. Porque funciona com comandos de importação de banco de dados nativos. É uma biblioteca personalizada desenvolvida especificamente para lidar com o esquema e também com os problemas de desempenho. Funciona para XLS, CSV, TXT que pode ser exportado para CSV, Parquet, SQL e Pandas.

Answer 3

Eu acho que df.to_sqlé muito legal! Tenho usado muito ultimamente. É um pouco lento, quando os conjuntos de dados são realmente grandes. Se você precisa de velocidade, acho que Bulk Insert será a opção mais rápida. Você pode até fazer o trabalho em lotes, para não ficar sem memória e talvez sobrecarregar sua máquina.

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