[Yandex Cloud documentation](../../index.md) > [Tutorials](../index.md) > [Building a data platform](index.md) > Analyzing data with Jupyter

# Analyzing data with Jupyter

Yandex Query supports integration with [Jupyter](https://jupyter.org) and [VSCode](https://code.visualstudio.com/docs/datascience/jupyter-notebooks) through the `cell` (`%%yq`) and `line` (`%yq`) Python magic commands. The integration allows you to streamline data collection and analysis for a more efficient and straightforward workflow.

![jupyter-screenshot](../../_assets/query/jupyter-screenshot.png)

To analyze Query data with Jupyter:

1. [Install and configure the `yandex_query_magic` package](#setup-plugin).
1. [Try creating query templates](#templating).
1. [Process the execution results](#capture-command-result).

## Getting started {#before-you-begin}

1. Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md):
   1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
   1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it.
   
   If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).
   
   [Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md).

1. [Get access](https://jupyter.org/install) to the JupyterLab or Jupyter Notebook environment.

## Installing and configuring the yandex_query_magic package {#setup-plugin}

{% list tabs %}

- Jupyter

  Install the [`yandex_query_magic`](https://pypi.org/project/yandex-query-magic/) package by running this command in the notebook cell:

  ```python
  %pip install yandex_query_magic --upgrade
  ```

- Shell

  1. Install the [yandex_query_magic](https://pypi.org/project/yandex-query-magic/) package using pip:

     ```shell
     pip install yandex_query_magic --upgrade
     ```

  1. Enable the Jupyter extension for the Jupyter Notebook UI controls:

     ```shell
     %jupyter contrib nbextension install --user
     ```

     If you get the `"No module named 'notebook.base'"` error, try upgrading to Jupyter Notebook 6.4.12:

     ```shell
     pip install --upgrade notebook==6.4.12
     ```

{% endlist %}

### Configuring the package {#global-settings}

To configure the `yandex_query_magic` package, you can use the `yq_settings` line command with the following arguments specified:

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

Available parameters:

* `--folder-id <folder_id>`: ID of the folder to run Query queries. The folder hosting a VM instance with Jupyter is used by default.
* `--vm-auth`: Authentication with the VM account key. For more information, see [Using Yandex Cloud from within a VM](../../compute/operations/vm-connect/auth-inside-vm.md).
* `--env-auth <environment_variable>`: Authentication with the authorized key kept in the environment variable. Use this mode when you cannot access the file system of the computer running Jupyter. For example, in [Yandex DataSphere](../../datasphere/concepts/index.md). In which case create a [DataSphere secret](../../datasphere/operations/data/secrets.md) and specify its name in the `--env-auth` parameter.
* `--sa-file-auth <authorized_key>`: Authentication with authorized keys. For more information, see [Creating an authorized key](../../iam/operations/authentication/manage-authorized-keys.md#create-authorized-key).

### Testing the package {#check-installation}

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.

If Jupyter is running on a VM with an [attached service account](../../compute/operations/vm-connect/auth-inside-vm.md), upload the extension to Jupyter:

```sql
%load_ext yandex_query_magic
%yq SELECT "Hello, world!"
```

Where:

* `%yq`: Jupyter magic name.
* `SELECT "Hello, world!"`: Text of the query to Query.

If the VM does not have any attached service accounts:

1. [Create](../../iam/operations/sa/create.md) a service account and [assign](../../iam/operations/sa/assign-role-for-sa.md) the `yq.viewer` role to it.

1. [Create an authorized key](../../iam/operations/authentication/manage-authorized-keys.md#create-authorized-key) for the service account.

1. Run the following commands specifying the path to the authorized key file:

    ```sql
    %load_ext yandex_query_magic
    %yq_settings --sa-file-auth '<path_to_key_file>'
    %yq SELECT "Hello, world!"
    ```

    Here is an example:

    ```sql
    %load_ext yandex_query_magic
    %yq_settings --sa-file-auth '/home/test/authorized_key.json'
    %yq SELECT "Hello, world!"
    ```

    The path to the `authorized_key.json` file is specified relative to the directory the current Jupyter Notebook file is saved in.

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

```sql
%%yq --folder-id <folder_ID> --name "My query" --description "Test 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.
* `--description`: Query description.
* `--raw-results`: Returns the unprocessed results of a query run in Query. For the format specification, refer to [YQL to JSON type mapping](../../query/api/yql-json-conversion-rules.md).

## Query templating using Mustache syntax {#templating}

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](https://mustache.github.io) 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](https://jinja.palletsprojects.com/en/3.1.x/) 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:

```python
myQuery = "select * from Departments"
%yq not_var{{myQuery}}
```

The system will interpret the `not_var{{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:

```python
var lstStr = ",".join(lst)
sqlQuery = f'select "Academy" in ListCreate({lstStr});
%yq not_var{{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:

```sql
%yq select "Academy" in not_var{{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:

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

### Jinja2 {#jinja2}

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:

```python
%pip install Jinja2
```

Example of using a Jinja template with the `for` loop:

```python
{% for user in users %}
    command = "select * from users where name='not_var{{ 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:

```python
{% if student.department == "Academy" %}
    not_var{{ student.department|upper }}
{% elif upper(student.department) != "MATHS DEPARTMENT" %}
    not_var{{ 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:

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

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

```sql
%%yq --no-var-expansion
...
```

### Built-in Mustache templates {#embedded_mustache}

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

```python
lst=["Academy", "Physics"]
```

```sql
%yq select "Academy" in not_var{{lst}}
```

#### Using Pandas DataFrame variables {#capture-dataframe}

Here is an example of using `yandex_query_magic` and Mustache syntax with [Pandas DataFrame](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html):

1. Define a variable in Jupyter:

    ```python
    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:

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

Pandas to Query type mapping:

| Pandas type | YQL type | Note |
|-----|-----|-----|
| int64 | Int64 | Exceeding the `int64` limit will result in a query error. |
| float64 | Double ||
| datetime64[ns] | Timestamp | Microsecond precision. Entering nanoseconds [in the `nanosecond` field](https://pandas.pydata.org/docs/user_guide/timeseries.html#time-date-components) will raise an exception. |
| str | String ||

#### Using Python dict variables {#capture-dict}

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

1. Define a variable in Jupyter:

    ```python
    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](https://ydb.tech/docs/en//yql/reference/builtins/dict) object:

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

1. Retrieve the data:

    ```sql
    %%yq
    SELECT "a" in not_var{{dct}}
    ```

Python dict to Query type mapping:

| Python type | YQL type | Note |
|-----|-----|-----|
| int | Int64 | Exceeding the int64 limit will result in a query error. |
| float | Double ||
| datetime | Timestamp ||
| str | String ||

Another way to convert a dictionary to a [Pandas DataFrame](#capture-dataframe) is by using a constructor:

```python
df = pandas.DataFrame(dct)
```

#### Using Python list variables {#capture-list}

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

1. Define a variable in Jupyter:

    ```python
    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](https://ydb.tech/docs/en//yql/reference/types/containers) object:

1. Retrieve the data:

    ```sql
    %%yq
    SELECT 1 IN not_var{{lst}}
    ```

Python list to Query type mapping:

| Python type | YQL type | Note |
|-----|-----|-----|
| int | Int64 | Exceeding the int64 limit will result in a query error. |
| float | Double ||
| datetime | Timestamp ||
| str | String ||

Another way to convert a dictionary to a [Pandas DataFrame](#capture-dataframe) is by using a constructor:

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

### Jinja templates {#jinja}

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:

    ```python
    name = "John"
    ```

1. 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](https://jinja.palletsprojects.com/en/):

    ```sql
    %%yq <other_parameters> --jinja2

    SELECT "not_var{{name}}"
    ```

    Settings:

    * `--jinja2`: Enables [Jinja](https://jinja.palletsprojects.com/) template rendering for the query text. To use this flag, you need to install the [Jinja2](https://pypi.org/project/Jinja2/) package (`%pip install Jinja2`).

#### `to_yq` filter {#to_yq}

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:

```sql
%%yq --jinja2
select "Academy" in not_var{{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:

```sql
%%yq --jinja2
select "Academy" in not_var{{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](#embedded_mustache).

## Capturing command results {#capture-command-result}

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:

```sql
output = %yq SELECT 1 as column1
```

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

```sql
%%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:

```python
output
```

By default, `%yq` and `%%yq` commands return a [Pandas DataFrame](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html). Its columns match SQL column names, and its rows contain query results. You can disable `Pandas DataFrame` conversion using the [--raw-results](#usage) argument.

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

||**column1**|
|---|----|
|**0**|1|

The `output2` variable has the following structure:

||**column2**|**column3**|
|---|----|-----|
|**0**|Two|3|

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:

![jupyter_query_info](../../_assets/query/jupyter-query-info-progress-output.png)

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.