Querying Data

Function Description

This section describes how to use HiveQL to query and analyze data. You can query and analyze data using the following methods:

• Use common features of a SELECT query, such as JOIN.
• Load data to a specified partition.
• Use built-in functions of Hive.
• Query and analyze data using user-defined functions. For details about how to create and define functions, see User-defined Functions.

Sample Code

-- Query contact information of employees whose salaries are paid in USD.
SELECT  
a.name,  
b.tel_phone,  
b.email  
FROM employees_info a JOIN employees_contact b  ON(a.id = b.id) WHERE usd_flag='D'; 
 
-- Query the IDs and names of employees who were hired in 2014, and load the query results to the partition with the hire date of 2014 in the employees_info_extended table.
INSERT OVERWRITE TABLE employees_info_extended PARTITION (entrytime = '2014')  
SELECT  
a.id,  
a.name,  
a.usd_flag,  
a.salary,  
a.deductions,  
a.address, 
b.tel_phone, 
b.email  
FROM employees_info a JOIN employees_contact b ON (a.id = b.id) WHERE a.entrytime = '2014'; 
 
-- Use the existing function COUNT() in Hive to calculate the number of records in the employees_info table.
SELECT COUNT(*) FROM employees_info; 
 
-- Query information about employees whose email addresses end with "cn".
SELECT a.name, b.tel_phone FROM  employees_info a JOIN employees_contact b ON (a.id = b.id) WHERE b.email like '%cn'; 

Extended Application

• Configure intermediate Hive data encryption.

  Set the table format to RCFile (recommended) or SequenceFile, and the encryption algorithm to ARC4Codec. SequenceFile is a unique Hadoop file format, and RCFile is a Hive file format with optimized column storage. When a big table is queried, RCFile provides higher performance than SequenceFile.

  set hive.exec.compress.output=true; 
   set hive.exec.compress.intermediate=true; 
   set hive.intermediate.compression.codec=org.apache.hadoop.io.encryption.arc4.ARC4Codec;

• For details about user-defined functions, see User-defined Functions.