BigData

Apache Hive Extract Function Alternative and Examples

In general, extract function extracts the sub field represented by units from the date/time value, interval, or duration specified for column. Apache Hive does not support extract function, you can use other built in functions to extract required units from date value. In this article, we will check Hive extract function alternative and some examples. Hive Extract Function Alternative There is no extract function in Hive to extract sub part of date values. You can use Hive built in date function date_format() to extract required values from date fields. Below…

Comments Off on Apache Hive Extract Function Alternative and Examples
January 10, 2018
BigData

Impala Create External Table, Syntax and Examples

A Impala external table allows you to access external HDFS file as a regular managed table. This operation saves resources and expense of importing data file into Impala database. You can perform join using these external tables same as managed tables. You can write complex queries using these external tables. In this article, we will check on Impala create external table with some examples. Syntax for creating impala external table is same as creating managed tables. There is one exception to this, LOCATION option is mandatory for creating external tables. LOCATION…

Comments Off on Impala Create External Table, Syntax and Examples
January 9, 2018
BigData

Difference Between Hive CLI and Beeline Client – Hive vs Beeline

Beeline is developed to interact with new server. Hive CLI is an Apache Thrift based client, whereas Beeline is JDBC client, based on SQLLine CLI. In this article, we will check difference between Hive CLI and Beeline client – Hive vs Beeline. Difference Between Hive CLI and Beeline Client – Hive vs Beeline Below are the some of the difference between Hive CLI and Beeline client. These some of differences will help you in case if you are migrating from old Hive CLI to new Beeline client. Server Connection Hive…

Comments Off on Difference Between Hive CLI and Beeline Client – Hive vs Beeline
January 8, 2018
BigData

Beeline Hive Command Options and Examples

You can run hive specific commands like Apache Hive Command options in Beeline shell. Just like in Hive command options, you can terminate Hive command by using “;” (semi colon). In this article, we will check Beeline Hive Command Options with some examples. Read: Execute Hive Beeline JDBC String Command from Python Beeline Hive Command Options Below are the Beeline supported Hive command options: Command Description set <key>=<value> Sets the value of a configuration variable (key). set -v This command prints all Hadoop and Hive configuration variables that are used. set This…

Comments Off on Beeline Hive Command Options and Examples
January 8, 2018
BigData

Export Hive Table into CSV Format using Beeline Client – Example

CSV or comma separated flat files are most common file system used to transfer data using electronic media. You may get requirement to export data for ad-hoc query or just unload data for subset of columns available in table, in this case export Hive table into CSV format using Beeline client comes into handy. Beeline Separated Value Output Format The values of a row are separated by different delimiters. Starting with Hive 0.14 there are improved output formats available, dsv (custom delimiter), csv2 (comma separated value) and tsv2 (tab separated…

Comments Off on Export Hive Table into CSV Format using Beeline Client – Example
January 7, 2018
BigData

Apache Hive Temporary Tables and Examples

A temporary table is a convenient way for an application to automatically manage intermediate data generated during a large or complex query execution. Hive 0.14 onward supports temporary tables. You can use them as a normal table within a user session. In this article, we will check Apache Hive Temporary tables, examples on how to create and usage restrictions. Apache Hive Temporary Tables Hive temporary tables are local to the user session. You can use temporary table repeatedly within a user session for multiple times. Hive automatically deletes all temporary…

Comments Off on Apache Hive Temporary Tables and Examples
January 4, 2018
BigData

Run HiveQL Script File Passing Parameter using Beeline CLI and Examples

Hive is used for batch and interactive SQL queries. HiveServer2 supports a command shell Beeline that works with HiveServer2. It's a JDBC client that is based on the SQLLine CLI. You can run HiveQL script file passing parameter using Beeline CLI. Variable Substitution allows for tasks such as separating environment-specific configuration variables from code. You can substitute the values to variable that you have used in HiveQL query. Read: Hive Dynamic SQL Support and Alternative HiveServer2 Beeline Command Line Shell Options and Examples Run HiveQL Script File Passing Parameter using…

Comments Off on Run HiveQL Script File Passing Parameter using Beeline CLI and Examples
January 4, 2018
BigData

Apache Hive group_concat Alternative and Example

Group concat is a single string representing the argument value concatenated together for each row of the result set. The resulting string is a comma separated values. Many relational databases supports group-concat functionality as a standard built in function. Unfortunately, Hive does not have group_concat function. In this article, we will check Apache Hive group_concat alternative functions and working examples. Apache Hive group_concat alternative Functions As mentioned earlier, Apache Hive does not support group_concat function. You have to use other built in functions available in Hive to perform group_concat. Apache…

Comments Off on Apache Hive group_concat Alternative and Example
January 4, 2018
BigData

Steps to Connect to Hive Using Beeline CLI

Beeline is a JDBC client that is based on the SQLLine CLI. HiveServer2 supports a command shell Beeline that works with HiveServer2. In this article, we will check how to connect to Hive using Beeline CLI and see some examples to execute HiveQL scripts. Connect to Hive Using Beeline CLI Beeline works on both standalone mode (embedded mode) as well as remote mode. Standalone more or embedded mode, it executes embedded Hive like Hive CLI, and you can use remote mode to connect separate hiveserver2 over thrift. Read: HiveServer2 Beeline…

Comments Off on Steps to Connect to Hive Using Beeline CLI
January 4, 2018
Redshift

Redshift User Defined Functions Examples

Create a user defined function is one of the important features of Amazon Redshift cloud data warehouse. Amazon Redshift supports creating user defined functions. You can create custom user-defined functions (UDF) using either SQL SELECT statements or Python program. In this article, we will check Redshift user defined functions examples and how to create them. Redshift User Defined Functions User defined function (UDF) in Redshift provide a powerful way to perform complex data processing tasks within the Redshift database. They can provide more control and flexibility over data processing workflows, and…

Comments Off on Redshift User Defined Functions Examples
January 2, 2018