Работа с SQL и Apache Calcite#

Описание SQL-движка на основе Apache Calcite#

Внимание

Новый SQL-движок находится в статусе beta.

Начиная с версии 4.2130 DataGrid поставляется с новым SQL-движком, который основан на фреймворке Apache Calcite.

Apache Calcite — фреймворк динамического управления данными, который служит посредником между приложениями, одним или несколькими местами хранения данных и механизмами их обработки.

У текущего SQL-движка, основанного на H2, есть набор фундаментальных ограничений, которые связаны с исполнением SQL-запросов в распределенной среде. Новый движок позволяет обойти эти ограничения. Он использует инструменты Apache Calcite для планирования и обработки запросов и новый процесс для их исполнения.

Фазы выполнения запроса через Calcite:

1. Обработка:

   • Вход — строка самого запроса.

   • Выход — синтаксическое дерево (AST — Abstract Syntax Tree).

2. Валидация (семантический анализ):

   • Вход — синтаксическое дерево (AST) и метаданные. На данном этапе синтаксическое дерево проверяется на соответствие метаданным.

   • Выход — AST с привязкой к конкретным метаданным.

3. Построение логического плана запроса на основе AST:

   • Вход — AST.

   • Выход — логический план запроса (дерево реляционных операторов).

4. Оптимизация:

   • Вход — логический план запроса и статистика.

   • Выход — физический план запроса (дерево реляционных операторов с привязкой к конкретному способу выполнения запроса).

5. Выполнение:

   • Вход — физический план запроса.

   • Выход — результат выполнения (курсор).

Способы настройки SQL-движка на основе Apache Calcite#

Режим Standalone#

При запуске кластера в режиме Standalone перед запуском скрипта ignite.sh или ignite.bat переместите подкаталоги optional/ignite-calcite и optional/ignite-slf4j в каталог libs. В этом случае контент папки, в которой находится модуль, добавится в classpath.

Конфигурация Maven#

Если в проекте используются библиотеки DataGrid, добавьте в зависимости модуль ignite-calcite. В случае использования Maven добавьте следующую зависимость (замените параметр ${ignite.version} на необходимую версию DataGrid):

<dependency>
    <groupId>org.apache.ignite</groupId>
    <artifactId>ignite-calcite</artifactId>
    <version>${ignite.version}</version>
</dependency>

Конфигурация SQL-движков#

Для включения SQL-движка на основе Apache Calcite укажите CalciteQueryEngineConfiguration в разделе SqlConfiguration.QueryEnginesConfiguration.

Пример конфигурации двух SQL-движков (H2 и Calcite), где движок по умолчанию — Calcite:

• XML:

  <bean class="org.apache.ignite.configuration.IgniteConfiguration">
      <property name="sqlConfiguration">
          <bean class="org.apache.ignite.configuration.SqlConfiguration">
              <property name="queryEnginesConfiguration">
                  <list>
                      <bean class="org.apache.ignite.indexing.IndexingQueryEngineConfiguration">
                          <property name="default" value="false"/>
                      </bean>
                      <bean class="org.apache.ignite.calcite.CalciteQueryEngineConfiguration">
                          <property name="default" value="true"/>
                      </bean>
                  </list>
              </property>
          </bean>
      </property>
      ...
  </bean>
  

• Java:

  IgniteConfiguration cfg = new IgniteConfiguration().setSqlConfiguration(
      new SqlConfiguration().setQueryEnginesConfiguration(
          new IndexingQueryEngineConfiguration(),
          new CalciteQueryEngineConfiguration().setDefault(true)
      )
  );
  

Исполнение запросов с указанием SQL-движка#

Обычно все запросы направляются в SQL-движок, который настроен по умолчанию. Если в queryEnginesConfiguration настроено более одного движка, можно указать один из них для исполнения отдельных запросов или всего подключения к базе данных.

JDBC#

Чтобы выбрать SQL-движок для JDBC-соединения, используйте параметр queryEngine.

Пример:

jdbc:ignite:thin://127.0.0.1:10800?queryEngine=calcite

где queryEngine=calcite — используемый движок.

ODBC#

SQL-движок для ODBC-соединения можно настроить с помощью свойства QUERY_ENGINE.

Пример:

[IGNITE_CALCITE]
DRIVER={Apache Ignite};
SERVER=127.0.0.1;
PORT=10800;
SCHEMA=PUBLIC;
QUERY_ENGINE=CALCITE

Подсказка QUERY_ENGINE#

Используйте подсказку (hint) QUERY_ENGINE, чтобы выбрать определенный движок для отдельных запросов:

SELECT /*+ QUERY_ENGINE('calcite') */ fld FROM table;

Справочник по SQL#

DDL#

DDL-команды (Data Definition Language, язык описания данных) совместимы со старым движком на основе H2. Подробнее об этом написано в официальной документации Apache Ignite.

DML#

Новый SQL-движок в основном наследует синтаксис DML (Data Manipulation Language, язык манипулирования данными) от фреймворка Apache Calcite.

В большинстве случаев синтаксис команд совместим со старым SQL-движком, но есть некоторые отличия между DML-диалектами в движках на основе H2 и Calcite. Например, изменился синтаксис команды MERGE. Подробнее об этом написано в SQL-справочнике Apache Calcite.

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

SQL-движок на основе Calcite поддерживает функции:

Подробнее о функциях написано в SQL-справочнике Apache Calcite.

Поддерживаемые типы данных#

Типы данных, которые поддерживает SQL-движок на основе Calcite:

Оптимизация запросов с помощью подсказок#

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

Примечание

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

Формат подсказок#

Подсказки SQL задаются специальным комментарием /*+ HINT */, который называется блоком подсказок. Пробелы до и после названия подсказки обязательны. Блок подсказок размещается сразу после реляционного оператора, обычно после SELECT. Несколько блоков для одного реляционного оператора использовать нельзя.

Пример:

SELECT /*+ NO_INDEX */ T1.* FROM TBL1 where T1.V1=? and T1.V2=?

Допускается задавать несколько подсказок для одного реляционного оператора. Для этого разделите их запятыми (пробелы необязательны).

Пример:

SELECT /*+ NO_INDEX, EXPAND_DISTINCT_AGG */ SUM(DISTINCT V1), AVG(DISTINCT V2) FROM TBL1 GROUP BY V3 WHERE V3=?

Параметры подсказок#

Если требуются параметры подсказок, поместите их в скобки после названия подсказки и разделите запятыми.

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

Пример:

SELECT /*+ FORCE_INDEX(TBL1_IDX2,TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;
 
SELECT /*+ FORCE_INDEX('TBL2_idx1') */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;

Области видимости подсказок#

Подсказки определяются для реляционного оператора, обычно для SELECT.

Большинство подсказок видны своим реляционным операторам, последующим операторам, запросам и подзапросам. Определенные в подзапросе подсказки видны только ему самому и его подзапросам. Подсказку не видно предыдущему реляционному оператору, если ее определили после него.

Пример:

SELECT /*+ NO_INDEX(TBL1_IDX2), FORCE_INDEX(TBL2_IDX2) */ T1.V1 FROM TBL1 T1 WHERE T1.V2 IN (SELECT T2.V2 FROM TBL2 T2 WHERE T2.V1=? AND T2.V2=?);
 
SELECT T1.V1 FROM TBL1 T1 WHERE T1.V2 IN (SELECT /*+ FORCE_INDEX(TBL2_IDX2) */ T2.V2 FROM TBL2 T2 WHERE T2.V1=? AND T2.V2=?);

В примере ниже подсказка есть только у первого запроса:

SELECT /*+ FORCE_INDEX */ V1 FROM TBL1 WHERE V1=? AND V2=?
UNION ALL
SELECT V1 FROM TBL1 WHERE V3>?

Исключение: подсказки уровня движка или оптимизатора, например DISABLE_RULE и QUERY_ENGINE, нужно определять в начале запроса. Они относятся ко всему запросу.

Ошибки в подсказках#

Оптимизатор пытается применить каждую подсказку и ее параметры, если это возможно. Он пропускает подсказку или параметр, если:

• такой подсказки не существует или она не поддерживается;

• не передали необходимые для подсказки параметры;

• параметры подсказки передали, но она их не поддерживает;

• параметр подсказки некорректен или ссылается на несуществующий объект (например, индекс или таблицу);

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

Поддерживаемые подсказки#

FORCE_INDEX#

Принудительное использование индексов для сканирования таблиц.

Параметры:

• Пусто — принудительное использование индексов для сканирования таблиц. Оптимизатор выберет любой доступный индекс.

• Одно название индекса — оптимизатор использует указанный индекс.

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

Пример:

SELECT /*+ FORCE_INDEX */ T1.* FROM TBL1 T1 WHERE T1.V1 = T2.V1 AND T1.V2 > ?;

SELECT /*+ FORCE_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;

NO_INDEX#

Отключает сканирование индексов.

Параметры:

• Пусто — не использовать индексы при сканировании таблиц. Оптимизатор отключит все индексы.

• Одно название индекса — оптимизатор пропустит указанный индекс.

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

Пример:

SELECT /*+ NO_INDEX */ T1.* FROM TBL1 T1 WHERE T1.V1 = T2.V1 AND T1.V2 > ?;

SELECT /*+ NO_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;

ENFORCE_JOIN_ORDER#

Устанавливает порядок соединений (JOIN), который указан в запросе. Оптимизатор не будет пытаться изменить порядок исполнения. Позволяет ускорить построение плана запроса с большим количеством соединений.

Пример:

SELECT /*+ ENFORCE_JOIN_ORDER */ T1.V1, T2.V1, T2.V2, T3.V1, T3.V2, T3.V3 FROM TBL1 T1 JOIN TBL2 T2 ON T1.V3=T2.V1 JOIN TBL3 T3 ON T2.V3=T3.V1 AND T2.V2=T3.V2

SELECT t1.v1, t3.v2 FROM TBL1 t1 JOIN TBL3 t3 on t1.v3=t3.v3 WHERE t1.v2 in (SELECT /*+ ENFORCE_JOIN_ORDER */ t2.v2 FROM TBL2 t2 JOIN TBL3 t3 ON t2.v1=t3.v1)

EXPAND_DISTINCT_AGG#

Принудительно разворачивает несколько операций агрегирования с ключевым словом DISTINCT через соединения (JOIN). Удаляет дубликаты перед объединением (JOIN) и ускоряет его.

Пример:

SELECT /*+ EXPAND_DISTINCT_AGG */ SUM(DISTINCT V1), AVG(DISTINCT V2) FROM TBL1 GROUP BY V3

QUERY_ENGINE#

Выбор конкретного движка для выполнения отдельных запросов. Указание на уровне движка.

Параметры:

Требуется один параметр — название движка.

Пример:

SELECT /*+ QUERY_ENGINE('calcite') */ V1 FROM TBL1

DISABLE_RULE#

Отключает определенные правила оптимизатора. Указание на уровне оптимизатора.

Параметры:

Одно или несколько правил оптимизатора для пропуска.

Пример:

SELECT /*+ DISABLE_RULE('MergeJoinConverter') */ T1.* FROM TBL1 T1 JOIN TBL2 T2 ON T1.V1=T2.V1 WHERE T2.V2=?