HIVE-13367: Extending HPLSQL parser (Dmitry Tolpeko reviewed by Alan Gates)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/39d66a43 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/39d66a43 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/39d66a43 Branch: refs/heads/llap Commit: 39d66a439c02ea2b5c7501b362c0d8f9b8d22cc0 Parents: 55383d8 Author: Dmitry Tolpeko <dmtolp...@gmail.com> Authored: Wed Mar 30 00:19:11 2016 -0700 Committer: Dmitry Tolpeko <dmtolp...@gmail.com> Committed: Wed Mar 30 00:19:11 2016 -0700 ---------------------------------------------------------------------- .../antlr4/org/apache/hive/hplsql/Hplsql.g4 | 266 +++++++++--- .../main/java/org/apache/hive/hplsql/Conf.java | 2 +- .../main/java/org/apache/hive/hplsql/Conn.java | 3 +- .../java/org/apache/hive/hplsql/Converter.java | 15 +- .../main/java/org/apache/hive/hplsql/Exec.java | 106 ++++- .../java/org/apache/hive/hplsql/Expression.java | 13 + .../main/java/org/apache/hive/hplsql/File.java | 11 + .../main/java/org/apache/hive/hplsql/Ftp.java | 415 +++++++++++++++++++ .../main/java/org/apache/hive/hplsql/Meta.java | 35 +- .../java/org/apache/hive/hplsql/Package.java | 3 + .../main/java/org/apache/hive/hplsql/Row.java | 4 +- .../java/org/apache/hive/hplsql/Select.java | 6 +- .../main/java/org/apache/hive/hplsql/Stmt.java | 167 +++++++- .../main/java/org/apache/hive/hplsql/Utils.java | 40 ++ .../main/java/org/apache/hive/hplsql/Var.java | 18 +- .../apache/hive/hplsql/functions/Function.java | 40 +- .../hive/hplsql/functions/FunctionDatetime.java | 40 ++ .../hive/hplsql/functions/FunctionString.java | 26 +- .../org/apache/hive/hplsql/TestHplsqlLocal.java | 28 +- .../apache/hive/hplsql/TestHplsqlOffline.java | 25 ++ .../test/queries/db/create_drop_database.sql | 5 + .../queries/db/create_procedure_no_params.sql | 25 ++ hplsql/src/test/queries/db/describe.sql | 3 + hplsql/src/test/queries/db/execute.sql | 7 + hplsql/src/test/queries/db/expression.sql | 1 + hplsql/src/test/queries/db/for.sql | 1 + hplsql/src/test/queries/db/insert.sql | 3 + hplsql/src/test/queries/db/insert_directory.sql | 12 + hplsql/src/test/queries/db/schema.sql | 10 +- hplsql/src/test/queries/db/truncate_table.sql | 2 + .../src/test/queries/local/create_function3.sql | 58 +++ .../src/test/queries/local/create_function4.sql | 19 + .../test/queries/local/create_procedure3.sql | 29 ++ hplsql/src/test/queries/local/declare3.sql | 7 + hplsql/src/test/queries/local/if.sql | 6 +- hplsql/src/test/queries/local/interval.sql | 4 +- hplsql/src/test/queries/local/replace.sql | 1 + .../queries/offline/create_table_mssql2.sql | 33 ++ .../test/queries/offline/create_table_mysql.sql | 5 + .../test/queries/offline/create_table_ora2.sql | 6 + .../test/queries/offline/create_table_pg.sql | 5 + hplsql/src/test/queries/offline/update.sql | 33 ++ .../results/db/create_drop_database.out.txt | 8 + .../results/db/create_procedure_mssql.out.txt | 2 +- .../db/create_procedure_no_params.out.txt | 10 + .../db/create_procedure_return_cursor.out.txt | 4 +- .../db/create_procedure_return_cursor2.out.txt | 4 +- hplsql/src/test/results/db/describe.out.txt | 12 + hplsql/src/test/results/db/execute.out.txt | 14 + hplsql/src/test/results/db/expression.out.txt | 5 + hplsql/src/test/results/db/for.out.txt | 44 ++ hplsql/src/test/results/db/insert.out.txt | 4 + .../test/results/db/insert_directory.out.txt | 9 + .../test/results/db/rowtype_attribute.out.txt | 2 +- .../src/test/results/db/truncate_table.out.txt | 4 + .../test/results/local/create_function3.out.txt | 22 + .../test/results/local/create_function4.out.txt | 9 + .../test/results/local/create_package.out.txt | 2 +- .../results/local/create_procedure3.out.txt | 31 ++ .../local/create_procedure_no_params.out.txt | 12 +- hplsql/src/test/results/local/declare3.out.txt | 9 + hplsql/src/test/results/local/if.out.txt | 4 + hplsql/src/test/results/local/interval.out.txt | 1 + hplsql/src/test/results/local/replace.out.txt | 1 + .../results/offline/create_table_mssql2.out.txt | 10 + .../results/offline/create_table_mysql.out.txt | 4 + .../results/offline/create_table_ora2.out.txt | 5 + .../results/offline/create_table_pg.out.txt | 5 + hplsql/src/test/results/offline/update.out.txt | 34 ++ 69 files changed, 1672 insertions(+), 137 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 ---------------------------------------------------------------------- diff --git a/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 b/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 index a1083ee..b84116f 100644 --- a/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 +++ b/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 @@ -23,30 +23,38 @@ program : block ; block : ((begin_end_block | stmt) T_GO?)+ ; // Multiple consecutive blocks/statements begin_end_block : - declare_block? T_BEGIN block exception_block? T_END + declare_block? T_BEGIN block exception_block? block_end ; single_block_stmt : // Single BEGIN END block (but nested blocks are possible) or single statement - T_BEGIN block T_END + T_BEGIN block exception_block? block_end | stmt T_SEMICOLON? ; + +block_end : + {!_input.LT(2).getText().equalsIgnoreCase("TRANSACTION")}? T_END + ; proc_block : - T_BEGIN block T_END + begin_end_block | stmt+ T_GO? ; stmt : assignment_stmt | allocate_cursor_stmt + | alter_table_stmt | associate_locator_stmt + | begin_transaction_stmt | break_stmt | call_stmt | close_stmt | cmp_stmt + | copy_from_ftp_stmt | copy_from_local_stmt | copy_stmt | commit_stmt + | create_database_stmt | create_function_stmt | create_index_stmt | create_local_temp_table_stmt @@ -56,7 +64,9 @@ stmt : | create_table_stmt | declare_stmt | delete_stmt + | describe_stmt | drop_stmt + | end_transaction_stmt | exec_stmt | exit_stmt | fetch_stmt @@ -65,6 +75,7 @@ stmt : | if_stmt | include_stmt | insert_stmt + | insert_directory_stmt | get_diag_stmt | grant_stmt | leave_stmt @@ -72,6 +83,7 @@ stmt : | merge_stmt | open_stmt | print_stmt + | raise_stmt | resignal_stmt | return_stmt | rollback_stmt @@ -79,6 +91,7 @@ stmt : | signal_stmt | update_stmt | use_stmt + | truncate_stmt | values_into_stmt | while_stmt | label @@ -123,6 +136,7 @@ assignment_stmt_item : assignment_stmt_single_item : ident T_COLON? T_EQUAL expr + | T_OPEN_P ident T_CLOSE_P T_COLON? T_EQUAL expr ; assignment_stmt_multiple_item : @@ -141,6 +155,10 @@ associate_locator_stmt : T_ASSOCIATE (T_RESULT T_SET)? (T_LOCATOR | T_LOCATORS) T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P T_WITH T_PROCEDURE ident ; +begin_transaction_stmt : + T_BEGIN T_TRANSACTION + ; + break_stmt : T_BREAK ; @@ -156,7 +174,10 @@ declare_stmt : // Declaration statement declare_block : // Declaration block T_DECLARE declare_stmt_item T_SEMICOLON (declare_stmt_item T_SEMICOLON)* ; - + +declare_block_inplace : + declare_stmt_item T_SEMICOLON (declare_stmt_item T_SEMICOLON)* + ; declare_stmt_item : declare_cursor_item @@ -200,7 +221,7 @@ create_table_stmt : ; create_local_temp_table_stmt : - T_CREATE (T_LOCAL T_TEMPORARY | (T_SET | T_MULTISET)? T_VOLATILE) T_TABLE ident T_OPEN_P create_table_columns T_CLOSE_P create_table_options? + T_CREATE (T_LOCAL T_TEMPORARY | (T_SET | T_MULTISET)? T_VOLATILE) T_TABLE ident create_table_preoptions? T_OPEN_P create_table_columns T_CLOSE_P create_table_options? ; create_table_columns : @@ -223,10 +244,12 @@ create_table_column_inline_cons : | T_UNIQUE | T_REFERENCES table_name T_OPEN_P ident T_CLOSE_P create_table_fk_action* | T_IDENTITY T_OPEN_P L_INT (T_COMMA L_INT)* T_CLOSE_P + | T_AUTO_INCREMENT + | T_ENABLE ; create_table_column_cons : - T_PRIMARY T_KEY T_CLUSTERED? T_OPEN_P ident (T_ASC | T_DESC)? (T_COMMA ident (T_ASC | T_DESC)?)* T_CLOSE_P index_storage_clause? + T_PRIMARY T_KEY T_CLUSTERED? T_OPEN_P ident (T_ASC | T_DESC)? (T_COMMA ident (T_ASC | T_DESC)?)* T_CLOSE_P T_ENABLE? index_storage_clause? | T_FOREIGN T_KEY T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P T_REFERENCES table_name T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P create_table_fk_action* ; @@ -234,6 +257,14 @@ create_table_fk_action : T_ON (T_UPDATE | T_DELETE) (T_NO T_ACTION | T_RESTRICT | T_SET T_NULL | T_SET T_DEFAULT | T_CASCADE) ; +create_table_preoptions : + (T_COMMA create_table_preoptions_item)+ + ; + +create_table_preoptions_item : + T_NO? T_LOG + ; + create_table_options : create_table_options_item+ ; @@ -243,7 +274,8 @@ create_table_options_item : | create_table_options_ora_item | create_table_options_db2_item | create_table_options_hive_item - | create_table_options_mssql_item + | create_table_options_mssql_item + | create_table_options_mysql_item ; create_table_options_ora_item : @@ -284,6 +316,31 @@ create_table_options_mssql_item : T_ON ident | T_TEXTIMAGE_ON ident ; + +create_table_options_mysql_item : + T_AUTO_INCREMENT T_EQUAL? expr + | T_COMMENT T_EQUAL? expr + | T_DEFAULT? (T_CHARACTER T_SET | T_CHARSET) T_EQUAL? expr + | T_ENGINE T_EQUAL? expr + ; + +alter_table_stmt : + T_ALTER T_TABLE table_name alter_table_item + ; + +alter_table_item : + alter_table_add_constraint + ; + +alter_table_add_constraint : + T_ADD2 (T_CONSTRAINT ident)? alter_table_add_constraint_item + ; + +alter_table_add_constraint_item : + T_PRIMARY T_KEY T_CLUSTERED? T_OPEN_P ident (T_ASC | T_DESC)? (T_COMMA ident (T_ASC | T_DESC)?)* T_CLOSE_P T_ENABLE? index_storage_clause? + | T_FOREIGN T_KEY T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P T_REFERENCES table_name T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P create_table_fk_action* + | T_DEFAULT expr T_FOR ident + ; dtype : // Data types T_CHAR @@ -299,6 +356,9 @@ dtype : // Data types | T_DOUBLE T_PRECISION? | T_FLOAT | T_INT + | T_INT2 + | T_INT4 + | T_INT8 | T_INTEGER | T_NCHAR | T_NVARCHAR @@ -336,9 +396,18 @@ dtype_default : // Default clause in variable declaration T_COLON? T_EQUAL expr | T_DEFAULT expr ; + +create_database_stmt : + T_CREATE (T_DATABASE | T_SCHEMA) (T_IF T_NOT T_EXISTS)? expr create_database_option* + ; + +create_database_option : + T_COMMENT expr + | T_LOCATION expr + ; create_function_stmt : - (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE)? T_FUNCTION ident create_routine_params? create_function_return (T_AS | T_IS)? single_block_stmt + (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE)? T_FUNCTION ident create_routine_params? create_function_return (T_AS | T_IS)? declare_block_inplace? single_block_stmt ; create_function_return : @@ -374,12 +443,15 @@ package_body_item : ; create_procedure_stmt : - (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE)? (T_PROCEDURE | T_PROC) ident create_routine_params? create_routine_options? (T_AS | T_IS)? label? proc_block (ident T_SEMICOLON)? + (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE)? (T_PROCEDURE | T_PROC) ident create_routine_params? create_routine_options? (T_AS | T_IS)? declare_block_inplace? label? proc_block (ident T_SEMICOLON)? ; create_routine_params : T_OPEN_P T_CLOSE_P - | T_OPEN_P? create_routine_param_item (T_COMMA create_routine_param_item)* T_CLOSE_P? + | T_OPEN_P create_routine_param_item (T_COMMA create_routine_param_item)* T_CLOSE_P + | {!_input.LT(1).getText().equalsIgnoreCase("IS") && + !_input.LT(1).getText().equalsIgnoreCase("AS")}? + create_routine_param_item (T_COMMA create_routine_param_item)* ; create_routine_param_item : @@ -398,6 +470,11 @@ create_routine_option : drop_stmt : // DROP statement T_DROP T_TABLE (T_IF T_EXISTS)? table_name + | T_DROP (T_DATABASE | T_SCHEMA) (T_IF T_EXISTS)? expr + ; + +end_transaction_stmt : + T_END T_TRANSACTION ; exec_stmt : // EXEC, EXECUTE IMMEDIATE statement @@ -444,6 +521,10 @@ insert_stmt_rows : insert_stmt_row: T_OPEN_P expr (T_COMMA expr)* T_CLOSE_P ; + +insert_directory_stmt : + T_INSERT T_OVERWRITE T_LOCAL? T_DIRECTORY expr_file expr_select + ; exit_stmt : T_EXIT L_ID? (T_WHEN bool_expr)? @@ -502,6 +583,10 @@ cmp_source : (table_name where_clause? | T_OPEN_P select_stmt T_CLOSE_P) (T_AT ident)? ; +copy_from_ftp_stmt : + T_COPY T_FROM T_FTP expr copy_ftp_option* + ; + copy_from_local_stmt : // COPY FROM LOCAL statement T_COPY T_FROM T_LOCAL copy_source (T_COMMA copy_source)* T_TO copy_target copy_file_option* ; @@ -531,6 +616,18 @@ copy_file_option : | T_OVERWRITE ; +copy_ftp_option : + T_USER expr + | T_PWD expr + | T_DIR (file_name | expr) + | T_FILES expr + | T_NEW + | T_OVERWRITE + | T_SUBDIR + | T_SESSIONS expr + | T_TO T_LOCAL? (file_name | expr) + ; + commit_stmt : // COMMIT statement T_COMMIT T_WORK? ; @@ -556,6 +653,10 @@ print_stmt : // PRINT statement | T_PRINT T_OPEN_P expr T_CLOSE_P ; +raise_stmt : + T_RAISE + ; + resignal_stmt : // RESIGNAL statement T_RESIGNAL (T_SQLSTATE T_VALUE? expr (T_SET T_MESSAGE_TEXT T_EQUAL expr)? )? ; @@ -571,6 +672,7 @@ rollback_stmt : // ROLLBACK statement set_session_option : set_current_schema_option | set_mssql_session_option + | set_teradata_session_option ; set_current_schema_option : @@ -586,9 +688,17 @@ set_mssql_session_option : (T_ON | T_OFF) ; +set_teradata_session_option : + T_QUERY_BAND T_EQUAL (expr | T_NONE) T_UPDATE? T_FOR (T_TRANSACTION | T_SESSION) + ; + signal_stmt : // SIGNAL statement T_SIGNAL ident ; + +truncate_stmt : + T_TRUNCATE T_TABLE? table_name + ; use_stmt : // USE statement T_USE expr @@ -708,7 +818,7 @@ from_join_clause : ; from_join_type_clause : - T_INNER T_JOIN + T_INNER? T_JOIN | (T_LEFT | T_RIGHT | T_FULL) T_OUTER? T_JOIN ; @@ -761,7 +871,11 @@ select_options_item : ; update_stmt : // UPDATE statement - T_UPDATE update_table T_SET assignment_stmt_item (T_COMMA assignment_stmt_item)* where_clause? update_upsert? + T_UPDATE update_table T_SET update_assignment where_clause? update_upsert? + ; + +update_assignment : + assignment_stmt_item (T_COMMA assignment_stmt_item)* ; update_table : @@ -787,13 +901,17 @@ merge_condition : merge_action : T_INSERT insert_stmt_cols? T_VALUES insert_stmt_row - | T_UPDATE T_SET assignment_stmt_item (T_COMMA assignment_stmt_item)* + | T_UPDATE T_SET assignment_stmt_item (T_COMMA assignment_stmt_item)* where_clause? | T_DELETE ; delete_stmt : // DELETE statement T_DELETE T_FROM? table_name (T_AS? ident)? where_clause? ; + +describe_stmt : + (T_DESCRIBE | T_DESC) T_TABLE? table_name + ; bool_expr : // Boolean condition T_NOT? T_OPEN_P bool_expr T_CLOSE_P @@ -843,13 +961,14 @@ bool_expr_binary_operator : | T_NOT? (T_LIKE | T_RLIKE | T_REGEXP) ; -expr : +expr : expr interval_item | expr T_MUL expr | expr T_DIV expr | expr T_ADD expr | expr T_SUB expr | T_OPEN_P expr T_CLOSE_P + | expr_interval | expr_concat | expr_case | expr_cursor_attribute @@ -859,7 +978,6 @@ expr : | expr_atom ; - expr_atom : date_literal | timestamp_literal @@ -867,11 +985,13 @@ expr_atom : | ident | string | dec_number - | interval_number | int_number | null_const ; +expr_interval : + T_INTERVAL expr interval_item + ; interval_item : T_DAY | T_DAYS @@ -879,10 +999,6 @@ interval_item : | T_MICROSECONDS ; -interval_number : - int_number interval_item - ; - expr_concat : // String concatenation operator expr_concat_item (T_PIPE | T_CONCAT) expr_concat_item ((T_PIPE | T_CONCAT) expr_concat_item)* ; @@ -943,7 +1059,7 @@ expr_func_over_clause : ; expr_func_partition_by_clause : - T_PARTITION T_BY ident (T_COMMA ident)* + T_PARTITION T_BY expr (T_COMMA expr)* ; expr_spec_func : @@ -978,18 +1094,25 @@ expr_func_params : func_param : {!_input.LT(1).getText().equalsIgnoreCase("INTO")}? (ident T_EQUAL T_GREATER?)? expr ; + +expr_select : + select_stmt + | expr + ; +expr_file : + file_name + | expr + ; + hive : T_HIVE hive_item* ; hive_item : - P_e expr - | P_f expr - | P_hiveconf L_ID T_EQUAL expr - | P_i expr - | P_S - | P_h + T_SUB ident expr + | T_SUB ident L_ID T_EQUAL expr + | T_SUB ident ; host : @@ -1006,7 +1129,7 @@ host_stmt : ; file_name : - L_FILE | '/'? ident ('/' ident)* + L_FILE | ('/' | '.' '/')? ident ('/' ident)* ; date_literal : // DATE 'YYYY-MM-DD' literal @@ -1047,6 +1170,7 @@ null_const : // NULL constant non_reserved_words : // Tokens that are not reserved words and can be used as identifiers T_ACTION | T_ACTIVITY_COUNT + | T_ADD2 | T_ALL | T_ALLOCATE | T_ALTER @@ -1057,6 +1181,7 @@ non_reserved_words : // Tokens that are not reserved words | T_ASC | T_ASSOCIATE | T_AT + | T_AUTO_INCREMENT | T_AVG | T_BATCHSIZE | T_BEGIN @@ -1076,12 +1201,14 @@ non_reserved_words : // Tokens that are not reserved words | T_CASESPECIFIC | T_CAST | T_CHAR - | T_CHARACTER + | T_CHARACTER + | T_CHARSET | T_CLIENT | T_CLOSE | T_CLUSTERED | T_CMP - | T_COLLECTION + | T_COLLECTION + | T_COMMENT | T_CONSTANT | T_COPY | T_COMMIT @@ -1102,6 +1229,7 @@ non_reserved_words : // Tokens that are not reserved words | T_CURRENT_TIMESTAMP | T_CURRENT_USER | T_CURSOR + | T_DATABASE | T_DATE | T_DATETIME | T_DAY @@ -1118,8 +1246,11 @@ non_reserved_words : // Tokens that are not reserved words | T_DELIMITED | T_DELIMITER | T_DENSE_RANK - | T_DESC + | T_DESC + | T_DESCRIBE | T_DIAGNOSTICS + | T_DIR + | T_DIRECTORY | T_DISTINCT | T_DISTRIBUTE | T_DO @@ -1129,7 +1260,9 @@ non_reserved_words : // Tokens that are not reserved words // T_ELSE reserved word // T_ELSEIF reserved word // T_ELSIF reserved word - // T_END reserved word + // T_END reserved word + | T_ENABLE + | T_ENGINE | T_ESCAPED | T_EXCEPT | T_EXEC @@ -1141,14 +1274,16 @@ non_reserved_words : // Tokens that are not reserved words | T_FALSE | T_FETCH | T_FIELDS - | T_FILE + | T_FILE + | T_FILES | T_FIRST_VALUE | T_FLOAT | T_FOR | T_FOREIGN | T_FORMAT | T_FOUND - | T_FROM + | T_FROM + | T_FTP | T_FULL | T_FUNCTION | T_GET @@ -1174,8 +1309,12 @@ non_reserved_words : // Tokens that are not reserved words | T_INOUT | T_INSERT | T_INT + | T_INT2 + | T_INT4 + | T_INT8 | T_INTEGER - | T_INTERSECT + | T_INTERSECT + | T_INTERVAL | T_INTO | T_INVOKER | T_ITEMS @@ -1194,10 +1333,12 @@ non_reserved_words : // Tokens that are not reserved words | T_LIKE | T_LIMIT | T_LINES - | T_LOCAL + | T_LOCAL + | T_LOCATION | T_LOCATOR | T_LOCATORS | T_LOCKS + | T_LOG | T_LOGGED | T_LOGGING | T_LOOP @@ -1212,11 +1353,13 @@ non_reserved_words : // Tokens that are not reserved words | T_MIN | T_MULTISET | T_NCHAR + | T_NEW | T_NVARCHAR | T_NO | T_NOCOMPRESS | T_NOCOUNT | T_NOLOGGING + | T_NONE | T_NOT | T_NOTFOUND // T_NULL reserved word @@ -1245,8 +1388,11 @@ non_reserved_words : // Tokens that are not reserved words | T_PRIMARY | T_PRINT | T_PROC - | T_PROCEDURE + | T_PROCEDURE + | T_PWD + | T_QUERY_BAND | T_QUOTED_IDENTIFIER + | T_RAISE | T_RANK | T_REAL | T_REFERENCES @@ -1273,7 +1419,9 @@ non_reserved_words : // Tokens that are not reserved words | T_SECURITY | T_SEGMENT | T_SEL - | T_SELECT + | T_SELECT + | T_SESSION + | T_SESSIONS | T_SET | T_SETS | T_SHARE @@ -1290,7 +1438,8 @@ non_reserved_words : // Tokens that are not reserved words | T_STEP | T_STDEV | T_STORAGE - | T_STRING + | T_STRING + | T_SUBDIR | T_SUBSTRING | T_SUM | T_SYSDATE @@ -1305,8 +1454,10 @@ non_reserved_words : // Tokens that are not reserved words | T_TITLE | T_TO | T_TOP + | T_TRANSACTION | T_TRIM | T_TRUE + | T_TRUNCATE // T_UNION reserved word | T_UNIQUE | T_UPDATE @@ -1334,6 +1485,7 @@ non_reserved_words : // Tokens that are not reserved words // Lexer rules T_ACTION : A C T I O N ; +T_ADD2 : A D D ; T_ALL : A L L ; T_ALLOCATE : A L L O C A T E ; T_ALTER : A L T E R ; @@ -1344,6 +1496,7 @@ T_AS : A S ; T_ASC : A S C ; T_ASSOCIATE : A S S O C I A T E ; T_AT : A T ; +T_AUTO_INCREMENT : A U T O '_' I N C R E M E N T ; T_AVG : A V G ; T_BATCHSIZE : B A T C H S I Z E ; T_BEGIN : B E G I N ; @@ -1365,18 +1518,20 @@ T_CASESPECIFIC : C A S E S P E C I F I C ; T_CAST : C A S T ; T_CHAR : C H A R ; T_CHARACTER : C H A R A C T E R ; +T_CHARSET : C H A R S E T ; T_CLIENT : C L I E N T ; T_CLOSE : C L O S E ; T_CLUSTERED : C L U S T E R E D; T_CMP : C M P ; T_COLLECTION : C O L L E C T I O N ; +T_COMMENT : C O M M E N T; T_CONSTANT : C O N S T A N T ; -T_COPY : C O P Y ; T_COMMIT : C O M M I T ; T_CONCAT : C O N C A T; T_CONDITION : C O N D I T I O N ; T_CONSTRAINT : C O N S T R A I N T ; T_CONTINUE : C O N T I N U E ; +T_COPY : C O P Y ; T_COUNT : C O U N T ; T_COUNT_BIG : C O U N T '_' B I G; T_CREATE : C R E A T E ; @@ -1386,6 +1541,7 @@ T_CS : C S; T_CURRENT : C U R R E N T ; T_CURRENT_SCHEMA : C U R R E N T '_' S C H E M A ; T_CURSOR : C U R S O R ; +T_DATABASE : D A T A B A S E; T_DATE : D A T E ; T_DATETIME : D A T E T I M E ; T_DAY : D A Y ; @@ -1402,7 +1558,10 @@ T_DELETE : D E L E T E ; T_DELIMITED : D E L I M I T E D ; T_DELIMITER : D E L I M I T E R ; T_DESC : D E S C ; +T_DESCRIBE : D E S C R I B E ; T_DIAGNOSTICS : D I A G N O S T I C S ; +T_DIR : D I R ; +T_DIRECTORY : D I R E C T O R Y ; T_DISTINCT : D I S T I N C T ; T_DISTRIBUTE : D I S T R I B U T E ; T_DO : D O ; @@ -1412,7 +1571,9 @@ T_DYNAMIC : D Y N A M I C ; T_ELSE : E L S E ; T_ELSEIF : E L S E I F ; T_ELSIF : E L S I F ; +T_ENABLE : E N A B L E ; T_END : E N D ; +T_ENGINE : E N G I N E ; T_ESCAPED : E S C A P E D ; T_EXCEPT : E X C E P T ; T_EXEC : E X E C ; @@ -1425,12 +1586,14 @@ T_FALSE : F A L S E ; T_FETCH : F E T C H ; T_FIELDS : F I E L D S ; T_FILE : F I L E ; +T_FILES : F I L E S ; T_FLOAT : F L O A T ; T_FOR : F O R ; T_FOREIGN : F O R E I G N ; T_FORMAT : F O R M A T ; T_FOUND : F O U N D ; T_FROM : F R O M ; +T_FTP : F T P ; T_FULL : F U L L ; T_FUNCTION : F U N C T I O N ; T_GET : G E T ; @@ -1456,8 +1619,12 @@ T_INNER : I N N E R ; T_INOUT : I N O U T; T_INSERT : I N S E R T ; T_INT : I N T ; +T_INT2 : I N T '2'; +T_INT4 : I N T '4'; +T_INT8 : I N T '8'; T_INTEGER : I N T E G E R ; T_INTERSECT : I N T E R S E C T ; +T_INTERVAL : I N T E R V A L ; T_INTO : I N T O ; T_INVOKER : I N V O K E R ; T_IS : I S ; @@ -1474,9 +1641,11 @@ T_LIKE : L I K E ; T_LIMIT : L I M I T ; T_LINES : L I N E S ; T_LOCAL : L O C A L ; +T_LOCATION : L O C A T I O N ; T_LOCATOR : L O C A T O R ; T_LOCATORS : L O C A T O R S ; T_LOCKS : L O C K S ; +T_LOG : L O G ; T_LOGGED : L O G G E D ; T_LOGGING : L O G G I N G ; T_LOOP : L O O P ; @@ -1491,11 +1660,13 @@ T_MICROSECONDS : M I C R O S E C O N D S; T_MIN : M I N ; T_MULTISET : M U L T I S E T ; T_NCHAR : N C H A R ; +T_NEW : N E W ; T_NVARCHAR : N V A R C H A R ; T_NO : N O ; T_NOCOUNT : N O C O U N T ; T_NOCOMPRESS : N O C O M P R E S S ; T_NOLOGGING : N O L O G G I N G ; +T_NONE : N O N E ; T_NOT : N O T ; T_NOTFOUND : N O T F O U N D ; T_NULL : N U L L ; @@ -1524,7 +1695,9 @@ T_PRIMARY : P R I M A R Y ; T_PRINT : P R I N T ; T_PROC : P R O C ; T_PROCEDURE : P R O C E D U R E; +T_QUERY_BAND : Q U E R Y '_' B A N D ; T_QUOTED_IDENTIFIER : Q U O T E D '_' I D E N T I F I E R ; +T_RAISE : R A I S E ; T_REAL : R E A L ; T_REFERENCES : R E F E R E N C E S ; T_REGEXP : R E G E X P ; @@ -1546,6 +1719,7 @@ T_ROWTYPE : R O W T Y P E ; T_ROW_COUNT : R O W '_' C O U N T ; T_RR : R R; T_RS : R S ; +T_PWD : P W D ; T_TRIM : T R I M ; T_SCHEMA : S C H E M A ; T_SECURITY : S E C U R I T Y ; @@ -1553,6 +1727,8 @@ T_SEGMENT : S E G M E N T ; T_SEL : S E L ; T_SELECT : S E L E C T ; T_SET : S E T ; +T_SESSION : S E S S I O N ; +T_SESSIONS : S E S S I O N S ; T_SETS : S E T S; T_SHARE : S H A R E ; T_SIGNAL : S I G N A L ; @@ -1569,6 +1745,7 @@ T_SQLWARNING : S Q L W A R N I N G ; T_STEP : S T E P ; T_STORAGE : S T O R A G E ; T_STRING : S T R I N G ; +T_SUBDIR : S U B D I R ; T_SUBSTRING : S U B S T R I N G ; T_SUM : S U M ; T_SYS_REFCURSOR : S Y S '_' R E F C U R S O R ; @@ -1583,7 +1760,9 @@ T_TINYINT : T I N Y I N T ; T_TITLE : T I T L E ; T_TO : T O ; T_TOP : T O P ; +T_TRANSACTION : T R A N S A C T I O N ; T_TRUE : T R U E ; +T_TRUNCATE : T R U N C A T E; T_TYPE : T Y P E ; T_UNION : U N I O N ; T_UNIQUE : U N I Q U E ; @@ -1657,13 +1836,6 @@ T_CLOSE_SB : ']' ; T_SEMICOLON : ';' ; T_SUB : '-' ; -P_e : '-e' ; -P_f : '-f' ; -P_hiveconf : '-hiveconf' ; -P_i : '-i' ; -P_S : '-S' ; -P_h : '-h' ; - L_ID : L_ID_PART (L_BLANK* '.' L_BLANK* L_ID_PART)* // Identifier ; L_S_STRING : '\'' (('\'' '\'') | ('\\' '\'') | ~('\''))* '\'' // Single quoted string literal http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Conf.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Conf.java b/hplsql/src/main/java/org/apache/hive/hplsql/Conf.java index 88afbb5..c78dda4 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Conf.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Conf.java @@ -57,7 +57,7 @@ public class Conf extends Configuration { InsertValues insertValues = InsertValues.NATIVE; TempTables tempTables = TempTables.NATIVE; - String dualTable = "default.dual"; + String dualTable = null; String tempTablesSchema = ""; String tempTablesLocation = "/tmp/hplsql"; http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java b/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java index a0e0958..8599536 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java @@ -110,6 +110,7 @@ public class Conn { if (stmt.execute(sql)) { rs = stmt.getResultSet(); } + timer.stop(); query.set(conn, stmt, rs); if (info) { exec.info(null, "SQL statement executed successfully (" + timer.format() + ")"); @@ -205,7 +206,7 @@ public class Conn { } Class.forName(driver); timer.start(); - Connection conn = DriverManager.getConnection(url.toString(), usr, pwd); + Connection conn = DriverManager.getConnection(url.toString().trim(), usr, pwd); timer.stop(); if (info) { exec.info(null, "Open connection: " + url + " (" + timer.format() + ")"); http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Converter.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Converter.java b/hplsql/src/main/java/org/apache/hive/hplsql/Converter.java index 9d8c77f..e19bcfc 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Converter.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Converter.java @@ -44,13 +44,26 @@ public class Converter { if (t.equalsIgnoreCase("BIT")) { t = "TINYINT"; } + else if (t.equalsIgnoreCase("INT") || t.equalsIgnoreCase("INTEGER")) { + // MySQL can use INT(n) + } + else if (t.equalsIgnoreCase("INT2")) { + t = "SMALLINT"; + } + else if (t.equalsIgnoreCase("INT4")) { + t = "INT"; + } + else if (t.equalsIgnoreCase("INT8")) { + t = "BIGINT"; + } else if (t.equalsIgnoreCase("DATETIME") || t.equalsIgnoreCase("SMALLDATETIME")) { t = "TIMESTAMP"; } else if ((t.equalsIgnoreCase("VARCHAR") || t.equalsIgnoreCase("NVARCHAR")) && len.T_MAX() != null) { t = "STRING"; } - else if (t.equalsIgnoreCase("VARCHAR2") || t.equalsIgnoreCase("NCHAR") || t.equalsIgnoreCase("NVARCHAR")) { + else if (t.equalsIgnoreCase("VARCHAR2") || t.equalsIgnoreCase("NCHAR") || t.equalsIgnoreCase("NVARCHAR") || + t.equalsIgnoreCase("TEXT")) { t = "STRING"; } else if (t.equalsIgnoreCase("NUMBER") || t.equalsIgnoreCase("NUMERIC")) { http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java index 84193da..02605a8 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java @@ -50,7 +50,7 @@ import org.apache.hive.hplsql.functions.*; */ public class Exec extends HplsqlBaseVisitor<Integer> { - public static final String VERSION = "HPL/SQL 0.3.13"; + public static final String VERSION = "HPL/SQL 0.3.17"; public static final String SQLCODE = "SQLCODE"; public static final String SQLSTATE = "SQLSTATE"; public static final String HOSTCODE = "HOSTCODE"; @@ -783,11 +783,9 @@ public class Exec extends HplsqlBaseVisitor<Integer> { new FunctionMisc(this).register(function); new FunctionString(this).register(function); new FunctionOra(this).register(function); - addVariable(new Var(SQLCODE, Var.Type.BIGINT, 0L)); addVariable(new Var(SQLSTATE, Var.Type.STRING, "00000")); addVariable(new Var(HOSTCODE, Var.Type.BIGINT, 0L)); - for (Map.Entry<String, String> v : arguments.getVars().entrySet()) { addVariable(new Var(v.getKey(), Var.Type.STRING, v.getValue())); } @@ -826,7 +824,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } execString = arguments.getExecString(); execFile = arguments.getFileName(); - execMain = arguments.getMain(); + execMain = arguments.getMain(); if (arguments.hasTraceOption()) { trace = true; } @@ -1066,7 +1064,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> { public Integer visitTable_name(HplsqlParser.Table_nameContext ctx) { String name = ctx.getText(); String nameUp = name.toUpperCase(); - String nameNorm = meta.normalizeIdentifier(name); + String nameNorm = meta.normalizeObjectIdentifier(name); String actualName = exec.managedTables.get(nameUp); String conn = exec.objectConnMap.get(nameUp); if (conn == null) { @@ -1093,6 +1091,14 @@ public class Exec extends HplsqlBaseVisitor<Integer> { public Integer visitInsert_stmt(HplsqlParser.Insert_stmtContext ctx) { return exec.stmt.insert(ctx); } + + /** + * INSERT DIRECTORY statement + */ + @Override + public Integer visitInsert_directory_stmt(HplsqlParser.Insert_directory_stmtContext ctx) { + return exec.stmt.insertDirectory(ctx); + } /** * EXCEPTION block @@ -1214,6 +1220,14 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } /** + * DESCRIBE statement + */ + @Override + public Integer visitDescribe_stmt(HplsqlParser.Describe_stmtContext ctx) { + return exec.stmt.describe(ctx); + } + + /** * DROP statement */ @Override @@ -1262,6 +1276,14 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } /** + * COPY FROM FTP statement + */ + @Override + public Integer visitCopy_from_ftp_stmt(HplsqlParser.Copy_from_ftp_stmtContext ctx) { + return new Ftp(exec).run(ctx); + } + + /** * COPY FROM LOCAL statement */ @Override @@ -1331,6 +1353,11 @@ public class Exec extends HplsqlBaseVisitor<Integer> { return 0; } + @Override + public Integer visitCreate_table_options_mysql_item(HplsqlParser.Create_table_options_mysql_itemContext ctx) { + return exec.stmt.createTableMysqlOptions(ctx); + } + /** * CREATE LOCAL TEMPORARY | VOLATILE TABLE statement */ @@ -1340,6 +1367,22 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } /** + * ALTER TABLE statement + */ + @Override + public Integer visitAlter_table_stmt(HplsqlParser.Alter_table_stmtContext ctx) { + return 0; + } + + /** + * CREATE DATABASE | SCHEMA statement + */ + @Override + public Integer visitCreate_database_stmt(HplsqlParser.Create_database_stmtContext ctx) { + return exec.stmt.createDatabase(ctx); + } + + /** * CREATE FUNCTION statement */ @Override @@ -1520,6 +1563,34 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } return 0; } + + /** + * Static SELECT statement (i.e. unquoted) or expression + */ + @Override + public Integer visitExpr_select(HplsqlParser.Expr_selectContext ctx) { + if (ctx.select_stmt() != null) { + stackPush(new Var(evalPop(ctx.select_stmt()))); + } + else { + visit(ctx.expr()); + } + return 0; + } + + /** + * File path (unquoted) or expression + */ + @Override + public Integer visitExpr_file(HplsqlParser.Expr_fileContext ctx) { + if (ctx.file_name() != null) { + stackPush(new Var(ctx.file_name().getText())); + } + else { + visit(ctx.expr()); + } + return 0; + } /** * Cursor attribute %ISOPEN, %FOUND and %NOTFOUND @@ -1677,7 +1748,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> { executed = packCallContext.execProc(name, ctx.expr_func_params(), false /*trace error if not exists*/); } if (!executed) { - exec.function.execProc(name, ctx.expr_func_params()); + exec.function.execProc(name, ctx.expr_func_params(), ctx); } exec.inCallStmt = false; return 0; @@ -1748,6 +1819,14 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } /** + * TRUNCATE statement + */ + @Override + public Integer visitTruncate_stmt(HplsqlParser.Truncate_stmtContext ctx) { + return exec.stmt.truncate(ctx); + } + + /** * MAP OBJECT statement */ @Override @@ -1845,15 +1924,16 @@ public class Exec extends HplsqlBaseVisitor<Integer> { public Integer visitHive_item(HplsqlParser.Hive_itemContext ctx) { Var params = stackPeek(); ArrayList<String> a = (ArrayList<String>)params.value; - if(ctx.P_e() != null) { + String param = ctx.getChild(1).getText(); + if (param.equals("e")) { a.add("-e"); a.add(evalPop(ctx.expr()).toString()); } - else if(ctx.P_f() != null) { + else if (param.equals("f")) { a.add("-f"); a.add(evalPop(ctx.expr()).toString()); } - else if(ctx.P_hiveconf() != null) { + else if (param.equals("hiveconf")) { a.add("-hiveconf"); a.add(ctx.L_ID().toString() + "=" + evalPop(ctx.expr()).toString()); } @@ -2007,7 +2087,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } } else { - if (!exec.buildSql && !exec.inCallStmt && exec.function.isProc(ident) && exec.function.execProc(ident, null)) { + if (!exec.buildSql && !exec.inCallStmt && exec.function.isProc(ident) && exec.function.execProc(ident, null, ctx)) { return 0; } else { @@ -2041,11 +2121,11 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } /** - * Interval number (1 DAYS i.e) + * Interval expression (INTERVAL '1' DAY i.e) */ @Override - public Integer visitInterval_number(HplsqlParser.Interval_numberContext ctx) { - int num = evalPop(ctx.int_number()).intValue(); + public Integer visitExpr_interval(HplsqlParser.Expr_intervalContext ctx) { + int num = evalPop(ctx.expr()).intValue(); Interval interval = new Interval().set(num, ctx.interval_item().getText()); stackPush(new Var(interval)); return 0; http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java b/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java index 7c500a8..33ef490 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java @@ -77,6 +77,16 @@ public class Expression { sql.append(evalPop(ctx.expr(0)).toString()); sql.append(")"); } + else if (ctx.T_MUL() != null) { + sql.append(evalPop(ctx.expr(0)).toString()); + sql.append(" * "); + sql.append(evalPop(ctx.expr(1)).toString()); + } + else if (ctx.T_DIV() != null) { + sql.append(evalPop(ctx.expr(0)).toString()); + sql.append(" / "); + sql.append(evalPop(ctx.expr(1)).toString()); + } else if (ctx.T_ADD() != null) { sql.append(evalPop(ctx.expr(0)).toString()); sql.append(" + "); @@ -325,6 +335,9 @@ public class Expression { else if (v1.type == Type.DATE && v2.type == Type.BIGINT) { exec.stackPush(changeDateByInt((Date)v1.value, (Long)v2.value, true /*add*/)); } + else if (v1.type == Type.STRING && v2.type == Type.STRING) { + exec.stackPush(((String)v1.value) + ((String)v2.value)); + } else if (v1.type == Type.DATE && v2.type == Type.INTERVAL) { exec.stackPush(new Var(((Interval)v2.value).dateChange((Date)v1.value, true /*add*/))); } http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/File.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/File.java b/hplsql/src/main/java/org/apache/hive/hplsql/File.java index e748772..d18c604 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/File.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/File.java @@ -83,6 +83,17 @@ public class File { e.printStackTrace(); } } + + /** + * Check if the directory or file exists + * @throws IOException + */ + boolean exists(String name) throws IOException { + if (fs == null) { + fs = createFs(); + } + return fs.exists(new Path(name)); + } /** * Read a character from input http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Ftp.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Ftp.java b/hplsql/src/main/java/org/apache/hive/hplsql/Ftp.java new file mode 100644 index 0000000..1f2fc5c --- /dev/null +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Ftp.java @@ -0,0 +1,415 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.hive.hplsql; + +import java.io.FileOutputStream; +import java.io.IOException; +import java.io.InputStream; +import java.io.OutputStream; +import java.util.ArrayList; +import java.util.Hashtable; +import java.util.concurrent.ConcurrentLinkedQueue; +import java.util.regex.Pattern; +import java.util.concurrent.atomic.AtomicInteger; +import java.util.concurrent.atomic.AtomicLong; + +import org.apache.commons.net.ftp.FTP; +import org.apache.commons.net.ftp.FTPClient; +import org.apache.commons.net.ftp.FTPFile; +import org.antlr.v4.runtime.ParserRuleContext; + +public class Ftp implements Runnable { + String host; + String user; + String pwd; + String dir; + String targetDir; + String filePattern; + boolean subdir = false; + boolean local = false; + boolean newOnly = false; + int sessions = 1; + + int fileCnt = 0; + int dirCnt = 0; + long ftpSizeInBytes = 0; + + FTPClient ftp = null; + ConcurrentLinkedQueue<String> filesQueue = new ConcurrentLinkedQueue<String>(); + Hashtable<String, FTPFile> filesMap = new Hashtable<String, FTPFile>(); + + AtomicInteger currentFileCnt = new AtomicInteger(1); + AtomicInteger currentThreadCnt = new AtomicInteger(0); + AtomicInteger fileCntSuccess = new AtomicInteger(0); + AtomicLong bytesTransferredAll = new AtomicLong(0); + + Exec exec; + boolean trace = false; + boolean info = false; + + Ftp(Exec e) { + exec = e; + trace = exec.getTrace(); + info = exec.getInfo(); + } + + /** + * Run COPY FROM FTP command + */ + Integer run(HplsqlParser.Copy_from_ftp_stmtContext ctx) { + trace(ctx, "COPY FROM FTP"); + initOptions(ctx); + ftp = openConnection(ctx); + if (ftp != null) { + Timer timer = new Timer(); + long start = timer.start(); + if (info) { + info(ctx, "Retrieving directory listing"); + } + retrieveFileList(dir); + long elapsed = timer.stop(); + if (info) { + info(ctx, "Files to copy: " + Utils.formatSizeInBytes(ftpSizeInBytes) + ", " + Utils.formatCnt(fileCnt, "file") + ", " + Utils.formatCnt(dirCnt, "subdirectory", "subdirectories") + " scanned (" + timer.format() + ")"); + } + if (fileCnt > 0) { + copyFiles(ctx); + } + } + return 0; + } + + /** + * Copy the specified files from FTP + */ + void copyFiles(HplsqlParser.Copy_from_ftp_stmtContext ctx) { + Timer timer = new Timer(); + long start = timer.start(); + if (fileCnt > 1 && sessions > 1) { + if (sessions > fileCnt) { + sessions = fileCnt; + } + try { + Thread threads[] = new Thread[sessions]; + for (int i = 0; i < sessions; i++) { + threads[i] = new Thread(this); + threads[i].start(); + } + for (int i = 0; i < sessions; i++) { + threads[i].join(); + } + } + catch(Exception e) { + } + } + else { // Transfer files in the single session + run(); + } + if (info) { + long elapsed = timer.stop(); + long bytesAll = bytesTransferredAll.get(); + info(ctx, "Transfer complete: " + Utils.formatSizeInBytes(bytesAll) + ", " + fileCntSuccess.get() + " files ok, " + (fileCnt - fileCntSuccess.get()) + " failed, "+ Utils.formatTime(elapsed) + ", " + Utils.formatBytesPerSec(bytesAll, elapsed)); + } + } + + /** + * Run a thread to transfer files + */ + public void run() { + byte[] data = null; + Timer timer = new Timer(); + FTPClient ftp = this.ftp; + if (currentThreadCnt.getAndIncrement() > 0) { + ftp = openConnection(null); + } + while(true) { + String file = filesQueue.poll(); + if (file == null) { + break; + } + int num = currentFileCnt.getAndIncrement(); + FTPFile ftpFile = filesMap.get(file); + long ftpSizeInBytes = ftpFile.getSize(); + String fmtSizeInBytes = Utils.formatSizeInBytes(ftpSizeInBytes); + String targetFile = getTargetFileName(file); + if (info) { + info(null, " " + file + " - started (" + num + " of " + fileCnt + ", " + fmtSizeInBytes +")"); + } + try { + InputStream in = ftp.retrieveFileStream(file); + OutputStream out = null; + java.io.File targetLocalFile = null; + File targetHdfsFile = null; + if (local) { + targetLocalFile = new java.io.File(targetFile); + if (!targetLocalFile.exists()) { + targetLocalFile.getParentFile().mkdirs(); + targetLocalFile.createNewFile(); + } + out = new FileOutputStream(targetLocalFile, false /*append*/); + } + else { + targetHdfsFile = new File(); + out = targetHdfsFile.create(targetFile, true /*overwrite*/); + } + if (data == null) { + data = new byte[3*1024*1024]; + } + int bytesRead = -1; + long bytesReadAll = 0; + long start = timer.start(); + long prev = start; + long readTime = 0; + long writeTime = 0; + long cur, cur2, cur3; + while (true) { + cur = timer.current(); + bytesRead = in.read(data); + cur2 = timer.current(); + readTime += (cur2 - cur); + if (bytesRead == -1) { + break; + } + out.write(data, 0, bytesRead); + out.flush(); + cur3 = timer.current(); + writeTime += (cur3 - cur2); + bytesReadAll += bytesRead; + if (info) { + cur = timer.current(); + if (cur - prev > 13000) { + long elapsed = cur - start; + info(null, " " + file + " - in progress (" + Utils.formatSizeInBytes(bytesReadAll) + " of " + fmtSizeInBytes + ", " + Utils.formatPercent(bytesReadAll, ftpSizeInBytes) + ", " + Utils.formatTime(elapsed) + ", " + Utils.formatBytesPerSec(bytesReadAll, elapsed) + ", " + Utils.formatBytesPerSec(bytesReadAll, readTime) + " read, " + Utils.formatBytesPerSec(bytesReadAll, writeTime) + " write)"); + prev = cur; + } + } + } + if (ftp.completePendingCommand()) { + in.close(); + cur = timer.current(); + out.close(); + readTime += (timer.current() - cur); + bytesTransferredAll.addAndGet(bytesReadAll); + fileCntSuccess.incrementAndGet(); + if (info) { + long elapsed = timer.stop(); + info(null, " " + file + " - complete (" + Utils.formatSizeInBytes(bytesReadAll) + ", " + Utils.formatTime(elapsed) + ", " + Utils.formatBytesPerSec(bytesReadAll, elapsed) + ", " + Utils.formatBytesPerSec(bytesReadAll, readTime) + " read, " + Utils.formatBytesPerSec(bytesReadAll, writeTime) + " write)"); + } + } + else { + in.close(); + out.close(); + if (info) { + info(null, " " + file + " - failed"); + } + exec.signal(Signal.Type.SQLEXCEPTION, "File transfer failed: " + file); + } + } + catch(IOException e) { + exec.signal(e); + } + } + try { + if (ftp.isConnected()) { + ftp.logout(); + ftp.disconnect(); + } + } + catch (IOException e) { + } + } + + /** + * Get the list of files to transfer + */ + void retrieveFileList(String dir) { + if (info) { + if (dir == null || dir.isEmpty()) { + info(null, " Listing the current working FTP directory"); + } + else { + info(null, " Listing " + dir); + } + } + try { + FTPFile[] files = ftp.listFiles(dir); + ArrayList<FTPFile> dirs = new ArrayList<FTPFile>(); + for (FTPFile file : files) { + String name = file.getName(); + if (file.isFile()) { + if (filePattern == null || Pattern.matches(filePattern, name)) { + if (dir != null && !dir.isEmpty()) { + name = dir + "/" + name; + } + if (!newOnly || !isTargetExists(name)) { + fileCnt++; + ftpSizeInBytes += file.getSize(); + filesQueue.add(name); + filesMap.put(name, file); + } + } + } + else { + if (subdir && !name.equals(".") && !name.equals("..")) { + dirCnt++; + dirs.add(file); + } + } + } + if (subdir) { + for (FTPFile d : dirs) { + String sd = d.getName(); + if (dir != null && !dir.isEmpty()) { + sd = dir + "/" + sd; + } + retrieveFileList(sd); + } + } + } + catch (IOException e) { + exec.signal(e); + } + } + + /** + * Open and initialize FTP + */ + FTPClient openConnection(HplsqlParser.Copy_from_ftp_stmtContext ctx) { + FTPClient ftp = new FTPClient(); + Timer timer = new Timer(); + long start = timer.start(); + try { + ftp.connect(host); + ftp.enterLocalPassiveMode(); + ftp.setFileType(FTP.BINARY_FILE_TYPE); + if (!ftp.login(user, pwd)) { + if (ftp.isConnected()) { + ftp.disconnect(); + } + exec.signal(Signal.Type.SQLEXCEPTION, "Cannot login to FTP server: " + host); + return null; + } + long elapsed = timer.stop(); + if (info) { + info(ctx, "Connected to ftp: " + host + " (" + timer.format() + ")"); + } + } + catch (IOException e) { + exec.signal(e); + } + return ftp; + } + + /** + * Check if the file already exists in the target file system + */ + boolean isTargetExists(String name) { + String target = getTargetFileName(name); + try { + if (local) { + if (new java.io.File(target).exists()) { + return true; + } + } + else if (new File().exists(target)) { + return true; + } + } + catch(Exception e) { + } + return false; + } + + /** + * Get the target file relative path and name + */ + String getTargetFileName(String file) { + int len = dir.length(); + return targetDir + file.substring(len); + } + + /** + * Initialize COPY FROM FTP command options + */ + void initOptions(HplsqlParser.Copy_from_ftp_stmtContext ctx) { + host = evalPop(ctx.expr()).toString(); + int cnt = ctx.copy_ftp_option().size(); + for (int i = 0; i < cnt; i++) { + HplsqlParser.Copy_ftp_optionContext option = ctx.copy_ftp_option(i); + if (option.T_USER() != null) { + user = evalPop(option.expr()).toString(); + } + else if (option.T_PWD() != null) { + pwd = evalPop(option.expr()).toString(); + } + else if (option.T_DIR() != null) { + if (option.file_name() != null) { + dir = option.file_name().getText(); + } + else { + dir = evalPop(option.expr()).toString(); + } + } + else if (option.T_FILES() != null) { + filePattern = evalPop(option.expr()).toString(); + } + else if (option.T_NEW() != null) { + newOnly = true; + } + else if (option.T_SUBDIR() != null) { + subdir = true; + } + else if (option.T_SESSIONS() != null) { + sessions = evalPop(option.expr()).intValue(); + } + else if (option.T_TO() != null) { + if (option.file_name() != null) { + targetDir = option.file_name().getText(); + } + else { + targetDir = evalPop(option.expr()).toString(); + } + if (option.T_LOCAL() != null) { + local = true; + } + } + } + } + + /** + * Evaluate the expression and pop value from the stack + */ + Var evalPop(ParserRuleContext ctx) { + exec.visit(ctx); + if (!exec.stack.isEmpty()) { + return exec.stackPop(); + } + return Var.Empty; + } + + /** + * Trace and information + */ + public void trace(ParserRuleContext ctx, String message) { + exec.trace(ctx, message); + } + + public void info(ParserRuleContext ctx, String message) { + exec.info(ctx, message); + } +} http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java b/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java index 2e04ef9..46bd55a 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java @@ -139,15 +139,26 @@ public class Meta { } /** - * Normalize identifier name (convert "" [] to `` i.e.) + * Normalize identifier for a database object (convert "" [] to `` i.e.) */ - public String normalizeIdentifier(String name) { + public String normalizeObjectIdentifier(String name) { ArrayList<String> parts = splitIdentifier(name); - if (parts != null) { + if (parts != null) { // more then one part exist StringBuilder norm = new StringBuilder(); - for (int i = 0; i < parts.size(); i++) { - norm.append(normalizeIdentifierPart(parts.get(i))); - if (i + 1 < parts.size()) { + int size = parts.size(); + boolean appended = false; + for (int i = 0; i < size; i++) { + if (i == size - 2) { // schema name + String schema = getTargetSchemaName(parts.get(i)); + if (schema != null) { + norm.append(schema); + appended = true; + } + } else { + norm.append(normalizeIdentifierPart(parts.get(i))); + appended = true; + } + if (i + 1 < parts.size() && appended) { norm.append("."); } } @@ -157,7 +168,17 @@ public class Meta { } /** - * Normalize identifier (single part) + * Get the schema name to be used in the final executed SQL + */ + String getTargetSchemaName(String name) { + if (name.equalsIgnoreCase("dbo") || name.equalsIgnoreCase("[dbo]")) { + return null; + } + return normalizeIdentifierPart(name); + } + + /** + * Normalize identifier (single part) - convert "" [] to `` i.e. */ public String normalizeIdentifierPart(String name) { char start = name.charAt(0); http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Package.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Package.java b/hplsql/src/main/java/org/apache/hive/hplsql/Package.java index 84fa94a..15be59c 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Package.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Package.java @@ -150,6 +150,9 @@ public class Package { HashMap<String, Var> out = new HashMap<String, Var>(); exec.enterScope(Scope.Type.ROUTINE, this); exec.callStackPush(name); + if (p.declare_block_inplace() != null) { + visit(p.declare_block_inplace()); + } if (p.create_routine_params() != null) { function.setCallParameters(ctx, actualParams, p.create_routine_params(), out); } http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Row.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Row.java b/hplsql/src/main/java/org/apache/hive/hplsql/Row.java index deeacaf..91392c7 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Row.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Row.java @@ -46,14 +46,14 @@ public class Row { void addColumn(String name, String type) { Column column = new Column(name, type); columns.add(column); - columnMap.put(name, column); + columnMap.put(name.toUpperCase(), column); } /** * Get the data type by column name */ String getType(String name) { - Column column = columnMap.get(name); + Column column = columnMap.get(name.toUpperCase()); if (column != null) { return column.getType(); } http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Select.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Select.java b/hplsql/src/main/java/org/apache/hive/hplsql/Select.java index 56fbb05..4bee252 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Select.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Select.java @@ -202,7 +202,7 @@ public class Select { sql.append(" " + evalPop(ctx.select_list())); if (ctx.from_clause() != null) { sql.append(" " + evalPop(ctx.from_clause())); - } else { + } else if (conf.dualTable != null) { sql.append(" FROM " + conf.dualTable); } if (ctx.where_clause() != null) { @@ -342,7 +342,9 @@ public class Select { sql.append(", "); } } - sql.append(" FROM " + conf.dualTable); + if (conf.dualTable != null) { + sql.append(" FROM " + conf.dualTable); + } if (i + 1 < rows) { sql.append("\nUNION ALL\n"); } http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java index e6ac196..d35f994 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java @@ -191,6 +191,17 @@ public class Stmt { evalString(sql); return 0; } + + /** + * CREATE TABLE options for MySQL + */ + public Integer createTableMysqlOptions(HplsqlParser.Create_table_options_mysql_itemContext ctx) { + StringBuilder sql = new StringBuilder(); + if (ctx.T_COMMENT() != null) { + evalString(ctx.T_COMMENT().getText() + " " + evalPop(ctx.expr()).toSqlString()); + } + return 0; + } /** * DECLARE TEMPORARY TABLE statement @@ -204,6 +215,44 @@ public class Stmt { } /** + * CREATE DATABASE | SCHEMA statement + */ + public Integer createDatabase(HplsqlParser.Create_database_stmtContext ctx) { + trace(ctx, "CREATE DATABASE"); + StringBuilder sql = new StringBuilder(); + sql.append(ctx.T_CREATE().getText() + " "); + if (ctx.T_DATABASE() != null) { + sql.append(ctx.T_DATABASE().getText() + " "); + } + else { + sql.append(ctx.T_SCHEMA().getText() + " "); + } + if (ctx.T_IF() != null) { + sql.append(exec.getText(ctx, ctx.T_IF().getSymbol(), ctx.T_EXISTS().getSymbol()) + " "); + } + sql.append(evalPop(ctx.expr()).toString()); + int cnt = ctx.create_database_option().size(); + for (int i = 0; i < cnt; i++) { + HplsqlParser.Create_database_optionContext option = ctx.create_database_option(i); + if (option.T_COMMENT() != null) { + sql.append(" " + option.T_COMMENT().getText() + " " + evalPop(option.expr()).toSqlString()); + } + else if (option.T_LOCATION() != null) { + sql.append(" " + option.T_LOCATION().getText() + " " + evalPop(option.expr()).toSqlString()); + } + } + trace(ctx, sql.toString()); + Query query = exec.executeSql(ctx, sql.toString(), exec.conf.defaultConnection); + if (query.error()) { + exec.signal(query); + return 1; + } + exec.setSqlSuccess(); + exec.closeQuery(query, exec.conf.defaultConnection); + return 0; + } + + /** * CREATE LOCAL TEMPORARY | VOLATILE TABLE statement */ public Integer createLocalTemporaryTable(HplsqlParser.Create_local_temp_table_stmtContext ctx) { @@ -252,6 +301,45 @@ public class Stmt { } /** + * DESCRIBE statement + */ + public Integer describe(HplsqlParser.Describe_stmtContext ctx) { + trace(ctx, "DESCRIBE"); + String sql = "DESCRIBE " + evalPop(ctx.table_name()).toString(); + trace(ctx, sql); + Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection); + if (query.error()) { + exec.signal(query); + return 1; + } + try { + ResultSet rs = query.getResultSet(); + ResultSetMetaData rm = null; + if (rs != null) { + rm = rs.getMetaData(); + int cols = rm.getColumnCount(); + while (rs.next()) { + for (int i = 1; i <= cols; i++) { + if (i > 1) { + System.out.print("\t"); + } + System.out.print(rs.getString(i)); + } + System.out.println(""); + } + } + } + catch (SQLException e) { + exec.signal(query); + exec.closeQuery(query, exec.conf.defaultConnection); + return 1; + } + exec.setSqlSuccess(); + exec.closeQuery(query, exec.conf.defaultConnection); + return 0; + } + + /** * DROP statement */ public Integer drop(HplsqlParser.Drop_stmtContext ctx) { @@ -260,10 +348,17 @@ public class Stmt { if (ctx.T_TABLE() != null) { sql = "DROP TABLE "; if (ctx.T_EXISTS() != null) { - sql += "IF NOT EXISTS "; + sql += "IF EXISTS "; } sql += evalPop(ctx.table_name()).toString(); } + else if (ctx.T_DATABASE() != null || ctx.T_SCHEMA() != null) { + sql = "DROP DATABASE "; + if (ctx.T_EXISTS() != null) { + sql += "IF EXISTS "; + } + sql += evalPop(ctx.expr()).toString(); + } if (sql != null) { trace(ctx, sql); Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection); @@ -278,6 +373,23 @@ public class Stmt { } /** + * TRUNCATE statement + */ + public Integer truncate(HplsqlParser.Truncate_stmtContext ctx) { + trace(ctx, "TRUNCATE"); + String sql = "TRUNCATE TABLE " + evalPop(ctx.table_name()).toString(); + trace(ctx, sql); + Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection); + if (query.error()) { + exec.signal(query); + return 1; + } + exec.setSqlSuccess(); + exec.closeQuery(query, exec.conf.defaultConnection); + return 0; + } + + /** * OPEN cursor statement */ public Integer open(HplsqlParser.Open_stmtContext ctx) { @@ -563,11 +675,21 @@ public class Stmt { */ public Integer insertSelect(HplsqlParser.Insert_stmtContext ctx) { trace(ctx, "INSERT SELECT"); - String table = evalPop(ctx.table_name()).toString(); - String select = evalPop(ctx.select_stmt()).toString(); - String sql = "INSERT INTO TABLE " + table + " " + select; - trace(ctx, sql); - Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection); + StringBuilder sql = new StringBuilder(); + sql.append(ctx.T_INSERT().getText() + " "); + if (ctx.T_OVERWRITE() != null) { + sql.append(ctx.T_OVERWRITE().getText() + " " + ctx.T_TABLE().getText() + " "); + } + else { + sql.append(ctx.T_INTO().getText() + " "); + if (ctx.T_TABLE() != null) { + sql.append(ctx.T_TABLE().getText() + " "); + } + } + sql.append(evalPop(ctx.table_name()).toString() + " "); + sql.append(evalPop(ctx.select_stmt()).toString()); + trace(ctx, sql.toString()); + Query query = exec.executeSql(ctx, sql.toString(), exec.conf.defaultConnection); if (query.error()) { exec.signal(query); return 1; @@ -621,7 +743,9 @@ public class Stmt { } } else if (type == Conn.Type.HIVE && conf.insertValues == Conf.InsertValues.SELECT) { - sql.append(" FROM " + conf.dualTable); + if (conf.dualTable != null) { + sql.append(" FROM " + conf.dualTable); + } if (i + 1 < rows) { sql.append("\nUNION ALL\n"); } @@ -641,6 +765,30 @@ public class Stmt { } /** + * INSERT DIRECTORY statement + */ + public Integer insertDirectory(HplsqlParser.Insert_directory_stmtContext ctx) { + trace(ctx, "INSERT DIRECTORY"); + StringBuilder sql = new StringBuilder(); + sql.append(ctx.T_INSERT().getText() + " " + ctx.T_OVERWRITE().getText() + " "); + if (ctx.T_LOCAL() != null) { + sql.append(ctx.T_LOCAL().getText() + " "); + } + sql.append(ctx.T_DIRECTORY().getText() + " " + evalPop(ctx.expr_file()).toSqlString() + " "); + sql.append(evalPop(ctx.expr_select()).toString()); + String conn = exec.getStatementConnection(); + trace(ctx, sql.toString()); + Query query = exec.executeSql(ctx, sql.toString(), conn); + if (query.error()) { + exec.signal(query); + return 1; + } + exec.setSqlSuccess(); + exec.closeQuery(query, conn); + return 0; + } + + /** * GET DIAGNOSTICS EXCEPTION statement */ public Integer getDiagnosticsException(HplsqlParser.Get_diag_stmt_exception_itemContext ctx) { @@ -876,7 +1024,7 @@ public class Stmt { public Boolean execProc(HplsqlParser.Exec_stmtContext ctx) { String name = evalPop(ctx.expr()).toString(); if (exec.function.isProc(name)) { - if (exec.function.execProc(name, ctx.expr_func_params())) { + if (exec.function.execProc(name, ctx.expr_func_params(), ctx)) { return true; } } @@ -997,8 +1145,7 @@ public class Stmt { public Integer print(HplsqlParser.Print_stmtContext ctx) { trace(ctx, "PRINT"); if (ctx.expr() != null) { - visit(ctx.expr()); - System.out.println(stack.pop().toString()); + System.out.println(evalPop(ctx.expr()).toString()); } return 0; } http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java b/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java index 1815deb..fb60b22 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java @@ -280,14 +280,54 @@ public class Utils { } /** + * Format elasped time + */ + public static String formatTime(long msElapsed) { + if (msElapsed < 60000) { + return msElapsed/1000 + " sec"; + } + else if (msElapsed < 60000 * 60) { + return msElapsed/60000 + " min " + (msElapsed%60000)/1000 + " sec"; + } + return ""; + } + + /** * Format bytes per second rate */ public static String formatBytesPerSec(long bytes, long msElapsed) { + if (msElapsed < 30) { + return "n/a"; + } float bytesPerSec = ((float)bytes)/msElapsed*1000; return Utils.formatSizeInBytes((long)bytesPerSec, "/sec"); } /** + * Format percentage + */ + public static String formatPercent(long current, long all) { + return String.format("%.1f", ((float)current)/all*100) + "%"; + } + + /** + * Format count + */ + public static String formatCnt(long value, String suffix) { + if (value == 1) { + return value + " " + suffix; + } + return value + " " + suffix + "s"; + } + + public static String formatCnt(long value, String suffix, String suffix2) { + if (value == 1) { + return value + " " + suffix; + } + return value + " " + suffix2; + } + + /** * Note. This stub is to resolve name conflict with ANTLR generated source using org.antlr.v4.runtime.misc.Utils.join */ static <T> String join(T[] array, String separator) { http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/Var.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Var.java b/hplsql/src/main/java/org/apache/hive/hplsql/Var.java index 5f7b355..63a1f43 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Var.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Var.java @@ -311,11 +311,12 @@ public class Var { else if (type.equalsIgnoreCase("INT") || type.equalsIgnoreCase("INTEGER") || type.equalsIgnoreCase("BIGINT") || type.equalsIgnoreCase("SMALLINT") || type.equalsIgnoreCase("TINYINT") || type.equalsIgnoreCase("BINARY_INTEGER") || type.equalsIgnoreCase("PLS_INTEGER") || - type.equalsIgnoreCase("SIMPLE_INTEGER")) { + type.equalsIgnoreCase("SIMPLE_INTEGER") || type.equalsIgnoreCase("INT2") || + type.equalsIgnoreCase("INT4") || type.equalsIgnoreCase("INT8")) { return Type.BIGINT; } - else if (type.equalsIgnoreCase("CHAR") || type.equalsIgnoreCase("VARCHAR") || type.equalsIgnoreCase("STRING") || - type.equalsIgnoreCase("XML")) { + else if (type.equalsIgnoreCase("CHAR") || type.equalsIgnoreCase("VARCHAR") || type.equalsIgnoreCase("VARCHAR2") || + type.equalsIgnoreCase("STRING") || type.equalsIgnoreCase("XML")) { return Type.STRING; } else if (type.equalsIgnoreCase("DEC") || type.equalsIgnoreCase("DECIMAL") || type.equalsIgnoreCase("NUMERIC") || @@ -487,7 +488,10 @@ public class Var { if (type == Type.BIGINT) { return ((Long)value).intValue(); } - return -1; + else if (type == Type.STRING) { + return Integer.parseInt((String)value); + } + throw new NumberFormatException(); } /** @@ -497,7 +501,7 @@ public class Var { if (type == Type.BIGINT) { return ((Long)value).longValue(); } - return -1; + throw new NumberFormatException(); } /** @@ -507,7 +511,7 @@ public class Var { if (type == Type.DECIMAL) { return (BigDecimal)value; } - return null; + throw new NumberFormatException(); } /** @@ -523,7 +527,7 @@ public class Var { else if (type == Type.DECIMAL) { return ((BigDecimal)value).doubleValue(); } - return -1; + throw new NumberFormatException(); } /** http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java b/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java index 70ef995..ed60d23 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java @@ -72,7 +72,7 @@ public class Function { if (execUser(name, ctx)) { return; } - else if (isProc(name) && execProc(name, ctx)) { + else if (isProc(name) && execProc(name, ctx, null)) { return; } if (name.indexOf(".") != -1) { // Name can be qualified and spaces are allowed between parts @@ -86,7 +86,7 @@ public class Function { } name = str.toString(); } - if (trace && ctx.parent.parent instanceof HplsqlParser.Expr_stmtContext) { + if (trace && ctx != null && ctx.parent != null && ctx.parent.parent instanceof HplsqlParser.Expr_stmtContext) { trace(ctx, "FUNC " + name); } FuncCommand func = map.get(name.toUpperCase()); @@ -219,19 +219,22 @@ public class Function { /** * Execute a stored procedure using CALL or EXEC statement passing parameters */ - public boolean execProc(String name, HplsqlParser.Expr_func_paramsContext ctx) { + public boolean execProc(String name, HplsqlParser.Expr_func_paramsContext ctx, ParserRuleContext callCtx) { if (trace) { - trace(ctx, "EXEC PROCEDURE " + name); + trace(callCtx, "EXEC PROCEDURE " + name); } HplsqlParser.Create_procedure_stmtContext procCtx = procMap.get(name.toUpperCase()); if (procCtx == null) { - trace(ctx, "Procedure not found"); + trace(callCtx, "Procedure not found"); return false; } ArrayList<Var> actualParams = getActualCallParameters(ctx); HashMap<String, Var> out = new HashMap<String, Var>(); exec.enterScope(Scope.Type.ROUTINE); exec.callStackPush(name); + if (procCtx.declare_block_inplace() != null) { + visit(procCtx.declare_block_inplace()); + } if (procCtx.create_routine_params() != null) { setCallParameters(ctx, actualParams, procCtx.create_routine_params(), out); } @@ -376,6 +379,16 @@ public class Function { } procMap.put(name.toUpperCase(), ctx); } + + /** + * Get the number of parameters in function call + */ + public int getParamCount(HplsqlParser.Expr_func_paramsContext ctx) { + if (ctx == null) { + return 0; + } + return ctx.func_param().size(); + } /** * Execute a special function @@ -403,8 +416,6 @@ public class Function { execMinPartDate(ctx); } else if(ctx.T_PART_LOC() != null) { execPartLoc(ctx); - } else if(ctx.T_SYSDATE() != null) { - execCurrentTimestamp(ctx, 0); } else { evalNull(); } @@ -440,21 +451,6 @@ public class Function { } /** - * Get the current date and time - */ - public void execCurrentTimestamp(HplsqlParser.Expr_spec_funcContext ctx, int defPrecision) { - trace(ctx, "CURRENT_TIMESTAMP"); - int precision = evalPop(ctx.expr(0), defPrecision).intValue(); - String format = "yyyy-MM-dd HH:mm:ss"; - if(precision > 0 && precision <= 3) { - format += "." + StringUtils.repeat("S", precision); - } - SimpleDateFormat f = new SimpleDateFormat(format); - String s = f.format(Calendar.getInstance(TimeZone.getDefault()).getTime()); - exec.stackPush(new Var(Utils.toTimestamp(s), precision)); - } - - /** * Execute MAX_PART_STRING function */ public void execMaxPartString(HplsqlParser.Expr_spec_funcContext ctx) { http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java index 4b29c59..4d48a55 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java @@ -21,6 +21,7 @@ package org.apache.hive.hplsql.functions; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.Calendar; +import java.util.Date; import java.util.TimeZone; import org.apache.commons.lang.StringUtils; @@ -37,11 +38,15 @@ public class FunctionDatetime extends Function { @Override public void register(Function f) { f.map.put("DATE", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { date(ctx); }}); + f.map.put("FROM_UNIXTIME", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { fromUnixtime(ctx); }}); + f.map.put("NOW", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { now(ctx); }}); f.map.put("TIMESTAMP_ISO", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { timestampIso(ctx); }}); f.map.put("TO_TIMESTAMP", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { toTimestamp(ctx); }}); + f.map.put("UNIX_TIMESTAMP", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { unixTimestamp(ctx); }}); f.specMap.put("CURRENT_DATE", new FuncSpecCommand() { public void run(HplsqlParser.Expr_spec_funcContext ctx) { currentDate(ctx); }}); f.specMap.put("CURRENT_TIMESTAMP", new FuncSpecCommand() { public void run(HplsqlParser.Expr_spec_funcContext ctx) { currentTimestamp(ctx); }}); + f.specMap.put("SYSDATE", new FuncSpecCommand() { public void run(HplsqlParser.Expr_spec_funcContext ctx) { currentTimestamp(ctx); }}); f.specSqlMap.put("CURRENT_DATE", new FuncSpecCommand() { public void run(HplsqlParser.Expr_spec_funcContext ctx) { currentDateSql(ctx); }}); f.specSqlMap.put("CURRENT_TIMESTAMP", new FuncSpecCommand() { public void run(HplsqlParser.Expr_spec_funcContext ctx) { currentTimestampSql(ctx); }}); @@ -114,6 +119,17 @@ public class FunctionDatetime extends Function { var.cast(evalPop(ctx.func_param(0).expr())); evalVar(var); } + + /** + * NOW() function (current date and time) + */ + void now(HplsqlParser.Expr_func_paramsContext ctx) { + if (ctx != null) { + evalNull(); + return; + } + evalVar(currentTimestamp(3)); + } /** * TIMESTAMP_ISO function @@ -148,4 +164,28 @@ public class FunctionDatetime extends Function { evalNull(); } } + + /** + * FROM_UNIXTIME() function (convert seconds since 1970-01-01 00:00:00 to timestamp) + */ + void fromUnixtime(HplsqlParser.Expr_func_paramsContext ctx) { + int cnt = getParamCount(ctx); + if (cnt == 0) { + evalNull(); + return; + } + long epoch = evalPop(ctx.func_param(0).expr()).longValue(); + String format = "yyyy-MM-dd HH:mm:ss"; + if (cnt > 1) { + format = evalPop(ctx.func_param(1).expr()).toString(); + } + evalString(new SimpleDateFormat(format).format(new Date(epoch * 1000))); + } + + /** + * UNIX_TIMESTAMP() function (current date and time in seconds since 1970-01-01 00:00:00) + */ + void unixTimestamp(HplsqlParser.Expr_func_paramsContext ctx) { + evalVar(new Var(System.currentTimeMillis()/1000)); + } } http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionString.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionString.java b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionString.java index 731443f..9d3fd19 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionString.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionString.java @@ -36,6 +36,7 @@ public class FunctionString extends Function { f.map.put("LEN", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { len(ctx); }}); f.map.put("LENGTH", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { length(ctx); }}); f.map.put("LOWER", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { lower(ctx); }}); + f.map.put("REPLACE", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { replace(ctx); }}); f.map.put("SUBSTR", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { substr(ctx); }}); f.map.put("SUBSTRING", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { substr(ctx); }}); f.map.put("TO_CHAR", new FuncCommand() { public void run(HplsqlParser.Expr_func_paramsContext ctx) { toChar(ctx); }}); @@ -50,7 +51,7 @@ public class FunctionString extends Function { */ void concat(HplsqlParser.Expr_func_paramsContext ctx) { StringBuilder val = new StringBuilder(); - int cnt = ctx.func_param().size(); + int cnt = getParamCount(ctx); boolean nulls = true; for (int i = 0; i < cnt; i++) { Var c = evalPop(ctx.func_param(i).expr()); @@ -71,7 +72,7 @@ public class FunctionString extends Function { * CHAR function */ void char_(HplsqlParser.Expr_func_paramsContext ctx) { - int cnt = ctx.func_param().size(); + int cnt = getParamCount(ctx); if (cnt != 1) { evalNull(); return; @@ -84,7 +85,7 @@ public class FunctionString extends Function { * INSTR function */ void instr(HplsqlParser.Expr_func_paramsContext ctx) { - int cnt = ctx.func_param().size(); + int cnt = getParamCount(ctx); if (cnt < 2) { evalNull(); return; @@ -178,10 +179,25 @@ public class FunctionString extends Function { } /** + * REPLACE function + */ + void replace(HplsqlParser.Expr_func_paramsContext ctx) { + int cnt = getParamCount(ctx); + if (cnt < 3) { + evalNull(); + return; + } + String str = evalPop(ctx.func_param(0).expr()).toString(); + String what = evalPop(ctx.func_param(1).expr()).toString(); + String with = evalPop(ctx.func_param(2).expr()).toString(); + evalString(str.replaceAll(what, with)); + } + + /** * SUBSTR and SUBSTRING function */ void substr(HplsqlParser.Expr_func_paramsContext ctx) { - int cnt = ctx.func_param().size(); + int cnt = getParamCount(ctx); if (cnt < 2) { evalNull(); return; @@ -253,7 +269,7 @@ public class FunctionString extends Function { * TO_CHAR function */ void toChar(HplsqlParser.Expr_func_paramsContext ctx) { - int cnt = ctx.func_param().size(); + int cnt = getParamCount(ctx); if (cnt != 1) { evalNull(); return; http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java ---------------------------------------------------------------------- diff --git a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java index 042bacf..80915ea 100644 --- a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java +++ b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java @@ -92,6 +92,16 @@ public class TestHplsqlLocal { public void testCreateFunction2() throws Exception { run("create_function2"); } + + @Test + public void testCreateFunction3() throws Exception { + run("create_function3"); + } + + @Test + public void testCreateFunction4() throws Exception { + run("create_function4"); + } @Test public void testCreatePackage() throws Exception { @@ -112,6 +122,12 @@ public class TestHplsqlLocal { public void testCreateProcedure2() throws Exception { run("create_procedure2"); } + + @Test + public void testCreateProcedure3() throws Exception { + run("create_procedure3"); + } + @Test public void testCreateProcedureNoParams() throws Exception { run("create_procedure_no_params"); @@ -143,6 +159,11 @@ public class TestHplsqlLocal { } @Test + public void testDeclare3() throws Exception { + run("declare3"); + } + + @Test public void testDeclareCondition() throws Exception { run("declare_condition"); } @@ -186,7 +207,7 @@ public class TestHplsqlLocal { public void testFloat() throws Exception { run("float"); } - + @Test public void testForRange() throws Exception { run("for_range"); @@ -262,6 +283,11 @@ public class TestHplsqlLocal { } @Test + public void testReplace() throws Exception { + run("replace"); + } + + @Test public void testReturn() throws Exception { run("return"); } http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java ---------------------------------------------------------------------- diff --git a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java index b9d80c7..59b7bff 100644 --- a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java +++ b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java @@ -37,6 +37,16 @@ public class TestHplsqlOffline { public void testCreateTableMssql() throws Exception { run("create_table_mssql"); } + + @Test + public void testCreateTableMssql2() throws Exception { + run("create_table_mssql2"); + } + + @Test + public void testCreateTableMysql() throws Exception { + run("create_table_mysql"); + } @Test public void testCreateTableOra() throws Exception { @@ -44,6 +54,16 @@ public class TestHplsqlOffline { } @Test + public void testCreateTableOra2() throws Exception { + run("create_table_ora2"); + } + + @Test + public void testCreateTablePg() throws Exception { + run("create_table_pg"); + } + + @Test public void testInsertMysql() throws Exception { run("insert_mysql"); } @@ -52,6 +72,11 @@ public class TestHplsqlOffline { public void testSelectDb2() throws Exception { run("select_db2"); } + + @Test + public void testUpdate() throws Exception { + run("update"); + } /** * Run a test file http://git-wip-us.apache.org/repos/asf/hive/blob/39d66a43/hplsql/src/test/queries/db/create_drop_database.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/db/create_drop_database.sql b/hplsql/src/test/queries/db/create_drop_database.sql new file mode 100644 index 0000000..d71bbbf --- /dev/null +++ b/hplsql/src/test/queries/db/create_drop_database.sql @@ -0,0 +1,5 @@ +create database 'test' || replace('2016-03-03', '-', ''); +create database if not exists test1 comment 'abc' location '/users'; + +drop database if exists 'test' || replace('2016-03-03', '-', ''); +drop database test1;
