DWgeek.com

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

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

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

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

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

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

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

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

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

January 2, 2018

Redshift

Redshift Column Compression Types – Compression Encoding

Amazon Redshift, is a fast, fully managed, petabyte-scale data warehousing service. Redshift supports various columnar compression encoding techniques. In this article we will check Redshift column compression types - Compression Encoding techniques for Redshift performance optimization. Amazon Redshift supports data compression for reducing the amount of disk storage used by a table. Amazon Redshift compression encoding can significantly reduce the storage required for a table and improve query performance by reducing I/O operations. The column compression in Redshift also helps storage optimization. Redshift Column Compression Types – Compression Encoding Columnar…

Comments Off

January 1, 2018