BigQuery и DataStudio для хранения технических метрик и их анализа

23 октября 2020

BigQuery и DataStudio для хранения технических метрик и их анализа

Все чаще компании экспортируют продуктовые и технические метрики в облачные сервисы. Естественно, зачем хранить в базе данных приложения огромные объемы данных, которые не участвуют в работе приложения, если их можно экспортировать во внешние хранилища и там же анализировать?

Мы тоже двигаемся в этом направлении. Наша команда занимается разработкой 1С - приложения Tardis (MCOB. Управление бухгалтерией). Приложение используется компаниями уполномоченных бухгалтерий для управления и комплексного взаимодействия c базами 1С, в которых ведется бухгалтерский учет.

А еще Tardis выполняет роль сборщика и хранилища показателей производительности Apdex баз облачного сервиса. Пользователи уполномоченных бухгалтерий данными Apdex не пользуются. А данных собирается довольно много, часто более 300 тысяч записей в день.

Чтобы оптимизировать размер базы данных приложения, мы решили вынести хранение показателей Apdex во внешний облачный сервис. Критериями выбора облачного сервиса были возможность интеграции по API и построение отчетов по данным показателей во внешнем сервисе.

После изучения популярных облачных решений мы остановились на сервисах BigQuery и DataStudio из Google Cloud Platform.

Общее описание

BigQuery - это облачная база данных, предназначенная для анализа больших наборов данных. Сервис предоставляет возможности для создания таблиц, полей в таблицах, записи и чтения данных из таблиц. Работать с сервисом можно через веб интерфейс и посредством API.

https://cloud.google.com/bigquery

На момент написания статьи сервис BigQuery Api является бесплатным. При его активации также активируется сервис BigQuery Storage API, который добавляет улучшенные механизмы взаимодействия с базой данных. При методах работы с сервисом, описанных в статье, сервис BigQuery Storage API не используется.

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

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

https://cloud.google.com/bigquery/quotas

DataStudio - это тоже сервис от Google, который позволяет строить отчеты на основе данных из внешних источников, например таких, как таблицы BigQuery. https://developers.google.com/datastudio

Настройка проекта в Google Cloud Platform

Работа по настройке сервисов происходит на форме Google Cloud Platform под выбранной учетной записью Google.

https://console.cloud.google.com/

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

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

Чтобы начать использовать BigQuery, нужно иметь платежный аккаунт Google (к аккаунту должна быть привязана платежная карта).

Если в выбранном Google аккаунте не создан платежный аккаунт, следует его добавить. Без привязки способа оплаты не будут доступны методы загрузки и получения данных через API.

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

После настройки связки проекта с платежным аккаунтом необходимо в панели сервисов выбрать сервис BigQuery. Если это новый проект, то форма предложит включить необходимые API для сервиса.

При активации API в разделе описания доступных сервисов появятся новые активированные сервисы. BigQuery тянет за собой много дополнительных API сервисов, поэтому список всех сервисов может быть примерно таким.

Если все действия выполнены правильно, то при открытии панели управления сервиса BigQuery появится форма без предупреждений об ошибках в верхнем разделе формы.

Аутентификация Google APIs

Для работы со всеми сервисами Google, в том числе с BigQuery, через API необходимо проходить процедуру аутентификации.

Для получения авторизационных данных в разделе APIs & Services нужно создать учетные данные типа OAuth client ID (работа с сервисом BigQuery через API key не поддерживается).

OAuth client ID предполагает авторизацию через веб форму с последующим перенаправлением на сторону веб приложения с передачей токена доступа и токена обновления. Если для работы с сервисом нет необходимости в использовании опубликованного веб приложения, то можно получить токены упрощенным способом.

Чтобы это сделать, нужно создать и заполнить учетные данные авторизации. Среди списка URL адресов обязательно указать bigquery и localhost.

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

Формируется ссылка для получения кода авторизации.

где client_id - это пользовательский ключ учетных данных OAuth client ID

Когда ссылка сформирована, выполняется переход по ссылке (url preview) в браузере с учетной записью Google, открывается форма авторизации.

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

Далее выполняется запрос через REST клиент для получения токена обновления и токена доступа.

где client_id - пользовательский ключ учетных данных OAuth client ID

client_secret - секретный ключ учетных данных OAuth client ID

code - код авторизации, полученный на предыдущем шаге

В коде 1С подготовка запроса выглядит так:

Процедура ОбновитьДанныеОблачногоХранилища(ДанныеАвторизации, ОписаниеОшибки = "")

					Попытка

					АдресРесурса = "oauth2/v3/token"
					+ "?refresh_token=" + ДанныеАвторизации.refresh_token
					+ "&client_id=" + ДанныеАвторизации.client_id
					+ "&client_secret=" + ДанныеАвторизации.secret_id
					+ "&grant_type=refresh_token";

					СтруктураПараметров = Новый Структура;
					СтруктураПараметров.Вставить("АдресРесурса", АдресРесурса);
					СтруктураПараметров.Вставить("Токен", "");
					СтруктураПараметров.Вставить("ИменаПолейСТипомДата", Неопределено);
					СтруктураПараметров.Вставить("ТипЗапроса", "POST");
					СтруктураПараметров.Вставить("ТелоЗапроса", "");
					СтруктураПараметров.Вставить("КодСостояния", "");
					СтруктураПараметров.Вставить("Успех", Ложь);
					СтруктураПараметров.Вставить("СтруктураЗаголовков", Неопределено);
					СтруктураПараметров.Вставить("ПолучитьКакДвоичныеДанные", Ложь);
					СтруктураПараметров.Вставить("Хранилище", Неопределено);

					Ответ = АрхивДокументов.УниверсальныйЗапросGoogleDrive(СтруктураПараметров);
					Если ЗначениеЗаполнено(ОписаниеОшибки) Тогда
					Возврат;
					КонецЕсли;

					Если ТипЗнч(Ответ) <> Тип("Структура")
					Или Не Ответ.Свойство("access_token")
					Или Не Ответ.Свойство("expires_in")
					Или Не Ответ.Свойство("token_type")
					Или Ответ.token_type <> "Bearer" Тогда
					ОписаниеОшибки = НСтр("ru='Неожиданный формат ответа при попытке обновления токена'", "ru");
					Возврат;
					КонецЕсли;

					ДанныеАвторизации.access_token = Ответ.access_token;
					ДанныеАвторизации.refresh_token = ?(Ответ.Свойство("refresh_token"), Ответ.refresh_token, ДанныеАвторизации.refresh_token);;
					ДанныеАвторизации.expiration_time = Формат(ТекущаяДатаСеанса() + Число(Ответ.expires_in), "ДФ=ггггММддЧЧммсс");     

					Исключение
					ОписаниеОшибки = КраткоеПредставлениеОшибки(ИнформацияОбОшибке());  
					КонецПопытки;

					КонецПроцедуры
				

В ответе будут значения access_token и refresh_token.

{
					"access_token": "ya29.A0AfH6SMDezfJalWo01n0M43ACdWTRWzypJQ9QBdVck3wQgt0isV4RDFSjzsRl8kU2hsaudSCv-z6HMrvWOrly-iUJwoWJ43RAy2foJji-bpxhdBxj3sHQn_seZVOnoCmnJqdT51dI-OgBVarkOV3WPFQqckEJ",
					"expires_in": 3599,
					"refresh_token": "1//0cBALjAQGrIhfCgYIARAAGAwSNwF-L9IrZa1oRP-RJo_TYwZDggKXasagQdwuenpdsBw3s7AdC-3zsGPawM_LmAwtDjHyutqRtxU",
					"scope": "https://www.googleapis.com/auth/bigquery",
					"token_type": "Bearer"
				}
			

Токен доступа access_token действует в течение часа, поэтому его нужно обновлять при помощи токена обновления refresh_token.

В ответе будет обновленное значение access_token.

{
				"access_token": "ya29.a0AfH6SMCxmki3_wxEMS-Xwtx9Y9AbQYIuoZAGbozAQvu3iG1V1hCKITCsuBNno5ZOAezPU1n0fgiljsOgLk_qwLnnhNku5Ej06h2kvpst1S2TXs9D7BAXGg9o3rDkdvdKSCLYW6RJPi-A9r1WqxbeCJ3li47VJt0Pkgcywpp4lQY",
				"expires_in": 3599,
				"scope": "https://www.googleapis.com/auth/bigquery",
				"token_type": "Bearer"
			}
		

Access_token передается в заголовках запросов при работе с сервисами через API.

Алгоритм авторизации в приложении может быть исполнен следующим образом.

С помощью REST клиента первый раз вручную получаются значения access_token и refresh_token. Значение refresh_token следует хранить в приложении.

При первом запросе из приложения на обновление access_token сохраняется новый токен и время истечения срока действия (можно вычислить как текущее время + значение expires_in).

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

Интеграция с BigQuery API

После того, как проект полностью настроен, можно приступать к непосредственной работе с BigQuery. Сначала добавляется датасет внутри проекта. Датасет - это набор таблиц базы данных.

В датасете необходимо создать таблицы и поля. Перечисленные действия можно также выполнить через API.

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

На этом этапе подготовка базы данных завершена, можно приступать к загрузке данных. Запись в таблицу через API может выполняться двумя способами - синхронным и асинхронным.

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

https://cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/insertAll

Асинхронный метод позволяет отправить текст SQL запроса на добавление неограниченного количества записей.

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/insert

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

Текст SQL запроса имеет ограничение в 1 000 000 символов. Чем больше запрос, тем дольше выполняется задание на добавление записей. Если требуется загружать большое количество записей за раз, то может возникнуть ситуация, когда новые задания добавляются, а старые не успевают завершиться из-за новых заданий. В этом случае произойдет переполнение очереди заданий.

Чтобы такого не произошло, нужно подбирать оптимальный размер добавляемых данных. Мы, например, реализовали порционную загрузку. Большой набор записей делился на части примерно по 900 записей в запросе. Это позволило уменьшить размер запроса, что ускорило время выполнения заданий.

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

INSERT INTO base.goods (id, name, category_id, category_name, price, quantity)
			VALUES ('0001', 'Chais', '0010', 'Products', 500, 50),
			('0002', 'Chang', '0010', 'Products', 1000, 100),
			('0003', 'Aniseed Syrup', '0010', 'Products', 3500, 30),
			('0004', 'Konbu', '0010', 'Products', 40, 75),
			('0005', 'Mishi Kobe Niku', '0011', 'Seafood', 750, 115),
			('0006', 'Ikura', '0011', 'Seafood', 120, 85),
			('0007', 'Tofu', '0012', 'Beverages', 340, 40),
			('0008', 'Inlagd Sill', '0012', 'Beverages', 20, 140),
			('0009', 'Gravad lax', '0012', 'Beverages', 2200, 180),
			('0010', 'Spegesild', '0012', 'Beverages', 1250, 10)

Пример JSON тела запроса для ассинхронного метода

{
				"configuration": {
				"jobType": "QUERY",
				"query":{
				"query": "INSERT INTO base.goods (id, name, category_id, category_name, price, quantity) VALUES ('0001', 'Chais', '0010', 'Products', 500, 50),('0002', 'Chang', '0010', 'Products', 1000, 100),('0003', 'Aniseed Syrup', '0010', 'Products', 3500, 30)",
				"useLegacySql": false
				}
			}
		}
	

Создание отчета в DataStudio

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

https://datastudio.google.com/u/1/

Сначала нужно добавить источник данных на вкладке Data sources. Источником для отчета будет созданная таблица в BigQuery.

Далее на вкладке Reports нужно создать пустой отчет и выбрать таблицу как источник данных.

После выбора в редакторе отчета появится таблица с несколькими полями. Добавление полей, управление видом, агрегатные и прочие функции настраиваются в правой панели свойств.

В рамках одного отчета можно создать несколько страниц, на каждой из которых можно разместить несколько независимых элементов с данными.

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

Отчет работает по принципу Google документов. Можно делиться отчетом по ссылке, добавлять права на просмотр и редактирование.

Вот так может выглядеть итоговый вариант отчета.

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

Комментарии для сайта Cackle