TOC
SQLite - это автономная безсерверная SQL база данных.
Д-р Ричард Хипп, создатель SQLite, впервые опубликовал программное обеспечение 17 августа 2000 года. С тех пор он стал вторым по распространённости программным обеспечением в мире. Он используется в важных системах таких как Airbus A350, поэтому неудивительно, что тесты для SQLite 3 сертифицированы для применения в авиации. Само программное обеспечение очень мало, пакет клиента и пакет с библиотекой для системы Debian amd64 составляет 765 КБ в сжатом виде дистрибутива и 2,3 МБ в установленном виде. Программное обеспечение лицензируется по очень лояльной лицензии: Public Domain.
Данный текст явлется переводом. Оригинал по ссылке
Установка клиента SQLite 3
Несмотря на отсутствие серверного ПО у SQLite 3, есть клиент, который позволяет вам иметь доступ к функционалу базовой библиотеки баз данных при помощи командной строки. Ниже приведена полная команда инсталяции SQLite 3. Я запустил следующую команду на свежеустановленной версии Ubuntu 16.04.2 LTS.
:::BASH
$ sudo apt install sqlite3
Настройка клиента
Вы можете изменить поведение CLI SQLite 3 по умолчанию, отредактировав файлы ~/.sqliterc в своем домашнем каталоге. Это удобно для сохранения настроек, которые вы часто используете. Вот пример, в котором я включаю заголовки, устанавливаю режим отображения в столбец, сокращаю приглашение, добавляю таймер к каждой команде и вывожу спец символ для значений NULL.
:::BASH
$ vi ~/.sqliterc
.headers on
.mode column
.nullvalue ¤
.prompt "> "
.timer on
Импорт данных CSV
Вы можете импортировать CSV-данные в SQLite 3 с помощью двух команд. Первая переводит клиент в режим CSV, а вторая импортирует данные из файла CSV. Предполагаемый разделитель по умолчанию - это пайп “|”, поэтому, если вы используете другой символ, вы можете изменить этот параметр с помощью команды .separator.
Если таблица назначения еще не существует, первая строка CSV-файлов будет использоваться для наименования каждого из столбцов. Если таблица существует, то все строки данных будут добавлены в существующую таблицу.
В качестве примера я собрал файл CSV с обоими ASCII и японскими символами, в которых перечислены аэропорты в Уэльсе в Великобритании.
$ vi airport.csv
都市,IATA,ICAO,空港
Aberporth,,EGFA,Aberporth 空港
Anglesey,,EGOQ,RAF Mona
Anglesey,,EGOV,RAF Valley
カナーボン,,EGCK,カナーボン空港
カーディフ,CWL,EGFF,カーディフ国際空港
カーディフ,,EGFC,Tremorfa ヘリポート
チェスター,CEG,EGNR,Hawarden 空港
Haverfordwest,HAW,EGFE,Haverfordwest 小型飛行場
Llanbedr,,EGOD,Llanbedr 空港
Pembrey,,EGFP,Pembrey 空港
St Athan,DGX,EGDX,RAF Saint Athan
スウォンジ,SWS,EGFH,スウォンジ空港
ウェルシュプール,,EGCW,ウェルシュプール空港
Дальше я запускаю клиент SQLite 3 с именем новой базы данных под названием airport.db . Этот файл базы данных еще не существует, поэтому SQLite 3 автоматически создаст его для меня.
$ sqlite3 airport.db
Затем я переключаю клиент в режим CSV, устанавливаю запятую символом разделителем, а затем импортирую файл airports.csv.
.mode csv
.separator ","
.import airports.csv airports
Затем я могу запустить команду schema в таблице новых аэропортов и увидеть два имени столбца на японском языке, а два - с использованием символов ASCII.
.schema airports
CREATE TABLE airports(
"都市" TEXT,
"IATA" TEXT,
"ICAO" TEXT,
"空港" TEXT
);
Для выхода из интерактивного режима взаимодействия с sqlite воспользуемся командой
.exit
Я могу выполнить запросы, смешивая символы японского и ASCII вместе без проблем. Я могу выполнить запросы, смешивая символы японского и ASCII вместе без проблем.
$ echo "select ICAO, 空港 from airports;" \
| sqlite3 airports.db
EGFA|Aberporth 空港
EGOQ|RAF Mona
EGOV|RAF Valley
EGCK|カナーボン空港
EGFF|カーディフ国際空港
EGFC|Tremorfa ヘリポート
EGNR|Hawarden 空港
EGFE|Haverfordwest 小型飛行場
EGOD|Llanbedr 空港
EGFP|Pembrey 空港
EGDX|RAF Saint Athan
EGFH|スウォンジ空港
EGCW|ウェルシュプール空港
Также я могу сделать дамп базы данных на SQL с помощью одной команды.
$ echo ".dump airports" \
| sqlite3 airports.db
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE airports(
"都市" TEXT,
"IATA" TEXT,
"ICAO" TEXT,
"空港" TEXT
);
INSERT INTO "airports" VALUES('Aberporth','','EGFA','Aberporth 空港');
INSERT INTO "airports" VALUES('Anglesey','','EGOQ','RAF Mona');
INSERT INTO "airports" VALUES('Anglesey','','EGOV','RAF Valley');
INSERT INTO "airports" VALUES('カナーボン','','EGCK','カナーボン空港');
INSERT INTO "airports" VALUES('カーディフ','CWL','EGFF','カーディフ国際空港');
INSERT INTO "airports" VALUES('カーディフ','','EGFC','Tremorfa ヘリポート');
INSERT INTO "airports" VALUES('チェスター','CEG','EGNR','Hawarden 空港');
INSERT INTO "airports" VALUES('Haverfordwest','HAW','EGFE','Haverfordwest 小型飛行場');
INSERT INTO "airports" VALUES('Llanbedr','','EGOD','Llanbedr 空港');
INSERT INTO "airports" VALUES('Pembrey','','EGFP','Pembrey 空港');
INSERT INTO "airports" VALUES('St Athan','DGX','EGDX','RAF Saint Athan');
INSERT INTO "airports" VALUES('スウォンジ','SWS','EGFH','スウォンジ空港');
INSERT INTO "airports" VALUES('ウェルシュプール','','EGCW','ウェルシュプール空港');
COMMIT;
Имейте в виду, что созданные файлы .db могут быть больше объема данных в его исходной форме. Во время написания этого блога я подготовил миллионный, 12-столбцовый CSV, состоящий в основном из целых чисел, чисел с плавающей точкой и одного текстового поля. Сжатый CSV-файл с GZIP был 41 МБ, распакованный CSV составлял 142 МБ, а при импорте в SQLite 3 - .db- файл - 165 МБ. Я могу GZIP-сжать файл .db до 48 МБ, но клиент SQLite 3 не сможет работать со сжатой GZIP-ом базой данных.
Создание базы данных в памяти
Локальность расположения данных может быть значительно улучшена за счет хранения базы данных SQLite 3 в памяти, а не на диске; так у вас будут преимущества скорости базы данных в памяти и вместс с легкостью связи с ними с помощью SQL. Ниже приведен пример, где я вычисляю 10 значений Фибоначчи и сохраняю их в базе данных SQLite 3, находящейся в памяти с использованием Python 3.
:::bash
$ sudo apt install python3
$ python3
:::python
import sqlite3
def fib(n):
a, b = 0, 1
for _ in range(n):
yield a
a, b = b, a + b
connection = sqlite3.connect(':memory:')
cursor = connection.cursor()
with connection:
cursor.execute('''CREATE TABLE IF NOT EXISTS fib (
calculated_value INTEGER)''')
cursor.executemany('INSERT INTO fib VALUES (?)',
[(str(x),) for x in fib(10)])
cursor.execute('SELECT * FROM fib')
print(cursor.fetchall())
connection.close()
Вышеприведенные команды создания и вставки таблицы выполняются в контексте соединения. Это соединение оборачивает команды в энергобезопасые атомарные транзакции базы данных. Пользовательские функции
Вы можете создавать пользовательские функции в Python, которые будут выполняться на данных, находящихся внутри базы данных SQLite 3. Ниже я создам небольшую базу данных SQLite 3:
$ sqlite3 urls.db
CREATE TABLE urls (url STRING);
INSERT INTO urls VALUES
('https://packages.debian.org/stretch/sqlite3'),
('https://docs.python.org/3/library/sqlite3.html'),
('https://sqlite.org/about.html');
Затем я создам функцию в Python, которая извлекает имя хоста из URL-адреса и применю её ко всем записям в таблицы URL-адресов в базе данных.
$ python3
import sqlite3
from urllib.parse import urlsplit
def hostname(url):
return urlsplit(url).netloc
connection = sqlite3.connect('urls.db')
connection.create_function('hostname', 1, hostname)
cursor = connection.cursor()
cursor.execute('SELECT hostname(url) FROM urls')
print(cursor.fetchall())
Ниже приведен результат вызова fetchall .
[(u'packages.debian.org',), (u'docs.python.org',), (u'sqlite.org',)]
Работа с несколькими базами данных
Клиент SQLite 3 способен работать с несколькими базами данных в рамках одной сессии. Ниже я запустил клиент, а затем подключу две базы данных в одной сессии.
$ sqlite3
ATTACH 'airports.db' AS airport;
ATTACH 'urls.db' AS urls;
Затем я запущу команду .databases для вывода имен и путей баз данных, прикрепленных к сеансу.
.databases
seq name file
--- --------------- -----------------------
0 main
2 airport /home/mark/airports.db
3 urls /home/mark/urls.db
Я могу указать префиксом в имени таблицы в запросе имя выбраной базы данных.
SELECT COUNT(*) FROM urls.urls;
3
Визуализация данных с помощью Jupyter Notebooks
Jupyter Notebooks - это популярный способ просмотра данных. Ниже я пройду настройку и дам несколько минимальных примеров различных визуализаций, запущенных на наборе данных.
Во-первых, я установлю ряд системных зависимостей.
$ sudo apt update
$ sudo apt install \
libgeos-dev \
python3-dev \
python3-pip \
python3-tk \
python3-venv
Затем я создам виртуальную среду Python, чтобы я мог изолировать свои зависимости Python от других проектов на своей машине. Эта среда будет называться .taxis .
$ pyvenv .taxis
$ source .taxis/bin/activate
Я обновляю пакет «pip» пакета Python от версии 8.1.1, который исходит от команды установки, до версии 9.0.1 в этой виртуальной среде.
$ pip install --upgrade pip
Затем я установил ряд популярных библиотек, написанных для Python.
$ pip install \
https://github.com/matplotlib/basemap/archive/v1.1.0.tar.gz \
'bokeh<0.12.4' \
gmaps \
'holoviews[extras]' \
jupyter \
pandas \
Pillow
Jupyter Notebooks откроет рабочую папку на моей машине Linux через HTTP, поэтому я создам изолированную рабочую папку вдали от других файлов.
$ mkdir -p ~/jupyter-working
$ cd ~/jupyter-working
Затем я включу расширение gmaps и разрешу Jupyter использовать виджеты.
$ jupyter nbextension enable --py --sys-prefix gmaps
$ jupyter nbextension enable --py widgetsnbextension
Затем я запускаю сервер Notebook. После запуска будет указан URL-адрес, содержащий параметр токена. Вставьте этот URL в свой веб-браузер, чтобы открыть интерфейс Notebook.
$ jupyter notebook \
--ip=0.0.0.0 \
--NotebookApp.iopub_data_rate_limit=100000000
...
Copy/paste this URL into your browser when you connect for the first time,
to login with a token:
http://0.0.0.0:8888/?token=123...
Перед открытием URL-адреса я создам базу данных SQLite 3 из файла CSV. Этот набор данных составлен из миллиона случайно выбранных записей из 1,1 миллиарда данных о поездке на такси, которые я опубликовал в своей статье Миллиард таксистов в блоге Redshift . Я использовал следующую команду, чтобы экспортировать миллион записей из Hive.
$ hive -e 'SET hive.cli.print.header=true;
SELECT trip_id,
cab_type,
passenger_count,
trip_distance,
fare_amount,
tip_amount,
pickup_datetime,
dropoff_datetime,
pickup_longitude,
pickup_latitude,
dropoff_longitude,
dropoff_latitude
FROM trips
WHERE RAND() <= 0.001
DISTRIBUTE BY RAND()
SORT BY RAND()
LIMIT 1000000' \
| sed 's/[\t]/,/g' \
| gzip \
> trips.csv.gz
В блоге Spark 2.2 & 3 Raspberry Pi 3 Model B есть краткие инструкции по импорту набора данных в Hive. Если вы используете эти инструкции на чем-то другом, отличающимся от Raspbian, обратите внимание, что имена пакетов, например, для JDK, вероятно, будут отличаться. Обратите внимание, что экспорт из CSV-хранимых данных, а не ORC, завершается в 2-3 раза быстрее.
Вот первые три строки этого CSV-файла. Обратите внимание: первая строка содержит имена столбцов.
$ gunzip -c trips.csv.gz | head -n3
trip_id,cab_type,passenger_count,trip_distance,fare_amount,tip_amount,pickup_datetime,dropoff_datetime,pickup_longitude,pickup_latitude,dropoff_longitude,dropoff_latitude
745713518,yellow,1,5.600,20.50,1.00,2013-04-30 13:43:58,2013-04-30 14:04:49,-73.94273100000000,40.79017800000000,-74.00244499999999,40.76083900000000
788379509,yellow,1,1.200,6.00,0.00,2013-07-07 12:24:33,2013-07-07 12:28:52,-73.95807200000000,40.76124600000000,-73.94632400000000,40.77708900000000
Я распакую файл GZIP и запущу SQLite 3. Я добавлю trip.db в качестве параметра, поэтому создам его при помощи SQLite 3.
$ gunzip trips.csv.gz
$ sqlite3 trips.db
Затем я переключусь в режим CSV, проверю, что разделителем является запятая и импортирую файл CSV в таблицу поездок.
.mode csv
.separator ","
.import trips.csv trips
С импортированными данными я открою URL-адрес ноутбука и создаю Python 3 Notebook в веб-интерфейсе Jupyter. Затем я вставлю следующие требования в первую ячейку, а затем нажмите shift и одновременно введите «выполнить» ячейку.
import sqlite3
import pandas as pd
import holoviews as hv
hv.extension('bokeh')
connection = sqlite3.connect('trips.db')
Вышеприведённое импортирует библиотеку Python для SQLite 3, Pandas - библиотеку обработки данных, Holoviews - библиотеку визуализации и инициализировать расширение Bokeh для Holoviews. Наконец, будет установлено соединение с базой данных SQLite 3 с набором данных о поездках на такси.
В следующем примере я вставляю код sniplet, который создаст карту тепла, показывающую разбивку по будним дням и часам числа поездок на такси в моем наборе данных о поездках на такси.
%%opts Points [tools=['hover']] (size=5) HeatMap [tools=['hover']] Histogram [tools=['hover']] Layout [shared_axes=False]
sql = """SELECT strftime('%w', pickup_datetime) as weekday,
strftime('%H', pickup_datetime) as hour,
COUNT(*) as cnt
FROM trips
GROUP BY 1, 2;"""
df = pd.read_sql_query(sql, connection)
hv.HeatMap(df)
Ниже приводится линейная диаграмма, показывающая количество поездок такси по дате в наборе данных.
%matplotlib inline
sql = """SELECT date(pickup_datetime) as date,
COUNT(*) as cnt
FROM trips
GROUP BY 1
ORDER BY 1;"""
df = pd.read_sql_query(sql, connection)
df['date'] = df.date.astype('datetime64[ns]')
df.plot(x='date', y='cnt')
Для штабелированной гистограммы, сравнивающей желтые и зеленые раскладки кабины друг против друга по месяцам года, вводим следующую информацию в новую ячейку.
%%opts Bars [stack_index=1 xrotation=90 legend_cols=7 show_legend=False show_frame=False tools=['hover']]
hv.extension('bokeh', 'matplotlib')
sql = """SELECT strftime('%m', pickup_datetime) as month,
cab_type,
COUNT(*) as cnt
FROM trips
GROUP BY 1, 2;"""
df = pd.read_sql_query(sql, connection)
table = hv.Table(df, kdims=[('month', 'month'), ('cab_type', 'cab_type')], vdims=[('cnt', 'cnt')])
table.to.bars(['month', 'cab_type'], 'cnt', [])
Ниже приводится круговая диаграмма, показывающая долю поездок в зависимости от часа дня.
%matplotlib inline
sql = """SELECT round(strftime('%H', pickup_datetime)) as hour,
count(*) as cnt
FROM trips
group by 1;"""
df = pd.read_sql_query(sql, connection)
df.plot(kind='pie', y='cnt', legend=False)
Чтобы создать матрицу графика рассеяния, используя следующую палитру с числовыми полями, выполним следующие действия. Заметьте, это может занять несколько минут. Вы должны увидеть выход массива на ранней стадии, а чуть позднее график.
%matplotlib inline
sql = """SELECT round(strftime('%H', pickup_datetime)) as hour,
round(trip_distance),
round(fare_amount),
round(tip_amount)
FROM trips;"""
df = pd.read_sql_query(sql, connection)
pd.plotting.scatter_matrix(df, figsize=(15, 15))
Я натолкнулся на два способа отображения географических точек на картах. Первый - с Matplotlib и Basemap, которые будут работать в автономном режиме и без необходимости использовать ключи API. Ниже будут указаны точки сбора для маршрутов такси в наборе данных.
%matplotlib inline
import matplotlib.pyplot as plt
from mpl_toolkits.basemap import Basemap
sql = """SELECT ROUND(pickup_longitude, 3) as long,
ROUND(pickup_latitude, 3) as lat,
COUNT(*) as cnt
FROM trips
GROUP BY long, lat"""
df = pd.read_sql_query(sql, connection)
df = df[pd.to_numeric(df['long'], errors='coerce').notnull()]
df = df[pd.to_numeric(df['lat'], errors='coerce').notnull()]
df = df.dropna(thresh=1)
df.long = df.long.astype(float, errors='ignore').fillna(0.0)
df.lat = df.lat.astype(float, errors='ignore').fillna(0.0)
plt.figure(figsize=(20, 20))
map = Basemap(projection='merc',
llcrnrlat=40,
urcrnrlat=42,
llcrnrlon=-75,
urcrnrlon=-72,
resolution='i',
area_thresh=50,
lat_0=40.78,
lon_0=-73.96)
map.drawcountries()
map.drawcoastlines(linewidth=0.5)
map.drawstates()
map.bluemarble()
lons = df['long'].values
lats = df['lat'].values
x, y = map(lons, lats)
map.plot(x, y, 'ro', markersize=4)
plt.show()
Вышеприведенное будет выглядеть несколько примитивно и не будет интерактивным.
Следующим шагом построим карту памяти поверх виджета Google Maps. Верхние уровни включают код шаблона значительно меньше, чем выше, сама карта выглядит великолепно и поставляется с различными навигационными элементами управления. Недостатком является то, что вам нужно будет создать ключ API с Google, чтобы использовать этот модуль, и вам нужно будет подключиться к Интернету, когда вы его используете.
Другая проблема заключается в том, что если пары широты / долготы недействительны, вы получите сообщение об ошибке, так как не получится проигноририровать ошибочные пары. Редко происходит так, что набор данных находится в идеальном состоянии, поэтому вам, возможно, придется потратить некоторое время на фильтрацию плохих значений.
Обратите внимание, что в этом упрощенном примере я не взвешивал точки, основанные на агрегированных подсчетах для каждой точки. Вы увидите горячие точки над аэропортами Манхэттена и Нью-Йорка, но это будет не совсем точная картина того, где находятся все горячие точки.
import gmaps
gmaps.configure(api_key="...")
locations = [(float(row['lat']), float(row['long']))
for index, row in df.iterrows()
if -80 < float(row['long']) < -70
and 35 < float(row['lat']) < 45]
fig = gmaps.Map()
fig.add_layer(gmaps.heatmap_layer(locations))
fig
В настоящее время пейзаж визуализации данных Python очень широк, разнообразен и фрагментирован. Это область Python, которая как я ожидаю в ближайшие несколько лет консолидируется и дозреет. Джейк ВандерПлас дал восхитительное выступление на эту тему в PyCon 2017 в Портленде, ее ценность стоит 30 минут вашего времени.
Формирование дампа Pandas DataFrames для SQLite
Pandas DataFrames отлично подходят для создания производных наборов данных с минимальным количеством кода. В довершение всего, сброс Pandas DataFrames обратно в SQLite 3 очень прост. В этом примере я заполню DataFrame некоторыми CSV-данными, создам новую базу данных SQLite 3 и выгружу DataFrame в этот файл.
import sqlite3
import pandas
connection = sqlite3.connect('trips.db')
df = pandas.read_csv('trips.csv', sep=',')
df.to_sql('trips', connection, if_exists='append', index=False)
Выводы
SQLite 3 не игрушка. Это мощное средство с интерфейсом на языке SQL. Поскольку скорость хранения и производительность одного ядра в процессорах увеличиваются то объемы данных, которые можно обрабатывать в разумные сроки, SQLite 3 продолжают расти.
SQLite 3 также является очень хорошим образовательным инструментом. Если кто-то исходит из фона практиков данных с закрытыми исходными кодами, и они пытаются разобраться в мире науки с открытым исходным кодом на Linux (или Windows или Mac OS, если на то пошло), то SQLite 3 обеспечивает очень лёгкость вхождения и приятный опыт обучения.
Существует не одна база данных, которая решает любую проблему, но есть базы данных, которые очень хорошо подходят для решения конкретных проблем. Я определенно считаю SQLite 3 одной из этих баз данных, и я рассматриваю ее как ценный инструмент в своем наборе инструментов.
Спасибо, что нашли время, чтобы прочитать этот пост. Я предлагаю консалтинговые, архитектурные и практические разработки для клиентов в Северной Америке и Европе. Если вы хотите обсудить, как мои предложения могут помочь вашему бизнесу, свяжитесь со мной через LinkedIn .
Картинка взята с xkcd
comments powered by Disqus