3 ms·

> SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases,

> SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no easy interoperability with other tools, limited editor/IDE support, etc. You're entitled to your opinion and tooling choices of course, but the problem is you don't know SQL.

dunefox 5y ago
That could be said about any tool, framework, language, library, etc. Invest enough time and you can do everything with malbolge but that doesn't mean it doesn't suck.
pandasusr 5y ago
I am a pandas user considering refactoring part of my ETL pipeline to SQL. I see the trade off as memory efficiency vs expressiveness, and for simple queries on big data, SQL wins. Would you disagree that Pandas/Python is more expressive than SQL? I’m less experienced in SQL but based on my limited experience there, it seems Pandas is clearly more expressive. What is the SQL equivalent of Pandas .apply(lambda x) ?
- higeorge13 5y ago
  Please define an example lambda function, in order to see whether there is an sql equivalent. Imo >90% of the data issues i have seen, can be solved with sql queries. I have seen some in the finance sector which would require super complex udfs, but other than these, sql is the first choise to solve a data issue.
  - pandasusr 5y ago
    I do work in finance sector and need complex functions :) And it’s interesting that pandas was invented at a hedge fund, AQR. I agree that SQL may be better for vanilla BI.
- hodgesrm 5y ago
  ClickHouse has lambdas for arrays. They are very useful. Here's an example. WITH ['a', 'bc', 'def', 'g'] AS array SELECT arrayFilter(v -> (length(v) > 1), array) AS filtered ┌─filtered─────┐ │ ['bc','def'] │ └──────────────┘ The lambda in this case is a selector for strings with more than one character. I would not argue that they are as general as Pandas, but they are might useful. More examples from the following article. https://altinity.com/blog/harnessing-the-power-of-clickhouse-arrays-part-2 https://altinity.com/blog/harnessing-the-power-of-clickhouse...
mixmastamyk 5y ago
A more useful comment would be to illustrate how. I'd like to know as well as I need to reuse queries in SQL occasionally but am not an expert. Currently I believe learning pl/pgsql is the answer, but even it reeks of punch-cards and other sixtiesisms :-). Tough sell when you're used to Python etc.
default-kramer 5y ago
"No composability or code reuse" is definitely a valid criticism of SQL. Check out the very first example of my personal project https://docs.racket-lang.org/plisqin/Read_Me_First.html https://docs.racket-lang.org/plisqin/Read_Me_First.html and let me know how you would implement something akin to `(CategoryName p)` and `(TotalSales p)` in SQL. A view does not count, because then you either have one view per derived field and that is very cumbersome to consume, or you have one view with all the derived fields and you will end up paying performance costs for fields that you are not using.
- ok123456 5y ago
  Don't views and materialized views give you reuse? Can't you do composibility with foreign references?
  - neurocean 5y ago
    Sure, while views may be fine for small projects, using them for larger projects like data warehousing is usually a mistake you'll come to regret. SQL views are rarely unit tested so you always end up in a regression nightmare when you need to make updates. If you're going to go pure SQL, you should use something like dbt.

dunefox 5y ago

That could be said about any tool, framework, language, library, etc. Invest enough time and you can do everything with malbolge but that doesn't mean it doesn't suck.

pandasusr 5y ago

I am a pandas user considering refactoring part of my ETL pipeline to SQL. I see the trade off as memory efficiency vs expressiveness, and for simple queries on big data, SQL wins. Would you disagree that Pandas/Python is more expressive than SQL? I’m less experienced in SQL but based on my limited experience there, it seems Pandas is clearly more expressive. What is the SQL equivalent of Pandas .apply(lambda x) ?

higeorge13 5y ago
Please define an example lambda function, in order to see whether there is an sql equivalent. Imo >90% of the data issues i have seen, can be solved with sql queries. I have seen some in the finance sector which would require super complex udfs, but other than these, sql is the first choise to solve a data issue.
- pandasusr 5y ago
  I do work in finance sector and need complex functions :) And it’s interesting that pandas was invented at a hedge fund, AQR. I agree that SQL may be better for vanilla BI.
hodgesrm 5y ago
ClickHouse has lambdas for arrays. They are very useful. Here's an example. WITH ['a', 'bc', 'def', 'g'] AS array SELECT arrayFilter(v -> (length(v) > 1), array) AS filtered ┌─filtered─────┐ │ ['bc','def'] │ └──────────────┘ The lambda in this case is a selector for strings with more than one character. I would not argue that they are as general as Pandas, but they are might useful. More examples from the following article. https://altinity.com/blog/harnessing-the-power-of-clickhouse-arrays-part-2 https://altinity.com/blog/harnessing-the-power-of-clickhouse...

mixmastamyk 5y ago

A more useful comment would be to illustrate how. I'd like to know as well as I need to reuse queries in SQL occasionally but am not an expert. Currently I believe learning pl/pgsql is the answer, but even it reeks of punch-cards and other sixtiesisms :-). Tough sell when you're used to Python etc.

default-kramer 5y ago

"No composability or code reuse" is definitely a valid criticism of SQL. Check out the very first example of my personal project https://docs.racket-lang.org/plisqin/Read_Me_First.html https://docs.racket-lang.org/plisqin/Read_Me_First.html and let me know how you would implement something akin to `(CategoryName p)` and `(TotalSales p)` in SQL. A view does not count, because then you either have one view per derived field and that is very cumbersome to consume, or you have one view with all the derived fields and you will end up paying performance costs for fields that you are not using.

ok123456 5y ago
Don't views and materialized views give you reuse? Can't you do composibility with foreign references?
- neurocean 5y ago
  Sure, while views may be fine for small projects, using them for larger projects like data warehousing is usually a mistake you'll come to regret. SQL views are rarely unit tested so you always end up in a regression nightmare when you need to make updates. If you're going to go pure SQL, you should use something like dbt.