Oracle SQL Developer Date/Time format

One of the most super annoying things about Oracle SQL developer is that it defaults to a not so useful (at least to me) date format along the lines of 15-NOV-11.  I prefer to see something helpful like an  ISO 8601 formatted date/time.

The solution is to, in Oracle SQL Developer, go to: Tools > Preferences.

Then in the Preferences dialog, select Database and drill down to NLS.

In the NLS parameters you'll be able to set the Date Format to:
YYYY-MM-DD"T"HH24.MI.SSFFTZR"Z"

I have found that on some systems, you may need to use a setting like:  
YYYY-MM-DD HH24.MI.SS



References:

http://stackoverflow.com/questions/8134493/how-can-i-set-a-custom-date-time-format-in-oracle-sql-developer

http://orastory.wordpress.com/2007/05/15/dates-timestamps-and-iso-8601/

git: purging the lurking big file

I was just working with an old repository where I had inadvertently committed a large file ~300MB.  I subsequently removed it, but not in a way that took it out of history.  I discovered it when I went to push to GitHub and it complained.  These are the steps I used to get rid of the file:

• git filter-branch --prune-empty -f --index-filter 'git rm -rf --cached --ignore-unmatch source/spikes/mo_offender_fak930.txt'
  -tag-name-filter cat -- --all
• git update-ref -d refs/original/refs/heads/master
• git reflog expire --expire=now –all
• git gc --prune=now

Here are some pages I found useful to figure this all out:

http://stackoverflow.com/questions/6017987/is-there-a-way-in-git-to-list-all-deleted-files-in-the-repository

http://stackoverflow.com/questions/2100907/how-to-remove-delete-a-large-file-from-commit-history-in-git-repository

http://naleid.com/blog/2012/01/17/finding-and-purging-big-files-from-git-history

Engineer's Notebook

I record daily activities, notes, thoughts, diagrams, ideas, some reference material and special insights in their own paper notebooks.  If you were going to jot something down on a scratchpad, put it in here.  Document who you were collaborating with.  Why?  When I am in stand up meetings or talking with customers I go the paper route because I can simply open up the journal and write.  Also, it is less intimidating and error prone than having a computer between you and the people you’re talking with.

Use a pen.  Erasing and re-writing is too much work and will slow you down.  Simply, draw a line through the erroneous material and continue on.

When it comes to choosing a paper journal it is a very personal decision.  Many people like the Moleskine line of journals.  Ecosystem makes similar more colorful journals of the same format.  The things to consider when choosing a journal are:

• Size.  Will I have it with me when I need it?
• Do I want one that lays flat?
• Does it utilize a closure of some kind?  Will it be a pain to open and close?
• Does it have a pocket in the back for small papers?  Do I care about that?
• Do I want plain, ruled or grid paper?
• Do I want a few pages or lots of pages?

Personally, I use a journal that is made by Miquelrius.  It is about 6 inches wide by 8 inches tall and about an inch thick.  It has rounded corners and the pages are grid ruled.  Having the grid rule makes it easy to sketch out design ideas.  Another nice thing about this page size is that you can easily “scrapbook” in index cards or ever so slightly trimmed down print outs.
  

Create a margin on the outside of every page.  Put a date in the margin when you start a new entry.  You may want to add the time as well.  Some days there isn’t much to note and I’ll continue on the page with the next day.  In that case I draw a horizontal line between days to make it more visible.  The margin can be used for icon style call outs, indicating action/todo items, work partners, work times and post activity summaries or thoughts.

It is my experience that page flags get caught on things and torn up.  Bookmarks fall out and get lost.  Therefore, I recommend using Book Darts—small metal pieces that can be used to mark your page and point to a specific line.

With so many thoughts and notes recorded in your journal you’ll want to add your contact information inside the front cover with an offer of reward if returned.

I’ve experience quite a few benefits from keeping this journal.  For example:

• Meeting action items and decisions documented and easily formally distributed.
• Look at back at project notes from years ago and see what was done and why.
• Able to reproduce work flow for prior infrequent work.
• Able to identify collaborators for project work.
• Identify when software was installed on a machine and what versions.

OneNote

For long term, shared electronic notes I utilize OneNote.  It is available on a wide variety of platforms now.  Notebooks can be local only on some platforms or synced to the cloud.  It lets you bring together your project notes into a portfolio style view.

OneNote provides numerous ways for getting information into it:

• Typed and handwritten notes (it supports inking!)
• Custom screen capture feature
• Insert a picture from a file or camera!  (Use the camera to bring in notes from your journal.)
• On Windows OneNote can OCR your inked writing, handwriting or print inside of images for search.
• Print to OneNote
• Drop in documents
• Link to web pages
• Voice notes
• Drawings by hand and assisted

Compartmentalize

I would also suggest that you not use a single journal for all your needs.  Use one for work, one for the research you do outside of work.  In addition, consider a free form journal for capturing personal thoughts, events and accomplishments.

Summary

There are many good reasons to start and maintain an Engineer's Notebook.  Get a journal today and start the habit!

Strata 2014 Retrospective

Introduction

This year I was fortunate enough to be able to attend the Strata conference put on by O’Reilly and Associates.   I started this retrospective by doing a SWOT analysis of my experience.  I then expand with some logistics info and take away research ideas.
I had high expectations for this conference given its marketing.  The last conference of this caliber that I had attended was ÜberConf.

Strengths

• The chairs in the session rooms were comfortable.
• Healthy snack options were available at breaks.
• Sponsored sessions were identified on the agenda.
• There were lots of vendors at the expo.
• Most if not all vendors had technical people on hand.
• Mobile app had maps and schedule.
• They did provide a daily print out of the session schedule and map.

Weaknesses

• The mobile app was more frustrating than useful.  It was always wanting to update.
• Being registered in the directory means you’ll start getting spam before the conference even starts.
• Navigating the website to find specific session information was difficult.  Easier to find it through Google.
• This is not an inexpensive conference to attend in terms of conference cost, travel and hotel expense.
• Full day workshop
• Late notice on software to pre-install
• Not enough AC outlets
• No tables!

Unless they address the logistics issues of the workshop environment I can not recommend attending one.

Opportunities

• From key notes, sessions and the vendors you get to learn about what tools/processes the future holds.
• Discern what tools/processes people are using now.
• Talk with other attendees about the work they are doing and the approaches they are taking to it.
• Some insights I had are:
• Many are using Python and Julia for ETL.
• R is being used for analysis.
• Data people are starting to think about discuss data patterns.  Such as the Side Kick Pattern presented by Abe Gong (@AbeGong) a Data Scientist from Jawbone.

Threats (or why wouldn’t I want to attend)

• Fundamentals are potentially better learned with targeted training.
• Attending this conference could prevent you from attending another more relevant conference.
• The target audience for this conference is narrow.  People that identify with big data, data science and business intelligence are well served by this conference.

When attending it helps to have specific questions or problems you are looking to solve.  This gives you a good context when choosing sessions and meeting with vendors.  (There are lots of vendors!)

Internet

The conference wireless was acceptable for as many people that were using it.  Internet in the hotel lobby was very good.  There is wired internet available in the hotel room for free.  I did not have an opportunity to use it.  There is also pay to use wireless available in the room.

Research

I came away from the conference with much I want to research and experiment with and people to connect.

Tools and Libraries

• scikit-learn.org
• wakari.io web based Python data analysis
• Anaconda Scientific Python development environment
• Getting IPython set up by hand is a pain—Anaconda is a must on Windows machines.
• Pandas Python data analysis library
• Microsoft PowerBI (Office 365)
• D3.js
• The R programming language
• Pentaho, Tableau and other BI tools
• Hadoop
• Aerospike

Techniques

• Adjacency Matrix
• pivot and fold operations
• hexagonal binning
• use visualization for data quality checks
• confusion matrix
• predictive modeling fundamentals
• machine learning
• The work of John Tukey (Statistics)

Speakers

• Abe Gong (@AbeGong) Jawbone Sidekick pattern
• Monica Rogati (@mrogati) Jawbone UP
• Emil Eifrem (@emileifrem) Neo4j
• Wes McKinney (@wesmckinn) Pandas creator
• Joe Hellerstein (@joe_hellerstein)
• Jeffrey Heer (@jeffrey_heer)

Joe and Jeffrey presented: Data Transformation: Skills of the Agile Data Wrangler

• Magda Balazinska

Can we make big data management easier?  Her 3 research threads are: effective, easier and cost effective.

Summary

I came away with a better appreciation of what constitutes data science, the skills needed, the tools utilized and the vendors in the different areas.  If I attend again in the future I would likely skip the workshop day.  I would do additional prep working thinking about specific questions I may have for the technical people that the vendors make available.

Tips for working with the MongoDB aggregation framework

1. Review the data.
   What are the data types? What is the structure? What do the values mean?
2. Build out the query one stage at a time.
   
3. Start with one pipeline operation and a limit operation at the end. Review that you are getting the results you expect. Keep the limit operation in place as you add operations. Verify expected behavior every step of the way.
   
4. Remove the limit operation as the last step .

The online documentation will be very useful:
http://docs.mongodb.org/manual/aggregation/
http://docs.mongodb.org/manual/core/aggregation-pipeline/
http://docs.mongodb.org/manual/reference/operator/aggregation/


This reference includes some nice material regarding comparison with standard SQL operations: http://docs.mongodb.org/manual/reference/sql-aggregation-comparison/

Understanding US Zip Codes

In the United States there are States and Provinces.  These States and Provinces have Cities.  The same city name may be used in different states.  For instance, Springfield, MO (Missouri) and Springfield, IL (Illinois).  A single city may be covered by more than one zip code.  A zip code may span states.

The graphic above shows 13 of the 16 Zip codes that are used for Springfield, MO.  For Springfield, IL, different Zip codes are used.  Here 7 of Springfield, IL 36 zip codes are shown.

Reference

http://zipcode.org/ 
http://zipcode.org/city/MO/SPRINGFIELD
http://zipcode.org/city/IL/SPRINGFIELD

MongoDB M101P Windows 8.1 64bit Installation

This document walks you through installing the tools and libraries needed for the MongoDB M101P course available through university.mongodb.com

Installing MongoDB

Go to www.mongodb.org/downloads

Click on the huge green button to download MongoDB in a MSI file.
(The current version is 3.0.1)
”Unblock” the downloaded MSI by, right clicking on the file and choosing properties.  Then hit the unblock button.   Then hit Apply, then OK. If you don’t have this that’s okay.  Continue on.

Run the downloaded MSI file – you’ll likely have to approve installation 
I prefer to choose to do a custom install to C:\mongodb
After you have finished installing, you should now have C:\mongodb
With a subfolder C:\mongodb\bin
Create a folder C:\data
Create a subfolder C:\data\db

Installing Python

Go to the Python download page: www.python.org/download


Choose the Python 2.7.9 X86-64 installer.  Download and install it.  Let it go with the default path of C:\Python27
As of this publication date, the course content is not compatible with Python 3.x.  So, don’t get it!

Setting your path

If you want to be able to easily run the Python and MongoDB binaries from the command line you need to set your system path.

Choose System

Choose Advanced System Settings.  Then choose Environment Variables button.


Select the System Variables path as shown above.  Then click edit.  Append this to your system path:
;C:\Python27;C:\Python27\Scripts;C:\mongodb\bin; 
Don’t miss the leading and trailing semi-colons. 

Installing PIP

Go to http://www.pip-installer.org/en/latest/installing.html
Download get-pip.py I did this by, right clicking on the link and choosing, save link as
Next, open a cmd prompt.  You can do this with  <windows> + <r>  then typing cmd <enter>

Change to the directory where you downloaded get-pip.py, most likely C:\users\yourName\Downloads, and run it:
python get-pip.py

Installing pymongo

Visit https://pypi.python.org/pypi/pymongo/#downloads

Download and run the win-amd64 EXE and run it.  The current version is pymongo-2.7.2 (pymongo-2.7.2.win-amd64-py2.7.exe).  The installer will be blue background with white text and will have a title similar to the following:

After installation, run pip list to see that pymongo is installed (should be the new version).

Installing bottle

Use pip to install bottle.  Note that we have to use a special option to specify the version of bottle that we want installed.
pip install bottle==0.11.6

Project folder

Create yourself a project folder such as C:\Projects\m101p

I recommend that you create a subfolder for every weeks course files.  So, for the first week you would have C:\Projects\m101p\week1

Do not put course files in either the mongodb or python27 installation folders.

Starting MongoDB

I also suggest that you see this gist: https://gist.github.com/k0emt/10342867 as a way to start up a development mongod instance.

Stopping MongoDB

Use the mongo shell to properly shutdown mongod. Issue the following commands:

use admin

db.serverShutdown()

Subsequent to issuing the commands you will see some error messages.   This is normal as the shell just lost connection with the server.  You can now use <ctrl> + <d> or type the word exit to quit the shell.

The costs of poor usability

Today, I am going to share with you a little story about the costs of poor usability.  Imagine if you will, that you are going to do the annual renewal for your website.  It looks like there might be some alternate lower cost, better feature options available from the vendor now.  However, you can't see them because you aren't allowed to "change plan" when you have a balance due.

The screw up

Here's where it gets interesting.  So, you think, well I'll just "cancel" this pending charge over here on the billing page.  Okay, some generic message about deleting a file or other.  Not super clear.  Okay, click go.  (You're not too worried because there is a different cancel site feature on the website control panel.)  Now you try and go back to your website control panel and look at the other plans.  Well, it keeps having some sort of unspecified error.  Then your domain email disappears.  Oh noes!  The cancel button on the billing page, actually blew away your entire domain!

The recovery

So, I was able to get in touch with technical support via an 800 number.  Not too bad a hold time since it was the middle of the night.  He worked with me and we got the website back up.  Good thing I had a full local instance of it, because they didn't have anything.  They had immediately deleted the full site, logs and backups!  I had to go through and manually recreate email accounts.  But then I had to wait for users to "activate" their accounts before I could restore their email from the vendors back ups.  This entailed about two hours of hold time the following day with less than 30 minutes additional time needed to talk with a tech to finish restoring the email.

The costs

All told, the cost to me ended up being about 2 hours and 15 minutes of hold time and I'm thinking less than 90 minutes of tech time.  (I'll try not to dwell on the being on hold for 30 minutes and then having the call dropped.) As a side impact of this was a lost day of productivity.

The vendor took the hit of paying for an 800 number line for almost 4 hours!  Then there was the hit for the tech time of about 2 hours.  That's a lot of money.

Domain users panicked.  Will I ever get all of those hundreds of emails back?  Will that important email I've been waiting for come in while we're down?  They had to spend some time activating their account and waiting for their email box to be restored.

Conclusion

Poor usability costs everyone.  Imagine if there had been a very clear warning along the lines of "hey, this operation will DESTROY YOUR ENTIRE DOMAIN!"  Then followed up with a confirmation email or text?  (I mean we do that for subscribing to an email list.  So, it seems like that would be a legit thing to do for canceling an entire domain.)  I think a lot of lost time, spent money and pain could have been avoided with better usability.

Important Information for KCDC MongoDB Workshop

TL;DR

The single most important thing you need to know is that if you show up with a machine that is running Windows XP or a machine that doesn't have a JDK on it, you're gonna have a bad day.

Prerequisites for your computer

OS: Windows 7 or 8, preferably 64bit, Linux or recent OS X
Have a Java SDK 1.7 installed and configured with IDE on machine

Why? Because we will utilize features that were introduced in MongoDB version 2.2.

Windows XP and older versions of Linux/OS X ARE NOT SUPPORTED by MongoDB 2.2+

Prerequisites for you

Review and be familiar with JavaScript Object Notation at http://json.org
Be able to run and use the command prompt / terminal of your OS.
Be able to create code, add a jar/library, compile and run code in your favorite IDE.

Primary Goal

It is my primary goal that you leave the workshop with a functioning MongoDB environment and knowledge of the fundamentals with the skills to do routine development work.

Course Outline

• Introduction and Installation of MongoDB
• Schema (Relational and Document Oriented)
• Creating, Reading, Updating and Deleting documents (CRUD)
• Advanced CRUD - sub documents, arrays, sorting, limiting and other operators
• backups
• Performance/Indexes
• Aggregation Framework
• GridFS
• Replication
• Sharding Overview
• Open Lab - Time Permitting

MongoDB Schema

One of the major aspects of MongoDB is that it is a document store.  You can put anything you want into a document-- it is schema-less.  However, in many cases the documents stored in a collection do consist of the same fields.  So, in answer to the question, "how do I determine the schema of a collection?"  There are a couple options.

1. Manually/visually inspect the contents of the collection
2. Use a utility to examine a single document
3. Use some sort of utility to examine all of the documents in a collection

Manually inspecting the collection

As you can imagine is simply, "using" the database, and doing a db.collectionName.findOne()

For thoroughness, you'd probably want to examine more than one document.  This is where a db.collectionName.find().pretty() will come in handy.

Use a utility to examine a single document

I created a small python utility given a database name and collection name will give you the keys for a document in the collection.  This requires you have Python 2.7 and pymongo installed on your computer.  I put this in my ~/bin directory and chmod +x it.

Use a utility to examine all of the documents in a collection

Skratch. has a cool extension to the MongoShell which examines all of the documents in a collection and tells you how many documents are using the field.  Fields can vary in type by document.  So, this tool even breaks down the occurrence of the field by type!  It is on github at: https://github.com/skratchdot/mongodb-schema/

MongoDB examples, Replica Set and GridFS

MongoDB Replica Set with Python example

I published a short lab on working with MongoDB replica sets in Python on GitHub https://github.com/k0emt/mongodb_repset_experiment

One thing to note is that I included all of the replica set nodes in the connection information.  That is because if "regular" primary node was down at the time of initial connection the code would fail.

The nodes in the replica set will figure out who should be primary and that will happen auto-magically behind the scenes.  However, your client code still needs to deal with needing to reconnect.

Adjust the counters if you want to have the client up and running longer while you experiment.

MongoDB GridFS with Java example

Example code for demonstrating GridFS and the metadata field with Java was also published on GitHub  https://github.com/k0emt/gridfs_example_java

By utilizing the metadata field you can keep your document metadata with it.