Show HN: SQueaLy – Fast track analytics for business
github.com
github.com
Well done.
Can someone comment on what's the benefit of this, as opposed to using parameterization and SQL-injection protections offered by say SQLAlchemy or other similar tools?
There comes a time when you need the power of sql, and an ORM gets in the way. Think unions, sql functions, group by, more complex sql. Think dynamically generated sql queries based on some data structure.
In such cases, you have to keep track of how many variables you are capturing in the query, and then manually bind them.
Jinjasql is just a query generator. At the end of the day, it doesn't actually execute the query. You take the query it generates, and the array of parameters it gives - and then execute it using traditional means using bind parameters.
This approach gives you the power of a template language to generate the query. You can create reusable macros, conditionals and other features that a template language provides.
I just prototyped a very expressive reporting API that takes database driven configurations for the reporting columns (dimensions and metrics), validates inputs and allows very expressive filtering (including partial string matches, etc.) in Flask and SQLAlchemy.
Did not use the ORM part, but rather the query API: http://docs.sqlalchemy.org/en/latest/orm/query.html
Queries included a with statement, the main query had 4 nested subqueries (4 level cascade basically) for ranking, filtering and ordering.
The prototype supports queries like this: Give me best performing shows and their stats in my top 5 countries by the number of viewers I have in each of these top countries, but I want shows not broken down by country, but instead by season and episode, including counts of uniques at each level. (Sorry don't have a copy of the resulting SQL on this computer, but it's fairly long.)
Queries I designed up front, and reverse engineered into being dynamically generated by SQLAlchemy query API. However, by not having a fixed template, I can simply update my reporting table configurations and not have to worry about updating the templates or the query structure.
When the user requests something that does not have top dimension and topN results, I can just omit that part of the query and build valid syntax without it.
All-in-all, the entire API is less than 1k lines of python.
If you have a single complex query that is generating all your reports based on database configuration - I'm guessing you need the full power of python. So while JinjaSQL could have save some effort, I'm not sure it'd save you a lot.
Hope that helps!
The reports are all API driven. User specifies dimensions and metrics, as well as filters, etc. The API returns the JSON response.
I can't predict what users will want to visualize in the future, so dedicated templates for each of the visualizations are not practical for my use case.
The goal is to minimize dev work on the API and let front end config dictate what columns the API returns.
Here's how you could write that in jinjasql. I'm writing this out of memory, and it has errors/issues - but I hope you get the idea.
SELECT
{% for metric in userquery.metrics %}
{{ metric.id }} as '{{metric.description}}'
{% endfor %}
FROM {{ userquery.cube }}
WHERE
{% for filter in userquery.filters %}
{{ filter.key }} {{ filter.operator }} {{ filter.value }}
{% endfor %}
GROUP BY
{% for dimension in userquery.dimensions %}
{{ dimension }}
{% endfor %}
Now, another interesting this in the security aspect. You can also pass the logged in user data model, and then append a where clause that restricts the rows based on the logged in user id or his role or whatever else is your application security model.
I'm not sure how much effort it would have saved you, but it does help us a lot - mostly because we don't have to translate between SQL and the way SQL ALchemy / ORM works.
It's pretty similar to what I did, but the template would be a lot more complex due to various column definitions and calculations, for example uniques require the base query to have the IDs of whatever I'm counting, but all in all I see the use case.
Soon we will be adding support for Mssql and Cassandra also
Thanks kyyd, Periscope Data was definitely on our minds while planning this project. Please share your feedback when you use it.
For now, all you need is a publicly accessible database and a free heroku account. Squealy supports one-click deploy to heroku. You can deploy within minutes and play around with the tool.
Please share your feedback with us once you use it.
Did you do anything to make it work with large datasets? Is the web-frontend still responsive if a query returns 1 million rows for example?
BTW which charting library are you using for your reports?
Most of the recurring reports simply spits out excel or csv files with no graphs.
We haven't had time to make a marketing/demo site for it yet, sales has been through networking so far.
- Cron based scheduled emails embedded with reports
- Auto-generated documentation for all the REST APIs using swagger
- More than just SQL - SQueaLy uses JinjaSQL behind the scenes which gives you power to use JINJA inside the SQL query.
You can refer the readme for more details
Also squealy lets you write Jinja templated SQL queries that's helpful in extracting some complex insights from the data.
Metabase is a general purpose BI tool that anyone in your company can use (whether they know SQL or not) to ask questions about your data, make charts and dashboards, etc.
It sounds like Squealy is primarily focused on generating APIs from SQL for integration in another applications.
That isn't Metabase's primary focus right now, but we do have the ability to embed charts or dashboards in other applications, as well as JSON and CSV APIs for each question (though that's not well documented).
Each question or dashboard can be parameterized, for example by a customer ID, and authenticated via JWT.
This works for questions built using our UI as well as SQL. We don't have a full template system in SQL yet, but we do let you add parameters to your SQL.
If you feel like we're missing a feature that would be useful, please file an issue! https://github.com/metabase/metabase/issues
SQueaLy's approach is a bit different. It is rather focused on writing powerful and dynamic SQL queries. SQueaLy uses JinjaSQL behind the scenes which gives you the power to use JINJA inside the SQL query. For more details, check out JinjaSQL here - https://github.com/hashedin/jinjasql
With JinjaSQL in the scene, you can access real-time variables related to
- A logged-in user
- Web session
- URL query parameters
inside your SQL queries.
You can then use Jinja templating inside the SQL query to handle conditional statements or create macros based on these real time variables
JinjaSQL simply generates a SQL query with an associated array of parameters. It isn't even aware of the SQL query that's written. JinjaSQL would work just fine with any database.
Squealy is a django project, so if you can get the right database driver configured and installed - in theory, squealy would just work fine with MSSQL. After all, the user is writing the query, so squealy doesn't care much about the underlying differences in the way SQL is written.