Visualizing data in DataLens using Cloud Functions
- Step 1. Create a database
- Step 2. Create tables
- Step 3. Create a connection to the database
- Step 4. Create a service account
- Step 5. Create a service account key
- Step 6. Create a cloud function
- Step 7. Set up integration
- Step 8. Connect DataLens to the database
- Step 9. Create a chart
- Step 10. Create a dashboard
- Step 11. Set up the chart
- Step 12. Set up display of colors in the chart
- Step 13. Place the new chart on the dashboard
- Step 14. Set up dashboard auto-update
You can deliver form responses to a PostgreSQL database created in Yandex Cloud and visualize them in Yandex DataLens.
With this guide, you will learn how to visualize:
- Number of form responses grouped by date.
- Statistics on responses to prompts of the Short text and One option type, grouped by date.
Step 1. Create a database
- Go to the Yandex Cloud management console
and select the folder where you want to create a database. - In the left-hand panel, click
and select Yandex Managed Service for PostgreSQL. - In the top-right corner, click Create cluster.
- Configure the cluster:
- Under Basic parameters, fill in the Cluster name field. The name may contain uppercase and lowercase Latin letters, numbers, underscores, and hyphens.
- Under Database, fill in the DB name and Username fields. The names may contain uppercase and lowercase Latin letters, numbers, underscores, and hyphens.
- Under Database, fill in the Password field.
- Under Database, set the Locale for sorting (LC_COLLATE) and Charset locale (LC_CTYPE) fields to en_US.UTF8. Once you create a database, you cannot change these parameters.
- Under Additional settings, enable Access from the management console, Serverless access, and DataLens access.
- Configure other parameters if needed. For more information, see Creating an PostgreSQL cluster.
- Click Create cluster.
- Wait until the new cluster's Availability field changes to Alive.
Step 2. Create tables
- Go to the page of the created cluster.
- In the left-hand panel, click SQL.
- Select the appropriate username and database, enter the password, and click Connect.
- Select the public schema.
- Run the following SQL queries to create the following tables:
- Table to store all form responses:
create table answers( id serial primary key, answer jsonb, created timestamp with time zone default now() );
- Table to count the number of form responses:
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) );
- Table to store responses to prompts of the Short text and One option type:
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) );
- Table to store all form responses:
Step 3. Create a connection to the database
- In the management console
, return to the folder with the new cluster. - In the left-hand panel, click
and select Cloud Functions. - In the left-hand panel, click
. - In the top-right corner, click Create connection.
- Set up a connection:
- Fill in the Name field. The name may only contain lowercase Latin letters, numbers, and hyphens.
- In the Type field, select PostgreSQL.
- Fill in the Cluster, Database, User, and Password fields. Enter the same field values as you set when creating your cluster in step 1.
- Click Create.
- Go to the connection page and copy the Entry point field value.
Step 4. Create a service account
- In the management console
, return to the folder with the new cluster. - In the top-right corner, click
→ Create service account. - In the service account creation window, fill in the following fields:
- Name; it may only contain lowercase Latin letters, numbers, and hyphens.
- Description; it may contain any characters.
- In the Roles in folder field, add the following roles:
serverless.functions.invoker
serverless.mdbProxies.user
- Click Create.
Step 5. Create a service account key
- In the management console
, return to the folder with the new cluster. - Go to the Service accounts tab.
- Select the account you need.
- In the top panel on the account page, click Create new key → Create API key.
- Provide a brief description for the key.
- Click Create.
- This will open a window with the key ID and the secret key. Store them in a secure place. You will not be able to access them after you close the window.
Step 6. Create a cloud function
-
In the management console
, return to the folder with the new cluster. -
In the left-hand panel, click
and select Cloud Functions. -
In the top-right corner, click Create function.
-
On the function creation page, fill in the following fields:
- Name; it may only contain lowercase Latin letters, numbers, and hyphens.
- Description; it may contain any characters.
-
Select the Python programming language.
-
Create a file named
requirements.txt
and add the following line to it:psycopg2
-
Create or edit a file named
index.py
:import json import psycopg2 def get_connection(context): return psycopg2.connect( database="<connection_ID>", user="<username>", password=context.token["access_token"], host="<entry_point>", 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', } }
Substitute the following values in this function:
<connection_ID>
: Value of the ID field of the database connection you created in step 3. You can copy it on the connection page.<username>
: Database user name that you specified in the cluster settings in step 1. You can find it in the Users tab on the cluster page.<entry_point>
: Value of the Entry point field of the database connection you created in step 3. You can copy it on the connection page.
-
Click Save changes.
-
On the function page, copy the value from the ID field.
Step 7. Set up integration
- Go to the form whose responses you want to deliver to the database and select the Integrations tab.
- Select a group of actions to set up issue creation in and click Cloud Functions at the bottom of the group.
- In the Function code field, paste the function ID that you copied in the previous step.
- Under Parameters, you can optionally select additional parameters to be transferred to the function.
- Click Save.
Step 8. Connect DataLens to the database
- In the management console
, return to the folder with the new cluster. - In the left-hand panel, click
and select DataLens. - Click Create connection → PostgreSQL.
- Set up a connection:
- In the Cloud and folder field, select the folder where you created the cluster.
- In the Cluster field, select the cluster that you created in step 1.
- In the Host name field, select a host.
- In the Path to database field, select the database where you created tables in step 2.
- In the User field, select the user that you created along with the cluster in step 1.
- In the Password field, enter the password that you created in step 1.
- Click Create connection.
Step 9. Create a chart
- In the top-right corner, click Create dataset.
- Drag the
public.answercount
table to the area in the middle of the page. - In the top-right corner, click Create chart.
- Drag the table column names from the Dimensions section to the Bar chart panel as follows:
modified
to the X fieldcount
to the Y fieldsurvey_id
to the Filters field
- Click Save.
Step 10. Create a dashboard
- Go back to the DataLens page.
- Click Create dashboard.
- Select the folder where you want to create your dashboard, name it, and click Create.
- In the top panel, click Add → Chart.
- In the add chart window:
- Fill in the Name field.
- In the Chart field, select the chart that you created in step 9.
- Click Add.
- In the top-right corner, click Save.
You will get a bar chart with the number of form responses by date.
Step 11. Set up the chart
- Go to the page of the cluster that you created in step 1.
- In the left-hand panel, click SQL.
- Select the appropriate username and database, enter the password, and click Connect.
- Select the public schema.
- Run the following SQL query:
- Create a table that will store the information about chart colors:
create table labels( id serial primary key, compound_key text not null, label text default '', unique(compound_key) );
- Add information about labels for prompt responses to this table:
insert into labels(compound_key, label) values('id-radio.62019338', 'Option 1'), ('id-radio.62019364', 'Option 2'), ('id-radio.62019365', 'Option 3');
- Create a view with a link between prompt IDs and response option IDs:
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;
- Create a table that will store the information about chart colors:
Step 12. Set up display of colors in the chart
- Open the DataLens page and go to the connection that you created in step 8.
- In the top-right corner, click Create dataset.
- Drag the
public.questioncount_vw
table to the middle of the page. - Drag the
public.labels
table to the middle of the page. - Click between the table names in the middle of the page.
- In the window that opens, click once again, select the left value by the
compound key
field, and click Save. - Go to the Fields tab.
- In the top panel, click Add field.
- Fill in the fields of the field creation window:
- In Field name, enter
question_label
. - In the main part of the field, enter:
if [label] is not null then [label] else [compound key (1)] end
- Click Save.
- In Field name, enter
- In the top panel, click Create chart.
Drag the names from the Dimensions section to the Bar chart panel as follows:modified
to the X fieldcount
to the Y fieldquestion_label
to the Colors fieldsurvey_id
andquestion_key
to the Filters field
- Click Save.
Step 13. Place the new chart on the dashboard
- Go to the dashboard that you created in step 10.
- In the top panel, click Add → Chart.
- In the add chart window:
- Fill in the Name field.
- In the Chart field, select the chart that you created in step 12.
- Click Add.
- In the top-right corner, click Save.
Now, your dashboard contains two similar bar charts, single- and multicolor, whose colors depend on user responses.
Step 14. Set up dashboard auto-update
- In the top panel, click
. - In the window that opens, select Auto-update and set the interval between dashboard updates.
- Click Save.