Analyzing data with Yandex Query

Yandex Query is an interactive service for serverless data analysis. It allows you to process data from various storages using SQL queries without creating a dedicated data processing cluster. Yandex Query works with Yandex Object Storage, Managed Service for PostgreSQL, and Managed Service for ClickHouse® data storages.

To analyze DataSphere data using Query:

1. Install and configure the yandex_query_magic package.
2. Create a query template.
3. Process the execution results.

Getting startedGetting started

Before getting started, register in Yandex Cloud, set up a community, and link your billing account to it.

1. On the DataSphere home page, click Try for free and select an account to log in with: Yandex ID or your working account with the identity federation (SSO).
2. Select the Yandex Identity Hub organization you are going to use in Yandex Cloud.
3. Create a community.
4. Link your billing account to the DataSphere community you are going to work in. Make sure you have a linked billing account and its status is ACTIVE or TRIAL_ACTIVE. If you do not have a billing account yet, create one in the DataSphere interface.

Set up the infrastructure to work with Yandex Query:

1. Go to the management console and link the billing account to the cloud.
2. Create a folder where Yandex Query will run.

Required paid resourcesRequired paid resources

The cost of analyzing data using Yandex Query includes:

• Fee for using DataSphere computing resources.
• Fee for data read by Yandex Query when running queries.

Install and configure the yandex_query_magic packageInstall and configure the yandex_query_magic package

1. Open the DataSphere project:

   1. Select the project in your community or on the DataSphere home page in the Recent projects tab.

   2. Click Open project in JupyterLab and wait for the loading to complete.
   3. Open the notebook tab.

2. Install the yandex_query_magic package by running this command in the notebook cell:

%pip install yandex_query_magic --upgrade

1. Configure the yandex_query_magic package by specifying the parameters using the yq_settings line command:

%yq_settings --folder-id <folder_ID> ...

Available parameters:

• --folder-id <folder_id>: ID of the folder to run Query queries.
• --env-auth <environment_variable>: Sets authentication with the authorized key whose contents are stored in the Yandex DataSphere secret. Create a DataSphere secret and specify its name in the --env-auth parameter.

Test the packageTest the package

You can use the %yq line magic command with a single-line SQL query. In this case, the %yq keyword is used to run the query.

Run the following commands in the notebook:

%load_ext yandex_query_magic
%yq_settings --env-auth <Yandex DataSphere_secret_name> --folder-id <folder_ID>
%yq SELECT "Hello, world!"

Where:

• %yq: "Magic" command name.
• SELECT "Hello, world!": Text of the query to Query.

To send multi-line SQL queries, you need to use %%yq cell magic. The query text must begin with the %%yq keyword:

%%yq --folder-id <folder_ID> --name "My query"  --raw-results

SELECT
    col1,
    COUNT(*)
FROM table
GROUP BY col1

Where:

• --folder-id: ID of the folder to run Query queries. The default folder is the one specified earlier through %yq_settings. If not specified, the folder in which the VM is running is used.
• --name: Query name, optional.
• --description: Query description, optional.
• --raw-results: Returns raw results of Query query processing, optional. For the format specification, refer to YQL to JSON type mapping.

Query templating using Mustache syntaxQuery templating using Mustache syntax

You can use query templates shared between Jupyter and Query to work with queries and perform routine operations without writing any code. For this, Query comes with built-in Mustache syntax support. You can use it to write queries, placing all template keywords and directives inside double curly brackets ({{}}). You can use Mustache syntax either with Jinja2 or with the built-in Mustache interpreter.

Built-in mustache templates {{ yq-name }} allow you to insert Jupyter runtime variables into SQL queries. Such variables will be automatically converted into required Query data structures. Here is an example:

myQuery = "select * from Departments"
%yq {{myQuery}}

The system will interpret the {{myQuery}} Mustache string as the name of the variable containing the required text and will send select * from Departments to Query for execution.

Mustache templates streamline Jupyter and Query integration. Suppose you have a Python list lst=["Academy", "Physics"] containing the names of departments whose data you want to process. If Query did not support Mustache syntax, you would need to convert the Python list into a string first and then insert it into your SQL query. Query example:

var lstStr = ",".join(lst)
sqlQuery = f'select "Academy" in ListCreate({lstStr});
%yq {{sqlQuery}}

This example showcases how working with complex data types requires you to understand the specifics of Query SQL syntax. With Mustache syntax, the query becomes simpler:

%yq select "Academy" in {{lst}}

Here, the system will automatically recognize lst as a Python list, insert the SQL fragment required for list processing, and send the following query text to Query:

%yq select "Academy" in ListCreate("Academy", "Physics") as lst

Jinja2Jinja2

We recommend using the built-in Mustache syntax for routine tasks in Jupyter and Query. For advanced templating, use Jinja2.

To install Jinja2, run this command:

%pip install Jinja2

Example of using a Jinja template with the for loop:

{% for user in users %}
    command = "select * from users where name='{{ user }}'"
{% endfor %}

You can also use Jinja templates for data processing operations. In the following example, the operations performed on the department name vary based on the student’s department:

{% if student.department == "Academy" %}
    {{ student.department|upper }}
{% elif upper(student.department) != "MATHS DEPARTMENT" %}
    {{ student.department|capitalize }}
{% endif %}

To make Jinja convert data according to Query rules, use the to_yq filter. Here is how the Python list from the previous example (lst=["Academy", "Physics"]) looks in a Jinja template:

%%yq --jinja2
select "Academy" in {{lst|to_yq}}

To disable templating, use the --no-var-expansion argument:

%%yq --no-var-expansion
...

Built-in Mustache templatesBuilt-in Mustache templates

Built-in mustache templates are enabled by default in Yandex Query to streamline basic operations with Jupyter variables:

lst=["Academy", "Physics"]

%yq select "Academy" in {{lst}}

Using Pandas DataFrame variablesUsing Pandas DataFrame variables

Here is an example of using yandex_query_magic and Mustache syntax with Pandas DataFrame:

1. Define a variable in Jupyter:

   df = pandas.DataFrame({'_float': [1.0],
                       '_int': [1],
                       '_datetime': [pd.Timestamp('20180310')],
                       '_string': ['foo']})
   

You can use the df variable when querying Yandex Query. During query execution, the system uses the df value to create a temporary table also named df. This table can then be used within the Yandex Query query.

1. Retrieve the data:

   %%yq
   SELECT
       *
   FROM mytable
   INNER JOIN {{df}}
       ON mytable.id=df._int
   

Pandas to Query type mapping:

Using Python dict variablesUsing Python dict variables

Here is an example of using yandex_query_magic with Mustache syntax and a Python dict:

1. Define a variable in Jupyter:

   dct = {"a": "1", "b": "2", "c": "test", "d": "4"}
   

   Now you can use the dct variable in Query queries. At query runtime, the system will convert dct into the relevant YQL Dict object:

   Key	Value
   a	"1"
   b	"2"
   c	"test"
   d	"4"

2. Retrieve the data:

   %%yq
   SELECT "a" in {{dct}}
   

Python dict to Query type mapping:

Another way to convert a dictionary to a Pandas DataFrame is by using a constructor:

df = pandas.DataFrame(dct)

Using Python list variablesUsing Python list variables

Here is an example of using yandex_query_magic with Mustache syntax and a Python dict:

1. Define a variable in Jupyter:

   lst = [1,2,3]
   

   Now you can use the lst variable in Query queries. At query runtime, the system will convert lst into the relevant YQL Dict object:

2. Retrieve the data:

   %%yq
   SELECT 1 IN {{lst}}
   

Python list to Query type mapping:

Another way to convert a dictionary to a Pandas DataFrame is by using a constructor:

df = pandas.DataFrame(lst,
                      columns =['column1', 'column2', 'column3'])

Jinja templatesJinja templates

Jinja templates make it easy to build SQL queries, allowing you to automatically insert search conditions and other data. This way, you don’t need to write each query from scratch. It can help you streamline your workflow, avoid mistakes, and create more readable code.

You can also use Jinja templates to automate building queries with repetitive parts. For example, you can use template loops to write multiple queries that check different values from a list. This adds even more flexibility, speeding up the process of writing complex queries when you need to handle large amounts of data.

The steps below explain how to filter Yandex Query data using a Python variable.

1. Define a variable in Jupyter:

   name = "John"
   

2. Before you run the following code in a Jupyter cell, set the jinja2 flag. It will tell the system to treat SQL queries as Jinja2 templates:

   %%yq <other_parameters> --jinja2
   
   SELECT "{{name}}"
   

   Settings:

   • --jinja2: Enables Jinja template rendering for the query text. To use this flag, you need to install the Jinja2 package (%pip install Jinja2).

filterto_yq filter

Jinja2 is a general-purpose templating engine. When handling variables, it uses a standard string representation of data types.

For example, if you have a Python list lst=["Academy", "Physics"], here is how you can use it in a Jinja template:

%%yq --jinja2
select "Academy" in {{lst}}

Running this code will result in the Unexpected token '[' error. This error occurs because Jinja converts the lst variable to the ["Academy", "Physics"] string using Python rules and ignores Yandex Query-SQL specifics.

To tell Jinja that it should follow Yandex Query rules, use the to_yq filter. In this case, the previous query in the Jinja syntax will look like this:

%%yq --jinja2
select "Academy" in {{lst|to_yq}}

The to_yq Jinja filter converts data to the Yandex Query syntax in the exact same way as built-in Mustache templates.

Capturing command resultsCapturing command results

You can capture the output of a line magic command using an assignment:

varname = %yq <query>

To capture a cell magic command’s output, start the query with the target variable name and the << operator:

%%yq
varname << <query>

From there, you can treat the result as any other Jupyter variable.

For example, let’s use a cell magic to capture the result in the output variable:

output = %yq SELECT 1 as column1

And here we use a line magic to capture the result in the output2 variable:

%%yq
output2 << SELECT 'Two' as column2, 3 as column3

Now, you can use these variables just like any other IPython variable. For example, you can print them:

output

By default, %yq and %%yq commands return a Pandas DataFrame. Its columns match SQL column names, and its rows contain query results. You can disable Pandas DataFrame conversion using the --raw-results argument.

In our example, the output variable has the following structure:

The output2 variable has the following structure:

If your query does not return any results (for example, it is insert into table select * from another_table), the return value will be None. If a query returns multiple results, they will be presented as a list.

When you run a query, yandex_query_magic outputs extra details, e.g., query ID, start time, and query duration:

To hide the progress output for a cell, you can use the %%capture command.

%%capture
%%yq
<query>

This will suppress progress output in the console.