4

Several times I've found myself working on data that has been exported to .sql files that I want to process rather than import into a database.

I'm only talking about "pure data" sql files. Ones that have nothing other than INSERT INTO statements. (They may have some prolog at the top that I can ignore.)

Generally the files will have many lines beginning with INSERT INTO and ending with ; between will be a variable number of "records" beginning with (, ending with ), and separated with ,. Each "record" will be made up of a fixed number of "fields" which are also separated by ,.

The filter should strip out all the SQL statements, separators, terminators, etc. It should output just one "record" per line with each "field" separated by say a comma or tab.

In my mind it should be totally trivial and common enough that it must've been implemented many times. But I never seem to be able to find existing tools or scripts.

I usually try to tackle it in Perl by treating ),( as the record separator instead of \n but I always get stuck in annoying details.

Has anyone got (or can make) a really simple script that does this? I don't care whether it's in bash, sed, awk, Perl, Python, etc.

Here's some real example data. I have altered it to have only a few records per line. The actual files have about a million characters per line /statement before they reach the ; and begin the next one.

INSERT INTO `categorylinks` VALUES
(0,'Wikisaurus','RunJobs.php','2008-01-27
03:10:30','','','page'),(8,'Wiktionary','TEXT OF THE GNU FREE
DOCUMENTATION LICENSE','2011-01-26
23:50:34','','uppercase','page'),(12,'Wiktionary','WHAT WIKTIONARY IS
NOT\nWHAT WIKTIONARY IS NOT','2011-04-10 18:22:23','What Wiktionary is
not','uppercase','page'),(12,'Wiktionary:Help','WHAT WIKTIONARY IS
NOT\nWHAT WIKTIONARY IS NOT','2011-04-10 18:22:23','What Wiktionary is
not','uppercase','page'); INSERT INTO `categorylinks` VALUES
(2184,'Swedish_nouns','RELIGION','2011-01-06
00:04:41','','uppercase','page'),(2184,'Translations_to_be_checked_(Albanian)','RELIGION','2007-01-27
08:16:34','','uppercase','page'),(2184,'Translations_to_be_checked_(Bulgarian)','RELIGION','2007-01-27
08:16:34','','uppercase','page');

Output would be something like this. Whether fields are quoted or not probably doesn't matter. Comma or tab separated doesn't matter. Correct escaping of commas would be needed though.

 0,'Wikisaurus','RunJobs.php','2008-01-27 03:10:30','','','page'
 8,'Wiktionary','TEXT OF THE GNU FREE DOCUMENTATION LICENSE','2011-01-26 23:50:34','','uppercase','page'
 12,'Wiktionary','WHAT WIKTIONARY IS NOT\nWHAT WIKTIONARY IS NOT','2011-04-10 18:22:23','What Wiktionary is not','uppercase','page'
 12,'Wiktionary:Help','WHAT WIKTIONARY IS NOT\nWHAT WIKTIONARY IS NOT','2011-04-10 18:22:23','What Wiktionary is not','uppercase','page'
 2184,'Swedish_nouns','RELIGION','2011-01-06 00:04:41','','uppercase','page'
 2184,'Translations_to_be_checked_(Albanian)','RELIGION','2007-01-27 08:16:34','','uppercase','page'
 2184,'Translations_to_be_checked_(Bulgarian)','RELIGION','2007-01-27 08:16:34','','uppercase','page'

The data I'm usually working with is part of the dumps of Wikipedia and Wiktionary.

5
  • 1
    It's not trivial because of the quoting. And your output format could be ambiguous because of that too. Have you considered importing to an sqlite database and exporting from there (see .mode and .separator commands)? (Won't remove the quoting problem.)
    – Mat
    Nov 19, 2011 at 14:53
  • Well Wikimedia and Wiktionary dump files can be extremely huge. I remember some imports taking days to import. I think the quoting issue was why I chose Perl too. Nov 19, 2011 at 15:36
  • 1
    You'll get better performance with restricted tools such as sed than with general-purpose tools such as Perl, and you'll get better performance if you exploit the specific format of your input rather than process SQL in full generality. Can you post a small representative sample of the data you want to process? Nov 20, 2011 at 0:09
  • If you really feel there are only nontrivial solutions that should be an acceptable answer by the way. At least then people working with such SQL files as input data will have a definitive answer and know not to bother wasting too much time scratching their heads. Nov 20, 2011 at 19:00
  • @Gilles: I posted a sample that I hope is representative. Nov 23, 2011 at 10:58

1 Answer 1

1

This would be trivial:

1) Import the SQL data into an SQL database

2) Output the data in the format you want with any of the SQL tools for doing this that already exist. E.g. SELECT INTO OUTFILE

And that is totally scriptable. If there are speed issues, get faster hardware, especially drives. If you absolutely want to parse this in some other language, you will probably find yourself writing a full SQL parser, just to deal with all the quotation / braces / reserved words etc. While this is definitely doable, I wouldn't consider it trivial.

2
  • No I've tried this. Parsing it in Perl took minutes, importing into a database took days. Faster hardware just does not magic away that many orders of magnitude. Just the parser wasn't very elegant like I imagined I should be able to make it. If you read the question you will see it's nowhere near full SQL but a trivial limited predictable subset of it. Jan 1, 2012 at 9:06
  • Will not work if DDL statements have problems (that is often) but insterts are ok.
    – kolen
    Nov 16, 2016 at 15:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .