Modern Data Practice and the SQL Tradition
tselai.com
tselai.com
That said, my affection for SQL correlates with my overall growth as a software engineer, particularly grokking the concept of piping data between scripts: e.g. writing a SQL script to extract, filter, and transform data, and then a separate Python/R script that creates a visualization. I think SQL seems so unattractive to data scientists (i.e. people who may not be particularly focused on software development) because it seems so alien and separate from the frameworks (R/Pandas) they use to do the actual analysis and visualization. And so it's understandable to just learn how to do it all in R/Pandas, rather than a separate language and paradigm for data wrangling.
That being said ... RDBMs still suffer from scale and there are many efforts to fix this with sharding to disaggregating the data layer from the query layer with examples like AWS's Aurora and GCP's BigQuery etc -- in all likelihood data will likely live in many different places and will need to be adapted into things like a datastore or queried from things like Hive and Presto, or even a traditional ETL + EDW setup -- my point being there's no one thing that will solve everything as most single solutions break down when the number of rows is measured in the billions or more.
Is this another way to say you use SQL to query specific data from a larger dataset?
COPY INTO s3://mybucket/foobar/ from (select a, b, c from ...);
When I'm wearing the data engineer hat, I start to get interested in some of the options that aren't even databases at all, such as storing the data in ORC files. Because they sometimes offer big performance advantages. The data intake pipelines I'm working on are batch-oriented, so they benefit little from how RDBMSes are optimized for random access to records, and maybe also ranges of records, but only at the cost of write performance if you're looking to filter on a natural key instead of a synthetic key. . . it gets thorny. ORC is less-than-stellar at selective ad-hoc queries, but I'm not doing selective ad-hoc queries against the internals of my data intake pipeline, and I will fight to the death to prevent the BI team from doing so.
When I'm wearing my data scientist hat, I've got no idea what my next query on the data is, so there's no indexing strategy that could possibly meet my needs. That kills one of the RDBMS's biggest value propositions. So let's just wing it with map-reduce and get on with life. And, TBH, SQL is just too declarative for the way my brain works when I'm in analyst mode - the semi-imperative workflow I get out of something like Spark SQL or R or Pandas feels more natural.
Hey they brought me a smile this morning thank you ( I love Heinlein ).
I'm going to go against the grain here, at least as expressed in this thread, and say something that I think is defensible, but...
SQL is a terrible language.
1. It's old. There have been many, many advancements in programming languages since its inception in the mid-80s, none of which have made it into the language itself.
2. SQL is naturally tedious, with few mechanisms for isolating common functionality into e.g. objects/interfaces/functions/monads. Stored procedures exist, but still suffer from the other shortcomings of the language.
3. There's no vendor-agnostic way to write unit tests.
4. The code itself is ugly. There isn't a standardized (or even semi-standardized) way to confidently format code, compared to languages like Python or Java. I've seen (and very occasionally written) code in languages like Ruby or Java that is elegant to the point of being artistic. SQL is almost universally ugly.
5. Over time SQL tends towards becoming unmaintainably complex, for reasons I've come to believe are endemic to the language itself. In my experience and almost without exception, SQL has been the source of the most complex and difficult to maintain/test code in every codebase I've worked with. Views that are 300+ lines long, with multiple layers of inner joins, are common. And no one wants to touch these monsters because it's almost inevitable that refactoring will break something way over yonder.
6. There's no type checking.
7. There's no standardized library sharing framework like RubyGems or Python's pip.
8. IDE support is limited, and is mainly limited to basic code formatting. Compare this to something like IntelliJ where you can just ctrl-click on a method or variable and go to the definition. Even TypeScript is better here.
9. Refactoring is dangerous, and the ability for your tools to lend a hand is limited. Want to rename a public method in Java? Right click. You can be pretty confident doing so won't break your app (with caveats). If you want to rename a column in SQL, you're not going to have nearly that amount of confidence.
Now, I think there is a place for something like SQL: fast, able to easily access and update large amounts of data, etc. Something that executes directly against an RDBMS.
I just wish it wasn't SQL.
But yeah, that is at the very least better than SQL.
Yes, I don't necessarily disagree with anything you said. I didn't mean to imply that SQL was ideal for very good beyond a fairly limited set of functionality, which I would loosely describe as: a system for which many stored operations and refactoring becomes a necessity.
The use case I have in mind is with wrangling multiple, disparate datasets, into a normalized format that can be piped into a visualization function, or into a Rails app (for which an ORM like ActiveRecord can suffice going forward). And also, I'm thinking of people relatively new to data in general. I find SQL as a language offers a more explicit and rigid way of communicating data transformation concepts. After that, for most people who end up learning pandas/R, it's generally easier to do all the work in those frameworks (especially if you like notebooks).
SQL wasn't so much invented as it was discovered.
Except PHP. It's hiding in the bathroom.
> 1. It's old. There have been many, many advancements in programming languages since its inception in the mid-80s, none of which have made it into the language itself.
It's even older than that (around 1979), but being old isn't an argument in itself.
> 2. objects/interfaces/functions/monads, Stored procedures
Not the job of a database
> 3. There's no vendor-agnostic way to write unit tests.
As compared to alternative NoSQL database code that isn't portable at all? Agree though that the testing culture in SQL land could be improved.
> 4. The code itself is ugly.
Entirely a subjective matter. Programming != poetry.
> 5. Over time SQL tends towards becoming unmaintainably complex
If queries are complex in a language that is already very compact compared to equivalent procedural code, then chances are they're complex because the business problem is irreducibly complex, and another langauge won't save you here.
> 6. There's no type checking.
Of course there is! You can't insert character data into number columns for example (SQLite handles this a bit different though), and will receive proper type-related error message on eg. date functions.
> 7. There's no standardized library sharing framework like RubyGems or Python's pip.
What exactly would a stdlib (of table definitions?) be useful for? The standard is the SQL language itself. Maybe the state of portable SQL scripting could be improved by eg. lifting the syntax of eg. Oracle SQLPlus, also implemented by DB/2 since a couple years, or another syntax into the ISO SQL standard.
> 8. IDE support is limited, and is mainly limited to basic code formatting.
Last I checked, IDE support for SQL was quite good on Eclipse, including autocompletion. When you assemble SQL from strings in your app, there's a limit to what an IDE can do.
> 9. Refactoring is dangerous
SQL/relational algebra has a very powerful construct known as "views" - a consistent relational interface for your apps to work against. Refactoring is as good or bad as you make it to be.
> It's even older than that (around 1979), but being old isn't an argument in itself.
I wanted to add an additional comment on this point. What is it with old automatically equalling bad? I definitely appreciate where improvements can be made, but I think as an industry we incorrectly think there is something wrong with an old/mature technology. Throwing things away just because they are old hinders progress since we're constantly rewriting tested, working code. I'll get off my soapbox now.
That's not what was said. What was said that it's old and that it hasn't benefited from decades of research on languages and the way programmers are most productive.
However, I've come to appreciate it, and it's pretty great for what it gets you. Databases use to be very data structure specific. So if you wanted to migrate to a different database, you'd have to map the data from one db's data structure implementation to another!
Queries also use to be path dependent! That means you could only access data through a specific object, like json or XML. This can be problematic if you haven't fully figured out the query patterns. With relational, you can be flexible with queries, and don't have to know them ahead of time.
Lastly, when you think about all the imperative/functional code you'd have to write to execute a query with joins, it's pretty great that you can just express it declaratively.
As for some of your reasons, I'll just pick a few of my reactions: 5) a 300+ line program is small compared to most imperative code bases out there. But it is dense, I concede. 6) type checking is enforced in the schema 9) This seems to be property of code that works with data. Unless the data has a way of carrying versions and schema changes with itself, I'm not sure how downstream code of any paradigm would know something has changed.
Now, I think SQL could be improved, just not for the reasons you've given. Just because a language is old, doesn't mean it doesn't have good idea. Lisp is one of the oldest languages out there, and lots of languages have only recently caught up to its features. http://paulgraham.com/diff.html
I wish the query was written from big to small, where you pick tables first, join them, and then select your projection. It seems more natural to think of narrowing down your query.
I wish you didn't have to explicitly state the foreign keys and add indicies, and it'd figure it out for you, and only ask in ambiguous cases.
I wish there was a way to modularize it and make parts of queries reuseable--though I know there's a WITH expression.
I wish you didn't need to specify the tables and how they're related in the queries, and just ask for the data by columns.
SQL is light years more intuitive in my eyes.
df_merged = df1.merge(df2, on="common_key")
SELECT * INTO df_merged
FROM (SELECT *
FROM df1
JOIN df2
ON df1.common_key = df1.common_key)
https://pandas.pydata.org/pandas-docs/stable/getting_started...
Here's one example:
-- tips by parties of at least 5 diners OR bill total was more than $45 SELECT * FROM tips WHERE size >= 5 OR total_bill > 45;
# tips by parties of at least 5 diners OR bill total was more than $45 In [12]: tips[(tips['size'] >= 5) | (tips['total_bill'] > 45)]
another one...
SELECT day, AVG(tip), COUNT(1) FROM tips GROUP BY day;
tips.groupby('day').agg({'tip': np.mean, 'day': np.size})
These are all very simple queries, and things can get very complicated, so this list of comparisons is hardly the last word. I've had to untangle sql, but then again, I've had to untangle python code as well. I do find the SQL expression of these two queries very clear when I read it, whereas I have to expend more mental effort on the others, especially the aggregation. And I think people who don't program would have an easier time reading the SQL as well.
Also, I find it is valuable to be able to run queries directly against a database, or port them to a new environment. Pandas and R data frames are fairly similar, but if you've written them in sql, the transfer is zero effort.
I also find that if you want to join several tables or more, select only a few columns, and do aggregations with HAVING clauses, with vertical stacking perhaps, the pandas code does get considerably more complicated. The SQL does too, and UNION queries are not pretty... but overall, I think the SQL code certainly can come out ahead, and times considerably ahead, in terms of expressing clarity of thought and intent to someone reading the code later.
Oh one other thing you did add a bit of code by selecting into a data frame. With pandasql, you can run sql directly against a data frame and switch back and forth between pandas and sql operations, you don't need the additional step of creating and selecting into a new table. In this case, the code would look like
df_merged = pysqldf("SELECT * FROM df1 JOIN df2 ON df1.common_key = df1.common_key")
This is really nice, since there are some data frame operations that are much simpler in SQL and others that are much simpler in Pandas (actually, a lot of those aggregations would be handled through df.describe() -- but the querying and subsetting getting to that df may be more succinctly expressed in SQL).
create table df_merged as (select from df1 join df2 using(common_key))
Perhaps a bit more verbose but infinitely more readable and parseable.
How about this one. Add a between join i.e. "AND where df1.datecol between df2.datecol - interval '30 day' and df2.datecol + interval '30' day" in pandas?
This is an extremely common operation in analytics, by no means an esoteric use case.
I think there is an element of that, but let me suggest another possibility: this is driven by modern (so-called “agile”) project management techniques. Every week or so, the programmers are called on the carpet to provide “estimates” for a series of a dozen or so vaguely-worded “tasks”, each of which does have clear business value but is pretty open-ended about acceptance criteria. These estimates can only be on the order of a few hours: if the estimate goes higher, the project manager insists that you “break it down” until the individual subtasks can be estimated on the order of a few hours. This “breaking down” process, likewise, is expected to only take an hour or so - if it takes longer, you’re going to be answering for it next review period.
So you have a “task” that looks like it could use a relational database, but you don’t really know relational databases that well. You’re not an arrogant narcissist, so you recognize that something that takes other people weeks or months to learn effectively is probably going to take you weeks or months to learn effectively, as well. You _do_, however, know how to stuff documents into a document repo, and you can estimate that, and the estimate is within the acceptable four-hour maximum that the project management group that is, for some reason, now running the show, permits.
Even if you’re supremely dedicated and spend your off time studying (assuming you don’t have a family and _have_ any off time), you still have to come up with something RIGHT NOW so maybe after a few weeks of evening study you might have been able to produce something better, but the idiotic “sprint” ends on Friday, so you stuff it into a key-value store, mark the task complete, and spend orders of magnitude more time working around the problems that were introduced by myopic, shallow-thinking project managers than you would have spent actually putting together a decent solution.
So there’s that.
If you are a professional developer and can't figure out SQL then you don't deserve that 6 figure salary.
I use SQL daily and while it's not ideal, it provides a very reliable foundation upon which R and STATA scripts build.
I work in an engineering data science consulting group for a major UK PLC. Almost all of our clients have mainly SQL based data sources, and none of them really require cloud scale. In fact I don't think I have come across a client whose data I can't handle in SQLite on my laptop.
However, the problems we solve do require data scientists - mainly STEM MScs with a smattering of PhDs who are capable of combining statistics, domain knowledge and the ability to work within a variety of organisations and present.
The do however invest early in sufficient data science functions to have a good liaison between the researchers and data acquisition.
I disagree. Data science is largely about applying and interpreting data, and the value of that isn't really dependent on having data that is problematic for RDBMSs due to scale.
It seems the central argument here is NoSQL doesn't force you into good design habits so it's overrated. I'll concede this is partially true from my perspective because much of my work involves trying to sanitize, transform, and otherwise refactor poorly structured or designed NoSQL datasets.
But I've also seen my fair share of SQL databases which are poorly designed, don't use features which are meant to benefit developers (I haven't seen a Foreign Key smartly implemented in a LONG time). It's not really fair to say NoSQL has encouraged poor design practices; from my experience it seems like data model implementation is given little effort in general.
NoSQL takes the 'training wheels' off data model implementation where SQL is like keeping them on, but even with them you can still fall off the bike if you aren't careful though it's much harder.
This has also been my experience as a web developer mostly. I rarely see any applications that make use of foreign keys constraints supplied by the database server. Usually I see relations being handled by code on the application side.
Even when I build apps that are using SQL, I always implement this stuff in code instead of FKs even though I know how they work and when designing a database schema in an app like (for example) mysql workbench, I have the option to add them easily. But I always use the "ignore foreign keys" option and then implement the constraints in code. I just find it a bit more sane to have all of the logic inside the app.
I know I'm probably "doing it wrong" but would love to hear some other opinions about this from the hn crowd that does web development. I'm guessing that more "enterprisey" apps like CRM's and ERP's will probably use more of the native database stuff.
Of course you're probably thinking that it's no big deal when you're writing the app and the database at the same time. I will sort of concede that point (although I think constraints, FKs, etc... should all be used unless later performance testing shows they must be removed in certain cases). But, data nearly always outlives the original application. When it's time to add to/update/rewrite the original application code is when you'll be ecstatic to have the business rules enforced in the db.
But either way, I can't imagine any reasons why you wouldn't want constraints to ensure you database is in a consistent state. There can always be bugs in you application code, manual db updates, etc.
The moment you or your customers need business intelligence and complex queries, you quickly realize how important FKs and indexes actually are. I firmly believe that you should always have FKs and indexes, referential integrity at the db level is a great thing to have and is cheap and easy to implement.
For example: transaction X deletes a parent row and its child rows while a concurrent transaction Y inserts a new child node. Without a FK, this can leave an orphaned child row.
For similar reasons, SELECT before INSERT is generally not a safe replacement for a UNIQUE constraint.
You don't even need that high a user volume to see these errors in practice.
It's a shame that ORMs don't tend to encourage the use of database-level constraints, but even if I just want to build something quickly in e.g. Rails, I still add the constraints as an extra check because I'd rather get the occasional 500 than an inconsistent database.
I frequently get the self-awareness that there is a lot of sql functionality that I'm probably missing, misusing or completely ignorant of.
Particularly, the fk example just stood out because even though it's usually an integral part of database design i don't think enough importance is given to them "overall".
As @mpartel stated, as most orm's have similar functionality built in, their usage is just basically ignored. Also good example about the transactions stuff, I rarely see the use of foreign keys encouraged (and on your example pretty much almost required) in online tutorials and docs when discussing transactions.
More of a rant really, thanks again for the input guys! +1
SQL is just an ugly historical accident. Unfortunately, this how it often works...
NoSQL are a different story, of course. BTW, I believe that they predate Codd's work. There were many examples of non-relational DBs in the 70s.
A possible exception could be MUMPS https://en.wikipedia.org/wiki/MUMPS but I have no direct experience with this (while I used something akin to https://en.wikipedia.org/wiki/Hierarchical_database_model at the start of my career).
For a discussion on how SQL speficially falls short of the relational ideal, see this link: http://www.nocoug.org/Journal/NoCOUG_Journal_201308.pdf
I’m continually amazed that we’re STILL primarily interacting with databases, programmatically, by passing SQL statements as strings into them, rather than, say, passing in a list of column names as an array, and a list of conditions as tuples, etc. ORM’s try to bridge this gap, but they do a pretty bad job of it; they end up oversimplifying so much that the result is unreasonably inefficient.
What relational algebra (and by extension SQL) gets "right" is that each of these operations (Projection, Filtering, Join, Aggregation, Union) are composable: They take 1 or 2 collections as input and produce a collection as output. Moreover, each operation has simple and well-defined semantics from which you can build much more complex logic.
That's not to say that Relational Algebra can't be built in to an imperative language. Scala (and by extension Spark) collections are a great example of composable operators at work. Ruby's array methods, Python comprehension syntax, and Pandas/NumPy are similar examples of simple, composable primitives that combine to be much more powerful data transformations.
Apart from RA-based language primitives, there's also compiler support that allows you to use SQL directly, but avoid passing strings around at runtime. .NET's LINQ is a great example. I'll also pitch one of my own projects, DBToaster (https://dbtoaster.github.io/), which compiles view maintenance queries down to a C++ or Scala class.
In short, I agree that passing strings around leaves performance on the floor and leads to escaping and code injection nightmares. But SQL is the culmination of literally decades of use-based design, and any effort to replace it needs to take care to understand what it does well and why (like the efforts I reference above)
This is one of the concerns where embedding a language in another is sort of necessary but is a bit too much to have the compiler handle it by default unless DSL's are already a first class construct in the language.
Side note with postgres you can "prepare" a statement, compile it once, give it a name, and then execute that query without passing the full query every time.
Maybe, but as Bjarne Stroustrup said: "There are only two kinds of languages: the ones people complain about and the ones nobody uses." SQL seems like that: pragmatic, not perfect, but everyone uses it.
That's partly why I wanted to create a tool to query various databases (NoSQL ones or files too) with SQL. We're still in an early stage with OctoSQL regarding the variety of databases, but take a look if that sound appreciable to you: https://github.com/cube2222/octosql/
btw, not being critical of your project, just trying to understand it.
There's a great paper on that: "One SQL to Rule Them All", check it out.
We also want to scale well from single computer one-of data exploration queries, to full blown clustered long-term stateful stream processing.
The point is to provide a well thought out SQL interface to as many data sources as possible, and like drill does, push down as much computation as possible.
We actually learned about drill only after creating OctoSQL, but that's another story. (We're definitely less mature currently and support fewer datasources)
https://news.ycombinator.com/item?id=20059006#20062821
I believe there is a similar effort going on in the Flink project. They're slowly merging in work from the Blink project to provide a unified SQL paradigm (batch + stream).
We're basically aiming for a middle ground between Flink, Presto and Drill.
"Hey, turns out there's lots of great ways to use SQL!" Yeah, we know, they've been at the core of our business for the last 20 years at least.
Lots of enterprise shops started out as SQL databases to keep track of all the data, and web apps grew up organically around them.
Some SQL dialects do support both traditional variable assignment and iteration for those cases where an iterative/imperative approach makes more sense than trying to shoehorn the problem into something set-based / declarative. Some limit them to stored procedures (e.g. Postgres, and AFAICT Db2), while others allow them pretty much anywhere (e.g. SQL Server / T-SQL).
That was until CTEs were introduced in SQL:1999
> it's not a programming language at all. It's a.. query language.
The two are not mutually exclusive. SQL is used to tell a computer what to do, and it is very powerful at it: https://www.youtube.com/watch?v=wTPGW1PNy_Y
But e.g. PostgreSQL doesn't have functions as a datatype. https://www.postgresql.org/docs/current/datatype.html
So, uh, SQL isn't 'functional', right?
But virtually all query languages are declarative; e.g. GraphQL.
But please don't give them stored procedures and triggers.
I'm pretty much with you on stored procedures.
By centralizing data integrity logic in the database, you know where to look and that all apps using the database will abide by it.
I think part of the problem is that there is still this huge chasm between good coding skills and good database skills. It’s hard to have both.
I like the author's point about this:
> The trigger function itself was also written in the “schema.sql” file which meant that everything was clear and documented along with the other relevant schema information, thus easier to track and manage.
In my experience, in most cases the application doing the insert/update/delete is the best place to handle most side effects. That may be an on-demand API, a scheduled/queued job, etc. I've found triggers to be helpful and simple for audit and history tracking. There are definitely plenty of cases where triggers are useful and it probably depends a lot on your architecture.
I'm not as anti-trigger as I am SP. Though I stay away from triggers in any case as the rest of the world is fairly negative on them.
If you don't track migrations, and track only the latest CREATE statement, you can't deploy to a real system with that.
If you track both migrations and a final state, you'll end up in an awkward situation, because you'll likely have to do your migrations first and rely on automation to render your final state (in how many files? under what criteria?) or manually copy-paste it without making mistakes while coming up with your own structure. Or worse, writing on the final state set and then carefully copying changes back to migrations.
It's certainly not the worst thing in the world, but it is very unfriendly compared to the usual way of development under version control.
We've been experimenting with versioning schemas; deploying new versions of schemas along side existing ones. There is never any question about what version of a stored procedure is in that that schema. Same goes for all of the other schema objects. There is no schema migration tool needed. Of course I have to mention this is in data warehouse scenario and not an operational data store.
Also something similar which uses apgdiff https://github.com/subzerocloud/subzero-cli
But stored procedures are another matter, this could work in a fairly reliable way. Have you used this in any project working in other people? How does it work out?
I think one should also not over complicate the data layer.
Boring tech, I like the safety C D of SQLs ACID. http://boringtechnology.club/
This is a rule of thumb, only, and depends on your definition of efficiency and your queries. If you normalize everything, especially with some analytics queries, you will quickly find silly barriers to query times caused by all of the required joins.
The artcile also refer to MongoDB as hard to config It maybe a matter of taste. I found MongoDB document is way better than Postgres. They had thing like this:
https://docs.mongodb.com/manual/administration/production-ch... https://docs.mongodb.com/manual/administration/analyzing-mon...
Which I can easily follow and apply and they are action-able like set noatime on fstab, use XFS, max file handler etc.
For Postgres https://www.postgresql.org/docs/12/index.html I cannot easily find something similar to MongoDB one.
For the record, I like both these things and they often go together well. But once your data gets too big for Postgres, you don't have to immediately jump to NoSQL: modern distributed SQL tools like Presto are quite good and can let you continue to use the same SQL interaction patterns, even at the terabyte or petabyte scale.
You have to be a little more aware of what's going on under the hood, so you don't write something pathological, but it's quite a powerful approach.
I am even using Presto for a lot of what would normally be considered ETL: using SQL to select from a "dirty" table and inserting into a "clean" table on a schedule.
Use SQLite if you want a file format where you don't want to rewrite the whole file for every update, or if you're in a situation or environment where it's not feasible to have an actual database server.
Otherwise please don’t. I earn my living (amongst other things) due to organisations that went with this as blanket approach.
Use a right tool for the job. SQLite is fantastic for small to medium size datasets, shines in immutable case. Plus its maintenance and sharing cost is close to zero(something you will cherish once docker comes to play or if you want to learn or test some SQL without going through a pain of setting local permissions for your schemas).
Well, you could say the same about Postgres...
They are both databases, and the lack of scalability or (not) being able to run as a service does not change that simple (and useful) fact.
Found your problem.
From the project's github page[1]:
ZomboDB brings powerful text-search and analytics features to Postgres by using Elasticsearch as an index type. Its comprehensive query language and SQL functions enable new and creative ways to query your relational data.
From a technical perspective, ZomboDB is a 100% native Postgres extension that implements Postgres' Index Access Method API. As a native Postgres index type, ZomboDB allows you to CREATE INDEX ... USING zombodb on your existing Postgres tables. At that point, ZomboDB takes over and fully manages the remote Elasticsearch index and guarantees transactionally-correct text-search query results.
>As a rule of thumb, vertical scalability is in most cases more economical than the horizontal one.
Isn't the opposite the reason why we started to scale horozontally in the first place?
There are exceptions - distributing the data can be really helpful if you need to do a lot of bulk I/O (ETL jobs, analytical queries, etc.), but it comes at the cost of making "transaction" use-cases difficult and expensive. Using a scaled-up OLTP[1] database for user interaction and a scaled-out OLAP[2] database for analytics and ETL jobs is a common pattern.
[1] https://en.wikipedia.org/wiki/Online_transaction_processing
[2] https://en.wikipedia.org/wiki/Online_analytical_processing
Amazon will rent you a machine with a TB of RAM in it for an hour for the price of a fancy coffee.
Horizontal scaling has enormous complexity costs. It's worth it if you are genuinely web scale (Facebook, Twitter etc) but very few projects are.
With horizontal scaling:
1. Spin up the new node(s)
2. ???
3. Profit
With vertical scaling:
1. Spin down the node¹ (hopefully you've got more than one!)
2. Resize it
3. Spin the node back up
4. GOTO 1 unless all nodes are scaled up
5. ???
6. Profit
OR (somewhat simpler, but at this point you might as well just horizontally scale):
1. Spin up the replacement node(s)
2. Spin down the old node(s)
3. ???
4. Profit
That is: vertical scaling has more steps, even when done in a way similar to horizontal scaling. Sometimes it's necessary to scale vertically instead of horizontally, though (but in that case you might as well just horizontally scale with bigger nodes).
----
¹ It's theoretically possible to add and remove resources to/from a running system; some mainframes offer it as a feature (Multics programmers in particular were known to yank hardware out of production systems during off-hours and plug 'em into dev systems for development/testing, then plug 'em back into production for on-hours, all without bringing down the system), and Linux supports hot-adding and hot-removing CPU and RAM provided the underlying hardware supports it (Windows supports hot-adding as of Windows Server 2008 Datacenter Edition, but not hot-removing, last I checked). However, I'm not aware of any PaaS/IaaS providers offering compute instances with this capability; EC2 instances definitely don't support it, last I checked (switching from one instance type to another absolutely requires stopping and restarting EBS-backed instances, and instance-storage instances have to be outright imaged and then reimaged onto a new instance of the desired type).
For example, if I'm trying to predict the next day's stats by using stats in the past by simply taking the average, how many days in the past should I look at results and take the average of? Is the last 3 day average the best? 5 days? 8? 10?
There are clearly a couple ways to do it. One is by getting a data frame of all the stats for all the objects, write the python logic to loop through the days, get back the stats from the past X days not including the current day, taking the average and then storing that back to postgres in a projections table. A function like set_projections(5) where 5 is the number of days in the past I'm taking the average of.
Second way to do this is write that function as a plpgsql function where uses subqueries to find the past X day stats for the players and then creates or updates the projections table all in sql so we can run `select set_projections(5)` and that'll do it itself.
So the question becomes, which ones is "best"? I have to imagine it's mostly a case by case basis. Since it's only me here, I've been doing it in postgres alone since it can be done in one query (with multiple sub queries, yes), but that's it. With python, it'd involve many more steps. On the other hand, the sql looks huge and then I've been running into the issue of do I split some of the sub queries into sub functions since that's what I'd be doing in python? If there were more people involved, would it be bad to have larger cases like that in postgres since we wouldn't know the skill of the others, where mostly they'd be coders and could write the projections in languages they'd want?
Another example of this tradeoff is how should I interact with the database? I have a rails background, and ActiveRecord is an incredibly good ORM, whereas SqlAlchemy hasn't done it for me. In either case, there's a ton of overhead to getting the connections running with the correct class variables. So instead, I kind of created my own ORM / interface where I can quickly write queries on my own and use those in the code. This is especially easy since most the queries can be written in postgres functions so the strings of those queries is incredibly tiny!
What I've learned from this project I'm doing is that sql is very, very powerful in what it does. I've shifted more to using it for actions than I would have in the past, and pretty much all thinking I do is making as little code as possible.
Anyone make it through reading this and have comments about what I'm doing and what they like to do?
I've also shifted more logic to Postgres recently and keep the queries in the code trivial. It's because I like that SQL is declarative and there's no intermediate state to mess up (the whole query can be processed within a transaction).
As for readability (refactoring) you have several options in Postgres. Going the PL/pgSQL route probably means you're gravitating back towards procedural code. It does have its uses, but I try to avoid it whenever I reasonably can. Try using language SQL [1] instead. Another option is functions. Can be more readable but likely less efficient (they're a barrier for the query planner), just as PL/pgSQL. I've been mostly happy using nested views (and materialized views) lately. But whenever you can, use CTEs to structure your queries.
YMMV, just providing ideas to think about.
I've also seen highly user-visible performance issues because some developer either didn't realize how slow performing a series of queries in a loop would be (didn't understand the network overhead of each request) or didn't know how to condense what they needed to one or two requests. High correlation of this with the ORM-dependent folks.
So far as averaging a few days of stats my gut feeling is "this can just be a view with one or more medium-complexity selects behind it" but maybe there's some reason it can't.
> So instead, I kind of created my own ORM / interface where I can quickly write queries on my own and use those in the code. This is especially easy since most the queries can be written in postgres functions so the strings of those queries is incredibly tiny!
I haven't been in non-Rails Ruby land in quite a while but I remember liking Sequel quite a bit. Very much The Right Amount of abstraction (i.e. barely any) over the DB.
SELECT AVG(stats.val)
OVER(ORDER BY stats.date ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS avg_val
FROM stats
What I've learned from this project I'm doing is that sql is very, very powerful in what it does. I've shifted more to using it for actions than I would have in the past, and pretty much all thinking I do is making as little code as possible.
It's an interesting tradeoff. I've been on the other side, in an environment that was super SQL heavy (also Oracle PL/SQL) where we had to deal with these huge complex queries, and it was incredibly painful, for various reasons. One, SQL by itself isn't that composable in the way code is[1], so there tends to be a lot of copy/paste/duplication. Two, giant queries can be hard to understand/debug. Three, SQL can be hard to test. I mean, you can set up integration tests against a database (which can be time consuming), but how do you unit test a query?
On the other extreme, I've seen cases where something separately queries two tables and does a join operation in code, which would be much easier/safer just by doing a sql join. So there's a tradeoff. There's a hard to define sweet spot somewhere in the middle, IMHO, where you take advantage of relational semantics/joins, and utilize various sql features like group bys and whatnot, but where you haven't shoved all of your logic into the database layer.
[1] You can create CTEs (with foo as....) in postgres, but they may have downsides wrt performance (at least, last time I'd read up on it they posed an optimization boundary). You can also create views, but similar issues may arise.
We use SA's all the time in my work. One example, we only had 2018 data in our SA, even though our full analysis included 2019 data...this let us see if trends changed year to year. Another example: we made 5 lookback variables, 90 day, 180 day, 365 day, etc...then we analyzed which of these was most relevant to our analysis.
"Data practitioners", "data practice", "data management" just seems like weird rebranding of stuff that businesses have been doing since the 1960s. Partly what the article seems to be relating regarding SQL.
What is "data science" besides computing, data storage of some sort, and analyzing the data? Because it's "BIG" now? Because now we "extract knowledge and insights" from data, since apparently giant databases were amassed for no reason in the past? Because now we "combine multidisciplinary fields like statistics", since apparently nobody had thought to run statistics on their data before? Because "AI"?
Doesn't the job title of "data scientist" still correlate with high pay, or has the hype started to wane?
This is a good summary of its meaning and history: https://en.wikibooks.org/wiki/Data_Science:_An_Introduction/...
I wrote a full blog post ranting about the disparity between expectation vs. reality for data scientists a year ago: https://minimaxir.com/2018/10/data-science-protips/
Is it no longer the case that every programmer has to know SQL? I have always and still do consider knowledge of SQL a minimum requisite for any programming job...
Programming is now such a big field that plenty of people will go their whole careers without knowledge that's foundational for other programmers - whether it be SQL, linear algebra, memory management, distributed systems, ...
In a way, it's very cool. Instead of a single-dimensional "how good a programmer are you", we've got a multidimensional space. That, naturally, yields a lot more interesting corners, like, say, what could be invented by someone who knows a lot about databases and GPUs.
For a lot of systems, a lot of engineers/programmers often care very little about the "data", and they very often don't need to. E.g. on a high abstraction level your webserver doesn't care if it's sending HTML, JSON or whatever, and here we are just speaking about formats and not even higher semantics of the data.
I think there can be a lot of value in data-focused roles in companies, because there are many (hard) questions around data that are often badly answered as afterthoughts:
- What are the exact semantics of this data?
- How do make sure the data is clean?
- How do we evolve the data over time?
Sure, well-thought out data structures were an integral part of good clean code, but the bulk of data was an artifact of software in the form of logs and largely unused database records, not the "center" of anything. Today, there are many people who take these logs and database records and run very specialized code on them. I entered industry in the mid 2000s and it was very difficult to find jobs that concentrated on this component back then. We're often called data scientists today, and there's orders of magnitude more attention that goes into it. This is a real shift that has brought data into the "center" of many organizations.
After a new round of hiring, we came to the conclusion that "pure" computer scientists will not do the cut, even those with a phd, simply because we are quite convinced that they won't be able to understand the problem at hand.
I don't think that this leads to changes in the traditional ways of handling IT because of any fundamental change in a data analyst's needs. It's more that, historically they were outside the formal tech sphere, and therefore lusers who had to make do with whatever resources they were able to squeeze out of IT. Now, armed with better technical chops, they're able to make a stronger case for their needs, which means that more is being done to meet those needs.
Which is not to say that your skepticism is unwarranted. "Data science" is still a catch-all term for a new and ill-defined way of working, and things are still pretty wild west. Anyone who tells you they're sure they know what the best practices are is probably trying to sell you their product, which happens to be the best practice. Not many people have the benefit of 20 years of hard-won experience from which to draw a set of really strong guidelines.
Moreover it’s an iterative process where you start with hypotheses on what features will be useful then build a model and might need to pull more data. For example you could build a recommendation engine just using the set of products purchased by customers but then might explore adding user agent features like devices or might add data about the products like category or price information.
An analyst (“data scientist”) who can pull and interact with that data is much more effective than one who needs to wait for someone else to pull the data for them (most actuaries I know are weak programmers outside of R and wouldn’t know where to begin using distributed computing or getting a TensorFlow gridsearch running on a cluster of cloud GPUs :)
I like some of these technologies for taking care of the dragnet data collection and engineering, but, when I'm actually doing a data analysis, it's rare I want to run it on a Spark cluster; I'd much rather run a Spark job to collect the information I need and sample or digest it down to a size that I can hack on locally in R or Pandas. Yeah, there will be some sampling error, but the potential dollar cost associated with that sampling error is much lower than the cost to eliminate that sampling error. And it's basically zero compared to the elephant in the room: the sampling bias I'm taking on by using big data in the first place. "Big data" is, from a stats perspective, just the word for "census data" that people like to use in California.
I also way prefer to just crank up the RAM on a single instance and use Pandas/Dask instead of dealing with distributed computing headaches :)
Sure, the cloud providers sell computing power but it’s a race to the bottom and makes much more sense than buying hardware for these kinds of bursty analytics workloads.
I don’t think “Big data” from a stats perspective is analogous to census data - in some cases yes but for applications like recommendation engines you lose a lot of valuable signal by sampling.
I do think that most companies log lots of data in different places without much thought or structure, and as a result need a "data scientist", who spends most of their time simply aggregating and cleaning data that can then easily be processed on a single machine. ie: they spend 10% of their time doing statistics.
Maybe I'm wrong, but the above is how all the data scientists that I know describe their work.
Most of a data scientist's time is definitely spent getting data from various places, possibly enriching it, cleaning it, and converting it into a dataframe that can then be modeled (and surprisingly a lot of those tasks are often beyond pure actuaries / statisticians who often get nervous writing SQL queries with joins nevermind Spark jobs)...
But also once you've got that dataframe, it's often too big to be processed on a single machine in the office so you need to spin up a VM and move the data around which also requires a lot more computer science / hacking skills than you typically learn in a stats university degree so I think the term data scientist for someone who can do the computer sciency stuff in addition to the stats has its place...
A C++ program with a couple megs on the heap is appropriate, just as an ETL pipeline is appropriate for gigabytes to terabytes.
Programmer -> Developer -> Software Engineer
Sysadmin -> Operations Engineer -> Site Reliability Engineer
QA Engineer -> Automation Engineer -> SDET
10 years ago they had a big initiative around “Analytics.” I’ve programmed and worked in data for a few decades so I tried to figure out why these biz people were interested in Analytics.
When I asked they brought up all the terms you said. I showed examples from various projects how they were just describing basic data programming. But they disagreed because this was new.
I figured it out a few years later when they spent $5B on the initiative and it was making money like gangbusters. This was now a product line. It was new that they were selling it.
So now there’s people who want more money and need to differentiate and thus the titles and whatnot. I wish there was an easier way to view work output of people because now the only way I can differentiate between “data engineers” and “data scientists” and “AI wizards” is by looking at their GitHub profiles or talking to them in depth and profiles are almost never complete or accurate and talking takes a lot of time.
In my experience most of the people doing what I consider data science have the titles “applied scientist” “machine learning engineer” “data engineer”.
I don't think this is GDPR compliant.
In fact, logic programming language and SQL should consolidate into a relational programming language. Every thing we write as a program, automatically supports persistent and distributed storage. It can also support probabilistic computation to have machine learning involved. Then we will have a complete data driven software solution.
Unfortunately, right now, we cook everything up with SQL, python, operational DBs, analytics DBs, Spark, Tensorflow.
It could have been a better place.
The only reason you might choose other approaches is to make the problem look significantly more complicated, thereby justifying more maintenance and resources.
SQL is just too easy and some super smart engineers don’t like it because if it. That said, NoSQL does have value in some corner cases where it could perform better when most of the logic is simple lookups.
Because initial application specifications are sparse and definitely wrong. If your application is still up and running 5 years later and your data definition hasn't changed much in the past 3, then maybe refactor around an RDBMS. Designing around rigid structures during your first pass is costly. This is why there's been a rise in NoSQL and dynamic languages.
It's less costly to create a relation table when you realize there may be multiple instances of a piece of data associated with a record than it is to just stick those pieces in an array.
Because when you don't use the relational data, you get the extra work of modifying all of your existing NoSQL records to use the array structure. And as a bonus, you make it easy to do queries in both directions with the relational data.
NoSQL offers virtually no efficiency benefit unless you're actually consuming unstructured and variable data.
You'll easily make up this lost time just in not having to immediately clean up crappy data that you've written to the database while you're developing the feature. That's been my experience anyway.
Contrast with, I was at a Cassandra workshop a few weeks ago, and the speaker, when asked directly, conceded that, in Cassandra, you really do need to nail your schema on the first try, because there are no great retroactive schema migration mechanisms, and any evolution is going to result in all consuming applications needing to know about all possible versions of the data model. Which ends up being a huge source of technical debt. And heaven help you if you didn't get the indexing strategy right on the first try.
I think that this might point to the classic tension between easy and simple: RDBMSes are focused (admittedly to varying degrees of success) on trying to keep things simple, but there might be some work involved. NoSQL solutions are often sold as being easy to work with, and I don't deny that at all, but my experience is that, in the long run, they can become a huge source of complexity.
This isn't a tension that's unique to software. In my contractor days, we'd also do things one way when it was just a quick-and-dirty job, and a whole different way if we were looking to build something to last. e.g, you'll never catch me using a sprayer to paint my own house, no matter how much faster it is.
Otherwise you end up with equivalent logic littered throughout multiple application codebases sharing the same database.
With the usual caveat that yes, some datasets & workloads probably do benefit from certain NoSQLs, but that's a pretty small minority.
Sure, SQL databases make you defined a schema. But it's very easy to change this when requirements change.
But, more importantly, this quote really is right:
> The more I work with existing NoSQL deployments however, the more I believe that their schemaless nature has become an excuse for sloppiness and unwillingness to dwell on a project’s data model beforehand.
Far too many software engineers just don't understand databases and don't understand the long-term implications of their decisions. Good SQL is rather easy, but it does take a few extra minutes than sloppy approaches.
Or, to put it differently, some NoSQL databases are great at rapid prototyping. But, they can't scale and hold little value for someone who will put in the extra hour or two upfront to use SQL.
But has an RDBMS ever been a major source of that pain? I can't say I've ever encountered a time when it has. If you need to change the structure, just write a script.
I'm not saying it's completely painless, but nothing is.
Yes, yes, there are sometimes exceptions, one must repeat explicitly despite having already said it (see: "probably") because this is HN.
When I'm designing an app from scratch I often think about the SQL tables first and how I'm going to build them, and it really sharpens my idea of what my program will be.
I don't see how skipping that process would make things easier.
However, perhaps there are tools that NoSQL provides that are handy.
However I've never greenfielded an actually large application.