Sep 28, 2022
As Metabase puts it:
Business intelligence for everyone Team doesn’t speak SQL? No problem. Metabase is the easy, open-source way to help everyone in your company work with data like an analyst.
The open source data warehouse built on Postgres
Hydra is the easy way for engineers to centralize, organize, and analyze data. We make data-driven decisions accessible.
Make data-driven decisions with an open-source modern data stack built with Hydra and Metabase.
Run Metabase and Hydra using Docker Compose
Copy the following docker-compose.yaml file to your local machine.
version: '3.9' services: postgres: image: postgres:14 restart: always volumes: - pg_data:/var/lib/postgresql/data environment: POSTGRES_USER: &metabase_user metabase POSTGRES_PASSWORD: &metabase_password metabasepw POSTGRES_DB: &metabase_db metabase hydra: platform: linux/amd64 image: ghcr.io/hydrasdb/hydra restart: always volumes: - hydra_data:/home/postgres/pgdata environment: PGPASSWORD_SUPERUSER: hydra ports: - "6432:5432" metabase: image: metabase/metabase:v0.44.2 ports: - "3000:3000" depends_on: - postgres - hydra restart: always environment: MB_DB_TYPE: postgres MB_DB_USER: *metabase_user MB_DB_PASS: *metabase_password MB_DB_DBNAME: *metabase_db MB_DB_HOST: postgres MB_DB_POST: 5432 volumes: pg_data: hydra_data:
This compose file defines three services: Metabase, a Hydra instance, and an isolated Postgres instance for storing Metabase’s metadata.
Seed some sample data
Now, let’s start by creating a sample database in Hydra and populate it with some sample data.
In a terminal from the folder where you’ve saved the docker-compose.yaml file:
Start Hydra with docker compose up hydra
Wait for the Hydra instance to complete first-time provisioning by looking for the log line initialized a new cluster
In another terminal let’s create a sample database and seed some data. You will be prompted for the Hydra password twice. It is (as configured in compose) hydra.
createdb -h localhost -p 6432 -U postgres sample_data psql -h localhost -p 6432 -U postgres -d sample_data \ -c "CREATE TABLE data (id uuid NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(), sample integer, timestamp timestamptz) USING columnar;" \ -c "INSERT INTO data (sample, timestamp) SELECT floor((random() + random() + random() + random() + random() + random()) / 6 * 100000000)::int, to_timestamp(EXTRACT(epoch from NOW()) - floor(random() * 2600000)) FROM generate_series(1, 50000);"
Now stop Hydra temporarily by sending control-c to the terminal running Hydra.
We’ve created a database named sample_data with a columnar table data with 50,000 random samples spread out over the last month.
Metabase first launch
Now we can start Metabase and configure it to access our sample data in the Hydra instance.
Start all of the services defined by compose: docker compose up
Wait for Metabase to complete its first-time set up. Wait until you see the log line: INFO metabase.core :: Metabase Initialization COMPLETE. This may take a few minutes.
You can now access Metabase in your browser at localhost:3000
Proceed to get started with Metabase by creating your Metabase user. Then add your Hydra instance as defined in the below screenshot. The Hydra password is hydra.
Once you’ve landed at the Metabase dashboard you should see some options to view insights after a few seconds. Take some time to explore Metabase, for example “a glance at Data”
Metabase: Queries, Questions and Dashboards
Now let’s combine the features of Metabase and Hydra to explore our sample data.
You can use Metabase to run ad hoc queries against Hydra. You can access it via New → SQL query.
SELECT MIN(sample), MAX(sample), COUNT(sample), AVG(sample), STDDEV(sample) FROM data;
For example we can run some summary statistics against our sample data using:
Metabase allows you to use questions to get answers from your data. Let’s create some questions now, that we’ll later turn into a dashboard. You can create questions via New → Question.
Let’s create four questions:
Number of samples over the last 7 days
Average values of samples over the last 7 days
Average value of samples per day over the last 30 days
Distribution of Samples over the last 30 days
Now let’s combine those questions into a dashboard to give our users at a glance access to these metrics.
Create a new dashboard via New → Dashboard
Add the four questions created above to the dashboard
Once you’re done exploring Metabase and Hydra you can stop docker compose by using control-c to shutdown the containers. To remove the containers use docker compose down and to remove the containers and cleanup Metabase’s metadata and the sample data in Hydra use docker compose down -v.
We explored how to make data-driven decisions with an open-source modern data stack built with Hydra and Metabase.
Make sure to follow us on Twitter, LinkedIn, or Discord to get notified about future blog posts!