Hive as a tool for ETL or ELT

Overview of ETL and ELT tools to process data

Data integration and data management technologies have been around for a long time. Tools that extract, transform, and load (ETL) data have changed the landscape for traditional databases and data warehouses. Now, in-memory transformation ETL tools make extract, load, transform (ELT) and ETL even faster. For big data, is it possible to use built-in Hadoop tools to extract, load, and transform your data, rather than using traditional ETL tools?

Most ETL software packages require their own servers, processing, databases, and licenses. They also require setup, configuration, and development by experts in that particular tool, and those skills are not always transferable. Experts in Microsoft® SQL Server® Integration Services or IBM InfoSphere® DataStage® might not know how to use Informatica or Pentaho. To avoid the learning curve involved in adopting new tools, consider using the tools within the Hadoop ecosystem, instead. Apache Hive and Apache Pig — included in the Hadoop ecosystem — are the frontrunners for extracting, loading, and transforming various forms of data. Unlike many traditional ETL tools, which are good at structured data, Hive and Pig are created to load and transform unstructured, structured, or semi-structured data into the Hadoop Distributed File System (HDFS).

Hive is built on traditional database and data warehouse philosophies. It treats the data as if it has an SQL- or schema-based structure. In Hive, you can load the data into HDFS or directly into a Hive table. Pig, however, is more similar to a standard ETL scripting language. In Pig, you might have a schema in mind, but you are more concerned with how to transform and integrate the data into HDFS using more complex functions than simply putting it into a particular table or database. Because both Pig and Hive make use of MapReduce functions, they might not be as fast at doing non-batch-oriented processing. Some open source tools attempt to improve this limitation, but the problem still exists.

Benefits and limitations of traditional ETL and ELT philosophies

For years, ETL technology and tools have remained almost the same, especially in the data warehouse context. The tools have improved, but the methodologies have remained largely unchanged. You extract data from various sources, run a set of scripts or ETL workflows to transform that data, then load it into a star schema or semi-normalized data warehouse or master data management system.

Most data professionals are familiar with ETL. Issues such as change management, slowly changing dimensions, inserts, updates, and tombstones have been addressed or worked around for years. Because data warehouse data has not always been trusted, people have relied on Microsoft Excel® spreadsheets, instead. Philosophies and methodologies have been developed and strategies have been implemented, but there are widely varying approaches.

The key limitation of the ETL philosophy is that early in the process, someone must decide what data is important, what data needs to be updated, what data gets cast aside, and who gets permission to that data. The data warehouse or the master data management systems then become repositories of only the data that someone has deemed important. The original raw data is not stored and cannot be retrieved. The data marts and transformed data become the only data available, even though it's a subset of data, which was architected and designed by someone who might not even be with the company anymore and who might not share the same philosophy on what data is important.

Given these limitations, people began to find workarounds, such as storing data in a local database. Departments built their own silos and data marts, and suddenly, master data was an interesting concept — but not a reality. Data was not integrated data. The sales, marketing, and finance teams all had different data. Numbers and dashboards were unreliable and not trustworthy. Obviously, ETL could not accommodate big data.

Hive as an alternative to traditional ELT tools

The Apache Hive data warehouse software facilitates querying and managing large datasets residing in distributed storage. Hive is a powerful tool for ETL, data warehousing for Hadoop, and a database for Hadoop. It is, however, relatively slow compared with traditional databases. It doesn't offer all the same SQL features or even the same database features as traditional databases. But it does support SQL, it does function as a database, and it gives access to Hadoop technology to more people (even those who are not programmers). It offers a way to transform unstructured and semi-structured data into usable schema-based data. Want to build a master data management system? You can do that with Hive. Want to build a data warehouse? You can also do that with Hive, but you will need to learn the tricks to make Hive a powerful ETL tool.

As opposed to Apache Pig and MapReduce, Hive makes it easier for traditional RDBMS database developers or others who know SQL to access and transform data in Hadoop. Pig, however, is not as easy to understand and has a steep learning curve for those who do not come from a software development background. MapReduce is a technology that Java™, C++, and Python programmers can pick up relatively quickly. But without a foundation in a technology such as Java, MapReduce is nearly impossible to learn. Therefore, if you know SQL, Hive can be relatively easy to learn and use.

Example: How to use Hive for ELT

I extracted comma-separated value (CSV) files from the World Bank website. The site has a great deal of sample and real data around economics, finance, poverty, and more. For this example, I downloaded World-Finance, Inequality, and Poverty 1958-1998. This data is for research and education purposes, but it's an aggregate sample of 52 developing and developed countries. This data offers possible insight by including indicators such as private credit, inflation, gross domestic product (GDP), GDP growth rate, and income share.

In this example, I extract the data from the website, load it, and transform it with Hive. The goal of this use case is to de-normalize the various sources (four CSV files, in this case), then do some simple aggregates on a column. After you read this article and step through this example, you will know how to do ELT functions with Hive. To learn how to do traditional ETL functions, simply reverse the processes and use Hive first to transform and aggregate the data and load it.

To follow along, you need access to IBM InfoSphere® BigInsights™ Quick Start Edition. (You need to have an IBM universal ID or register for one before you can download InfoSphere BigInsights Quick Start Edition.)

Two versions are available for download. The first is the Quick Start Native Software Edition; use this to run the software on your own machine. The other version is the Quick Start Edition VMware Image (fusion for Mac users); you need VMware Player for this virtualized image to work. I am using the VMware Player edition on a CentOS 6.4 Linux® desktop.

Import the data

Begin by downloading the .zip files that contain four CSV files from the World Bank website. See Download for the files.

Next, start InfoSphere BigInsights. (In the VMware Player version, simply click the icon to start InfoSphere BigInsights, and Hadoop will be up and running in a flash.) Click the InfoSphere BigInsights shell folder, click the terminal, and you're ready to start building your directories for this use case. Listing 1 shows the command line.

If done correctly, you should have four CSV files. Now, load the data into Hadoop. You can do so in a couple of ways with InfoSphere BigInsights. The Distributed File Copy application makes this easier, but for this example, use the following Hadoop shell commands:

$ hadoop fs ‑ls
$ hadoop fs ‑mkdir WorldBank

Now that you have created the directory World Bank in the HDFS, you can add the CSV files to Hadoop, using the following code:

$ hadoop fs ‑copyFromLocal 
/home/biadmin/WorldBank/data/WLD_1998_FIP‑v01_M/*.csv /user/biadmin/WorldBank
$ hadoop fs ‑ls /user/biadmin/WorldBank

The four CSV files are now in the HDFS. The next step is to transform those files with Hive.

Design the Hive schema and database

First, you need to understand how the data is laid out in these CSV files. Issue the following command in the Linux terminal window:

$ head ‑2 Finance_inequality_and_the_poor_data_6005.csv

The head -2 command enables you to see the first two lines (header plus the first line) of your CSV file. You see 17 columns, most of which are numeric or in decimal format, which makes it easier. The only exception is the Countrycode, which is a string-like character. Next, look in the other files. In Linux, you can use different ways to compare files (diff, etc.), but for now, simply look at the column names and data using the following command:

$ head ‑2 Finance_inequality_and_the_poor_data_8005.csv

The second file has a few more columns. In addition to countrycode are columns for Country and year. Only 13 columns are visible. It seems that a few other columns are missing, when you compare this file with the *.6005.csv file. As you continue this process, you see that the four CSV files have differences, which makes de-normalizing and aggregating data a bit more challenging.

Determine how to model your database. You can see that there will be four core tables (6005, 8005, data_panel, and data_poverty). After you create and load these tables, you create a master de-normalized table. For this example, call it Finance_Inequality. This table integrates all the data from the four tables. (Obviously, some columns will be null.)

After the master table (Finance_Inequality) has been created and loaded, the final step is to aggregate a column and build a Finance_Inequality_Transformed table. This is a simple use case, but as you will see, it is not difficult to imagine production-level systems in which Hive is used to build complex aggregated and transformed tables.

Look at the 6005 columns you must create for your table:

• countrycode
• year
• loginitialgini
• growtheingini
• span
• loginitialgdppercapita
• growthgdppercapital
• privcreavg
• logprivatecredit
• inflation
• logtrade
• gr_ltrade
• gr_school
• logschooling
• logcommercialcentralbank
• loginitiallowestincomshare
• growthinlowestincomeshare

This list gives you a good idea of what you need to do and create. The 6005 table will have 17 numeric or decimal columns, in addition to the countrycode. Next, you start building the database.

Click the InfoSphere BigInsights shell, then click Hive Shell.

I typically create text files for each table, write the SQL and Data Manipulation Language (DML) code there, save the tables, then paste the code into the Hive shell. This method is far easier than typing SQL and DML code line by line in a command shell.

Listing 2 provides the SQL code to create the database and the first 6005 table.

Note: In the version of InfoSphere BigInsights I used, DECIMAL did not work, so I had to use Double.

Now, create tables tbl_8005, tbl_data_panel, and tbl_data_poverty in a similar fashion.

After the four tables have been created, they are ready to be populated using the following command:

LOAD DATA INPATH '/user/biadmin/WorldBank/Finance_inequality_and_the_poor_data_6005.csv' 
OVERWRITE INTO TABLE worldbank.finance_inequality.tbl_6005;
select  from worldbank.finance_inequality.tbl_6005 limit 10;
select count() from worldbank.finance_inequality.tbl_6005;

After populating the tables and running some simple select statements, the data should be correct. You can delete the header files before loading them into the HDFS or Hive table.

Note: Remember to include row format-delimited fields terminated by single quotation marks (') when you create the tables. If you leave out that key part, your data will load incorrectly.

Build the master Finance_Inequality table to integrate the data

After building the four core tables, the next step is to build the master table that integrates the data from all four tables. This task can be complicated, depending on your sources. For this use case, left and right outer joins are sufficient to create the master table. Listing 3 shows the code.

After you run this code, you have 315 rows or 314 rows, depending on whether you discarded the header row. With this master table, you can combine, aggregate, delete, and do whatever you want with that data. Obviously, with more thorough research, you might find data quality issues or duplicates, but for this use case, you have used Hive for ELT and built a master database from your transformation efforts.

Aggregate tables

To aggregate or perform analysis of the data in the de-normalized master table, you have many options. You can combine a few columns; disregard columns; or add, subtract, multiply, or divide a couple of the columns and come up with your own derived column in a new table. You need a strategy. Perhaps you want to find a correlation between population growth and the poverty gap. You can run complicated Hive SQL queries to obtain these results, but if you want to make things easier for yourself or your users, consider building an aggregate table.

You create an aggregate table similar to how you created the master table. In Hive, you can use options such as if Case A then this,__if Case B then this, etc. Hive has limitations compared with your traditional RDBMS, but with the right philosophy and preparation, you can create aggregate queries or more complicated and complex derived tables.

Run a query

Use the code in Listing 4 to run a query.

    
use WorldBank_Finance_Inequality;
select countrycode, country, 
CASE WHEN YEAR > 1990 then YEAR ELSE 0 END AS theYEAR, 
populationgrowth, growthinpovertygap
FROM master_tbl_finance_inequality;

Conclusion

Obviously, choosing between adopting an ELT or ETL philosophy requires thought. This decision can account for more than 70 percent of the planning time required for many data warehouse, master data management, and other database projects. Effective data analysis requires the right data. Without the appropriate data, you cannot rely on the accuracy of the analysis.

In this article, the use case example shows you that it is relatively easy to dump data into the HDFS and worry about the schema later. This article starts building a Hive database after the data is already in the HDFS, and the sample data comes from the web as CSV files. But the data can come from any source and can be in any format. Near the end of this example, we made decisions about the layout of those files, the delimiter, the variables, and other factors before building the databases and running queries on that data.

Hive certainly has limitations, but if you're in the Hadoop ecosystem and already know SQL, it is a wonderful tool to start building your databases, table flows, transformations, and data integration. Although this is a relatively simple use case, far more complicated processes are possible within Hive and Hadoop.