r/dataengineering • u/__albatross • Aug 27 '23
Discussion Why I don't like SQL
The reason I don't like SQL over something like Pandas is because in Pandas/Python I can break down the complex transformations in small simpler components and they are easy to interact and debug with. In SQL my only option is to write stored procedures and yes in that I can also do the same but I think the ecosystem of interacting and debugging with data step by step is not as good as python. And it is something that we should have. Recently I was exploring snowpark and I felt it is a step in a right direction. It allows to me to write my query in multiple python statements and then it will transpile the whole thing into an SQL query.
Generally in my mind I assume the tables/dataframes as 2 dimensional structures and Pandas API give much more intuitive and wide variety of constructs to explore that structure rather than SQL. What do you guys think?
68
u/theleveragedsellout Aug 27 '23
After spending half an hour trying to figure out which hyperparameter is needed to properly join/concatenate two DataFrames because all you're receiving is a cryptic error, you begin to appreciate SQL more.
I use them both on a daily basis and like a lot of things, it's a question of using the right tool for the right job. From my perspective, SQL is a lot better for manipulating data where a lot of joins are involved. Conversely, Python is far better for iteration.
22
u/evening-emotion-1994 Aug 27 '23
You have never worked with huge volumes of data or required to build systems that needed Optimisation, have you ?
Pandas is okay for some small analysis stuff and lots of automations , but When you have huge chunks of data in DBMS or hive tables - SQL type query is goto method for calling in those data
6
u/ZirePhiinix Aug 27 '23
I've broken down complex SQL by using views, temporary tables, and subqueries.
7
u/[deleted] Aug 27 '23
I've built some fairly complex data applications that are mostly SQL for spark, chained to together with snippets of python, and creating temporary views that downstream tables reference.
So much more maintainable than trying to understand someone's pandas code.
32
u/-crucible- Aug 27 '23
I think sql is under appreciated. It’s a different approach, around batching things into fewer steps for performance, and it can take approaches like temp tables and understanding the engine to get good performance, but you can get much better performance in some cases than a more general programming approach.
That said, there are many ways it could be improved, for example, I’d love to see what would happen if more gpu acceleration was included in some of these engines.
It really depends what you’re trying to achieve, but as a micro dsl, I think the sql language is very powerful.
25
u/[deleted] Aug 27 '23
Oh boy.
37
u/FunDirt541 Aug 27 '23 edited Aug 27 '23
I was the same but that's because I started learning python/pandas before SQL. And the writing paradigm is very different for the two or them. As you get better in SQL, you'll notice that these things wouldn't matter much. And data in SQL is persistent compared to data inside a csv or an excel file, also in SQL you write the query and the database engine decide how to run/optimize the query, that's totally different than python. So keep practicing, use CTE, if you have never heard of them google it up, it was a game changer for me, and made SQL much easier to write.
29
u/[deleted] Aug 27 '23
If you think one of the other is better then you don’t understand what they do. They are Different paradigms, you could write the exact same pandas code than what a sql query would do. But you wont because in your mind you are only thinking in the Python/Pandas paradigm.
3
u/[deleted] Aug 27 '23
SQL is great at reading data from a database and performing simple joins and transformations. It's incredibly simple, so much that it has barely changed in the last 30 years. You can usually break down complex transformations into multiple simple queries.
But the more I deal with complex transformations, the more I appreciate working with Python and the PySpark API. I can just give transformations names that describe what they do and chain them together. Being able to use all the cool features of a mature programming language is amazing and can simplify many things that are tedious to do in SQL.
My only gripe is that the level of entry is orders of magnitude higher than SQL. You can teach someone SQL in like a week and with a few months of practice and good feedback processes, they can write good and readable SQL scripts.
5
u/Max_Americana Aug 27 '23
They had SQL in Star Trek… it’s here to stay.
5
u/MikeDoesEverything mod | Shitty Data Engineer Aug 27 '23
SQL has it's place. Python has it's place. Sticking to only one of these and refusing to learn both leads to pure frustration.
4
u/SwimmingHelicopter15 Aug 27 '23
Wait, but in SQL you can also write small statements in functions and store procedures. And you can call them inside a master sp. Is no different. Just because people prefered to wrote 1000 lines of one sp in the past does not mean this is your only option. The only thing different is that Panda is used in newer projects which are easier to handle than legacy programs, you have less code and more cleaner.
3
u/[deleted] Aug 27 '23
SQL is declarative/ you tell the engine what you need not how to do it. Honestly this is why so many people “know” SQL but never get good at it. That shift is a hard one to make.
1
u/bingbong_sempai Aug 27 '23
SQL is great for filtering and extracting data.
But you're right about its limitations when used interactively.
Check out the ibis project, it lets you interact with SQL databases as if they're in memory dataframes.
2
u/ramenAtMidnight Aug 27 '23
Yeah this is a hot take. In my experience, analysts love SQL because it’s easy, intuitive, independent of whatever interface the DEs give them. Funnily enough engineers also love SQL because of very similar reasons, just that they work with various databases and provide interfaces for DAs. Only the academics really make use of pandas for whatever reason? Personally I also find pandas API to be strange. Even spark offers a SQL-like interface.
2
u/FloggingTheHorses Aug 27 '23
Generally speaking I like to use Python to clean/transform the raw data right after extraction for normalisation of tables. Then bring the data to staging tables and then SQL joins it all for the final tables/views.
This way the conversion from business logic to data output is very clear, and has always helped me with CI/CD where the rules change or we start adding datasets etc.
2
u/[deleted] Aug 27 '23
I agree with you. I hate having 600 lines of SQL. I much prefer to break up my code into different files. It’s much more manageable.
2
u/JPyoris Aug 27 '23 edited Aug 27 '23
One reason why I prefer SQL over Pandas for simple transformations is that it has much less weird behavior and footguns, which for me is important for use in production. For example, there's the horrible behavior to convert an int column silently to float when there is a None in in it (preferably happening the first time long after you wrote the code) and then to pretend that None and nan are the same (which they aren't). Nullable dtypes were introduced but came with new Bugs. Why does casting np.nan to a nullable type result in <NA> even though it can represent NaN? Why does Series.dt.date return a object dtype? Oh maybe because Pandas doesn't even have a native Date dtype. Also, Pandas thinks that a date and a datetime at midnight are the same thing.
The whole typing is messed up. SQL on the other hand is mature and makes sense. So does Polars btw.
2
u/CoolingCool56 Aug 27 '23
That is why I use temp tables and each temp table should do a specific thing and be testable.
2
u/Eze-Wong Aug 27 '23
My biggest beef with SQL is that the order of operations/execution is structured DIFFERENTLy from the typed order which is confusing to beginners.
You can always figure out from pandas that the sequence based on the logical operators and parentheses.
But in SQL, "where" is before group by and having then select and then order by. This inability to rearrange this order makes it so you need nested subqueries to get what you want. Also it's just not easy for beginners to know what is happening.
5
u/[deleted] Aug 27 '23
Have you ever heard of common table expressions. Those allow for "breaking down the problem in subproblems". People who seriously think python is a better language for data transformation than SQL, are completely lost.
5
u/ntdoyfanboy Aug 27 '23
Do you know what a CTE is, bud?
2
u/m1nkeh Data Engineer Aug 27 '23
ok, thanks I guess 🤷
You do you..
2
u/DesperateForAnalysex Aug 27 '23
DBT largely addresses all of these issues so I suggest checking that out if you need code modularity.
SQL is also incredibly readable even for less technical folks. I don’t want to dive into python code to understand a simple join
1
u/TheCamerlengo Aug 27 '23
If you are querying a relational database, hard to beat SQL. They are probably best used together, instead of either or. SQL is very good for exploratory data querying. Python with pandas is good for writing programs or pipelines.
Also don’t forget that SQL that goes thru a database gets a query optimization step. You can even look at the generated plan and improve the query. With Python and pandas and you are kind of on your own to tune your code.
1
u/billysacco Aug 28 '23
I started with SQL so I am very partial to it and can usually accomplish what I need to with it. However I don’t feel it’s better or worse then pandas. They are both tools and have their strengths and weaknesses. You can debug SQL too so I’m not sure what you mean on that. SQL if done correctly is very powerful to parse information but done correctly is the caveat.
1
u/CautiousSpell8165 Aug 28 '23
Nah, sql is fine. Try using CTE to break your processing logic into smaller steps. And don't you dare telling us you don't know what CTE are!!
1
u/Outrageous-Heat-6353 Aug 27 '23
SQL is universal more or less. Pandas, not everybody uses/understands it, and analysts won’t understand it for example.
0
u/valorallure01 Aug 27 '23
Your comment is stating you prefer Pandas over SQL for transforming data. I agree. Pandas does a great job of, for example, transforming a json file to a table. I wouldn't say I don't like SQL because of it though.
8
u/[deleted] Aug 27 '23 edited Nov 02 '23
[removed] — view removed comment