Free Beer or How to Get Free Demo Data from iTunes

Are you sick and tired of playing with the same sample data? Do you shudder at even the mention of the name, ‘Scott?’ First of all, if you’re still using SCOTT, you should try out HR instead. And if you want some beefier tables and need to experiment with partitioning, then SH is the way to go.

But eventually you will get to the point where if you see another SALARY or DEPTNO in a query, you’ll just scream.

So what’s a data geek to do?

Build your OWN demo tables!

Too lazy to do that? Ok, jump to the end of this document and I’ll share my BEER table. It’s hash partitioned, has about 12,000 rows in it, and come in very handy when you’re travelling and want to know what’s available locally.

So the problem with building your own demo tables is that you need some data to pull from. Thanks to the Apple machine, just about everyone has a copy of iTunes. Did you know you can export your iTunes library to a text file? Just mouse-right-click in iTunes on the left where it says ‘Music’. It will create a tab delimited text file.

I recommend converting it to spreadsheet before continuing…let Excel or OpenOffice auto-magically use TABs as a column separator.

You can now import that data to Oracle as a new table!

You have lots and lots of options on how to proceed. You could build a SQL*Loader control file, you could do the 10/11g EXTERNAL TABLE stuff, or you can cheat and use your IDE to auto-import the data for you. Thankfully SQL Developer supports this.

Import Table Data using SQL Developer

Connect to the database

Mouse-right-click on the Tables tree node lable

Point to your file, and answer some questions!

You can get away with the INSERT method, I doubt anyone has a million+ iTunes library. When it’s finished churning, you’ll have a decent sized un-normalized table to play with. For bonus credit, create an ARTIST, COMPOSER, ALBUM, etc set of tables, and replace the text with IDs pointing to the parent records. It’s good SQL practice

Now that's classy data!

Beer here!

Ok, I made you suffer long enough. Here’s your BEER table. I’ve built the following script using my new favorite SQL Developer feature, the Cart. After you download the file, you’ll just want to login to Oracle as the user who want to own the data and run the ‘Generated-20111117140806.sql’ file. Before running the file, please edit the BEER.sql file and insert appropriate tablespace names. You’ll notice the table is partitioned. If you don’t own the partitioning license, then feel free to create it as a straight up ordinary table instead.

You can also add a
‘CONNECT SCOTT/TIGER’
to the top of my script so it’s loaded to the user you want to own the objects.

After you’ve run the script, you SHOULD have something that looks like this

Please query your data responsibly.

We are surrounded by data

Did you know you can have your Facebook account exported and archived? That could make for an interesting table or two!

Very impressive brewery list. I checked out the Western Australian ones, I was impressed with the range. Even an awesome little one down south where I proposed to my wife!
Perhaps only missing the Indian Ocean Brewing Company

I mostly write about working with Oracle databases, mostly. I currently work for Oracle, but I'm not an official spokesman, nor am I authorized to speak on behalf of Oracle Corp. In other words, what you read here are my words and ramblings.

If you've detected a bit of snark, that means you've been paying attention. +1 bonus credits for you!