09.06.2020 22:59
CoolJuice 12 828 Источник

Клиенты не любят платить больше, чем планировалось — подробное обоснование расходов неотъемлемая и важная часть внедрения облачных технологий.

Google Cloud Platform предоставляет различные тарифные планы для используемых ресурсов. Например, стоимость GCE зависит от конфигурации компьютера (CPU, память, сетевые модули, жесткие диски). Расходы на Google Kubernetes Engine (GKE) и Google Cloud Dataproc основываются на всех узлах, которые работают в Google Compute Engine (GCE). Остальные затраты могут вычисляться по сложной и замысловатой формуле. Планировать бюджет становится всё сложнее, особенно если вы пользуетесь несколькими облачными технологиями. Мониторинг и своевременное информирование становятся тем ценнее по мере увеличения трат на инфраструктуру.

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

Предпосылки


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

Распространенные фильтры:

  • Тип ресурса;
  • Временной период;
  • Отдел и команда;
  • Сравнение с предыдущими периодами.

Первое решение, которое основывается на рекомендации Google — использовать Google Data Studio. Реализация очень проста, — требуется только настройка источника данных. Примеры отчетов и панель мониторинга предустановлены, — но само решение недостаточно гибкое. При создании диаграмм и графиков в Google Data Studio, нельзя ввести формулу, приходится выбирать все параметры вручную.

Grafana — простой и понятный инструмент мониторинга и анализа данных. Он хорошо зарекомендовал себя. Такой дашборд отлично подошел бы для визуализации данных о платежах. Остаётся открытым вопрос о том, как подключить веб-интерфейс к BigQuery (BQ). Плагин BQ, с открытым исходным кодом, чрезмерно забагован. В итоге, связка BQ-Grafana получается недостаточно стабильной. Кроме того, есть еще один неудобный момент — запросы BQ возвращают данные слишком долго.

Хорошим решением оказалась загрузка данных в PostgreSQL через CloudSQL, эта СУБД имеет официальный плагин PostgreSQL для Grafana. А последующие результаты тестирования показали, что выбранный способ имеет явные преимущества по скорости работы.

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

Обзор решения


Рабочий процесс может быть схематически описан следующим образом. Инструмент отложенных заданий в Kubernetes кластере, каждые 4 часа запускает задачу в Cloud Dataflow. Эта задача, в свою очередь, запускает процесс загрузки данных из BigQuery в PostgreSQL. Выгрузка происходит только тех данных, которые были найдены после последнего экспорта из BQ. После чего последние данные можно будет увидеть в Grafana, подключенной к PostgreSQL.



Настройка Базы Данных


В первую очередь необходимо настроить экспорт данных о платежах в BigQuery и создать базу данных PostgreSQL.

Скрытый текст
Подробнее о настройке BigQuery можно прочитать в документации. А базу данных можно создать при помощи Cloud SQL.

После подготовительного этапа создаём несколько объектов баз данных, включая таблицу с имитацией структуры BQ:

Скрытый текст 
CREATE DATABASE billing;
USE billing;
 
CREATE TABLE public.billing_export_2 (
	id serial NOT NULL,
	sku_id varchar NULL,
	labels varchar NULL,
	export_time varchar NULL,
	currency varchar NULL,
	sku_description varchar NULL,
	location_zone varchar NULL,
    currency_conversion_rate float8 NULL,
	project_labels varchar NULL,
	location_country varchar NULL,
	usage_start_time varchar NULL,
	billing_account_id varchar NULL,
	location_region varchar NULL,
	usage_pricing_unit varchar NULL,
	usage_amount_in_pricing_units float8 NULL,
	cost_type varchar NULL,
	project_id varchar NULL,
	system_labels varchar NULL,
	project_description varchar NULL,
	location_location varchar NULL,
	project_ancestry_numbers varchar NULL,
	credits varchar NULL,
	service_description varchar NULL,
	usage_amount float8 NULL,
	invoice_month varchar NULL,
	usage_unit varchar NULL,
	usage_end_time varchar NULL,
	"cost" float8 NULL,
	service_id varchar NULL,
	CONSTRAINT billing_export_2_pkey PRIMARY KEY (id)
);

Также создаём материализованное представление со значениями, которые будут использоваться для создания подключения PostgreSQL через Grafana:


CREATE MATERIALIZED VIEW vw_billing_export AS
	SELECT
    	id,
        sku_id,
        labels,
        export_time::timestamp,
        currency,
        sku_description,
        location_zone,
        currency_conversion_rate,
        project_labels,
        location_country,
        usage_start_time::timestamp,
        billing_account_id,
        location_region,
        usage_pricing_unit,
        usage_amount_in_pricing_units,
        cost_type, project_id,
        system_labels,
        project_description,
        location_location,
        project_ancestry_numbers,
        credits,
        service_description,
        usage_amount,
        invoice_month,
        usage_unit,
        usage_end_time::timestamp,
        "cost",
        service_id,
    	l_label1 ->> 'value' as label1,
    	l_label2 ->> 'value' as label2,
   	...
    	FROM billing_export_2
        	LEFT  JOIN jsonb_array_elements(labels::jsonb) AS l_label1
on l_label1 ->> 'key' = ‘label1’
        	LEFT  JOIN jsonb_array_elements(labels::jsonb) AS l_label2
on l_label2 ->> 'key' = ‘label2’
        	...


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

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

CREATE INDEX vw_billing_export_label1
ON vw_billing_export (label1);


DataFlow


Создаём учётную запись с доступом к DataFlow и BigQuery. Это нужно для того, чтобы задачи DataFlow могли получать данные от BigQuery.

export  project=myproject
gcloud iam service-accounts create "bq-to-sql-dataflow" --project ${project}
 
gcloud projects add-iam-policy-binding ${project} --member serviceAccount:"bq-to-sql-dataflow@${project}.iam.gserviceaccount.com" --role roles/dataflow.admin
 
gcloud projects add-iam-policy-binding ${project} --member serviceAccount:"bq-to-sql-dataflow@${project}.iam.gserviceaccount.com" --role roles/bigquery.dataViewer

Для задач DataFlow необходимо создать два контейнера: один для временных, второй для выходных данных.

gsutil mb gs://some-bucket-staging
gsutil mb gs://some-bucket-temp

Скрипт для загрузки данных


Скрипт потребуется для загрузки данных из BigQuery в CloudSQL. Облачный DataFlow поддерживает разработку на Python, можно воспользоваться им. Также потребуется библиотека Apache Beam, json-файл (установленный в переменную среды GOOGLE_APPLICATION_CREDENTIALS с предварительно настроенными полномочиями учетной записи) и файл requirements.txt, который содержит список пакетов для инсталляции (в нашем случае нужен только один beam-nuggets пакет). Скрипт на Python представлен ниже:

Скрытый текст
Основная часть скрипта bq-to-sql выглядит следующим образом:

args = parser.parse_args()
project = args.project
job_name = args.job_name + str(uuid.uuid4())
bigquery_source = args.bigquery_source
postgresql_user = args.postgresql_user
postgresql_password = args.postgresql_password
postgresql_host = args.postgresql_host
postgresql_port = args.postgresql_port
postgresql_db = args.postgresql_db
postgresql_table = args.postgresql_table
staging_location = args.staging_location
temp_location = args.temp_location
subnetwork = args.subnetwork
 
options = PipelineOptions(
        	flags=["--requirements_file", "/opt/python/requirements.txt"])
# For Cloud execution, set the Cloud Platform project, job_name,
# staging location, temp_location and specify DataflowRunner.
 
google_cloud_options = options.view_as(GoogleCloudOptions)
google_cloud_options.project = project
google_cloud_options.job_name = job_name
google_cloud_options.staging_location = staging_location
google_cloud_options.temp_location = temp_location
google_cloud_options.region = "us-west1"
worker_options = options.view_as(WorkerOptions)
worker_options.zone = "us-west1-a"
worker_options.subnetwork = subnetwork
worker_options.max_num_workers = 20
 
options.view_as(StandardOptions).runner = 'DataflowRunner'
 
start_date = define_start_date()
with beam.Pipeline(options=options) as p:
	rows = p | 'QueryTableStdSQL' >> beam.io.Read(beam.io.BigQuerySource(
                    	query='SELECT                         	billing_account_id,                         	service.id as service_id,                         	service.description as service_description,                         	sku.id as sku_id,                         	sku.description as sku_description,                         	usage_start_time,                         	usage_end_time,                         	project.id as project_id,                         	project.name as project_description,                         	TO_JSON_STRING(project.labels)                             	as project_labels,                         	project.ancestry_numbers                             	as project_ancestry_numbers,                         	TO_JSON_STRING(labels) as labels,                         	TO_JSON_STRING(system_labels) as system_labels,                         	location.location as location_location,                         	location.country as location_country,                         	location.region as location_region,                         	location.zone as location_zone,                         	export_time,                         	cost,                         	currency,                         	currency_conversion_rate,                         	usage.amount as usage_amount,                         	usage.unit as usage_unit,                         	usage.amount_in_pricing_units as                          	usage_amount_in_pricing_units,                         	usage.pricing_unit as usage_pricing_unit,                         	TO_JSON_STRING(credits) as credits,                         	invoice.month as invoice_month,                    	     cost_type FROM `' + project + '.' + bigquery_source + '`                         	WHERE export_time >= "' + start_date + '"',
                    	use_standard_sql=True))
	source_config = relational_db.SourceConfiguration(
          	              drivername='postgresql+pg8000',
                        	host=postgresql_host,
                        	port=postgresql_port,
                        	username=postgresql_user,
                        	password=postgresql_password,
                        	database=postgresql_db,
                        	create_if_missing=True,
                        	)
	table_config = relational_db.TableConfiguration(
                        	name=postgresql_table,
            	            create_if_missing=True
                        	)
	rows | 'Writing to DB' >> relational_db.Write(
    	source_config=source_config,
    	table_config=table_config
	)


P.S: Поддержка Python, как верно меня скорректировали в комментариях, в скором времени будет прекращена.
On October 7, 2020, Dataflow will stop supporting pipelines using Python 2.
Для долгосрочной работы стоит задуматься над портированием этой части.

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

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

Создание файла JSON с разрешениями SA


Учетная запись, созданная ранее для рабочего процесса Cloud Dataflow, также используется в задачах Cron. Нужно задать команду, которая создаст личный ключ-пароль для учетной записи, в дальнейшем загруженный в кластер Kubernetes, где он будет выступать как скрытый пароль для доступа в Cron задачи.

gcloud iam service-accounts keys create ./cloud-sa.json --iam-account "bq-to-sql-dataflow@${project}.iam.gserviceaccount.com" --project ${project}

Развертывание секретного пароля в кластере K8s:

kubectl create secret generic bq-to-sql-creds --from-file=./cloud-sa.json

Создание Docker образа


Так как основной целью является ежедневное автоматическое выполнение задач DataFlow, создаём Docker образ со всеми нужными переменными среды и скриптом на Python:

Скрытый текст
Dockerfile:

FROM python:latest
RUN   bin/bash -c "   apt-get update &&   apt-get install python2.7-dev -y &&   pip install virtualenv &&   virtualenv -p /usr/bin/python2.7 --distribute temp-python &&   source temp-python/bin/activate &&   pip2 install --upgrade setuptools &&   pip2 install pip==9.0.3 &&   pip2 install requests &&   pip2 install Cython &&   pip2 install apache_beam &&   pip2 install apache_beam[gcp] &&   pip2 install beam-nuggets &&   pip2 install psycopg2-binary &&   pip2 install uuid"

COPY ./bq-to-sql.py /opt/python/bq-to-sql.py
COPY ./requirements.txt /opt/python/requirements.txt
COPY ./main.sh /opt/python/main.sh

FROM python:latest
RUN   bin/bash -c "   apt-get update &&   apt-get install python2.7-dev -y &&   pip install virtualenv &&   virtualenv -p /usr/bin/python2.7 --distribute temp-python &&   source temp-python/bin/activate &&   pip2 install --upgrade setuptools &&   pip2 install pip==9.0.3 &&   pip2 install requests &&   pip2 install Cython &&   pip2 install apache_beam &&   pip2 install apache_beam[gcp] &&   pip2 install beam-nuggets &&   pip2 install psycopg2-binary &&   pip2 install uuid"

COPY ./bq-to-sql.py /opt/python/bq-to-sql.py
COPY ./requirements.txt /opt/python/requirements.txt
COPY ./main.sh /opt/python/main.sh

image: 
imageTag: latest
imagePullPolicy: IfNotPresent
project: 
job_name: "bq-to-sql"
bigquery_source: "[dataset].[table]”
postgresql:
  user: 
  password: 
  host: 
  port: "5432"
  db: "billing"
  table: "billing_export"
staging_location: "gs://my-bucket-stg"
temp_location: "gs://my-bucket-tmp"  
subnetwork: "regions/us-west1/subnetworks/default"


Для того чтобы облегчить процесс развертывания и повторного использования Cron задач, используем установщик пакетов Kubernetes, Helm:

Скрытый текст
cronjob.yaml:

apiVersion: batch/v1beta1
kind: CronJob
metadata:
  name: {{ template "bq-to-sql.fullname" . }}
spec:
  schedule: "0 0 * * *"
  jobTemplate:
	spec:
  	template:
    	spec:
    	  restartPolicy: OnFailure
      	containers:
      	- name: {{ template "bq-to-sql.name" . }}
        	image: "{{ .Values.image }}:{{ .Values.imageTag }}"
        	imagePullPolicy: "{{ .Values.imagePullPolicy }}"
        	command: [ "/bin/bash", "-c", "bash /opt/python/main.sh             	{{ .Values.project }}             	{{ .Values.job_name }}             	{{ .Values.bigquery_source }}             	{{ .Values.postgresql.user }}             	{{ .Values.postgresql.password }}             	{{ .Values.postgresql.host }}             	{{ .Values.postgresql.port }}             	{{ .Values.postgresql.db }}             	{{ .Values.postgresql.table }}             	{{ .Values.staging_location }}             	{{ .Values.temp_location }}                 {{ .Values.subnetwork }}"]
        	volumeMounts:
        	- name: creds
          	mountPath: /root/.config/gcloud
          	readOnly: true
        	env:
        	- name: GOOGLE_APPLICATION_CREDENTIALS
          	value: /root/.config/gcloud/creds.json
      	volumes:
        	- name: creds
          	secret:
            	secretName: bq-to-sql-creds


Визуализация данных с помощью Grafana


Последним шагом будет создание долгожданных дашбордов в Grafana. На этом этапе нет каких-то ограничений, можно использовать любой понравившийся стиль для отображения. Как пример, это может быть Data Studio Billing Report Demo.

Скрытый текст
Конечно, все SQL запросы придётся написать с нуля :)

Из важного на что хотелось бы обратить еще внимание. Из соображений безопасности лучше назначать пользователям разные права:

  • В режиме чтения (для просмотра данных счетов);
  • Для соединения с Grafana.

Заключение


Описанное руководство даёт возможность взглянуть на рабочий процесс целью которого является визуализация данных счетов. Этот процесс поддерживает добавление новых фильтров и метрик. В итоге клиент получает набор полезных и быстрых дашбардов, которые помогают контролировать и оптимизировать в дальнейшем затраты на Google Cloud.