Visualize data with Redash

1. Install Redash and start a running instance, following the Docker Based Developer Installation Guide (opens in a new tab).
2. Configure Redash to query Pinot, by doing the following:
   1. Add pinotdb dependency
   2. Add a Python data source for Pinot
3. Create visualizations, by doing the following:
   1. Start Pinot
   2. Query in Redash
   3. Add a visualization and dashboard in Redash

Add pinot db dependency

Apache Pinot provides a Python client library pinotdb to query Pinot from Python applications. Install pinotdb inside the Redash worker instance to make network calls to Pinot.

1. Navigate to the root directory where you’ve cloned Redash. Run the following command to get the name of the Redash worker container (by default, redash_worker_1):

docker-compose ps

2. Run the following command (change redash_worker_1 to your own Redash worker container name, if applicable):

docker exec -it redash_worker_1 /bin/sh                                
pip install pinotdb

3. Restart Docker.

Add Python data source for Pinot

1. In Redash, select Settings > Data Sources.

2. Select New Data Source, and then select Python from the list.

3. On the Redash Settings - Data Source page, add Pinot as the name of the data source, enter pinotdb in the Modules to import prior to running the script field.

4. Enter the following optional fields as needed:

   • AdditionalModulesPaths: Enter a comma-separated list of absolute paths on the Redash server to Python modules to make available when querying from Redash. Useful for private modules unavailable in pip.

   • AdditionalBuiltins: Specify additional built-in functions as needed. By default, Redash automatically includes 25 Python built-in functions.

5. Click Save.

Start Pinot

Run the following command in a new terminal to spin up an Apache Pinot Docker container in the quick start mode with a baseball stats dataset built in.

docker run \
  --name pinot-quickstart \
  -p 2123:2123 \
  -p 9000:9000 \
  -p 8000:8000 \
  apachepinot/pinot:0.9.3 QuickStart -type batch

Run a query in Redash

1. In Redash, select Queries > New Query, and then select the Python data source you created in Add a Python data source for Pinot.

2. Add Python code to query data. For more information, see the Python query runner (opens in a new tab).

3. Click Execute to run the query and view results.

For more information, see Querying (opens in a new tab) in Redash documentation.

Example Python queries

Query top 10 teams by total runs

The following query connects to Pinot and queries the baseballStats table to retrieve the top ten players with the highest scores. The results are transformed into a dictionary format supported by Redash.

from pinotdb import connect
 
conn = connect(host='host.docker.internal', port=8000, path='/query/sql', scheme='http')
curs = conn.cursor()
curs.execute("""
    select 
playerName, sum(runs) as total_runs
from baseballStats
group by playerName
order by total_runs desc
limit 10
""")
 
result = {}
result['columns'] = [
    {
      "name": "player_name",
      "type": "string",
      "friendly_name": "playerName"
    },
    {
      "name": "total_runs",
      "type": "integer",
      "friendly_name": "total_runs"
    }
  ]
 
rows = []
 
for row in curs:
    record = {}
    record['player_name'] = row[0]
    record['total_runs'] = row[1]
 
 
    rows.append(record)
 
result["rows"] = rows

Query top 10 teams by total runs

from pinotdb import connect
 
conn = connect(host='host.docker.internal', port=8000, path='/query/sql', scheme='http')
curs = conn.cursor()
curs.execute("""
    select 
teamID, sum(runs) as total_runs
from baseballStats
group by teamID
order by total_runs desc
limit 10
""")
 
result = {}
result['columns'] = [
    {
      "name": "teamID",
      "type": "string",
      "friendly_name": "Team"
    },
    {
      "name": "total_runs",
      "type": "integer",
      "friendly_name": "Total Runs"
    }
  ]
 
rows = []
 
for row in curs:
    record = {}
    record['teamID'] = row[0]
    record['total_runs'] = row[1]
 
 
    rows.append(record)
 
result["rows"] = rows

Query total strikeouts by year

from pinotdb import connect
 
conn = connect(host='host.docker.internal', port=8000, path='/query/sql', scheme='http')
curs = conn.cursor()
curs.execute("""
    select 
yearID, sum(strikeouts) as total_so
from baseballStats
group by yearID
order by yearID asc
limit 1000
""")
 
result = {}
result['columns'] = [
    {
      "name": "yearID",
      "type": "integer",
      "friendly_name": "Year"
    },
    {
      "name": "total_so",
      "type": "integer",
      "friendly_name": "Total Strikeouts"
    }
  ]
 
rows = []
 
for row in curs:
    record = {}
    record['yearID'] = row[0]
    record['total_so'] = row[1]
 
 
    rows.append(record)
 
result["rows"] = rows

Add a visualization and dashboard in Redash

Add a visualization

In Redash, after you've ran your query, click the New Visualization tab, and select the type of visualization your want to create, for example, Bar Chart. The Visualization Editor appears with your chart.

For example, you may want to create a bar chart to view the top 10 players with highest scores.

You may want to create a line chart to view the total variation in strikeouts over time.

For more information, see Visualizations (opens in a new tab).

Add a dashboard

Create a dashboard with one or more visualizations (widgets).

1. In Redash, go to Dashboards > New Dashboards, and then add the widgets to your dashboard. For example, by adding the three visualizations from the three example queries above, you create a Baseball stats dashboard.

For more information, see Dashboards (opens in a new tab) in Redash documentation.