Jamie Thomson

In the spirit of “when you learn something stick it in a blog post so you can find it later” here’s a solution to splitting a file into multiple files using the Unix/Linux command awk.

Here’s a demo script that dumps some sample data into a file and then uses awk to split it into multiple output streams:

echo "John,Bananas,London,10" > /tmp/input
echo "John,Apples,London,5" >> /tmp/input
echo "Jane,Bananas,London,2" >> /tmp/input
echo "John,Apples,Leeds,3" >> /tmp/input
echo "Jane,Bananas,Leeds,8" >> /tmp/input
echo
echo Jane buying Bananas
echo ===================
cat /tmp/input | awk -F, '$1=="Jane" && $2=="Bananas"'
echo
echo
echo John buying in London
echo =====================
cat /tmp/input | awk -F, '$1=="John" && $3=="London"'
echo
echo
echo Buying in Leeds
echo ===============
cat /tmp/input | awk -F, '$3=="Leeds"'
echo
echo

which when run returns this output:

$ . /tmp/demoawk.sh

Jane buying Bananas
===================
Jane,Bananas,London,2
Jane,Bananas,Leeds,8

John buying in London
=====================
John,Bananas,London,10
John,Apples,London,5

Buying in Leeds
===============
John,Apples,Leeds,3
Jane,Bananas,Leeds,8

Like I said, I wrote this for my own reference but if someone else finds it useful, so much the better! Thank you to my colleague Marcin Kulisz who helped me out with this.

JT

 

In my day job at dunnhumby I’m using Apache Spark a lot and so when Windows 10 gained the ability to run Ubuntu, a Linux distro, I thought it would be fun to see if I could run Spark on it. My earlier efforts in November 2016 were thwarted (something to do with enumerating network connections) so when Microsoft released the Windows 10 Creators Update I thought I’d give it another bash (pun intended). Happily this time it worked and in this blog post I’ll explain how I got it running in case anyone wants to do the same. If you don’t read all the blurb herein and instead just go run all the steps it should take you, if you’re quick, about 10 minutes.

Enable Windows subsystem for Linux

There are countless sites that will tell you how to do this (here’s one) but basically you need to turn on Windows Subsystem for Linux (Beta) in Control Panel->Windows Features:

 

 

 

 

 

 

 

 

 

 

 

then open up a PowerShell window and run

lxrun /install

which will install all the bits:

 

 

 

 

Once its installed run

bash

to launch a linux bash prompt.

Download and install Spark

Once at the prompt run the following commands:

#install Java runtime environment (JRE)
sudo apt-get install openjdk-8-jre-headless
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-amd64/jre
#download spark, visit https://spark.apache.org/downloads.html if you want a different version
wget http://d3kbcqa49mib13.cloudfront.net/spark-2.1.0-bin-hadoop2.7.tgz
#untar and set a symlink
sudo tar -xvzf spark-2.1.0-bin-hadoop2.7.tgz -C /opt
sudo ln -s spark-2.1.0-bin-hadoop2.7 /opt/spark

That’s it. Now to actually use it.

Run some Spark code

Run:

/opt/spark/bin/spark-shell

and you’ll be launched into the Spark REPL which is actually the Scala REPL preloaded with some Spark stuff (Spark is written in Scala hence its considered the canonical language for doing Spark development):

 

 

 

 

 

 

 

 

Let’s try the “Hello World” for Spark:

sc.parallelize(Array(1,2,3,4,5)).collect()

If it works you should see this:

scala> sc.parallelize(Array(1,2,3,4,5)).collect()
res5: Array[Int] = Array(1, 2, 3, 4, 5)

Not in itself particularly exciting, but given I’m running this on Windows and it actually works I think that’s pretty cool.

OK let’s do something more interesting.

val df = spark.read.json("/opt/spark/examples/src/main/resources/people.json")
df.show()

 

 

 

 

Cool! If you’re already familiar with Spark then you can stop here but if you want to know a bit more about its APIs, keep reading.

Now let’s synthesize a bigger dataset and get its cardinality:

for (a <- 1 until 10){
  df = df.union(df)
}
df.count()

 

 

 

 

A dataset of 1536 rows is a bit more interesting that we can do a few things with:

df.filter(df("age") > 21).count()
df.groupBy("name").count().show()
//combine a few functions together
//this fluent API approach is what I've come to love about Spark
df.withColumn("is over 21", df("age") > 21).limit(5).show()

 

 

 

 

 

 

 

 

 

Notice that when Spark shell started up it provided a link to the Spark UI:

 

 

 

Browse to that URL to view all the stats that Spark keeps about everything that it executes:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you want to persist the data as a database table there is a simple saveAsTable() method that allows you to do that, which is then easy to consume using table():

val df = spark.read.json("/opt/spark/examples/src/main/resources/people.json")
df.write.saveAsTable("savetabledemo")
spark.table("savetabledemo").show()

 

 

 

 

 

And if fluent APIs aren’t your thing there’s still good old SQL:

spark.sql("select count(*) from savetabledemo").show()

 

 

 

Prefer Python to Scala?

If you prefer Python to Scala then you’ll be glad to know you can use that too. One thing you’ll need to do first is, back at the bash prompt, create a symlink to the Python interpreter then launch the Python REPL for Spark, pyspark:

ln -s /usr/bin/python3.5 python
/opt/spark/bin/pyspark

From there you can run the same operations as before, just using python syntax instead.

sc.parallelize([1,2,3,4,5]).collect()
df = sqlContext.read.json("/opt/spark/examples/src/main/resources/people.json")
df.show()
df.filter(df.age > 21).count()
df.groupBy("name").count().show()
df.withColumn("is over 21", df.age > 21).limit(5).show()
df.write.saveAsTable('savetabledemo_python')
sqlContext.table('savetabledemo_python').show()
sqlContext.sql('select count(*) from savetabledemo_python').show()

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

That’s probably enough for now. Hope this helps.

@jamiet

HMRC will, if you ask them, send you a form called SA302 which shows your tax calculation for a given year. As I’m self-employed and thus have to submit a Self Assessment tax return every year I find this to be very useful.

To order the SA302 calculation telephone 01619319070 or 08453000627, it should take a couple of minutes at most. You will need your National Insurance Number.

My latest SA302 arrived today and I’ve spent this evening compiling an Excel workbook containing all my SA302 data for the past five years (fun fun fun). I’ve found this to be very very useful so figured I should make the workbook available to anyone else in a similar position. The workbook can be accessed via your web browser (no Excel installation required) here: TaxAnalysis.xlsx. It already has some (fake) numbers filled in for years 2009-10 through to 2013-14:

all you need to do is replace the fake numbers with your own and voila, you’ll get some nice charts like these showing you useful information about how much you earned and how much tax you paid over those years:

Hope this is useful! If so, please do let me have some feedback. Thanks!

@Jamiet