I'm trying to load a SQL query, trying to extract data from a Snowflake database into a dataframe (to torch tensor actually, but the conversion happens later). I can't share the query, but please note the following: The result size is 385 million rows x (1 integer + 1 float). So 385 million x 12 bytes = 4.6GB approximately. This is verified in the Snowflake GUI, where we see the result size being even smaller (due to compression), and the query running for "only" 40 seconds. However, when we retrieve the query in code, we see the memory usage skyrocket (to >75GB). It seems to happen inside the read/fetch statement. Moreover, it takes over 40 minutes to extract the query, while in the Snowflake GUI query history, we see that query being finished in the "expected" 40 seconds. After it's being loaded, we can verify that the resulting dataframe is of expected size. We tried using malloc, but it doesn't seem to find where the memory overhead is coming from. My main question is, how can I reduce the memory overhead? Code attempt 1, sqlalchemy: import pandas as pd from sqlalchemy import Engine, text def load_data(date: date, engine: Engine) -> pd.DataFrame: """Load all data and case_ids.""" query = text(my query) # dependent on date with engine.connect() as connection: df = pd.read_sql_query(query, con=connection) return df Code attempt 2: snowflake connector import pandas as pd import snowflake def load_data(date : date) -> pd.DataFrame: query = my query with snowflake.connector.connect(**credentials) as conn: with conn.cursor() as cur: rows = cur.execute(query).fetchall() # Convert rows to df return df Continue reading...
