Apache Hive : Write Ordering

Overview

Write ordering controls the physical layout of data within table files. Unlike SORT BY which orders data during query execution, write ordering is applied at write time and persists in the stored files.

Write ordering is supported for Iceberg tables and can be specified during table creation.

Hive supports two write ordering strategies:

• Type-Native Ordering: Sort by one or more columns in a specified order
• Z-Ordering: Multi-dimensional clustering using space-filling curves

Type-Native Column Ordering

Version

Introduced in Hive version 4.1.0

Apache Hive : Apache Hive SQL Conformance

This page documents which parts of the SQL standard are supported by Apache Hive. The information here is not a full statement of conformance but provides users detail sufficient to generally understand Hive’s SQL conformance.

This information is versioned by Hive release version, allowing a user to quickly identify features available to them.

The formal name of the current SQL standard is ISO/IEC 9075 “Database Language SQL”. A revised version of the standard is released from time to time; the most recent update appearing in 2016. The 2016 version is referred to as ISO/IEC 9075:2016, or simply as SQL:2016. Hive’s SQL Conformance pages reference SQL features by the Feature ID values of the SQL:2016 Standard.

Apache Hive : CAST…FORMAT with SQL:2016 datetime formats

Usage

CAST(<timestamp/date> AS <varchar/char/string> [FORMAT <template>])  
CAST(<varchar/char/string> AS <timestamp/date> [FORMAT <template>])

Example

select cast(dt as string format 'DD-MM-YYYY')  
select cast('01-05-2017' as date format 'DD-MM-YYYY')

Template elements, a.k.a. Tokens, a.k.a Patterns a.k.a SQL:2016 Datetime Formats

Notes

For all tokens:

• Patterns are case-insensitive, except AM/PM and T/Z. See these sections for more details.
• For string to datetime conversion, no duplicate format tokens are allowed, including tokens
  that have the same meaning but different lengths (“Y” and “YY” conflict) or different
  behaviors (“RR” and “YY” conflict).

For all numeric tokens:

Apache Hive : Common Table Expression

A Common Table Expression (CTE) is a temporary result set derived from a simple query specified in a WITH clause, which immediately precedes a SELECT or INSERT keyword.  The CTE is defined only within the execution scope of a single statement.  One or more CTEs can be used in a Hive SELECT, INSERT, CREATE TABLE AS SELECT, or CREATE VIEW AS SELECT statement.

Version

Common Table Expressions are added in Hive 0.13.0 with HIVE-1180.

Apache Hive : Compaction pooling

Concept:

Compaction requests and workers can be assigned to pools. A worker assigned to a specific pool will only process compaction requests in that pool. Workers and compaction requests without pool assignment are implicitly belong to the default pool. The pooling concept allows fine tuning of processing compaction requests. For example it is possible to create a pool name ‘high priority compaction’, assign some frequently modified tables to it, and dedicate a set of workers to this pool. As a result, the compaction requests for these tables will be immediately picked up by the dedicated workers, even if there are several other compaction requests (enqueued earlier) in the default queue.

Apache Hive : Datasketches Integration

Apache DataSketches (https://datasketches.apache.org/) is integrated into Hive via HIVE-22939.
This enables various kind of sketch operations thru regular sql statement.

Sketch functions

Naming convention

All sketch functions are registered using the following naming convention:

ds_{sketchType}_{functionName}

For example we have a function called: ds_hll_estimate which could be used to estimate the distinct values from an hll sketch.

sketchType

For detailed info about the sketches themself please refer to the datasketches site!

Apache Hive : Enhanced Aggregation, Cube, Grouping and Rollup

This document describes enhanced aggregation features for the GROUP BY clause of SELECT statements.

Version

Grouping sets, CUBE and ROLLUP operators, and the GROUPING__ID function were added in Hive 0.10.0.
See HIVE-2397, HIVE-3433, HIVE-3471, and HIVE-3613.
Also see HIVE-3552 for an improvement added in Hive 0.11.0.

Version

GROUPING__ID is compliant with semantics in other SQL engines starting in Hive 2.3.0 (see HIVE-16102).
Support for SQL grouping function was added in Hive 2.3.0 too (see HIVE-15409).

Apache Hive : Exchange Partition

The EXCHANGE PARTITION command will move a partition from a source table to target table and alter each table’s metadata.  The Exchange Partition feature is implemented as part of HIVE-4095. Exchanging multiple partitions is supported in Hive versions 1.2.2, 1.3.0, and 2.0.0+ as part of HIVE-11745.

When the command is executed, the source table’s partition folder in HDFS will be renamed to move it to the destination table’s partition folder.  The Hive metastore will be updated to change the metadata of the source and destination tables accordingly.

Apache Hive : Tutorial to write a GenericUDAF

User-Defined Aggregation Functions (UDAFs) are an excellent way to integrate advanced data-processing into Hive. Hive allows two varieties of UDAFs: simple and generic. Simple UDAFs, as the name implies, are rather simple to write, but incur performance penalties because of the use of Java Reflection, and do not allow features such as variable-length argument lists. Generic UDAFs allow all these features, but are perhaps not quite as intuitive to write as Simple UDAFs.

Apache Hive : Hive Operators

Operators Precedences

Relational Operators

The following operators compare the passed operands and generate a TRUE or FALSE value depending on whether the comparison between the operands holds.

Apache Hive : Hive UDFs

Hive User-Defined Functions (UDFs) are custom functions developed in Java and seamlessly integrated with Apache Hive. UDFs are routines designed to accept parameters, execute a specific action, and return the resulting value. The return value can either be a single scalar row or a complete result set, depending on the UDF’s code and the implemented interface. UDFs represent a powerful capability that enhances classical SQL functionality by allowing the integration of custom code, providing Hive users with a versatile toolset. Apache Hive comes equipped with a variety of built-in UDFs that users can leverage. Similar to other SQL-based solutions, Hive also offers functionality to expand its already rich set of UDFs by incorporating custom ones as needed.

Apache Hive : Plugins

Creating Custom UDFs

First, you need to create a new class that extends UDF, with one or more methods named evaluate.

package com.example.hive.udf;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public final class Lower extends UDF {
  public Text evaluate(final Text s) {
    if (s == null) { return null; }
    return new Text(s.toString().toLowerCase());
  }
}

(Note that there’s already a built-in function for this, it’s just an easy example).

Apache Hive : HiveQL

This page is deprecated

Please see the HiveQL Language Manual

Apache Hive : LanguageManual

This is the Hive Language Manual.  For other Hive documentation, see the Hive wiki’s Home page.

• Commands and CLIs

  • Commands
  • Hive CLI (old)
  • Beeline CLI (new)
  • Variable Substitution
  • HCatalog CLI

• File Formats

  • Avro Files
  • ORC Files
  • Parquet
  • Compressed Data Storage
  • LZO Compression

• Data Types

• Data Definition Statements

  • DDL Statements
    • Bucketed Tables
    • Write Ordering (Type-Native & Z-Order)
  • Statistics (Analyze and Describe)
  • Indexes
  • Archiving

• Data Manipulation Statements

  • DML: Load, Insert, Update, Delete
  • Import/Export

• Data Retrieval: Queries

Apache Hive : LanguageManual Archiving

Archiving for File Count Reduction.

Overview

Due to the design of HDFS, the number of files in the filesystem directly affects the memory consumption in the namenode. While normally not a problem for small clusters, memory usage may hit the limits of accessible memory on a single machine when there are >50-100 million files. In such situations, it is advantageous to have as few files as possible.

Apache Hive : LanguageManual Authorization

Introduction

Note that this documentation is referring to Authorization which is verifying if a user has permission to perform a certain action, and not about Authentication (verifying the identity of the user). Strong authentication for tools like the Hive command line is provided through the use of Kerberos. There are additional authentication options for users of HiveServer2.

Hive Authorization Options

Three modes of Hive authorization are available to satisfy different use cases.

Apache Hive : LanguageManual Hive CLI

$HIVE_HOME/bin/hive is a shell utility which can be used to run Hive queries in either interactive or batch mode.

Deprecation in favor of Beeline CLI

HiveServer2 (introduced in Hive 0.11) has its own CLI called Beeline, which is a JDBC client based on SQLLine.  Due to new development being focused on HiveServer2, Hive CLI will soon be deprecated in favor of Beeline (HIVE-10511).

See Replacing the Implementation of Hive CLI Using Beeline and Beeline – New Command Line Shell in the HiveServer2 documentation.

Apache Hive : LanguageManual Commands

Commands are non-SQL statements such as setting a property or adding a resource. They can be used in HiveQL scripts or directly in the CLI or Beeline.

Sample Usage:

Apache Hive : LanguageManual DDL

Overview

HiveQL DDL statements are documented here, including:

• CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX
• DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
• TRUNCATE TABLE
• ALTER DATABASE/SCHEMA, TABLE, VIEW
• MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS)
• SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, VIEWS, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE
• DESCRIBE DATABASE/SCHEMA, table_name, view_name, materialized_view_name

PARTITION statements are usually options of TABLE statements, except for SHOW PARTITIONS.

Keywords, Non-reserved Keywords and Reserved Keywords

Version information

Apache Hive : LanguageManual DDL BucketedTables

This is a brief example on creating and populating bucketed tables. (For another example, see Bucketed Sorted Tables.)

Bucketed tables are fantastic in that they allow much more efficient sampling than do non-bucketed tables, and they may later allow for time saving operations such as mapside joins. However, the bucketing specified at table creation is not enforced when the table is written to, and so it is possible for the table’s metadata to advertise properties which are not upheld by the table’s actual layout. This should obviously be avoided. Here’s how to do it right.

Apache Hive : LanguageManual DML

Hive Data Manipulation Language

Loading files into tables

Hive does not do any transformation while loading data into tables. Load operations are currently pure copy/move operations that move datafiles into locations corresponding to Hive tables.

Syntax

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)

Synopsis

Load operations prior to Hive 3.0 are pure copy/move operations that move datafiles into locations corresponding to Hive tables.

Apache Hive : LanguageManual Explain

EXPLAIN Syntax

Hive provides an EXPLAIN command that shows the execution plan for a query. The syntax for this statement is as follows:

EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query

AUTHORIZATION is supported from HIVE 0.14.0 via HIVE-5961. VECTORIZATION is supported from Hive 2.3.0 via HIVE-11394. LOCKS is supported from Hive 3.2.0 via HIVE-17683.

AST was removed from EXPLAIN EXTENDED in HIVE-13533 and reinstated as a separate command in HIVE-15932.

The use of EXTENDED in the EXPLAIN statement produces extra information about the operators in the plan. This is typically physical information like file names.

Apache Hive : LanguageManual GroupBy

Group By Syntax

groupByClause: GROUP BY groupByExpression (, groupByExpression)*

groupByExpression: expression

groupByQuery: SELECT expression (, expression)* FROM src groupByClause?

In groupByExpression columns are specified by name, not by position number. However in Hive 0.11.0 and later, columns can be specified by position when configured as follows:

• For Hive 0.11.0 through 2.1.x, set hive.groupby.orderby.position.alias to true (the default is false).
• For Hive 2.2.0 and later, set hive.groupby.position.alias to true (the default is false).

Simple Examples

In order to count the number of rows in a table:

Apache Hive : LanguageManual Import/Export

Version information

The EXPORT and IMPORT commands were added in Hive 0.8.0 (see HIVE-1918).

Replication extensions to the EXPORT and IMPORT commands were added in Hive 1.2.0 (see HIVE-7973 and Hive Replication Development).

Overview

The EXPORT command exports the data of a table or partition, along with the metadata, into a specified output location. This output location can then be moved over to a different Hadoop or Hive instance and imported from there with the IMPORT command.

Apache Hive : LanguageManual Indexing

Indexing Is Removed since 3.0

There are alternate options which might work similarily to indexing:

• Materialized views with automatic rewriting can result in very similar results.  Hive 2.3.0 adds support for materialzed views.
• Using columnar file formats (Parquet, ORC) – they can do selective scanning; they may even skip entire files/blocks.

Indexing has been removed in version 3.0 (HIVE-18448).

Overview of Hive Indexes

The goal of Hive indexing is to improve the speed of query lookup on certain columns of a table. Without an index, queries with predicates like ‘WHERE tab1.col1 = 10’ load the entire table or partition and process all the rows. But if an index exists for col1, then only a portion of the file needs to be loaded and processed.

Apache Hive : LanguageManual Join Optimization

Improvements to the Hive Optimizer

Version

The join optimizations described here were added in Hive version 0.11.0. See HIVE-3784 and related JIRAs.

This document describes optimizations of Hive’s query execution planning to improve the efficiency of joins and reduce the need for user hints.

Hive automatically recognizes various use cases and optimizes for them. Hive 0.11 improves the optimizer for these cases:

• Joins where one side fits in memory. In the new optimization:
  • that side is loaded into memory as a hash table
  • only the larger table needs to be scanned
  • fact tables have a smaller footprint in memory
• Star-schema joins
• Hints are no longer needed for many cases.
• Map joins are automatically picked up by the optimizer.

Star Join Optimization

A simple schema for decision support systems or data warehouses is the star schema, where events are collected in large fact tables, while smaller supporting tables (dimensions) are used to describe the data.

Apache Hive : LanguageManual Joins

Join Syntax

Hive supports the following syntax for joining tables:

join_table:
    table_reference [INNER] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition
  | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )

join_condition:
    ON expression

See Select Syntax for the context of this join syntax.

Apache Hive : LanguageManual LateralView

Lateral View Syntax

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

Description

Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in Built-in Table-Generating Functions, a UDTF generates zero or more output rows for each input row. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.

Apache Hive : LanguageManual LZO Compression

General LZO Concepts

LZO is a lossless data compression library that favors speed over compression ratio. See http://www.oberhumer.com/opensource/lzo and http://www.lzop.org for general information about LZO and see Compressed Data Storage for information about compression in Hive.

Imagine a simple data file that has three columns

• id
• first name
• last name

Let’s populate a data file containing 4 records:

19630001     john          lennon
19630002     paul          mccartney
19630003     george        harrison
19630004     ringo         starr

Let’s call the data file /path/to/dir/names.txt.

Apache Hive : LanguageManual ORC

ORC Files

ORC File Format

Version

Introduced in Hive version 0.11.0.

The Optimized Row Columnar (ORC) file format provides a highly efficient way to store Hive data. It was designed to overcome limitations of the other Hive file formats. Using ORC files improves performance when Hive is reading, writing, and processing data.

Compared with RCFile format, for example, ORC file format has many advantages such as:

Apache Hive : LanguageManual Sampling

Sampling Syntax

Sampling Bucketized Table

table_sample: TABLESAMPLE (BUCKET x OUT OF y [ON colname])

The TABLESAMPLE clause allows the users to write queries for samples of the data instead of the whole table. The TABLESAMPLE clause can be added to any table in the FROM clause. The buckets are numbered starting from 1. colname indicates the column on which to sample each row in the table. colname can be one of the non-partition columns in the table or rand() indicating sampling on the entire row instead of an individual column. The rows of the table are ‘bucketed’ on the colname randomly into y buckets numbered 1 through y. Rows which belong to bucket x are returned.

Apache Hive : LanguageManual Select

Select Syntax

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]

• A SELECT statement can be part of a union query or a subquery of another query.
• table_reference indicates the input to the query. It can be a regular table, a view, a join construct or a subquery.
• Table names and column names are case insensitive.
  • In Hive 0.12 and earlier, only alphanumeric and underscore characters are allowed in table and column names.
  • In Hive 0.13 and later, column names can contain any Unicode character (see HIVE-6013). Any column name that is specified within backticks (```) is treated literally. Within a backtick string, use double backticks (````) to represent a backtick character.
  • To revert to pre-0.13.0 behavior and restrict column names to alphanumeric and underscore characters, set the configuration property [hive.support.quoted.identifiers](#hive-support-quoted-identifiers) to none. In this configuration, backticked names are interpreted as regular expressions. For details, see Supporting Quoted Identifiers in Column Names (attached to HIVE-6013). Also see REGEX Column Specification below.
• Simple query. For example, the following query retrieves all columns and all rows from table t1.

SELECT * FROM t1 

Note

Apache Hive : LanguageManual SortBy

Order, Sort, Cluster, and Distribute By

This describes the syntax of SELECT clauses ORDER BY, SORT BY, CLUSTER BY, and DISTRIBUTE BY.  See Select Syntax for general information.

Syntax of Order By

The ORDER BY syntax in Hive QL is similar to the syntax of ORDER BY in SQL language.

colOrder: ( ASC | DESC )
colNullOrder: (NULLS FIRST | NULLS LAST)           -- (Note: Available in Hive 2.1.0 and later)
orderBy: ORDER BY colName colOrder? colNullOrder? (',' colName colOrder? colNullOrder?)*
query: SELECT expression (',' expression)* FROM src orderBy

There are some limitations in the “order by” clause. In the strict mode (i.e., hive.mapred.mode=strict), the order by clause has to be followed by a “limit” clause. The limit clause is not necessary if you set hive.mapred.mode to nonstrict. The reason is that in order to impose total order of all results, there has to be one reducer to sort the final output. If the number of rows in the output is too large, the single reducer could take a very long time to finish.

Apache Hive : LanguageManual SubQueries

Subqueries in the FROM Clause

SELECT ... FROM (subquery) name ...
SELECT ... FROM (subquery) AS name ...   (Note: Only valid starting with Hive 0.13.0)

Hive supports subqueries only in the FROM clause (through Hive 0.12). The subquery has to be given a name because every table in a FROM clause must have a name. Columns in the subquery select list must have unique names. The columns in the subquery select list are available in the outer query just like columns of a table. The subquery can also be a query expression with UNION. Hive supports arbitrary levels of subqueries.

Apache Hive : LanguageManual Transform

Transform/Map-Reduce Syntax

Users can also plug in their own custom mappers and reducers in the data stream by using features natively supported in the Hive language. e.g. in order to run a custom mapper script - map_script - and a custom reducer script - reduce_script - the user can issue the following command which uses the TRANSFORM clause to embed the mapper and the reducer scripts.

Apache Hive : LanguageManual Data Types

Overview

This lists all supported data types in Hive. See Type System in the Tutorial for additional information.

For data types supported by HCatalog, see:

• HCatLoader Data Types
• HCatStorer Data Types
• HCatRecord Data Types

Numeric Types

• TINYINT (1-byte signed integer, from -128 to 127)
• SMALLINT (2-byte signed integer, from -32,768 to 32,767)

INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)

* [`BIGINT`](https://hive.apache.org/docs/latest/language/languagemanual-types#integral-types-tinyintsmallintintintegerbigint) (8-byte signed integer, from `-9,223,372,036,854,775,808` to `9,223,372,036,854,775,807`)
* `FLOAT` (4-byte single precision floating point number)
* `DOUBLE` (8-byte double precision floating point number)
* ```
DOUBLE PRECISION (alias for DOUBLE, only available starting with Hive [2.2.0](https://issues.apache.org/jira/browse/HIVE-13556))

• DECIMAL
  • Introduced in Hive 0.11.0 with a precision of 38 digits
  • Hive 0.13.0 introduced user-definable precision and scale
• NUMERIC (same as DECIMAL, starting with Hive 3.0.0)

Date/Time Types

• TIMESTAMP (Note: Only available starting with Hive 0.8.0)
• DATE (Note: Only available starting with Hive 0.12.0)
• INTERVAL (Note: Only available starting with Hive 1.2.0)

String Types

• STRING
• VARCHAR (Note: Only available starting with Hive 0.12.0)
• CHAR (Note: Only available starting with Hive 0.13.0)

Misc Types

• BOOLEAN
• BINARY (Note: Only available starting with Hive 0.8.0)

Complex Types

• arrays: ARRAY<data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)
• maps: MAP<primitive_type, data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)
• structs: STRUCT<col_name : data_type [COMMENT col_comment], ...>
• union: UNIONTYPE<data_type, data_type, ...> (Note: Only available starting with Hive 0.7.0.)

Column Types

Integral Types (TINYINT, SMALLINT, INT/INTEGER, BIGINT)

Integral literals are assumed to be INT by default, unless the number exceeds the range of INT in which case it is interpreted as a BIGINT, or if one of the following postfixes is present on the number.

Apache Hive : LanguageManual Operators and User-Defined Functions

Overview

All Hive keywords are case-insensitive, including the names of Hive operators and functions.

In Beeline or the CLI, use the commands below to show the latest documentation:

SHOW FUNCTIONS;
DESCRIBE FUNCTION <function_name>;
DESCRIBE FUNCTION EXTENDED <function_name>;

Bug for expression caching when UDF nested in UDF or function

When hive.cache.expr.evaluation is set to true (which is the default) a UDF can give incorrect results if it is nested in another UDF or a Hive function. This bug affects releases 0.12.0, 0.13.0, and 0.13.1. Release 0.14.0 fixed the bug (HIVE-7314).

Apache Hive : LanguageManual Union

Union Syntax

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

UNION is used to combine the result from multiple SELECT statements into a single result set.

• Hive versions prior to 1.2.0 only support UNION ALL (bag union), in which duplicate rows are not eliminated.
• In Hive 1.2.0 and later, the default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

You can mix UNION ALL and UNION DISTINCT in the same query. Mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.

Apache Hive : LanguageManual VariableSubstitution

Introduction

Hive is used for batch and interactive queries. Variable Substitution allows for tasks such as separating environment-specific configuration variables from code.

The Hive variable substitution mechanism was designed to avoid some of the code that was getting baked into the scripting language on top of Hive.

Examples such as the following shell commands may (inefficiently) be used to set variables within a script:

$ a=b
$ hive -e " describe $a "

This is frustrating as Hive becomes closely coupled with scripting languages. The Hive startup time of a couple seconds is non-trivial when doing thousands of manipulations such as multiple hive -e invocations.

Apache Hive : LanguageManual VirtualColumns

Virtual Columns

Hive 0.8.0 provides support for two virtual columns:

One is INPUT__FILE__NAME, which is the input file’s name for a mapper task.

the other is BLOCK__OFFSET__INSIDE__FILE, which is the current global file position.

For block compressed file, it is the current block’s file offset, which is the current block’s first byte’s file offset.

Since Hive 0.8.0 the following virtual columns have been added:

• ROW__OFFSET__INSIDE__BLOCK
• RAW__DATA__SIZE
• ROW__ID
• GROUPING__ID

It is important to note, that all of the virtual columns listed here cannot be used for any other purpose (i.e. table creation with columns having a virtual column will fail with “SemanticException Error 10328: Invalid column name..”)

Apache Hive : LanguageManual WindowingAndAnalytics

Enhancements to Hive QL

Introduced in Hive version 0.11.

This section introduces the Hive QL enhancements for windowing and analytics functions. See “Windowing Specifications in HQL” (attached to HIVE-4197) for details. HIVE-896 has more information, including links to earlier documentation in the initial comments.

All of the windowing and analytics functions operate as per the SQL standard.

The current release supports the following functions for windowing and analytics:

Apache Hive : LanguageManual XPathUDF

Documentation for Built-In User-Defined Functions Related To XPath

UDFs

xpath, xpath_short, xpath_int, xpath_long, xpath_float, xpath_double, xpath_number, xpath_string

• Functions for parsing XML data using XPath expressions.
• Since version: 0.6.0

Overview

The xpath family of UDFs are wrappers around the Java XPath library javax.xml.xpath provided by the JDK. The library is based on the XPath 1.0 specification. Please refer to http://java.sun.com/javase/6/docs/api/javax/xml/xpath/package-summary.html for detailed information on the Java XPath library.

Apache Hive : Literals

Literals

Integral types

Integral literals are assumed to be INT by default, unless the number exceeds the range of INT in which case it is interpreted as a BIGINT, or if one of the following postfixes is present on the number.

String types

String literals can be expressed with either single quotes (’) or double quotes ("). Hive uses C-style escaping within the strings.

Apache Hive : Managed vs. External Tables

Hive fundamentally knows two different types of tables:

• Managed (Internal)
• External

Introduction

This document lists some of the differences between the two but the fundamental difference is that Hive assumes that it owns the data for managed tables. That means that the data, its properties and data layout will and can only be changed via Hive command. The data still lives in a normal file system and nothing is stopping you from changing it without telling Hive about it. If you do though it violates invariants and expectations of Hive and you might see undefined behavior.

Apache Hive : Materialized views

This page documents the work done for the supporting materialized views in Apache Hive.

Version information

Materialized views support is introduced in Hive 3.0.0.

Objectives

Traditionally, one of the most powerful techniques used to accelerate query processing in data warehouses is the pre-computation of relevant summaries or materialized views.

The initial implementation introduced in Apache Hive 3.0.0 focuses on introducing materialized views and automatic query rewriting based on those materializations in the project. In particular, materialized views can be stored natively in Hive or in other systems such as Druid using custom storage handlers, and they can seamlessly exploit new exciting Hive features such as LLAP acceleration. Then the optimizer relies in Apache Calcite to automatically produce full and partial rewritings for a large set of query expressions comprising projections, filters, join, and aggregation operations.

Apache Hive : OperatorsAndFunctions

Hive Operators and Functions

• Hive Plug-in Interfaces - User-Defined Functions and SerDes

• Guide to Hive Operators and Functions

  • Reflect UDF
  • Generic UDAF Case Study
  • Functions for Statistics and Data Mining

Apache Hive : Partition Filter Syntax

Example: for a table having partition keys country and state, one could construct the following filter:

country = "USA" AND (state = "CA" OR state = "AZ")

In particular notice that it is possible to nest sub-expressions within parentheses.

The following operators are supported when constructing filters for partition columns (derived from HIVE-1862):

• =
• <
• <=
• >
• >=
• <>
• AND
• OR
• LIKE (on keys of type string only, supports literal string template with ‘.*' wildcard)

Apache Hive : ReflectUDF

Reflect (Generic) UDF

A Java class and method often exists to handle the exact function a user would like to use in Hive. Rather than having to write a wrapper UDF to call this method, the majority of these methods can be called using reflect UDF. Reflect uses Java reflection to instantiate and call methods of objects; it can also call static functions. The method must return a primitive type or a type that Hive knows how to serialize.

Apache Hive : Scheduled Queries

Introduction

Executing statements periodically can be usefull in

• Pulling informations from external systems
• Periodically updating column statistics
• Rebuilding materialized views

Overview

• The metastore maintains the scheduled queries in the metastore database
• Hiveserver(s) periodically polls the metastore for a scheduled query to be executed

• During execution informations about ongoing/finished executions are kept in the metastore

Scheduled queries were added in Hive 4.0 (HIVE-21884)

Hive has it’s scheduled query interface built into the language itself for easy access:

Apache Hive : SQL Standard Based Hive Authorization

Status of Hive Authorization before Hive 0.13

The default authorization in Hive is not designed with the intent to protect against malicious users accessing data they should not be accessing. It only helps in preventing users from accidentally doing operations they are not supposed to do. It is also incomplete because it does not have authorization checks for many operations including the grant statement. The authorization checks happen during Hive query compilation. But as the user is allowed to execute dfs commands, user-defined functions and shell commands, it is possible to bypass the client security checks.

Apache Hive : Statistics and Data Mining

This page is the secondary documentation for the slightly more advanced statistical and data mining functions that are being integrated into Hive, and especially the functions that warrant more than one-line descriptions.

ngrams() and context_ngrams(): N-gram frequency estimation

N-grams are subsequences of length N drawn from a longer sequence. The purpose of the ngrams() UDAF is to find the k most frequent n-grams from one or more sequences. It can be used in conjunction with the sentences() UDF to analyze unstructured natural language text, or the collect() function to analyze more general string data.

Apache Hive : Supported Features: Apache Hive 3.1

This table covers all mandatory features from SQL:2016 as well as optional features that Hive implements.

Apache Hive : Supported Features: Apache Hive 2.1

Apache Hive : Supported Features: Apache Hive 2.3