Specifically, distributed computing engines will be firstly described because it is the foundation for large scale ETL design. Next, Hadoop file formats will be presented and compared between each other which serves as the basis of data persistence in the following paper.

Distributed Computing Engines

Hive serves both as a distributed computing engine and as a data warehouse based on MapReduce [9] which facilitate the process of implementing business requirement on massive data stored in HDFS via SQL-like query. The underlying architecture of Hive is essentially mapping data in HDFS to table in Hive, where a service called Hive Metastore comes into play to help manage the metadata and partition information of all Hive tables. Users or other tools like Spark could access Hive Metastore via HCatalog or HWebcat tools as API portals.

Moreover, Hive table could be built with the support of partition feature which will separate underlying data by specific context like day, month or year. In this way, not all data needs to be scanned for queries with date range constraint, which will remarkably boost the speed of computation.

From the above point of view, Hive definitely provides an easier and more convenient way to manipulate on big data, yet it still suffers from performance penalty because it will cost disk I/O shuffling immensely when there’s multiple phases due to the underlying architecture of MapReduce [12]. Consequently, if ETL is constructed upon Hive with massive data transformed every day, it’s going to cost much more resources and time to finish the job.

Spark, as a new computing engine after Hive framework, it’s biggest characteristics is the ability of in-memory computation, which is faster than Hive especially when there’s multiple MapReduce phases on a complicated query. Further, UDF implementation in SparkSQL is much easier and more flexible when comparing to that in Hive , which needs to be packaged, deployed and referenced from Hive. This is essential since ETL

requires UDFs frequently and it saves human resources significantly in terms of implementing, deploying and management. And lastly, Spark and SparkSQL could take use of Hive table via Hive Metastore connection, therefore all ETL jobs that are implemented in Hive are essentially able to be migrated to SparkSQL, which will yield great performance gain [15]. Consequently, Spark and SparkSQL will be the major choice for implementing ETL pipelines.

Hadoop File Formats

There’re multiple storage format in Hadoop file system to store underlying data as. For instance, TextFile, SequenceFile, Avro, and Parquet.

TextFile and SequenceFile are both file-based formats, the former will store data based on row in plain text, whereas the latter will serialize data in key- value structure for each line of record

Avro, on the other hand, is one of the serialization formats, whose files are compressed and splittable. The ability to split is essentially important among all compressed Hadoop file formats since it will help take full advantages of distributed computation engines like MapReduce, Hive or Spark to split compressed files to different executors

Parquet is one of the columnar-based storage formats which are optimized for column-oriented queries, that will select only limited columns out of wide tables with numerous columns [26]. The reason for that is because data is stored per-column rather than per-row, so only a limited number of data will be loaded when querying on subset of all columns 

Also, columnar-based file format will be more suitable for parallelism. Multiple executors could load each column simultaneously when it intends to load one line of record [28]. Moreover, since data in the same column will be more similar than that in each record, it will gain better compression rate 

Comparison

there’re various types of file format that could be applied. In this section, the advantages and disadvantages will be analyzed in detail as well as the difference among major Hadoop file formats.

To begin with, TextFile and Parquet file format are firstly compared. According to experiment, Parquet table consumes much less disk space than TextFile table with the same underlying data, which is expected empirically. In the experiment itself, log data is compressed from 7.2TB plain text to 2.9TB in Parquet format, which reduces virtually 60% of disk spaces. Moreover, queries on Parquet table gain much better performance in comparison with that on TextFile table when only querying on a subset of all columns in the table. This is because Parquet table is columnar-based so that not all data in every column needs to load into memory and shuffle around, which will decrease the query time remarkably. On the contrary, if it is frequent to select all columns out of the table, then the result shows that query on Parquet table will be relatively much slower than that on TextFile table due to the record assembling procedure for columnar-based Parquet files.

Further, the vital advantages of either Avro or Parquet file type over SequenceFile is the characteristics of schema evolution. Schema evolution supports that the schema to load files is not necessarily the same as the schema for writing the file. This enables user to add new fields to existing files without affecting the historical data when facing changes in the future.

Lastly, when it comes to comparing Avro and Parquet file formats, Avro is merely better than Parquet in terms of compression rate, which will save more disk space. Regarding querying, Avro table takes more time for the same SQL query than that on Parquet table or even TextFile table [26]. Moreover, according to experiment all types of queries, scan queries and aggregation queries, yield better performance over table in TextFile or Avro format.

File Format Selection

In our scenario, there will be two categories, namely, data lake and data mart 

For App events data in data lake, since it is coming from API Gateway and persist in Amazon S3 in plain text, so this table will be built on TextFile for the consideration of ease of implementation (no need to transfer from TextFile to Parquet anymore). Moreover, Hive partition and denormalized tables will be created or calculated based on merely every day’s new incoming data in app_events table, whose procedure is incremental and will suffer less from performance penalty when comparing to query on Hive table in Parquet format.

Further, as for denormalized tables, since they are calculated based on tables in data lake and will result in wide tables with much more columns that will provide insights for business logic, so it would be better to persist in Parquet format which will be more efficient in later query.