Metabase Extraction Prerequisites

This topic briefs about the Metabase extraction prerequisites.

In This Topic:

Introduction
Accessing the Metabase Database
Identifying Relevant Tables
Exporting Questions/Queries
Exporting Dashboards
Exporting Dashboard Cards
Extracting and Formatting JSON Data
Automating the Extraction Process
Storing the Extracted Data
Scheduling Regular Extractions
Analyzing the Extracted Data
Documenting the Process
Getting Help

Download

Introduction

The purpose of this document is to provide detailed instructions to extract questions, queries, reports, and dashboards from Metabase.

Accessing the Metabase Database

Metabase stores its questions, queries, and dashboards in a database. The first step is to access this database.

Command Example (PostgreSQL):

Copy code

psql -h <hostname> -U <username> -d <database_name>

Identifying Relevant Tables

Identify the tables that contain the information you need. Common tables include report_card, report_dashboard, and report_dashboardcard.

Key Tables:

report_card: Stores individual questions/queries.
report_dashboard: Stores dashboard information.
report_dashboardcard: Stores the mapping of cards to dashboards.

Exporting Questions/Queries

Extract data from the report_card table to get questions and queries.

SQL Query Example:

sql

Copy code

COPY (SELECT * FROM report_card) TO ‘/path/to/output/questions.csv’ WITH CSV HEADER;

Exporting Dashboards

Extract data from the report_dashboard table to get dashboard definitions.

SQL Query Example:

sql

Copy code

COPY (SELECT * FROM report_dashboard) TO ‘/path/to/output/dashboards.csv’ WITH CSV HEADER;

Exporting Dashboard Cards

Extract data from the report_dashboardcard table to get the relationships between dashboards and cards.

SQL Query Example:

sql

Copy code

COPY (SELECT * FROM report_dashboardcard) TO ‘/path/to/output/dashboard_cards.csv’ WITH CSV HEADER;

Extracting and Formatting JSON Data

Metabase often stores configurations in JSON format. Parse these JSON fields if needed for analysis.

Python Example:

python

Copy code

import pandas as pd

import json

# Load the exported CSV

df = pd.read_csv(‘/path/to/output/questions.csv’)

# Parse the JSON column

df[‘visualization_settings’] = df[‘visualization_settings’].apply(json.loads)

# Save to a new file if needed

df.to_csv(‘/path/to/output/parsed_questions.csv’, index=False)

Automating the Extraction Process

Use a script to automate the extraction process at regular intervals.

Shell Script Example:

Copy code

#!/bin/bash

PSQL=”psql -h <hostname> -U <username> -d <database_name>”

$PSQL -c “COPY (SELECT * FROM report_card) TO ‘/path/to/output/questions.csv’ WITH CSV HEADER;”

$PSQL -c “COPY (SELECT * FROM report_dashboard) TO ‘/path/to/output/dashboards.csv’ WITH CSV HEADER;”

$PSQL -c “COPY (SELECT * FROM report_dashboardcard) TO ‘/path/to/output/dashboard_cards.csv’ WITH CSV HEADER;”

Storing the Extracted Data

Store the extracted files in a location where they can be accessed for further analysis such as a data warehouse, local directory, or cloud storage service.

Scheduling Regular Extractions

Use a job scheduler like cron to run your extraction script at regular intervals.

Cron Job Example:

Copy code 0 0 * * * /path/to/extraction_script.sh

Analyzing the Extracted Data

Use data analysis tools (like Python, R, SQL, etc.) to parse and analyze the extracted data files as per your requirements.

Documenting the Process

Document the extraction process including scripts and commands used for future reference and reproducibility.

By following these steps, you can effectively extract Metabase questions, queries, reports, and dashboards into readable and analyzable files.

Steps to Extract Metabase Questions, Queries, Reports, and Dashboards

Prepare the Environment

Ensure you have access to the Metabase instance.
Verify that you have the necessary permissions to access and extract data.
Install any required dependencies, such as Metabase’s API client libraries if needed.

Access the Metabase API

Metabase provides a REST API for accessing various elements. You can use tools like curl, Postman, or any programming language with HTTP capabilities.
Obtain your Metabase API key or set up a session to authenticate requests.

bash

Copy code

# Example using curl to authenticate and obtain a session token

curl -X POST \

-H “Content-Type: application/json” \

-d ‘{“username”: “your_username”, “password”: “your_password”}’ \ http://your-metabase-instance/api/session

Extract Questions

Use the API endpoint to fetch questions. Questions in Metabase are equivalent to saved queries.

bash

Copy code

# Example to get all questions

curl -X GET \

-H “Content-Type: application/json” \

-H “X-Metabase-Session: your_session_token” \ http://your-metabase-instance/api/card

Extract Queries

Extracting queries involves fetching the raw SQL (if applicable) or query details from the questions.

bash

Copy code

# Example to get the raw SQL of a question

curl -X GET \

-H “Content-Type: application/json” \

-H “X-Metabase-Session: your_session_token” \ http://your-metabase-instance/api/card/:card-id/query

Extract Reports (Dashboards)

Dashboards in Metabase are collections of questions displayed together.

bash

Copy code

# Example to get all dashboards

curl -X GET \

-H “Content-Type: application/json” \

-H “X-Metabase-Session: your_session_token” \ http://your-metabase-instance/api/dashboard

Extract Dashboard Details

For detailed information on each dashboard, including the questions.

bash

Copy code

# Example to get details of a specific dashboard

curl -X GET \

-H “Content-Type: application/json” \

-H “X-Metabase-Session: your_session_token” \http://your-metabase-instance/api/dashboard/:dashboard-id

Process and Store Extracted Data

Store the extracted data in a preferred format (JSON, CSV, etc.) for further use.

python

Copy code

# Example using Python to save the response as JSON

import requests

import json

session_token = ‘your_session_token’

headers = {

‘Content-Type’: ‘application/json’,

‘X-Metabase-Session’: session_token

}

response = requests.get(‘http://your-metabase-instance/api/card’, headers=headers)

questions = response.json()

with open(‘metabase_questions.json’, ‘w’) as f: json.dump(questions, f)

Automate the Extraction (Optional)

To schedule this process, automate it using a script or a cron job.

Example Commands Summary

Authenticate and get session token:

bash

Copy code curl -X POST -H “Content-Type: application/json” -d ‘{“username”: “your_username”, “password”: “your_password”}’ http://your-metabase-instance/api/session

Get all questions:

bash

Copy code curl -X GET -H “Content-Type: application/json” -H “X-Metabase-Session: your_session_token” http://your-metabase-instance/api/card

Get raw SQL of a question:

bash

Copy code curl -X GET -H “Content-Type: application/json” -H “X-Metabase-Session: your_session_token” http://your-metabase-instance/api/card/:card-id/query

Get all dashboards:

bash

Copy code curl -X GET -H “Content-Type: application/json” -H “X-Metabase-Session: your_session_token” http://your-metabase-instance/api/dashboard

Get details of a specific dashboard:

bash

Copy code curl -X GET -H “Content-Type: application/json” -H “X-Metabase-Session: your_session_token” http://your-metabase-instance/api/dashboard/:dashboard-id

By following these steps, you can systematically extract questions, queries, reports, and dashboards from Metabase.

Getting Help

Contact LeapLogic technical support at info@leaplogic.io