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

151–160 of 187 posts

Re: Stochastic gradient descent written in SQL

#151

Post by itsthecourier »

rukuu001 wrote: post #141
spprashant wrote: post #135

Earlier quoted context omitted.

The problem with throwing everything in a database is you end up with brittle stored procedures all over the place, which are painful to debug. There is no good support for version control or testing, which means you end up creating a dozen copies of each function named (sp_v1, sp_v2,.., etc.). It much more harder to practice iterative development which the rest of software development seems implements effectively. A…

No comment re rDBs supporting parallelized ML but re stored procedures - if your workflow evolves to treat them as ‘1st class’ code assets they’ll be just the same as the rest of your code. We always had them in version control, unit tested etc. The tools are there if you want to use them.

Updating stored procedures was a pain in the ass last time I checked. Checking changes on them in deployed servers was too very painful

Re: Stochastic gradient descent written in SQL

#152

Post by mr_toad »

dventimi wrote: post #96
alphanumeric0 wrote: post #92

Earlier quoted context omitted.

What if I wrote a very long, complicated query that I'd like to test against different tables (like test tables), and let's say I can't use stored functions or procedures. How could I pass different tables to my query?

If you can't use stored procedures which are good for this very case, many databases offer dynamic SQL. That might work in some cases.

Dynamic SQL isn’t SQL, and it’s not relational. It’s no different from using a language like Python to generate SQL queries.

Re: Stochastic gradient descent written in SQL

#153

Post by wpietri »

CreRecombinase wrote: post #133
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 spend most of my time in the parallel universe that is scientific computing/HPC. In this alternate reality SQL (not to mention databases) never really took off. Instead of scalable, performant databases, we have only the parallel filesystem. I'm convinced the reason contemporary scientific computing don't involve much SQL is sociological/path-dependency, but there are also very good technical reasons. Optimizing so…

For sure. Anything done in SQL is running on top of a million lines or more of extremely complicated non-SQL code. If that works for a given use case, great, but if not, optimizing can get very challenging. I'd much rather deal with something closer to the metal.

Re: Stochastic gradient descent written in SQL

#154

Post by wpietri »

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 option is to use an approach like Prevayler: https://prevayler.org/

The basic notion is you keep your data hot in RAM and manage it directly. You make every change an object (or a command), and write that out serially to a log before you execute it. That gets you the ACID guarantees but with no I/O but linear writes, so it can be extremely fast.

It only makes sense when your data fits conveniently in RAM, but that's a lot of things.

Re: Stochastic gradient descent written in SQL

#155

Post by naasking »

mr_toad wrote: post #149
naasking wrote: post #124

Earlier quoted context omitted.

You're still not getting it. First-class status means that anywhere a value or variable can be used, a query or table should also be able to appear, and vice versa. This means a table or query can appear as a return type, a parameter to a stored procedure or query, a variable, and so on. SQL just does not have this, it instead has 15 different second class ways to handle tables and queries that try to make up for the…

> You're still not getting it. First-class status means that anywhere a value or variable can be used, a query or table should also be able to appear, and vice versa. This means a table or query can appear as a return type, a parameter to a stored procedure or query, a variable, and so on. I doubt you could implement a query planner that would cope with that degree of flexibility. Which means you’d be forced to deal…

Without these features, people have to resort to dynamically generated SQL queries in procedural or functional languages, which is much worse! SQL has also become significantly more complicated by adding all sorts of second-class features to get around this composability limitation (CTEs, table valued functions, views, etc.).

Besides, I don't think it would be as bad as you say. You can approach it as a simple template expansion into flat SQL queries except where a data dependency occurs, at which point template expansion proceeds in stages, one for each dependency.

LINQ on .NET provides most of the composability I'm talking about, although it has a few limitations as well. Still worlds better than raw SQL.

Re: Stochastic gradient descent written in SQL

#156

Post by pgwhalen »

naasking wrote: post #124
chrstr wrote: post #85

Earlier quoted context omitted.

create table x as (select * from person); select name from x where ...; there you go, just configure your editor to display "create table x" as "declare x = " ;) or even a version with lazy evaluation: create view x as (select * from person); select name from x where ...;

You're still not getting it. First-class status means that anywhere a value or variable can be used, a query or table should also be able to appear, and vice versa. This means a table or query can appear as a return type, a parameter to a stored procedure or query, a variable, and so on. SQL just does not have this, it instead has 15 different second class ways to handle tables and queries that try to make up for the…

Usually I balk at the idea of of someone describing a language feature as “first class” because it seems to wishy washy. But in this thread you’ve shown me that maybe the best definition is through “proof by negation,” by patiently responding to arguments and demonstrating why a certain usage and the ensuing restriction around it means it is not first class. Bravo!

Re: Stochastic gradient descent written in SQL

#157

Post by craigacp »

westurner wrote: post #146
Lemaxoxo wrote: post #91

Earlier quoted context omitted.

Hehe I was wondering if someone would catch that. Rest assured, I know the difference between online and stochastic gradient descent. I admit I used stochastic on Hacker News because I thought it would generate more engagement.

What are some adversarial cases for gradient descent, and/or what sort of e.g. DVC.org or W3C PROV provenance information should be tracked for a production ML workflow? Gradient descent: https://en.wikipedia.org/wiki/Gradient_descent Stochastic gradient descent: https://en.wikipedia.org/wiki/Stochastic_gradient_descent Online machine learning: https://en.wikipedia.org/wiki/Online_machine_learning adversarial gradien…

We built model & data provenance into our open source ML library, though it's admittedly not the W3C PROV standard. There were a few gaps in it until we built an automated reproducibility system on top of it, but now it's pretty solid for all the algorithms we implement. Unfortunately some of the things we wrap (notably TensorFlow) aren't reproducible enough due to some unfixed bugs. There's an overview of the provenance system in this reprise of the JavaOne talk I gave here https://www.youtube.com/watch?v=GXOMjq2OS_c. The library is on GitHub - https://github.com/oracle/tribuo.

Re: Stochastic gradient descent written in SQL

#158

Post by naasking »

pgwhalen wrote: post #156
naasking wrote: post #124

Earlier quoted context omitted.

You're still not getting it. First-class status means that anywhere a value or variable can be used, a query or table should also be able to appear, and vice versa. This means a table or query can appear as a return type, a parameter to a stored procedure or query, a variable, and so on. SQL just does not have this, it instead has 15 different second class ways to handle tables and queries that try to make up for the…

Usually I balk at the idea of of someone describing a language feature as “first class” because it seems to wishy washy. But in this thread you’ve shown me that maybe the best definition is through “proof by negation,” by patiently responding to arguments and demonstrating why a certain usage and the ensuing restriction around it means it is not first class. Bravo!

I agree the term is often abused, but I think the wikipedia page actually does a decent job of making the notion somewhat precise, along the lines I've been arguing here:

https://en.wikipedia.org/wiki/First-class_citizen

If you want to see what queries as first-class values looks like, LINQ in .NET is pretty close. I can actually write a series of queries that build on and compose with each other, like this:

    IQueryable RunQuery(int userSelection)
    {
        var first = from x in People
                    select x;
        var second = userSelection == 1
            ? from x in first where x.Birthday > '2000-01-01' select x
            : from x in first where x.Name.Contains("Jane") select x;
        return DumbJoin(first, second);
    }

    IQueryable DumbJoin(IQueryable first, IQueryable)
    {
        return from x in second
               join y in first on y.Role equals x.Role into g
               select g;
    }
This query is nonsense, but it just shows you what composition really looks like when queries are first-class values. I wish raw SQL were like this!

Re: Stochastic gradient descent written in SQL

#159

Post by margorczynski »

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 problem with this approach is that:

1) No static typing 2) Updating the logic requires migrations 3) You put the logic into the place which is the hardest to scale and many times a single point of failure 4) Cannot compose the code effectively and general verbosity

It's one of those ideas that sounds great on paper and maybe works in some smaller problems but as you go up in complexity things get worse and worse

Re: Stochastic gradient descent written in SQL

#160

Post by haolez »

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've created a big system with only SQL in the past like you just said. I wouldn't do it again because of these two pain points that I had to deal with:

1. it's really hard to debug SQL queries and stored procedures (at least it was in Postgres 11)

2. when you hit a performance bottleneck, you don't have much control over it - parallelizing is hard and you have to trick the query planner to do what you want (and it doesn't work sometimes)

Post reply on HN