What SQL Analysts Need to Know About Python (2016) • Hacker News Board

Re: What SQL Analysts Need to Know About Python (2016)

#111

Post by avmich » Wed, Nov 06, 2019, 12:19 AM UTC

bshipp wrote: post #108

avmich wrote: post #100

Earlier quoted context omitted.

> But as a sketchpad for handling a problem? Excel is pretty hard to beat. I'd recommend trying J. Download here - https://code.jsoftware.com/wiki/System/Installation#Installa... . There is, of course, the famous barrier to entry, but the result, unsurprisingly, is also respectable.

Thanks for the recommendation! I've honestly never heard of J and I'll take a look at it.

To understand what's going on I'd recommend "J for C programmers" (see links on Jsoftware site) and also the mailing list - it could be very helpful.

Re: What SQL Analysts Need to Know About Python (2016)

#112

Post by philipov » Wed, Nov 06, 2019, 8:25 AM UTC

goatinaboat wrote: post #104

starpilot wrote: post #96

Earlier quoted context omitted.

Dang, Ibis doesn't support Redshift or SQL Server. I'm also having trouble understanding what it really is - it's an entire framework for big data it seems and not just a translator. What I'd really like is just that, something that turns pandas dataframe operation into ANSI SQL. So input pandas2sql('tablename["col"]') -> "select col from tablename". Something really simple to use.

Pandas has from_sql and to_sql methods that are compatible with SQLAlchemy if you insist on using an ORM, that gets you most of the way there...

SQLAlchemy is more than just an ORM. It also has sql expression language, for writing queries using python without using any ORM features.

Re: What SQL Analysts Need to Know About Python (2016)

#113

Post by Doxin » Wed, Nov 06, 2019, 9:30 PM UTC

csours wrote: post #110

Doxin wrote: post #107

Earlier quoted context omitted.

Turns out a good database is really good at data munging. A solution for postgresql might look something like this: CREATE VIEW my_table_improved AS SELECT SUBSTRING(the_column, 0, 8) as col1, SUBSTRING(the_column, 8, 8) as col2, SUBSTRING(the_column, 16, 8) as col3 FROM my_table After which you can query my_table_improved as a normal table. col1, col2, and col3 contain the data split out from the_column. In practice…

If it was just one column, that would be easy enough. Or if one object was on one row, it would be easy enough. In my case, up to 6 rows could be required to represent one object, and I had to slice 3 columns with an arbitrary number of slices. Oh, and there were two types of sub-record per object, and they had to be processed in database order.

Well, as long as you can build a query to get the data in the right format (which you almost inevitably can) you can make a view out of it. But honestly the true solution here would be to migrate away from such a brain damaged format.

Re: What SQL Analysts Need to Know About Python (2016)

#114

Post by Whut » Thu, Nov 07, 2019, 7:24 PM UTC

danso wrote: post #83

Whut wrote: post #74

Earlier quoted context omitted.

I'm a data engineer and I learned SQL before I learned python. I started out as a marketing specialist (mostly working in Google AdWords), then I graduated up to BI Analyst, and eventually to data engineer. I've picked up 100% of my python in my current job.

What did you think of Python, and was it difficult to grasp the differences between the two languages/paradigms? I ask because, for myself, any general language I pick up (Ruby,Python,JS,R), I have a good idea of underlying concepts like memory pointers and garbage collection, even if it's all abstracted by the language. With SQL, I have an incredible ignorance of the most basic programmatic concepts, like how to def…

Yeah, the learning curve was pretty steep for me. I've been learning some C# for my job, and it's been a lot easier after learning basic Python. SQL is kind of weird in the sense that starting out is very very very easy, but mastering it is incredibly hard. I've been writing SQL everyday for about 3 years and I'm still blown away with what the SQL experts at my company can do with the language. I see presentations at conferences that make me feel like an absolute beginner.

Re: What SQL Analysts Need to Know About Python (2016)

#115

Post by andybak » Fri, Nov 08, 2019, 1:23 AM UTC

overcast wrote: post #20

andybak wrote: post #13

Earlier quoted context omitted.

> particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs (they aren't). No need to check the SQL logs directly. First thing I learned about optimising Django was to check django-debug-toolbar to see how many queries were being generated per page. This is fairly common knowledge. However. I don't often bother because SQL calls ar…

When you have millions or billions of rows, SQL calls absolutely become a huge bottleneck. I deal with this all the time from shortsighted developers at the office in other ORM environments. Most recent being a SELECT...NOT IN('a','b','c'), causing an INDEX SCAN on 200 million rows, and then complaining it takes 6 minutes to run. Look at Django's select_related. It's one of those if you don't understand what's happen…

> When you have millions or billions of rows, SQL calls absolutely become a huge bottleneck.

Not necessarily. It's the specifics of the queries not the number of rows.

Which is why you measure before you optimize.