Звісно! Ось вичерпний переклад документації для SqlWrapperService українською мовою.

SQL-обгортка для OpenSearch у NestJS

Цей документ описує SqlWrapperService, ін'єктований сервіс NestJS, що діє як шар трансляції між SQL-подібною мовою запитів та мовою специфічною для домену OpenSearch (DSL). Він дозволяє розробникам надсилати запити до кластера OpenSearch, використовуючи звичний синтаксис SQL, що спрощує типові завдання з отримання та агрегації даних.

Сервіс використовує бібліотеку node-sql-parser для розбору (парсингу) SQL-запитів і трансформує Абстрактне синтаксичне дерево (AST) у відповідний запит OpenSearch DSL.

✨ Особливості

• Трансляція з SQL у DSL: Пишіть прості SQL-запити для роботи з вашими індексами OpenSearch.
• Спрощені запити: Абстрагує складність OpenSearch DSL для поширених сценаріїв використання.
• Підтримувані операції:
  • SELECT, FROM, WHERE
  • ORDER BY
  • LIMIT (з OFFSET та без)
  • AND, OR, а також широкий спектр операторів порівняння (=, !=, >, <, IN, BETWEEN, LIKE, IS NULL тощо).
• Потужні агрегації:
  • COUNT(*) та COUNT(field)
  • SUM(), AVG(), MIN(), MAX()
  • GROUP BY для одного або кількох полів.
  • HAVING для фільтрації згрупованих результатів.
  • SELECT DISTINCT для отримання унікальних значень полів.
• Пошук за шаблоном індексу: Використовуйте вайлдкарди (*) або регулярні вирази (/.../) у FROM для запитів до кількох індексів одночасно.
• Автоматичне використання поля keyword: Розумно використовує .keyword або інші під-поля для агрегацій, сортування та точних запитів (term-level queries) для забезпечення коректності.
• Кешування мапінгів полів: Кешує мапінги індексів, щоб зменшити затримку при повторних запитах.
• Параметризовані запити: Безпечно передавайте динамічні значення у ваші SQL-запити за допомогою плейсхолдерів ($1, $2 тощо).

⚙️ Налаштування та встановлення

1. Залежності

Спершу переконайтеся, що у вашому проєкті NestJS встановлені необхідні пакети.

npm install @opensearch-project/opensearch node-sql-parser

2. Конфігурація модуля

SqlWrapperService потребує екземпляра Client з OpenSearch, доступного для ін'єкції. Рекомендований спосіб — створити для OpenSearch окремий модуль.

Приклад: opensearch.module.ts

Створіть модуль, який надаватиме клієнт OpenSearch. Сервіс використовує токен ін'єкції 'OPEN_SEARCH' для отримання екземпляра клієнта.

// src/opensearch/opensearch.module.ts

import { Module } from '@nestjs/common';
import { Client } from '@opensearch-project/opensearch';

@Module({
  providers: [
    {
      provide: 'OPEN_SEARCH',
      useFactory: () => {
        // Налаштуйте ваш клієнт OpenSearch тут
        const client = new Client({
          node: process.env.OPENSEARCH_URL || 'http://localhost:9200',
          // За потреби додайте інші конфігурації, наприклад, автентифікацію
        });
        return client;
      },
    },
  ],
  exports: ['OPEN_SEARCH'], // Експортуйте провайдер
})
export class OpenSearchModule {}

3. Інтеграція сервісу

Тепер ви можете імпортувати OpenSearchModule у ваш функціональний модуль та надати SqlWrapperService.

Приклад: app.module.ts

// src/app.module.ts

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { SqlWrapperService } from './sql-wrapper.service'; // За потреби змініть шлях
import { OpenSearchModule } from './opensearch/opensearch.module'; // Імпортуйте новий модуль

@Module({
  imports: [OpenSearchModule], // Імпортуйте OpenSearchModule
  controllers: [AppController],
  providers: [
    AppService,
    SqlWrapperService, // Додайте SqlWrapperService до провайдерів
  ],
})
export class AppModule {}

🚀 Використання

Ін'єктуйте SqlWrapperService у ваші контролери чи сервіси та використовуйте його методи queryAll або queryOne.

Публічні методи

• queryAll<T>(sql: string, args: unknown[] = [], returnRaw = false): Promise<T[]>: Виконує SQL-запит і повертає масив результатів.
  • sql: Рядок SQL-запиту.
  • args: Масив значень для заміни плейсхолдерів ($1, $2, ...).
  • returnRaw: Якщо true, повертає "сирі" результати OpenSearch ({ _index, _id, _score, _source }). За замовчуванням false, що повертає лише об'єкт _source.
• queryOne<T>(sql: string, args: unknown[] = []): Promise<T>: Зручний метод, який виконує запит і повертає лише перший результат.

Приклади

Припустимо, у нас є індекс products із полями name, category, price та stock_quantity.

1. Базовий Select з WHERE та ORDER BY

import { Controller, Get } from '@nestjs/common';
import { SqlWrapperService } from './sql-wrapper.service';

@Controller('products')
export class ProductsController {
  constructor(private readonly sql: SqlWrapperService) {}

  @Get()
  async findExpensiveLaptops() {
    const query = `
      SELECT name, price
      FROM products
      WHERE category = 'Laptops' AND price > 1500
      ORDER BY price DESC
      LIMIT 10
    `;
    return this.sql.queryAll(query);
  }
}

2. Параметризований запит

Використання плейсхолдерів запобігає SQL-ін'єкціям і спрощує створення динамічних запитів.

@Get('search')
async searchProducts(
  @Query('category') category: string,
  @Query('minPrice') minPrice: number,
) {
  const query = `
    SELECT name, price, stock_quantity
    FROM products
    WHERE category = $1 AND price >= $2
    LIMIT 50
  `;
  // '$1' буде замінено на category, '$2' на minPrice
  const args = [category, minPrice];
  return this.sql.queryAll(query, args);
}

3. Агрегація COUNT

@Get('count')
async countProducts() {
  const query = `SELECT COUNT(*) FROM products WHERE stock_quantity > 0`;
  const result = await this.sql.queryOne<{ count: number }>(query);
  // Повертає: { count: 1234 }
  return result;
}

4. GROUP BY з агрегаціями та HAVING

Отримати середню ціну та загальну кількість товарів для кожної категорії, але лише для категорій, де товарів більше 10.

@Get('stats')
async getCategoryStats() {
  const query = `
    SELECT
      category,
      AVG(price) as avg_price,
      SUM(stock_quantity) as total_stock
    FROM products
    GROUP BY category
    HAVING COUNT(*) > 10
    ORDER BY avg_price DESC
  `;
  // Повертає: [{ category: 'Monitors', avg_price: 899.99, total_stock: 540 }, ...]
  return this.sql.queryAll(query);
}

5. SELECT DISTINCT

Знайти всі унікальні категорії.

@Get('categories')
async findDistinctCategories() {
  const query = `SELECT DISTINCT category FROM products`;
  // Повертає: [{ category: 'Laptops' }, { category: 'Monitors' }, ...]
  return this.sql.queryAll(query);
}

6. Використання шаблонів індексів

Запит до всіх логів за певний рік.

@Get('logs/2023')
async getLogsForYear() {
  // Використання вайлдкарда
  const query = `SELECT message, level FROM "logs-2023-*" WHERE level = 'error' LIMIT 100`;

  // Використання регулярного виразу (потужніше, але потенційно повільніше)
  // const query = `SELECT message, level FROM /logs-2023-.*/ WHERE level = 'error' LIMIT 100`;

  return this.sql.queryAll(query);
}

📖 Підтримувані можливості SQL

| Можливість | Рівень підтримки | Приклад | |----------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------| | SELECT | *, конкретні стовпці (col1, col2), аліаси (col1 as my_col). | SELECT name, price as p FROM products | | FROM | Один індекс, індекс із вайлдкардами (my-index-*), індекс із регулярним виразом (/my-index-\d+/). | FROM "logs-2023-*" | | WHERE | AND, OR, (). Оператори: =, !=, >, >=, <, <=, IN, NOT IN, BETWEEN, LIKE, ILIKE (нечутливий до регістру), IS NULL, IS NOT NULL, REGEXP. | WHERE (name LIKE '%Macbook%' OR category = 'Laptops') AND price > 1000 | | LIMIT | LIMIT count, LIMIT offset, count. | LIMIT 100 або LIMIT 50, 10 | | ORDER BY | Один або кілька стовпців, ASC або DESC. Підтримує сортування за агрегованими полями в запитах GROUP BY. | ORDER BY category ASC, price DESC | | Агрегати | COUNT(*), COUNT(field), SUM(field), AVG(field), MIN(field), MAX(field). | SELECT AVG(price), MAX(price) FROM products | | GROUP BY | Один або кілька полів. Має використовуватися з агрегатними функціями у SELECT. | GROUP BY category, brand | | HAVING | Фільтрує результати GROUP BY на основі агрегатної функції. | HAVING COUNT(*) > 5 | | DISTINCT | SELECT DISTINCT col1, col2, .... | SELECT DISTINCT category, brand FROM products |

⚠️ Обмеження та застереження

• Не повноцінний SQL-рушій: Цей сервіс є транслятором, а не базою даних. Складні можливості SQL, такі як JOIN, підзапити, віконні функції та загальні табличні вирази (CTE), не підтримуються.
• Обмеження парсера: Трансляція обмежена тим, що може успішно розібрати node-sql-parser. Дуже складний або нестандартний синтаксис SQL може не спрацювати.
• Продуктивність:
  • Запити за шаблонами індексів (* або /.../) можуть бути повільними на великих кластерах. За можливості будьте більш конкретними.
  • Запити LIKE з вайлдкардом на початку (наприклад, '%term') перетворюються на запити OpenSearch wildcard, які є неефективними.
• Пагінація з GROUP BY: Сервіс отримує всі згруповані результати з OpenSearch за допомогою композитної агрегації, а потім застосовує HAVING, ORDER BY та LIMIT в пам'яті. Це може споживати значний обсяг пам'яті, якщо кількість унікальних груп дуже велика.
• DISTINCT для кількох полів: Ця функція реалізована через скриптову агрегацію, яка може бути повільнішою, ніж агрегації по одному полю.
• Мапінги полів: Сервіс покладається на мапінги ваших індексів в OpenSearch. Для агрегацій, сортування та точних збігів (=) він автоматично намагається використовувати під-поле .keyword або .lower, якщо воно доступне. Переконайтеся, що ваші текстові поля мають під-поле keyword для коректної роботи.