Ad hoc reporting что это
SQL-Ex blog
Новости сайта «Упражнения SQL», статьи и переводы
Что такое ad hoc запрос?
Вот простой пример ad hoc запроса в SQL Server:
SQL Server параметризует этот простой запрос:
Вот пример динамического запроса, который параметризован (подготовлен), поэтому он не является ad hoc:
Однако если параметров нет, запрос останется ad hoc. Вот пример ad hoc запроса, который так же является динамическим:
Чем полезны ad hoc запросы?
Во многих случаях разработчик или DBA может один раз выполнить ad hoc запрос, и больше никогда не использовать его. С другой стороны, один и тот же запрос может выполняться тысячи раз за день из приложения, и при этом, возможно, оставаться ad hoc запросом. В зависимости от запроса может не иметь смысла включать его в хранимую процедуру или параметризовать его.
Ad hoc запросы не являются ни плохими, ни хорошими; как и все остальное, это зависит от того, как они используются. Сошлюсь на интересную статью от Phil Factor, посвященную устранению проблем с некоторыми неэффективными ad hoc операторами.
Что такое ad hoc запрос в базе данных?
Чтобы выяснить, рассматривает ли SQL Server запрос как ad hoc, вы можете проверить тип объекта в кэше плана. Вот запрос из книги «Внутри Microsoft SQL Server 2012» Kalen Delaney и др. Замечу, что вам может потребоваться добавить больше фильтров на [text], если он вернет слишком много строк, чтобы отыскать ваш запрос.
Вы увидите тип объекта Adhoc для ad hoc запроса. Для параметризованных запросов вы также увидите строку с типом объекта Prepared. Вызовы хранимых процедур будут возвращать Proc, и имеется несколько других.
Что такое параметр Optimize for Ad Hoc Workload?
Представим себе систему, на которой каждый из большого числа запросов может выполняться только раз. Чтобы избежать ситуации, когда они занимают место в кэше планов, включите параметр Optimize for Ad Hoc Workload. Тогда при первом выполнении запроса в кэше сохраняется только заглушка плана. Если запрос выполняется снова, SQL Server сохранит весь план.
Заключение
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
ad-hoc report
Смотреть что такое «ad-hoc report» в других словарях:
Pointe du Hoc — (IPA2|pwε̃t dy ɔk) is a clifftop location on the coast of Normandy in northern France. It lies 4 miles (6.4 km) west of Omaha Beach, and stands on 100 ft (30 m) tall cliffs overlooking the sea. It was a point of attack by the United States Army… … Wikipedia
Pointe du Hoc — Saltar a navegación, búsqueda Imagen de la Pointe du Hoc. La Punta de Hoc, más conocida por el nombre original francés Pointe du Hoc, es una zona situada en la cima de un acantilado en la costa de Normandía, en el norte de Francia, y está a 30… … Wikipedia Español
Anderson Report — This article refers to a Report on Scientology. For the Anderson Report into UK Higher Education see Anderson Report. The Anderson Report is the colloquial name of the report of the Board of Inquiry into Scientology, an official inquiry into the… … Wikipedia
ad hoc — /ad hok /; Lat. /ahd hohk / 1. for the special purpose or end presently under consideration: a committee formed ad hoc to deal with the issue. 2. concerned or dealing with a specific subject, purpose, or end: The ad hoc committee disbanded after… … Universalium
ad hoc — (ahd HOHK) [Latin: for this] For a particular purpose or thing; up to this time. Usually applied to small groups or committees whose work will continue only as long as a specific problem is under discussion. This ad hoc group of five… … Dictionary of foreign words and phrases
Massaker in Hoc Khojali — Das Massaker von Xocalı ereignete sich am 25. Februar 1992 in der Stadt Xocalı (auch Hocalı, ausgesprochen Chodschali; russisch Ходжалы/Chodschaly, in englischer Transkription aus dem Russischen Khojaly). Im Zuge des bewaffneten Konflikts… … Deutsch Wikipedia
Vela Incident — Orthographic projection centered on the Prince Edward Islands, the location of the Vela incident The Vela Incident (sometimes referred to as the South Atlantic Flash) was an unidentified double flash of light that was detected by an American Vela … Wikipedia
Cognos — Former type Brand (of IBM) Industry Business Intelligence, Operational Intelligence, Performance Management, Workforce Analytics Fate Acquired Founded 1969 Foun … Wikipedia
Old Hungarian alphabet — For the Romanian village of Răvăşel, called Rovás in Hungarian, see Mihăileni, Sibiu. Old Hungarian Type Alphabet Time period unknown to today … Wikipedia
The InterProse Corporation — Infobox company company name = The InterProse Corporation company CC BY SA company type = Private foundation = 1996, incorporated 1997 location city = Vancouver, Washington location country = USA slogan = People Knowledge Technology key people =… … Wikipedia
Как мы автоматизировали выгрузки и другие Ad-hoc задачи аналитика с помощью Zeppelin
На момент написания этой статьи в компании Cardsmobile, которая разрабатывает мобильное приложение «Кошелёк», работает 195 человек: 8 аналитиков и 187 потенциальных заказчиков аналитиков. Мы делаем приложение для конечных пользователей, а также работаем с ритейлом, банками, брендами и другими партнерами. Долгое время работа аналитика в Кошельке состояла не только из исследований поведения пользователя, но и из различных выгрузок, типовых анализов для партнеров и прогнозов для потенциальных клиентов. Конечно, дашборды сильно спасали нам жизнь и позволяли всей компании следить за показателями продукта. Но мы всё ещё тратили время на остальную текучку, и с ростом команды (заказчиков) и бизнеса упёрлись: Ad-hoc задач стало слишком много, а исследования, желание развиваться и светлое будущее простаивали в отсутствие у нас времени.
Так много вокруг классных конференций, интересных статей про различные аналитические исследования, data-science, data-driven, data-счастье. А мы смотрели на всю эту красоту и не знали, где среди всего потока текучки найти время на эксперименты. Многие рассказывают, как сделать классно, но мало кто рассказывает, КАК преодолеть нарастающую текучку и освободить ресурсы для интересных и творческих задач. В этой статье я расскажу про наш опыт выхода в светлое будущее. Дальше будут примеры, как мы автоматизируем Ad-hoc задачи аналитиков в Zeppelin.
Что такое Zeppelin
Zeppelin – это OpenSource Notebook от Apache, который позволяет обращаться к различным БД на разных языках (Python, R, SQL, Spark). Но что делает его особенно кайфовым, так это набор визуальных элементов – dynamic forms.
В одном ноутбуке мы можем извлекать данные по api из Amplitude, быстро считать агрегаты из Clickhouse, дополнять результат данными из MSSQL и обрабатывать все это на Python. А готовые отчеты заворачивать в Excel в удобном заказчику формате и класть в html-ссылку, по которой их можно легко скачать.
Изначально мы начали использовать его просто как notebook, в котором было удобно писать на разных языках. Потом изучили возможности Zeppelin получше, нашли встроенные динамические формы: инбоксы, выпадающие списки и чеклиты – лампочка над головой загорелась! Сразу придумали, сколько всего мы можем автоматизировать. У нас было много типовых задач с готовым кодом, в котором надо было просто менять значения переменных. Мы перенесли весь наш код в Zeppelin, вынесли переменные в динамические формы и дали заказчикам возможность самостоятельно заполнять их и запускать скрипты. Идея понравилась и нам, и всей остальной команде!
Какие динамические формы есть
Input – текстовое поле. Мы используем его, для того чтобы задать временной диапазон для ввода идентификаторов. Другими словами, для всего, вариаций чего бывает много.
Select – выпадающий список. К каждому элементу списка можно прописать сразу готовый кусок кода. Мы предлагаем пользователю выбрать один из нескольких типовых вариантов. Например, одну из метрик для типового отчета.
Checkbox – форма для множественного выбора вариаций. Мы даем его пользователю, чтобы он, к примеру, сам мог выбрать список необходимых полей в выгрузке. Пожалуй, у нас это самый популярный кейс. Или когда мы даем возможность выбрать несколько метрик, сегментов пользователей.
Какие задачи мы автоматизируем в Zeppelin
Выгрузки простые и сложные, с использованием фильтров по дате, партнеру, задавая набор столбцов.
Чаще всего запросы на выгрузки приходят от аккаунт-менеджеров. А еще, с большой вероятностью, они поступают внезапно и срочно. Сами по себе задачи на выгрузку типовые и быстрые в выполнении. Но в действительности они отвлекают от тех самых интересных аналитических исследований, и их число растет по мере роста партнерской сети.
С какими задачами обычно приходят:
Мы создали отчеты для всех частых задач на выгрузки, с которыми к нам обращались. Ускорили процессы наших коллег и высвободили время и внимание для более интересных задач. Теперь только дорабатываем эти отчеты по мере необходимости.
Типовые задачи, в которых надо просто запустить готовый скрипт. Тут тоже применяем фильтры, даём задать значение переменных. Например, пересчет какой-то метрики или отчета, которые используются редко и не хочется ставить их на расписание.
Более изощренный кейс из жизни. Отдел маркетинга совместно с нашими стратегическими партнерами решили провести промо-акцию с определенной механикой. Пользователи нашего приложения должны были совершить цепочку действий, становясь участниками розыгрыша подарков. Раз в неделю мы хотели получать список участников недели, рандомно определять победителей, поздравлять их и отправлять подарки. Аналитик направления создал notebook в Zeppelin, который собирал пользователей, соответствующих условиям участия в розыгрыше за прошедшую календарную неделю. Маркетолог самостоятельно запускал notebook и забирал участников недели.
Подведение итогов А/B-тестов, измерение base-line метрик в тестовой и контрольной группах. Когда мы тестируем новый функционал или триггерную коммуникацию, мы смотрим не только на изменение целевой метрики, но и на то, как в целом меняется поведение пользователя. Мы выделили 4 base-line метрики пользовательского поведения:
Тут Zeppelin дает нам свободу в том, как мы хотим подводить итоги, какие метрики считать, как отрисовывать графики и как объяснять результат тем, кто будет пользоваться этим инструментом.
Собираем базы для коммуникаций и ретаргетинговых кампаний на основе выгружаемой из Amplitude когорты. Когда-то мы отказались от готовых коммуникационных платформ в пользу собственной разработки (возможно, это тема для отдельной статьи, а мы тут не об этом). Наше внутреннее решение было в первую очередь заточено под партнерские рассылки: выбери партнера и отправь сообщение на всю базу. А вот подготовка баз для продуктовых и маркетинговых коммуникаций — то есть собственных коммуникаций Кошелька — легла на плечи аналитиков. Типизировать все запросы от маркетинга и продактов казалось невозможным. Мы все стремились выделить наиболее релевантные сегменты, не ограничивая свои возможности. Например, вымышленный запрос, но запросы аналогичной сложности с нами случались:
Конечно, мы сохраняли код после каждой такой задачи и собирали его в некий монструозный конструктор. Но это все еще было время и внимание аналитика. А ошибка из-за невнимательности могла стоить нам волны негодующих пользователей, которые получили очевидно нерелевантную для них коммуникацию.
И все это так и было, пока один аналитик не обленился достаточно, чтобы не писать код для выборки пользователей в Clickhouse, а собрать когорту в Amplitude и выгрузить ее по api. Что, согласитесь, сильно проще и быстрее. Привычный и уже понятный интерфейс Amplitude, где любой менеджер может самостоятельно собрать когорту со всеми фильтрами из примера выше, проверить ее размер, дополнительно проконтролировать себя и проверить пользователей из когорты, что они попали в нее верно.
Как выглядит механика:
Что происходит в это время:
Я привела пример именно с пуш-рассылками, но у наших коллег быстро возникли идеи, где еще мы можем применять похожий инструмент: любые выгрузки списка пользователей с определенным пользовательским поведением. Сейчас мы используем когорты из Amplitude еще и для запуска ретаргетинговых кампаний. И, думаю, будем использовать и для многих других задач.
Системы мониторинга
Есть еще одна удобная фича, которая правда не относится к динамическим формам и, наверное, не совсем про автоматизацию – запуск по расписанию. Мы используем ее для пересчета дашбордов, запуска разных расчетов. Но самая полезная аналитическая задача, которую мы с ее помощью решаем, это мониторинг. Аномалии в событиях, в поведении метрик, что угодно, за чем должен регулярно следить аналитик, но что тоже хочется автоматизировать. Мы настроили систему алертов в slack и теперь можем вовремя реагировать на изменения, о которых хотим знать:
Success. Победили текучку, освободили время для развития аналитики в компании
Самый приятный абзац этой статьи – наступило светлое будущее! Мы уже автоматизировали большую часть наших Ad-hoc задач. Теперь в спринте их меньше 10%. В освободившееся время мы проводим исследования, выдвигаем и проверяем гипотезы, усложняем наши продукты аналитики и применяем подходы из тех самых статей и выступлений на конференциях. Другими словами, мы наконец занимаемся интересной аналитической работой. А главное, у нас появилось время принимать активное участие в развитии Кошелька.
Совет начинающим автоматизаторам: все частые и типовые куски кода выносите в библиотеки. Это позволит писать быстрее, улучшать качество написания кода всей команды аналитиков и править код в одном месте, а не во всех ноутбуках. И не забывайте, что вы делаете инструмент не для себя, а для своих коллег. А у них разный бэкграунд. Не пугайте их сложными интерфейсами и формулировками, делайте проще и понятнее.
Data-счастье еще впереди, но мы уже сильно воодушевились, ожили и бежим ему навстречу.
Выполнение запросов автоматизированной системы отчетности к нескольким базам данных (База данных SQL Azure)
ОБЛАСТЬ ПРИМЕНЕНИЯ: 
База данных SQL Azure
В этом руководстве вы выполните распределенные запросы ко всем клиентским базам данных, чтобы создать автоматизированную систему отчетности. С помощью таких запросов можно найти интересные сведения в огромных объемах текущих операционных данных приложения SaaS Wingtip Tickets. Чтобы выполнять такие запросы, вам нужно развернуть дополнительную базу данных на сервере каталога и применить эластичные распределенные запросы.
Из этого руководства вы узнаете следующее:
Для работы с этим руководством выполните следующие предварительные требования:
Схема работы автоматизированной системы отчетности
Приложения SaaS позволяют анализировать огромное количество данных клиентов, которые централизованно хранятся в облаке. Такой анализ дает много полезных сведений о том, как работает приложение и как клиенты используют его. Эти сведения будут полезны при разработке функций, помогут повысить удобство использования, а также обеспечить вложение дополнительных средств в приложения и службы.
Доступ к этим данным получить очень просто, если они содержатся в отдельной мультитенантной базе данных, но не так просто, если они распределены по тысячам масштабируемых баз данных. Одним из подходов является использование эластичного запроса, который позволяет выполнять запросы в распределенном наборе баз данных с общей схемой. Этот набор может включать базы данных из нескольких групп ресурсов и разных подписок. Вам нужно лишь создать единую учетную запись и предоставить ей права на извлечение данных из всех баз данных. Эластичный запрос использует отдельную головную базу данных, в которой внешние таблицы определены как зеркальные таблицы или представления в распределенных (клиентских) базах данных. Запросы, отправленные к этой головной базе данных, компилируются для создания распределенного плана запроса с частями запроса, которые при необходимости можно принудительно установить в клиентских базах данных. Эластичный запрос использует карту сегментов в базе данных каталога для определения расположения всех клиентских баз данных. При настройке и выполнении запроса используется стандартный язык Transact-SQL и поддерживаются настраиваемые запросы из специализированных инструментов, таких как Power BI и Excel.
Благодаря распределению запросов между базами данных клиента эластичный запрос позволяет мгновенно получить представление о текущих производственных данных. Тем не менее, так как потенциально эластичный запрос извлекает данные из множества баз данных, задержка при выполнении запроса иногда может быть выше, чем для аналогичных запросов, отправляемых к отдельной мультитенантной базе данных. Чтобы сократить объем возвращаемых данных следует применить проектирование запросов. Как правило, эластичный запрос лучше всего подходит для получения небольших объемов данных в реальном времени, в отличие от часто используемых или сложных аналитических запросов или отчетов. Если запросы работают плохо, изучите план выполнения и узнайте, какая часть запроса передается на удаленную базу данных. Также вы можете оценить, сколько данных получено от базы данных. Чтобы хорошо выполнялись запросы, требующие сложной аналитической обработки, иногда будет полезным извлечь часть клиентских данных в выделенную базу данных и оптимизировать ее для аналитических запросов. Эта базу данных аналитики можно разместить в Базе данных SQL или Azure Synapse Analytics.
Получение скриптов и исходного кода для SaaS-приложения Wingtip Tickets c мультитенантной БД
Сценарии для приложения SaaS Wingtip Tickets c мультитенантной базой данных и исходный код этого приложения вы найдете в репозитории GitHub WingtipTicketsSaaS-MultitenantDB. Инструкции по скачиванию и разблокированию сценариев приложения SaaS Wingtip Tickets см. в статье Общие рекомендации по работе с примерами приложений SaaS Wingtip Tickets.
Создание данных о продажах билетов
Чтобы выполнять запросы к более интересному набору данных, создайте данные о продаже билетов, запустив генератор данных билетов.
Изучение клиентских таблиц
В приложении SaaS мультитенантной базы данных Wingtip Tickets используется гибридная модель управления клиентами. Клиентские данные хранятся в нескольких мультитенантных базах данных и базах данных одного клиента, а также могут перемещаться между ними. При этом важно, чтобы в эластичном запросе к нескольким клиентским базам данных все эти данные можно было рассматривать как часть логически единой базы данных, сегментированной по клиентам.
Для реализации такой схемы работы во всех клиентских таблицах есть столбец VenueId, указывающий на клиента, которому принадлежат соответствующие данные. Здесь значение VenueId вычисляется как хэш от названия объекта (Venue), но вы можете применить любой другой метод получения уникальных значений. Аналогичным способом вычисляется в ключ клиента для использования в каталоге. Эластичный запрос использует таблицы со столбцами VenueId, чтобы разделить запросы на несколько параллельных запросов и передать их соответствующим удаленным клиентским базам данных. Это значительно сокращает объем возвращаемых данных и повышает производительность, особенно при большом числе клиентов, данные которых хранятся в базах данных одного клиента.
Развертывание базы данных, используемой для распределенных запросов автоматизированной системы отчетности
Этот пример развертывает базу данных adhocreporting. Это головная база данных, которая будет содержать схему, используемую для выполнения запросов по всем базам данных клиента. Эта база данных развертывается на имеющемся сервере каталога, используемом для всех баз данных для управления в данном примере приложения.
В \интегрированной среде сценариев PowerShell\ откройте скрипт …\Learning Modules\Operational Analytics Adhoc Reporting**Demo-AdhocReporting.ps1 и задайте в нем следующие значения.
Нажмите клавишу F5, чтобы выполнить скрипт и создать базу данных adhocreporting.
В следующем разделе вы добавите схему в базу данных, чтобы ее можно было использовать для выполнения распределенных запросов.
Настройка головной базы данных для выполнения распределенных запросов
Этот пример добавляет в базу данных автоматизированной системы отчетности схему (внешний источник данных и определения внешних таблиц), которая позволяет выполнять запросы ко всем клиентским базам данных.
Откройте SQL Server Management Studio и подключитесь к базе данных автоматизированной системы отчетности, которую вы создали на предыдущем шаге. Это база данных с именем adhocreporting.
Просмотрите этот сценарий SQL и обратите внимание на следующее.
Эластичный запрос использует учетные данные уровня базы данных для доступа к каждой базе данных клиента. Эти учетные данные должны присутствовать во всех базах данных. Обычно им предоставляются минимальные права, необходимые для поддержки запросов автоматизированной системы отчетности.
При использовании каталога базы данных в качестве внешнего источника данных запросы распределяются по всем базам данных, зарегистрированным в каталоге, при выполнении. Так как имена серверов для каждого развертывания отличаются, этот сценарий инициализации получает расположение каталога базы данных, извлекая имя текущего сервера (@@servername), на котором выполняется сценарий.
В определении внешних таблиц, которые ссылаются на клиентские таблицы, указан параметр DISTRIBUTION = SHARDED(VenueId). Эта инструкция перенаправляет запрос для определенного значения VenueId в соответствующую базу данных, что во многих сценариях повышает производительность запроса, как показано в следующем разделе.
Локальная таблица VenueTypes, которая создается и заполняется. Эта таблица эталонных данных является общей во всех базах данных клиента, поэтому здесь ее можно представить в качестве локальной таблицы и заполнить общими данными. Для некоторых запросов это может уменьшить объем данных, перемещаемых между базами данных клиента и базой данных adhocreporting.
В случае добавления ссылочных таблиц таким способом необходимо обновлять схему и данные таблицы при каждом обновлении базы данных клиента.
Нажмите клавишу F5, чтобы выполнить скрипт и инициализировать базу данных adhocreporting.
Теперь можно выполнять распределенные запросы и собирать данные аналитики по всем клиентам.
Выполнение распределенных запросов автоматизированной системы отчетности
Теперь вы завершили настройку базы данных adhocreporting и можете выполнить примеры распределенных запросов. Добавьте план выполнения, чтобы лучше понимать, где происходит обработка запросов.
При проверке плана выполнения наведите указатель мыши на значки плана, чтобы получить дополнительные сведения.
В SSMS откройте \Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReportingQueries.sql.
Убедитесь в наличии подключения к базе данных adhocreporting.
В меню Запрос выберите Включить действительный план выполнения.
Выделите запрос Which venues are currently registered? и нажмите клавишу F5.
Запрос вернет полный список мест проведения, демонстрируя, как быстро и легко можно выполнить запрос по всем клиентам и вернуть данные из каждого из них.
Просмотрите план, и вы увидите, что все затраты связаны исключительно с удаленным запросом, так как мы просто перебираем базы данных клиента и выбираем информацию о местах проведения.
Выберите следующий запрос и нажмите клавишу F5.
Этот запрос объединяет данные из клиентских баз данных и локальной таблицы VenueTypes (она считается локальной, поскольку размещена в базе данных adhocreporting).
Просмотрите план, и вы увидите, что большая часть затрат связана с удаленным запросом, так как мы запрашиваем информацию о месте проведения каждого клиента (dbo.Venues), а затем быстро выполняем локальное соединение с локальной таблицей VenueTypes, чтобы отобразить понятные имена.
Теперь выберите запрос On which day were the most tickets sold? и нажмите клавишу F5.
Этот запрос выполняет более сложное соединение и агрегирование. Важно отметить, что большая часть обработки выполняется удаленно, и опять же, мы возвращаем только те строки, которые необходимы, получая только одну строку для общего числа проданных билетов за день для каждого места проведения.
Дальнейшие действия
Из этого руководства вы узнали, как выполнять такие задачи:
Теперь ознакомьтесь с руководством Межклиентская аналитика с помощью извлеченных данных, чтобы изучить извлечение данных в отдельную базу данных аналитики для более сложной аналитической обработки.