Перейти к содержанию

Команды SQL

Данный справочник предоставляет основные варианты использования команд SQL в Picodata при работе с распределенной СУБД.

Поддерживаемые функции

Возможности SQL в Picodata включают следующие функции:

  • Data Definition Language (DDL): команды для создания и удаления таблиц;
  • Data Modification Language (DML): команды INSERT для добавления данных в таблицы, UPDATE для обновления данных и DELETE для удаления данных;
  • Data Query Language (DQL): команда SELECT для получения данных из таблиц;
  • Access Control Lists (ACL): команды для управления пользователями и ролями;
  • команда EXPLAIN для отображения планов запросов DML и DQL.

Схемы этих функций на верхнем уровне показаны ниже:

STATEMENT

DML

DQL

DDL

Ниже приведены особенности синтаксиса команд и дано описание часто используемых команд с примерами их выполнения.

Начало работы

Для ввода команд SQL требуется интерактивная консоль Picodata. Она доступна после запуска инстанса с ключом -i или явного подключения к нему из отдельного терминала.

Доступные языки консоли

Для ввода команд можно использовать доступные языки консоли:

  • язык sql позволяет писать запросы непосредственно на языке SQL;
  • язык lua предполагает, что SQL-запрос будет находиться внутри дополнительных элементов Lua-обертки.

Дополнительно стоит отметить, что для использования параметризированных запросов подойдет только язык Lua.

Переключение языков консоли

Для установки языка SQL:

\set language sql

Для возможности вводить многострочные (и легче читаемые) запросы, следует задать разделитель команд:

\set delimeter ;

Указанный в этом примере разделитель (;) используется далее в этом справочнике в примерах команд.

Для установки языка Lua:

\set language lua

В этом случае SQL-запрос должен передаваться в качестве содержимого функции pico.sql(). Тело запроса следует указать внутри скобок. Для языка Lua внутри фигурных скобок опционально указываются параметры значений:

"запрос", {значения передаваемых параметров}

Дополнительно, как показано в примерах данного раздела, можно заключить запрос в парные квадратные скобки с тем, чтобы не использовать экранирование кавычек:

[[запрос]], {значения передаваемых параметров}

Если передаваемых параметров нет, блок фигурных скобок можно оставить пустым или не указывать его совсем. Пример команды:

pico.sql([[select * from "characters"]])

Далее приведены команды с объяснением их действия. Для большинства примеров используется язык консоли SQL кроме тех случаев, когда использование ввода на Lua предпочтительнее.

Управление пользователями

Ниже показаны схемы для создания и удаления пользователей и ролей.

Access control lists

Create user

Drop user

Create role

Drop role

Пример команды для создания пользователя:

create user andy with password 'P@ssw0rd' using md5 option (timeout = 3.0)

Удаление пользователя:

drop user andy

См. также:

Создание таблицы

Схема запроса для создания шардированной таблицы показана ниже.

CreateTable

Column

PrimaryKey

Distribution

Для примера в этом разделе будем использовать две тестовых таблицы для учета персонажей из "Истории игрушек":

  • characters — список персонажей с указанием года выхода на экран;
  • assets — список соответствующих игрушек с указанием их остатков на складе.

Таблицы с пользовательскими данными в Picodata могут быть двух типов:

  • глобальными, т.е. полностью доступными на каждом из инстансов кластера. Для создания глобальной таблицы следует использовать параметр distributed globally.
  • шардированными, т.е. распределенные по шардам кластера. Для создания шардированной таблицы следует использовать параметр distributed by (), указав в скобках название колонки, по которой такая таблица будет распределена. См. подробнее.

Для демонстрации работы распределенного SQL будем создавать шардированные таблицы. Создадим первую таблицу следующей командой:

create table "characters" (
            "id" integer,
            "name" text not null,
            "year" integer,
            primary key ("id")
)
using memtx distributed by ("id")
option (timeout = 3.0) ;

Для второй таблицы команда будет отличаться только именем третьей колонки.

При создании таблицы также можно выбрать один из доступных движков хранения данных:

  • memtx — движок хранения данных в ОЗУ (in-memory);
  • vinyl — дисковый движок хранения данных, использующий LSM-деревья (Log Structured Merge Tree).

Удаление таблицы

Cхема запроса для удаления таблицы показана ниже:

DropTable

Пример удаления таблицы:

drop table "characters" ;

Запрос SELECT

Запрос SELECT используется для получения информации из указанной таблицы в базе данных. Он возвращает 0 или более строк из таблицы согласно поисковому запросу. В контексте распределенной системы, запрос SELECT в Picodata получает информацию из всех сегментов таблицы, которая может храниться на нескольких узлах кластера. В Picodata источником данных для запроса SELECT может выступать таблица, строка значений или другой подзапрос. Также можно соединять несколько запросов одного уровня вместе. Внутри запроса значения имен полей (например, имена колонок) не должны дублироваться.

Cхема возможных распределенных запросов SELECT показана ниже.

SELECT

Примеры запросов

Пример получения данных всей таблицы:

select * from "characters" ;

Вывод в консоль:

---
- metadata:
  - {'name': 'id', 'type': 'integer'}
  - {'name': 'name', 'type': 'string'}
  - {'name': 'year', 'type': 'integer'}
  rows:
  - [1, 'Woody', 1995]
  - [2, 'Buzz Lightyear', 1995]
  - [3, 'Bo Peep', 1995]
  - [4, 'Mr. Potato Head', 1995]
  - [5, 'Slinky Dog', 1995]
  - [6, 'Barbie', 2010]
  - [7, 'Daisy', 2010]
  - [8, 'Forky', 2019]
  - [9, 'Dragon', 2019]
  - [10, 'The Dummies', 2019]
...

Примечание: строки в выводе идут в том порядке, в каком их отдают узлы хранения Picodata.

Параметризированные запросы

В читающих запросах с условиями можно использовать как обычный вид, так и параметризированный. В последнем случае потребуется сначала переключить язык консоли на Lua. Так, следующие две команды дадут одинаковый результат (вывод строки по известному id). :

select "name" from "characters" where "id" = 1;

pico.sql([[select "name" from "characters" where "id" = ?]], {1})

Вывод в консоль:

---
- metadata:
  - {'name': 'name', 'type': 'string'}
  rows:
  - ['Woody']
...

Разница состоит в том, что при параметризации происходит кеширование плана запроса по ключу от шаблона SQL (в данном случае select "name" from "characters" where "id" = ?), и если подобных запросов несколько, то они все смогут использовать кешированный план. Без параметризации у каждого запроса будет свой отдельный план, и ускорения от кеша не произойдет.

Пример вывода строк по нескольким условиям для разных столбцов (также два варианта):

select "name","year" from "characters" where "id" > 3 and "year" > 2000 ;

pico.sql([[select "name","year" from "characters" where "id" > ? and "year" > ? ]], {3, 2000})

Вывод в консоль:

---
- metadata:
  - {'name': 'name', 'type': 'string'}
  - {'name': 'year', 'type': 'integer'}
  rows:
  - ['Barbie', 2010]
  - ['Daisy', 2010]
  - ['Forky', 2019]
  - ['Dragon', 2019]
  - ['The Dummies', 2019]
...

Структурно SQL-запрос состоит из трех частей:

  1. То, что требуется получить
  2. Разделяющий оператор from
  3. Оставшаяся часть запроса, которая логически представляет собой одну сущность и может включать как простое указание на таблицу, так и конструкцию из разных условий.

Имя объекта

Имена объектов (таблиц, колонок, ролей, пользователей и т.д.) могут содержать русские и английские буквы во всех регистрах, цифры, а также символы дефиса (-, U+2010) и нижнего подчеркивания (_, U+005F). Если сами команды SQL нечувствительны к регистру (select равнозначен SELECT), то регистр имен объектов имеет значение при условии, что имена заключены в двойные кавычки. То есть, команды create table "characters"... и create table "Characters"... создадут две разные таблицы.

При разборе SQL содержимое двойных кавычек будет прочитано "как написано". Как следствие, внутри двойных кавычек можно использовать пробелы. В случае наименования без кавычек, имя объекта будет явно приведено в верхний регистр. Например, команды create table characters... и create table "CHARACTERS"... создадут одну и ту же таблицу CHARACTERS.

values

VALUES ( row , )

row

( value , )

alias

password

Пароль пользователя может использовать русские и английские буквы во всех регистрах, а так же цифры и основные знаки препинания из таблицы ASCII. При использовании методов аутентификации chap-sha1 и md5 требуется использовать пароль не короче 8 символов, для метода ldap длина пароля не проверяется.

expression

column_name CAST ( expression AS type ) expression IS NULL IS NOT NULL OR BETWEEN expression AND * / + - = > < >= <= <> != expression reference value SELECT

reference

table . name

value

TRUE FALSE NULL ? INTEGER UNSIGNED DOUBLE DECIMAL STRING row

type

ANY BOOL BOOLEAN DECIMAL DOUBLE INT INTEGER NUMBER SCALAR STRING TEXT UNSIGNED VARCHAR ( length )

Использование агрегатных функций

Поддерживаются следующие агрегатные функции для работы с колонками:

  • COUNT, количество значений в колонке;
  • SUM, сумма значений в колонке (если строк нет, возвращает null);
  • AVG, среднее значение в колонке;
  • TOTAL, сумма значений в колонке (если строк нет, возвращает 0);
  • MIN, минимальное значение в колонке;
  • MAX, максимальное значение в колонке;
  • GROUP_CONCAT ["column_name", 'string'], добавляет к каждому значению колонки column_name указанное значение string.

Пример подсчета общего числа товаров на складе:

select sum("stock") from "assets" ;

Вывод в консоль:

---
- metadata:
  - {'name': 'COL_1', 'type': 'decimal'}
  rows:
  - [10536]
...

Пример добавления текста к значениям в колонке:

select group_concat("name",' character, ') from "characters" ;

Вывод в консоль:

---
- metadata:
  - {'name': 'COL_1', 'type': 'string'}
  rows:
  - ['Woody character, Buzz Lightyear character, Bo Peep character, Mr. Potato Head
      character, Slinky Dog character, Barbie character, Daisy character, Forky character,
      Dragon character, The Dummies']
...

Использование VALUES

Команда VALUES представляет собой конструктор строки значений для использования в запросе SELECT. В некотором смысле, передаваемые с VALUES значения являются временной таблицей, которая существует только в рамках запроса. Использовать VALUES имеет смысл тогда, когда требуется получить набор строк, для которых известны значения одного или более столбцов. Например, с помощью команды ниже можно выяснить название игрушки, зная ее количество на складе:

select "name" from "assets" where ("stock") in (values (2561)) ;

Вывод в консоль:

---
- metadata:
  - {'name': 'name', 'type': 'string'}
  rows:
  - ['Woody']
...

Использование UNION ALL

Команда UNION ALL используется для соединения результатов нескольких запросов. Это может быть полезно для объединения данных из нескольких таблиц, или для удобного отображения разных вычислений или манипуляций со строками таблицы. Результат запроса может содержать дублирующиеся строки.

Для примера предположим, что требуется получить список игрушек с персонажами 1995 года, а также все игрушки, остатки которых на складе превышают 1000 штук:

select "name"
from "characters"
where "year" = 1995
union all
select "name" from "assets"
where "stock" > 1000 ;

Вывод в консоль:

---
- metadata:
  - {'name': 'name', 'type': 'string'}
  rows:
  - ['Woody']
  - ['Buzz Lightyear']
  - ['Bo Peep']
  - ['Mr. Potato Head']
  - ['Slinky Dog']
  - ['Woody']
  - ['Buzz Lightstock']
  - ['Slinky Dog']
...

Использование EXCEPT

Команда EXCEPT используется для соединения нескольких запросов SELECT по принципу исключения. Это означает, к примеру, что из результата первого запроса будут исключены результаты второго, если между ними есть пересечение. EXCEPT может применяться при запросах из разных таблиц, либо разных столбцов одной таблицы, когда нужный результат нельзя получить лишь одним SELECT-запросом.

Используем команду, похожую на предыдущий пример, но с другим смыслом. На этот раз нужно получить список игрушек с персонажами 1995 года, но только если их запасы меньше 1000 штук:

select "name"
from "characters"
where "year" = 1995
except select "name"
from "assets"
where "stock" > 1000 ;

Вывод в консоль:

---
- metadata:
  - {'name': 'name', 'type': 'string'}
  rows:
  - ['Bo Peep']
  - ['Buzz Lightyear']
  - ['Mr. Potato Head']
...

Использование псевдонимов

Использование псевдонимов (aliases) позволяет переопределить названия получаемых столбцов в SELECT-запросах. Псевдоним вставляется после ключевого слова AS и может содержать произвольный текст со следующими ограничениями: он не должен начинаться с цифры и не может содержать пробелов и специальных служебных символов (кроме знака подчеркивания).

Пример для вывода столбцы таблицы:

select "score" as "Total_score" from "scoring" ;
---
- metadata:
  - {'name': 'Total_score', 'type': 'decimal'}
  rows:
  - [78.33]
  - [84.61]
  - [47.28]
...

Пример для функции CAST():

select sum(cast("score" as int)) as "_Total_score_1" from "scoring" ;
---
- metadata:
  - {'name': '_Total_score_1', 'type': 'decimal'}
  rows:
  - [209]
...

Использование JOIN

Команда JOIN используется для комбинирования данных из нескольких таблиц. Но, в отличие от UNION ALL, результатом будет объединение не строк, а столбцов. Таким образом, если UNION ALL добавляет к строкам одной таблицы строки другой (при условии совпадения типов данных в соответствующих столбцах), то JOIN создает новую результирующую таблицу из указанных столбцов этих таблиц. Использование JOIN полезно для соединения связанных друг с другом данных из разных таблиц. Склеивание столбцов требует указания условия (оператор ON). Для примера создадим из двух таблицы выше новую результирующую таблицу, где будут одновременно и остатки игрушек на складе, и годы выхода соответствующих персонажей:

Команда:

select "id","name","stock","year"
from "characters"
join (
select "id" as "number","stock" from "assets"
) as stock
on "characters"."id" = stock."number" ;

Вывод в консоль:

---
- metadata:
  - {'name': 'characters.id', 'type': 'integer'}
  - {'name': 'characters.name', 'type': 'string'}
  - {'name': 'STOCK.stock', 'type': 'integer'}
  - {'name': 'characters.year', 'type': 'integer'}
  rows:
  - [1, 'Woody', 2561, 1995]
  - [2, 'Buzz Lightyear', 4781, 1995]
  - [3, 'Bo Peep', 255, 1995]
  - [4, 'Mr. Potato Head', 109, 1995]
  - [5, 'Slinky Dog', 1112, 1995]
  - [6, 'Barbie', 998, 2010]
  - [7, 'Daisy', 66, 2010]
  - [8, 'Forky', 341, 2019]
  - [9, 'Dragon', 235, 2019]
  - [10, 'The Dummies', 78, 2019]
...

При использование после JOIN подзапроса (см. схему) обязательно следует указать псевдоним (AS) для временной таблицы подзапроса. С помощью дополнительных псевдонимов можно заменить автоматические имена колонок в результирующей таблице на собственные. Пример:

select
  "id" as "id1",
  "name" as "name1",
  "stock" as "stock1",
  "year" as "year1"
from "characters"
join (
  select "id" as "number", "stock" from "assets"
) as stock
on "characters"."id" = stock."number" ;

Вывод в консоль:

---
- metadata:
  - {'name': 'id1', 'type': 'integer'}
  - {'name': 'name1', 'type': 'string'}
  - {'name': 'stock1', 'type': 'integer'}
  - {'name': 'year1', 'type': 'integer'}
  rows:
  - [1, 'Woody', 2561, 1995]
  - [2, 'Buzz Lightyear', 4781, 1995]
  - [3, 'Bo Peep', 255, 1995]
  - [4, 'Mr. Potato Head', 109, 1995]
  - [5, 'Slinky Dog', 1112, 1995]
  - [6, 'Barbie', 998, 2010]
  - [7, 'Daisy', 66, 2010]
  - [8, 'Forky', 341, 2019]
  - [9, 'Dragon', 235, 2019]
  - [10, 'The Dummies', 78, 2019]
...

Более того, использование псевдонимов может быть обязательным, если во внутренней и внешней таблицах есть колонки с одинаковыми именами: так как у реляционного оператора JOIN в плане будут в финальном кортеже все колонки из этих таблиц, то их имена должны быть уникальными.

Использование функции CAST()

Функция CAST() используется для изменения получаемого типа данных при SELECT-запросах. С ее помощью можно преобразовать числа в текст, дробные числа в целые и так далее согласно приведенной выше схеме. Поддерживаемые в Picodata типы данных приведены в отдельном документе.

Пример запроса

В качестве примера покажем преобразование дробных чисел в целые с отбрасыванием дробной части. Используем следующую таблицу:

В обычном виде значения столбца score имеют дробную часть и определены в схеме данных типом decimal:

select "score" from "scoring" ;
---
  'metadata': [
   {'name': 'score', 'type': 'decimal'}],
  'rows': [
    [78.33],
    [84.61],
    [47.28]]
...

Преобразуем эти числа в int:

select cast("score" as int) from "scoring" ;
---
  'metadata': [
  {'name': 'COL_1', 'type': 'integer'}],
  'rows': [
  [78],
  [84],
  [47]]
...

Использование опций неблокирующего SQL

При работе с распределенными SQL-запросами следует принимать в расчет следующие факторы:

  • SQL-запрос может вызывать значительные задержки и блокировать транзакционный поток, что будет мешать выполнению других запросов;
  • SQL-запрос не должен приводить к исчерпанию памяти на отдельных узлах кластера;
  • при объединении результатов локальных запросов в виртуальную таблицу на узле-маршрутизаторе, нужно следить, чтобы эта таблица не разрасталась бесконтрольно.

Для решения этих задач в DQL- и DML-запросах предусмотрены следующие необязательные опции:

  • sql_vdbe_max_steps, ограничение на максимальное число опкодов при работе VDBE на локальных узлах Picodata;
  • vtable_max_rows, ограничение на максимальное число строк в результирующей виртуальной таблице, собирающей результаты отдельных локальных запросов.

Options

Пример использования

select * from "characters" option(sql_vdbe_max_steps = 55, vtable_max_rows = 10) ;
---
- metadata:
  - {'name': 'id', 'type': 'integer'}
  - {'name': 'name', 'type': 'string'}
  - {'name': 'year', 'type': 'integer'}
  rows:
  - [1, 'Woody', 1995]
  - [2, 'Buzz Lightyear', 1995]
  - [3, 'Bo Peep', 1995]
  - [4, 'Mr. Potato Head', 1995]
  - [5, 'Slinky Dog', 1995]
  - [6, 'Barbie', 2010]
  - [7, 'Daisy', 2010]
  - [8, 'Forky', 2019]
  - [9, 'Dragon', 2019]
  - [10, 'The Dummies', 2019]
...

Если ограничения не позволяют выполнить запрос, то будет возвращена ошибка.

При слишком низком значении sql_vdbe_max_steps:

---
- null
- ("Reached a limit on max executed vdbe opcodes. Limit: 54\")
...

При слишком низком значении vtable_max_rows:

---
- null
- (Exceeded maximum number of rows (9) in virtual table: 10\")
...

Запрос INSERT

Команда INSERT используется для помещения (записи) строки данных в таблицу. На данный момент атомарность записи гарантируется только для одной строки в рамках одного запроса.

INSERT

Примеры запросов

Запись строки данных в таблицу командой INSERT возможна как в обычном виде, так и в параметризированном (потребуется переключить язык консоли на Lua). Пример параметризированной вставки строки значений в таблицу:

pico.sql([[insert into "assets" ("id", "name", "stock") values (?, ?, ?)]], {1, "Woody", 2561})

Если вставляемая строка точно содержит значения для всех столбцов, то их можно явно не указывать.

Пример для Lua:

pico.sql([[insert into "assets" values (1, 'Woody', 2561)]], {})

Пример для SQL:

insert into "assets" values (1, 'Woody', 2561) ;

Параметризация значений при INSERT влияет на тип данных при выполнении запроса. Так, в обычном виде дробные числа конвертируются в числа с фиксированной запятой (например, values(2.5) в decimal 2.5). В параметризированном виде дробнному числу будет назначен типа с плавающей запятой (например, values(?), {2.5} в double 2.5). См. подробнее о типах данных.

При использовании INSERT вместе с подзапросом (SELECT) происходит кеширование подзапроса, поэтому его тоже имеет смысл параметризировать, чтобы выиграть в скорости при выполнении последующих подобных запросов:

pico.sql([[insert into "assets" select * from "assets2" where "id2" = ?]], {11}

Результатом INSERT в приведенных примерах будет вывод в консоль информации о количестве успешно вставленных строк:

---
- row_count: 1
...

Обработка конфликтов

В некоторых случаях вставка строки может вернуть ошибку, например, при попытке вставить строку с уже существующим индексом:

insert into "characters" ("id", "name", "year") values (10, 'Duke Caboom', 2019) ;
---
- null
- 'sbroad: Lua error (IR dispatch): LuaError(ExecutionError("sbroad: failed to create
  transaction: RolledBack(FailedTo(Insert, Some(Space), \"TupleFound: Duplicate key
  exists in unique index \\\"primary_key\\\" in space \\\"characters\\\" with old
  tuple - [10, 2695, \\\"The Dummies\\\", 2019] and new tuple - [10, 2695, \\\"Duke
  Caboom\\\", 2019]\"))"))'
...

Для обработки таких ситуаций можно использовать необязательный параметр on conflict, который может принимать одно из трех значений:

  • fail, вернуть ошибку в случае конфликта;
  • replace, затереть старую строку новой по первичному ключу;
  • nothing, ничего не делать (оставить старую версию строки).

Вариант с do fail предполагает, что запрос будет возвращать ошибку в случае конфликта вставки. Может возникнуть ситуация, когда запрос успешно вставит данные на части узлов хранения, но вернет ошибку на остальных (данные на них откатятся), что приведет к неконсистентному состоянию кластера. Чтобы решить эту проблему, можно повторить вставку с другими параметрами разрешения конфликта — например, do replace (замена кортежа на новый).

Вариант с do replace решает проблему конфликтов только в первичном ключе. Если таблица содержит несколько уникальных индексов (помимо первичного ключа) и конфликт произошел в одном из них, do replace вернет ошибку. На узле хранения, где произошел конфликт, данные не будут зафиксированы в таблице.

Вариант с do nothing никогда не возвращает ошибку из-за конфликтов в уникальных индексах, т.к. просто оставляет старую версию строки в таблице. При такой вставке в результате вернется только количество успешно вставленных новых строк (строки где был конфликт и остались прежние данные в подсчет не попадают).

Если параметр on conflict не указан, то по умолчанию используется поведение do fail.

insert into "characters" ("id", "name", "year")
values (10, 'Duke Caboom', 2019)
on conflict do nothing ;
---
- row_count: 0
...

Для успешной вставки (замены строки) следует использовать вариант do replace:

insert into "characters" ("id", "name", "year")
values (10, 'Duke Caboom', 2019)
on conflict do replace ;
---
- row_count: 1
...

Ошибка вставки может также быть вызвана ограничениями неблокирующего SQL. Если речь идет о запросе на вставку более одной строки, то для исправления неконсистентного состояния кластера следует повторить запрос с другими опциями и способом разрешения конфликтов do nothing.

Запрос UPDATE

Команда UPDATE используется для обновления данных в колонках таблицы. При использовании фильтрующего условия (WHERE) можно применять ее только к определенным строкам таблицы. В то время как для полной замены строк можно использовать команду INSERT c параметром on conflict do replace, команда UPDATE подойдет для выборочной замены значений отдельных колонок в нужных строках. В качестве источника данных для UPDATE можно использовать как непосредственно передаваемые значения, так и результат подзапроса в конструкции from (select ...).

UPDATE

При использовании UPDATE следует учитывать следующие ограничения:

  • нельзя обновить значение первичного ключа (например, id);
  • нельзя указать одну и ту же колонку дважды (например, name = 'Duke', name = 'Nukem');
  • нельзя обновить значение в системной колонке bucket_id (оно вычисляется автоматически);
  • передаваемое новое значение не должно противоречить типу данных, присвоенному колонке.

Примеры запросов

Обновление явно указанных данных в отдельной строке с использованием условия:

pico.sql([[update "characters" set "name" = 'Etch', "year" = 2010 where "id" = 2]], {})

Обновление всех значений в колонке:

pico.sql([[update "characters" set "year" = 2010]], {})

Обновление с помощью ссылки на существующую колонку таблицы (без необходимости использовать подзапрос):

pico.sql([[update "characters" set "year" = "year" + 1]], {})

Обновление значений колонки на основе значений другой колонки (с подзапросом):

pico.sql([[update "characters" set "name" = "item" from (select "id" as i, "name" as "item" from "assets") where "id" = i]], {})

В данном случае использования подзапроса (from (select ...)), потребуется указать и фильтр (where) для того, чтобы каждой строке первой таблицы соответствовала ровно одна строка второй таблицы.

В каждом случае результатом успешного выполнения будет сообщение с количеством обработанных строк. Например:

---
- row_count: 10
...

Запрос DELETE

Команда DELETE используется для удаления данных из таблицы.

DELETE

Примеры запросов

Простой запрос удаляет все данные из указанной таблицы:

delete from "characters" ;

Запрос с условием позволяет удалить только нужную строку:

delete from "characters" where "id" = 1 ;

Или несколько строк:

delete from "characters" where "id" in (1,2,3) ;

В всех случаях в выводе в консоль будет указано количество удаленных строк. Например:

---
- row_count: 10
...

При выполнении запроса на удаление нескольких строк может возникнуть ошибка неблокирующего SQL. Если виртуальная таблица на роутере не может вместить предварительный результат выполнения запроса (не хватает емкости виртуальной таблицы vtable_max_rows) и/или задействовано слишком много операций с кортежами на узле хранения (sql_vdbe_max_steps), то запроc будет отработан лишь частично. Следует увеличить значения указанных параметров в [опциях]#options) запроса и повторить его.

Запрос EXPLAIN

Команда EXPLAIN добавляется перед командами SELECT и INSERT для того чтобы показать как будет выглядеть план исполнения запроса, при этом не выполняя сам запрос. План строится на узле-маршрутизаторе и позволяет наглядно оценить структуру и последовательность действий при выполнении запроса. EXPLAIN является инструментом для анализа и оптимизации запросов.

Схема использования EXPLAIN показана ниже.

EXPLAIN

Простые запросы

Для начала рассмотрим план простого запроса на получение данных одного столбца таблицы:

explain select "score" from "scoring" ;

Вывод в консоль:

---
- - projection ("scoring"."score"::decimal -> "score")
  - '    scan "scoring"'
...

Обязательными элементами плана запроса являются scan и projection. Первый узел отвечает за сканирование (получение данных) таблицы, второй — за выборку нужных столбцов. Построение проекции (projection) всегда происходит после сканирования. В рамках построения проекции планировщик создает псевдоним для столбца: "scoring"."score" -> "score".

Если в запросе есть условие (where), то в план добавляется узел selection:

explain select "score" from "scoring" where "score" > 70 ;

Вывод в консоль:

---
- - projection ("scoring"."score"::decimal -> "score")
  - '    selection ROW("scoring"."score"::decimal) > ROW(70::unsigned)'
  - '        scan "scoring"'
...

Если projection выбирает столбцы (атрибуты таблицы), то selection фильтрует данные по строкам (ROW).

Фраза selection ROW("scoring"."score") > ROW(70)' является результатом трансформации фильтра where "score" > 70 в where ("score") > (70), т.е. превращения значения в строку из одного столбца.

Запрос с несколькими проекциями

Пример построения проекции из более сложного запроса:

explain select
  "id","name"
from "characters"
except select
  "id","name" from "assets"
where "stock" > 1000 ;

Вывод в консоль:

---
- - except
  - '    projection ("characters"."id"::integer -> "id", "characters"."name"::string -> "name")'
  - '        scan "characters"'
  - '    projection ("assets"."id"::integer -> "id", "assets"."name"::string -> "name")'
  - '        selection ROW("assets"."stock"::integer) > ROW(1000::unsigned)'
  - '            scan "assets"'
...

В таком плане запроса присутствует два блока projection, перед которыми стоит логическое условие (except). В каждом блоке есть свое сканирование таблицы и, опционально, дополнительный фильтр по строкам (selection).

Варианты перемещения данных

В плане запроса может быть указан параметр motion, который отражает вариант перемещения данных между узлами хранения. Существуют следующие четыре варианта:

  1. Локальная вставка. Представляет собой локальную материализацию данных с подсчетом значений bucket_id для каждого кортежа (соответственно, кортежи будут сгруппированы по этим бакетам). Перемещения данных на другие узлы хранения через узел-маршрутизатор не происходит. На текущем узле хранения будет локально создана виртуальная таблица из результатов читающего запроса или из переданных VALUES, а потом данные из нее будут вставлены локально в целевую таблицу. Планировщик отобразит значение motion [policy: local segment].
  2. Локальная материализация. Данный вариант аналогичен предыдущему с той разницей, что при материализации данных не происходит вычисление bucket_id. При таком запросе планировщик отобразит значение motion [policy: local].
  3. Частичное перемещение. При выполнении запроса на каждый узел кластера будет отправлена только востребованная часть данных (таблица перераспределяется по новому ключу). При таком запросе планировщик отобразит значение motion [policy: segment].
  4. Полное перемещение. На каждый узел кластера будет отправлена вся таблица. Планировщик отобразит значение motion [policy: full].

Перемещение данных происходит в тех случаях, когда в запросе требуется обработать данные из нескольких таблиц или несколько раз из одной таблицы (JOIN, EXCEPT, подзапросы), а также при выполнении агрегатных функций (SUM, COUNT...). Перемещение данных происходит по следующей схеме:

  • на узле-маршрутизаторе (router) собираются запрошенные данные со всех узлов хранения (storage);
  • собранные данные объединяются в виртуальную таблицу с новым ключом шардирования;
  • узел-маршрутизатор отправляет на узлы хранения только нужные им строки из этой виртуальной таблицы.

Таким образом, перемещение обеспечивает корректность выполнения локальных запросов за счет копирования недостающих данных на каждый узел хранения в кластере.

Вариант перемещения данных (motion policy) зависит от того, какие данные доступны на локальных узлах хранения. При запросе к одной таблице перемещения нет никогда. При работе с несколькими таблицами перемещения также может не быть, если в каждой части запроса адресуются те столбцы, по которым таблица распределена (указан ключ шардирования).

Локальная вставка характерна для INSERT с передачей строки значений:

explain insert into "assets" values (1, 'Woody', 2561) ;

Вывод в консоль:

---
- - 'insert "assets" on conflict: fail'
  - '    motion [policy: local segment([ref("COLUMN_1")])]'
  - '        values'
  - '            value row (data=ROW(1::unsigned, ''Woody''::string, 2561::unsigned))'
...

Локальная материализация относится к тем случаям, когда требуется положить в память прочитанные данные из локального запроса для их дальнейшей обработки. Перемещения данных нет и вычисление bucket_id не требуется (см. подробнее). Примером может служить удаление данных из таблицы:

explain delete from "characters" where "id" = 1 ;

Вывод в консоль:

---
- - delete "characters"
  - '    motion [policy: local]'
  - '        projection ("characters"."id"::integer -> pk_col_0)'
  - '            selection ROW("characters"."id"::integer) = ROW(1::unsigned)'
  - '                scan "characters"'
...

Локальная материализация происходит и при обновлении данных в тех случаях, если не затрагивается колонка, по которой таблица шардирована. Например, если при создании таблицы было указано шардирование по колонке id (distributed by ("id")), то обновление данных в других колонках не приведет к их перемещению через узел-маршрутизатор. Поскольку при UPDATE не происходит пересчет bucket_id, то планировщик использует политику local:

picodata> pico.sql([[explain update "characters" set "year" = 2010]], {})

Вывод в консоль:

---
- - update "characters"
  - '"year" = COL_0'
  - '    motion [policy: local]'
  - '        projection (2010::unsigned -> COL_0, "characters"."id"::integer -> COL_1)'
  - '            scan "characters"'
  - 'execution options:'
  - sql_vdbe_max_steps = 45000
  - vtable_max_rows = 5000
...

Частичное перемещение происходит, когда требуется отправить на узлы хранения недостающую часть таблицы.

Пример INSERT со вставкой из читающего запроса другой таблицы, у которой отличается ключ шардирования:

explain insert into "assets" select * from "assets3" where "id3" = 1 ;

Вывод в консоль:

---
- - 'insert "assets" on conflict: fail'
  - '    motion [policy: segment([ref("id3")])]'
  - '        projection ("assets3"."id3"::integer -> "id3", "assets3"."name3"::string ->
             "name3", "assets3"."stock3"::integer -> "stock3")'
  - '            selection ROW("assets3"."id3"::integer) = ROW(1::unsigned)'
  - '                scan "assets3"'
...

Пример JOIN двух таблиц с разными ключами шардирования:

explain select
  "id","name"
from "assets"
join (
  select "id3","name3" from "assets3"
  ) as "new_assets"
on "assets"."id" = "new_assets"."id3" ;

Вывод в консоль:

---
- - projection ("assets"."id"::integer -> "id", "assets"."name"::string -> "name")
  - '    join on ROW("assets"."id"::integer) = ROW("new_assets"."id3"::integer)'
  - '        scan "assets"'
  - '            projection ("assets"."id"::integer -> "id", "assets"."name"::string
    -> "name", "assets"."stock"::integer -> "stock")'
  - '                scan "assets"'
  - '        motion [policy: segment([ref("id3")])]'
  - '            scan "new_assets"'
  - '                projection ("assets3"."id3"::integer -> "id3", "assets3"."name3"::string -> "name3")'
  - '                    scan "assets3"'
...

Пример UPDATE с обновлением колонки, по которой шардирована таблица (например, distributed by ("id", "name")):

picodata> pico.sql([[explain update "characters" set "name" = 'Etch', "year" = 2010 where "id" = 2]], {})

Вывод в консоль:

---
- - update "characters"
  - '"id" = COL_0'
  - '"name" = COL_1'
  - '"year" = COL_2'
  - '    motion [policy: segment([])]'
  - '        projection ("characters"."id"::integer -> COL_0, ''Etch''::string ->
    COL_1, 2010::unsigned -> COL_2, "characters"."id"::integer -> COL_3, "characters"."name"::string
    -> COL_4)'
  - '            selection ROW("characters"."id"::integer) = ROW(2::unsigned)'
  - '                scan "characters"'
  - 'execution options:'
  - sql_vdbe_max_steps = 45000
  - vtable_max_rows = 5000
...

Полное перемещение происходит, когда требуется скопировать всю внутреннюю таблицу (в правой части запроса) на все узлы, содержащие внешнюю таблицу (в левой части).

Пример JOIN с соединениям не по колонкам шардирования для обеих таблиц:

explain select
  "id","name","stock","year"
from "characters"
join (
  select "id" as "number","stock" from "assets"
) as stock
on "characters"."id" = stock."number" ;

Вывод в консоль:

---
- - projection (
    - "characters"."id" -> "id",
    - "characters"."name" -> "name",
    - "STOCK"."stock" -> "stock",
    - "characters"."year" -> "year")',
    - '    join on ROW("characters"."id") = ROW("STOCK"."number")',
    - '        scan "characters"',
    - '            projection (
    -   "characters"."id" -> "id",
    -   "characters"."name" -> "name",
    -   "characters"."year" -> "year")',
    -   '                scan "characters"',
    -   '        motion [policy: full]',
    -   '            scan "STOCK"',
    -   '                projection (
    -     "assets"."id" -> "number",
    -     "assets"."stock" -> "stock")',
    -     '                    scan "assets"'
...

Пример выполнения агрегатной функции.

explain select count("id") from "characters" ;

Вывод в консоль:

---
- - projection (sum(("8278664dae744882bfeec573f427fd0d_count_11"::integer))::decimal
    -> "COL_1")
  - '    motion [policy: full]'
  - '        scan'
  - '            projection (count(("characters"."id"::integer))::integer -> "8278664dae744882bfeec573f427fd0d_count_11")'
  - '                scan "characters"'
...

Читать далее: Типы данных SQL