New Features in Impala 2.2.0 for mapR

Spill to Disk

Impala 2.2.0 for MapR introduces the "spill to disk" feature which prevents queries that use memory-intensive operations, such as large sort, join, aggregation, or analytic function operations, from failing with out-of-memory errors. Impala automatically writes data to disk when queries with memory-intensive operations exceed the set memory limit on an Impala node. You can enable or disable the spill to disk feature in /opt/mapr/impala/impala-<version>/conf/env.sh. You can also change the DISABLE_UNSAFE_SPILLS setting at the session level to allow or prevent data spills.

See Spill to Disk for more information about this feature.

Security

You can use SQL statements to create roles and grant/revoke privileges on objects.

Note: This security functionality requires Sentry 1.6 .

The following statements create roles and grant privileges:

• CREATE ROLE - creates roles for users and groups
• GRANT - grants privileges on objects

The following statements revoke roles and privileges:

• DROP ROLE - removes roles from the metastore database

• REVOKE - revokes roles or privileges on a specified object from groups

The following statements display role related information:

• SHOW GRANT ROLE - list all the grants for the given role name
• SHOW ROLES - displays roles
• SHOW ROLE GRANT GROUP - lists all the roles assigned to a specified group

Smaller Parquet and MapR-FS Block Size

The default setting for the PARQUET_FILE_SIZE query option has changed from 1 GB to 256 MB. This makes the file more accurate and reduces the amount of memory reserved during an INSERT into Parquet tables, potentially avoiding out-of-memory errors and improving scalability when inserting data into Parquet tables

.impalarc Configuration File

You can specify impala-shell options from the command line or in the $HOME/.impalarc configuration file. You can define a set of default options for your impala-shell environment in the$HOME/.impalarc configuration file. For every command line option, there is an equivalent setting in the $HOME/.impalarc configuration file. Options specified from the impala-shell command line override corresponding options in the configuration file. See Impala-Shell Command Line Options for more information. The configuration file must contain a header label [impala], followed by the options specific to impala-shell. This is a standard convention for configuration files that enables a single file to hold configuration options for multiple applications. To specify a different filename or path for the configuration file, specify the argument

--config_file=path_to_config_file

on the impala-shell command line.

Files

Impala can read GZIP, BZIP, or Snappy compressed text files.The files do not require special table settings to work in an Impala text table. Impala ignores temporary files typically produced by ETL tools, such as those with the suffixes .copying and .tmp.

Log Rotation

Log rotation is the automatic removal of unneeded or old log files. By default, Impala switches out old log files every 5 seconds, based on the default interval specified in the logbufsecs setting. The -max_log_files configuration option specifies how many log files to keep at each severity level (INFO, WARNING, ERROR, and FATAL). You can configure the

Impala Debug Web Interface

The Impala debug web interface displays a visual representation of the query plan. You can access the Impala debug web interface at

Date and Time Improvements

Flexibility to interpret TIMESTAMP values using the UTC time zone or using the local time zone, for compatibility with TIMESTAMP values produced by Hive.

Startup flags for the impalad daemon enable a higher level of compatibility with TIMESTAMP values written by Hive and more flexibility for working with date and time data using the local time zone instead of UTC. To enable these features, set the impalad startup flags -use_local_tz_for_unix_timestamp_conversions=true and -convert_legacy_hive_parquet_utc_timestamps=true.

The -use_local_tz_for_unix_timestamp_conversions setting controls how the unix_timestamp(), from_unixtime(), and now() functions handle time zones. By default, this setting disabled, and Impala considers all TIMESTAMP values to be in the UTC time zone when converting to or from Unix time values. When this setting is enabled, Impala treats TIMESTAMP values passed to or returned from these functions to be in the local time zone. When this setting is enabled, verify that all hosts in the cluster have the same timezone settings to avoid inconsistent results depending on which host reads or writes TIMESTAMP data.

The -convert_legacy_hive_parquet_utc_timestamps setting causes Impala to convert TIMESTAMP values to the local time zone when it reads them from Parquet files written by Hive. This setting only applies to data using the Parquet file format where Impala can use metadata in the files to reliably determine that the files were written by Hive. If in the future Hive changes the way it writes TIMESTAMP data in Parquet, Impala will automatically handle that new TIMESTAMP encoding.

Built-in functions that accept or return integers representing TIMESTAMP values use the BIGINT type for parameters and return values rather than INT. This change prevents the date and time functions from having overflow errors that would otherwise occur on January 19th, 2038 (known as the "Year 2038 problem" or "Y2K38 problem"). This change affects the from_unixtime() and unix_timestamp() functions. You might need to change application code that interacts with these functions, change the types of columns that store the return values, or add CAST() calls to SQL statements that call these functions.

Analytic Functions

Analytic (window) functions operate on a set of rows and return a single value for each row from the underlying query. The term "window" describes the set of rows on which the function operates. A window function uses values from the rows in a window to calculate the returned values. When you use a window function in a query, you define the window using the OVER() clause. The OVER() clause (window clause) differentiates window functions from other analytical and reporting functions.

As of Impala 2.2.0, you can use the following analytic functions in queries:

• MAX()
• MIN()
• SUM()
• COUNT()
• AVG()
• RANK()
• LAG()
• LEAD()
• FIRST_VALUE()

The analytic functions support the following syntax:

function(args) OVER([partition_by_clause] [order_by_clause [window_clause]])
partition_by_clause ::= PARTITION BY expr [, expr ...]order_by_clause ::= ORDER BY expr  [ASC | DESC] [NULLS FIRST | NULLS LAST] [, expr [ASC |DESC] [NULLS FIRST | NULLS LAST] ...]

ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

RANGE BETWEEN [ {m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

Query Options

You issue query options to Impala using the SET command. SET has been promoted to an SQL statement and can be used in client applications through the JDBC and ODBC APIs.

The following table lists new query options that you can use with the SET statement:

set APPX_COUNT_DISTINCT=true|false;

SET
                  EXEC_SINGLE_NODE_ROWS_THRESHOLD=number_of_rows;

-- 128 megabytes.set PARQUET_FILE_SIZE=134217728INSERT OVERWRITE parquet_table SELECT * FROM text_table;
-- 512 megabytes.set PARQUET_FILE_SIZE=512m;INSERT OVERWRITE parquet_table SELECT * FROM text_table;
-- 1 gigabyte.set PARQUET_FILE_SIZE=1g;INSERT OVERWRITE parquet_table SELECT * FROM text_table;

set PARQUET_FILE_SIZE=size
INSERT OVERWRITE parquet_table SELECT * FROM text_table;

SET QUERY_TIMEOUT_S=seconds;

Functions

The following table lists new functions with their descriptions and syntax:

APPX_MEDIAN([DISTINCT | ALL] expression)

impalad-d 

CURRENT_DATABASE()

DATE_PART(string,timestamp)

decode(type expression, type search1, type result1 [, type search2, type result2...] [, type default] )

mod(numeric_type a, same_type b)

NDV([DISTINCT | ALL] expression)

{ STDDEV | STDDEV_SAMP | STDDEV_POP }([DISTINCT | ALL] expression)

{ VARIANCE | VAR[IANCE]_SAMP | VAR[IANCE]_POP } ([DISTINCT | ALL] expression)

Statements

The following table lists new or improved statements with their descriptions and syntax:

COMPUTE|DROP INCREMENTAL STATS [db_name.]table_name [PARTITION (partition_spec)] partition_spec ::= partition_col=constant_value

CREATEROLErole_name

DROPROLErole_name

GRANT ROLE role_name TO GROUP group_name

GRANT privilege ON object_type object_name
TO [ROLE] roleName
[WITH GRANT OPTION]

privilege ::= SELECT | INSERT | ALL
object_type ::= TABLE | DATABASE | SERVER | URI

REVOKE ROLE role_name FROM GROUP group_name

REVOKE privilege ON object_type object_name
FROM [ROLE] role_name
privilege ::= SELECT | INSERT | ALL
object_type ::= TABLE | DATABASE | SERVER | URI

SHOW FILES IN table

SHOWGRANTROLErole_name

SHOW ROLES

SHOWROLEGRANT GROUP group_name

Data Types

The following table lists new or improved data types with their descriptions:

Clauses and Operators

The following table lists new or improved clauses and operators with descriptions and syntax:

[NOT]EXISTS (subquery)

expression [NOT] IN (expression [,
                  expression]) 

expression [NOT] IN (subquery)

SELECT select_list FROM

table_or_subquery1 {LEFT | RIGHT} ANTI JOIN table_or_subquery2 |

[ ON col1 = col2 [AND col3 = col4 ...] |

USING (col1 [, col2 ...]) ]

[other_join_clause ...]

[ WHERE where_clauses ]

Hint

The Impala SQL dialect supports query hints to fine-tune the inner workings of queries. You can specify hints as a temporary workaround for expensive queries where missing statistics or other factors cause inefficient performance.

Insert + immediately before a hint name to include hints inside comments that use /* */ or – notation.

Example:

INSERT insert_clauses  
/* +SHUFFLE|NOSHUFFLE */  
SELECT remainder_of_query;  
INSERT insert_clauses  
-- +SHUFFLE|NOSHUFFLE  
SELECT remainder_of_query;