ThatJeffSmith

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.

click here for beer

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!

What’s in your archive?

“Any photos or videos you’ve shared on Facebook
Your Wall posts, messages and chat conversations
Your friends’ names and some of their email addresses”

Publicly Available Datasets

Kris reminded me about this, some really cool stuff here including the Human Genome Project.