Кэширование внешних SQL-таблиц в Azure Data Explorer

Azure Data Explorer предоставляет несколько подходов к кэшированию данных внешних SQL-таблиц, хотя встроенный кэш результатов запросов не применяется к внешним таблицам. Наиболее эффективное решение для краткосрочного кэширования (1-5 минут) — это периодический импорт в локальную таблицу внутри ADX, в сочетании с правильной конфигурацией политики кэширования и кэшированием результатов часто выполняемых запросов.

Содержание:

• Понимание ограничений внешних таблиц
• Подход с периодическим импортом
• Конфигурация политики кэширования
• Кэширование результатов запросов
• Техники оптимизации производительности
• Шаги реализации
• Альтернативные решения

Понимание ограничений внешних таблиц

Внешние таблицы в Azure Data Explorer обеспечивают удобный доступ к данным SQL Server без необходимости импорта, но они имеют значительные компромиссы в производительности. Как показывает исследование, существует примерно 5-кратная разница в производительности между запросами к внешним таблицам и управляемым таблицам [источник]. Этот разрыв в производительности возникает потому, что внешние таблицы требуют живого подключения к источнику SQL для каждого запроса, обходя собственные механизмы кэширования ADX.

Кэш результатов запросов в Azure Data Explorer работает путем хранения результатов запросов в памяти, но этот механизм не применяется к внешним таблицам [источник]. Каждый запрос к внешней таблице запускает новый запрос к базовому SQL Server, создавая сетевую задержку и нагрузку на базу данных. Для сценариев, требующих частого запроса одних и тех же данных, этот подход становится неприемлемым.

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

Подход с периодическим импортом

Наиболее надежный метод краткосрочного кэширования в Azure Data Explorer — это периодический импорт внешних SQL-данных в локальные таблицы ADX. Этот подход использует инфраструктуру кэширования ADX, обеспечивая необходимую актуальность данных.

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

• Использует горячий кэш ADX: данные, хранящиеся в ADX, выигрывают от кэширования на основе SSD и доступа к памяти
• Снижает нагрузку на SQL Server: минимизирует повторяющиеся запросы к вашей SQL-базе данных
• Включает кэширование результатов запросов: импортированные данные могут использовать кэш результатов запросов ADX
• Гибкие интервалы обновления: могут быть настроены для интервалов 1-5 минут по мере необходимости

Варианты реализации:

1. Непрерывный экспорт с запланированными запросами

.ingest into MyLocalTable 
external data ("https://your-blob-storage/path") 
with ( 
    format = "parquet",
    ingestionMapping = "MyMapping"
)

2. Периодический пакетный импорт
Создайте запланированный запрос, который выполняется каждые 1-5 минут для импорта данных:

.create-continuous-export MyExport 
to 
external_data("https://your-blob-storage") 
with 
(
    format = "parquet",
    schedule = "*/1 * * * *",
    executionLimit = 60 // Запускать в течение 60 минут
)

Исследование подтверждает, что задания непрерывного экспорта Azure Data Explorer позволяют выполнять долгосрочные пакетные запросы и могут быть настроены для конкретных потребностей синхронизации [источник]. Для краткосрочного кэширования вы можете настроить эти задания на частый автоматический запуск.

Конфигурация политики кэширования

После импорта данных в таблицы ADX вы можете оптимизировать производительность за счет стратегической конфигурации политики кэширования. Azure Data Explorer предлагает многоуровневую систему кэширования данных, которая гарантирует, что наиболее релевантные данные остаются кэшированными близко к CPU [источник].

Настройка горячего кэша:

.alter table MyLocalTable 
policy caching 
hot = 1d, 
cold = 30d

Уровни политики кэширования:

• Уровень кластера: влияет на все базы данных и таблицы
• Уровень базы данных: применяется ко всем таблицам в базе данных
• Уровень таблицы: специфичен для отдельных таблиц
• Уровень материализованного представления: для кэширования на основе представлений

Для сценариев краткосрочного кэширования (1-5 минут) вы можете настроить:

.alter database MyDatabase 
policy caching 
hot = 5m, 
cold = 1h

Период кэширования определяет, как долго данные остаются в “горячем кэше” на дисках SSD [источник]. Хотя упоминается, что 31 день является распространенным значением по умолчанию, вы можете настроить гораздо более короткие периоды для вашего конкретного случая использования.

Кэширование результатов запросов

Хотя внешние таблицы не выигрывают от кэширования результатов запросов, ваши локально импортированные данные могут использовать эту мощную функцию. Azure Data Explorer сохраняет результаты запросов в памяти, значительно сокращая время отклика для повторяющихся запросов [источник].

Включение кэширования результатов запросов:

// Выполните ваш запрос - результаты будут кэшированы
MyLocalTable | where Timestamp > ago(1h) | count

// Последующие идентичные запросы будут использовать кэшированные результаты

Лучшие практики для кэширования результатов запросов:

• Используйте согласованные шаблоны запросов для максимизации попаданий в кэш
• Включайте фильтры по меткам времени для запросов, ограниченных по времени
• Ограничивайте наборы результатов, чтобы они помещались в память
• Мониторьте показатели попадания в кэш с помощью .show query_cache_stats

Исследование показывает, что кэширование результатов запросов может “сэкономить значительные ресурсы”, избегая повторения дорогостоящих операций [источник]. Для вашего сценария с внешней SQL-таблицей это означает кэширование результатов ваших периодических запросов импорта, а не необработанных SQL-запросов.

Техники оптимизации производительности

1. Push-запросы

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

.create external table SQLExternalTable 
kind=sql 
with (
    table="dbo.MyTable",
    database="MyDB",
    data_source="MyDataSource"
) 
// Включите push-запросы для лучшей производительности

2. Разбиение данных на разделы

Разбейте ваши импортированные данные на разделы для улучшения производительности запросов:

.create table MyLocalTable (Timestamp: datetime, Value: double) 
partition by bin(TimeStamp, 1h)

3. Материализованные представления

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

.create materialized-view MySummary 
as 
MyLocalTable | summarize count() by bin(Timestamp, 1h)

4. Конфигурация пакетной обработки

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

.alter database MyDatabase 
policy ingestion_batching 
max_events = 1000, 
max_flush_time = 5s

Исследование специально упоминает, что “параметры политики пакетной обработки импорта могут улучшить производительность”, оптимизируя обработку данных [источник].

Шаги реализации

Вот пошаговый подход к реализации краткосрочного кэширования данных вашей внешней SQL-таблицы:

Шаг 1: Создание структуры локальной таблицы

.create table MyCachedTable (Timestamp: datetime, Value: double, SourceId: string)

Шаг 2: Настройка периодического импорта

.create-continuous-export MySQLIngestion 
external data ("https://your-blob-storage/path") 
with (
    format = "parquet",
    schedule = "*/2 * * * *", // Каждые 2 минуты
    executionLimit = 60
)

Шаг 3: Конфигурация политики кэширования

.alter table MyCachedTable 
policy caching 
hot = 5m, 
cold = 1h

Шаг 4: Настройка кэша результатов запросов

Используйте согласованные шаблоны запросов для использования кэша результатов запросов ADX:

// Кэшируйте часто используемую агрегацию
MyCachedTable | summarize count() by bin(Timestamp, 1h)

Шаг 5: Мониторинг производительности

.show operations | where OperationName == "Query" | summarize count() by bin(TimeGenerated, 1h)

Альтернативные решения

1. Интеграция DATA-SYNC

Исследование предлагает использовать функцию DATA-SYNC Azure SQL Database для синхронизации таблиц без использования внешних таблиц [источник]. Это обеспечивает более надежный механизм синхронизации, чем внешние таблицы ADX в одиночку.

2. Гибридный подход

Сочетайте внешние таблицы с локальным кэшированием:

• Используйте внешние таблицы для запросов в реальном времени
• Используйте периодически импортированные данные для аналитических запросов
• Реализуйте бизнес-логику для соответствующей маршрутизации запросов

3. Кэширование наборов результатов

Для очень краткосрочных потребностей в кэшировании (1-2 минуты) рассмотрите:

// Сохраните результаты во временной таблице
let cachedResults = external("SQLExternalTable") | take 1000;
// Используйте кэшированные результаты для последующих запросов
cachedResults | where Value > 100

Заключение

Чтобы эффективно кэшировать данные внешней SQL-таблицы в Azure Data Explorer для улучшения производительности:

1. Используйте периодический импорт как основной подход для краткосрочного кэширования (1-5 минут), используя инфраструктуру горячего кэша ADX
2. Настройте политики кэширования на уровне таблицы с соответствующими периодами горячего кэша для вашего случая использования
3. Включите кэширование результатов запросов для часто выполняемых запросов к вашим импортированным данным
4. Реализуйте задания непрерывного экспорта с соответствующим планированием для поддержания актуальности данных
5. Мониторьте производительность с помощью встроенных инструментов ADX для оптимизации вашей стратегии кэширования

Хотя Azure Data Explorer не предоставляет встроенного кэширования для внешних таблиц, подход с периодическим импортом предлагает наилучший баланс производительности, актуальности и управляемости для ваших требований. Это решение работает полностью в рамках ADX без зависимости от внешних планировщиков или конвейеров Fabric.

Для сценариев, требующих еще более сложной синхронизации, рассмотрите функцию DATA-SYNC Azure SQL Database как дополнительный подход для снижения проблем производительности внешних таблиц [источник].

Источники

1. Performance Optimizations for Applications using Azure Data Explorer - Microsoft Tech Community
2. Optimize for high concurrency with Azure Data Explorer - Microsoft Learn
3. Lesson Learned #56: External tables and performance issues - Microsoft Tech Community
4. Query data in Azure Data Lake using Azure Data Explorer - Microsoft Learn
5. Caching policy (hot and cold cache) - Azure Data Explorer - Microsoft Learn
6. How Azure Data Explorer works - Microsoft Learn
7. From Zero to Hero in Azure Data Explorer: Key Learnings and Architecture Advice - Brightly
8. Using Query results cache in Azure Data Explorer - Microsoft Tech Community
9. Azure Data Explorer Caching, Retention, and “Hot Windows” Explained by Example - Medium
10. How to Achieve Better Performance and less latency while querying SQL and ADX tables together - Microsoft Q&A