Netezza

Load HDFS file into Netezza Table Using nzload and External Tables

nzload command is bulk copy command available in Netezza data warehouse appliance. This Netezza native command provides an easy method for using external tables and getting data into the Netezza appliance. There is no straight forward option to load hdfs file into Netezza tables using nzload command. You must use some work around to get hdfs file into Netezza tables. In this article, we will check out methods to load HDFS file into Netezza Table Using nzload and external tables with some examples. Install Netezza Drivers Before attempting to load…

Continue ReadingLoad HDFS file into Netezza Table Using nzload and External Tables
Comments Off on Load HDFS file into Netezza Table Using nzload and External Tables
January 16, 2018
BigData

Apache Hive LEFT-RIGHT Functions Alternative and Examples

If you have been working on other RDBMS like Oracle, Redshift etc then you will be surprised to know Hive does not support LEFT-RIGHT functions. You will either should write your own UDF’s using Java or find out any other alternatives. In this article, we will check Apache Hive LEFT-RIGHT functions alternative with some examples. Hive LEFT-RIGHT Functions Alternatives Since Hive does not support LEFT-RIGHT function, you could use Hive SUBSTR string function or regexp_extract regular expression function to select leftmost or rightmost characters from the string values. Other possible…

Continue ReadingApache Hive LEFT-RIGHT Functions Alternative and Examples
Comments Off on Apache Hive LEFT-RIGHT Functions Alternative and Examples
January 12, 2018
BigData

Cloudera Impala Extract Function and Examples

Cloudera extract function returns one of the numeric date or time fields from a TIMESTAMP value. Cloudera Impala extract function extracts the sub field represented by units from the date/time value, interval, or duration specified for column. This function is equivalent to Impala date_part() function but parameters reversed. In this article, we will discuss on Impala extract function and its usage with some examples. Cloudera Impala Extract Function Syntax The extract function complies with SQL-99 standard function. The syntax for extract function is same as other RDBMS extract functions. Below…

Continue ReadingCloudera Impala Extract Function and Examples
Comments Off on Cloudera Impala Extract Function and Examples
January 10, 2018
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…

Continue ReadingApache Hive Extract Function Alternative and Examples
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…

Continue ReadingImpala Create External Table, Syntax and Examples
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…

Continue ReadingDifference Between Hive CLI and Beeline Client – Hive vs Beeline
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…

Continue ReadingBeeline Hive Command Options and Examples
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…

Continue ReadingExport Hive Table into CSV Format using Beeline Client – Example
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…

Continue ReadingApache Hive Temporary Tables and Examples
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…

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