Pandas vs dask vs sqlite на raspberry pi
Возникла необходимость поработать с базой штрихкодов (barcode) на одноплатнике raspberry pi. Задача простая — открыть базу данных (~700 Мб), найти штрихкод и сделать это «достаточно быстро». Как оказалось, эти действия могут вылиться в захватывающее путешествие как в части того, как в базе быстро найти нужный код, так и в части того, как эту базу быстро открыть, чтобы найти. В работе помогали фреймворки pandas, dask, а также инструмент sqlite. Кто из них справился и какие есть нюансы — в данной статье.
Сама база кодов (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.
Скачать: