Skip to content

Instantly share code, notes, and snippets.

@zcaudate

zcaudate/gist:63a287c7e23458f5361e178e9aaf0056

Created January 26, 2025 10:23
Show Gist options
  • Save zcaudate/63a287c7e23458f5361e178e9aaf0056 to your computer and use it in GitHub Desktop.
Save zcaudate/63a287c7e23458f5361e178e9aaf0056 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
Below is an overview of how you can interface with Metabase from a data science perspective using Clojure. The examples focus on using Metabase’s REST API to run queries and retrieve results for further analysis or visualization in Clojure.
---
## 1. Overview of the Metabase API
Metabase provides a REST API that you can use to:
- Authenticate and obtain a session token
- Run queries (either raw SQL or saved “cards” in Metabase)
- Manage entities like dashboards, pulses, collections, etc.
From a data science perspective, the most common workflow is:
1. **Authenticate** with Metabase and acquire a session token.
2. **Run a query** (either a saved question/card or a raw query).
3. **Retrieve the results** in JSON form.
4. **Parse those results** in your Clojure code.
5. **Perform subsequent analysis** using your preferred Clojure data libraries.
---
## 2. Setup: Clojure Dependencies
In your `deps.edn` (if you’re using the CLI tools) or `project.clj` (Leiningen), add:
```clojure
;; deps.edn
{:deps {org.clojure/clojure {:mvn/version "1.11.1"}
clj-http/clj-http {:mvn/version "3.12.3"}
cheshire/cheshire {:mvn/version "5.11.0"}}}
;; or project.clj
(defproject my-metabase-integration "0.1.0-SNAPSHOT"
:dependencies [[org.clojure/clojure "1.11.1"]
[clj-http "3.12.3"]
[cheshire "5.11.0"]])
```
This brings in:
- **`clj-http`** for making HTTP requests.
- **`cheshire`** for converting JSON to/from Clojure data structures.
---
## 3. Authenticating With Metabase
When interacting with the Metabase API, you typically start by logging in to obtain a session token. You’ll send a POST request to `/api/session` with your Metabase username and password.
```clojure
(ns myproject.metabase
(:require [clj-http.client :as client]
[cheshire.core :as json]))
(def metabase-url "https://your-metabase-instance.com")
(defn metabase-login
"Logs into Metabase using username and password.
Returns a session token (string) if successful."
[username password]
(let [url (str metabase-url "/api/session")
response (client/post url
{:content-type :json
:accept :json
:as :json
:body (json/encode {:username username
:password password})})]
(-> response :body :id))) ; :id is the session token
```
- Adjust `metabase-url` to match your Metabase host (e.g., `https://metabase.mycompany.com`).
- Store the returned session token to use in subsequent API calls.
---
## 4. Running a Query
### 4.1 Running a Raw SQL Query
To run a raw SQL query, you can POST to `/api/dataset` with the necessary parameters. For example:
```clojure
(defn run-raw-sql-query
"Runs a raw SQL query against a given database in Metabase."
[token database-id sql]
(let [url (str metabase-url "/api/dataset")
response (client/post url
{:headers {"X-Metabase-Session" token}
:content-type :json
:accept :json
:as :json
:body (json/encode
{:type "native"
:native {:query sql}
:database database-id})})]
(:body response)))
```
- Replace `database-id` with the ID that corresponds to your Metabase database (visible in the Metabase Admin panel).
- The response body typically includes `:data` with `:rows` and `:cols`.
- For large results, you may want to handle pagination or streaming if your dataset is huge.
Example usage:
```clojure
(let [token (metabase-login "[email protected]" "YourPassword")
database-id 1 ; example ID
sql "SELECT * FROM orders LIMIT 10"
response (run-raw-sql-query token database-id sql)]
(println "Query result:" response))
```
The `response` map might look like:
```clojure
{:data
{:rows [[1 "Alice" 42.0] [2 "Bob" 33.5] ...]
:cols [{:name "id"} {:name "customer_name"} {:name "amount"}] }
:native_query {:query "SELECT * FROM orders LIMIT 10"}
;; ... more fields ...
}
```
You can parse these rows/cols into your own data structures or load them into a Clojure data analysis library.
---
### 4.2 Running a Saved Question (Metabase “Card”)
If you have a saved question (called a “card” in Metabase), you can run it by hitting `/api/card/:id/query`. For example:
```clojure
(defn run-saved-question
"Runs a saved question (Metabase Card) by its ID."
[token card-id]
(let [url (str metabase-url "/api/card/" card-id "/query")
response (client/post url
{:headers {"X-Metabase-Session" token}
:content-type :json
:accept :json
:as :json})]
(:body response)))
```
Example usage:
```clojure
(let [token (metabase-login "[email protected]" "YourPassword")
card-id 12345
result (run-saved-question token card-id)]
(println "Saved question results:" result))
```
This returns the same type of structure (`:data` -> `:rows` and `:cols`) that you can work with in your code.
---
## 5. Parsing Metabase Results for Data Science
Once you have the JSON response from Metabase, the typical structure (for tabular data) is something like:
```clojure
{:data
{:rows [[val11 val12 ...]
[val21 val22 ...]
...]
:cols [{:name "column1"}
{:name "column2"}
...]}}
```
You can transform this into a Clojure-friendly table or records for further processing. For instance, you could map over each row and zip it with column names:
```clojure
(defn response->maps
"Converts Metabase `:data` with `:rows` and `:cols` into a seq of maps."
[data]
(let [cols (map :name (:cols data))
rows (:rows data)]
(map (fn [row] (zipmap cols row)) rows)))
(defn example-analysis []
(let [token (metabase-login "[email protected]" "YourPassword")
sql "SELECT id, customer_name, amount FROM orders LIMIT 10"
raw (run-raw-sql-query token 1 sql)
rows (-> raw :data response->maps)]
(doseq [row rows]
(println row))))
```
You might see prints like:
```text
{:id 1, :customer_name "Alice", :amount 42.0}
{:id 2, :customer_name "Bob", :amount 33.5}
...
```
From here, you can load this data into your favorite Clojure data analysis library, such as:
- [**tech.ml.dataset**](https://github.com/techascent/tech.ml.dataset)
- [**tablecloth**](https://scicloj.github.io/tablecloth/)
---
## 6. Example Workflow
1. **Add your Metabase credentials** to a secure location (environment variables or a secrets manager).
2. **Authenticate** to Metabase to get a session token.
3. **Run** a raw query or a saved question.
4. **Transform** the results to a suitable data structure.
5. **Analyze** using your library of choice (machine learning, data visualization, etc.).
6. **(Optional)** Save or visualize results back in Metabase or in your own environment.
```clojure
(defn full-workflow []
(let [username (System/getenv "METABASE_USER")
password (System/getenv "METABASE_PASS")
token (metabase-login username password)
db-id 1
sql "SELECT category, COUNT(*) AS count FROM products GROUP BY category"
response (run-raw-sql-query token db-id sql)
data-maps (-> response :data response->maps)]
(println "Analyzed data:" data-maps)
;; proceed with further analysis...
))
```
---
## 7. Additional Resources
- **Official Metabase API Docs**:
Metabase has an (unofficial) swagger interface if you append `"/api/docs"` to your instance URL, or search for Metabase’s official docs on their GitHub.
- **Metabase Public API**:
Look for references to endpoints like `/api/dataset`, `/api/card`, `/api/dashboard`, etc.
- **Security**:
Don’t forget to handle your session token carefully and avoid checking credentials or tokens into source control.
- **Data Science in Clojure**:
- Libraries like [tech.ml.dataset](https://github.com/techascent/tech.ml.dataset) and [tablecloth](https://scicloj.github.io/tablecloth/) provide DataFrame-like operations.
- If you need machine learning or advanced analytics, check out [scicloj.ml](https://scicloj.github.io/) or other related projects.
---
### Summary
By leveraging the Metabase REST API with **clj-http** and **cheshire**, you can programmatically authenticate, run queries, and fetch data from Metabase into your Clojure environment. From there, you can perform any data science or machine learning tasks you need, seamlessly integrating Metabase’s visualization and dashboarding capabilities with your Clojure-based data workflow.
Below is a step-by-step example of how you might load a cycling dataset from Kaggle into Metabase and then ask interesting questions from a cyclist’s perspective, all while leveraging Clojure. The high-level steps are:
1. **Download the dataset from Kaggle** (typically CSV).
2. **Load that data into a database** (e.g. PostgreSQL).
3. **Add/Configure that database in Metabase** so Metabase can query it.
4. **Use Metabase’s REST API** (from Clojure) to run queries and retrieve insights.
> **Note**: Metabase itself does not store raw data; it queries your existing databases. So the common workflow is:
> 1) Put your data in a DB,
> 2) Connect that DB to Metabase,
> 3) Query from Metabase (either via the UI or via the API).
Below is an illustrative example using PostgreSQL. Adjust as necessary for your environment, column names, and Kaggle dataset schema.
---
## 1. Ingest the Kaggle Cycling Dataset Into PostgreSQL
Suppose you downloaded a Kaggle cycling CSV (e.g., `kaggle-cycling-dataset.csv`) with the following columns:
```
start_time,end_time,start_station,end_station,distance_km,avg_speed_km_h,user_type
2024-01-01 08:00:00,2024-01-01 08:15:00,Station A,Station B,3.2,12.8,Subscriber
...
```
> Adjust the schema to match your actual CSV. This is just an example.
### 1.1 Add Dependencies
In your `deps.edn` (Clojure CLI) or `project.clj` (Leiningen), you’ll need libraries for:
- **PostgreSQL JDBC driver** (for inserting data)
- **`next.jdbc`** or `clojure.java.jdbc` (for interacting with SQL)
- **`clojure.data.csv`** (for reading CSV files)
Here’s an example `deps.edn` snippet:
```clojure
{:deps
{org.clojure/clojure {:mvn/version "1.11.1"}
org.postgresql/postgresql {:mvn/version "42.6.0"}
seancorfield/next.jdbc {:mvn/version "1.3.873"}
clojure.data.csv {:mvn/version "1.0.1"}}}
```
### 1.2 Create a Table and Load Data
```clojure
(ns example.load-cycling
(:require [clojure.java.io :as io]
[clojure.data.csv :as csv]
[next.jdbc :as jdbc]
[next.jdbc.sql :as sql]))
;; Connection details for PostgreSQL
(def db-spec
{:dbtype "postgresql"
:host "localhost"
:port 5432
:dbname "cyclingdb"
:user "myuser"
:password "mypassword"})
(defn create-rides-table []
(jdbc/execute!
db-spec
["
CREATE TABLE IF NOT EXISTS rides (
id SERIAL PRIMARY KEY,
start_time TIMESTAMP,
end_time TIMESTAMP,
start_station VARCHAR(255),
end_station VARCHAR(255),
distance_km NUMERIC,
avg_speed_km_h NUMERIC,
user_type VARCHAR(50)
)
"]))
(defn load-csv-into-db [csv-file]
(with-open [reader (io/reader csv-file)]
;; Assuming the first row is a header
(doseq [row (rest (csv/read-csv reader))]
(let [[start-time-str end-time-str start-station end-station
distance-km-str avg-speed-km-h-str user-type] row]
(sql/insert! db-spec :rides
{:start_time (java.sql.Timestamp/valueOf start-time-str)
:end_time (java.sql.Timestamp/valueOf end-time-str)
:start_station start-station
:end_station end-station
:distance_km (Double/parseDouble distance-km-str)
:avg_speed_km_h (Double/parseDouble avg-speed_km-h-str)
:user_type user-type})))))
(defn -main []
(create-rides-table)
(load-csv-into-db "kaggle-cycling-dataset.csv")
(println "Data loaded successfully into 'rides' table!"))
```
- Adjust the code based on your CSV structure (column order, data types, etc.).
- After running `-main`, your PostgreSQL database should have a new `rides` table containing rows from the Kaggle dataset.
---
## 2. Connect the New Database to Metabase
Once the data is in PostgreSQL, you need to let Metabase know about it. You can do this via the Metabase Admin UI (simplest approach) or programmatically via the Metabase API. Below is a short snippet using Metabase’s API in Clojure.
### 2.1 Add Metabase API Dependencies
In addition to the DB libraries, add:
```clojure
;; For Metabase API usage:
clj-http/clj-http {:mvn/version "3.12.3"}
cheshire/cheshire {:mvn/version "5.11.0"}
```
### 2.2 Create a Namespace for Metabase Integration
```clojure
(ns example.metabase-integration
(:require [clj-http.client :as client]
[cheshire.core :as json]))
(def metabase-url "http://localhost:3000")
;; or "https://your-metabase-instance.com" if you host remotely
(defn login
"Obtain a Metabase session token via username and password."
[username password]
(-> (client/post (str metabase-url "/api/session")
{:content-type :json
:accept :json
:as :json
:body (json/encode {:username username
:password password})})
:body
:id))
(defn add-postgres-database
"Registers a new PostgreSQL DB in Metabase via the Metabase API."
[token {:keys [name host port dbname user password]}]
(client/post (str metabase-url "/api/database")
{:headers {"X-Metabase-Session" token}
:content-type :json
:accept :json
:as :json
:body (json/encode {:name name
:engine "postgres"
:details {:host host
:port port
:dbname dbname
:user user
:password password
:ssl false}})}))
(defn -main []
(let [token (login "metabase-user" "metabase-pass")]
(add-postgres-database token
{:name "CyclingDB"
:host "localhost"
:port 5432
:dbname "cyclingdb"
:user "myuser"
:password "mypassword"})
(println "Database 'CyclingDB' added to Metabase!")))
```
- Adjust credentials and connection details accordingly.
- Run `-main` here to register the database with Metabase.
- Now Metabase can see your `cyclingdb` and the `rides` table.
---
## 3. Ask Interesting Cycling Questions via Metabase’s API
With the database connected, we can run queries from Clojure by posting to Metabase’s `/api/dataset` endpoint. You can also build these “Questions” in the Metabase UI, but here is how you do it programmatically:
```clojure
(ns example.query-cycling
(:require [clj-http.client :as client]
[cheshire.core :as json]
[example.metabase-integration :as mb]))
(defn run-raw-sql-query
"Executes a raw SQL query against a given DB ID in Metabase."
[token database-id sql]
(let [url (str mb/metabase-url "/api/dataset")
response (client/post url
{:headers {"X-Metabase-Session" token}
:content-type :json
:accept :json
:as :json
:body (json/encode
{:type "native"
:native {:query sql}
:database database-id})})]
(:body response)))
(defn interesting-queries [token db-id]
;; 1. Top 5 most popular start stations
(println "\n--- Top 5 most popular start stations ---")
(let [sql "SELECT start_station, COUNT(*) AS total_rides
FROM rides
GROUP BY start_station
ORDER BY total_rides DESC
LIMIT 5"
result (run-raw-sql-query token db-id sql)]
(println (json/encode (:data result) {:pretty true})))
;; 2. Average distance traveled (km) across all rides
(println "\n--- Average distance traveled (km) ---")
(let [sql "SELECT AVG(distance_km) AS avg_distance FROM rides"
result (run-raw-sql-query token db-id sql)]
(println (json/encode (:data result) {:pretty true})))
;; 3. Busiest hour of the day to start a ride
(println "\n--- Busiest hour of the day ---")
(let [sql "SELECT EXTRACT(HOUR FROM start_time) AS hour_of_day,
COUNT(*) AS total_rides
FROM rides
GROUP BY hour_of_day
ORDER BY total_rides DESC
LIMIT 1"
result (run-raw-sql-query token db-id sql)]
(println (json/encode (:data result) {:pretty true})))
;; 4. Which user type (e.g. Subscriber vs. Customer) travels the greatest average distance?
(println "\n--- User type with the greatest average distance ---")
(let [sql "SELECT user_type, AVG(distance_km) AS avg_dist
FROM rides
GROUP BY user_type
ORDER BY avg_dist DESC
LIMIT 1"
result (run-raw-sql-query token db-id sql)]
(println (json/encode (:data result) {:pretty true}))))
(defn -main []
(let [token (mb/login "metabase-user" "metabase-pass")]
;; You can find the DB ID in Metabase’s Admin > Databases, or via /api/database
(interesting-queries token 2))) ; e.g., 2 might be the ID assigned to "CyclingDB"
```
> **Tip**: `:data` in the JSON response often has a structure like:
> ```clojure
> {:rows [[val1 val2 ...] [val1b val2b ...] ...]
> :cols [{:name "column1"} {:name "column2"} ...]}
> ```
> You can transform these rows/cols into maps for further analysis in Clojure.
---
## 4. Potential Questions of Interest to Cyclists
Here are a few additional ideas you might query (in raw SQL, or build visually in Metabase):
- **Peak commuting times**: “Which hours on weekdays see the most rides started?”
- **Most traveled routes**: “Which (start_station -> end_station) pair has the highest count?”
- **Average speed vs. distance**: “Do longer rides typically have higher or lower average speeds?”
- **Seasonal usage**: If you have time-series data, “Which months have the highest ridership? Which have the lowest?”
- **Trip duration**: “Average or median trip duration per user type or per time of day.”
All of these questions can be turned into Metabase “Questions” or raw queries. You can explore them in Metabase’s UI (great for quick charting and dashboards) or programmatically via the `/api/dataset` endpoint as shown.
---
## 5. Summary
1. **Load your Kaggle cycling data** into a SQL database (like PostgreSQL) so that Metabase can query it.
2. **Register that database** in Metabase (via Admin UI or API).
3. **Use Metabase’s REST API** from Clojure (via `clj-http` + `cheshire`) to run interesting SQL queries.
4. **Interpret the JSON response** in your Clojure application for deeper data science/analysis.
This approach allows you to combine Metabase’s easy dashboarding with Clojure’s robust data and ML libraries—giving you a powerful, end-to-end solution for exploring cycling data from Kaggle.
Metabase is designed to connect to an external data source rather than storing large datasets internally. By default, Metabase doesn’t offer a “CSV import” workflow that brings data into its own database. Instead, you typically connect Metabase to a database (PostgreSQL, MySQL, SQLite, BigQuery, etc.) and query the data there.
That said, there are a couple of ways to “skip Postgres” if you just want to point Metabase at a CSV file or small dataset without managing a separate database:
1. **Use the (Unofficial) CSV Driver for Metabase**
2. **Use a Lightweight Embedded Database (e.g. SQLite) and load the CSV there**
Below, I’ll outline both approaches.
---
## 1. Use the (Unofficial) CSV Driver
A community-maintained CSV driver allows Metabase to treat CSV files as a data source. This is not part of Metabase’s core, but a third-party plugin you can install.
### 1.1 Installing the CSV Driver
1. **Download the driver** (a `.jar` file) from the community repository, e.g., [metabase/elastic2-mb](https://github.com/Markenson/csv-metabase-driver) or a similar fork that’s actively maintained.
2. **Place** that `.jar` in Metabase’s `plugins/` directory.
3. **Restart** Metabase so it picks up the new driver.
*(Because these are unofficial drivers, the exact source/steps might differ. Check the driver’s README for instructions.)*
### 1.2 Configure the CSV “Database” in Metabase
After restarting Metabase:
1. Go to **Admin > Databases** in Metabase.
2. Click **Add Database**.
3. Select the “CSV” (or “CSV File”) driver from the dropdown (the name can vary based on the plugin).
4. Point it to your CSV file(s) or the folder that contains them. Provide any config options the driver needs (e.g., delimiter, header row, etc.).
5. Save.
Metabase will now treat the CSV files as if they’re tables, allowing you to explore them, run queries, and create dashboards—no separate PostgreSQL or MySQL database needed.
> **Limitations**:
> - Query performance on large CSV files may be slow.
> - Many CSV drivers load the entire file into memory.
> - Some advanced Metabase features may not work identically, since you’re using a custom driver.
---
## 2. Use a Lightweight Embedded Database
If you’re comfortable with a little script work, you can load a CSV into a small local database like **SQLite** (instead of PostgreSQL). Metabase supports SQLite out of the box, so you can “skip Postgres” while still using a real database engine underneath.
### 2.1 Create a SQLite Database
1. **Install SQLite** on your machine.
2. Open a terminal and run something like:
```bash
sqlite3 cycling.db
```
3. Create a table and import the CSV:
```sql
CREATE TABLE rides (
id INTEGER PRIMARY KEY,
start_time TEXT,
end_time TEXT,
start_station TEXT,
end_station TEXT,
distance_km REAL,
avg_speed_km_h REAL,
user_type TEXT
);
.mode csv
.import /path/to/kaggle-cycling-dataset.csv rides
```
(Adjust commands depending on your CSV’s structure.)
4. Exit SQLite.
### 2.2 Connect Metabase to the SQLite file
1. Go to **Admin > Databases** in Metabase.
2. Click **Add Database**.
3. Choose **SQLite**.
4. For **Database File**, provide the absolute path to `cycling.db` (e.g., `/home/username/projects/cycling.db`).
5. Save.
Metabase will scan the `rides` table and allow you to query it without any PostgreSQL setup.
---
## 3. Example: Interesting Cycling Questions
Whichever method you use (CSV driver or SQLite), once Metabase has access to the data, you can query it in two ways:
1. **In the Metabase UI**: Create a new question, choose “Custom,” and build queries with the GUI.
2. **Programmatically**: Use Metabase’s REST API from any language (including Clojure).
### 3.1 Programmatic Query (Clojure)
Here’s a small snippet that queries the Metabase API for top stations. We assume:
- **DB ID** is discovered from Metabase’s Admin > Databases or from `/api/database`.
- You already have a **session token** (via the standard “login” call).
```clojure
(ns myproject.metabase-queries
(:require [clj-http.client :as client]
[cheshire.core :as json]))
(def metabase-url "http://localhost:3000")
(defn run-raw-sql-query
[token database-id sql]
(client/post (str metabase-url "/api/dataset")
{:headers {"X-Metabase-Session" token}
:content-type :json
:accept :json
:as :json
:body (json/encode
{:type "native"
:native {:query sql}
:database database-id})}))
(defn top-start-stations [token db-id]
(let [sql "SELECT start_station, COUNT(*) AS total_rides
FROM rides
GROUP BY start_station
ORDER BY total_rides DESC
LIMIT 5"
resp (run-raw-sql-query token db-id sql)]
(println (json/encode (:body resp) {:pretty true}))))
(defn -main []
(let [token "YOUR_METABASE_SESSION_TOKEN"
database-id 2] ;; e.g. found in Metabase Admin
(top-start-stations token database-id)))
```
This returns JSON with a `:data` map containing rows and columns. You can transform them into more convenient structures in Clojure for further analysis.
---
## 4. Summary
- **Metabase doesn’t natively “store” CSV data**—it queries external data sources.
- To “skip Postgres,” you can:
- **Install a CSV driver plugin** so Metabase can directly read your CSV file.
- **Load the CSV into a lightweight DB like SQLite** and point Metabase to that file.
- Either way, once connected, you can explore and visualize the Kaggle cycling data in Metabase or query it via the Metabase REST API from Clojure (or any language).
This approach keeps things simple if you’re just prototyping or analyzing a relatively small dataset. For large-scale data or production scenarios, consider using a more robust database for better performance and reliability.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment