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

41–50 of 187 posts

Re: Stochastic gradient descent written in SQL

#41

Post by AndrewKemendo »

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 mean this is an opportunity right? Build those things

Re: Stochastic gradient descent written in SQL

#42

Post by syats »

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.

Save yourself the trouble and all your collaborators the pain of working with this code in the future, of trying to add new features, of trying to reuse it in another project.

If you want to operate near the data, use PL/Python for PostgreSQL.

EDIT: Fixed formatting.

Re: Stochastic gradient descent written in SQL

#43

Post by Lemaxoxo »

AndrewKemendo wrote: post #41
ISL wrote: post #33

Earlier quoted context omitted.

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 mean this is an opportunity right? Build those things

I agree. Databases are going to be here for a long time, and we're barely scratching the surface of making people productive with them. dbt is just the beginning.

Re: Stochastic gradient descent written in SQL

#44

Post by hobs »

swasheck wrote: post #26
hobs wrote: post #4

Earlier quoted context omitted.

In a non-streaming db What would prevent you from storing the result set and just using the last iteration to calculate the next?

i’m trying to learn here so please pardon my ignorance. wouldn’t a pre-aggregated set affect the new aggregate result? i suppose you could store avg, sum, count and then add the new value(s) to sum, new count to count, and recalculate average from that. or even just avg and count and then re-aggregate as ((avg*count)+new values)/(count + new values count) but i didn’t know if there’s a better way to process new value…

Yep that would be what I would do - effectively a functional approach where no memory is required besides the current set and iteration.

A big part of materializing datasets for performance is finding the right grain that is both easy enough to calculate and also can do nice things like be resumeable for snapshots.

Re: Stochastic gradient descent written in SQL

#45

Post by dventimi »

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.

Is a query not a relation?

Re: Stochastic gradient descent written in SQL

#46

Post by dventimi »

Scubabear68 wrote: post #30
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…

With “traditional” RDBMS systems, putting a lot of code in the DB lead to a lot of scaling issues where you’d need gigantic machines with a lot of RAM and CPU: because the DB was doing so much work. It was expensive and clunky to get HA right. In more modern DBs being distributed horizontally, this approach may see a rebound. The big “but” is still costs, in my experience in AWS as an example, managed Postgres Aurora…

Why would running code in a database process be intrinsically more expensive than running it in some other process?

Re: Stochastic gradient descent written in SQL

#48

Post by Serow225 »

justsomeuser wrote: post #35
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…

I think general programming languages are better for general programs than SQL. Specifically they have: Type systems, compilers, debuggers, text editors, package managers, C FFI etc. But I agree that having the data and the program in the same process has benefits. Writing programs in SQL is one way. Another way is to move your data to your general program with SQLite. I like using SQL for ACID, and queries as a firs…

Another is MS SQL Server, which lets you run .NET on the database server :D "you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code"

Re: Stochastic gradient descent written in SQL

#49

Post by dventimi »

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?

Post reply on HN