Sq.io: jq for databases and more • Hacker News Board

Re: Sq.io: jq for databases and more

#121

Post by WhitneyLand » Mon, Oct 07, 2024, 4:48 PM UTC

remon wrote: post #49

dewey wrote: post #48

Sometimes I wonder if it wouldn't be more efficient for people to just learn SQL instead of trying to build tools or layers on top of it that introduce more complexities and are harder to search for.

HN is inundated with posts announcing paper thin abstractions on top of existing technology or utilities that just move the goalpost of what you knowledge you need to be effective. It's a weird trend that seems almost entirely motivated by people wanting open source projects in their resume, or seek funding if its a startup.

What a cynical take. Most people working on side projects do so because they find them interesting or useful rather than to just score resume points.

They’re not moving anyone’s goalposts all usage is voluntary.

The fact that HN is mentioned is also confusing. If something had no value beyond paper thin abstraction I doubt we’d be seeing it on the front page with 500 votes.

Even if this project turned out to be less than hoped it seems counterproductive to complain about people creating and exploring. It’s a good thing to see and natural selection will do any sorting needed.

Re: Sq.io: jq for databases and more

#122

Post by anarazel » Mon, Oct 07, 2024, 4:50 PM UTC

setr wrote: post #117

jeltz wrote: post #66

Earlier quoted context omitted.

The issue with the abstractions over SQL is that while they fix some problems they always introduce a bunch of new problems so in the end SQL is still preferable. I have yet to see an example where that is not the case.

The fundamental issue is that they have to generate SQL at the end of the day, so there’s a hard limit on how much you can really change. I don’t know why every database treats SQL as the only API, even Postgres. Even the extension systems, which have the opportunity to hook directly into DB internals (and has no standardization to bother meeting) end up with SQL as the API to do actual db operations.

> Even the extension systems, which have the opportunity to hook directly into DB internals (and has no standardization to bother meeting) end up with SQL as the API to do actual db operations.

FWIW, nothing forces an extension to do so. I'm pretty sure there are several that do DML using lower level primitives.

Re: Sq.io: jq for databases and more

#123

Post by beembeem » Mon, Oct 07, 2024, 8:26 PM UTC

jasongill wrote: post #18

This is interesting. I wonder if there is anything that does the opposite - takes JSON input and allows you to query it with SQL syntax (which would be more appealing to an old-timer like me)

let me introduce you to this hot, open source, nosql database that's webscale...

Re: Sq.io: jq for databases and more

#124

Post by snthpy » Mon, Oct 07, 2024, 9:31 PM UTC

VMG wrote: post #114

pie_flavor wrote: post #86

Earlier quoted context omitted.

Newline and pipe mean the same thing and are interchangeable.

nice - are there any PRQL CLI tools for json?

Not directly, but DuckDB, ClickHouse, and GlareDB all support PRQL and they each have CLIs.

In a previous HN comment [1] I showed how you can leverage PRQL to make your RDBM's JSON functionality more ergonomic. For example

    ```sh
    > prqlc compile  s"""{obj} -> {path}"""
    let getstr = path obj -> s"""{obj} ->> {path}"""
    let extract = obj path -> s"""json_extract({obj}, {path})"""
    
    from [{data='{"duck": [1, 2, 3]}'}]
    select { (data | get '$.duck[0]'), (data | getstr '$.duck[1]'), extract data '$.duck[2]'}
    EOF
    ┌───────────────────────┬──────────────────────────┬───────────────────────────────────┐
    │ "data" -> '$.duck[0]' │ ("data" ->> '$.duck[1]') │ json_extract("data", '$.duck[2]') │
    │         json          │         varchar          │               json                │
    ├───────────────────────┼──────────────────────────┼───────────────────────────────────┤
    │ 1                     │ 2                        │ 3                                 │
    └───────────────────────┴──────────────────────────┴───────────────────────────────────┘
    ```

Re: Sq.io: jq for databases and more

#125

Post by snthpy » Mon, Oct 07, 2024, 9:38 PM UTC

pkkm wrote: post #39

VMG wrote: post #37

Earlier quoted context omitted.

What I love about `jq` that I can edit my query (or "program") by appending tokens at the end. Similar to unix pipes. With plain SQL that is not easy.

Sounds like PRQL [1]. [1] https://prql-lang.org/

Some time ago I wrote pq (https://github.com/prql/prql-query) which aimed to be a simple CLI for PRQL to wrangle data on the command line, much like sq.

Unfortunately I haven't had time to maintain it so it is now archived and out of date. I hope that I might get a chance to update it again. More has happened since then and there are low hanging fruit to make it more usable, for example adding connector_arrow (https://github.com/aljazerzen/connector_arrow) support for other databases, etc...

Quick example of how things looked with pq:

    ```sh
    $ pq --from i=invoices.csv "from i | take 5"
    +------------+-------------+-------------------------------+-------------------------+--------------+---------------+-----------------+---------------------+-------+
    | invoice_id | customer_id | invoice_date                  | billing_address         | billing_city | billing_state | billing_country | billing_postal_code | total |
    +------------+-------------+-------------------------------+-------------------------+--------------+---------------+-----------------+---------------------+-------+
    | 1          | 2           | 2009-01-01T00:00:00.000000000 | Theodor-Heuss-Straße 34 | Stuttgart    |               | Germany         | 70174               | 1.98  |
    | 2          | 4           | 2009-01-02T00:00:00.000000000 | Ullevålsveien 14        | Oslo         |               | Norway          | 0171                | 3.96  |
    | 3          | 8           | 2009-01-03T00:00:00.000000000 | Grétrystraat 63         | Brussels     |               | Belgium         | 1000                | 5.94  |
    | 4          | 14          | 2009-01-06T00:00:00.000000000 | 8210 111 ST NW          | Edmonton     | AB            | Canada          | T6G 2C7             | 8.91  |
    | 5          | 23          | 2009-01-11T00:00:00.000000000 | 69 Salem Street         | Boston       | MA            | USA             | 2113                | 13.86 |
    +------------+-------------+-------------------------------+-------------------------+--------------+---------------+-----------------+---------------------+-------+
    $ # When there is only one input table then this automatically becomes the source relation, i.e. `from i | ` is prepended to the query
    $ # so this can be simplified to:
    $ pq --from invoices.csv "take 5"
    ...
    ```

Re: Sq.io: jq for databases and more

#126

Post by snthpy » Mon, Oct 07, 2024, 9:50 PM UTC

kitd wrote: post #34

ramraj07 wrote: post #31

Earlier quoted context omitted.

Why is this better than DuckDB?

Why is DuckDB better than clickhouse-local?

duckdb is shorter to type than clickhouse-local and at the command line brevity is king! Of course the winner here is chdb! (And don't talk to me about shell aliases) :-p

While on the topic, how exactly does chdb relate to clickhouse-local?

Re: Sq.io: jq for databases and more

#127

Post by snthpy » Mon, Oct 07, 2024, 9:55 PM UTC

jasongill wrote: post #18

This is interesting. I wonder if there is anything that does the opposite - takes JSON input and allows you to query it with SQL syntax (which would be more appealing to an old-timer like me)

This question has come up a few times in this thread. However I don't see how people expect this to be possible unless they are talking about ndjson with flat records. JSON in general is a very nested format so languages based on relational algebra/calculus like SQL and PRQL are not going to be that useful unless the data is flattened and normalised first.

Re: Sq.io: jq for databases and more

#128

Post by mattbrewsbytes » Mon, Oct 07, 2024, 10:54 PM UTC

neilotoole wrote: post #93

mattbrewsbytes wrote: post #84

Earlier quoted context omitted.

The sq and jq tools are both neat command line gimmicks but in my workflow their usefulness is very short. I can't imagine using sq on a query involving a handful of tables and some inner/outer joins. How would I know its outputting the correct SQL? If you mess up joins you end up with bad output. Here's my theory: some developers see simple languages that are easy to learn and want to build something more complex to…

> How would I know its outputting the correct SQL? If you mess up joins you end up with bad output. The generated SQL is output in sq's logs, when logging is enabled. https://sq.io/docs/config#logging

If I have to go look at log files to see if the intermediary tool is doing the right thing, I might as well just write the SQL myself, right? jq has more use cases dealing with JSON that could come in handy. sq seems to handle trivial use cases but for complicated data models its going to be a hindrance.

Re: Sq.io: jq for databases and more

#129

Post by zmmmmm » Mon, Oct 07, 2024, 11:42 PM UTC

dewey wrote: post #56

zmmmmm wrote: post #54

Earlier quoted context omitted.

I think it'd be a moot point if SQL wasn't painful and awkward to work with in the first place. But database purists control it and won't let go, so we will have to live with everyone else inventing layers to make their lives easier.

For me this feels like the complaints about error handling in Go. People who work with it all the time, don't even think about it past the first week. If you are starting out it might bother people because they are not used to it. Personally I really like working with SQL and find it quite elegant, I always encourage people to use it as it really is a job-superpower if you can just dig up issues directly in the DB qu…

Take the simple example of joining via foreign key to another table in a parent child relationship. SQL makes you redundantly specify the other table even though it is fully defined by the foreign key relationship. Purists will say "what if you actually want to join on some other column" or "what if there are multiple foreign key relationships" and a plethora of other "what ifs" and all these ignore the reality that 99.99% of the time it is completely unambiguous and they are just happy that millions of developers all around the world are uselessly typing redundant terms into queries. This is what leads people to eventually say "screw it, I'm scratching that itch" and we have "yet another abstraction layer".

Re: Sq.io: jq for databases and more

#130

Post by novoreorx » Tue, Oct 08, 2024, 8:58 AM UTC

I really like the idea of https://github.com/dinedal/textql, which uses SQL to interact with file-based data stores. However, I don't understand why sq does the opposite—using a new DSL to access a database that already has a widely-adopted and easy-to-use language: good old SQL.