Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Hashquery, a Python library for defining reusable analysis

hashquery.dev

Post reply on HN
Print view View original

11–19 of 19 posts

Re: Show HN: Hashquery, a Python library for defining reusable analysis

#11

Post by cpimhoff »

demaga wrote: post #5

> way beyond the capabilities of standard SQL Maybe some examples would help > AI and LLMs (coming soon) Why?

> way beyond the capabilities of standard SQL

Maybe the more precise language would be "way beyond the _expressive_ capabilities of standard SQL". Ultimately Hashquery compiles into SQL for execution, in that way it's a bit of a transpiler.

One concrete example is funnel analysis. In SQL, an efficient funnel analysis on large data sets will span several hundred lines of pipelined queries; writing, reading, and re-parameterizing this is very challenging. In Hashquery, it's abstracted into a single function call: `model.funnel(*str[])`.

> AI and LLMs (coming soon)

Frankly? SEO just told us to drop "AI" _somewhere_ on the page for new `.dev` website. I agree it's a little silly to make it a top-line bullet point.

We do think Hashquery is better suited for coding co-pilot tools, as it's a known language, and you can establish API boundaries that an AI tool won't break into and futz with (good for writing queries on top of already defined business logic).

Re: Show HN: Hashquery, a Python library for defining reusable analysis

#12

Post by cpimhoff »

tomrod wrote: post #3

How aware is the library regarding existing structure, e.g foreign key relationships?

The library itself provides tools for declaring data tables, columns, metrics, and relationships, then performing transformations (eg. queries) on top of them.

Reflecting an existing database isn't part of the library, but with the magic of "it's just Python" it's pretty easy to write a function that does so yourself:

    import hashquery as hq
    def reflect_db(url: str) -> list[hq.Model]:
      """
      Given a connection string,
      returns a list of Hashquery models
      for all the physical tables in
      the database.
      """
      models: list[hq.Model] = []

      reflection = some_reflection_lib.reflect_db(url)
      for table in reflection.tables:
        models.append(
          hq.Model()
          .with_source(url, table.name, table.schema)
          .with_attributes(*table.column_names)
        )
        # ...more logic for importing foreign keys or whatever else

      return models

Re: Show HN: Hashquery, a Python library for defining reusable analysis

#13

Post by vndrewlee »

tomrod wrote: post #3

How aware is the library regarding existing structure, e.g foreign key relationships?

Joining logic is defined in our data modeling layer, either from Hashboard (our BI tool) or can also be defined in Hashquery as well instead of replying on your database schema for fk relationships. We went this route since a lot of people are using dbt generated tables.

https://hashquery.dev/docs/pattern_guides/joins/

Re: Show HN: Hashquery, a Python library for defining reusable analysis

#14

Post by cpimhoff »

guy4261 wrote: post #2

Looks pretty darn cool! Two questions please: 1. How does this compare to dbt? If we're already using dbt, why migrate? 2. Will you consider making a tool that tries to transpile SQL back to Hashquery models? This way I can work against my database, then merge the changes back to the model. Good luck!

> 1. How does this compare to dbt? If we're already using dbt, why migrate?

I actually think dbt and Hashquery work very well alongside one another!

dbt can help you normalize, clean, and materialize your data upstream, then Hashquery can be used to associate semantics to those output tables (measurements, synthesized attributes, common views) and query them.

So dbt can be the build/ETL part, and Hashquery can be the semantic layer/frontend for analytical queries.

> 2. Will you consider making a tool that tries to transpile SQL back to Hashquery models?

This is a really interesting idea!

I'm pretty skeptical we could make this technically feasible. Compilation from a higher level abstraction (Hashquery semantics) to a lower level abstraction is inherently lossy and can't really be done in reverse without a lot of noise.

Hashquery has a lot of escape hatches for raw SQL though if you need to access some functionality not yet implemented as part of the project. There's API to inline Hashquery structures inside of SQL fragments and visa versa.

Re: Show HN: Hashquery, a Python library for defining reusable analysis

#15

Post by code_biologist »

I'm exactly the target audience for this type of tool, a tech leader that has implemented a data warehouse and BI strategy. Some concrete tips for adoption:

- Break the dependency on your product. I need to be able to use the library even if your company goes under.

- Add a dbt library that makes it easy to use hashquery within dbt models. It gets you materialization for free and will answer a lot of questions you will get about dbt integration.

To comment more broadly, if you want to be a broad solution, the going trend in data integration seems to be at the warehouse level so you need to have SQL answers.

A bunch of tools all consume from our warehouse (existing BI, reverse ETL, data science systems). A BI definition tool won't work if I can't define segments in a way that all of those can access, even as just tables or views.

Our programmers and data science people know Python and are often very good at SQL, but their time is short and BI projects depending on them have been delayed. Our analysts know SQL, and have the dedicated time to make these projects happen.

This kind of code snippet isn't crazy to put into dbt, and if someone wants to do Python magic in the background they can:

    hq_project.models.events_model.with_activity_schema(
        group='user_id', timestamp='timestamp', event_key='event_type'
    ).funnel("ad_impression", "add_to_cart", "buy").as_sql()

Re: Show HN: Hashquery, a Python library for defining reusable analysis

#16

Post by grantjpowell »

This looks neat. I'm the author of a similar project in typescript we use at Cotera called Era [0]. Y'all might be implement something similar to our caching layer [1] which we think is super useful. Once you have a decent cross warehouse representation it's pretty easy to "split" queries across the real warehouse and something like duckdb. The other thing that we find useful in Era that y'all might like are "Invariants"[2]. Invariants work by compiling lazily evaluated invalid casts into the query that only trigger under failing conditions. We use "invariants" to fail a query at _runtime_, which eliminates TOUTOC problems that come from a DBT tests style solution.

    [0] https://newera.dev/
    [1] https://cotera.co/blog/how-era-brings-last-mile-analytics-to-any-data-warehouse-via-duckdb
    [2] https://newera.dev/docs/invariants

Re: Show HN: Hashquery, a Python library for defining reusable analysis

#17

Post by cpimhoff »

code_biologist wrote: post #15

I'm exactly the target audience for this type of tool, a tech leader that has implemented a data warehouse and BI strategy. Some concrete tips for adoption: - Break the dependency on your product. I need to be able to use the library even if your company goes under. - Add a dbt library that makes it easy to use hashquery within dbt models. It gets you materialization for free and will answer a lot of questions you wi…

All great advice. All this resonated with the team.

100% on decoupling it from the product. Our customers needed a headless BI solution and we needed a better internal framework, so the stars aligned for this first version being a little coupled. This kind of feedback is helpful, because it helps me advocate for capacity to decouple it fully!

Hashquery does have APIs to get the rendered SQL without executing it, so I think integration with any in-warehouse processing tool is possible, dbt likely being the most valuable.

Full materialization/interoperability with SQL is hard for any tool trying to encapsulate the semantics of the SQL. The intent of those tools is to encode concepts that are not possible to represent with static tables, so folding them back to a SQL-surface area will always be lossy. Having said that, we could certainly have a better story around it — materialization can still be useful even if it has some caveats.

Post reply on HN