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

Open Source SQL Parsers

tokern.io

Post reply on HN
Print view View original

51–60 of 103 posts

Re: Open Source SQL Parsers

#51

Post by rzmmm »

vkazanov wrote: post #42
tehlike wrote: post #5

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql I hope in near future databases will come with better query languages...

...and there's more to sql trouble than just troublesome syntax, typical for languages of its generation! 1. Sql is non-modular 2. Non-orthogonal 3. Its parroting of the (beautiful) relational data model is... opinionated at best. The standard is unreadable and not really implementable, one always has to resort to implementation docs. Realistically there are no alternatives, but... sql is no good.

What do you think about using SQL views and functions to make composable, modular SQL code?

Re: Open Source SQL Parsers

#52

Post by 7952 »

zmxz wrote: post #41
tehlike wrote: post #5

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql I hope in near future databases will come with better query languages...

In my case it's the opposite - SQL is perfectly readable and fits my mental model while the project you linked feels awkward and hard to read. However, it's good to have options, I wonder if prql will get traction. It would be great if it did.

A lot of the time people's mental model will be derived from the language though.

Re: Open Source SQL Parsers

#53

Post by zasdffaa »

madebyherzblut wrote: post #50
anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

Slightly off-topic, but I appreciate any pointers: In a project, I want to offer an SQL interface for data analysts similar to Stripe Sigma[1]. The tricky thing is not to parse the query but to map the public schema to the private schema, add authorization, and distribute the query across data stores. So, I am looking for a customizable query parser, planner, and execution engine. I briefly looked into Apache Calcite…

> So, I am looking for a customizable query parser, planner, and execution engine

That looks like an entire DB engine, isn't it?

> map the public schema to the private schema

sounds like view.

It seems like a pretty major project as you describe it.

Re: Open Source SQL Parsers

#54

Post by haliax »

madebyherzblut wrote: post #50
anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

Slightly off-topic, but I appreciate any pointers: In a project, I want to offer an SQL interface for data analysts similar to Stripe Sigma[1]. The tricky thing is not to parse the query but to map the public schema to the private schema, add authorization, and distribute the query across data stores. So, I am looking for a customizable query parser, planner, and execution engine. I briefly looked into Apache Calcite…

For a similar use case, I’ve been considering a combination of s3 exports of db views (mapping private schema to public) + lakeformation governed tables (which allow table, row, and cell level security via iam) + redshift or athena for querying + sts/cognito for authorization to give logged in users a temporary access key id and secret access key. Admittedly an AWS heavy setup, but in my use case that’s an advantage :)

Re: Open Source SQL Parsers

#55

Post by tbrownaw »

rzmmm wrote: post #51
vkazanov wrote: post #42

Earlier quoted context omitted.

...and there's more to sql trouble than just troublesome syntax, typical for languages of its generation! 1. Sql is non-modular 2. Non-orthogonal 3. Its parroting of the (beautiful) relational data model is... opinionated at best. The standard is unreadable and not really implementable, one always has to resort to implementation docs. Realistically there are no alternatives, but... sql is no good.

What do you think about using SQL views and functions to make composable, modular SQL code?

It doesn't work very well.

Table Out1 has translated versions of fields AA..AZ from table In1.

Table Out2 has translated versions of fields BA..BZ from table In1.

table Out3 has translated versions of fields X..Z from table In2, and the translation depends on some of the AA..BZ fields, in both translated and untranslated forms.

We'd end up with views that depended on other views and after expanding had like a dozen self-joins to add more fields that weren't included in the base view. And views that were selecting a big list of columns unchanged, plus adding a couple.

But somehow despite being an absolute ^%#@!^&$! it still worked better than the pointy-clicky ETL tooling we'd been using previously. Mostly because of the run->debug->modify cycle time being faster, and only a little because of avoiding pointy-clicky stuff.

Re: Open Source SQL Parsers

#56

Post by conaclos »

SQL is a mess. It is difficult to find a parser that fulfills your needs.

A month ago, I needed a parser to parse SQLite schemas. SQLite has some edge case and I needed a lossless parsing to detect these edge cases.

I finally wrote my own parser [1] to fullfill my needs and to have some fun. This is for a POC project.

[1] https://github.com/coast-team/sqlschm

Re: Open Source SQL Parsers

#57

Post by cryptonector »

anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

Here's a few:

  - syntax highlighting
  - algebraic manipulations
  - optimization analysis of queries seen
  - analysis of equivalence of queries
  - query rewriting (for style, performance, etc.)
  - RDBMS portability layer (implement a common
    subset of SQL, port queries to different
    engines; see previous item)
I can probably think of more.

Re: Open Source SQL Parsers

#58

Post by ErwinSmout »

zasdffaa wrote: post #53
madebyherzblut wrote: post #50

Earlier quoted context omitted.

Slightly off-topic, but I appreciate any pointers: In a project, I want to offer an SQL interface for data analysts similar to Stripe Sigma[1]. The tricky thing is not to parse the query but to map the public schema to the private schema, add authorization, and distribute the query across data stores. So, I am looking for a customizable query parser, planner, and execution engine. I briefly looked into Apache Calcite…

> So, I am looking for a customizable query parser, planner, and execution engine That looks like an entire DB engine, isn't it? > map the public schema to the private schema sounds like view. It seems like a pretty major project as you describe it.

And if you're set on building an "entire DB engine" then you can just as well go the full mile and go for relational. As opposed to SQL.

Re: Open Source SQL Parsers

#59

Post by cryptonector »

conaclos wrote: post #56

SQL is a mess. It is difficult to find a parser that fulfills your needs. A month ago, I needed a parser to parse SQLite schemas. SQLite has some edge case and I needed a lossless parsing to detect these edge cases. I finally wrote my own parser [1] to fullfill my needs and to have some fun. This is for a POC project. [1] https://github.com/coast-team/sqlschm

If I needed to parse SQLite3 schemas, I might just fork the Lemon grammar for SQLite3 from SQLite3 and make it produce an AST.

Re: Open Source SQL Parsers

#60

Post by cryptonector »

tehlike wrote: post #5

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql I hope in near future databases will come with better query languages...

I don't mind SQL, really, but I would prefer a query construction API that has the full power of SQL (and more even). At the very least such a thing would not have a SQL injection problem, but also could be used to generate ASTs.

  Query q = db.from("foo").join("bar").using("id")
    .select("id","foothing","barthing");
However, the moment you want any non-trivial SQL expression, such an API becomes unwieldy. And yet the need for non-trivial SQL expressions in queries can't be avoided, nor can it be left to the host language.

Also, I'd really like a SQL mode where no literal constants are allowed, as a mechanism to force the use of query parameters and prevent SQL injection.

Post reply on HN