1

I have the below table in SQL which has 86M rows in it :

Transactions

I am trying to get it into a dataframe as from the code below

data = cs.execute("""
select * from transactions;
""").fetch_pandas_all()

This takes much too long to load.

What is a way I can make this load faster? Is there any method I can use? Should I create the table itself in the sql statement instead of a select? Any insight would be helpful.

It is interesting because to create this table in SQL it takes about 25 seconds. But when putting the same data into a dataframe - it takes about 15 minutes. So am thinking if there is a way to achieve the same speed as SQL in python.

Michael Norman
  • 121
  • 2
  • 5
  • 13

1 Answers1

0

There is fundamental difference in what you are doing and expexting.

case 1 - when using snowflake only - you are doing something like this

create table mytable as select ... from anothertable;

This is fast because it does a data movement from S3 to S3 and uses snowflake optimization of micro partitions etc.

case 2 - for Python pandas fetch_all() - You are trying to read data from snowflake and fetch it in local system(into pandas dataframe) where python is running. This means, all 86M data is moved over network and can take time.

So, here is what i can think to optimize py code -

  1. Instead of fetch all, fetch some.
  2. Apply filter to fetch only required rows.
  3. Fetch only required columns instead of all.
  4. Run python close to your Snowflake cluster like AWS or GCP using a machine with more RAM.
  5. If all fails, you can try lazily evaluated pandas data frame which will not fetch all data untill you do a commit. So this is somewhat faster but at one point it will take time. Pls refer to below link - How to create lazy_evaluated dataframe columns in Pandas
Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • The issue is, I do need all of the rows and columns and cannot make it closer to my cluster. What would your number 5 mean? I suppose I don't understand how that can be used I also found some info on creating the dataframe with chunks but not sure if that is the best way to go https://docs.snowflake.com/en/user-guide/python-connector-pandas#migrating-to-pandas-dataframes – Michael Norman Feb 22 '23 at 18:54
  • Chunking and looping is something you can do. `lazy Frame` is something which will not hit the database unless you do some commit. so, create frame, select frame etc. wont take time but writing to table or any I/O operation will take time. – Koushik Roy Feb 23 '23 at 05:54