0

I have a number of csv-files that I would like to import into my postgres-database. Since each file is quite large (200m-2bn records/file) I believe it would be better to add each file in smaller batches.

FOr comparison, when using MS-SQL and using bcp, files get automatically split into 1000 record batches. That is exactly what I (think I) want to do with COPY.

My main script is Python 3.5.2, the database is PostgreSQL 9.5.10 on Ubuntu 16.04 LTS. I am using psycopg2's copy functionality (on the simple account of it being the first that worked). I'm happy to change that, as long as I can call it from python (since I need to do some other processing per file -- organizing files, searching, determining the target stage table, unzip, cleanup, etc. -- since I am not sure how exactly the data transfer works using this method. Per psycopg2's doc the sql-version of copy is used (https://www.postgresql.org/docs/current/static/sql-copy.html), not the command line version. However I suspect the entire file will be buffered through stdin, but again, I'm not sure.

con = psycopg2.connect(database=dbname, user=user, password=password, host=host, port=port)
cur = con.cursor()
f = open(pathTempFile)
cur.copy_expert("""copy public.stage_datafile from STDIN DELIMITER ',' CSV HEADER""",
    file=f)
f.close()
con.commit()
con.close()

This process works fine, and I'm not even sure there are any drawbacks. However, it seems there might be a better/more robust way? I will use a trigger to export the data from the staging tables into the normalized main tables, surely that would benefit from smaller data blocks?

I appreciate any suggestions on making this process more robust.


Addendum:

  • I only need to import the data once, not regularly.
  • I could also use sed to generate some temporary files and import those, in fact I'm doing that while debugging
  • An alternative to sed is use the linux split command to break your csv's into smaller chunks. Eg. split -l10000 yourfile.csv pg_, where pg_ is the prefix for the resulting files, though you might want to use input filename ("yourfile" in this example) as the prefix, so no csv's get overwritten by running split on subsequent files. -l10000 splits on 10k lines. If you need more advanced ETL options (outside of Python), there's also a slick tool called pgloader. – bma Dec 3 '17 at 19:30
  • In addition to what Evan Carroll said, you can create your staging table UNLOGGED to save on WAL generation. – bma Dec 3 '17 at 19:33
  • Thanks @bma for the suggestion! I'll try ´sed` once I run into problems, but i've made the tables unlogged. A colleague suggested to check out autogrowth options and datatable types, but I haven't had the time to try those. – zuiqo Dec 7 '17 at 8:46
1

I have a number of csv-files that I would like to import into my postgres-database. Since each file is quite large (200m-2bn records/file) I believe it would be better to add each file in smaller batches.

Probably not. It'll work just fine. You're probably better off running the CREATE TABLE and COPY in the same transaction though, that way you generate minimal WAL.

  • Thanks! I don't drop the entire table, I just remove all records. So I only have to create it once, so I don't think I'll have to save at that point. – zuiqo Dec 7 '17 at 8:44
  • @phi use TRUNCATE and COPY in the same transaction, it'll be faster. – Evan Carroll Dec 7 '17 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.