HPL/SQL - Procedural SQL on Hadoop, NoSQL and RDBMS

User Tools

Site Tools

Sidebar

HPL/SQL is included to Apache Hive since version 2.0

new

Table of Contents

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

  1. SUMMARY statement (HIVE-17253)
  2. .IF statement (HIVE-13516 Teradata BTEQ compatibility)
  3. .QUIT statement (HIVE-13516 Teradata BTEQ compatibility)
  4. ERRORCODE built-in variable (HIVE-13516 Teradata BTEQ compatibility)
  5. COLLECT STATISTICS for columns statement parser (Teradata compatibility)
  6. Supporting ${name} syntax for identifiers
  7. CREATE TABLE AS SELECT
  8. DELETE ALL to TRUNCATE TABLE conversion (Teradata compatibility)
  9. FALLBACK option parser in CREATE TABLE (Teradata compatibility)
  10. QUALIFY clause in SELECT statement (Teradata compatibility)
  11. PRIMARY INDEX clause in CREATE TABLE (Teradata compatibility)
  12. Subselect statement in expressions (Bug fix)
  13. EXISTS clause in SELECT statement (Bug fix)
  14. UPDATE FROM parser
  15. INTERVAL SECOND parser
  16. Calling a package procedure/function defined in INCLUDE file (Bug fix)
  17. Casting STRING to INT, DECIMAL, FLOAT and DOUBLE (HIVE-13540 Bug fix)
  18. COPY FROM LOCAL returns HOSTCODE 2 when no files found to copy
  19. If source is a directory in COPY FROM LOCAL, always treat target as a directory
  20. Switching between connections (Bug fix)
  21. STORED AS clause in CREATE TABLE for Hive (Parser fix)
  22. WITH DEFAULT, WITH RESTRICT ON DROP, INDEX IN in CREATE TABLE parser (IBM DB2 compatibility)
  23. COPY FROM FTP lack of DIR option leads to NPE (HIVE-13721 Bug fix)
  24. FOR range - REVERSE behavior modified according Oracle PL/SQL HIVE-14382
  25. CLI fails to exit after NPE using embedded connection HIVE-17024
  26. hplsql.conn.convert.hiveconn seems to default to false, contrary to docs HIVE-17025
  27. HPL/SQL requires single quotes for string literals, resulting in surprising behavior HIVE-17027
  28. HPL/SQL function variable block is not initialized HIVE-17452
  29. Casting strings to integers in HPL/SQL differs from Hive behavior HIVE-17028
  30. HPL/SQL: Comparison if not null-safe HIVE-17029
  31. HPL/SQL: Many cast operations are ignored without warning or notice HIVE-17030
  32. HPL/SQL Addition/Subtraction only supported on integers, datetimes and intervals HIVE-17031
  33. HPL/SQL Comparisons are only supported with strings and integers HIVE-17032
  34. DATETIME data type bug fix HIVE-13923
  35. Problems handling escaped quotes within strings HIVE-14862
  36. Multiplication or division for decimal and floating point numbers HIVE-13898

HPL/SQL 0.3.17 - March 28, 2016

  1. COPY FROM FTP statement
  2. CREATE DATABASE statement
  3. DROP DATABASE statement
  4. TRUNCATE statement
  5. DESCRIBE statement
  6. INSERT DIRECTORY statement
  7. REPLACE function
  8. NOW function
  9. UNIX_TIMESTAMP function
  10. FROM_UNIXTIME function
  11. + Operator to concatenate two strings
  12. INT(n) to INT data type conversion (MySQL compatibility)
  13. TEXT to STRING data type conversion (MySQL compatibility)
  14. INT2 to SMALLINT data type conversion (PostgreSQL compatibility)
  15. INT4 to INT data type conversion (PostgreSQL compatibility)
  16. INT8 to BIGINT data type conversion (PostgreSQL compatibility)
  17. INTERVAL keyword in interval expressions
  18. AUTO_INCREMENT, ENGINE, CHARSET, COMMENT clauses parser (MySQL compatibility)
  19. BEGIN TRANSACTION and END TRANSACTION statements parser (Teradata compatibility)
  20. ALTER TABLE ADD CONSTRAINT parser
  21. WHERE clause in UPDATE clause of MERGE statement (Parser fix)
  22. Allow expressions in PARTITION BY clause of window functions (Parser fix)
  23. SET QUERY_BAND statement parser (Teradata compatibility)
  24. ENABLE constraint keyword parser (Oracle compatibility)
  25. Removing [dbo] schema when executing SQL (Microsoft SQL Server compatibility)
  26. By default, not adding FROM hplsql.dual.table to SELECT without FROM
  27. INNER keyword is optional in join (Parser fix)
  28. RAISE statement (Parser fix)
  29. Variable declaration block in procedures and functions (Bug fix)
  30. Subtraction ('-') operator (Bug fix)
  31. Multiplication ('*') and division ('/') operators in SQL statements (Bug fix)
  32. OVERWRITE keyword missed in INSERT statements (Bug fix)
  33. PL/SQL-style stored procedure without parameters (Bug fix)
  34. FOR cursor columns values (Bug fix)
  35. EXCEPTION block in functions (Bug fix)

HPL/SQL 0.3.13 - October 30, 2015

  1. CREATE PACKAGE statement
  2. %TYPE attribute
  3. %ROWTYPE attribute
  4. PART_COUNT function
  5. PART_COUNT_BY function
  6. PLS_INTEGER, BINARY_INTEGER and SIMPLE_INTEGER data types
  7. REAL, FLOAT and DOUBLE data types
  8. BINARY_FLOAT and BINARY_DOUBLE data types
  9. SIMPLE_FLOAT and SIMPLE_DOUBLE data types
  10. BOOL and BOOLEAN data types
  11. CMP statement to compare data in tables
  12. COPY TO HDFS statement to copy JDBC query results to HDFS
  13. NULL statement (no operation)
  14. TRUE and FALSE boolean literals
  15. CONSTANT declarations
  16. Support expressions in INCLUDE statement
  17. BYTE and CHAR column length semantics (Parser)
  18. FOREIGN KEY and UNIQUE constraint in CREATE TABLE (Parser)
  19. Oracle storage clause in CREATE TABLE (Parser)
  20. NOT NULL constraint in variable declaration (Parser)
  21. XML data type (Parser)
  22. Calling stored procedures without parameters (Bug fix)
  23. AS alias in SELECT list (Bug fix)
  24. Subselect in FROM clause (Bug fix)
  25. Multiplication * and division / operators for integers (Bug fix)
  26. NOT boolean expression (Bug fix)
  27. Invoke built-in functions in lower case (Bug fix)
  28. Compare DECIMAL values, DECIMAL and integer values (Bug fix)
  29. NUMBER data type (Bug fix)
  30. Variable scope when calling functions or procedures (Bug fix)
  31. Using parameters in JDBC connection URL to specify advanced options such as Kerberos authentication (Bug fix)

HPL/SQL 0.3.11 - July 30, 2015

  1. SYS_REFCURSOR cursor variable
  2. %ISOPEN, %FOUND and %NOTFOUND cursor attributes
  3. Named parameters in procedure call
  4. SELECT @var = col, … FROM assignment syntax
  5. ALLOCATE CURSOR statement
  6. ASSOCIATE RESULT SET LOCATOR statement
  7. SET CURRENT SCHEMA statement
  8. CURRENT_USER function
  9. CURRENT USER function
  10. INSTR function
  11. SUBSTRING(str FROM start FOR len) function
  12. USER function
  13. VARCHAR(MAX) to STRING data type conversion
  14. DATETIME and SMALLDATETIME to TIMESTAMP data type conversion
  15. NUMERIC to DECIMAL data type conversion
  16. NCHAR and NVARCHAR to STRING data type conversion
  17. BIT to TINYINT data type conversion
  18. Identifier conversion (“” and [] to ``)
  19. UPDATE ELSE INSERT statement parser (UPSERT)
  20. LIKE, RLIKE and REGEXP parser
  21. CUME_DIST function parser
  22. CHARACTER SET data type attribute
  23. CASESPECIFIC data type attribute
  24. DYNAMIC RESULT SETS clause parser in CREATE PROCEDURE
  25. TITLE 'alias' clause parser in SELECT
  26. WITH RETURN TO CALLER/CLIENT clause parser in DECLARE CURSOR
  27. IDENTITY clause parser
  28. SET ANSI_NULLS, ANSI_PADDING, NOCOUNT, QUOTED_IDENTIFIER and XACT_ABORT parser
  29. OPEN cursor FOR SELECT (Bug fix)
  30. SELECT INTO a DECIMAL, BIGINT, SMALLINT or TINYINT variable (Bug fix)
  31. NULL in +/- operations (Bug fix)
  32. Initialize missing parameters to NULL in -main procedure call (Bug fix)
  33. Calling stored procedure without parameters (Bug fix)
  34. Do not throw NOT FOUND exception in FETCH statement (Bug fix)
  35. Division operator for integer values (Bug fix)
  36. Assign an integer value to decimal variable (Bug fix)
  37. Catch exceptions thrown during expression evaluation (Bug fix)

HPL/SQL 0.3.7 - May 19, 2015

  1. COPY statement
  2. COPY FROM LOCAL statement
  3. SET v = (SELECT c FROM ...) assignment
  4. hplsql.conn.db2conn option
  5. hplsql.conn.tdconn option
  6. DATE data type
  7. TIMESTAMP data type
  8. CURRENT_DATE, CURRENT DATE to TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) conversion in executable SQL for Hive
  9. CURRENT_TIMESTAMP, CURRENT TIMESTAMP to FROM_UNIXTIME(UNIX_TIMESTAMP()) conversion in executable SQL for Hive
  10. TOP n to LIMIT n conversion in SELECT statement for Hive
  11. LIMIT clause in SELECT
  12. UPDATE statement parser
  13. DELETE statement parser
  14. MERGE statement parser
  15. Common table expression (CTE) parser
  16. ROW FORMAT DELIMITED, FIELDS TERMINATED, LINES TERMINATED clauses for Hive
  17. Default values parser in procedure parameters
  18. LANGUAGE SQL clause in CREATE PROCEDURE
  19. SQL SECURITY clause in CREATE PROCEDURE
  20. Procedure label in CREATE PROCEDURE
  21. WITH UR, RR, RS, CS clause in SELECT
  22. CREATE INDEX parser
  23. GRANT EXECUTE ON PROCEDURE parser
  24. #, @ and / characters can be used as the statement terminators
  25. CASE expressions in SQL statements (Bug fix)
  26. BETWEEN clause in boolean expressions and SQL statements (Bug fix)
  27. IS NOT NULL clause in boolean expressions and SQL statements (Bug fix)
  28. NOT IN clause in SQL statements (Bug fix)
  29. GROUP BY parser (Bug fix)
  30. ORDER BY parser (Bug fix)
  31. Windows-style file path parser (Bug fix)

HPL/SQL 0.3.1 - April 30, 2015

  1. Hive UDF to run HPL/SQL scripts from Hive CLI queries
  2. Automatically put functions referenced in Hive queries to Distributed Cache, register UDF and modify SQL statements
  3. Multiple connections to different systems from a single script
  4. ACTIVITY_COUNT built-in variable
  5. CONCAT function
  6. DECODE function
  7. TO_CHAR function
  8. TO_TIMESTAMP function
  9. CALL statement
  10. CREATE FUNCTION statement
  11. CREATE LOCAL TEMPORARY TABLE statement
  12. CREATE PROCEDURE statement
  13. CREATE VOLATILE TABLE statement
  14. DECLARE CONDITION statement
  15. DECLARE HANDLER statement - user-defined condition
  16. INCLUDE statement
  17. MAP OBJECT statement
  18. SIGNAL statement
  19. .hplsqlrc configuration file
  20. || operator to CONCAT function conversion in SQL queries
  21. Parser for column list in INSERT statement
  22. hplsql.conn.hiveconn option
  23. hplsql.conn.init.hiveconn option
  24. hplsql.conn.convert.hiveconn option
  25. hplsql.conn.hive2conn option
  26. hplsql.conn.init.hive2conn option
  27. hplsql.conn.convert.hive2conn option
  28. hplsql.conn.<connection_profile> option
  29. hplsql.conn.init.<connection_profile> option
  30. hplsql.conn.mysqlconn option
  31. SET statement - assigning NULL to a variable (Bug fix)
  32. Boolean expressions and predicates (Bug fix)

HPL/SQL 0.3 - March 31, 2015

  1. TIMESTAMP literal
  2. INTERVAL expressions
  3. SQLSTATE built-in variable
  4. CAST function
  5. CHAR function
  6. COALESCE function
  7. CURRENT_TIMESTAMP function
  8. DATE function
  9. LEN function
  10. LENGTH function
  11. LOWER function
  12. SYSDATE function
  13. TIMESTAMP_ISO function
  14. TRIM function
  15. UPPER function
  16. CREATE TABLE statement
  17. DECLARE TEMPORARY TABLE statement
  18. DROP TABLE statement
  19. FOR cursor statement
  20. FROM TABLE (VALUES ...) in SELECT statement
  21. INSERT statement
  22. GET DIAGNOSTICS EXCEPTION statement
  23. RESIGNAL statement
  24. VALUES INTO statement
  25. hplsql.conn.convert.default option
  26. hplsql.dual.table option
  27. hplsql.insert.values option
  28. hplsql.temp.tables option
  29. hplsql.temp.tables.schema option
  30. hplsql.temp.tables.location option
  31. VARCHAR2 to STRING conversion in CREATE TABLE
  32. NUMBER to DECIMAL conversion in CREATE TABLE

HPL/SQL 0.1 - February 27, 2015

  • Initial release

Page Tools

GNU Free Documentation License 1.3 Driven by DokuWiki