Learnings from implementing Hive on Google Cloud Storage

Cloud Storage is the managed object store offering from google cloud. In our organization we are making use of cloud storage as the backend for storing data. This enables us to create tables in both HIVE and Big Query while maintaining data at just one place.

Having hive and big query on top of the same data enables us to

1. Run our scheduled and legacy data workflows on cheaper spark compute, accessing the data using the hive meta store.
2. All our OLAP can be done using big query which is faster and comes with an intuitive user interface.

While hive is built to work on top of traditional file systems such as HDFS , it works well with object storage as well. Object storage differs from the traditional file storage in the following ways.

1. There is no concept of folders/directories.
2. All objects have a UUID associated and is stored in flat structure. Each object will have a detailed metadata associated with it. This flat structure allows object store to scale effectively since they are not constrained to traditional name stores which fails at handling density and a particular storage branch.
3. In a traditional file system renaming files is a constant time operation. But in an object store each object insider the conceptual folder needs to be renamed.

Inserting data into hive table

Hive insertInto operation into an existing table seems to work pretty slow in GCS. This is because insert into writes all the data into a temporary directory and then renames the temporary directory into the main directory once the entire write is successful . This works well in a traditional file system. But when it comes to an object store, renaming is not a constant time opetation. Because of this insert into statements seems to take a lot of time when there are a lot of objects involved.

We solved this by converting all our insert into’s into a Save operation in GCS. Below is the high level algorithm we used

boolean insertInto( df:DataFrame , table: String , partitionCols : List[String]){
Map[Partition] = findPartitionValues(df,partitionCols)
for(partition : Partition ){
filteredDF = filteDF(df,partition)
val path = formPath(table,partition)
filteredDF.save(path)
}
}

Maintaining data and access control

For all the important data assets its best to go for a one to one relationship between tables and buckets. That is only data associated with a single table goes into that particular bucket. This enables you to

1. Control access at the bucket level itself. Even though you can control access to the datasets at a BQ/HIVE data warehousing level. Having access control at the source level gives you finer control and single source of control.
2. It will be easier to calculate storage cost associated with a bucket this way.
3. You will also be able to archive data of a table, by changing the class of a bucket.

Soft Delete Strategy

Soft delete is a new feature that was rolled out to all GCS buckets starting last month. This keeps deleted data in the soft deleted space for configured amount of time before permanently deleting the data.

In order to make use of this feature we divided our buckets into two

1. In which intermediate working tables are stored . These buckets were configured to not have soft delete enabled. The intermediate tables are often snapshot tables and are re-written , this is considered objects delete and would have consumed a lot of soft delete storage space. Classifying them as
2. In which important downstream facing tables are maintained. These buckets were configured to have a larger window of soft deletion period. To safeguard from accidental deletions. This is better than backing them up to a cold line storage which involves a high network and class A/B operation costs.

Cost Implications

Google provides multiple classes of storage depending on the frequency of data access. They are nearline, cold line and archive . Google also provides a new storage class called auto class which automatically categorizes and stores the data depending on how we are accessing it. For most of our buckets we have configured them to be auto-classs. We are seeing significant cost benefits from this change.

Dealing with growing storage

With managed storage comes the ability to store unlimited amount of data but it also comes with a responibility to clean up unwanted data. We have made it a practice in our team to prune tables which are verionesd snapshot of data at various points in time. By making pruning of data a part of the data management workflow, we are seeing significant reduction in storage cost and elimination of manual work for one time clean ups.