Kettle (Pentaho Data Integration): Connecting to Google Cloud SQL

We were playing around Google Cloud SQL and wanted to see if we can migrate some of our MySQL database data onto the cloud. We opted to use Pentaho Data Integration (aka Kettle) to do some ETL. We were surprised to see that Kettle could not connect properly to Google Cloud SQL (even if Google says it's running MySQL 5.5). So, we had to dig deeper. Here's what we found out.

We tried using the MySQL connection type. But we could not get it to use the correct URL. We also found out the following::

1. Using a MySQL connection type does not allow a custom URL. It was prefixing the URL with "jdbc:mysql://". We needed to use a URL that starts with "jdbc:google:rdbms://".
2. Using a "Generic database" connection type allows Kettle to connect successfully. But it still fails in retrieving meta information about tables/columns/datatypes.
3. Kettle optimizes by using meta data from a JDBC prepared statement (not from the result set). Unfortunately, calling PreparedStatement.getMetaData on Google Cloud SQL JDBC driver always returns null (even after the statement is executed). This prevents Kettle from proceeding to display the table in its UI.

To solve the above problems, we decided to create our own database plugin.

Creating a Database Plugin for Kettle

For more information on developing your own Kettle database plugin go to creating database plugins at Pentaho InfoCenter.

To create a database plugin for Kettle, we need to implement the DatabaseInterface. Here's what we did.

We extended from the original MySQLDatabaseMeta class to get the default behavior of a MySQL connection type. Then we override a couple of methods to achieve behavior that is specific to Google Cloud SQL. We had to specify a URL that starts with "jdbc:google:rdbms", and com.google.cloud.sql.Driver as the driver class.

// the annotation allows PDI to recognize this class as a database plug-in 
@DatabaseMetaPlugin(
    type="GoogleCloudSqlJdbc", // A globally unique ID for database plugin
    typeDescription="Google Cloud SQL" // The label to use in the database dialog
)
public class GoogleCloudSqlDatabaseMeta extends MySQLDatabaseMeta implements
        DatabaseInterface {

    @Override
    public String getDriverClass() {
        return "com.google.cloud.sql.Driver";
    }

    /**
     * As of Google App Engine SDK 1.7.4, the driver does not
     * support metadata retrieval with prepared statements.
     */
    @Override
    public boolean supportsPreparedStatementMetadataRetrieval() {
        return false;
    }

    /**
     * As of Google App Engine SDK 1.7.4, the driver only supports
     * result set metadata (which requires the execution of the
     * statement).
     */
    @Override
    public boolean supportsResultSetMetadataRetrievalOnly() {
        return true;
    }

    /**
     * Due to code that is specific to {@link MySQLDatabaseMeta},
     * a fetch size of {@link Integer#MIN_VALUE} causes an error
     * with the Google Cloud SQL driver. Thus, this is turned off.
     */
    @Override
    public boolean isFetchSizeSupported() {
        return false;
    }

    /**
     * As of Google App Engine SDK 1.7.4, the driver is not supporting
     * {@link Statement#setMaxRows(int)}.
     */
    @Override
    public boolean supportsSetMaxRows() {
        return false;
    }

    @Override
    public String getURL(String hostname, String port, String databaseName) {
        // port number is actually ignored
        return "jdbc:google:rdbms://" + hostname + "/" + databaseName;
    }

    @Override
    public String[] getUsedLibraries() {
        return new String[] { "google_sql.jar" };
    }

    @Override
    public int[] getAccessTypeList() {
        return new int[] { DatabaseMeta.TYPE_ACCESS_NATIVE };
    }

}

While tinkering with Kettle code, we found out that it has MySQLDatabaseMeta-specific code that sets a fetch size of Integer.MIN_VALUE. Something along the lines of:

    if (databaseMeta.getDatabaseInterface() instanceof MySQLDatabaseMeta
            && databaseMeta.isStreamingResults()) {
        sel_stmt.setFetchSize(Integer.MIN_VALUE);
    }

Because of the above, the Google Cloud SQL driver throws an exception. We had to disable the setting of fetch size.

Deploying the Database Plugin to Kettle

This was tested on version 4.4 of Kettle. The resulting JAR file is copied to the plugins/databases folder of your Kettle installation (e.g. /Applications/pentaho/design-tools/data-integration). The JDBC driver from Google Cloud SQL can be downloaded from http://dl.google.com/cloudsql/tools/google_sql_tool.zip, or if you already have the App Engine SDK for Java (90+ MB) (look for the google_sql.jar file under the lib/impl folder). Copy the driver to the libext/JDBC folder of your Kettle installation.

After deploying, restart Spoon (the GUI) and you should be able to see the "Google Cloud SQL" connection type.

Acknowledgements

Thanks to Karl Benavidez, Jose Araneta, and the awesome developers at Orange and Bronze Software Labs for making this possible.

Let us know if this works for you.

JavaScript: A Love-Hate Relationship

I’ve been programming in Java for a greater part of my professional career. As web development got more powerful and demanding (and complex), I ended up dealing with client-side JavaScript more often than I wanted. In most of these cases, I’ve come to hate it. To avoid it, I considered alternatives like Adobe’s Flex and GWT.

Until about five (5) years ago, when I sat down and really started learning JavaScript, I started to love it. It felt like one of those new year’s resolutions that I failed to complete. Good thing, I faced projects that relied heavily on jQuery and Google Maps JavaScript API. With that, I was forced encouraged to dive in and learn JavaScript. So far, I’m glad I did.

Variables and Primitive Data Types

How many primitive data types does JavaScript have? Three: string, numeric, and boolean. It only has three primitive data types compared to the eight of Java. Wow! How simpler could it get?!

As it turns out, there are two more primitive data types: null and undefined are also considered primitive data types. Undefined is the values of a variable with no value. Variables can be emptied by setting the value to null.

Note that primitive data types are automatically wrapped as objects (e.g. a string literal is automatically wrapped as a String object). This makes them possess properties and methods.

var text = 'hello'; // or text = new String('hello');
var len = text.length; // returns a number
text.charAt(1); // returns 'e'

Native Objects

The JavaScript language provides some native objects: Array, Function, Date, Error, and Math. Objects can have properties and methods. Objects are key-value pairs. The code snippet below shows an object with five properties. Two of which are functions.

var myObject = {
  prop1: 'hello',
  prop2: false,
  prop3: new Date(),
  function1: function(param1, param2) {. . .},
  function2: function() {. . .}
};

// the above is equivalent to this:
var myObject2 = {
  'prop1': 'hello',
  'prop2': false,
  'prop3': new Date(),
  'function1': function(param1, param2) {. . .},
  'function2': function() {. . .}
};

// accessing the properties is like indexing a map
alert(myObject['prop1'] == myObject.prop1); // true

No Classes, Just Objects

As I dug deeper, I found it confusing that there are no classes, just objects. Coming from a Java background, I wanted to learn how to apply OOP. With that, I looked for creating private member variables and functions. JavaScript is a class-free, prototypal language. Instead of defining classes, and creating objects based on classes, it simply creates objects that inherit from (parent) objects that act as classes.

JavaScript is a prototype-based language.

Prototype-based programming is a style of object-oriented programming in which classes are not present, and behavior reuse (known as inheritance in class-based languages) is accomplished through a process of decorating existing objects which serve as prototypes. This model is also known as class-less, prototype-oriented, or instance-based programming.

JavaScript MDN

This is the part I found confusing. But after a while, it’s becoming clear. Declaring a class in JavaScript is as easy as defining a function.

function Student(name) {
  // This is the constructor
  // We can add properties (instance variables) to "this"
  this.name = name;
}

Since functions are objects in JavaScript, we can add properties and methods. In this case, we use the prototype property of a Function object to add methods.

Student.prototype.greet = function(greeting) {
  alert(greeting + ', my name is '
      + this.name);
};

Student.prototype.anotherMethod = function() {
};

We can create Student objects by calling the constructor with the new keyword.

var student1 = new Student('John Smith');
student1.greet('Hi'); // will display 'Hi, my name is John Smith'
alert(student1.name); // the name property is public

So, how can we provide private members to classes? In JavaScript, functions are used to provide scope and hide variables. For this, let’s change the public name property of the Student class to become private. We also add a public getter method to access the private name property.

function Student(name) {
  // This is the constructor
  // We can add public properties (instance variables) to "this"
  // this.name = name;
  var _name = name;
  this.getName = function() { return _name; };
}

This also changes the public member method in its reference to the private name property.

Student.prototype.greet = function(greeting) {
  alert(greeting + ', my name is '
      + this.getName());
};

Student.prototype.anotherMethod = function() {. . .};

Now that the name property is made private, the usage of Student class will also change.

var student1 = new Student('John Smith');
student1.greet('Hi'); // will display 'Hi, my name is John Smith'
alert(student1.name); // will return undefined
alert(student1.getName()); // will return 'John Smith'

Closures

JavaScript uses closures to provide access to variables that continue to exist even after the variables are out of scope. Let's take the following example from one of Douglas Crockford's talks.

var names = ['zero', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine'];
var digit_name = function(n) {
  return names[n];
};

In the above example, the names array is declared at the global scope. We can move names as a variable inside the function (and avoid name clashes with other functions). The result looks something like this:

// var names = [];
var digit_name = function(n) {
  var names = ['zero', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine'];
  return names[n];
};

But the above is slower, since the names array would have to be (re-)initialized every time the function is called. Here's where we can use a closure to allow the function to continue to have access to the names array variable even after it is out of scope.

var digit_name = (function() {
  var names = ['zero', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine'];

  // return a function
  return function(n) {
    // var names = [];
    return names[n];
  };
})();

Notice that the variable digit_name is assigned the result of a function call. To make it clearer, let’s try re-formatting the code. Hopefully, this makes it clearer.

var digit_name = (function() {...})();

The open-and-close parenthesis immediately calls the function. And that function returns a function.

var digit_name = (function() {... return function(n) {}; })();

Closures are also used to declare private members. In the Student class example, notice how the getName method still had access to the _name variable?

Understanding the Misunderstood

JavaScript is the world’s most misunderstood language largely because people aren’t taking the time to learn it. I hope that developers continue to take programming seriously.

It is the language that most people use without bothering to learn it first.

Programming is a complicated business. It should never be taken in ignorance.

Douglas Crockford - Senior JavaScript Architect, PayPal

The Problem is Fixed-Scope, Not Fixed-Price

The problem with fixed-price software projects (and contracts) is not really fixed-price; the problem is fixed-scope.

In this article, when I say fixed-price, I also mean fixed-budget. It is perfectly normal for an organization to provide a budget (maximum allowed monetary expense) for the creation of software to solve their problems. It is good that the organization determines how much they can afford to spend for the software, and put a cap on it; and anything beyond that is something they couldn’t afford.

So, if fixed-price is not the problem, then why is fixed-scope the problem?

People in the I.T. world often hear fixed-bid or fixed-price projects. We rarely hear fixed-scope projects. But underneath it all, any fixed-price project that has a lengthy detailed requirements document (more popularly known as RFP or request for proposal, or RFI or request for information) has a big chance of having fixed-scope. Instead of focusing on requirements, the lengthy RFP document goes on to specify a solution design. This solution design causes fixed-scope! This fixed-scope could be dangerous. Was the solution design tested? Was it verified to successfully satisfy the requirement and solve problems? If not, this fixed-scope becomes the problem.

Good business analysts can readily distinguish requirements from design. A good requirement should be design free. It should also be verifiable with acceptance tests. If the contractor has difficulty writing design-free requirements, it’s probably better to just write user stories.

With the detailed requirements document, the contractor (the organization looking for a vendor to develop its software) is already assuming that the solution (albeit incomplete and untested) is solving the problem, and is just looking for a vendor to supply the developers who’ll build the system (according to spec).

Detailed requirements are "actually" solution design

I watched Mary Poppendieck on her talk about Agile Under Contract. She points out the following lessons learned:

1. Detailed requirements are "actually" solution design.
2. Development is a learning process.
3. Start with a clear understanding of the critical business results that must be delivered.
4. Mitigate development risk with frequent delivery/assessment.

I won’t go through all four (4) lessons. I’ll focus on the first one, as it is most relevant here. Detailed requirements are "actually" solution design. Mary goes on to say that,

The responsibility of success lies with the solution designer.

Responsibility of success lies with the organization that specifies the detailed "requirements".

The problem is that the so-called detailed "requirements" in the RFP are "actually" solution design. Unfortunately, the solution design are done by amateurs who didn’t care enough to test and verify their solution. What if the solution design in the RFP was incorrect or incomplete? What if it conflicts with another section of the lengthy RFP? Who should be the one to fix/change it? The vendor that was hired to build the software, or the organization that approved and sent out the RFP?

For me, it should be the organization that approved the RFP in the first place. Since they’re the ones who "designed" the solution, they should be the one to correct it, and pay accordingly for the changes. Instead of coming up with a "pieced" together solution, they should go through the solution design (cut out a single flow from start to finish with some meaningful end, with limited options along the way) and have tests to prove that it works.

I often encounter RFPs that go into detail and even provide UI wireframes and screenshots. This level of detail gives them the false impression that the requirements/solution have been detailed. But they fall short of not having a domain model (or even a data model). It’s as if they copied portions of solutions that they liked (only God knows where they got them), and compiled them together. Any good business analyst would see that the solution does not "come together".

I hope to see more fixed-price projects that go through a more collaborative initial phase to flesh out the requirements, and develop a solution design that can be proven to work and solve stated problems. But be careful! I don’t want to go back to doing big design up front (BDUF). I prefer to see a time-boxed (two weeks or less) envisioning iteration (called iteration 0 in agile modeling) where the requirements are understood, and just enough modeling is done. This should allow for a better (understood) detailed "requirements" or solution design. And the development can start to build a small portion of the whole solution and have users (and other stakeholders) providing feedback. This feedback builds trust between contractor and vendor. Ideally, if the solution design is well done, it can be contracted to a another vendor (and not the vendor that helped design the solution).

What if the contractor does provide a good complete tested solution design (not the half-baked detailed requirements), can a vendor go fixed-price? The answer is definitely, yes! An experienced vendor (who has done something similar) can develop the system (based on the provided solution design) with great accuracy with the effort and its cost.

In fixed-price and fixed-scope projects, the only variable left is quality. Do we really want the quality of our software suffer? Can any contractor really live with fixed-price and fixed-scope projects knowing that quality is going to suffer?

References:

• Requirements vs. Solutions
• Scrum and Fixed Price Contracts
• Where does a business analyst fit in Scrum?
• Fixed Price Contracts for Agile Projects
• Writing Quality Requirements
• Ten Contracts for your next Agile Software Project