Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Is a Dataframe Just a Table? (2019) [pdf]

plateau-workshop.org

Post reply on HN
Print view View original

111–120 of 120 posts

Re: Is a Dataframe Just a Table? (2019) [pdf]

#111

Post by jstrong »

somurzakov wrote: post #84
jstrong wrote: post #81

Earlier quoted context omitted.

is the database server running on a $700 workstation? how many rows? what types of queries? what is a typical query execution time? interested in your response because I generally find RDBMS performance quite poor, although I've never used SQL server. Pandas gets to be fairly painful after the data size hits 10GB, in my experience. I do think you are missing how pandas fits into a data exploration pipeline for someon…

Commodity virtualized server with 4 cores and 8GB of RAM, storage is on NAS. We have hundreds of these typical SQL instances. db has lot of rows, around 20k rows logged per minute and the events are logged 24/7 three years. Again, because the schema is well designed, I use clustered index on Date to filter and analyze and the engine actually never reads the whole db all the time. It actually read only the pages I nee…

ok, so 20k * 60 minutes * 24 hours * 365 days * 3 years = 31,536,000,000 rows. You are querying 31.5 billion rows on a machine with 4 cores and 8gb ram? Are queries that return in 5-10 seconds running over the entire table? or small portions of it?

Re: Is a Dataframe Just a Table? (2019) [pdf]

#112

Post by jstrong »

somurzakov wrote: post #84
jstrong wrote: post #81

Earlier quoted context omitted.

is the database server running on a $700 workstation? how many rows? what types of queries? what is a typical query execution time? interested in your response because I generally find RDBMS performance quite poor, although I've never used SQL server. Pandas gets to be fairly painful after the data size hits 10GB, in my experience. I do think you are missing how pandas fits into a data exploration pipeline for someon…

Commodity virtualized server with 4 cores and 8GB of RAM, storage is on NAS. We have hundreds of these typical SQL instances. db has lot of rows, around 20k rows logged per minute and the events are logged 24/7 three years. Again, because the schema is well designed, I use clustered index on Date to filter and analyze and the engine actually never reads the whole db all the time. It actually read only the pages I nee…

I just did a simple benchmark: 67 million rows, integers, 4 columns wide, with postgresql 10 and pandas.

pg 10

  huge=# \timing on
  Timing is on.
  huge=# copy lotsarows from '~/src/lotsarows/data.csv' with csv header;
  COPY 67108864
  Time: 85858.899 ms (01:25.859)
  huge=# select count(*) from lotsarows;
    count                               
  ----------                            
   67108864                             
  (1 row)                               
                                        
  Time: 132784.743 ms (02:12.785)       
  huge=# vacuum analyze lotsarows;      
  VACUUM                                
  Time: 185040.485 ms (03:05.040)       
  huge=# select count(*) from lotsarows;
    count                               
  ----------                            
   67108864                             
  (1 row)                               
                                        
  Time: 48622.062 ms (00:48.622)        
  huge=# select count(*) from lotsarows where a > b and c
pandas

  In [2]: import pandas as pd                                           
                                                                        
  In [3]: %time df = pd.read_csv('data.csv')                            
  CPU times: user 34.1 s, sys: 4.49 s, total: 38.6 s                    
  Wall time: 38.7 s                                                     
                                                                        
  In [4]: %time len(df)                                                 
  CPU times: user 125 µs, sys: 19 µs, total: 144 µs                     
  Wall time: 166 µs                                                     
  Out[4]: 67108864                                                      
                                                                        
  In [5]: %time ((df['a'] > df['b']) & (df['c']

Re: Is a Dataframe Just a Table? (2019) [pdf]

#113

Post by aeternum »

collyw wrote: post #32
synthc wrote: post #27

Earlier quoted context omitted.

I think a factor in this is that NoSql databases have nice API's that programmers can use to setup tables, do simple queries etc, which makes it much easier to get started. For RDBMS's you have to muck around with connections and SQL, which is more powerful but requires much more ceremony. (Connection pooling, prepared statements etc) The lack of understanding of the relational model is not the limiting factor in my…

What you say is True, but can be solved by an ORM, though that adds an extra layer of complexity

It's not just ORM. Many NoSQL databases allow for real-time events (query subscriptions), a simple security model, built-in data versioning, built-in sharding. You rarely get any of that out-of-the-box for a traditional RDS.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#114

Post by idclip »

nnq wrote: post #20
em500 wrote: post #17

Earlier quoted context omitted.

> Variable number of rows that have all the same type. Why do they need to have the same type? In sqlite a field can have a different type in every record/row. ( https://www.sqlite.org/datatype3.html ). Is having a fixed typed fields fundamental to the concept of a table, or just a property of most SQL implementations?

How do you aggregate without field types? You'd end up writing custom code for untyped map-reduce... How can you code know what data to expect without typed field? Your code size can grow up even 10x if you need to assume that any record can have any shape... You could have more complex and user defined types in an ideal super-SQL, like "int or map:string->bool" etc., but you WANT types. They reduce complexity at all…

Interestingly, the tables i know from random access machines are basically of type

bin -> bin,

So what were saying here that random data is stuff we cant meaningfully into a table?

I think thats a good statement.

Tables are non random data. (Crypto keys are again data, because they are bin -> bin in terms encrypt and decrypt, but the number itself isnt a table)

Re: Is a Dataframe Just a Table? (2019) [pdf]

#115

Post by wodenokoto »

closed wrote: post #99
leto_ii wrote: post #54

> Having many different ways to express the same logic makes it hard for developers to understand programs of heterogeneous styles. Besides having varying ways to express the same simple logic, the sheer number of APIs (> 200) that are not only overloaded but also have default parameters that may change version to version, making it hard to remember the APIs. It's a bit tangential to the main point, but I do agree wi…

I just finished a lengthy analysis of why pandas groupby operations ends up harder to use than R's dplyr or data.table. For example, a grouped filter is very cumbersome in pandas. Interested to hear if you think it gets at the heart of the problem. https://mchow.com/posts/2020-02-11-dplyr-in-python/

> result length dependent operations: to calculate a mean in this case we have to pass the string “mean” to transform. This tells pandas that the result should be the same length as the original data.

    g_students.score.mean()
has the same length as using `g_students.score.transform('mean')` but the result has different values!

I think that is a great point to add to you very interesting article. I wouldn't know which of the two operations is correct to use, and I would not notice anything wrong, or odd with either method in a code review, so this is ripe for adding wrong results in a production environment.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#116

Post by somurzakov »

jstrong wrote: post #111
somurzakov wrote: post #84

Earlier quoted context omitted.

Commodity virtualized server with 4 cores and 8GB of RAM, storage is on NAS. We have hundreds of these typical SQL instances. db has lot of rows, around 20k rows logged per minute and the events are logged 24/7 three years. Again, because the schema is well designed, I use clustered index on Date to filter and analyze and the engine actually never reads the whole db all the time. It actually read only the pages I nee…

ok, so 20k * 60 minutes * 24 hours * 365 days * 3 years = 31,536,000,000 rows. You are querying 31.5 billion rows on a machine with 4 cores and 8gb ram? Are queries that return in 5-10 seconds running over the entire table? or small portions of it?

small portions of it, sometimes an hour or two, sometimes a day, or a week. most of the times there are 5-6 other conditions. pandas will have to full scan entire dataframe for any query to filter, while SQL uses index seek

Re: Is a Dataframe Just a Table? (2019) [pdf]

#117

Post by sgdread »

noelsusman wrote: post #106
somurzakov wrote: post #74

Earlier quoted context omitted.

everything you mentioned can be done easily through database schema. window functions work well in SQL. plots are easily done in any BI solution that hooks up to any database. pandas is just poor man's SQL+BI. pandas stores everything in memory and has many limitations. in SQL Server I can easily churn through terabyte sized database and get the data I need, because the schema is well designed with partitioned tables…

Of course you shouldn't use Pandas to analyze terabytes of data, but most people aren't analyzing terabytes of data.

That's what Spark is for. You can do petabyte-scale jobs... with DataFrames.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#118

Post by kyberias »

rankam wrote: post #83
kyberias wrote: post #31

I was unable to learn R because I couldn't understand what a dataframe is. It was irritating that it wasn't defined clearly and there seemed to be no connection to terminology that was familiar to me (relational databases, SQL tables etc.).

There's no other way to say this without sounding rude, but you weren't unable to learn R because you couldn't understand what a dataframe was - you were unable to learn R because you gave up. Blaming a data structure for the failure seems like a bit of a stretch.

That is what I said. I didn't understand what a dataframe was. Note that I didn't really blame the data structure. I said it was an irritating factor. Someone else might not have been as irritated. But if R was closer to what I already knew, learning would have been easier. For me.

Re: Is a Dataframe Just a Table? (2019) [pdf]

#119

Post by johnthescott »

roenxi wrote: post #14

Tables and data frames are both leaves in the far more fundamental flow that beginners don't pick up because it is too simple - the relational model of data. The real problem is the the basic normal forms are so obvious and simple it is difficult to tell if people designed around them on purpose or stumbled onto the right path. I suspect the distinction between tables and data frames (and arguing about query language…

i am confused. R's "gather" just merges a set of columns values into a single column. how is that beyond the scope of sql?

Re: Is a Dataframe Just a Table? (2019) [pdf]

#120

Post by johnthescott »

lincpa wrote: post #58
kumarvvr wrote: post #38

Earlier quoted context omitted.

Can you elaborate on the topic of a "relational data model on top of a hash map"? Are there any books that cover the concepts?

"a relational data model on top top of hash-map" is my original idea, and then combine [The Pure Function Pipeline Data Flow v3.0 with Warehouse/Workshop Model]( https://github.com/linpengcheng/PurefunctionPipelineDataflow ), can perfectly realize the simplicity and unity combination of system architecture.

cockroach is built on top of a key/value index, i believe.

https://www.cockroachlabs.com/blog/sql-in-cockroachdb-mappin...

Post reply on HN