Squashql
Official repository of SquashQL, the SQL query engine for multi-dimensional and hierarchical analysis that empowers your SQL database
Install / Use
/learn @squashql/SquashqlREADME
SquashQL is an open-source SQL query engine designed to streamline the process of building multi-dimensional queries. At its core, it acts as a middleware layer that stands between SQL databases and multiple clients or front-end applications. The primary objective is twofold: to empower back-end developers in optimizing SQL database usage and to provide front-end developers with an intuitive API for configuring metrics in the UI.
It follows a "write once, run everywhere" philosophy, making it an excellent solution for scenarios where data needs to be efficiently queried from multiple databases. Currently, SquashQL supports compatibility with databases such as Apache Spark, ClickHouse, BigQuery, Snowflake, PostgreSQL and DuckDB.
💡 Key features
Simple API
Typescript SQL-like query builder available on npm. This empowers developers to construct and execute SQL queries directly from their applications, utilizing a syntax that feels familiar and expressive.
import {
from, avg, TableField
} from "@squashql/squashql-js"
class Orders {
readonly _name: string = "Orders"
readonly customer_id: TableField = new TableField("Orders.customer_id")
readonly amount: TableField = new TableField("Orders.amount")
}
const orders = new Orders()
const q = from(orders._name)
.select([orders.customer_id], [], [avg("average_spends", orders.amount)])
.build()
Pivot Table
Create pivot table in SQL with a simple and intuitive syntax. Totals and subtotals are computed by the database.
const pivotConfig: PivotConfig = {
rows: [sales.product, sales.region],
columns: [sales.month]
}
const query = from(sales._name)
.select([sales.product, sales.region, sales.month], [], [sum("sales", sales.revenue)])
.build()
querier.executePivotQuery(query, pivotConfig)
👉 https://jsfiddle.net/azeq/c6f9ox4u/
Try the tutorial to build your own pivot table.
Bucketing
Bucketing, also known as binning or discretization, is a technique used in data analysis to group continuous or numerical data into discrete intervals or "buckets."
<details><summary>SQL</summary>-- Create a sample employees table
CREATE TABLE employees
(
employee_id INT PRIMARY KEY,
employee_name VARCHAR(255),
salary DECIMAL(10, 2),
age INT
);
-- Insert some sample data
INSERT INTO employees (employee_id, employee_name, salary, age)
VALUES (1, 'John Doe', 50000.00, 50),
(2, 'Jane Smith', 65000.00, 33),
(3, 'Bob Johnson', 80000.00, 42),
(4, 'Alice Brown', 40000.00, 21),
(5, 'Peter Parker', 61000.00, 55),
(6, 'Jack Black', 73000.00, 39),
(7, 'Nicole Williams', 44000.00, 25),
(8, 'Charlie Wilson', 72000.00, 28);
Data:
+-------------+-----------------+---------+-----+
| employee_id | employee_name | salary | age |
+-------------+-----------------+---------+-----+
| 1 | John Doe | 50000.0 | 50 |
| 2 | Jane Smith | 65000.0 | 33 |
| 3 | Bob Johnson | 80000.0 | 42 |
| 4 | Alice Brown | 40000.0 | 21 |
| 5 | Peter Parker | 61000.0 | 55 |
| 6 | Jack Black | 73000.0 | 39 |
| 7 | Nicole Williams | 44000.0 | 25 |
| 8 | Charlie Wilson | 72000.0 | 28 |
+-------------+-----------------+---------+-----+
Easy to bucket on one or several attributes. Result can be displayed in a pivot table to enhance data visualization and help analysis.
<img src="documentation/assets/bucketing-age-salary.png" width="500">👉 https://jsfiddle.net/azeq/r1xfqt89/
Comparison or hierarchical measures
Make calculations that are cumbersome or challenging in SQL easy to perform.
Hierarchical measures are multidimensional measures that are calculated based on the relationships between values in different levels of a hierarchy. This allows for the calculation of percentages, ratios, and other comparisons that provide insights into the distribution and change of data over time or across different levels of a hierarchy.
Hierarchical / Parent-Child comparison
💡 Compare aggregates and sub-aggregates computed at different levels of a lineage. A lineage is defined on-the-fly
at query time. In below example, it is defined as continent_name -> country_name -> city_name.
👉 https://jsfiddle.net/azeq/31qs2jyt/
Time-series comparison
💡 Compare aggregates between time period like year, semester, quarter, month.
<img src="documentation/assets/time-period-comp.png" width="500">👉 https://jsfiddle.net/azeq/dqebkp2x/
Drilling across
Query two or more fact tables and stitch together the results on shared columns. SquashQL will fill aggregated values with null for entries that are not compatible with the result they come from.
Result query 1 Result query 2
+-------------+---------------+ +-------------+-------------+-------------------+
| product | quantity sold | | product | reason | quantity returned |
+-------------+---------------+ +-------------+-------------+-------------------+
| Grand Total | 54 | | Grand Total | Grand Total | 5 |
| A | 15 | | A | Total | 1 |
| B | 23 | | A | defective | 1 |
| C | 16 | | C | Total | 3 |
+-------------+---------------+ | C | unwanted | 3 |
| D | Total | 1 |
| D | unwanted | 1 |
+-------------+-------------+-------------------+
Drilling across result (with left join)
<img src="documentation/assets/drilling-accross-minify.png" width="500">👉 https://jsfiddle.net/azeq/702gxqcb/
N query results can be merged, N >= 2.
Query cache
SquashQL provides an in-memory query cache to not re-execute queries already executed. Caching can be customized or deactivated.
Output format
SquashQL supports several output format :
text/plaintext/csv(withContent-Dispositionheader allowing file download)application/json
The default format for SquashQL is application/json.
To override this behavior and choose the selected output format, simply pass an Accept header when instantiating the querier :
const csvQuerier = new Querier(url, {
headers: {
'Accept': 'text/plain',
}
});
🎓 Try SquashQL
You can try SquashQL directly from your web browser with our showcase project. No need to install anything!
📕 Documentation
- Configuration: learn how to connect SquashQL to your database.
- API tour: a quick introduction to the API.
- Typescript API Handbook
- Drilling across
- Cache
📚 Resources
🤝 Contributing
We are always thrilled to receive contributions: code, documentation, issues, or feedback.
- Start by checking out the GitHub issues. These are a great place for newcomers to contribute.
- Read our Code of Conduct to understand our community standards.
- Read our contributing guidelines to understand how to contribute.
- Create a fork to have your own copy of the repository where you can make changes.
- Once you've made your changes and tested them, you can contribute by submitting a pull request.
If you can't find something you'd want to use or want to share your ideas, please open an issue or join our discord server.
💬 Join Our Community
We welcome everyone to our community! Whether you're contributing code or just saying hello, we'd love to hear from you. Join our discord server!
