7 ms·

Author here: All good points - I should have been more specific: I'm really talking here about the data transformation logic. I live in a world where raw dat

by RobinL 4y ago

Author here: All good points - I should have been more specific: I'm really talking here about the data transformation logic. I live in a world where raw data generally gets loaded into a data lake, and data engineering proceeds from that point - but appreciate that's not the general case. 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

mcdonje 4y ago
In a datalake, PySpark should be the default for large operations with multiple transformations. Since you're essentially working with jupyter notebooks, it allows you to lay out your transformation steps in a way that's more clear to the reader what each step does than with SQL. 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.
- simonw 4y ago
  I worry that if I write significant amounts of code in pyspark it will have fallen out of fashion in ten year's time and someone will end up either rewriting it or running an ancient, unmaintained version of pyspark forever just to keep my stuff working. I'm much less worried about that happening to SQL scripts.
  - mritchie712 4y ago
    In addition to being good at what it does, SQL has Lindy on it's side like no other language used today.
    - rnk 4y ago
      Lindy?
      - mritchie712 4y ago
        https://en.wikipedia.org/wiki/Lindy_effect https://en.wikipedia.org/wiki/Lindy_effect
        rnk 4y ago
        Thanks, that's an idea that makes a certain sense. The counter point is that technical things get replaced quickly at times when something vastly superior arrives. We don't use cpm computers or apple IIs any more. I think saying sql has the lindy affect is a clever slam against it. We don't use system 360 assembler any more. X86 seemed to be certain to own the world, maybe arm or risc-v will usurp it.
  - rnk 4y ago
    PySpark is such a performance killer. How many startups will there be that just have a goal to improve performance and reduce cost. It locks you into a small number of platforms (bc many dbs don't have an impl and details vary), every database vendor eventually struggles to make their own implementation.
  - afpx 4y ago
    Spark is useful in that it’s pretty cheap and flexible. I can grind through 10 TB of data for like $50 and a few hours. If my experiments work, I can use it as a component in other projects. Plus, I can use (slightly non-standard) SQL right in pyspark, if I want to. And, it often performs faster because of the query optimizer. 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).
    - dangwhy 4y ago
      > I can grind through 10 TB of data for like $50 Is this hosted databricks ?
      - afpx 4y ago
        EMR
    - berkle4455 4y ago
      $50 to analyze 10TB over the course of a few hours is an insanely expensive and inefficient setup.
      - afpx 4y ago
        Not analyze. Transform. I'm not talking about a query. 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.
        berkle4455 4y ago
        a single node of clickhouse would likely be sufficient for a fraction of the cost and time. use attached nvme disks (ebs on aws) or a physical local disk.
  - btilly 4y ago
    About 20 years ago I wrote a bunch of complex reports using SQL. The engineer who came after me tried briefly to understand it, decided it all needed to be rewritten in "a real language", broke all the reports, and they never really got them back. So merely writing in a language that we believe will be around forever does not prevent your code from being rewritten.
    - remus 4y ago
      I think that's always a risk (the old "which idiot wrote this? clearly needs a full rewrite!" followed by the rewriter rediscovering all the bugs the original had fixed), but with a well established language like SQL hopefully the risk is lower.
      - btilly 4y ago
        The fact is that, word for word, SQL is incredibly more efficient for reporting than most programming languages. But since few programmers treat it as a real language, it tends to be written without formatting. Which makes it hard to read. 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.)
        xupybd 4y ago
        Many developers, good developers haven't worked with complex SQL queries. 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.
        rvanlaar 4y ago
        While I agree with the sentiment, in practice I haven't seen it go over well. I find the tooling around SQL to be severely lacking.
- ramraj07 4y ago
  Either you work with data that’s not really big data or you have some super awesome engineers who know spark very well, because spark is pretty much not declarative as much as it says so. You have to constantly battle crap like GC errors, data skew, etc. Even on supposedly managed solutions like databricks. As opposed to solutions like snowflake which are a lot more declarative (nothing is perfect of course).
  - hobs 4y ago
    And the vendors are happy to tell you to throw more cores at it instead of grapple with the core issues. 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.
- dangwhy 4y ago
  As someone who is written tons of spark code. I disagree is that is it somehow more immune to code rot than sql. There are couple of famous examples https://shopify.engineering/build-production-grade-workflow-sql-modelling https://shopify.engineering/build-production-grade-workflow-...
- camgunz 4y ago
  This doesn't really respond to the article. The argument here is "jupyter notebooks are clearer", but that's pretty subjective, and the article specifically addresses this kind of point: "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
- pushingice 4y ago
  This isn't even an either/or proposition. You can inline SQL in pyspark just fine: df = spark.sql("...")
alexott 4y ago
Even for data transformation logic, SQL isn’t the best choice. How would you handle the case when you need to apply the same transformations to few dozens or hundreds columns?
- cced 4y ago
  I'm not sure I understand the question, you can create tables/views based on others that have the transformations applied to them, tools like dbt[1] make this easy. [1]: https://www.getdbt.com/ https://www.getdbt.com/
- thesz 4y ago
  Some SQL engines support generating and evaluating queries. I stumbled upon a function in MariaDB code base that is introduced and keps specifically for that use case (it returns properly quoted SQL value as a string, including "NULL" string for null values). It is aptly named QUOTE [1]. [1] https://mariadb.com/kb/en/quote/ 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/ https://mariadb.com/kb/en/execute-immediate/ I hope that helps.

by RobinL 4y ago

mcdonje 4y ago

In a datalake, PySpark should be the default for large operations with multiple transformations. Since you're essentially working with jupyter notebooks, it allows you to lay out your transformation steps in a way that's more clear to the reader what each step does than with SQL. 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.

simonw 4y ago
I worry that if I write significant amounts of code in pyspark it will have fallen out of fashion in ten year's time and someone will end up either rewriting it or running an ancient, unmaintained version of pyspark forever just to keep my stuff working. I'm much less worried about that happening to SQL scripts.
- mritchie712 4y ago
  In addition to being good at what it does, SQL has Lindy on it's side like no other language used today.
  - rnk 4y ago
    Lindy?
    - mritchie712 4y ago
      https://en.wikipedia.org/wiki/Lindy_effect https://en.wikipedia.org/wiki/Lindy_effect
      - rnk 4y ago
        Thanks, that's an idea that makes a certain sense. The counter point is that technical things get replaced quickly at times when something vastly superior arrives. We don't use cpm computers or apple IIs any more. I think saying sql has the lindy affect is a clever slam against it. We don't use system 360 assembler any more. X86 seemed to be certain to own the world, maybe arm or risc-v will usurp it.
- rnk 4y ago
  PySpark is such a performance killer. How many startups will there be that just have a goal to improve performance and reduce cost. It locks you into a small number of platforms (bc many dbs don't have an impl and details vary), every database vendor eventually struggles to make their own implementation.
- afpx 4y ago
  Spark is useful in that it’s pretty cheap and flexible. I can grind through 10 TB of data for like $50 and a few hours. If my experiments work, I can use it as a component in other projects. Plus, I can use (slightly non-standard) SQL right in pyspark, if I want to. And, it often performs faster because of the query optimizer. 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).
  - dangwhy 4y ago
    > I can grind through 10 TB of data for like $50 Is this hosted databricks ?
    - afpx 4y ago
      EMR
  - berkle4455 4y ago
    $50 to analyze 10TB over the course of a few hours is an insanely expensive and inefficient setup.
    - afpx 4y ago
      Not analyze. Transform. I'm not talking about a query. 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.
      - berkle4455 4y ago
        a single node of clickhouse would likely be sufficient for a fraction of the cost and time. use attached nvme disks (ebs on aws) or a physical local disk.
- btilly 4y ago
  About 20 years ago I wrote a bunch of complex reports using SQL. The engineer who came after me tried briefly to understand it, decided it all needed to be rewritten in "a real language", broke all the reports, and they never really got them back. So merely writing in a language that we believe will be around forever does not prevent your code from being rewritten.
  - remus 4y ago
    I think that's always a risk (the old "which idiot wrote this? clearly needs a full rewrite!" followed by the rewriter rediscovering all the bugs the original had fixed), but with a well established language like SQL hopefully the risk is lower.
    - btilly 4y ago
      The fact is that, word for word, SQL is incredibly more efficient for reporting than most programming languages. But since few programmers treat it as a real language, it tends to be written without formatting. Which makes it hard to read. 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.)
      - xupybd 4y ago
        Many developers, good developers haven't worked with complex SQL queries. 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.
      - rvanlaar 4y ago
        While I agree with the sentiment, in practice I haven't seen it go over well. I find the tooling around SQL to be severely lacking.
ramraj07 4y ago
Either you work with data that’s not really big data or you have some super awesome engineers who know spark very well, because spark is pretty much not declarative as much as it says so. You have to constantly battle crap like GC errors, data skew, etc. Even on supposedly managed solutions like databricks. As opposed to solutions like snowflake which are a lot more declarative (nothing is perfect of course).
- hobs 4y ago
  And the vendors are happy to tell you to throw more cores at it instead of grapple with the core issues. 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.
dangwhy 4y ago
As someone who is written tons of spark code. I disagree is that is it somehow more immune to code rot than sql. There are couple of famous examples https://shopify.engineering/build-production-grade-workflow-sql-modelling https://shopify.engineering/build-production-grade-workflow-...
camgunz 4y ago
This doesn't really respond to the article. The argument here is "jupyter notebooks are clearer", but that's pretty subjective, and the article specifically addresses this kind of point: "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
pushingice 4y ago
This isn't even an either/or proposition. You can inline SQL in pyspark just fine: df = spark.sql("...")

alexott 4y ago

Even for data transformation logic, SQL isn’t the best choice. How would you handle the case when you need to apply the same transformations to few dozens or hundreds columns?

cced 4y ago
I'm not sure I understand the question, you can create tables/views based on others that have the transformations applied to them, tools like dbt[1] make this easy. [1]: https://www.getdbt.com/ https://www.getdbt.com/
thesz 4y ago
Some SQL engines support generating and evaluating queries. I stumbled upon a function in MariaDB code base that is introduced and keps specifically for that use case (it returns properly quoted SQL value as a string, including "NULL" string for null values). It is aptly named QUOTE [1]. [1] https://mariadb.com/kb/en/quote/ 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/ https://mariadb.com/kb/en/execute-immediate/ I hope that helps.