I've been a big fan of ElasticSearch the since last Spring - using it on my RiffBank project as well as various other "data collection" experiments. Put (very) simply, it's a badass search server based on Apache Lucene. But in all honestly, to me, it's really a very scalable, auto-balancing, redundant, NoSQL data-store with all the benefits of a full search and analytics server.

#helladistributed

Also... it's fast. Really. Fucking. Fast.

Generally speaking, that is (can you use "generally speaking" after dropping an f-bomb?).

(yes, I said "Hadoop" - don't be afraid, little Ricky)

My current love affair (romantic fling?) with ES not-withstanding - it generally isn't usually used as a general purpose data-store (even though it's more than capable), but with the impending release of v1.0 I can see this changing and it's role expanding. At the very least why would you bother to using something like MongoDB (or a similar "hot" NoSQL sink) when ES is all that and more (plus a joy to work with and scale out).

But therein lies a problem - if I'm storing a shit-ton of data in ES, I certainly can't use my go-to visual analytics tool Tableau on it, since ES querying is strictly RESTful... or can I?

Enter ES's Hadoop plug-in. While primarily created to get Hadoop data INTO ES (assumably) we also use it to create an external "table" (more like 'data structure template') in Hive pointing to an ES index and SQL our little goat hearts out on it (and use a pretty generic Hive driver in Tableau to connect to it.).

So: Tableau -> Hive SQL -> Hadoop -> Map/Reduce -> ES

So, yeah, there is some serious overhead and translation / abstraction involved (obviously) and queries will be much slower than native - but the only other (direct) alternative is... well, there ISN'T ONE. You'd have to build custom ETL to load data from ES to another DB and query that directly (or query the initial source, if possible).

Maybe some day Tableau will allow us to write data-source access plug-ins...

Granted, if you already run a hadoop cluster you should be able to leverage it for better scaling MapR jobs. But, I'm going to assume that you don't use hadoop at all and we will set up an instance JUST for making ElasticSearch queries via Hive SQL.

First things first - this assumes that your ES cluster is already running on the network in an area accessable by the box we will be configuring. In my case it's a group of similar Ubuntu 12.04LTS DigitalOcean VMs as shown below. I'm not going to cover setting up ES, but trust me - it's dead easy and very felxible.

For my sample dataset I'm using a Wikipedia English Page dump (imported via the ES wikipedia-river plugin).

(showing my test ES cluster* / index setup - using the excellent "head" plugin - great for watching shards re-balance and overall a great front-tend tool for most 'Elastic' needs)

*Lovecraftian node names optional

"They had come from the stars, and had brought Their images with Them..."

Let's get started...

Ok, first log in to a fresh 12.04 box... let's create a 'dumbo' user and get him/her all configured correctly...

I'm going to assume that you are logged in as root.

Now to make sure that we can invoke SUDO as dumbo.

Many posts on setting up Hadoop recommend disabling ipv6, so we will just take care of that in order to eliminate any possible cluster 'lookup' issues later.

Ok, enough root. Now feel free to disconnect and re-login as our 'dumbo' user (or SU to it, or reboot, whatever you like).

Let's set some environment settings for later (and fun, laziness)

Let's re-up our .bashrc to reflect the changes

Ok, let's add some packages and update our install (using some of the aliases we just created)

Ok, now we can start installing shit without fear! You scared? Don't be scared. Be brave!

First up, Hadoop.

Ok, now we have to edit a bunch of config files. Bear with me here...

Ok, now we need to make sure that Hadoop can spawn new 'dumbo' shell sessions via ssh without a password (it's how their scripts operate)....

We need to make sure that 127.0.0.1 doesn't map to your literal hostname in /etc/hosts but only localhost (can cause loopback connection issues)

Time to "format" our hadoop HDFS 'filesystem'...

Let's start it all up and hope for the best...

If all is good, you shouldn't get any errors and see all 5+ procs running if you run a 'jps' command

Let's move on to Hive

Don't worry, way less to do here.

Only one config file to set up / create (to load the lib that we will get in the next step)

Create some dirs in HDFS for Hive...

You should now be able to run 'hive' and not see any errors launching.. (then just type 'quit;' to exit)

The ES "plug-in" part

Bit of a curveball here: Time to copy the lib to a HDFS folder so remote connections can find it as well as local.

Booya. We should be all configured to make the magic happen now. Let's start up an interactive HIVE SQL shell and check it out.

Fuckin' A right! If you see this above. You are a master of all your survey - or something like that. Anyways, with the ES plugin - we are creating an external table def that maps directly to our ES index / ES query.

Notice the ES vars, In this case I'm running a simple ES client on the Hadoop server (no data, no master) and connecting to that - but it could be anywhere on your network, shouldn't matter.

You should see a response like this.

Let's give it some work to do.

...and the MapReduce train gets rolling'.

It's a bit of an odd (and slow) example (esp on my small VM set up / example data), since in pure ES you'd just run a faceted open query on title - but it shows that we can talk to ES using Hive SQL. Also the plugin is getting better all the time and should optimize the query better in the future.

Now we have a Hive "wikitable" object that can be treated just like any other hive table (via Tableau or other). However, did you notice the ElasticSearch URL query string during table creation (wikipedia_river/page/_search?q=*)?

This essentially allows us to run a query / filter / etc in native ES BEFORE it gets abstracted and interpreted by Hive. For Example...

See q=title:metallica? It's going to do a regular full-text search on all fields for 'metallica' before passing the result set to Hive SQL.

Powerful leverage indeed... now if only we could pass parameters from query to table def...

Tableau time

First, make sure that Hive is launched as an external service (defaults to port 10000)

Easy. Install the MapR Hive ODBC Connector and fire up Tableau.

And you are "off to the races" as they say...

Errors? Issues? Let me know in the comments.

As with any EPIC nuts n' bolts type How-To post, there are bound to be errors and typos in v1 - and there are a lot of moving parts in this one...

[ Drawings part of Kenn Mortensen's amazing Post-It Monstres collection ]

We've all heard that 'witty' interjection - usually after explaining (or complaining about) a very complicated software procedure (typically administrative / operational in nature) someone will always say something like "Hey, that's why we have jobs!" (or, if consulting, "that's why you're here", etc). Sometimes then followed by everyone making their best 4chan LOL face before returning to the days tedium.

Most Enterprise Software == Added 'Technical Debt'.

All packaged up in a big shiny box, and by "box" I mean "poorly formatted auto-bro email". I haven't see a product box or printed manual in years, those cheap pricks.

It's 2013 for fucks sake.

I never imagined that the state of enterprise corporate software would *still* be so damn bad.

• Layers upon layers of antiquated cruft masquerading as "robustness" or "a solid foundation"
• complicated software stacks with multiple dependencies for the sake of a single (lazily designed) application
• purposely obfuscated database back-end schema (so you have to buy their "reporting modules" of course)
• monolithic per-user licensing plans on self-hosted software
• ...the list goes on...

Hell, it's spawned this complacent "business as usual" worldview among an entire generation of IT workers and managers. And people wonder why we drink so much...

Does anyone ever look around and say "Ye gods! We need to stop this ghastly madness!". Not really. And I'm not telling you to stand up and be that heretic, but I am asking you to think about it.

Why is it that many large well-known companies seem to write bad software on purpose? How does this continue for so long? Teams are too large? Bound to their own legacy technical debt? They don't want to rock the boat? The more (over) complicated it is, the more money they can charge for it?

I'm sure it's all of these things and more - but that doesn't make it right.
The people at these big software companies are supposed to be smarter than us, right? So we can get a better solution quicker, by opening our wallets and standing on the empathetic shoulders of software giants, right? I'm not so sure.

Fresh interfaces, fresh ideas, forward-thinking technology? Nope. Not here. Enterprise IT these days is about as "backward thinking" as it can get away with.

Every new decade brings the same old song, often using Moore's Law and cheap labor as a crutch.

"Bring on more of the same! We can always mitigate it with more computing power and more Dilbert-esque application-specific workers!"

I jest, but look around - it forms the very basis of many IT support organizations.

IT is kind of funny this way. It's pretty much seen as a "necessary evil" in most traditional (READ: BigCorp) industries. They have to have it and they don't like paying for it - because it doesn't really make them money directly. So when I complain about software decisions that turns a team into a bunch technological ditch diggers, they would say, "So? That's your job". But it isn't. IT's job is to support the "business people" so that they can make more money and everyone can high-five each other.

Instead, most IT jobs these days are truly "meta". They exist to support the tools they use to support the business (many times with even more layers of recursion than that)...

Don't take me as a ungrateful whiner - maybe they're right, maybe we "wouldn't have jobs" if software was (mostly) excellent... but I seriously doubt it. Instead we'd be able to spend more time making things *that* much better and profitable.

You know, as opposed to swimming upstream toward the next exposed tree root because someone didn't think to build a boat instead of buying a bottomless Yacht.

We ALL can do better. Better software up top - better purchasing decisions down low.

Vote with your dollars.

Guitar tablature is meant for human readability...
not for machine consumption.

Granted it's "procedural" and "linear" already, but it's also column-based AND row-based at the same time (readers read down a short row and then over) - you are dealing with text chunks that are easily understandable by a human, but require a lot of "context" and rules for a machine to decipher. Not to mention the fact that it's hand-written by humans, which is another error waiting to happen.

Aside from the "how to do this" aspect, I also had to create a system to 'normalize' tab into a consistent format that lent itself to being queried properly.

The solution? "RiffCode"

My initial implementation goes like this:

• encode single notes and chords into "pseudo-words"
• turn those riff sections into "sentences"
• capture note meta-data when possible (palm-muting, etc)

By storing the data this way I can use full-text search technology to try and gleam results from (which, with ElasticSearch, worked quite well).

In practice it looks / works like this:

input

output

The concept is to pivot each "column line" of tab and keep it as "lossless" as possible (including extraneous spaces) using a basic letter system for fret number and number for strings. The system has some shortcomings (over 26 frets), but is adequate (if not damn good) for 95% of tab.

Ok, now how do we do this a million times over? - Python.

Since I wasn't even sure this was going to work - I wrote fast and carelessly. So what we have is a very inelegant solution that iterates over the text several times and creates several dictionaries - and then re-constructs it at the end.

It's one cluster fuck of a text-parsing function, but it works, and it's fast enough.

"No time for love, Dr. Jones!"

Text parsing from hell!

Anyways, that function is part of the module that I use to read the tab file into a JSON format that I can then insert into my "riff" ElasticSearch index - that is, of course, AFTER I pull the raw tab out of my "scraping" index. :)

Note: I'm using the amazing Requests module instead of a specific ES python module.

So now I've got each tab file (song) split into many "riff-based" JSON documents in my ElasticSearch system... (with a lot of extra meta-data picked up along the way - I'll write another post on searching Spotify and Echonest)

What's next? Getting it OUT in a meaningful way...

What is it?

A fun data/hack project that I've been working on for the past few weeks. Basically put...

Riffbank is a "reverse search engine" for guitar tabs.

Give it a simple section of guitar tab (a "riff"), and it will tell you what songs it could be (plus any other metadata it knows). Give it a try and let me know!

[ still a work in progress, being a small side-project ]

Why?

Anyone who has seen my crazy shit on this blog has probably stumbled upon my 'Enter Sandman Tableau 7 viz', Basically, I set out to see if I could process guitar tab in such a way so it could be re-displayed in an analytical visualization engine like Tableau, and become a bit of a "tab learning app".

Obviously this was a proof of concept and just a cool hack to use Tableau for - but I never completely stopped thinking about the concept of using guitar tab acsii files as a "source of data".

[ some riffJSON action ]

If you've learned guitar at some point in life and been into tech and computers - you've probably used text-file based guitar tablature before. Hell, I can remember using it back in the early 90's for god sake. I would print it out on a dot-matrix printer and keep a mess of it in my guitar case...

Ah, memories. Anyways, it's been around for a long time - and there is NO shortage of it on the internet these days. While writing the sandman viz, I remember thinking "could I do this with a whole shitload of tab files - and if so, what insights (or not) could I gain from that kind 'data' repository?".

[the riffbank elasticsearch cluster]

More to come...

I just got back from spending the last week in the beautiful San Francisco Bay area. If you've never been, seriously, go there. As an obnoxious East Coast / New England guy, I thought I had seen it all. Nope.

Anyways, one of the things we did was visit the SF Museum of Modern Art - the building is a wonder in itself, but obviously the museum hosts countless photographs and art installations from many famous (and not so famous) artists. Honestly, I had no expectations. I was on vacation after all - just doing a fun "touristy" activity between rounds of Grey Goose Oysters shooters, eating sea creatures and pints of AnchorSteam.

I had no idea I'd find inspiration that can be DIRECTLY applied to modern data visualization practices. Advice that doesn't get followed as much as it should...

The exhibit - a complete retrospective of the working life and career of Garry Winogrand (1920s - 1984), including many photos that were never published (he left behind 6.5k rolls of undeveloped film when he passed at the age of 56).

Garry was a famous street-documentary photographer mostly known for his NYC shots in the 50s-early 80s. His many striking photos told a story through the faces and bodies of it's living subjects. Short on hard "data" - but miles long on human interest, wonder, and conveyance. Stories of frozen emotions and reactions, but without a full sense of context (aka interpretative art; mystery).

I came upon a particular photo in the "Down in the Bronx" section of the exhibit that caught my eye. It was an assault scene from the 50s. Murder possibly. There was a well dressed man on the ground, you can see only his well-made shoes and an upturned hat on the ground - near a large pool of blood. You can see people rushing around him, a nun leaning in and praying, and a little boy stading in the background looking on with eyes agape. Clearly "something" had happened just moments earlier.

On the display card that accompanied the photo is where I found my connection. It described how Garry always gravitated toward this type of photo journalism instead of blatantly trying to capture the What or Why for the sake of written narration. This quote from him was below it.

Boom. I immediately tapped this quote out on my phone.

It seems that in all the academic and ideological viz wars we've seen lately - people sometimes forget the importance of storytelling and conveyance. Showing the results without the story is next to useless.

Our industry is about action and communication, not mystery.

The facts mean nothing if the reader can't understand it, conceptualize it, or (even worse) be intrigued enough to even give a shit about it.

The reader might be a literal "reader" of a magazine, newspaper, or online periodical - but really in our case the "reader" is YOUR FUCKING BOSS and YOUR CUSTOMERS. It's essential that they not only know the facts, but the breadcrumbs that led to such facts. Seeing is understanding. You can't make things actionable if they can't be understood (even more important is self-explanatory interactive visualizations).

In any useful data viz - calculations & insightful analysis is good, facts are good, in-depth data is good, but...

It's not only about WHAT you are showing, but about the why, how, and the elusive 'what do we do next'. Data is good, geeky math is good, but if it can't be framed in a way that biz or end-user peeps (or the public) can understand it's just shred fodder that you can use in little Suzi's hamster cage.

Getting and interpreting the data is one part - CONVEYING it is another altogether.
(again - not to get into a Pure Math / Science vs Functional "Art" war - but it bodes discussion)

What do you guys think?

Greetings, it's been awhile since I published anything - my apologies! I've been hella busy this year, but finally have dedicated some time to a mini-project I've been working on for some time. This time, it's a baseball viz / park issue that I can hopefully shed some (theoretical) light on. IN an effort to stop writing massive blog posts - consider this post a intro - more like the "Business Case", the next one will be "Technical HOW, WHY, WHAT".

Anyways, on with the premise...

Tools Used:

"Taming PetCo Park". Click for full interactive version.

Gird your loins. Next time, I'll have all the gory details - Python, SQL scripts and all....

With the release of Tableau 8 just around the corner, and the Tableau 8 Public servers in a (very) limited release beta - I figured that it was good a time as any to see what I could come up with using (a couple) of the new features. Specifically the "floating tile" option for dashboard creation (one that I'm particularly jazzed about, as you'll see)...

"Floating Tiles" = A Game Changer

Previously in 7 (and earlier) you were restricted to putting dashboard "parts" (for lack of a better term - think worksheets, text, and images) into a editable "grid" pattern. While this allowed some cool designs if you wanted to get creative - you could really only go so far. Now since that has been lifted (with the added inclusion of allowing overlapping dashboard parts) we are going to see some really cool designs.

I wanted to visually illustrate how the new dashboard layout rules radically change the dash construction and design in Tableau 8 - so I started up Photoshop to give this particular viz a "grid autopsy". Anyone who is familiar with Tableau in any way should be able to see quickly what we've got going on here.

This dashboard viz is literally made up of 21 separate Tableau worksheets, plus several static images and static text boxes.

• Blue - Tableau Worksheet
• Red - Static Image
• Green - Static Text

Also, the ability to move tiles pixel by pixel and size them pixel makes it possible without contemplating murder.

For the record, SOME of this would have been somewhat possible with 7, but it would have been a total trauma to keep things aligned properly - and generally not recommended for anyone who wants continued grip on their sanity.

To be continued... :) Really looking forward to what everyone else comes up with too!

Howdy. Cheers to all ya'll down there in internet land. I got in a conversation earlier today regarding geocoding addresses in data-sets - it's a pretty common thing, and I've done it numerous times for a WIDE variety of data-points (Bigfoot, Sex Offenders, Concert Venues, etc.), so I figured hell, I'll clean it up and offer it to the Google gods. Maybe someone will find it useful.

"Why aren't we flying? Because getting there is half the fun. You know that."

The script is pretty simple, but it usually gets the job done with minimal modification. I tend to use a small dimension table for geo lat / long data - because generally the coordinates of an address won't change, so there is no reason to RE-Geocode an address (in most cases).

In this example, I'm using a SQL Server connection - but the query usage are so basic that any other could be used just by loading a different module and changing the connection syntax a bit (SQLite, Oracle, MYSQL, etc).

You'll need to download and install this simple Google Maps API wrapper first.

Feel free to give me a shout!

So here we have the 3rd in a series about using Tableau 8's 'Data Extract' API to automatically create TDE files from various data sources without using the desktop client. This time we're focusing on good ole' Comma Separated files.

Much like my SQL Server script, this one tries to guess field names and field data types in order to produce as good of an extract as possible - I've also added a little command line progress bar for your FILE WATCHING ENJOYMENT.

You know, in case you're into shit like that.

Plus, I report on file sizes and row counts! OMG! You ALSO have the ability to run MULTIPLE files in a directory - in case you have a bunch that need to be crunched into their own TDE files - that's right, Christmas come early! (You're welcome)

I used some census data files for testing (as well as Metallica, Bigfoot, and UFO data - but that's a given) as you can see below. I gotta, say processing CSV files is decent speedy - even though I haven't tried to process any HUGE files yet (2GB+).

Bit of explanation about the script parameters:

• cvsfilenamemask = '.csv' It's basically an "ends with" string - so you can use something like '.csv' to process ALL CSV files in a directory, or simply change it to something like 'input.csv' to ONLY process that file from that dir.
• sourcedir = 'C:\\Python27\\' Fairly obvious - this is the folder that the script will look in for any files matching 'csvfilenamemask' above. You can also use UNC file paths like ''\\\\ryrobesxps\d$\\'. Remember to double up your slashes accordingly!
• targetdir = 'C:\\Python27\\' Same as above, except this it the target dir that the resulting TDE file will be written to.
• csvdelimiter = ',' You should know this one.
• csvquotechar = '"' AND this one, but who knows.
• rowoutput = False Set to 'True' to have all the row and column info echoed into the command line window - it's good for debugging data and script errors but it slows things down like 10X (literally). Who knew that printing out thousands of things would be so expensive! ;)

Here's a sample output from some tests I did earlier

Sexy, right? Indeed. Ok, here it is, it's a whopper, so get that Cntrl-C hand ready...

Wh3r3's th3 b33f? Oh, nevermind.

Issues? Let me know below and I can fix them! Maybe.

Coming off the excitement of my last post about writing a simple bare-bones python usage of Tableau's brand-new Data Extracts API from version 8.0 - I figured that it was time to build on that. Let's take a step forward and get a little more complicated - AND a little more useful.

Oh, noes!

I'm encapsulated in a proprietary binary data file OF EMOTION!

Save me, Cringer!

Wait. This could be MORE useful then my last post?

You mean your business doesn't run on a billion rows of dummy shit data? Weird.

Anyways, this time we're going to extract data from SQL Server with a single SQL query and populate a new TDE file.

This script has 3 main features:

I got you, shawty. I GOT YOU.

All of which we had to explicitly state in the last script (even as a 'proof of concept'), and as you can imagine - with a wide result set, defining every little thing and then inserting every little thing can be a huge pain in the ass (not to mention ugly). With my approach, it's literally plug and play - err, paste and play. You can even run queries like "select * from " it's not even necessary to use the field names (assuming they are all unique, that is - more on that later) - and yes, I mentioned that twice for EMPHASIS!

Ye gods! No scripting out my lame_ass_field_names? Huzzah!

Basically, the script works like this:

• Connect to SQL Server
• Execute your SQL query
• Look at the field names and first row data
• Have Python try to guess what data types they are (based on that first row)
• Map those data types and field names to a TDE schema definition (TableDefinition())
• Loop through the entire result set, calling each field by name and type so the correct insert method is used (setDouble, setInteger, setNull, setDateTime, setBoolean, etc)
• Do a little headbanging as you watch the text fly by!
• Close the file (write the TDE to disk)
• Close the SQL connection
• Wipe hands on pants

Booya. Done.

And you didn't have to do a damn thing except give it a server connection and a SQL query. Now you can open that fresh TDE file and have yourself a little data party. { Pants optional }

Here it is! Cut, Paste, and give it a try, gosh darnint!

I threw this together in a few hours, and will probably be adding to it, fixing it down the line. It has very little exception handling and is only around 130 lines; including my asinine comments and whitespace lines. Parts of it are a bit 'inelegant', lots of list building and string mangling - but it works. Quite well actually.

Love to get some feedback so I can make it better. I tested it on many different tables I had laying around, including that crazy MS AdventureWorks2008R2 Data Warehouse DB which uses a lot of strange data types and calculated fields. All worked splendidly.

select a.*, b.*, c.* from table1 a, table2 b, table3 c 
            where a.id = b.id and a.id = c.id

I'm also thinking of making a version of this with command line parameters instead of file editing.

i.e. Python TDE_from_mssql.py 
--file=belly.tde 
--server=servername 
--username=sa 
--database=db45 
--sql="select * from bellybutton"

What's next for TDE file population posts? Web scraping, MySQL, Oracle, flat files? Might as well hit 'em all.