Минималистическое руководство по SQLite

Posted by VladymyrL on Saturday, December 30, 2017

TOC

SQLite - это автономная безсерверная SQL база данных.

Д-р Ричард Хипп, создатель SQLite, впервые опубликовал программное обеспечение 17 августа 2000 года. С тех пор он стал вторым по распространённости программным обеспечением в мире. Он используется в важных системах таких как Airbus A350, поэтому неудивительно, что тесты для SQLite 3 сертифицированы для применения в авиации. Само программное обеспечение очень мало, пакет клиента и пакет с библиотекой для системы Debian amd64 составляет 765 КБ в сжатом виде дистрибутива и 2,3 МБ в установленном виде. Программное обеспечение лицензируется по очень лояльной лицензии: Public Domain.

SQLite logo

Данный текст явлется переводом. Оригинал по ссылке

Установка клиента 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