I’ve been playing around with the BBC football data set that I wrote about a couple of months ago and I wanted to write some code that would take the import script and replace all instances of remote URIs with a file system path.
For example the import file contains several lines similar to this:
LOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/mneedham/neo4j-bbc/master/data/matches.csv" AS row
And I want that to read:
LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/matches.csv" AS row
The start of that path also happens to be my working directory:
$ echo $PWD /Users/markneedham/repos/neo4j-bbc
So I wanted to write a script that would look for occurrences of ‘https://raw.githubusercontent.com/mneedham/neo4j-bbc/master’ and replace it with $PWD. I’m a fan of Sed so I thought I’d try and use it to solve my problem.
The first thing we can do to make life easy is to change the default delimiter. Sed usually uses ‘/’ to separate parts of the command but since we’re using URIs that’s going to be horrible so we’ll use an underscore instead.
For a first cut I tried just removing that first part of the URI but not replacing it with anything in particular:
$ sed 's_https://raw.githubusercontent.com/mneedham/neo4j-bbc/master__' import.cql $ sed 's_https://raw.githubusercontent.com/mneedham/neo4j-bbc/master__' import.cql | grep LOAD LOAD CSV WITH HEADERS FROM "/data/matches.csv" AS row LOAD CSV WITH HEADERS FROM "/data/players.csv" AS row LOAD CSV WITH HEADERS FROM "/data/players.csv" AS row LOAD CSV WITH HEADERS FROM "/data/fouls.csv" AS row LOAD CSV WITH HEADERS FROM "/data/attempts.csv" AS row LOAD CSV WITH HEADERS FROM "/data/attempts.csv" AS row LOAD CSV WITH HEADERS FROM "/data/corners.csv" AS row LOAD CSV WITH HEADERS FROM "/data/corners.csv" AS row LOAD CSV WITH HEADERS FROM "/data/cards.csv" AS row LOAD CSV WITH HEADERS FROM "/data/cards.csv" AS row LOAD CSV WITH HEADERS FROM "/data/subs.csv" AS row
Cool! That worked as expected. Now let’s try and replace it with $PWD:
$ sed 's_https://raw.githubusercontent.com/mneedham/neo4j-bbc/master_file://$PWD_' import.cql | grep LOAD LOAD CSV WITH HEADERS FROM "file://$PWD/data/matches.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/players.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/players.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/fouls.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/attempts.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/attempts.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/corners.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/corners.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/cards.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/cards.csv" AS row LOAD CSV WITH HEADERS FROM "file://$PWD/data/subs.csv" AS row
Hmmm that didn’t work as expected. The $PWD is being treated as a literal instead of being evaluated like we want it to be.
It turns out this is a popular question on Stack Overflow and there are lots of suggestions – I tried a few of them and found that single quotes did the trick:
$ sed 's_https://raw.githubusercontent.com/mneedham/neo4j-bbc/master_file://'$PWD'_' import.cql | grep LOAD LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/matches.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/players.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/players.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/fouls.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/attempts.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/attempts.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/corners.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/corners.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/cards.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/cards.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/subs.csv" AS row
We could also use double quotes everywhere if we prefer:
$ sed "s_https://raw.githubusercontent.com/mneedham/neo4j-bbc/master_file://"$PWD"_" import.cql | grep LOAD LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/matches.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/players.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/players.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/fouls.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/attempts.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/attempts.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/corners.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/corners.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/cards.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/cards.csv" AS row LOAD CSV WITH HEADERS FROM "file:///Users/markneedham/repos/neo4j-bbc/data/subs.csv" AS row
| Reference: | Sed: Using environment variables from our JCG partner Mark Needham at the Mark Needham Blog blog. |
