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

Stochastic gradient descent written in SQL

maxhalford.github.io

Post reply on HN
Print view View original

61–70 of 187 posts

Re: Stochastic gradient descent written in SQL

#61

Post by 62951413 »

jackschultz wrote: post #13

>A machine learning algorithm which can be trained using SQL opens a world of possibilities. The model and the data live in the same space. This is as simple as it gets in terms of architecture. Basically, you only need a database which runs SQL. First paragraph of the conclusion, and this very much fits with the mindset that's been growing in me in the data world over the past few years. Databases are much more powe…

Junior developers like me were uncomfortable with SQL twenty years ago. Java ORM frameworks became popular because of the Object-Relational impedance. I kind of see the same kind of sentiment nowadays among newer generations but in Python&Co.

The success of the Apache Spark engine can at least partially be attributed to

* being able to have the same expressive power as SQL but with a real Scala API (including having reusable libraries based on it)

* being able to embed it into unit tests at a low price of additional ~20 seconds latency to spin up a local Spark master

Re: Stochastic gradient descent written in SQL

#62

Post by adeelk93 »

ISL wrote: post #33
jackschultz wrote: post #13

>A machine learning algorithm which can be trained using SQL opens a world of possibilities. The model and the data live in the same space. This is as simple as it gets in terms of architecture. Basically, you only need a database which runs SQL. First paragraph of the conclusion, and this very much fits with the mindset that's been growing in me in the data world over the past few years. Databases are much more powe…

The challenge I, with limited knowledge, see with developing detailed algorithms in SQL is a lack of good testing, abstraction, and review tooling. Similarly for a lot of the user-defined-functions for the larger data warehouses (redshift, bigQuery, etc.) dbt solves a lot of it, but I'd love to learn more about good resources for building reliable and readily-readable SQL algorithms for complex logic.

I've supported 3 different models over the years with inference implemented in SQL. First one I inherited, loved it so much that I implemented it twice again. Amazingly fast for TBs of data and no waiting on other tech teams.

That tooling you're describing is definitely not there. Bigquery has BQML but it's very much in its infancy. I tend to do all the modeling in Python/R on sampled data and then deploy to SQL.

Re: Stochastic gradient descent written in SQL

#63

Post by simonw »

naasking wrote: post #32
noloblo wrote: post #19

Earlier quoted context omitted.

+1 sql is extremely elegant composable and is under rated Postgres is very powerful. While I sought a short detour in nosql Mongodb land now back to Mysql Postgresql sql territory and glad for it Being able to generate views is and stored procedures is useful as well.having sql Take over more like ml, gradient descent does open up good possibility. Also since sql is declarative it Makes it so it's rather easier than…

SQL has some positives but it is not composable. At all. This is because relations are not first-class values in SQL.

CTEs go a long way to making SQL more composable.

Re: Stochastic gradient descent written in SQL

#64

Post by naasking »

dventimi wrote: post #45
naasking wrote: post #32

Earlier quoted context omitted.

SQL has some positives but it is not composable. At all. This is because relations are not first-class values in SQL.

Is a query not a relation?

Basically, but queries are not first class in SQL. You can't assign a query to a variable, or pass it as a parameter to a stored procedure, for example. This would make SQL composable:

    declare @x = (select * from Person)
    select Name from @x where Birthdate

Re: Stochastic gradient descent written in SQL

#65

Post by simonw »

I tried to replicate this in SQLite. The first few steps worked OK, e.g.

https://lite.datasette.io/?json=https://gist.github.com/simo...

(I replaced "figures" with "raw" due to the way Datasette Lite assigns a default table name to the imported JSON)

But the more complex recursive queries gave me this error and I'm not sure how to work around it:

    recursive reference in a subquery: state
E.g. https://lite.datasette.io/?json=https://gist.github.com/simo...

Re: Stochastic gradient descent written in SQL

#66

Post by syats »

dventimi wrote: post #49
syats wrote: post #42

Just don't. SQL: - does not allow for easy and clean importing of modules/libraries - is not easily to write tests for - has limited support for a debugger - lacks a consistent style for such large queries (plus most textbook cover fairly simple stuff) which means it's hard for a developer to start reading someone else's code (more than in other languages) - clearly indicates in its name that it is a Query language.…

-PostgreSQL extensions are easy to include and use. -pgTAP exists for testing. -A large query in SQL is not made smaller but translating it into an ORM DSL. -If "Query" in "SQL" means it's for querying data, then evidently "Query" not being in say Java or Python means those languages are NOT meant for querying data. If that's true, then why would you use them for querying data?

Re modules/libraries: I meant it is not easy to write a piece of SQL code, and then import it into several queries to reuse it, or lend it to someone else for use on their on schema. It is possible, yes, but seldom done, because it is hell. PostgreSQL extensions could be used for this purpose, but developing an extension requires a different set of SQL statements (or luckily, python or c) than those used by the user of the extension, which makes compounding them a bit hard. Not impossible, just hard to maintain,

About your last point, I don't think that was my line of reasoning, but, yes, for the love of what is precious, don't open SQL files as python/java file objects and then parse and rummage through them to find the data you are looking for. Not impossible, just hard to maintain.

Thanks for pointing out pgTAP, didn't know about this.

For some reason, data-science folks haven't yet caught up with ORMs.. I don't know if this is good or bad, but (as the OP shows) they are more used to rows and columns (or graphs) than objects. Maybe that will change one day.

Re: Stochastic gradient descent written in SQL

#67

Post by roflyear »

jackschultz wrote: post #13

>A machine learning algorithm which can be trained using SQL opens a world of possibilities. The model and the data live in the same space. This is as simple as it gets in terms of architecture. Basically, you only need a database which runs SQL. First paragraph of the conclusion, and this very much fits with the mindset that's been growing in me in the data world over the past few years. Databases are much more powe…

> Are the current tradeoffs just that more people know python vs sql or database specific languages to where moving logic to postgres or snowflake is looked down on?

Yes, mostly development, deployment, etc.. concerns. I haven't ever seen an org that versions their SQL queries, unless they are in a codebase. The environment is just unfriendly towards that type of management. Nevermind testing! Things that have solutions but we haven't matured enough here, because that type of development has been happening in application code.

Also, SQL is generally more complex than application logic, because they are designed to do different things. What is a simple exercise in iteration or recursion can more easily become something a little more of a headache.

Problems that can be resolved, but they are problems.

Re: Stochastic gradient descent written in SQL

#69

Post by noloblo »

naasking wrote: post #64
dventimi wrote: post #45

Earlier quoted context omitted.

Is a query not a relation?

Basically, but queries are not first class in SQL. You can't assign a query to a variable, or pass it as a parameter to a stored procedure, for example. This would make SQL composable: declare @x = (select * from Person) select Name from @x where Birthdate

Exactly since it declarative the style Lends itself using stored procedure calls to become composable

Re: Stochastic gradient descent written in SQL

#70

Post by Lemaxoxo »

naasking wrote: post #64
dventimi wrote: post #45

Earlier quoted context omitted.

Is a query not a relation?

Basically, but queries are not first class in SQL. You can't assign a query to a variable, or pass it as a parameter to a stored procedure, for example. This would make SQL composable: declare @x = (select * from Person) select Name from @x where Birthdate

Isn't that the point of common table expressions (CTEs)?
Post reply on HN