Визуализировать данные в DataLens с помощью Cloud Functions

Вы можете передавать ответы на форму в базу данных PostgreSQL, созданную в Yandex Cloud, и визуализировать их в сервисе DataLens.

С помощью этой инструкции вы сможете визуализировать:

Количество ответов на форму, распределенное по датам.
Статистику ответов на вопросы с типом Короткий текст и Один вариант, распределенное по датам.

Шаг 1. Создать базу данных

Перейдите в консоль Yandex Cloud и выберите каталог, в котором хотите создать базу данных.
На панели слева нажмите и выберите сервис Managed Service for PostgreSQL.
В правом верхнем углу нажмите Создать кластер.
Настройте кластер:
1. В разделе Базовые параметры заполните поле Имя кластера. Оно может содержать строчные и прописные буквы латинского алфавита, цифры, нижние подчеркивания и дефисы.
2. В разделе База данных заполните поля Имя БД и Имя пользвателя. Они могут содержать строчные и прописные буквы латинского алфавита, цифры, нижние подчеркивания и дефисы.
3. В разделе База данных заполните поле Пароль.
4. В разделе База данных в полях Локаль сортировки (LC_COLLATE) и Локаль набора символов (LC_CTYPE) установите значение en_US.UTF8. После создания базы данных изменить эти параметры не получится.
5. В разделе Дополнительные настройки включите опции Доступ из консоли управления, Доступ из Serverless и Доступ из DataLens.
6. При желании настройте другие параметры. Подробнее в разделе {#T}.
Нажмите Создать кластер.
Дождитесь, когда в у нового кластера поле Доступность примет значение Alive.

Шаг 2. Создать таблицы

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

Выполните SQL-запросы, чтобы создать таблицы:

Таблица для хранения всех ответов на форму:

create table answers(
	id serial primary key,
	answer jsonb,
	created timestamp with time zone default now()
);

Таблица для подсчета количества ответов на форму:

create table answercount(
	id serial primary key,
	survey_id text not null,
	modified date not null default current_date,
	count int default 0,
	unique(survey_id, modified)
);

Таблица для хранения ответов на вопросы с типом Короткий текст и Один вариант.

create table questioncount(
	id serial primary key,
	survey_id text not null,
	question_key text not null,
	option_key text not null default '',
	modified date not null default current_date,
	count int default 0,
	unique(survey_id, question_key, option_key, modified)
);

Шаг 3. Создать подключение к базе данных

В консоли перейдите обратно в каталог, в котором находится созданный кластер.
На панели слева нажмите и выберите сервис Cloud Functions.
На панели слева нажмите .
В правом верхнем углу нажмите Создать подключение.
Настройте подключение:
1. Заполните поле Имя. Оно может содержать только строчные буквы латинского алфавита, цифры и дефисы.
2. В поле Тип выберите PostgreSQL.
3. Запоните поля Кластер, База данных, Пользователь и Пароль. Введите в них те значения, которые устанавливали при создании кластера в шаге 1.
Нажмите Создать.
Перейдите на страницу подключения и скопируйте значение поля Точка входа.

Шаг 4. Создать сервисный аккаунт

В консоли перейдите обратно в каталог, в котором находится созданный кластер.
В правом верхнем углу нажмите → Создать сервисный аккаунт.
В окне создания сервисного аккаунта заполните поля:
1. Имя может содержать только строчные буквы латинского алфавита, цифры и дефисы.
2. Описание может содержать любые символы.
3. В поле Роли в каталоге добавьте следующие роли:
  - serverless.functions.invoker
  - serverless.mdbProxies.user
Нажмите Создать.

Шаг 5. Создать ключ сервисного аккаунта

В консоли перейдите обратно в каталог, в котором находится созданный кластер.
Перейдите на вкладку Сервисные аккаунты.
Выберите нужный аккаунт.
На странице аккаунта на верхней панели нажмите Создать новый ключ → Создать API-ключ.
Напишите краткое описание ключа.
Нажмите Создать.
Откроется окно с идентификатором ключа и секретным ключом. Сохраните их в безопасном месте. После закрытия окна доступ к ним получить нельзя.

Шаг 6. Создать облачную функцию

В консоли перейдите обратно в каталог, в котором находится созданный кластер.
На панели слева нажмите и выберите сервис Cloud Functions.
В правом верхнем углу нажмите Создать функцию.
На странице создания функции заполните поля:
1. Имя может содержать только строчные буквы латинского алфавита, цифры и дефисы.
2. Описание может содержать любые символы.
Выберите язык программирования Python.
Создайте файл requirements.txt и напишите в нем строку:
```
psycopg2
```

Создайте или отредактируйте файл index.py:

import json
import psycopg2

def get_connection(context):
	return psycopg2.connect(
		database="<идентификатор_подключения>",
		user="<имя_пользователя>",
		password=context.token["access_token"],
		host="<точка_входа>",
		port=6432,
		sslmode="require",
	)

def run_function(connection, answer, **params):
	survey_id = answer['survey']['id']
	args = (survey_id, )

	with connection.cursor() as c:
		sql = '''
			insert into answercount as t (survey_id, count) 
			values(%s, 1)
			on conflict (survey_id, modified) 
			do update set count = t.count + excluded.count
		'''
		c.execute(sql, args)

	args, args_size = [], 0
	for question_key, question_data in answer['data'].items():
		match question_data['question']['answer_type']['slug']:
			case 'answer_choices':
				for choice_item in question_data['value']:
					args.extend([survey_id, question_key, choice_item['key']])
					args_size += 1
			case 'answer_short_text':
				args.extend([survey_id, question_key, ''])
				args_size += 1

	with connection.cursor() as c:
		values = ','.join(['(%s, %s, %s, 1)'] * args_size)
		sql = '''
			insert into questioncount as t (survey_id, question_key, option_key, count) 
			values{values}
			on conflict (survey_id, question_key, option_key, modified) 
			do update set count = t.count + excluded.count
		'''.format(values=values)
		c.execute(sql, args)
		
	connection.commit()

def handler(event, context):
	body = json.loads(event.get('body'))
	params = {
		name: value
		for name, value in body.items()
		if name != 'answer'
	}
	connection = get_connection(context)
	result = {
		'id': run_function(connection, body.get('answer'), **params),
	}

	return {
		'statusCode': 200,
		'body': result,
		'headers': {
			'Content-Type': 'application/json',
		}
	}

В этой функции подставьте значения:

<идентификатор_подключения> — значение поля Идентификатор подключения к базе данных, которое вы создавали в шаге 3. Скопировать его можно на странице подключения.
<имя_пользователя> — имя пользователя базы данных, которое вы вводили при настройке кластера в шаге 1. Найти его можно на странице кластера во вкладке Пользователи.
<точка_входа> — значение поля Точка входа в подключении к базе данных, которое вы создавали в шаге 3. Скопировать его можно на странице подключения.

Нажмите Сохранить изменения.
На странице функции скопируйте значение ее поля Идентификатор.

Шаг 7. Настроить интеграцию

Перейдите в форму, ответы на которую хотите передавать в базу данных, и выберите вкладку Интеграции.
Выберите группу действий, в которой хотите настроить создание задачи, и внизу группы нажмите кнопку Cloud Functions.
В поле Код функции вставьте идентификатор функции, который скопировали в предыдущем шаге.
При желании в разделе Параметры выберите дополнительные параметры, которые хотите передать в функцию.
Нажмите Сохранить.

Шаг 8. Подключить DataLens к базе данных

В консоли перейдите обратно в каталог, в котором находится созданный кластер.
На панели слева нажмите и выберите сервис DataLens.
Нажмите Содать подключение → PostgreSQL.
Настройте подключение:
1. В поле Облако и каталог выберите каталог, в котором создали кластер.
2. В поле Кластер выберите кластер, который создали в шаге 1.
3. В поле Имя хоста выберите хост.
4. В поле Путь к базе данных выберите имя базы данных, в которой создавали таблицы в шаге 2.
5. В поле Имя пользователя выберите пользователя, которого создавали вместе с кластером в шаге 1.
6. В поле Пароль введите пароль, который создавали в шаге 1.
Нажмите Создать подключение.

Шаг 9. Создать чарт

В правом верхнем углу нажмите Создать датасет.
Перетащите таблицу public.answercount в область посередине страницы.
В правом верхнем углу нажмите Создать чарт.
Перетащите названия столбцов таблицы из раздела Измерения на панель Столбчатая диаграмма следующим образом:
- modified — в поле X.
- count — в поле Y.
- survey_id — в поле Фильтры.
Нажмите Сохранить.

Шаг 10. Создать дашборд

Перейдите обратно на страницу DataLens.
Нажмите Создать дашборд.
Выберите каталог, в котором будет создан дашборд, введите его название и нажмите Создать.
На панели сверху нажмите Добавить → Чарт.
В окне добавления чарта:
1. Заполните поле Название.
2. В поле Чарт выберите чарт, созданный в шаге 9.
3. Нажмите Добавить.
В правом верхнем углу нажмите Сохранить.

У вас готова столбчатая диаграмма с количеством ответов на форму в зависимости от даты.

Шаг 11. Настроить диаграмму

Перейдите на страницу кластера, который создавали в шаге 1.
На панели слева нажмите SQL.
Выберите подходящее имя пользователя и базу данных, введите пароль и нажмите Подключиться.
Выберите схему public.

Выполните следующий SQL-запрос:

Создайте таблицу, в которой будет храниться информация о цветах диаграммы:
```
create table labels(
	id serial primary key,
	compound_key text not null,
	label text default '',
	unique(compound_key)
);
```

Добавьте в эту таблицу информацию о лейблах для вариантов ответа:

insert into labels(compound_key, label)
values('id-radio.62019338', 'Option 1'), ('id-radio.62019364', 'Option 2'), ('id-radio.62019365', 'Option 3');

Создайте представление, в котором будут связаны идентификатор вопроса и идентификатор варианта ответа.

create view questioncount_vw as
select t.*, 
	case 
		when t.option_key = '' or t.option_key is null then t.question_key
		else t.question_key || '.' || t.option_key
	end as compound_key
from questioncount t;

Шаг 12. Настроить отображение цветов на диаграмме

Перейдите на страницу DataLens и перейдите в подключение, которое создали в шаге 8.
В правом верхнем углу нажмите Создать датасет.
Перетащите таблицу public.questioncount_vw на середину страницы.
Перетащите таблицу public.labels на середину страницы.
Между названиями таблиц посередине страницы нажмите .
В открывшемся окне нажмите еще раз, выберите значение left по полю compound key и нажмите Сохранить.
Перейдите на вкладку Поля.
На панели сверху нажмите Добавить поле.
Заполните окно создания поля:
1. В поле Название поля введите question_label.
2. В основной части поля введите:
```
if [label] is not null then [label] else [compound key (1)] end
```
3. Нажмите Сохранить.
На панели сверху нажмите Создать чарт.
Перетащите названия из раздела Измерения на панель Столбчатая диаграмма следующим образом:
- modified — в поле X.
- count — в поле Y.
- question_label — в поле Цвета.
- survey_id и question_key — в поле Фильтры.
Нажмите Сохранить.

Шаг 13. Разместить новый чарт на дашборде

Перейдите в дашборд, который создали в шаге 10.
На панели сверху нажмите Добавить → Чарт.
В окне добавления чарта:
1. Заполните поле Название.
2. В поле Чарт выберите чарт, созданный в шаге 12.
3. Нажмите Добавить.
В правом верхнем углу нажмите Сохранить.

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

Шаг 14. Установить автообновление дашборда

На панели сверху нажмите .
В открывшемся окне включите опцию Автообновление и задайте интервал времени между обновлениями дашборда.
Нажмите Сохранить.

Была ли статья полезна?

Передавать ответы в базу данных

История выполненных действий