I'm really contrasting SQL to other commonly used data manipulation APIs and dataframe libraries like dplyr, pandas, polars etc., but again, appreciate this should be clearer in the blog
The PySpark API has methods for SQL-like operations, so it's familiar to people like you and me who know and love SQL. There is no point to Pandas on a datalake since PySpark has dataframes.
I'm a huge proponent of SQL. I'm dubious of ORMs and think a lot of websites would be more performant if people just learned SQL, or whatever the idiomatic querying language for their chosen DB is. But with datalakes, pyspark is great.
I'm much less worried about that happening to SQL scripts.
If your db is scalable enough (like Snowflake) and you have the money, you can use SQL directly in the same way. But, I've seen data analysts writing bad SQL against Snowflake costing thousands of dollars per query.
It’s really tough to future proof anything. At least with pyspark you will have the code. And, there will always be experts out there. Worse case, if it’s important enough, someone will wrap it in an API and use it until something better comes along (see: 50 year old mainframes still in use).
Is this hosted databricks ?
But, tell me more. I always like a better way.
Is it more efficient to read/write from SSD? Right now, I need everything in memory.
You can choose when to cache the data in memory.
And it's the fact that you can write the code in Python, Scala etc instead of SQL.
So merely writing in a language that we believe will be around forever does not prevent your code from being rewritten.
Also unfamiliarity will make SQL feel inefficient for non-SQL programmers. The experience of rewriting SQL teaches some this lesson. But others find that the familiarity of the language of the rewrite makes it seem better to them, even though it is objectively worse. An objectively worse that only becomes visible when someone needs to optimize it for performance reasons. And it is seldom the programmer who did the inefficient write who has the skills to optimize it back to what SQL did in the first place!
(Yeah, I've had to do those optimization fixes.)
So on seeing one they can sometimes think it's quicker to redo this in a language I know rather than upskill enough to understand this.
One of the biggest problems with spark imo is that people think that the magic cloud is going to make it go fast, when it actually means that the REPL is just muuuuch slower to iterate on.
There are couple of famous examples
https://shopify.engineering/build-production-grade-workflow-...
"By using SQL, a much wider range of people can read your code, including BI developers, business analysts, data engineers and data scientists."
If you really want to advocate for PySpark in this context, you've gotta contend with everything the article brings up:
- More people will be able to understand your code
- Future proofed, with automatic speed improvements and ‘autoscaling’
- Make data typing someone else’s problem
- Simpler maintenance, with less dependency management
- Compatibility with good practice software engineering
- SQL is more expressive and versatile than it used to be
[1] https://mariadb.com/kb/en/quote/
You can then specify columns as a query, group_concat the result and evaluate resulting statement [2].
https://mariadb.com/kb/en/execute-immediate/
I hope that helps.
Even though it’s improved a lot the testing situation in sql is still not where a more traditional programming language is, modularity comes either in the form of ever nested cte, stored procs or dbt style templates. And sql types are wholly dependent on the sql engine, which can lead to wacky transforms.
Sql is great for adhoc analysis. If something isn’t adhoc, there is almost always a better tool.
Additionally, in this day and age where enriching the data by running it through some ML model isn't that rare, doing it in SQL by exposing it through an API and invoking some UDF on a per-row basis is extremely inefficient due to network RTT. In my opinion it is much better to use something like Apache Beam and load the model in memory of your workers and run predictions "locally" on batches of data at the time.
On the other hand I see the value in expressing "simple" logic in SQL, especially when joining a series of tabular sources. That's why I am super happy with Apache beam SQL extensions (https://beam.apache.org/releases/pydoc/2.30.0/apache_beam.tr...) which, IMHO, has the benefits of both worlds.
I do this using sql
1. Extracting an 'ephemeral model' to different model file
2. Mock out this model in upstream model in unit tests https://github.com/EqualExperts/dbt-unit-testing
3. Write unit tests for this model.
This is not different than regular software development in a language like java.
I would argue its even better better because unit tests are always in tabular format and pretty easy to understand. Java unit tests on other hand are never read by devs in practice.
> in this day and age where enriching the data by running it through some ML model isn't that rare,
Still pretty rare, This constitutes a very minor percentage of ETL in an typical enterprise.
You should get to know better java developers. :-)
sql is so pretty and so very readable and maintable if done by people who know what they are doing.
See my comment here https://news.ycombinator.com/item?id=34580675
I don't use nested cte/stored procedures . I simply extract extract it to a new file and mark it 'ephemeral' in dbt, no different than what you'd do in a regular programming language.
Depending on how big your data & org is - orgs solve this by writing their own query engines that can query many different data formats.
If you want to use the same logic / language to query some exotic dataset for specific use cases - it can often be worth it to write a custom engine that can do it - rather than expect all end-users to learn the ins & outs of the other databases & datasets (not to mention, to learn something beside SQL).
Instead, a single team (the query engine owners) can optimize the query engine - rather than individual users trying to optimize every script individually.
Your users can become masters of their engine / language (SQL) - because it can be used for the vast majority of cases.
For many reasons - you might want to store data in a format that MySQL / Postgres does not support natively (see Google Spanner). But, ideally, you'd still be able to leverage the fact that almost every programmer in the world can write SQL.
We don't use any of them though, we have custom C# ingestion code that can talk to most databases and Odata API's. .Net's lazy collections make it quite easy to create a consumer/producer pipeline for streaming large datasets without too much overhead.
Why would this be more predictable in something you handroll vs using something like trino.
For larger data, i'm a fan of writing everything in SQL, and executing it using AWS Athena (a Presto engine), which is extremely cheap.
If you have even larger data, you can consider AWS Glue (steering clear of any aws specific stuff and literally using it as spark SQL as a service). For many simple cases you don't really need to know any Spark, and you're just submitting SQL to AWS for execution.
But all of this assumes you're storing your data as files on disk (e.g. parquet). I'm less experienced in running everything in more traditional SQL engines like MS SQL server or postgres.
I am working on the (parallel, distributed) SQL engine and you can't even fathom how often I see SQL queries disguised-as/hand-compiled-into for loops in C++. Essentially, whatever SQL engine works on can and should be expressed as data tables and their processing and transformation should also be SQL-expressed.
shameless plug but if you want an open source/YC backed solution for data movement pipelines try Airbyte https://news.ycombinator.com/item?id=34533429 :)
- orchestration, or what happens when
- data manipulation, aka the T in ETL
- data movement, getting data from A to B
Orchestration? Please do not use dynamic SQL, you're digging a hole you'll never get out of. Moreover, SQL has zero support for time-based triggers so you'll need a secondary system anyway.
Manipulating the data? Sure, use SQL, as long as you're operating on data from one database. Trying to collate data from multiple databases may have unpredictable performance issues even in the engines that support it.
Moving data? There are much better options. And the same caveat as for orchestration applies here: every RDBMS vendor has its own idea of what external query's should look like (openrowset, external tables, linked servers) with its own performance considerations.