User Tools

Site Tools


Sidebar

**HPL/SQL is included to Apache Hive since version 2.0** * [[home|Home]]\\ * [[why|Why HPL/SQL]]\\ * [[features|Key Features]]\\ * [[start|Get Started]]\\ * [[doc|HPL/SQL Reference]]\\ * [[download|Download]]\\ * [[new|What's New]]\\ * [[about|About]]

new

====== What's New ====== Please check the Apache Hive repository for new changes: [[https://github.com/apache/hive/tree/master/hplsql]] New HPL/SQL features: ===== HPL/SQL 0.3.31 - September 11, 2017 ===== - [[summary|SUMMARY]] statement ([[https://issues.apache.org/jira/browse/HIVE-17253|HIVE-17253]]) - [[if|.IF]] statement ([[https://issues.apache.org/jira/browse/HIVE-13516|HIVE-13516]] Teradata BTEQ compatibility) - [[quit|.QUIT]] statement ([[https://issues.apache.org/jira/browse/HIVE-13516|HIVE-13516]] Teradata BTEQ compatibility) - [[errorcode|ERRORCODE]] built-in variable ([[https://issues.apache.org/jira/browse/HIVE-13516|HIVE-13516]] Teradata BTEQ compatibility) - COLLECT STATISTICS for columns statement parser (Teradata compatibility) - Supporting ${name} syntax for identifiers - CREATE TABLE AS SELECT - DELETE ALL to TRUNCATE TABLE conversion (Teradata compatibility) - FALLBACK option parser in CREATE TABLE (Teradata compatibility) - QUALIFY clause in SELECT statement (Teradata compatibility) - PRIMARY INDEX clause in CREATE TABLE (Teradata compatibility) - Subselect statement in expressions (Bug fix) - EXISTS clause in SELECT statement (Bug fix) - UPDATE FROM parser - INTERVAL SECOND parser - Calling a package procedure/function defined in INCLUDE file (Bug fix) - Casting STRING to INT, DECIMAL, FLOAT and DOUBLE ([[https://issues.apache.org/jira/browse/HIVE-13540|HIVE-13540]] Bug fix) - [[copy-from-local|COPY FROM LOCAL]] returns [[hostcode|HOSTCODE]] 2 when no files found to copy - If source is a directory in [[copy-from-local|COPY FROM LOCAL]], always treat target as a directory - Switching between connections (Bug fix) - STORED AS clause in CREATE TABLE for Hive (Parser fix) - WITH DEFAULT, WITH RESTRICT ON DROP, INDEX IN in CREATE TABLE parser (IBM DB2 compatibility) - [[copy-from-ftp|COPY FROM FTP]] lack of DIR option leads to NPE ([[https://issues.apache.org/jira/browse/HIVE-13721|HIVE-13721]] Bug fix) - [[for-range|FOR range]] - REVERSE behavior modified according Oracle PL/SQL [[https://issues.apache.org/jira/browse/HIVE-14382|HIVE-14382]] - CLI fails to exit after NPE using embedded connection [[https://issues.apache.org/jira/browse/HIVE-17024|HIVE-17024]] - hplsql.conn.convert.hiveconn seems to default to false, contrary to docs [[https://issues.apache.org/jira/browse/HIVE-17025|HIVE-17025]] - HPL/SQL requires single quotes for string literals, resulting in surprising behavior [[https://issues.apache.org/jira/browse/HIVE-17027|HIVE-17027]] - HPL/SQL function variable block is not initialized [[https://issues.apache.org/jira/browse/HIVE-17452|HIVE-17452]] - Casting strings to integers in HPL/SQL differs from Hive behavior [[https://issues.apache.org/jira/browse/HIVE-17028|HIVE-17028]] - HPL/SQL: Comparison if not null-safe [[https://issues.apache.org/jira/browse/HIVE-17029|HIVE-17029]] - HPL/SQL: Many cast operations are ignored without warning or notice [[https://issues.apache.org/jira/browse/HIVE-17030|HIVE-17030]] - HPL/SQL Addition/Subtraction only supported on integers, datetimes and intervals [[https://issues.apache.org/jira/browse/HIVE-17031|HIVE-17031]] - HPL/SQL Comparisons are only supported with strings and integers [[https://issues.apache.org/jira/browse/HIVE-17032|HIVE-17032]] - DATETIME data type bug fix [[https://issues.apache.org/jira/browse/HIVE-13923|HIVE-13923]] - Problems handling escaped quotes within strings [[https://issues.apache.org/jira/browse/HIVE-14862|HIVE-14862]] - Multiplication or division for decimal and floating point numbers [[https://issues.apache.org/jira/browse/HIVE-13898|HIVE-13898]] ===== HPL/SQL 0.3.17 - March 28, 2016 ===== - [[copy-from-ftp|COPY FROM FTP]] statement - [[create-database|CREATE DATABASE]] statement - [[drop-database|DROP DATABASE]] statement - [[truncate|TRUNCATE]] statement - [[describe|DESCRIBE]] statement - [[insert-directory|INSERT DIRECTORY]] statement - [[replace|REPLACE]] function - [[now|NOW]] function - [[unix-timestamp|UNIX_TIMESTAMP]] function - [[from-unixtime|FROM_UNIXTIME]] function - [[plus#concatenate_two_strings|+ Operator]] to concatenate two strings - [[/data-types#data_type_conversion|INT(n) to INT]] data type conversion (MySQL compatibility) - [[/data-types#data_type_conversion|TEXT to STRING]] data type conversion (MySQL compatibility) - [[/data-types#data_type_conversion|INT2 to SMALLINT]] data type conversion (PostgreSQL compatibility) - [[/data-types#data_type_conversion|INT4 to INT]] data type conversion (PostgreSQL compatibility) - [[/data-types#data_type_conversion|INT8 to BIGINT]] data type conversion (PostgreSQL compatibility) - [[/interval|INTERVAL]] keyword in interval expressions - AUTO_INCREMENT, ENGINE, CHARSET, COMMENT clauses parser (MySQL compatibility) - BEGIN TRANSACTION and END TRANSACTION statements parser (Teradata compatibility) - ALTER TABLE ADD CONSTRAINT parser - WHERE clause in UPDATE clause of MERGE statement (Parser fix) - Allow expressions in PARTITION BY clause of window functions (Parser fix) - SET QUERY_BAND statement parser (Teradata compatibility) - ENABLE constraint keyword parser (Oracle compatibility) - Removing [dbo] schema when executing SQL (Microsoft SQL Server compatibility) - By default, not adding FROM [[configuration#hplsqldualtable|hplsql.dual.table]] to SELECT without FROM - INNER keyword is optional in join (Parser fix) - RAISE statement (Parser fix) - Variable declaration block in procedures and functions (Bug fix) - Subtraction ('-') operator (Bug fix) - Multiplication ('*') and division ('/') operators in SQL statements (Bug fix) - OVERWRITE keyword missed in INSERT statements (Bug fix) - PL/SQL-style stored procedure without parameters (Bug fix) - FOR cursor columns values (Bug fix) - EXCEPTION block in functions (Bug fix) ===== HPL/SQL 0.3.13 - October 30, 2015 ===== - [[create-package|CREATE PACKAGE]] statement - [[type-attribute|%TYPE]] attribute - [[rowtype-attribute|%ROWTYPE]] attribute - [[part-count|PART_COUNT]] function - [[part-count-by|PART_COUNT_BY]] function - [[data-types|PLS_INTEGER]], [[data-types|BINARY_INTEGER]] and [[data-types|SIMPLE_INTEGER]] data types - [[data-types|REAL]], [[data-types|FLOAT]] and [[data-types|DOUBLE]] data types - [[data-types|BINARY_FLOAT]] and [[data-types|BINARY_DOUBLE]] data types - [[data-types|SIMPLE_FLOAT]] and [[data-types|SIMPLE_DOUBLE]] data types - [[data-types|BOOL]] and [[data-types|BOOLEAN]] data types - [[cmp|CMP]] statement to compare data in tables - [[copy|COPY TO HDFS]] statement to copy JDBC query results to HDFS - [[null|NULL]] statement (no operation) - TRUE and FALSE boolean literals - CONSTANT declarations - Support expressions in [[include|INCLUDE]] statement - BYTE and CHAR column length semantics (Parser) - FOREIGN KEY and UNIQUE constraint in CREATE TABLE (Parser) - Oracle storage clause in CREATE TABLE (Parser) - NOT NULL constraint in variable declaration (Parser) - XML data type (Parser) - Calling stored procedures without parameters (Bug fix) - AS alias in SELECT list (Bug fix) - Subselect in FROM clause (Bug fix) - Multiplication * and division / operators for integers (Bug fix) - NOT boolean expression (Bug fix) - Invoke built-in functions in lower case (Bug fix) - Compare DECIMAL values, DECIMAL and integer values (Bug fix) - NUMBER data type (Bug fix) - Variable scope when calling functions or procedures (Bug fix) - Using parameters in JDBC connection URL to specify advanced options such as Kerberos authentication (Bug fix) ===== HPL/SQL 0.3.11 - July 30, 2015 ===== - [[data-types|SYS_REFCURSOR]] cursor variable - [[cursor-attributes|%ISOPEN, %FOUND and %NOTFOUND]] cursor attributes - Named parameters in procedure call - SELECT @var = col, ... FROM assignment syntax - [[allocate-cursor|ALLOCATE CURSOR]] statement - [[associate-locator|ASSOCIATE RESULT SET LOCATOR]] statement - [[set-session#current-schema|SET CURRENT SCHEMA]] statement - [[current-user|CURRENT_USER]] function - [[current-user|CURRENT USER]] function - [[instr|INSTR]] function - [[substring|SUBSTRING(str FROM start FOR len)]] function - [[user|USER]] function - [[data-types#data-type-conversion|VARCHAR(MAX) to STRING]] data type conversion - [[data-types#data-type-conversion|DATETIME and SMALLDATETIME to TIMESTAMP]] data type conversion - [[data-types#data-type-conversion|NUMERIC to DECIMAL]] data type conversion - [[data-types#data-type-conversion|NCHAR and NVARCHAR to STRING]] data type conversion - [[data-types#data-type-conversion|BIT to TINYINT]] data type conversion - [[conversion#language-elements-and-operators|Identifier]] conversion ("" and [] to ``) - UPDATE ELSE INSERT statement parser (UPSERT) - LIKE, RLIKE and REGEXP parser - CUME_DIST function parser - CHARACTER SET data type attribute - CASESPECIFIC data type attribute - DYNAMIC RESULT SETS clause parser in CREATE PROCEDURE - TITLE 'alias' clause parser in SELECT - WITH RETURN TO CALLER/CLIENT clause parser in DECLARE CURSOR - IDENTITY clause parser - SET ANSI_NULLS, ANSI_PADDING, NOCOUNT, QUOTED_IDENTIFIER and XACT_ABORT parser - OPEN cursor FOR SELECT (Bug fix) - SELECT INTO a DECIMAL, BIGINT, SMALLINT or TINYINT variable (Bug fix) - NULL in +/- operations (Bug fix) - Initialize missing parameters to NULL in -main procedure call (Bug fix) - Calling stored procedure without parameters (Bug fix) - Do not throw NOT FOUND exception in FETCH statement (Bug fix) - Division operator for integer values (Bug fix) - Assign an integer value to decimal variable (Bug fix) - Catch exceptions thrown during expression evaluation (Bug fix) ===== HPL/SQL 0.3.7 - May 19, 2015 ===== - [[copy|COPY]] statement - [[copy-from-local|COPY FROM LOCAL]] statement - [[assign#assignment-from-a-select-statement|SET v = (SELECT c FROM ...)]] assignment - [[configuration#hplsqlconndb2conn|hplsql.conn.db2conn]] option - [[configuration#hplsqlconntdconn|hplsql.conn.tdconn]] option - DATE data type - TIMESTAMP data type - CURRENT_DATE, CURRENT DATE to TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) conversion in executable SQL for Hive - CURRENT_TIMESTAMP, CURRENT TIMESTAMP to FROM_UNIXTIME(UNIX_TIMESTAMP()) conversion in executable SQL for Hive - TOP //n// to LIMIT //n// conversion in SELECT statement for Hive - LIMIT clause in SELECT - UPDATE statement parser - DELETE statement parser - MERGE statement parser - Common table expression (CTE) parser - ROW FORMAT DELIMITED, FIELDS TERMINATED, LINES TERMINATED clauses for Hive - Default values parser in procedure parameters - LANGUAGE SQL clause in CREATE PROCEDURE - SQL SECURITY clause in CREATE PROCEDURE - Procedure label in CREATE PROCEDURE - WITH UR, RR, RS, CS clause in SELECT - CREATE INDEX parser - GRANT EXECUTE ON PROCEDURE parser - #, @ and / characters can be used as the statement terminators - CASE expressions in SQL statements (Bug fix) - BETWEEN clause in boolean expressions and SQL statements (Bug fix) - IS NOT NULL clause in boolean expressions and SQL statements (Bug fix) - NOT IN clause in SQL statements (Bug fix) - GROUP BY parser (Bug fix) - ORDER BY parser (Bug fix) - Windows-style file path parser (Bug fix) ===== HPL/SQL 0.3.1 - April 30, 2015 ===== - [[udf|Hive UDF]] to run HPL/SQL scripts from Hive CLI queries - Automatically put functions referenced in Hive queries to Distributed Cache, register [[udf|UDF]] and modify SQL statements - [[connections|Multiple connections]] to different systems from a single script - [[activity_count|ACTIVITY_COUNT]] built-in variable - [[concat|CONCAT]] function - [[decode|DECODE]] function - [[to-char|TO_CHAR]] function - [[to-timestamp|TO_TIMESTAMP]] function - [[call|CALL]] statement - [[create-function|CREATE FUNCTION]] statement - [[create-local-temporary-table|CREATE LOCAL TEMPORARY TABLE]] statement - [[create-procedure|CREATE PROCEDURE]] statement - [[create-volatile-table|CREATE VOLATILE TABLE]] statement - [[declare-condition|DECLARE CONDITION]] statement - [[declare-handler|DECLARE HANDLER]] statement - user-defined condition - [[include|INCLUDE]] statement - [[map-object|MAP OBJECT]] statement - [[signal|SIGNAL]] statement - [[configuration##hplsqlrc-file|.hplsqlrc]] configuration file - [[twopipes||| operator to CONCAT]] function conversion in SQL queries - Parser for column list in [[insert|INSERT]] statement - [[configuration#hplsqlconnhiveconn|hplsql.conn.hiveconn]] option - [[configuration#hplsqlconninithiveconn|hplsql.conn.init.hiveconn]] option - [[configuration#hplsqlconnconverthiveconn|hplsql.conn.convert.hiveconn]] option - [[configuration#hplsqlconnhive2conn|hplsql.conn.hive2conn]] option - [[configuration#hplsqlconninithive2conn|hplsql.conn.init.hive2conn]] option - [[configuration#hplsqlconnconverthive2conn|hplsql.conn.convert.hive2conn]] option - [[configuration#hplsqlconn-connection_profile|hplsql.conn.<connection_profile>]] option - [[configuration#hplsqlconninit-connection_profile|hplsql.conn.init.<connection_profile>]] option - [[configuration#hplsqlconnmysqlconn|hplsql.conn.mysqlconn]] option - [[assign|SET]] statement - assigning NULL to a variable (Bug fix) - Boolean expressions and predicates (Bug fix) ===== HPL/SQL 0.3 - March 31, 2015 ===== - [[timestamp-literal|TIMESTAMP]] literal - [[interval|INTERVAL]] expressions - [[sqlstate|SQLSTATE]] built-in variable - [[cast|CAST]] function - [[char|CHAR]] function - [[coalesce|COALESCE]] function - [[current-timestamp|CURRENT_TIMESTAMP]] function - [[date|DATE]] function - [[len|LEN]] function - [[length|LENGTH]] function - [[lower|LOWER]] function - [[sysdate|SYSDATE]] function - [[timestamp-iso|TIMESTAMP_ISO]] function - [[trim|TRIM]] function - [[upper|UPPER]] function - [[create-table|CREATE TABLE]] statement - [[declare-temporary-table|DECLARE TEMPORARY TABLE]] statement - [[drop-table|DROP TABLE]] statement - [[for-cursor|FOR cursor]] statement - [[select|FROM TABLE (VALUES ...)]] in SELECT statement - [[insert|INSERT]] statement - [[get-diagnostics|GET DIAGNOSTICS EXCEPTION]] statement - [[resignal|RESIGNAL]] statement - [[values-into|VALUES INTO]] statement - [[configuration#hplsqlconnconvertdefault|hplsql.conn.convert.default]] option - [[configuration#hplsqldualtable|hplsql.dual.table]] option - [[configuration#hplsqlinsertvalues|hplsql.insert.values]] option - [[configuration#hplsqltemptables|hplsql.temp.tables]] option - [[configuration#hplsqltemptablesschema|hplsql.temp.tables.schema]] option - [[configuration#hplsqltemptableslocation|hplsql.temp.tables.location]] option - [[data-types#data-type-conversion|VARCHAR2 to STRING]] conversion in CREATE TABLE - [[data-types#data-type-conversion|NUMBER to DECIMAL]] conversion in CREATE TABLE ===== HPL/SQL 0.1 - February 27, 2015 ===== * Initial release