#sql in hadoop

[hadoop-analytics-247-blog]

Different types of SQL on Hadoop

Search Query Relations (SQL) on Hadoop is the category of tools and applications which together form SQL querying while integrating Hadoop data networks into its processes. It is an important part of businesses nowadays because it gives many programmers and business managers opportunity to work together and oversee work on computing clusters related to business. Though it initially started off based on relational databases, it proved so successful that it was developed further and now utilizes the Hadoop Distributed File System combined with Map-Reduce. It is necessary to have HDFS and Map-Reduce in place for this SQL on Hadoop to work effectively.

SQL on Hadoop is extremely effective to retrieve data from big data storage in Hadoop because they share sql language they run on. It is very user friendly as it is adaptive in checking and examining data however it is presented. SQL on Hadoop has been a blessing for many organizations because big data analytics at first was limited to just a few, now many businesses can reap its advantages. Advanced knowledge regarding interfaces or vast amounts of resources were required to access such valuable data, but SQL on Hadoop has made it possible.

There are different kinds of SQL on Hadoop, some of which are given below:

1.      Batch SQL

Different applications are created for batch query processing for SQL on Hadoop. Tech for example Hive make use of HiveQL abstraction layers that utilize Map-Reduce as background processes to execute tasks. Hive is designed especially to work for processing large data clusters and these tasks require from a few minutes to a few hours, which is entirely dependent on the nature of job.

2.      Interactive SQL

Interactive SQL in Hadoop uses technologies like Apache Drill and Impala to give shared query abilities which allows both orthodox business strategies ad SQL on Hadoop analytics to work together and share their resources and power. Tasks given to execute take any time between milliseconds to minutes. Interactive SQL on Hadoop nowadays is advanced and integrates Map-Reduce and Apache Drill to be reactive and adaptable.

3.      In-Memory SQL and Streaming

In-memory SQL on Hadoop has revolutionized ecosystem schemes which make use of live data management. They utilize Apache Spark and Apache Storm to streamline efficiency and improve response time of steams and processing power.

4.      Operational SQL

Operation SQL on Hadoop is relatively different from interactive and batch queries. This kind of SQL on Hadoop is used especially to finalize a strategy and usually is designed as read only task. It is set to oversee smaller networks and control their certain functions such as insertion, updates and removal of data. These applications work extremely fast and their response time is measures in milliseconds.

[hadoop-analytics-247-blog]

Revolutionary SQL in Hadoop

Over the past couple of years, many big name vendors have tried to bring SQL capabilities to Hadoop with many open-source solutions. While these SQL in Hadoop solutions have been trying to bridge the gap, almost all of them have missed the basic essence of Hadoop and in doing so they have a trichotomy of approaches, looking to address the needs of data analysts. The recently released Apache Hive and Apache Drill are so unique that you will have to forget all you know about the solutions bringing SQL to Hadoop.

Apache Drill

Unlike the other solutions which force a schema on top of Hadoop, Apache Drill doesn’t need any kind of schema based tables formatted by Hive. The new release by Apache features an engine capable to handle plugins, which can query data from schema less files as well as from Hive, HBase, MongoDB and even JSON. That said, it can easily reach data stored locally in the Hadoop File System (HDFS) and from cloud storage systems by Google, Microsoft, as well as Amazon. Essentially, Drill is a true SQL in Hadoop solution bringing about a great ease of use and better functionality.

Rather than enforcing anything, Drill uses an SQL interface to the data whether its structured or unstructured, enabling plug and play like behavior over a huge collection of data without any preliminary requisites or preparation. Although Drill provides with SQL in Hadoop, calling it so kind of misses the essence of Drill. Calling it SQL on Everything would be much suited.

Re-imagined Hive

Along with Drill, Apache has been very busy with the Hive. The dedicated team at Hortonworks led an initiative, they named “Stinger” to modernize the Hive and transform it into such an engine which is capable of interactive querying rather than just a MapR based batch processing engine. Working with other vendors, Hive has been imbued with a great deal of enterprise grade RDE (relational database engine) features.

Here it is worth mentioning that Hive is very common amongst the Hadoop community. Although Hortonworks have realized that improving upon the Hive is a very good strategy, they own exclusive rights to the project.

So which is the best SQL in Hadoop engine?

Honestly it is a very hard decision to make. Hortonworks has pointed out that Spark SQL is much faster than the Hive on Spark. But if the customers are torn between an engine which exclusively runs over Spark and one which can run Spark SQL as well as Tez and MapR, Spark SQL might need some explaining to do.

Furthermore, there are a lot of goodies from Stinger in the Hive, including LLAP layer (it stands for Live Long and Process, Seriously) which provides with services related to query caching, the VPL (vector processing logic) which is fed multiple rows of data altogether rather than one by one and the query optimizer based on cost.

So while SQL in Hadoop solutions used to be divided into three basic categories, it is now about much more than all of that.

Summary: Over the past couple of years, SQL in Hadoop has seen a lot of faces but with the release of Apache Drill and Hive the frontier is changing greatly, for the good.

[coolmichel101-blog-blog]

State of the Art – SQL in Hadoop

Since the Big Data adoption by mainstream organizations, many enterprise grade open source tools have emerged, with an aim to solve the Hadoop querying issue. Although, Hadoop started off as a simple yet extremely powerful platform consisting of a storage layer (HDFS) along with a processing layers (MapR). Only after the starting couple of years of HBase’s release, operating on data in HDFS became more actionable. After that several SQL in Hadoop solutions surfaced, however, the problem of querying data from HDFS remains unsolved in basically two aspects:

Integration

Making data query-able making use of query engine was a challenge itself. For instance how does the database integrate with the HDFS? How mature is it? Can I update all the data in one swift big transaction?

Flexibility

The engines outside of Hadoop has had very distinct features and different query languages. Many were limited in their nature to serve key and values. Choosing such an engine always involved a tradeoff and choosing one over the other kept making things harder.

After the NoSQL hype dialed down a bit, SQL seemed as a viable option to resolve the Hadoop querying flexibility. Although it’s true that Sqoop has already made it possible to connect Hadoop with conventional relational database management systems, however, it didn’t effectively solve the issue. Also, there was Batch SQL, which, unfortunately, had very high latency although making the MapR avoidable.

Even though SQL is a decades-old language, it has some element which makes it very convenient as a querying language for Hadoop.

SQL in Hadoop solutions provide with the necessary flexibility for calculating aggregates without pre-calculating them before it all.

Many developers and database administrators know SQL, which makes it a very good alternate to MapR.

Supporting joins, it allows the data model to remain de-normalized.

SQL on Hadoop allows to integrate Hadoop with the existing BI and Data Visualization tool through ODBC and/or JDBC.

The rise of SQL in Hadoop solutions in the last year can easily explain the maturity of the Big Data Platform, Hadoop. Since the number of organizations adopting Hadoop is increasing, the requirements and problems have pushed the SQL on top of it. For instance, we are beginning to see an increased amount of hybrid architectures.

This post is aimed at enlightening organizations about the capabilities of these SQL engine. They provide with the capabilities of Batch SQL (long processes which can operate easily on big datasets), Interactive querying (robust response time for ad-hoc querying), point querying (sub-second response time for querying mobile and web applications) and basic operational SQL (ACID transactions, reads & writes). While sometimes an engine will overlap these capabilities (which can be expected). With a plethora of big-name vendors putting their solutions on the market, you can easily have a hard time choosing the right solution for your organization. Conduct a thorough research, considering all the big names such as Impala, Presto, and many mores in order to siphon off actionable insights for your organization to succeed.

SQL in Hadoop is one of the most revolutionary techs the corporate world has seen in the past couple of years. Organizations should make use of these to leverage their datasets, to pull ahead of the competition.

[andyjay-world-blog-blog]

How SQL on Hadoop Solutions Amplify their Strengths

By introducing a great deal of agility into the world of Big Data, Hadoop has succeeded in making a name for themselves. While it provides with the required access to Big Data sets, the only thing it lacks in is the agility and robustness of SQL based data warehouses, which is why there are countless vendors out there who have made use of the available technology to develop SQL on Hadoop engines which bridge the agility gap and skillset. By employing these engines, organizations can move operate on their big data sets as they used to, using traditional SQL data warehouses.

Since the first release, the most debated complaint about Hadoop adoption is that not many people know MapReduce and the skills required to operate on big data sets using MapR. Similarly, the SQL domain has faced the issue of constructing and modifying the schema when operating with huge data sets which has a plethora of ambiguities. Similarly, the issue arises when the need to add a new field into the schema, it gets tougher as the complexity of the schema rises, which usually involves more than one people and most of the times it ends up messing up the situation even more.

With the help of a multitude of SQL on Hadoop engines, companies which used to stick to either of these domain exclusively, are getting rid of such issues transforming the conventional warehouses into more flexible and robust ones.

This transformation is being performed by modelling the conventional data warehouse into an abstract form which is then fed into a model which generates all of the detailed configuration, which is usually handled by an administrator. Doing so, Database Administrators can now do in minutes what they used to in hours or maybe even longer. Companies like BIReady and Wherescape are making it way easier to set up new schema, make companywide changes in the schema or map the proceedings from one warehouse to another. The API-driven ecosystem of AWS (Amazon Web Services) is built on top of the warehouses of Oracle, Microsoft along with IBM and others.

Such SQL on Hadoop engines reduce the risk of error greatly. For instance, when you present the administrator with a lot of options and choices, they get worried about making mistakes but then when you simplify things with models, implementing changes becomes much less risky. With these type of SQL in Hadoop tools in the play, you don’t have to worry about turning into your older uncle who yells at everyone to stop changing things around him. Also the Administrators and the data analysts are able to make changes as abruptly the need arises while resting assured that their actions won’t have extremely dire results.

In this way, the SQL engines for Hadoop amplify the skill sets of Hadoop and SQL so much that the adoption rate for such solutions is increasing by a lot. Many corporations are adopting these engines to achieve their desired results from their Big Data operations.

Hadoop and SQL stand for two very different things i.e. unstructured and structured data. Both platforms have their strengths and with SQL on Hadoop engines strengths of both the platform are being amplified as much as needed.

[acealden-blog]

Benefits of adding SQL on Hadoop

As the world shifts its trends towards the digital age, increased percentage of data is translated into digitalized form and used in businesses. This form is data is critical to decide how the business should be run and future predictions. Same is the importance for the softwares that function to process this data. Information from social media websites, online forms, streaming websites and statistical and logistical websites is utilized to enhance customer experience and produce better offers for them. In this case, Hadoop presents itself to be a capable platform as a programming engine to process this large data and make sense of it. Thus SQL on Hadoop is implemented to increase its efficiency. There are certain reasons for this.

Hadoop is somewhat limited to perform alone. When processing vast amounts of graphs, Hadoop fails to deliver efficiently. When analyzing vast amounts of structured data, Hadoop periodically gives uncertain results. Same is the case with interactive queries with a response time ranging from couple of milliseconds to seconds. This is why SQL on Hadoop is implemented. Bulk Synchronous Parallel (BSP) programming models have shown promise in erasing these shortcomings but SQL on Hadoop proves to be far better in working alongside Hadoop to tackle this issue.

By using Hadoop alone, programmers are tested to the brink of their knowledge of Hadoop as it does not give any helping softwares that automatically set background tasks. It is imperative that the programmer be fully versed in Hadoop. This is another reason why SQL on Hadoop is implemented as many programmers have strong grip over SQL and combining it with Hadoop provides them with best of both softwares. The High Level Language declarative language takes care of background tasks by itself as well. SQL on Hadoop proves to be an effective way to make processing of large amounts of structured data efficient and accurate.

SQL on Hadoop works in the following manner. There exists a data storage in which the structured data is saved. From here, SQL is given access to the information. By pairing SQL on Hadoop, the SQL uses Hadoop to make smaller queries of complex data types for Hadoop to execute. The engine then hands out these smaller tasks for Hadoop to perform. In case of interactive queries, the SQL functions by utilizing a predefined query optimizer which translates the input query into local jobs with the use of dynamic scheduling mechanisms which in turn reduces overall latency and makes the whole SQL in Hadoop system very efficient, effective and precise.

With the implementation of SQL on Hadoop, the complex and otherwise redundant Hadoop platform can be upgraded to deal with both massive quantities of structured data and interactive queries. This also gives programmers versatility to focus on the main task and leaves processing and handling of background tasks to the software.

[acealden-blog]

Why SQL on Hadoop works so well

Search Query Language (SQL) has been in use for quite some time now and still delivers promising results due to its effectiveness and reliability. It is a strong base to use as a platform for making programs. Even though Hadoop is newer and implements the latest of advances in programming languages in it which makes it very reliable for use for both structures and unstructured data, it has its limitations. When SQL on Hadoop is integrated, there is the necessity for a schema to be in place so that the whole program is able to execute its function. This in the long run is actually better for the platform.

SQL and Hadoop have their own strengths and shortcomings but when integrated together on SQL on Hadoop, they compliment each other and become very effective together. With the leaps in modern era, all forms of data are digitalized and this means that such platforms have enormous load on them to function on these vast quantities of data. It is easier when both the softwares are functioning on a single platform. This is necessary because the execution of more recent projects have shown that the SQL Queries have to be taken under Hadoop platform. SQL on Hadoop is important when databases need to exchange their contents with one another.

With the advances in technology, improved hardware has prompted use of stronger software and same is the case for implementing SQL on Hadoop. Data has evolved and its quantity is never ending. It keeps flowing from one source to another such as social media websites, online blogs, forums and websites. These advances mean that there is an ever increasing need for an open source software to be present which is capable of processing large quantities of data and SQL on Hadoop is just the thing to handle this function. Hadoop lends its ability to be adaptive and reconfigureable as it can accept enormous data clusters at one point and then scale itself and calculate how to run the application, depending on the size of data clusters it has accepted. This scaling according to task at hand allows the SQL on Hadoop platform to streamline its efficiency.

Although SQL on Hadoop has lots of potential and proven to be very useful, it still has the shortcoming of not being able to process large scale graphing properly. This disadvantage can be overcome by simply using a storm system. It gives the SQL in Hadoop platform a built in runtime environment which diagnoses the internal errors of the programs in real time, fixes them automatically, processes data in real time, returns the results quickly, is reliable and can work upon live data.

[sqlonhadoop-blog]

SQL-on-Hadoop: The dos and the don’ts

While considering using SQL to query data from Hadoop, the first question which comes to mind is  “Which is the tool to be used?” There are a lot of tools out there but they don’t truly provide with the promised effectiveness and efficiency. While some turn to MapReduce, they are unable to achieve the desired results. On the other hand, MR framework works well with data on a massive scale and caters to fault tolerance effectively. MapReduce was dedicatedly built for processing Big Data on large scale which we mostly refer to as batch processing.

As enterprises have started using Hadoop as a Big Data repository for the inflow of their data (originating from various sources varying from the operational systems, smart sensors, mobile devices such as smartphones and tablets, along with various applications over the Internet), SQL becomes an optimal choice. A fundamental reason being, most enterprise management and analytical tools rely heavily on SQL. Also that the talent pool for SQL is much bigger than that of data scientists and MR developers.

Hadoop is the best tool in the market for Big Data processing, there are many tools and frameworks available in the market which make use of SQL to provide the functionalities. That said, some of them even make SQL-on-Hadoop better than native SQL tools. As a means of query execution, SQL processing benefits from years of research, user experiences and optimizations.

MapReduce might be appropriate for ad-hoc analysis but that is as far as it can go with the current stage of technology. It serves the requirements well if they adhere to general aspects of the data, but to perform complex queries and analytics SQL-on-Hadoop is a necessity rather than an accessory. As discussed there are a lot of tools in the market which provide us with this capability, but to get the one that fits your needs perfectly, you need to perform a thorough research.

Some of the major tools which provide us with SQL via third party development on Hadoop are:

-          Apache Hive

-          Cloudera Impala

-          Presto by Facebook

-          Big SQL 3.0 by IBM

All of these can provide with the required functionality, but where one gains advantage over the other in terms of functionality, the others might take advantage in terms of pricing. Usually these tools are provided for free, with minimal usage fee but you might not be in a position to pay that fee either. So make sure that you perform the required research well before hand, and avoid dedicated hiring for Map Reduce. Hiring from the SQL resource pool will ensure that you get the work done effectively and efficiently. The SQL professionals have economically priced services and can query data in ways others simply cannot. It’s a win-win situation for all, you get SQL on Hadoop and they get to play around with Big Data.

[sqlinhadoop-blog]

The true essence of Hadoop

For numerous years, Hive and Hadapt were the solutions providing SQL capabilities on Hadoop. However, as people began to comprehend the problems with the database connector approach and its insurmountable approach, a great deal of SQL in Hadoop solutions were introduced in the market last year at Hadoop World. There are a lot of them out there, which can help you achieve you goal. All of these solutions providing with SQL capabilities are a great step towards the adoption of Hadoop. There is just one problem, all of these solutions are completely missing essence of Hadoop.

To defend it, we have to agree on what is the essence of Hadoop. Obviously there could be a lot of difference in opinions but there should be a point where all agree. Hadoop performs exceptionally well, when it comes to Big Data storage and analysis. It can scale up exceptionally and is tolerant to faults and is dynamic. However the traditional parallel database technologies can do all of these things but what do these SQL in Hadoop solutions provide with, that the others don’t? It’s the flexibility.

Hadoop can perform analysis and other processing tasks over unstructured and structured data alike. Furthermore the time required for the analysis is very low, that said there is no schema requirement or ETL processing before loading the data into Hadoop. It can simply be dumped into the Hadoop File System and accessed via Map Reduce. It’s the flexibility with regard to the schema and the data structure that has resulted in such a good momentum and popularity in the market. That said, majority of the solutions in the market fail at just that.

Including the Hive, Stinger, Hadapt and even Impala, such solutions restrict this flexibility. In all of these solutions, in order to be able to query data from Hadoop, you need to define a schema for your data. If you don’t understand the data well enough, nor the attributes or the types that you have, none of these SQL in Hadoop solutions might work for you. To use them, you have to define a schema, otherwise you won’t be able to query data. Although this restriction is not unreasonable, there could be a way to work around it. SQL itself stands for structured query language and queries written in SQL are done so against the defined schema. Writing SQL without a schema sounds a bit nonsensical. The queries written in SQL fundamentally need attributes to refer to or a table in the schema.

Thus the problem arises when Hadoop meets SQL. While Hadoop is famous for the flexibility and the capability to process large volumes of unstructured, dynamic data in an unknown schema, SQL requires a schema for query processing. Thus these solutions require a schema to be defined before any SQL query can be issued. Although they prove to be very helpful, but they are missing the true essence of Hadoop.