ankane / blazer

Blazer 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 Features • **Multiple data sources** - PostgreSQL, MySQL, Redshift, and many more • **Variables** - run the same queries with different values • **Checks & alerts** - get emailed when bad data appears • **Audits** - all queries are tracked • **Security** - works with your authentication system Docs • Installation • Queries • Charts • Dashboards • Checks • Cohorts • Anomaly Detection • Forecasting • Uploads • Data Sources • Query Permissions Installation Add this line to your application’s Gemfile: Run: And mount the dashboard in your : For production, specify your 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. Checks (optional) Be sure to set a host in for emails to work. 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. You can also set up failing checks to be sent once a day (or whatever you prefer). Here’s what it looks like with cron. For Solid Queue, update . For Slack notifications, create an incoming webhook and set: Name the webhook “Blazer” and add a cool icon. Authentication Don’t forget to protect the dashboard in production. Basic Authentication Set the following variables in your environment or an initializer. Devise Other Specify a method to run in . You can define this method in your . Be sure to render or redirect for unauthorized users. Permissions PostgreSQL Create a user with read-only permissions: MySQL and MariaDB Create a user with read-only permissions: Sensitive Data If your database contains sensitive or personal data, check out Hypershield to shield it. Encrypted Data If you need to search encrypted data, use blind indexing. You can have Blazer transform specific variables with: Queries Variables Create queries with variables. Use and for time ranges. Example Smart Variables Example Suppose you have the query: Instead of remembering each occupation’s id, users can select occupations by name. Add a smart variable in with: 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. Linked Columns 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 . Smart Columns Example - occupation_id column Suppose you have the query: See which city the user belongs to without a join. You can also use a hash for static data and enums. Caching Blazer can automatically cache results to improve speed. It can cache slow queries: Or it can cache all queries: Of course, you can force a refresh at any time. Charts Blazer will automatically generate charts based on the types of the columns returned in your query. **Note:** The order of columns matters. Line Chart There are two ways to generate line charts. 2+ columns - timestamp, numeric(s) - Example 3 columns - timestamp, string, numeric - Example Column Chart There are also two ways to generate column charts. 2+ columns - string, numeric(s) - Example 3 columns - string, string, numeric - Example Scatter Chart 2 columns - both numeric - Example Pie Chart 2 columns - string, numeric - and last column named - Example Maps Columns named and or and or and - Example or a column named To enable, get an access token from Mapbox and set . Targets Use the column name to draw a line for goals. Example Dashboards 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 Checks give you a centralized place to see the health of your data. Example Create a query to identify bad rows. 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. Cohorts Create a cohort analysis from a simple SQL query. Example Create a query with the comment . The result should have columns named and and optionally . You can generate cohorts from the first conversion time: (the first conversion isn’t counted in the first time period with this format) Or from another time, like sign up: This feature requires PostgreSQL or MySQL 8. Anomaly Detection Blazer supports three different approaches to anomaly detection. Prophet Add prophet-rb to your Gemfile: And add to : Trend Add trend to your Gemfile: Set the URL to the API in an initializer: And add to : AnomalyDetection.rb Add anomaly_detection to your Gemfile: And add to : Forecasting Blazer supports for two different forecasting methods. Example A forecast link will appear for queries that return 2 columns with types timestamp and numeric. Prophet Add prophet-rb to your Gemfile: And add to : Trend Add trend to your Gemfile: Set the URL to the API in an initializer: And add to : Uploads Create database tables from CSV files. Example Run: And add to : This feature requires PostgreSQL. Create a new schema just for uploads. Data Sources Blazer supports multiple data sources :tada: Add additional data sources in : Full List • Amazon Athena • Amazon Redshift • Apache Drill • Apache Hive • Apache Ignite • Apache Spark • Cassandra • Druid • Elasticsearch • Google BigQuery • IBM DB2 and Informix • InfluxDB • MySQL and MariaDB • Neo4j • OpenSearch • Oracle • PostgreSQL • Presto and Trino • Salesforce • Snowflake • Socrata Open Data API (SODA) • SQLite • SQL Server…