Metabase Extraction Prerequisites
This topic briefs about the Metabase extraction prerequisites.
In This Topic:
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):
sh
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)
Use a script to automate the extraction process at regular intervals.
Shell Script Example:
sh
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;”
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.
Use a job scheduler like cron to run your extraction script at regular intervals.
Cron Job Example:
sh
Copy code 0 0 * * * /path/to/extraction_script.sh
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.
- 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
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
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
Leaplogic