Стек рассматриваемых технологий: Postgresql 9.3, Python 2.7 с установленным модулем «psycopg2».
Проблема
Как часто в вашей практике приходилось сталкиваться с задачей обработки таблиц большого объема (более 10 млн. записей)? Думаю вы согласитесь, что данная задача является довольно ресурсоемкой как в плане времени обработки, так и задействованных ресурсов системы. Сегодня я постараюсь показать альтернативный способ решения задачи.
Предложение:
В СУБД Postgresql есть прекрасный оператор для работы с большими объемами информации, а именно «COPY». Применение данного оператора позволяет нам читать и записывать огромные объемы информации в таблицу. В данной статье мы будем рассматривать режим чтения.
Согласно документации оператора «COPY» нам доступны несколько режимов чтения в файл либо в поток STDOUT, а также различные форматы, в том числе и «csv». Как раз его мы и постараемся использовать с максимальной пользой.
Подготовка:
В качестве «подопытного кролика» мы создадим таблицу с 1 млн. записей и напишем небольшой скрипт, отражающий суть метода. Sql файл можно найти в моем репозитории git (ссылку можно найти внизу статьи).
Также не забудьте установить расширение psycopg2!
Реализация:
Для выборки данных мы будем использовать чудесную функцию «copy_expert», которая позволяет нам исполнять «COPY» запросы из Python клиента.
query = """
SELECT * from big_data inner join big_data as t1 USING(fname)
"""
output = StringIO()
self.cursor.copy_expert("COPY (%s) TO STDOUT (FORMAT 'csv', HEADER true)" % query, output)
data = output.getvalue()
output.close()
result = list()
for item in getResults(data):
# do whatever we need
item = {k: None if v == "" else v for k, v in item.items()}
result.append(item)
Пояснения к коду:
- В запросе делаем объединение на себя, для его усложнения (замечено, что преимущество в скорости прямо пропорционально сложности запроса);
- В качестве буфера используем объект «StringIO», куда мы будем записывать данные из курсора;
- Парсить строку будем генератором «getResults»;
- Для удобства интерпретации я преобразую все пустые строки в тип «None», т.к. после использования «COPY» мы получаем строковые значения;
- Хочу отметить, что формат я будем использовать «csv» с лидирующей строкой заголовков, почему именно так поймете, немного позже.
Код генератора:
def getResults(stream):
"""
get result generator
"""
f = StringIO(stream)
result = csv.DictReader(f, restkey=None)
for item in result:
yield item
f.close()
Пояснения:
- Как видно из листинга, опять же используем уже знакомый буфер «StringIO»;
- Для преобразования строки «csv» в словарь (dictionary) используем метод «DictReader» родной библиотеки csv. По умолчанию, данный метод принимает первую строку за список полей словаря.
Вот, собственно, и все, что нам требуется!
Моя конфигурация: MacBook Air 2013 Processor: 1,3 GHz Intel Core i5, Ram: 4 ГБ 1600 МГц DDR3, SSD.
PS:
Хочу отметить, что данный подход к ускорению чтения не всегда работает, а именно, если у вас довольно простая таблица из 3-5 полей, ощутимой разницы вы не заметите (по крайней мере до 1 млн.). Однако данный метод показывает просто сумасшедший прирост в скорости, при сложных запросах ускорение достигает до 10-20 раз! Также очень сильно влияет конфигурация железа, на котором исполняется скрипт.
Весь код можно найти в git репозитории https://github.com/drizgolovicha/python_bulk_read.
Буду рад замечаниям и предложениям по оптимизации!
Спасибо что дочитали до конца.
Автор: Icebeer
