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

181–187 of 187 posts

Re: Stochastic gradient descent written in SQL

#181

Post by CRConrad »

bob1029 wrote: post #80
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…

> Databases are much more powerful than we think The older I get the more I agree with this. There is nothing you cannot build by combining SQL primitives. Side effects can even be introduced - on purpose - by way of UDFs that talk to the outside world. I've seen more than one system where the database itself was directly responsible for things like rendering final HTML for use by the end clients. You might think thi…

> Side effects can even be introduced - on purpose - by way of UDFs that talk to the outside world.

> I've seen more than one system where the database itself was directly responsible for things like rendering final HTML for use by the end clients.

I built BI Web pages entirely in PL/SQL (using some Oracle modules I can't quite recall the nams of) over twenty years ago. Got a bit hairy to keep the meta-recursive stuff in your head when hard-coding JavaScript into it, calling the next such page for each value on the page, for drill-down to the next level... Not fun, but quite doable.

And with a bit more modern tooling -- as I said, this was turn-of-the-century tech -- I have no doubt it could be done much more effectively and efficiently nowadays.

Re: Stochastic gradient descent written in SQL

#182

Post by CRConrad »

naasking wrote: post #127
chrstr wrote: post #100

Earlier quoted context omitted.

CREATE TABLE data_a AS (SELECT 'a' AS test_case, 1 AS value); CREATE TABLE data_b AS (SELECT 'b' AS test_case, 2 AS value); CREATE VIEW data AS (SELECT * FROM data_a UNION ALL SELECT * FROM data_b); CREATE VIEW complicated_query AS (SELECT test_case, value+1 FROM data); SELECT * FROM complicated_query WHERE test_case = 'a'; SELECT * FROM complicated_query WHERE test_case = 'b';

You just reinvented defunctionalization, which is a transformation from a domain that has first-class values to a domain where support is only second-class. Defunctionalization is typically used in programming languages to simulate first-class functions in languages where functions are only second-class citizens, like C and Pascal. This perfectly illustrates my point. You had to manually defunctionalize your data mod…

> languages where functions are only second-class citizens, like C and Pascal.

1) Only if you define Pascal as only Wirth's very first version. That changed almost immediately.

2) Only if you refuse to equate “pointer to function” with “function”. Which in C, where “everything is a pointer” (a bit like in Unix Linux “everything is a file”), seems rather silly.

Re: Stochastic gradient descent written in SQL

#183

Post by CRConrad »

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…

> 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.

Non sequitur. SQL is typed; SQL can be edited in any text editor; there are lots of SQL IDEs and, arguably, debuggers and package managers. Sure, the package managers are specific to each RDBMS, but so what? Npm is no use in COBOL either. And sure, the “debuggers”, to the extent they can be said to exist, are radically different from those of “conventional” – of other – programming languages. But again, so what? A Smalltalk debugger is no use to fix the output from Intel’s latest C compiler either, or vice versa.

IOW: There is no such “SQL vs programming languages” dichotomy. SQL is just another programming language, with its own strengths and weaknesses, just like all the rest of them. “The rest” are not distinguished from SQL by somehow magically all having the attributes you claim for them: Some have them, some don't; some have this but not that, some others, the other way around. Someone built all those IDEs and debuggers and package managers for (some / many / most of) those other languages; you can build them for SQL too.

Re: Stochastic gradient descent written in SQL

#184

Post by CRConrad »

justsomeuser wrote: post #167
fifilura wrote: post #39

Earlier quoted context omitted.

> Type systems SQL has types > compilers For what specifically do you need a compiler? > debuggers Some tasks - like the concurrency SQL enables - are just very difficult to debug with debuggers. It would be the same with any other language. What SQL does here though is to allow you to focus on the logic, not the actual concurrency, > text editors, package managers I feel like these two are just for filling up the sp…

> Type systems Sure SQL has types, but they are checked at runtime, not compile time. Also you cannot define function input and return arguments with types that are checked before you run the program. > compilers If you want efficient and/or portable code. They will check your code for type errors before you run them. They give you coding assistance in your editor. > debuggers Being able to break a program and see it…

> > text editors, package managers.

> Editor support of real languages is much better than SQL.

So text editors suck at supporting SQL... How is that SQL’s fault?!? Go complain to the text editor authors.

> Package managers enable code re-use.

Yup. Build one for SQL, then you can re-use SQL code. Just like someone had to build one for every other language. What does this prove about SQL being inferior to other languages? A: Nothing at all.

Re: Stochastic gradient descent written in SQL

#185

Post by CRConrad »

bob1029 wrote: post #88
civilized wrote: post #72

In the comments here so far, we see a pattern we've seen before. When someone suggests doing something in SQL, there's a lot of concern about SQL being a very limited programming language where it's hard to do proper engineering. Here's I would really love to know: why is it that SQL is, to first order, the only language used to interact with databases, and SQL has about the same features as it did in the 70s? It see…

> why is it that SQL is, to first order, the only language used to interact with databases, and SQL has about the same features as it did in the 70s? Because SQL is effectively a domain-specific language. If you added 100+ additional keywords/functions/etc., do you think it would be easier or more difficult for the average developer to build something clean with it? I look at SQL like a pile of bricks. You don't want…

> You don't want complicated, unicorn bricks or you won't be able to fit anything meaningful together. Experienced masons almost certainly prefer their materials to be as dumb and consistent as possible.

And now we know what's gone wrong with Lego for the last two-three (close to four?) decades.

Funny – and sad! – how popular this shit has been on sites like this, though. Dare one hope that that popularity might begin to wane? Idunno

Re: Stochastic gradient descent written in SQL

#186

Post by rukuu001 »

spprashant wrote: post #168
rukuu001 wrote: post #141

Earlier quoted context omitted.

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.

I ll take this as a learning opportunity. I have looked around to find a reliable framework to implement within our team and failed to find anything usable. How do you guys manage to implement versioning and testing? If you had a new stored procedure to deploy, where do you deploy? How to you integrate with existing applications which rely on it?

Depends on what you're using. The Java world has a bunch of JDBC wrappers for testing SPs. SQLServer GUI has unit testing tools that can also be run from command line. @dventimi has good postgres recommendation.

Version control - each SP is in a file sp_descriptive_name.sql that's on the source tree under /db.

Deployment - compiled from the command line. Deployment target managed by env variables. Systems can require odd workarounds for ensuring no downtime - this is where systems end up with sp_descriptive_name_vN with N for each version (still stored in sp_descriptive_name.sql tho) so the new SP can be loaded alongside the old version, and then application code can be updated (or config updated) to use the new SP name. Good practice to remove the old version on next release.

I think it's true that SPs introduce a compromise, but having logic operate on data without having to send data back and forth over DB connection is a pretty big win.

It can also be a good abstraction because it avoids application code needing to know (for example) which field in which table indicates a customer is active, and code can execute something like sp_deactivate_customer(cust_id) and then let the stored procedure take care of updating flags, dates etc that describe a customer's active status. ymmv depending on whether your team owns the data and database, or if that's managed separately.

Re: Stochastic gradient descent written in SQL

#187

Post by justsomeuser »

CRConrad wrote: post #184
justsomeuser wrote: post #167

Earlier quoted context omitted.

> Type systems Sure SQL has types, but they are checked at runtime, not compile time. Also you cannot define function input and return arguments with types that are checked before you run the program. > compilers If you want efficient and/or portable code. They will check your code for type errors before you run them. They give you coding assistance in your editor. > debuggers Being able to break a program and see it…

> > text editors, package managers. > Editor support of real languages is much better than SQL. So text editors suck at supporting SQL... How is that SQL’s fault?!? Go complain to the text editor authors. > Package managers enable code re-use. Yup. Build one for SQL, then you can re-use SQL code. Just like someone had to build one for every other language. What does this prove about SQL being inferior to other langua…

> What does this prove about SQL being inferior to other languages

My point is that I think it is inferior for general application/business logic programs. For queries that filter large datasets to small result sets, SQL is probably better as it has the built in indexes and query planner (plus ACID).

I am pointing out that the current environment (better text editors and package managers) favours general languages, so they are a better current choice (to use in combination with SQL) over just writing everything, including application logic, in SQL.

Post reply on HN