I want to use a python data source in Re: Dash to get query results

Introduction

Re: Dash is for engineers who don't want to make screens It's very convenient to create a screen or graph just by forming a query. But do you want to get a little more data than just making queries? What's more, do you want to combine query results from different data sources? ??

Re: Dash has a "python data source" With a little setting, you will be able to mess with data with python. This was very convenient, so this time I will introduce the main functions and usage from the setting method.

Make python data source available

By default, python data sources are disabled. If you add the following settings, you can specify python from the data source.

Modify /opt/redash/.env

/opt/redash/.env

export REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.python

Reflect the settings

sudo supervisorctl restart redash_server

Add new data source

From Data Sources, click + New Data Sources. Since Python can be selected for Type, select it, enter an appropriate name in Name, and save Allows you to select a Python data source when creating a query.

Prepare the data you want to go to

Prepare the data before you go to school in advance. I will omit the details in this article, but let's set the periodic execution and acquire and cache only the necessary data at the necessary timing.

This time, I will prepare for the purpose of "summing and displaying the numerical values between different data sources". Two simple query results are prepared as shown below.

Data ①

Make a note of the query ID of the created query. This time this query is query ID = 76

Data ②

Make a note of the query ID of the created query. This time this query is query ID = 77

Let's try the data

What kind of assumption aside w From query results obtained from two different data sources Let's try to add up the COUNTs with the same ID and display them.

The whole code I wrote roughly

Get the query result using API, write the process you want to do with python, and set the result with API OK

• See below for API
• The following is an appropriate code, but you can use it normally such as for and if statements! In the example. ..
• The query result obtained by get_query_result is obtained in the form of rows ['rows'] [number of rows] [column name] = value.

#Get the query result of the specified query ID
queryResults1 = get_query_result(76)
queryResults2 = get_query_result(77)

result = {}
for rows1 in queryResults1['rows']:
    for rows2 in queryResults2['rows']:
        if rows1['id'] == rows2['id']:
            #Add row data
            add_result_row(result, {
                'id'    :rows1['id'],
                'count' :rows1['count'] + rows2['count']
            })
            break

#Specify the column name
add_result_column(result, 'id', '', 'integer')
add_result_column(result, 'count', '', 'integer')

Key API

For more information, please read Actual Re: dash internal source

If you want to use a module

It can be used by adding a module from the setting screen of Data Source. If you want to add more than one, add them separated by commas. After saving, you can use it just by importing it in the code.

at the end

Since you can write code in python normally, you can do a little complicated processing. I think that the range of usage will expand. Above all, it's great when you don't have to make a screen. .. Please use it!

Related article

I want to upgrade Re: Dash Summary of useful tricks with Re: Dash