[Python] Suggestions to improve speed of table transformation in normalization process

I've downloaded a dataset that comes in as a billion-row, 300-field CSV and I'm porting it into PostgreSQL and manipulating it using Python/psycopg, hoping to exercise some normalization. Each row consists of a few fields that identify a weather station (I've determined those values are locked together; the value of one is explicitly linked to the value of another). Then there is a date field, and the rest of the fields are observational values and flags about each of those values. I've started out by making a single table that includes all rows and columns and created indexes on 1) the 11-character station identifier, 2) the date) and 3) the station identifier and the other mutually-locked station characteristic fields.

From that table I've used SELECT DISTINCT to create a new table of just stations called "stations" (there are about 128,000 of them) - the station identifier and their other characteristics. I've added a PRIMARY KEY column of type SERIAL to that table.

Having done that, I want to take that billion-row table and make a new one that replaces all the station characteristic columns with a single column "station_id" of type INTEGER. So I query the "stations" table to return just the primary key and the 11-character station identifier in pairs like so:

dbcur.execute('SELECT (id,station) FROM stations;')
all_stations = dbcur.fetchall()


[Note: I know there shouldn't be parentheses around the variables; the aftermath of doing so gets handled in the Python so don't concern yourself with that]

In the Python code below, "select_set" is a list of column names that comprise just the date field and the rest of the observation fields from that initial table (called "source"). So a SELECT <select_set> FROM source WHERE station='<station_id>' is performed for each value of "station_id" and the values that arise from the selection of "select_set" are prepended by the value of the SERIAL primary key associated with that value of "station_id". Here is the code that does this:

def prepend_each(prepend, to):
return ((prepend,) + i for i in to)

for station_id_pair in all_stations:
station_id = station_id_pair[0][0] # This is the station primary key
station_code = station_id_pair[0][1] # This is the 11-character station code
dbcur.execute("SELECT {} FROM source WHERE station='{}'".format(','.join(select_set), station_code))
result = dbcur.fetchall()
with dbcur.copy("COPY observations ({}) FROM STDIN".format(','.join(['station_id'] + select_set))) as copy:
for copychunk in prepend_each(station_id, result):
copy.write_row(copychunk)
dbconn.commit()


The SELECT results for each trip through that for loop can vary considerably but on average each trip takes about a second so I can expect this transformation could take about a day and a half. Is there anything about my coding approach that I could change to speed things up? I will add incidentally that one possible issue is that the Dell PERC6/i controller is insisting on running an initialization on a four-drive RAID10 volume despite having been told not to and I have no idea how it's prioritizing that operation versus actual I/O coming from the OS so I don't know if the RAID volume initialization is getting in the way of PostgreSQL trying to read and write or not.

The procedure is running as I type and this time I'm running the Python on the PostgreSQL server itself so that network bandwidth and overhead isn't involved. Whereas I'm content to let this operation complete, if there is a better approach I should take, it will still matter because I will need to make another run through this for the four-per-observation attribute flags. When watching htop on the PostgreSQL server, it seems as though python is taking up more of the CPUs' time than PostgreSQL is.

Thanks.

Continue reading...