Сама база кодов (barcodes) собиралась энтузиастами и размещена в открытом доступе — здесь. Есть даже целая посвященная ей — статья.
Если кратко: база представляет из себя csv файл ~774Мб с колонками, которые носят следующие наименования:
"""
* (ID) Идентификатор товара в базе данных Universe-HTT
* (UPCEAN) Штрихкод
* (Name) Наименование товара
* (CategoryID) Идентификатор категории в базе данных Universe-HTT
* (CategoryName) Наименование категории
* (BrandID) Идентификатор брэнда в базе данных Universe-HTT
* (BrandName) Наименование брэнда
"""Реально полезную информацию имеют столбцы (UPCEAN) Штрихкод — собственно сам штрихкод, (Name) Наименование товара, (CategoryName) Наименование категории, (BrandName) Наименование брэнда.
Вот так, например, выглядит кофе:
Товар: Кофе lavazza crema E gusto, натуральный молотый 250г, вак уп, 3876
Категория: Продукты питания (folder)/Чай и кофе/Кофе молотый
Брэнд: Lavazza
Эта информация пригодится при считывании csv в датасет.
Pandas.
Фреймворк известен своими способностями по работе с большими и малыми данными, из которых формирует датасеты (dataframes). Так как база штрихкодов небольшая <1Гб, pandas должен с ней отлично справиться. Посмотрим, насколько быстро это произойдет на raspberry pi 4b.
Так как формат данных в сsv файле известен, воспользуемся этими сведениями и определим тип данных при считывании в датафрейм:
df = pd.read_csv('uhtt_barcode_ref_all.csv',
warn_bad_lines=True,
dtype ={'ID':'object','UPCEAN':'int64','Name':'object','CategoryID':'category',
'CategoryName':'category','BrandID':'category','BrandName':'category'},
delimiter='\t')
Как видно, UPCEAN коды помещаются в int64, Name лучше рассматривать как object, CategoryName, BrandName, BrandID в category. К сожалению, ID и CategoryID нельзя поместить в int, так как там попадаются битые данные.
Все эти манипуляции нужны для того, чтобы датасет потреблял меньше оперативной памяти. Без них считывание выглядит так:
df = pd.read_csv('uhtt_barcode_ref_all.csv',
warn_bad_lines=True,
delimiter='\t')
и потребление памяти увеличивается:
Что со временем считывания датасета?
время работы (h:min:sec): 0:01:13
*время считывания датасета при оптимизации по dtype практически не изменяется.
Как видно, скоростью работы pandas не отличается. Однако, в данном случае речь идет всего лишь о загрузке датасета, а не о работе с ним.
Насколько быстро работает сам поиск?
Поищем штрихкод:
some_value = 4603726031011
a=df.loc[df['UPCEAN'] == some_value]
print('товар: {} , категория: {}, брэнд: {}'\
.format(a.at[1,'Name'],a.at[1,'CategoryName'], a.at[1,'BrandName']))
Поиск по базе работает практически мгновенно:
Таким образом, все основное время съедается на загрузку базы данных в оперативную память одноплатника.
Что в данном случае можно сделать, чтобы ускориться при считывании?
Возможно, при считывании датасета считывать только те столбцы, которые нам нужны: их всего четыре.
К сожалению, pandas так не умеет, поэтому для начала необходимо сохранить данные столбцы в отдельный датасет и в дальнейшем использовать его:
header = ['UPCEAN','Name','CategoryName','BrandName']
df.to_csv('out.csv', columns = header, index=False,header=False, na_rep = 'N/A', sep = '\t', compression='gzip')
*заодно датасет поместим в архив, чтобы уменьшить занимаемое пространство. Теперь он занимает 93Мб (вместо 733Мб).
Теперь посмотрим, дали ли эти действия прирост скорости при считывании датасета:
df = pd.read_csv('out.csv', compression='gzip',
dtype ={'UPCEAN':'int64','Name':'object','CategoryName':'category','BrandName':'category'},
warn_bad_lines=True,
delimiter='\t')
Итог:
время работы (h:min:sec:msec): 0:01:00.588706
Таким образом, удалось выиграть 30 сек, что неплохо.
Однако время в 1 мин для поиска по базе, даже если это не поиск, а ее загрузка в память, слишком много.
Dask
Следующий на очереди инструмент — dask. Чтобы с ним поработать, для начала, конечно, его нужно установить.
И вот тут есть, как говорится, есть нюанс.
Поставить dask на raspberry pi, а в частности, на armv7l напрямую через pip install dask не получится.
Dask зависит от другой библиотеки pyarrow, а она, в свою очередь ни через pip, ни из исходных кодов по инструкции с сайта не собирается. Тем не менее, собрать pyarrow все-таки, возможно, но на это уйдет несколько часов.
Для тех, кто хочет этим заняться, оставлю инструкции ниже, а для остальных колесо(wheel).
Dask в деле.
Загрузим датасет и посмотрим на время.
Заодно сразу определим колонки в соответствующие типы данных для оптимизации памяти:
import dask.dataframe as dd
import numpy as np
from datetime import datetime
dtypes = {
'ID':'object',
'UPCEAN':'int64',
'Name':'object',
'CategoryID': 'object',
'CategoryName':'category',
'BrandID':'object',
'BrandName':'category'}
df = dd.read_csv('uhtt_barcode_ref_all.csv', dtype=dtypes, sep='\t', \
comment='#', error_bad_lines=False,lineterminator='\n')
print('загрузка датасета (h:min:sec): '+str(datetime.now()- start))
Загрузка датасета (h:min:sec): 0:00:00.059191
Неплохо. Победитель найден?
Но не будем торопиться с выводами, поищем по датасету штрихкод:
good = 4820024700016
a=df.loc[df['UPCEAN'] == good].compute()
И вот тут получаем подножку от dask:
поиск по датасету (h:min:sec:msec): 0:00:42.424091
42 сек. Очень странно.
Очень странно, если не знать, как работает dask.
На самом деле dask работает в так называемом lazy режиме, не выполняя поиск, а лишь сохраняя инструкции о выполнении. Сама работа происходит, когда вызывается compute().
Как ускорить dask?
Так как dask, так же как и pandas производит считывание датасета, поработаем над самим датасетом.
В отличие от pandas, dask умеет многопоточно считывать датасет, кроме того, умеет работать с таким форматом данных как parquet, а также умеет считывать из датасета столбцы выборочно, не читая весь датасет целиком.
Чтобы воспользоваться всеми этими преимуществами, конвертируем базу в формат parquet и далее будем работать с ним:
df.to_parquet("data/parquet", engine="pyarrow", compression='snappy')
*здесь мы также сжали датасет, чтобы уменьшить его размер.
Теперь база занимает 204Мб(было 733Мб) и выглядит так:
Считаем базу данных, выполним поиск и замерим время:
start = datetime.now()
df = dd.read_parquet("parquet", engine="pyarrow", columns=["UPCEAN", "Name","CategoryName","BrandName"])
print('загрузка датасета (h:min:sec): '+str(datetime.now()- start)[:-7])
good = 4820024700016
a=df.loc[df['UPCEAN'] == good].compute()
a=a.to_string(index=False,header=False).split(' ')
print('поиск по датасету (h:min:sec): '+str(datetime.now()- start)[:-7])
Итог:
16 сек вместо 44 сек.
Таким образом, dask «с улучшениями» выдает результат 16 сек.
Можно ли лучше? Слово за sql.
Sqlite
Чтобы поработать с базой sqllite, а точнее с sqlite3, необходимо для начала конвертировать
csv в db.
В этом нам поможет следующий код:
import csv, sqlite3,sys
#csv.field_size_limit(sys.maxsize)
"""
* (ID) Идентификатор товара в базе данных Universe-HTT
* (UPCEAN) Штрихкод
* (Name) Наименование товара
* (CategoryID) Идентификатор категории в базе данных Universe-HTT
* (CategoryName) Наименование категории
* (BrandID) Идентификатор брэнда в базе данных Universe-HTT
* (BrandName) Наименование брэнда
"""
con = sqlite3.connect('my_big.db')
cur = con.cursor()
cur.execute("CREATE TABLE t (ID,UPCEAN,Name,CategoryID,CategoryName,BrandID,BrandName);")
# ID UPCEAN Name CategoryID CategoryName BrandID BrandName - названия столбцов
with open('uhtt_barcode_ref_all.csv','r', encoding='utf-8') as fin:
# csv.DictReader по умолчанию использует первую строку под заголовки столбцов
dr = csv.DictReader(fin, delimiter="\t",quoting=csv.QUOTE_NONE)
to_db = [(i['ID'], i['UPCEAN'], i['Name'],i['CategoryID'],\
i['CategoryName'],i['BrandID'],i['BrandName']) for i in dr]
cur.executemany("INSERT INTO t (ID,UPCEAN,Name,CategoryID,CategoryName,BrandID,BrandName) VALUES (?, ?, ?, ?, ?, ?, ?);", to_db)
con.commit()
con.close()
На выходе будет база размером 835Мб, что немного больше чем csv файл на старте (774Мб).
Теперь осуществим поиск по базе из python и замерим время:
import sqlite3
from datetime import datetime
start = datetime.now()
def read_sqlite_table(id):
try:
sqlite_connection = sqlite3.connect('my_big.db')
cursor = sqlite_connection.cursor()
print("Подключен к SQLite")
sql_select_query = """select Name,CategoryName,BrandName from t where UPCEAN = ?"""
cursor.execute(sql_select_query, (id,))
for row in cursor:
print(row)
print("Name:", row[0])
print("CategoryName:", row[1])
print("BrandName", row[2], end="\n\n")
cursor.close()
except sqlite3.Error as error:
print("Ошибка при работе с SQLite", error)
finally:
if sqlite_connection:
sqlite_connection.close()
print("Соединение с SQLite закрыто")
read_sqlite_table(str(5412058004308))
print('поиск по датасету (h:min:sec): '+str(datetime.now()- start)[:-7])
Итог:
3 сек. Пожалуй, это лучший результат здесь.
Выводы
- на примере работы с базой данных штрихкодов удалось выяснить, какой из инструментов наиболее подходит для быстрого решения поставленной задачи;
- не смотря на то, что sqlite показал лучший результат, это не умаляет значение других фреймворков;
- выяснены нюансы при работе с pandas и dask при ускорении загрузки датасета в целом и на raspberry pi в частности;
- получен неоценимый опыт многочасовых сборок pyarrow на arm.
Скачать:
Бонус. Как собрать pyarrow на arm
*under construction.
Комментарии (18)
FSA
03.06.2022 19:49-1Полагаю, справиться легко может любой SQL: MySQL, PostgreSQL. База настолько мала, что может вся в оперативке малинки храниться. Делать этого она конечно не будет, потому что SQL достаточно умные, чтобы не загружать всё в оперативку и не положить сервер. Ну а если лень их ставить, то SQLite тоже подойдёт. Главное индексы сделать для поиска.
А, вообще, у меня веб-сервер работает с базой PostgreSQL в несколько мегабайт с 1 гигом оперативки. Нагрузка небольшая. Оперативки хватает с головой. Страницы напрямую из базы выдаются с учётом времени доставки до моего компьютера за 100-200 мс, если не срабатывет кэш, который, как раз в оперативке, тогда ещё быстрее. Или вам принципиально из csv читать данные?
zoldaten Автор
04.06.2022 12:01Из csv читать не принципиально. Требования стандартные: быстро искать, меньше места на диске/в памяти, меньше трудозатрат по развертыванию/обновлению.
Zibx
05.06.2022 11:01Если данные отсортированы, то даже собранный на коленке бинарный поиск по csv файлу (без предварительного считывания всего файла) даст не плохой результат. Лучший результат даёт индекс по баркодам. Индекс в данном случае — это отдельно лежащие данные заточенные под бинарный поиск в формате: код, ссылка_на_полную_запись. Любая база данных при правильном использовании великолепно с этим справится.
Pandas и Dask тоже умеют сооружать индексы.
serhit
03.06.2022 23:53+2Я конечно не знаю полной постановки задачи, но если:
нужно только искать записи по уникальнму ключевому значению, и очень быстро
список помещается в оперативную память
не важно как хранить (поскольку автор несколько раз преобразовывал формат хранения: csv -> сжатый csv с частью колонок, parquet, база SQLight)
то, наверное, можно все записи преобразовать в что-то очень компактное, типа стандартного словаря, индексированного значением штрихода, и сохранять/загружать его из pickle внутри GZip файла.
Компактно на диске, быстрая десериализация, моментальный поиск, минимальный перерасход памяти.
zoldaten Автор
04.06.2022 11:54Интересный вариант. Проверим.
Преобразуем базу в словарь с индексом UPCEAN и сохраним в pickle:df=df.set_index('UPCEAN').T.to_dict('dict')
with open('full_base.pickle', 'wb') as handle:
pickle.dump(a, handle, protocol=pickle.HIGHEST_PROTOCOL)
*без gzip
База на диске 537Мб(было 774 в csv). Это хорошо.
Теперь считаем, найдем штрихкод, замерим время и потребление памяти на raspberry.from datetime import datetime
import pickle
from memory_profiler import profile@profile
def load_file():
with open('full_base.pickle', 'rb') as handle:
a = pickle.load(handle)
for k,v in a.items():
if k==71736000619:
return vif name== "main":
start = datetime.now()
load_file()
print('время работы (h:min:sec:msec): '+str(datetime.now()- start))Итог:
*профилирование "съедает" 1 сек
Следовательно, если ничего не упустил, pickle работает медленнее "ускоренного варианта" dask и памяти занимает больше.
edo1h
04.06.2022 13:32for k,v in a.items():
вы это серьёзно написали? если да, то, как говорится, просто нет слов, у вас очень серьёзные пробелы в образовании.
zoldaten Автор
04.06.2022 13:55понятно. свои плюсы вы собрали, шагайте дальше.
p.s. следующее высказывание, пожалуй, про PEP8 будет.
randomsimplenumber
05.06.2022 13:11+1Это был намёк на полный перебор всех данных. O(N) и O(ln(N)) - видите разницу?
13werwolf13
04.06.2022 20:08можно выиграть ещё производительности если после запуска перекладывать базу в tmpfs и работать с ней там (я же правильно понял что база меняется не часто?)
Revertis
Вы не добавили индекс на столбец кода. Это может ускорить sqlite ещё во много раз.
edo1h
не может, а точно ускорит.
вообще есть ощущение, что автору надо не статьи писать, а получать базовые знания по базам данных (о большое линейного поиска, поиска по b-tree, поиска по хэш-таблицам).
zoldaten Автор
Проверим.
cur.execute("CREATE INDEX IF NOT EXISTS my_big ON t (UPCEAN);") #создание индекса
Размер на диске - 943Мб (было 774Мб в сsv)
Время поиска:
Воистину!Спасибо.
zoldaten Автор
с памятью тоже все ок:
economist75
Ну раз уж SQLite помогли индексом, то будет честно и Pandas помочь (им же). Для роста скорости загрузки данных и выборки можно сделать это:
1) Избавиться от типов object (например, преобразовать их в string): df.Name.astype('string'). Это же избавит от ошибок при сортировке, сравнении итд по Name
2) То что ищем - нужно сделать индексом: df.set_index('UPCEAN', inplace=True)
3) Обязательно его отсортировать: df.sort_index(inplace=True)
4) Сохранить df в бинарный pickle-формат Python с простым сжатием (zip): df.to_pickle('uhtt_barcode_ref_all.pkl', compression='zip') Сжатие замедлит чтение в ~2 раза, но здорово помогает при скачивании данных по LAN или из Интернет (для Raspberry Pi4 c медленной MicroSD м.б. актуально)
5) При выборке нескольких столбцов по индексу - быстрее всего сработает локатор loc[], а не методы query() или at(). Локатор, кстати, позволяет и изменять исходные данные, не только читать.
В результате по сравнению с итогами поста - имеем ~10-кратное ускорение первичного чтения и ~500-кратное для выборки, и все это - только средствами Python/Pandas. Метрики:
%%time
import pandas as pd
df = pd.read_pickle('uhtt_barcode_ref_all.pkl', compression='zip') # загрузили чистый датасет
Wall time: 5.74 s # было ~1 минута
%%timeit
df.loc[4603726031011, ['Name','CategoryName','BrandName']].to_frame() # выбрали записи
581 µs ± 21.2 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each) # было 0,3 секунды
Отказ от сжатия "консервы" (pkl-файла) или использование pyarrow выведет Pandas в бесспорные победители, но SQLite, безусловно, прекрасна и является самой быстрой SQL БД. К тому же она прекрасно поддерживается в языке Python "из коробки" (стандартная библиотека) и Pandas умеет с ней работать.
zoldaten Автор
Загрузка базы и поиск c pickle, gzip, UPCEAN в index и sort:
то же самое, но без gzip:
Хорошо, но до sql далеко.
economist75
Загрузка из pkl - согласен, не быстрая (грузить приходится все данные). А вот у SQLite реализована очень эффективное чтение файла БД по указателям и индексу (частичное чтение). Хотя у себя на Pi4 вижу вдвое более высокую скорость чтения (13 и 7 секунд), возможно из-за более быстрой MicroSD. Также использую разгон CPU до 2 ГГц (Raspberrry OS 64).
Но поиск (выборка) у SQLite c индексами в вашем опыте - самое лучшее 83 миллисекунды, а у Pandas на Pi4 у меня 0.5-2 миллисекунды. Разница в 40 раз и она достаточна для того чтобы признать Pandas самым быстрым решением из рассмотренных, а возможно и из существующих (особенно если нужно найти несколько кодов).
Для чистоты эксперимента можно проверить еще и IN-MEMORY базы данных SQLite, но уверен что результат будет не лучше чем у Pandas, хотя наверняка приблизится к ней вплотную.