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

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

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

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

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

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

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

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

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

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

      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)
      );
      

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

      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. Создать подключение к базе данныхШаг 3. Создать подключение к базе данных

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

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

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

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

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

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

1. В консоли перейдите обратно в каталог, в котором находится созданный кластер.

2. На панели слева нажмите и выберите сервис Cloud Functions.

3. В правом верхнем углу нажмите Создать функцию.

4. На странице создания функции заполните поля:

   1. Имя может содержать только строчные буквы латинского алфавита, цифры и дефисы.
   2. Описание может содержать любые символы.

5. Выберите язык программирования Python.

6. Создайте файл requirements.txt и напишите в нем строку:

   psycopg2
   

7. Создайте или отредактируйте файл 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. Скопировать его можно на странице подключения.

8. Нажмите Сохранить изменения.

9. На странице функции скопируйте значение ее поля Идентификатор.

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

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

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

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

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

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

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

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

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

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

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

      create table labels(
      	id serial primary key,
      	compound_key text not null,
      	label text default '',
      	unique(compound_key)
      );
      

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

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

   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. Настроить отображение цветов на диаграммеШаг 12. Настроить отображение цветов на диаграмме

1. Перейдите на страницу DataLens и перейдите в подключение, которое создали в шаге 8.
2. В правом верхнем углу нажмите Создать датасет.
3. Перетащите таблицу public.questioncount_vw на середину страницы.
4. Перетащите таблицу public.labels на середину страницы.
5. Между названиями таблиц посередине страницы нажмите .
6. В открывшемся окне нажмите еще раз, выберите значение left по полю compound key и нажмите Сохранить.
7. Перейдите на вкладку Поля.
8. На панели сверху нажмите Добавить поле.
9. Заполните окно создания поля:
   1. В поле Название поля введите question_label.
   2. В основной части поля введите:

      if [label] is not null then [label] else [compound key (1)] end
      

   3. Нажмите Сохранить.
10. На панели сверху нажмите Создать чарт.
    Перетащите названия из раздела Измерения на панель Столбчатая диаграмма следующим образом:
    • modified — в поле X.
    • count — в поле Y.
    • question_label — в поле Цвета.
    • survey_id и question_key — в поле Фильтры.
11. Нажмите Сохранить.

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

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

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

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

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