Visualizing data from Yandex Object Storage on Yandex DataLens dashboards

Yandex DataLens is a data visualization and analysis service. Yandex Query and Yandex DataLens integration lets you visualize data stored in Yandex Object Storage. Yandex DataLens generates a YQL query, Yandex Query runs the query and returns its execution results, while Yandex DataLens visualizes these results.

Below is the architecture of the solution with Cloud Logging.

In the given example, we'll show the cost of New York City yellow taxi trips on the map, depending on the passenger drop-off area, with the driver tip amount highlighted. The data was previously uploaded to Yandex Object Storage and stored in a public bucket named yq-sample-data, in the nyc_taxi_csv folder.

Setup

Setup steps:

1. In Yandex Query, create a connection to data stored in Yandex Object Storage.
2. In Yandex Query, write a query that will generate a data structure.
3. Create a data source in Yandex DataLens.
4. In Yandex DataLens, create a dataset.
5. Create a chart in Yandex DataLens for data visualization.

Creating a data connection in Yandex Query

To get started, create a connection to Yandex Object Storage:

1. Go to the Yandex Query interface, the Tutorial section, select Batch mode, and click Create infrastructure for training.
2. In the Create connection window that opens, all the necessary field values are preset, so just click Create to create a connection.
3. In the Create binding window that opens, all the necessary field values are preset, so just click Create to create a data binding.

Creating a data source in Yandex DataLens

To create a connection, you'll need a service account with the editor role.

To create a Yandex Query connection:

1. Go to the connections page.

2. Click Create connection.

3. Select the Yandex Query connection.

4. Select the folder to create a Yandex Query connection in.

5. Specify the connection parameters:

   • Connection name. Name it yellow-taxi.

   • Cloud and folder. Select the folder where your service account will be located.

   • **Service account **. Select an existing service account or create a new one.

   • SQL query access level. Enables you to use an ad-hoc SQL query to generate a dataset.

6. Click Create in the lower-left corner. The connection appears in the list.

Creating a dataset

1. On the Yandex DataLens homepage, click Create dataset.

2. On the left side of the screen, click Add.

3. Select the yellow-taxi connection you created in the previous step.

4. In the connection properties, go to the SQL tab and paste the query text into the Subquery field:

   SELECT
       *
   FROM
       bindings.`tutorial-analytics`
   

5. Click Create. The dataset appears in the list.

6. If everything is set up correctly, the Preview field will contain data like
   .

7. Create a calculated field:

   1. Go to the Fields tab.
   2. Click Add field.
   3. In the Field name field, specify dropoff_point.
   4. In the Formula field, enter:

      GEOPOINT(ROUND([dropoff_latitude],1),ROUND([dropoff_longitude],1))
      

   5. Click Save.
   6. The Preview section will contain a new field named dropoff_point with data like [40.7,-74].

8. Click Save in the top right-hand corner to save the dataset.

Charts with data

To create a chart:

1. Go to the Charts section in the Yandex DataLens interface.
2. Click Create chart and select Chart.
3. Select the yellow-taxi dataset.
4. Select the Map chart type.
5. Drag the dropoff_point dimension into the Points field.
6. Drag the fare_amount dimension into the Points size field.
7. Drag the tip_amount dimension into the Color field.

The result of running the query is a map showing the distribution of trip fares by region and with the driver tip amount highlighted.