BigData

Apache Hive Grouping Function, Alternative and Examples

Most of the relational databases supports Grouping function to segregate super-aggregated rows.  Apache Hive Support for SQL grouping function was added in Hive 2.3.0. But who are using lower version of Hive will have difficult time in porting SQL queries that are written using grouping functions. In this article, we will check Apache Hive Grouping function alternative and examples. Grouping Function In general, the grouping function indicates whether an expression in a GROUP BY clause is aggregated or not for a given row. The value 0 represents a column that is part…

Continue ReadingApache Hive Grouping Function, Alternative and Examples
Comments Off on Apache Hive Grouping Function, Alternative and Examples
June 4, 2018
Netezza

Netezza Data Transformation Functions and Examples

You must secure the data in case if you are working on various sensitive and confidential data. Netezza does provides various transformation functions that you can use to change the data representation. In this article, we will check Netezza data transformation functions and some examples. Netezza Data Transformation Functions The data transformation functions in Netezza are used to change data into different representation for purpose of security (encryption and decryption), space saving (compression) and save time to transmit data across various medium. IBM used industry standard algorithms to build these…

Continue ReadingNetezza Data Transformation Functions and Examples
Comments Off on Netezza Data Transformation Functions and Examples
May 19, 2018
Netezza

Netezza Phonetic Matching Functions and Examples

Netezza nzsql language support phonetic matching functions, that is, you can encode names into phonetic representations. These functions are useful to match names that are based on their pronunciation and reduce misses that might result from spelling variations. In this article, we will check types of Netezza phonetic matching functions and usage with some examples. Netezza Phonetic Matching Functions There are two types of Phonetic matching functions supported in Netezza:  SoundEx NYSIIS algorithm  Double Metaphone (dbl_mp) algorithm There are three helper functions in Double Metaphone:  Primary Metaohine (pri_mp)  Secondary Metaphone…

Continue ReadingNetezza Phonetic Matching Functions and Examples
Comments Off on Netezza Phonetic Matching Functions and Examples
May 16, 2018
BigData

Step by Step Guide Connecting HiveServer2 using Python Pyhive

Data plays important role in every decision-making process. You may have to connect to various remote servers to get required data for your application. This article explains how to connect Hive running on remote host (HiveSever2) using commonly used Python package, Pyhive. In this article, we will check step by step guide Connecting HiveServer2 using Python Pyhive. There are lot of other Python packages available to connect to remote Hive, but Pyhive package is one of the easy and well-maintained and supported package. There is a option to connect to…

Continue ReadingStep by Step Guide Connecting HiveServer2 using Python Pyhive
2 Comments
May 4, 2018
Netezza

Netezza nz_migrate Script Usage and Examples

If you have upgraded you Netezza server to higher capacity box then want to migrate data from one netezza server to other, you can use the IBM provided Netezza nz_migrate utility. You can either mention the tables to be migrated or you can migrate all tables available in source database. Netezza nz_migrate script is one of the best Netezza utility that provides lot of option to use while migrating data from one server to other. nz_migrate optionally creates destination database if it is not already present.  All the tables mentioned in the…

Continue ReadingNetezza nz_migrate Script Usage and Examples
2 Comments
April 25, 2018
Netezza

Connecting Netezza using Python pyodbc – Working Example

In this modern era, data plays very important role in every decision making. Most of applications that is written in various programming languages require access to relational databases to get required data for its process. Before you can run SQL statements to create, update, delete, or retrieve data, you must connect to a database. In this article, we will check process and working example on connecting Netezza using Python pyodbc driver. Netezza also supports JDBC driver: How to Connect Netezza using JDBC Driver and working Examples Netezza ODBC drivers Before attempting…

Continue ReadingConnecting Netezza using Python pyodbc – Working Example
Comments Off on Connecting Netezza using Python pyodbc – Working Example
March 28, 2018
BigData

Apache Hive Table Update using ACID Transactions and Examples

Apache Hive and Cloudera Impala supports SQL on Hadoop and provides better way to manage data on Hadoop ecosystem. There are many frameworks to support SQL on Hadoop are available, but Hive and Cloudera are widely used and popular frameworks. Until recently, Apache Hive did not support Update tables. Version 0.14 onwards, Hive supports ACID transactions. You must define the table as transaction to use ACID transactions such as UPDATE and DELETE. In this article, we will check Apache Hive table update using ACID Transactions and Examples. Apache Hive Table…

Continue ReadingApache Hive Table Update using ACID Transactions and Examples
Comments Off on Apache Hive Table Update using ACID Transactions and Examples
March 26, 2018
Greenplum

Greenplum Computed Column Support and Alternative

Derived or computed columns in Greenplum are columns that are derived from the previously computed columns in same table. These computed columns are virtual columns that are not physically stored in the Greenplum table. Their values are re-calculated every time they are referenced in a query. Many relational databases such as Netezza supports derived or computed columns but Greenplum does not support as of now. Development work is going on to include this feature in upcoming ProsgreSQL release. In this article, we will check Greenplum Computed Column Support and alternative…

Continue ReadingGreenplum Computed Column Support and Alternative
Comments Off on Greenplum Computed Column Support and Alternative
March 22, 2018
BigData

Apache Hive Derived Column Support and Alternative

Derived columns are columns that are derived from the previously derived or computed columns in same table. Derived columns or computed columns are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. Many relational databases such as Netezza supports derived columns but Apache Hive does not support derived columns. In this article, we will check Apache Hive Derived Column Support and Alternative method that you can use to derive columns. What are derived columns? Before going in…

Continue ReadingApache Hive Derived Column Support and Alternative
Comments Off on Apache Hive Derived Column Support and Alternative
March 22, 2018
BigData

How to update Hive Table without Setting Table Properties?

Apache Hive and Cloudera Impala provides better way to manage data on Hadoop ecosystem. There are many frameworks to support SQL on Hadoop are available, but Hive and Cloudera are widely used and popular frameworks. Until recently, Apache Hive did not support Update tables. You must set up TBLPROPERTIES to use transaction on the Hive table. These are relatively new features and should be used with caution. In this article, we will discuss how to update Hive table without setting table properties. You should not think Apache Hive as a…

Continue ReadingHow to update Hive Table without Setting Table Properties?
Comments Off on How to update Hive Table without Setting Table Properties?
March 21, 2018