We have a large text file containing 325 million integer values that we need to load into a database. The file uncompressed is about 4.5GB. Even though disk is cheap, we didn’t have enough readily available to quickly decompress the file.<\/p>\n
As such, I used the python gzip module to decompress the source file on the fly and read it line by line while using cx_Oracle to load the rows. We batch the inserts into 5,000 row chunks to take advantage of bulk loading.<\/p>\n
See below:<\/p>\n
\r\n#!\/home\/oracle\/local\/bin\/python\r\n\r\nimport gzip, os, cx_Oracle, sys, string\r\n\r\nconn = cx_Oracle.connect(\"user\",\"password\")\r\n\r\nrows = []\r\nloops = 0\r\n\r\ncursor = conn.cursor()\r\ncursor.prepare(\"insert into oclc_num_seq(xwcmd_id) values(:1)\")\r\n\r\nfd = gzip.open(os.path.join(\"\/u02\", \"missing_find.gz\"))\r\nfor line in fd:\r\n tmp = line.strip()\r\n try:\r\n rows.append([int(tmp)])\r\n except:\r\n print str(sys.exc_info()[1])\r\n loops = loops + 1\r\n if loops % 5000 == 0:\r\n cursor.executemany(None, rows)\r\n conn.commit()\r\n del rows[:]\r\n print loops\r\n sys.stdout.flush()\r\n\r\n#run one last execution to ensure we got the \"stragglers\" less than 5,000\r\ncursor.executemany(None, rows)\r\nconn.commit()\r\ndel rows[:]\r\nprint loops\r\nsys.stdout.flush()\r\n<\/pre>\nWe can load about one million rows per minute doing this.<\/p>\n","protected":false},"excerpt":{"rendered":"
We have a large text file containing 325 million integer values that we need to load into a database. The file uncompressed is about 4.5GB. Even though disk is cheap, we didn’t have enough readily available to quickly decompress the…<\/p>\n