Как ускорить массовую вставку в MS SQL Server из CSV, используя pyodbc

Ниже мой код, с которым мне нужна помощь. Мне нужно запустить его более 1 300 000 строк, что означает, что для вставки ~ 300 000 строк требуется до 40 минут.

Я полагаю, что объемная вставка - это маршрут, чтобы ускорить его? Или это потому, что я повторяю строки через for data in reader: часть?

#Opens the prepped csv file
with open (os.path.join(newpath,outfile), 'r') as f:
    #hooks csv reader to file
    reader = csv.reader(f)
    #pulls out the columns (which match the SQL table)
    columns = next(reader)
    #trims any extra spaces
    columns = [x.strip(' ') for x in columns]
    #starts SQL statement
    query = 'bulk insert into SpikeData123({0}) values ({1})'
    #puts column names in SQL query 'query'
    query = query.format(','.join(columns), ','.join('?' * len(columns)))

    print 'Query is: %s' % query
    #starts curser from cnxn (which works)
    cursor = cnxn.cursor()
    #uploads everything by row
    for data in reader:
        cursor.execute(query, data)
        cursor.commit()

Я динамически выбираю заголовки столбцов специально (так как я хотел бы создать наиболее возможный пифонический код).

SpikeData123 - это имя таблицы.

Ответ 1

BULK INSERT почти наверняка будет намного быстрее, чем чтение исходного файла по строкам и выполнение регулярного INSERT для каждой строки. Тем не менее, как BULK INSERT, так и BCP имеют значительное ограничение в отношении файлов CSV, поскольку они не могут обрабатывать текстовые классификаторы (ref: здесь). То есть, если ваш CSV файл не имеет в нем квалифицированных текстовых строк...

1,Gord Thompson,2015-04-15
2,Bob Loblaw,2015-04-07

... тогда вы можете BULK INSERT его, но если он содержит текстовые классификаторы (потому что некоторые текстовые значения содержат запятые)...

1,"Thompson, Gord",2015-04-15
2,"Loblaw, Bob",2015-04-07

... тогда BULK INSERT не сможет справиться с этим. Тем не менее, может быть, скорее всего, предварительно обработать такой CSV файл в файле с разделителями каналов...

1|Thompson, Gord|2015-04-15
2|Loblaw, Bob|2015-04-07

... или файл с разделителями табуляции (где → представляет символ табуляции)...

1→Thompson, Gord→2015-04-15
2→Loblaw, Bob→2015-04-07

... и затем BULK INSERT этот файл. Для последнего (с разделителями табуляции) файла код BULK INSERT будет выглядеть примерно так:

import pypyodbc
conn_str = "DSN=myDb_SQLEXPRESS;"
cnxn = pypyodbc.connect(conn_str)
crsr = cnxn.cursor()
sql = """
BULK INSERT myDb.dbo.SpikeData123
FROM 'C:\\__tmp\\biTest.txt' WITH (
    FIELDTERMINATOR='\\t',
    ROWTERMINATOR='\\n'
    );
"""
crsr.execute(sql)
cnxn.commit()
crsr.close()
cnxn.close()

Примечание. Как указано в комментарии, выполнение инструкции BULK INSERT применимо только в том случае, если экземпляр SQL Server может напрямую читать исходный файл. Для случаев, когда исходный файл находится на удаленном клиенте, см. этот ответ.

Ответ 2

Как отмечено в комментарии к другому ответу, команда T-SQL BULK INSERT будет работать, только если файл, который будет импортирован, находится на том же компьютере, что и экземпляр SQL Server, или находится в сетевом расположении SMB/CIFS, что Экземпляр SQL Server может читать. Таким образом, это может быть неприменимо в случае, когда исходный файл находится на удаленном клиенте.

pyodbc 4.0.19 добавлена функция Cursor # fast_executemany, которая может быть полезна в этом случае. fast_executemany по умолчанию отключен и следующий тестовый код...

cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("TRUNCATE TABLE fast_executemany_test")

sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"
params = [(f'txt{i:06d}',) for i in range(1000)]
t0 = time.time()
crsr.executemany(sql, params)
print(f'{time.time() - t0:.1f} seconds')

... потребовалось около 22 секунд для выполнения на моей тестовой машине. Просто добавив crsr.fast_executemany = True...

cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("TRUNCATE TABLE fast_executemany_test")

crsr.fast_executemany = True  # new in pyodbc 4.0.19

sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"
params = [(f'txt{i:06d}',) for i in range(1000)]
t0 = time.time()
crsr.executemany(sql, params)
print(f'{time.time() - t0:.1f} seconds')

... сократил время выполнения до чуть более 1 секунды.

Ответ 3

да встроенная вставка - это правильный путь для загрузки больших файлов в БД. С первого взгляда я бы сказал, что причина, по которой это так долго, - это то, как вы упомянули, что вы перебираете каждую строку данных из файла, что эффективно означает удаление преимуществ использования объемной вставки и превращение ее в обычную вставку. Просто помните, что, поскольку это название подразумевает, что оно используется для вставки патронов данных. Я бы удалил цикл и повторил попытку.

Также я бы дважды проверял ваш синтаксис для массовой вставки, поскольку он не выглядит корректным для меня. проверьте sql, который генерируется pyodbc, поскольку у меня возникает ощущение, что он может выполнять обычную вставку

Альтернативно, если он все еще медленный, я бы попытался использовать массивную вставку непосредственно из sql и либо загрузить весь файл в временную таблицу с объемной вставкой, а затем вставить соответствующий столбец в правые таблицы. или используйте сочетание объемной вставки и bcp для ввода определенных столбцов или OPENROWSET.

Ответ 4

Используйте библиотеку 'turbodbc', это был единственный способ быстрой загрузки.