BigData

Hive Table Sampling – Concept and Example

The Relational databases like SQL server supports writing queries on a relatively small number of rows from the very large table. In this article, we will check Hive table sampling concept, methods and some examples. Hive Table Sampling Concept The Hive TABLESAMPLE clause allows the users to write queries for samples of the data instead of the whole table. The sampling comes handy when you are working on the large tables and it takes time to return results. The TABLESAMPLE clause can be added to any table in the FROM…

Continue ReadingHive Table Sampling – Concept and Example
Comments Off on Hive Table Sampling – Concept and Example
November 17, 2019
BigData

Apache Hive Integer Value Check – Examples

In a data warehouse environment, there are many options that you can check for an integer value. Using this process, you can usually remove the unwanted records and save some I/O. For example, filter out non-numeric values when comparing it with integer types. In this article, we see different methods to check integer value in Hive. Apache Hive Integer Value Check Many relational databases provide an extended SQL function to help the data warehouse developers. The built-in functions such isnumeric is used to check given string value is a number…

Continue ReadingApache Hive Integer Value Check – Examples
Comments Off on Apache Hive Integer Value Check – Examples
November 17, 2019
BigData

Working with Hive Macros, Syntax and Examples

Many relational databases such as Teradata supports Macro functions. In RDBMS, Macros are stored in the data dictionary. Users can share macros and can execute based on the requirements. Hive Macros are a bit different compared to that of relational databases. In this article, we will check what are Macros, its syntax, how to use them and some macro examples. What are Macros in Hive? The macros in Hive are set of SQL statements which are stored and executed by calling macro function names. Macros exist for the duration of the current session. Macros are…

Continue ReadingWorking with Hive Macros, Syntax and Examples
Comments Off on Working with Hive Macros, Syntax and Examples
November 17, 2019
Apache Spark

Spark DataFrame Column Type Conversion using CAST

In my other post, we have discussed how to check if Spark DataFrame column is of Integer Type. Some application expects column to be of a specific type. For example, Machine learning models accepts only integer type. In this article, we will check how to perform Spark DataFrame column type conversion using the Spark dataFrame CAST method. Spark DataFrame Column Type Conversion You can use the Spark CAST method to convert data frame column data type to required format. Test Data Frame Following is the test data frame (df) that…

Continue ReadingSpark DataFrame Column Type Conversion using CAST
Comments Off on Spark DataFrame Column Type Conversion using CAST
November 16, 2019
Apache Spark

Spark DataFrame Integer Type Check and Example

Apache Spark is one of the easiest framework to deal with different data sources. You can combine heterogeneous data source with the help of dataFrames. Some application, for example, Machine Learning model requires only integer values. You should check the data type of the dataFrame before feeding it to ML models, or you should type cast it to an integer type. In this article, how to perform Spark dataFrame integer type check and how to convert it using CAST function in Spark. Spark DataFrame Integer Type Check Requirement As mentioned…

Continue ReadingSpark DataFrame Integer Type Check and Example
Comments Off on Spark DataFrame Integer Type Check and Example
November 16, 2019
Apache Spark

How to Create Spark SQL User Defined Functions? Example

A user defined function (UDF) is a function written to perform specific tasks when built-in function is not available for the same. In a Hadoop environment, you can write user defined function using Java, Python, R, etc. In this article, we will check how to create Spark SQL user defined functions with an python user defined functionexample. Spark SQL User-defined Functions When you migrate your relational database warehouse to Hive and use Spark as an execution engine, you may miss some of the built-in function support. Some user defined functions…

Continue ReadingHow to Create Spark SQL User Defined Functions? Example
Comments Off on How to Create Spark SQL User Defined Functions? Example
November 16, 2019
Apache Spark

Spark SQL isnumeric Function Alternative and Example

Most of the organizations are moving their data warehouse to the Hive and using Spark as an execution engine. Spark as an execution engine will boost the performance. In SQL, there are many options that you can use to deal with non-numeric values, for example, you can create user defined functions to filter out unwanted data. In this article, we will check Spark SQL isnumeric function alternative and examples. Spark SQL isnumeric Function Spark SQL, or Apache Hive does not provide support for is numeric function. You have to write…

Continue ReadingSpark SQL isnumeric Function Alternative and Example
Comments Off on Spark SQL isnumeric Function Alternative and Example
November 16, 2019
Apache Spark

Spark SQL DataFrame Self Join and Example

You can use Spark Dataset join operators to join multiple dataframes in Spark. Two or more dataFrames are joined to perform specific tasks such as getting common data from both dataFrames. In this article, we will check how to perform Spark SQL DataFrame self join using Pyspark. Spark SQL DataFrame Self Join using Pyspark Spark DataFrame supports various join types as mentioned in Spark Dataset join operators. A self join in a DataFrame is a join in which dataFrame is joined to itself. The self join is used to identify…

Continue ReadingSpark SQL DataFrame Self Join and Example
Comments Off on Spark SQL DataFrame Self Join and Example
November 14, 2019
BigData

Hive Self Join Query, Performance and Optimization

By definition, self join is a join in which a table is joined itself. Self joins are usually used only when there is a parent child relationship in the given data. In this article, we will check how to write self join query in the Hive, its performance issues and how to optimize it. Hive Self Join Query As mentioned earlier, self join is used when there is parent-child relation between your data. For example, consider an employee table. an employee table contains details about the employees and an employee…

Continue ReadingHive Self Join Query, Performance and Optimization
Comments Off on Hive Self Join Query, Performance and Optimization
November 5, 2019
BigData

How to Update or Drop Hive Partition? Steps and Examples

In general, partitions in relational databases are used to increase the performance of the SQL queries. The partition is the concept of storing relevant data in the same place. For example, let us say you want to query the data monthly bases, then you can partition your data on month. In this article, we will check how to update or drop the Hive partition that you have already created. What are Partitions in Hive? Just like relational databases, Apache Hive partitions are used to improve the performance of the HiveQL…

Continue ReadingHow to Update or Drop Hive Partition? Steps and Examples
Comments Off on How to Update or Drop Hive Partition? Steps and Examples
November 3, 2019