Business intelligence made simple
Explore your data with SQL. Easily create charts and dashboards, and share them with your team.
Try it out Blazer is also available as a Docker image.
:tangerine: Battle-tested at Instacart
Add this line to your application’s Gemfile:
gem "blazer"
Run:
rails generate blazer:install
rails db:migrate
And mount the dashboard in your config/routes.rb:
mount Blazer::Engine, at: "blazer"
For production, specify your database:
ENV["BLAZER_DATABASE_URL"] = "postgres://user:password@hostname:5432/database"
When possible, Blazer tries to protect against queries which modify data by running each query in a transaction and rolling it back, but a safer approach is to use a read-only user. See how to create one.
Be sure to set a host in config/environments/production.rb for emails to work.
config.action_mailer.default_url_options = {host: "blazer.dokkuapp.com"}
Schedule checks to run (with cron, Solid Queue, Heroku Scheduler, etc). The default options are every 5 minutes, 1 hour, or 1 day, which you can customize. For each of these options, set up a task to run.
rake blazer:run_checks SCHEDULE="5 minutes"
rake blazer:run_checks SCHEDULE="1 hour"
rake blazer:run_checks SCHEDULE="1 day"
You can also set up failing checks to be sent once a day (or whatever you prefer).
rake blazer:send_failing_checks
Here’s what it looks like with cron.
*/5 * * * * rake blazer:run_checks SCHEDULE="5 minutes"
0 * * * * rake blazer:run_checks SCHEDULE="1 hour"
30 7 * * * rake blazer:run_checks SCHEDULE="1 day"
0 8 * * * rake blazer:send_failing_checks
For Solid Queue, update config/recurring.yml.
production:
blazer_run_checks_5_minutes:
command: "Blazer.run_checks(schedule: '5 minutes')"
schedule: every 5 minutes
blazer_run_checks_1_hour:
command: "Blazer.run_checks(schedule: '1 hour')"
schedule: every hour
blazer_run_checks_1_day:
command: "Blazer.run_checks(schedule: '1 day')"
schedule: every day at 7:30am
blazer_send_failing_checks:
command: "Blazer.send_failing_checks"
schedule: every day at 8am
For Slack notifications, create an incoming webhook and set:
BLAZER_SLACK_WEBHOOK_URL=https://hooks.slack.com/...
Name the webhook “Blazer” and add a cool icon.
Don’t forget to protect the dashboard in production.
Set the following variables in your environment or an initializer.
ENV["BLAZER_USERNAME"] = "andrew"
ENV["BLAZER_PASSWORD"] = "secret"
authenticate :user, ->(user) { user.admin? } do
mount Blazer::Engine, at: "blazer"
end
Specify a before_action method to run in config/blazer.yml.
before_action_method: require_admin
You can define this method in your ApplicationController.
def require_admin
# depending on your auth, something like...
redirect_to main_app.root_path unless current_user && current_user.admin?
end
Be sure to render or redirect for unauthorized users.
Create a user with read-only permissions:
BEGIN;
CREATE ROLE blazer LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE dbname TO blazer;
GRANT USAGE ON SCHEMA public TO blazer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO blazer;
ALTER DEFAULT PRIVILEGES FOR ROLE migrations_role IN SCHEMA public GRANT SELECT ON TABLES TO blazer;
COMMIT;
Create a user with read-only permissions:
CREATE USER 'blazer'@'127.0.0.1' IDENTIFIED BY 'secret';
GRANT SELECT, SHOW VIEW ON dbname.* TO 'blazer'@'127.0.0.1';
FLUSH PRIVILEGES;
If your database contains sensitive or personal data, check out Hypershield to shield it.
If you need to search encrypted data, use blind indexing.
You can have Blazer transform specific variables with:
Blazer.transform_variable = lambda do |name, value|
value = User.generate_email_bidx(value) if name == "email_bidx"
value
end
Create queries with variables.
SELECT * FROM users WHERE gender = {gender}
Use {start_time} and {end_time} for time ranges. Example
SELECT * FROM ratings WHERE rated_at >= {start_time} AND rated_at <= {end_time}
Suppose you have the query:
SELECT * FROM users WHERE occupation_id = {occupation_id}
Instead of remembering each occupation’s id, users can select occupations by name.
Add a smart variable in config/blazer.yml with:
smart_variables:
occupation_id: "SELECT id, name FROM occupations ORDER BY name ASC"
The first column is the value of the variable, and the second column is the label.
You can also use an array or hash for static data and enums.
smart_variables:
period: ["day", "week", "month"]
status: {0: "Active", 1: "Archived"}
Example - title column
Link results to other pages in your apps or around the web. Specify a column name and where it should link to. You can use the value of the result with {value}.
linked_columns:
user_id: "/admin/users/{value}"
ip_address: "https://www.infosniper.net/index.php?ip_address={value}"
Example - occupation_id column
Suppose you have the query:
SELECT name, city_id FROM users
See which city the user belongs to without a join.
smart_columns:
city_id: "SELECT id, name FROM cities WHERE id IN {value}"
You can also use a hash for static data and enums.
smart_columns:
status: {0: "Active", 1: "Archived"}
Blazer can automatically cache results to improve speed. It can cache slow queries:
cache:
mode: slow
expires_in: 60 # min
slow_threshold: 15 # sec
Or it can cache all queries:
cache:
mode: all
expires_in: 60 # min
Of course, you can force a refresh at any time.
Blazer will automatically generate charts based on the types of the columns returned in your query.
Note: The order of columns matters.
There are two ways to generate line charts.
2+ columns - timestamp, numeric(s) - Example
SELECT date_trunc('week', created_at), COUNT(*) FROM users GROUP BY 1
3 columns - timestamp, string, numeric - Example
SELECT date_trunc('week', created_at), gender, COUNT(*) FROM users GROUP BY 1, 2
There are also two ways to generate column charts.
2+ columns - string, numeric(s) - Example
SELECT gender, COUNT(*) FROM users GROUP BY 1
3 columns - string, string, numeric - Example
SELECT gender, zip_code, COUNT(*) FROM users GROUP BY 1, 2
2 columns - both numeric - Example
SELECT x, y FROM table
2 columns - string, numeric - and last column named pie - Example
SELECT gender, COUNT(*) AS pie FROM users GROUP BY 1
Columns named latitude and longitude or lat and lon or lat and lng - Example
SELECT name, latitude, longitude FROM cities
or a column named geojson
SELECT name, geojson FROM counties
To enable, get an access token from Mapbox and set ENV["MAPBOX_ACCESS_TOKEN"].
Use the column name target to draw a line for goals. Example
SELECT date_trunc('week', created_at), COUNT(*) AS new_users, 100000 AS target FROM users GROUP BY 1
Create a dashboard with multiple queries. Example
If the query has a chart, the chart is shown. Otherwise, you’ll see a table.
If any queries have variables, they will show up on the dashboard.
Checks give you a centralized place to see the health of your data. Example
Create a query to identify bad rows.
SELECT * FROM ratings WHERE user_id IS NULL /* all ratings should have a user */
Then create check with optional emails if you want to be notified. Emails are sent when a check starts failing, and when it starts passing again.
Create a cohort analysis from a simple SQL query. Example
Create a query with the comment /* cohort analysis */. The result should have columns named user_id and conversion_time and optionally cohort_time.
You can generate cohorts from the first conversion time:
/* cohort analysis */
SELECT user_id, created_at AS conversion_time FROM orders
(the first conversion isn’t counted in the first time period with this format)
Or from another time, like sign up:
/* cohort analysis */
SELECT users.id AS user_id, orders.created_at AS conversion_time, users.created_at AS cohort_time
FROM users LEFT JOIN orders ON orders.user_id = users.id
This feature requires PostgreSQL or MySQL 8.
Blazer supports three different approaches to anomaly detection.
Add prophet-rb to your Gemfile:
gem "prophet-rb"
And add to config/blazer.yml:
anomaly_checks: prophet
Add trend to your Gemfile:
gem "trend"
Set the URL to the API in an initializer:
Trend.url = "http://localhost:8000"
And add to config/blazer.yml:
anomaly_checks: trend
Add anomaly_detection to your Gemfile:
gem "anomaly_detection"
And add to config/blazer.yml:
anomaly_checks: anomaly_detection
Blazer supports for two different forecasting methods.
A forecast link will appear for queries that return 2 columns with types timestamp and numeric.
Add prophet-rb to your Gemfile:
gem "prophet-rb", ">= 0.2.1"
And add to config/blazer.yml:
forecasting: prophet
Add [trend](https://github.com/
No open issues yet, or sync has not completed.