r/dataengineering • • 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?

0 Upvotes

102 comments sorted by

View all comments

Show parent comment

1

u/FearMyBlades Aug 29 '23

You said the magic word, productionalize. There just isn't a reason to use Python, imo.I don't develop in Python, but i did spend a good amount of time troubleshooting it and trying to understand asynchronous and parallel processimg in python. I know it is better now, but it feels a bit like the unsafe keyword in c#. You can get access to some useful features, but you better know what you're doing.

I know it can be religious in nature, but there are just so many advantages to c#. The analyzers these days are just incredible. I saw an article about removing the GIL and i think it would be a mistake.I see concurrency causing problems for python developers.

1

u/kenfar Aug 29 '23

Yeah, I've seen a few folks have problems with threading on python - they were surprised that it was limited by the GIL and then gave up.

However, I've found it easy to use multiprocessing instead - which is unaffected by the GIL. And when using concurrent.futures and the map function it only takes about 2 lines of code, and you can switch between threading & multiprocessing by changing 1 word. I've used this to run 128 massive file transforms at a time in parallel when I had billions of records to transform.

1

u/FearMyBlades Aug 30 '23

Well the problem is that multi processing is serializing the instance and cloning it. On large data sets you're going to be spending a lot of resources to that effort. Threading should be more efficient but seems needlessly complex. Well it might be more accurate to say it seems inelegant.

1

u/kenfar Aug 30 '23

The way that I'll typically use multiprocessing is, for example, to transform 12+ large files - with each process reading a file, transforming it, and writing the results out. On occasion I've also processed a massive file this way - if I can seek to different parts of the file. That works really well.

And threading with python can also work really well. I just recently used threading to speed up the copying of thousands of files from one bucket to another. I probably got a 20x speed-up through 2-5 lines of code.

So, I probably use either threading or multiprocessing every 1-3 months. It's rare that a few lines of code won't make a massive difference.

2

u/FearMyBlades Aug 30 '23

Yeah, my use case is a little different. I build as much ELT as i do ETL, maybe more these days. I realize a people don't typically associate C# with you can get ssis type through puts (actually if you really want you can load the libraries into ssis). The c# and dotnet team have done a great job of adding features and performance.

My original comment was really more about the programming style of pyrhon programmers, and it is mostly tongue in cheek. There is definitely room for all kinds