An alternative to ISNULL() and NVL() functions in Hive

The NVL() function enables you to substitute null for a more relevant alternative in the query results. This function accepts two arguments. If the first argument is null, then it returns the second argument. If the first argument is not null, it returns the first one and will ignore the second argument. This function is available in Oracle SQL*Plus, but not in MySQL, SQL Server, and Hive.

However, as an alternative, ISNULL() and COALESCE() functions can be used to achieve the same result in MySQL and SQL Server. Since ISNULL() is not available in Hive, COALESCE() function is the only option to achieve the desired output.

The difference between NVL() and COALESCE() is that COALESCE() will return the first non-null value from the list of expressions while NVL() only takes two parameters and returns the first if it is not null, otherwise, returns the second.

Let's see what these three functions will do.

Oracle:
SELECT first_name + middle_name + last_name As EmpName
FROM Employees;

Result:

Employees
---------------------------
Robert Finn Hill
Bruce M. Wills
Maria Andrew Brown
NULL

The last row is null because there is no middle name of the employee. NULL is returned when concatenated the null with first-name and last-name. There we use NVL() function. 

SELECT first_name + NVL(middle_name, ' ') + last_name As EmpName
FROM Employees;

Result:

Employees
----------------------------
Robert Finn Hill
Bruce M. Wills
Maria Andrew Brown
Ashley Miller

SQL Server:

SELECT first_name + ISNULL(middle_name,'') + last_name As EmpName
FROM Employees;

SELECT first_name + COALESCE(middle_name,'') + last_name As EmpName
FROM Employees;

Hive:

SELECT first_name + COALESCE(middle_name,'') + last_name As EmpName
FROM Employees;

Hope you find this article helpful.

Hive Internal vs External Tables

This article offers a summary of the situations in which 

you would need to create internal (managed) tables and external tables in Apache Hive.

Create "External" tables when:

• the data is being used outside the Hive. The data files are read and interpreted by an existing program that does not lock the files, for instance.
  
  
• data needs to stay in the underlying position even after a DROP TABLE. In other words, the data file always stays on the HDFS server even if you delete an external table. This also means that Metadata is maintained on the master node, and deleting an external table from HIVE only deletes the metadata not the data/file.
  
  
• you choose a custom place to be used, use external tables.
  
  
• Hive doesn't own the data.
  
  
• you are not creating a table based on an existing table (AS SELECT), use external tables.
  
  
• you are okay with the fact that External table files are accessible to anyone who has access to HDFS. Security needs to be handled at the HDFS folder level.

Create "Internal" tables when:

• the data is temporary.
  
  
• you want Hive to completely manage the lifecycle of the table and data.
  
  
• you want the data and metadata to be stored inside Hive's warehouse.
  
  
• You are okay with the fact that table deletion would also erase the master-node and HDFS metadata and actual data, respectively.
  
  
• you want the security of the data to be controlled solely via HIVE. 

Conclusion:

In "Internal" tables, the table is created first and data is loaded later.

In "External" tables, the data is already present in HDFS and the table is created on top of it.

Big Data: Apache Hive & Impala Data Types Quick Reference

This article offers an overview of the various data types that are available both in Apache Hive & Impala. 

TINYINT - 1 byte 
Range: -128 to 127

SMALLINT - 2 bytes 

Range: -32,768 to 32,767

INT - 4-bytes

Range: -2,147,483,648 to 2,147,483,647

BigInt - 8 bytes value

Range: -9223372036854775808 .. 9223372036854775807.

FLOAT  - 4 bytes
single precision floating point number

DOUBLE - 8-byte
double precision floating point number

DECIMAL 

Hive 0.13.0 introduced user definable precision and scale

STRING 

The hard limit on the size of a STRING and the total size of a row is 2 GB.

The limit is 1 GB on STRING when writing to Parquet files.

TIMESTAMP

Timestamps were introduced in Hive 0.8.0. It supports traditional UNIX timestamp with the optional nanosecond precision.

The supported Timestamps format is yyyy-mm-dd hh:mm:ss[.f…].

Complex types:

Complex types (also referred to as nested types) in Hive let you represent multiple data values within a single row/column position. Impala supports the complex types ARRAY, MAP, and STRUCT in Impala 2.3 and higher. 

Arrays: Array<data_type>

     Collection of Similar Data

Maps: Map<primitive_type, data_type>

     Key Value Combination

Structs: Struct<col_name : data_type [Comment col_comment], …>

    Collection of Different Data

TOP, LIMIT, ROWNUM vs DENSE_RANK

What would you do if you were asked to identify top-ten products based on their prices?

In SQL Server, using a TOP clause with a specified number of records with descending order of price?

In MySQL and Impala, using a LIMIT clause with a specified number of records with descending order of price?

Basically, TOP (in SQL Server), LIMIT (in MySQL and Impala), or ROWNUM (in Oracle SQL*Plus) keywords are used for pagination or page-results or limit the number of rows and is useful when applied on large tables. They will not help in identifying the rankings directly unless some workarounds. 

Let's create some sample data and do some exercises to understand the scenario.

The following statement will create a "Products" table:

CREATE TABLE Products
(
ProductName STRING,
Price DECIMAL(7,2)
);

INSERT INTO Products (ProductName, Price) VALUES

('Delights breads',25),
('Galaxy Chocolates',20),
('Kitkat Chocolates',22),
('Rainbow Chocolates',19),
('Americana Chocobread',26),
('Palm Milky Chocobars',28),
('Bounty chocolates',26),
(Sparkles chocos',23),
('Smiley Cocos',21),
('DelightPlus chocos',22),
('Softy chocobar',18),
('Minis chocos',8)

Now we have "Products" table with data.

Let's query against the table to retrieve "Products" data based on the descending order of "Price" 

SELECT ProductName, Price FROM Products
ORDER BY Price DESC;

Now, let us retrieve the top ten product information based on the highest price using LIMIT clause.

SELECT ProductName, Price FROM Products
ORDER BY Price DESC
LIMIT 10;

This returned ten rows however by looking at the data we can say it is not giving the information what we are looking for. i.e. the top-ten product information. There are some products that have the same price hence it will be considered only Top-8 products.

In this scenario, we need to use DENSE_RANK to fetch the ranking of the products based on their price.

SELECT * FROM (
SELECT ProductName, Price, DENSE_RANK() OVER (ORDER BY Price DESC)
AS RankValue FROM Products)
AS Tab
WHERE RankValue <= 10;

Finally, we have successfully retrieved top-ten product information.


Hope you find this article helpful.

Sqoop Complete Tutorial Part-7

This is the continuation part of "Sqoop Complete Tutorial". If you want to read -

Part-1, please click here. 

Part-2, please click here. 

Part-3, please click here. 

Part-4, please click here. 

Part-5, please click here. 

Part-6, please click here. 

18) Importing all tables from MySQL to Hive  

Importing a table from MySQL to Hive's default database. The below command will help in copying all the tables from MySQL to Hive user database. 

sqoop import-all-tables
--connect jdbc:mysql://localhost/empdept
--username root
--password cloudera
--hive-import
--hive-database dbTest

The result is below:

If you look at the result, the data is replicated. This is because the tables "emp" and "dept" already exists in the database. hive-overwrite will help in replacing the data if already exist. 

sqoop import-all-tables
--connect jdbc:mysql://localhost/empdept
--username root
--password cloudera
--hive-import
--warehouse-dir /user/hive/warehouse/dbtest
--hive-database dbtest
--hive-overwrite

Here we have additionally provided the warehouse directory to specify the location of the database.

19) Importing all tables but excluding few from MySQL to Hive  

I have created a table named "location" in my current database 'empdept'.

I am about to import all tables but excluding 'emp' and 'dept' since those were already imported. Since "location" is the only table to import, I can specify the table name, however, let's see how it can be done with sqoop-import-all.

sqoop import-all-tables
--connect jdbc:mysql://localhost/empdept
--username root
--password cloudera
--hive-import
--hive-database dbtest
--exclude-tables "emp,dept"

If you look at the above screenshot, the import process selecting only "loc" table and excluding the tables "emp" and "dept" from the import.

The import process is completed and the table schema and data populated into Hive warehouse/database. Let's verify in Hive.

 

Please click here for the next part.

Sqoop Complete Tutorial Part-6

This is the continuation part of "Sqoop Complete Tutorial". If you want to read -

Part-1, please click here. 

Part-2, please click here. 

Part-3, please click here. 

Part-4, please click here. 

Part-5, please click here. 

16) Importing a table from MySQL to Hive's default database.  

Importing a table from MySQL to Hive's default database. The below command will help in copying "emp" table and data from MySQL to Hive "default" database as "employee".

sqoop import
--connect jdbc:mysql://localhost/empdept
--table emp
--username root
--password cloudera
--hive-import
--hive-table employees

Verifying the data:

17) Importing a table from MySQL to Hive's user database.  

Importing a table from MySQL to Hive's default database. The below command will help in copying "emp" table and data from MySQL to Hive's user database (dbTest) as "employee" table.

sqoop import 
--connect jdbc:mysql://localhost/empdept 
--table emp 
--username root 
--password cloudera 
--hive-import 
--hive-table employees
--hive-database dbTest

Verifying the data:

Please click here for the next part.

Get Table Row Count And Used Size

The following query will help you in checking the total number of rows and the size of the table(s). 

SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY Total_MB DESC
GO

Last Modified Date - Stored Procedure

Usually, several stored procedures will be provided to DBAs to deploy in the Production environment. Some of the existing stored procedures need to be replaced or sometimes the stored procedures are new to the environment.

If the deployment process is not automated, it can cause some confusion in DBAs if the existing stored procedure is replaced or missed.

The below script will help you to clear any doubts by looking at the last modified date of the stored procedure.

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC

Please do let me know if you find any other approach.

Import from Excel - Named Sheet

This article is to let you know how to import Excel data into SQL Server.

First of all download a set of components i.e OLE DB support that can be used to facilitate the transfer of data between Microsoft Office System files and non-Microsoft Office applications.

Please click here for the Microsoft download link.

The excel file has only one sheet and it is named as Sheet1 by default. Use the below script to import this data into SQL Server.

USE TestDB1

GO

CREATE TABLE SampleTable(IDCol INT, Col2 VARCHAR(10))

INSERT INTO SampleTable
SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0; Database=D:\TestDataFolder\sample.xlsx',
                 'SELECT * FROM [Sheet1$]')
GO

SELECT * FROM SampleTable

If the excel file's tab has a specific name, i.e. named sheet then -

USE TestDB1
GO

CREATE TABLE SampleTable(IDCol INT, Col2 VARCHAR(10))

INSERT INTO SampleTable
SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0; Database=D:\TestDataFolder\sample.xlsx',
                 'SELECT * FROM [Sample Data$]')
Go

SELECT * FROM SampleTable

Hope you find this article helpful.

Generate multiple CSV data files from SQL

Assume that there are thousands of rows in a table and the rows need to be split and saved in different CSV files based on date or category or status. Have you ever come across such a requirement?

If so, here is the code to accomplish it.

USE TestDB1
GO

DECLARE @TestData20 TABLE(IntValCol INT, DateCol DATETIME)

INSERT INTO @TestData20 (IntValCol, DateCol) VALUES
(1,'09/05/2020'), (2,'09/05/2020'), (3,'09/06/2020'), (4,'09/06/2020'),
(5,'09/07/2020'), (6,'09/07/2020'), (7,'09/08/2020'), (8,'09/08/2020'),
(9,'09/09/2020'), (10,'09/09/2020'),(11,'09/10/2020'), (12,'09/10/2020') 

-- Declaring Variables
DECLARE @MinDate DATETIME,
              @MaxDate DATETIME,
              @FileName VARCHAR(30),
              @FilePath VARCHAR(100),
              @BCPCommand VARCHAR(4000) 

-- Assigning Values To Variables
SELECT @MinDate = MIN(DateCol) FROM @TestData20
SELECT @MaxDate = MAX(DateCol) FROM @TestData20

--Creating the loop
WHILE @MinDate <= @MaxDate
BEGIN
       --Setting up the filename and filepath
       SET @FileName = 'TempData'+'_'+CONVERT(VARCHAR(8),@MinDate,112)+'.csv'
       SET @FilePath = 'D:\TestDataFolder\'+@FileName

       --Fetching the data into a table
       SELECT * INTO Temp FROM @TestData20 WHERE DateCol = @MinDate 

       --Assigning the BCP statement to the variable
       SET @BCPCommand = 'BCP  TestDB1.dbo.Temp out '+@FilePath+' -T -w -t, '

       --Executing the BCP command through command-shell
       EXEC master..xp_cmdshell @BCPCommand

       --Dropping the table, to utilize it again
       DROP TABLE Tempf

       SET @MinDate = DATEADD(D,1,@MinDate)

END

Hope you find this article helpful.