Handle big files with python & pandas

Question

Thanks for reading my post.

I need to deal with big files, let me give you more context, I extract some tables from a database convert those tables to CSV and after that, I convert them to JSON.

All that is to send the information to BigQuery.

Now my script works fine but I have a problem, some tables I extract are so so big one of them has 14 Gb, my problem is my server memory just has 8 Gb, exist any way to integrate some to my script to split or append the information ???

My script:

import pyodbc
import fileinput
import csv
import pandas as pd
import json
import os
import sys

conn = pyodbc.connect("Driver={SQL Server};"
                      "Server=TEST;"
                      "username=test;"
                      "password=12345;"
                      "Database=TEST;"
                      "Trusted_Connection=no;")
cursor = conn.cursor()

query = "SELECT * FROM placeholder where "


with open(r"D:\Test.txt") as file:
    lines = file.readlines()
    print(lines)


for user_input in lines:

    result = query.replace("placeholder", user_input)
    print(result)
    sql_query = pd.read_sql(result,conn)
    df = pd.DataFrame(sql_query)
    user_inputs =  user_input.strip("\n")
    filename = os.path.join('D:\\', user_inputs + '.csv')
    df.to_csv (filename, index = False, encoding='utf-8', sep = '~', quotechar = "`", quoting=csv.QUOTE_ALL)
    print(filename)
    filename_json = os.path.join('D:\\', user_inputs + '.jsonl')
    csvFilePath = (filename)
    jsonFilePath = (filename_json)
    print(filename_json)
    df_o = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)
    df_o.to_json(filename_json, orient = "records",  lines = True, date_format = "iso", double_precision = 15, force_ascii = False, date_unit = 'ms', default_handler = str)

dir_name = "D:\\"
test = os.listdir(dir_name)

for item in test:
    if item.endswith(".csv"):
        os.remove(os.path.join(dir_name, item)) 

cursor.close()
conn.close()

I'm really new to python, I hope you can help me to integrate some into my script.

Really thanks so many guys !!!

Kind regards.

Answer 1

For large data sets you should avoid reading all of it at once and then writing it all at once. You should do partial reads and partial writes.

Since you are using BigQuery you should use paritions to limit the query output. Have some logic to update the partition offsets. For each partition you can generate one file per parition. In this case your output would be like output-1.csv, output-2.csv etc.

An example of using parition:

SELECT * FROM placeholder
WHERE transaction_date >= '2016-01-01'

As a bonus tip, avoid doing Select * as BigQuery is columnar storage system mentioning the columns you would want to read will significatnly improve the peformance.