Faster Django CSV generation for several million database entries

Question

I am writing a CSV file generator that's filtering through about seven million database entries (MySQL backend). This part is especially slow and I was wondering if there is a way to make it much faster. I was thinking of wiring to a temp file first before serving it and then deleting the file.

The Station.header() returns a list with header names ['a','b','c'], etc.

Is there an advantage to this? Is there a better way?

def metadata_file(request):
    """Gets metadata for sensors"""
    if request.GET.has_key('all'):
        s101 = Station.objects.all().filter(fwy='101', dir='S',
                                    abs_pm__gte=420.80, abs_pm__lte=431.63)
        s280 = Station.objects.all().filter(fwy='280', dir='S',
                                    abs_pm__gte=41.16, abs_pm__lte=56.49)
        q = s101|s280
        response = HttpResponse(mimetype='text/csv')
        response['Content-Disposition'] = 'attachment; filename="all_stations.csv"'

        writer = csv.writer(response)
        writer.writerow(Station().header())
        for x in q:
            writer.writerow(x.line())
        return response

Answer 1

1. Introduction

By coincidence, I'm working on a similar problem right now, so here's a chance for me to write up an experiment I ran today, in the hope that it might prove useful. However, the solution presented below is far from ideal (see section 3).

2. The idea

Processing your query results through Django's ORM and then through csv.writer is time-consuming. You can speed things up by getting the database to write the query output directly to a file and then serving that file.

First, your database user needs to have FILE privilege in order to be able to write files:

mysql> GRANT FILE ON *.* TO 'user'@'host';

Then you need to run your query with MySQL's INTO OUTFILE clause. You can get the query you need from Django by calling .query.sql_with_params():

from django.db.models import Q
s101 = Q(fwy='101', dir='S', abs_pm__gte=420.80, abs_pm__lte=431.63)
s280 = Q(fwy='280', dir='S', abs_pm__gte=41.16, abs_pm__lte=56.49)
sql, params = Station.objects.filter(s101|s280).query.sql_with_params()

Add the INTO OUTFILE clause:

sql += ''' INTO OUTFILE %s
           FIELDS TERMINATED BY ','
           OPTIONALLY ENCLOSED BY '"'
           LINES TERMINATED BY '\n' '''

Pick a place to put the CSV:

import os.path
csv_path = os.path.join(csv_directory, csv_filename)

Run the query:

from django.db import connection
cursor = connection.cursor()
cursor.execute(sql, params + (csv_path,))

Send the file (using StreamingHttpResponse which is new in Django 1.5):

from django.http import StreamingHttpResponse
response = StreamingHttpResponse(open(csv_path), content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename=' + csv_filename
return response

3. Analysis

In my test cases (about a million records) this is around ten times as fast as processing the data through csv.writer in Python.

But there are several problems, mostly related to security:

1. This depends on the MySQL server being on the same machine as the Django web server. You can imagine working around this by some kind of reverse proxying but it all gets rather complicated.

2. Granting FILE access to your MySQL user is dangerous because it means that a SQL injection attacker can read and write files on your disk. You might want to create a separate MySQL user just to generate these CSV files, and you'll definitely want to set MySQL's secure_file_priv variable.

3. Even if the attacker can't inject SQL, they may be able to cause your disk to fill up with these temporary files. You need a plan for how they are going to be deleted. (Maybe using the request_finished signal?)

4. You need to find somewhere safe to put them (a directory to which the MySQL database user has write access).

4. Update

I did some more timing experiments today, trying out five different ways of generating and downloading the CSV, summarized in the table below:

+------+-----------------------+-------+
|      |     through Django    |Direct |
|      |Streaming|Not streaming|from DB|
+------+---------+-------------+-------+
|ORM   |      630|          370|    N/A|    
+------+---------+-------------+-------+
|No ORM|      190|          125|     58|     
+------+---------+-------------+-------+

Notes on the table:

• Times are in seconds.
• This is for a query with a million rows that generates 150 MiB of CSV.
• "ORM" means that I passed the query results through Django's object-relational mapping system before generating the CSV.
• "No ORM" means that I avoided this by running a custom SQL query.
• "Streaming" means that I used a StreamingHttpResponse and generated the CSV one line at a time using a technique I'll describe below.
• "Not streaming" means that I used the technique described in the Django documentation to generate the CSV. (This involves reading the entire CSV output into memory, which is a bit painful for other processes running on the server.)
• "Direct" is the technique described in section 2 above, using MySQL's INTO OUTFILE clause.

Clearly Django's ORM and Python's csv.writer are both significant bottlenecks. If only the INTO OUTFILE approach weren't so painful!

So I would still be interested to see other suggestions for speeding up this operation.

5. Appendix: streaming CSV download

Python's csv module doesn't provide an easy way to generate one record at a time, but you can coerce it into doing so like this, using io.BytesIO to capture the output:

from io import BytesIO

def writerow(row):
    """Format row in CSV format and return it as a byte string."""
    bio = BytesIO()
    csv.writer(bio).writerow([unicode(r).encode('utf-8') for r in row])
    return bio.getvalue()

Answer 2

Ammm. And no one thought to try to do it this way?

from django.core.management import call_command

call_command('dumpdata', app_name + "." + model_name, output='dump.csv')