3 ms·

Do you guys think Hadoop would be a good solution to parse and convert 16 GB worth of XML files to SQL? I'm thinking of using EC2 to distribute this in the clou

Do you guys think Hadoop would be a good solution to parse and convert 16 GB worth of XML files to SQL? I'm thinking of using EC2 to distribute this in the cloud, as on my desktop it can take days.

jwilliams 18y ago
What is the exact solution? If it's a massive parallel parsing operation.. then maybe. If you want it to parse the XML and put into a database - probably not. You're going to be bottlenecked by the database, so not much point increasing the parallelism of the parsing.
- FiReaNG3L 18y ago
  I have 750 x 150 MB XML files, I just want to generate a huge .sql to import it all at once which is much quicker.
  - jwilliams 18y ago
    I think you'll be constrained by the inserts - even if you use bulk insert. You are probably better off using a bulk loader to get the data in - SQL*Loader on Oracle. I believe MySQL has an equivalent. You could use Hadoop to process the data into whatever intermediate format (SQL or Bulk Data), but my instinct would be that getting the data loaded/inserted will be the slowest part by far.
andrewf 18y ago
I agree that the database will be the bottleneck, and would suggest: (1) Look at bunching your insertions into appropriately sized transactions. You don't want transactions to be too small (you spend most of your time waiting for tiny writes) or too big (buffering gets inefficient and you spill over to temp files) (2) Tweak your database config. Can you turn off "safety" features like fsyncing post-transaction? Can you configure the db server to accept only a few connections, each with loads of working memory? (3) Writing out SQL to disk then having a database client read it back in is wasted time. The fastest solution would probably be to have your parsing software talk direct to the database. Piping generated SQL straight into the standard database client will get you most of that speed with zero effort. I'm a postgresql guy myself. Some databases already have widely used bulk loader solutions, and all of the transaction/config stuff may not apply to you as much as it does to me.

Do you guys think Hadoop would be a good solution to parse and convert 16 GB worth of XML files to SQL? I'm thinking of using EC2 to distribute this in the cloud, as on my desktop it can take days.

jwilliams 18y ago

What is the exact solution? If it's a massive parallel parsing operation.. then maybe. If you want it to parse the XML and put into a database - probably not. You're going to be bottlenecked by the database, so not much point increasing the parallelism of the parsing.

FiReaNG3L 18y ago
I have 750 x 150 MB XML files, I just want to generate a huge .sql to import it all at once which is much quicker.
- jwilliams 18y ago
  I think you'll be constrained by the inserts - even if you use bulk insert. You are probably better off using a bulk loader to get the data in - SQL*Loader on Oracle. I believe MySQL has an equivalent. You could use Hadoop to process the data into whatever intermediate format (SQL or Bulk Data), but my instinct would be that getting the data loaded/inserted will be the slowest part by far.

andrewf 18y ago

I agree that the database will be the bottleneck, and would suggest: (1) Look at bunching your insertions into appropriately sized transactions. You don't want transactions to be too small (you spend most of your time waiting for tiny writes) or too big (buffering gets inefficient and you spill over to temp files) (2) Tweak your database config. Can you turn off "safety" features like fsyncing post-transaction? Can you configure the db server to accept only a few connections, each with loads of working memory? (3) Writing out SQL to disk then having a database client read it back in is wasted time. The fastest solution would probably be to have your parsing software talk direct to the database. Piping generated SQL straight into the standard database client will get you most of that speed with zero effort. I'm a postgresql guy myself. Some databases already have widely used bulk loader solutions, and all of the transaction/config stuff may not apply to you as much as it does to me.