So I was alerted to this blog post by a colleague and I was floored on what Cloudera is doing.
Basically, they are creating a new feature for objects that can store multiple levels in a single table. In a traditional relational structure, you may have an Orders table with another table for Order_Details. You would simply JOIN the two tables together when needed.
CREATE SCHEMA example; CREATE TABLE example.Orders (order_id int NOT NULL, order_date date NOT NULL, customer_id int NOT NULL); CREATE TABLE example.Order_Details (order_detail_id int NOT NULL, order_id int NOT NULL, product_id int NOT NULL, quantity int NOT NULL, price numeric NOT NULL);
And when you want to query both tables:
SELECT o.order_date, sum(od.price) FROM example.orders o JOIN example.order_details od ON o.order_id = od.order_id GROUP BY o.order_date;
What Cloudera is doing is an Object approach which combines both tables into a single table using a feature called a STRUCT. This is similar to a composite type in Greenplum, PostgreSQL, and HAWQ.
--Impala CREATE TABLE Orders (order_id int NOT NULL, order_date date NOT NULL, customer_id int NOT NULL, Order_Details ARRAY<STRUCT< order_detail_id: int, product_id: int, quantity: int, price: numeric>> );
I get this approach when working with a OLTP system and developers want to match their Classes to the database structure but I really don’t see the benefit of this in a big data platform. It just makes it difficult for Database Users to understand and use the data.
Every database I’ve ever worked with uses Schema.Table to organize table structures but not Impala! Cloudera has decided to break away from the decades old standard and use the dot notation for their nested fields stored within a STRUCT. If you adopt this silliness, you are adopting a standard that no one else uses. Why would Cloudera want that? Vendor lock-in maybe????
--Impala select * from orders.order_details;
Orders isn’t the schema in the above example. This is a table. Cloudera just broke SQL!
Cartesian Product? Nope, Just Vendor Specific Syntax
Notice how this query appears to reference two different tables without specifying a join.
SELECT o.order_date, sum(od.price) FROM orders o, o.order_details od GROUP BY o.order_date;
They even allow you to do an OUTER JOIN to the STRUCT ARRAY without defining the columns to join on.
SELECT o.order_date, sum(od.price) FROM orders o LEFT OUTER JOIN o.order_details od GROUP BY o.order_date;
Reasons Why Cloudera is Doing This
Here are my guesses as to why they are doing this.
1. They hate SQL or just don’t understand it.
2. Vendor Lock-In. They need ways to make it difficult for customers to switch to a competitor’s product.
3. Impala is bad at joins. Organizing tables like this make it easier for Impala’s query optimizer to create a robust query plan.
Please Cloudera, just stop!