[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=506280&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-506280 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 29/Oct/20 16:44 Start Date: 29/Oct/20 16:44 Worklog Time Spent: 10m Work Description: kgyrtkirk merged pull request #1542: URL: https://github.com/apache/hive/pull/1542 This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 506280) Time Spent: 4h 50m (was: 4h 40m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 4h 50m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=505157&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-505157 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 27/Oct/20 10:53 Start Date: 27/Oct/20 10:53 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512590211 ## File path: standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-3.2.0-to-4.0.0.mysql.sql ## @@ -109,6 +109,20 @@ CREATE TABLE IF NOT EXISTS REPLICATION_METRICS ( CREATE INDEX POLICY_IDX ON REPLICATION_METRICS (RM_POLICY); CREATE INDEX DUMP_IDX ON REPLICATION_METRICS (RM_DUMP_EXECUTION_ID); +-- Create stored procedure tables +CREATE TABLE STORED_PROCS ( + `SP_ID` BIGINT(20) NOT NULL, + `CREATE_TIME` INT(11) NOT NULL, + `DB_ID` BIGINT(20) NOT NULL, + `NAME` VARCHAR(256) NOT NULL, + `OWNER_NAME` VARCHAR(128) NOT NULL, + `SOURCE` LONGTEXT NOT NULL, + PRIMARY KEY (`SP_ID`) +); + +CREATE UNIQUE INDEX UNIQUESTOREDPROC ON STORED_PROCS (NAME, DB_ID); +ALTER TABLE `STORED_PROCS` ADD CONSTRAINT `STOREDPROC_FK1` FOREIGN KEY (`DB_ID`) REFERENCES DBS (`DB_ID`); Review comment: good catch, fixed it. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 505157) Time Spent: 4.5h (was: 4h 20m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 4.5h > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=505158&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-505158 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 27/Oct/20 10:53 Start Date: 27/Oct/20 10:53 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512590386 ## File path: standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/model/MPosParam.java ## @@ -0,0 +1,69 @@ +/* + * + * * 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.hadoop.hive.metastore.model; + +public class MPosParam { Review comment: removed ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java ## @@ -1,780 +1,30 @@ /* - * 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 + * * Licensed to the Apache Software Foundation (ASF) under one Review comment: fixed This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 505158) Time Spent: 4h 40m (was: 4.5h) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 4h 40m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504777&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504777 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:42 Start Date: 26/Oct/20 15:42 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512061184 ## File path: hplsql/src/main/java/org/apache/hive/hplsql/Exec.java ## @@ -799,30 +801,35 @@ Integer init(String[] args) throws Exception { select = new Select(this); stmt = new Stmt(this); converter = new Converter(this); - -function = new Function(this); -new FunctionDatetime(this).register(function); -new FunctionMisc(this).register(function); -new FunctionString(this).register(function); -new FunctionOra(this).register(function); + +builtinFunctions = new BuiltinFunctions(this); +new FunctionDatetime(this).register(builtinFunctions); +new FunctionMisc(this).register(builtinFunctions); +new FunctionString(this).register(builtinFunctions); +new FunctionOra(this).register(builtinFunctions); +if ("hms".equalsIgnoreCase(System.getProperty("hplsql.storage"))) { + function = new HmsFunction(this, getMsc(System.getProperty("hplsq.metastore.uris", "thrift://localhost:9083")), builtinFunctions); Review comment: This part is removed from subsequent patch. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 504777) Time Spent: 4h 20m (was: 4h 10m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 4h 20m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504772&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504772 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:41 Start Date: 26/Oct/20 15:41 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512060372 ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java ## @@ -36,20 +36,20 @@ public FunctionDatetime(Exec e) { * Register functions */ @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); }}); + public void register(BuiltinFunctions f) { +f.map.put("DATE", this::date); Review comment: This is a general issue, many things are package private. We can refactor that little by little. . This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 504772) Time Spent: 3h 40m (was: 3.5h) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 3h 40m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504775&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504775 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:41 Start Date: 26/Oct/20 15:41 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512060858 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,31 @@ + + Review comment: I feel like the similarity is accidental and in the future we can expect more diversion between the two. We would likely have columns which are only applicable to one case and not the other. There are already things like className, resourceUri, resourceType in MFunction. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 504775) Time Spent: 4h (was: 3h 50m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 4h > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504776&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504776 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:41 Start Date: 26/Oct/20 15:41 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512060986 ## File path: hplsql/src/main/java/org/apache/hive/hplsql/Exec.java ## @@ -1659,13 +1665,70 @@ public Integer visitExpr_func(HplsqlParser.Expr_funcContext ctx) { } return 0; } - + + /** + * User-defined function in a SQL query + */ + public void execSql(String name, HplsqlParser.Expr_func_paramsContext ctx) { +if (execUserSql(ctx, name)) { + return; +} +StringBuilder sql = new StringBuilder(); +sql.append(name); +sql.append("("); +if (ctx != null) { + int cnt = ctx.func_param().size(); + for (int i = 0; i < cnt; i++) { +sql.append(evalPop(ctx.func_param(i).expr())); +if (i + 1 < cnt) { + sql.append(", "); +} + } +} +sql.append(")"); +exec.stackPush(sql); + } + + /** + * Execute a HPL/SQL user-defined function in a query + */ + private boolean execUserSql(HplsqlParser.Expr_func_paramsContext ctx, String name) { +if (!function.exists(name.toUpperCase())) { + return false; +} +StringBuilder sql = new StringBuilder(); +sql.append("hplsql('"); Review comment: I didn't check since this is not new code, and fixing every issue in the existing code was not the scope of this issue. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 504776) Time Spent: 4h 10m (was: 4h) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 4h 10m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504773&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504773 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:41 Start Date: 26/Oct/20 15:41 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512060541 ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/HmsFunction.java ## @@ -0,0 +1,232 @@ +/* + * + * * 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.functions; + +import static org.apache.hive.hplsql.functions.InMemoryFunction.setCallParameters; + +import java.util.ArrayList; +import java.util.HashMap; +import java.util.Map; +import java.util.Optional; + +import org.antlr.v4.runtime.ANTLRInputStream; +import org.antlr.v4.runtime.CommonTokenStream; +import org.antlr.v4.runtime.ParserRuleContext; +import org.apache.hadoop.hive.metastore.IMetaStoreClient; +import org.apache.hadoop.hive.metastore.api.Database; +import org.apache.hadoop.hive.metastore.api.NoSuchObjectException; +import org.apache.hadoop.hive.metastore.api.StoredProcedure; +import org.apache.hadoop.hive.metastore.api.StoredProcedureRequest; +import org.apache.hive.hplsql.Exec; +import org.apache.hive.hplsql.HplsqlBaseVisitor; +import org.apache.hive.hplsql.HplsqlLexer; +import org.apache.hive.hplsql.HplsqlParser; +import org.apache.hive.hplsql.Scope; +import org.apache.hive.hplsql.Var; +import org.apache.thrift.TException; + +public class HmsFunction implements Function { Review comment: I agree, but this is how it used to work, and I didn't want to address these things as part of this patch. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 504773) Time Spent: 3h 50m (was: 3h 40m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 3h 50m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504770&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504770 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:39 Start Date: 26/Oct/20 15:39 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512058846 ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/FunctionDatetime.java ## @@ -27,7 +27,7 @@ import org.apache.commons.lang3.StringUtils; import org.apache.hive.hplsql.*; -public class FunctionDatetime extends Function { +public class FunctionDatetime extends BuiltinFunctions { Review comment: It used to extend from Function which was a class before. I didn't really change how it used to work, it's still using implementation inheritance, which I personally don't like but didn't want to change it as part of this patch. We might want to move builtin functions into the DB later on, making this class unnecessary in the future. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 504770) Time Spent: 3.5h (was: 3h 20m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 3.5h > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504768&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504768 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:38 Start Date: 26/Oct/20 15:38 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512058291 ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java ## @@ -1,780 +1,30 @@ /* - * 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 + * * 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. * - * 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.functions; -import java.sql.ResultSet; -import java.sql.Date; -import java.sql.SQLException; -import java.text.SimpleDateFormat; -import java.util.ArrayList; -import java.util.Calendar; -import java.util.HashMap; -import java.util.Map; -import java.util.TimeZone; -import java.util.regex.Matcher; -import java.util.regex.Pattern; +import org.apache.hive.hplsql.HplsqlParser; -import org.apache.commons.lang3.StringUtils; -import org.antlr.v4.runtime.ParserRuleContext; -import org.apache.hive.hplsql.*; - -interface FuncCommand { - void run(HplsqlParser.Expr_func_paramsContext ctx); -} - -interface FuncSpecCommand { - void run(HplsqlParser.Expr_spec_funcContext ctx); -} - -/** - * HPL/SQL functions - */ -public class Function { - Exec exec; - HashMap map = new HashMap(); - HashMap specMap = new HashMap(); - HashMap specSqlMap = new HashMap(); - HashMap userMap = new HashMap(); - HashMap procMap = new HashMap(); - boolean trace = false; - - public Function(Exec e) { -exec = e; -trace = exec.getTrace(); - } - - /** - * Register functions - */ - public void register(Function f) { - } - - /** - * Execute a function - */ - public void exec(String name, HplsqlParser.Expr_func_paramsContext ctx) { -if (execUser(name, ctx)) { - return; -} -else if (isProc(name) && execProc(name, ctx, null)) { - return; -} -if (name.indexOf(".") != -1) { // Name can be qualified and spaces are allowed between parts - String[] parts = name.split("\\."); - StringBuilder str = new StringBuilder(); - for (int i = 0; i < parts.length; i++) { -if (i > 0) { - str.append("."); -} -str.append(parts[i].trim()); - } - name = str.toString(); -} -if (trace && ctx != null && ctx.parent != null && ctx.parent.parent instanceof HplsqlParser.Expr_stmtContext) { - trace(ctx, "FUNC " + name); -} -FuncCommand func = map.get(name.toUpperCase()); -if (func != null) { - func.run(ctx); -} -else { - info(ctx, "Function not found: " + name); - evalNull(); -} - } - - /** - * User-defined function in a SQL query - */ - public void execSql(String name, HplsqlParser.Expr_func_paramsContext ctx) { -if (execUserSql(ctx, name)) { - return; -} -StringBuilder sql = new StringBuilder(); -sql.append(name); -sql.append("("); -if (ctx != null) { - int cnt = ctx.func_param().size(); - for (int i = 0; i < cnt; i++) { -sql.append(evalPop(ctx.func_
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504767&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504767 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:38 Start Date: 26/Oct/20 15:38 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512058024 ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java ## @@ -1,780 +1,30 @@ /* - * 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 + * * 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. * - * 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.functions; -import java.sql.ResultSet; -import java.sql.Date; -import java.sql.SQLException; -import java.text.SimpleDateFormat; -import java.util.ArrayList; -import java.util.Calendar; -import java.util.HashMap; -import java.util.Map; -import java.util.TimeZone; -import java.util.regex.Matcher; -import java.util.regex.Pattern; +import org.apache.hive.hplsql.HplsqlParser; -import org.apache.commons.lang3.StringUtils; -import org.antlr.v4.runtime.ParserRuleContext; -import org.apache.hive.hplsql.*; - -interface FuncCommand { - void run(HplsqlParser.Expr_func_paramsContext ctx); -} - -interface FuncSpecCommand { - void run(HplsqlParser.Expr_spec_funcContext ctx); -} - -/** - * HPL/SQL functions - */ -public class Function { - Exec exec; - HashMap map = new HashMap(); - HashMap specMap = new HashMap(); - HashMap specSqlMap = new HashMap(); - HashMap userMap = new HashMap(); - HashMap procMap = new HashMap(); - boolean trace = false; - - public Function(Exec e) { -exec = e; -trace = exec.getTrace(); - } - - /** - * Register functions - */ - public void register(Function f) { - } - - /** - * Execute a function - */ - public void exec(String name, HplsqlParser.Expr_func_paramsContext ctx) { -if (execUser(name, ctx)) { - return; -} -else if (isProc(name) && execProc(name, ctx, null)) { - return; -} -if (name.indexOf(".") != -1) { // Name can be qualified and spaces are allowed between parts - String[] parts = name.split("\\."); - StringBuilder str = new StringBuilder(); - for (int i = 0; i < parts.length; i++) { -if (i > 0) { - str.append("."); -} -str.append(parts[i].trim()); - } - name = str.toString(); -} -if (trace && ctx != null && ctx.parent != null && ctx.parent.parent instanceof HplsqlParser.Expr_stmtContext) { - trace(ctx, "FUNC " + name); -} -FuncCommand func = map.get(name.toUpperCase()); -if (func != null) { - func.run(ctx); -} -else { - info(ctx, "Function not found: " + name); - evalNull(); -} - } - - /** - * User-defined function in a SQL query - */ - public void execSql(String name, HplsqlParser.Expr_func_paramsContext ctx) { -if (execUserSql(ctx, name)) { - return; -} -StringBuilder sql = new StringBuilder(); -sql.append(name); -sql.append("("); -if (ctx != null) { - int cnt = ctx.func_param().size(); - for (int i = 0; i < cnt; i++) { -sql.append(evalPop(ctx.func_
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504769&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504769 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:38 Start Date: 26/Oct/20 15:38 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r512058576 ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/BuiltinFunctions.java ## @@ -0,0 +1,435 @@ +/* + * + * * 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.functions; + +import java.sql.Date; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.HashMap; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import org.antlr.v4.runtime.ParserRuleContext; +import org.apache.hive.hplsql.Exec; +import org.apache.hive.hplsql.HplsqlParser; +import org.apache.hive.hplsql.Query; +import org.apache.hive.hplsql.Utils; +import org.apache.hive.hplsql.Var; + +public class BuiltinFunctions { Review comment: This was just extracted out from the existing code, it's a not a new thing, I think this is out of scope now. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 504769) Time Spent: 3h 20m (was: 3h 10m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 3h 20m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=504755&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-504755 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 26/Oct/20 15:02 Start Date: 26/Oct/20 15:02 Worklog Time Spent: 10m Work Description: kgyrtkirk commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r511984518 ## File path: standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-3.2.0-to-4.0.0.mysql.sql ## @@ -109,6 +109,20 @@ CREATE TABLE IF NOT EXISTS REPLICATION_METRICS ( CREATE INDEX POLICY_IDX ON REPLICATION_METRICS (RM_POLICY); CREATE INDEX DUMP_IDX ON REPLICATION_METRICS (RM_DUMP_EXECUTION_ID); +-- Create stored procedure tables +CREATE TABLE STORED_PROCS ( + `SP_ID` BIGINT(20) NOT NULL, + `CREATE_TIME` INT(11) NOT NULL, + `DB_ID` BIGINT(20) NOT NULL, + `NAME` VARCHAR(256) NOT NULL, + `OWNER_NAME` VARCHAR(128) NOT NULL, + `SOURCE` LONGTEXT NOT NULL, + PRIMARY KEY (`SP_ID`) +); + +CREATE UNIQUE INDEX UNIQUESTOREDPROC ON STORED_PROCS (NAME, DB_ID); +ALTER TABLE `STORED_PROCS` ADD CONSTRAINT `STOREDPROC_FK1` FOREIGN KEY (`DB_ID`) REFERENCES DBS (`DB_ID`); Review comment: what will happen when the db is dropped? wouldn't this FK will restrict the DB from being dropped? ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java ## @@ -1,780 +1,30 @@ /* - * 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 + * * Licensed to the Apache Software Foundation (ASF) under one Review comment: these lines start with `* *` ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/BuiltinFunctions.java ## @@ -0,0 +1,435 @@ +/* + * + * * 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.functions; + +import java.sql.Date; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.HashMap; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import org.antlr.v4.runtime.ParserRuleContext; +import org.apache.hive.hplsql.Exec; +import org.apache.hive.hplsql.HplsqlParser; +import org.apache.hive.hplsql.Query; +import org.apache.hive.hplsql.Utils; +import org.apache.hive.hplsql.Var; + +public class BuiltinFunctions { Review comment: do we really need to define these function differently than others; I've taken a look at `MIN_PART_STRING` and it seenms like its an ordinary function...so it could probably use the registry way approach ## File path: hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java ## @@ -1,780 +1,30 @@ /* - * 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 + * * 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 complia
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=502753&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-502753 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 20/Oct/20 15:51 Start Date: 20/Oct/20 15:51 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r508638143 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Review comment: Discussed this further offline, let's try the string based approach for now and see how it goes. I'll modify the patch. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 502753) Time Spent: 2h 40m (was: 2.5h) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 2h 40m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=502091&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-502091 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 19/Oct/20 10:19 Start Date: 19/Oct/20 10:19 Worklog Time Spent: 10m Work Description: kgyrtkirk commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r507634498 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Review comment: > I understand it is only for showing the user, but it it feels like an afterthought, I don't really like it. Okay; then we don't need to add and store it - we could also use a simple UDF to get the signature if we want that. About `search for the return type` I think we should come up with a "working solution first" and go after these things when we need it (maybe never?) I think that `MethodFinder.methodFor` will need a lot more than just a signature...so it's a little bit too much > These things might sound unimportant but I think language design and tool development shouldn't be separated. Tool support should be considered from day 1 when creating a language. We may leave this thing out completely right now - because we don't need it right now. I think instead of adding something which may not perfectly align with our future needs ; leaving something out will not lock us in at all. > but interpreting the AST (method body) as a bottleneck. that's the problem of the language implementation itself - I think in case the language function is defined in some kind of "text" then we should store it as "text" - to have a less convoluted contract with the embedded language. > If we only allow defining procedures in terms of the "host language", then this is true indeed. My assumption was that we might want to accept procedure definitions in terms of the "foreign language". yes; we should only store those procedures/functions which are usable from the HiveQL - don't we? > `CREATE FUNCTION func1(a int) RETURNS int LANGUAGE XX BEGIN ... END;` I think we should simply store the whole definition from `CREATE` to the closing `;`. Storing anything less could become unparsable. > > I don't know what feature you are refering > It's probably that one. I think that illustrates nicely why parsing is not always a prerequisite of calling a procedure. There might be some misunderstanding here by "parsing" - I mean to process it with the host sql language. If the client language has performance issues doing parsing/etc is then that issue belong to the language itself. In case the language is an on-the fly interpreted language which also happens to have a compiled version ; then that other version could either be registered as a separate language (and refer to a binary somehow) or the language could add some caching/etc to avoid unneccessary parsing overhead. Couldn't we do something like this for hplsql? In any case: I think that extending the basic implementation with a "generic blob storage" option to provide additional services for the stored procedures (which could potentially be used to speed up sql function execution) should be a separate feature - and as such; should be discussed(and implemented) separetly. (Honestly I think there would be marginal benefits implementing this - and could be dodged by the client language implementation with a few caches/etc.) This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 502091) Time Spent: 2.5h (was: 2h 20m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 >
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=502050&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-502050 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 19/Oct/20 07:40 Start Date: 19/Oct/20 07:40 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r507534867 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Review comment: > That was a suggestion to provide a way to store a human readable.. I understand it is only for showing the user, but it it feels like an afterthought, I don't really like it. It adds redundancy and it only has a single use. The structured information can be used to generate this output, (or in fact other outputs, like showing the parameters in a table) or to supply meta programming information to various tools, like code completion tools or method finders. For example, search for the return type: ``` int i = fn.. // find every function that starts with "fn" and returns an int ``` Although this is a search for the return type not on the parameter but it's the same problem since the return type is also part of the signature. I we had higher order functions (in fact JavaScript has and that might be the 2nd supported language) then: ``` filter(fn.., listOfString); // find every function that starts with "fn" and takes a single string parameter ``` Method finder: This might look like a bit scifi, there are only 2 programming environments I'm aware of, which know how to do this. ``` MethodFinder.methodFor('somefile.txt', 'txt'); // find which method returns the extension part of filename, by an by example, ``` It will return: ``` "FilenameUtils.getExtension()" ``` These things might sound unimportant but I think language design and tool development shouldn't be separated. Tool support should be considered from day 1 when creating a language. > If at some point in time the "parsing" will prove to be a bottle neck I'm was not talking about parsing as a bottleneck (though it could be) but interpreting the AST (method body) as a bottleneck. I can't think of any imperative language that can be taken seriously that works that way. Perhaps shell scripts, or early versions of Ruby which was notoriously slow so later they changed it. > I think we should clarify/separate 2 things If we only allow defining procedures in terms of the "host language", then this is true indeed. My assumption was that we might want to accept procedure definitions in terms of the "foreign language". For example function(x,y) {}. But ok, let's say this is not allowed. Then you're right, using the alternative format seems to be viable if we don't count the other issues. If we go to that route, what would you do with the other columns which are also part of the signature, like "LANG", "RET_TYPE", "NAME" ? ``` CREATE FUNCTION func1(a int) RETURNS int LANGUAGE XX BEGIN ... END; ``` > I don't know what feature you are refering It's probably that one. I think that illustrates nicely why parsing is not always a prerequisite of calling a procedure. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 502050) Time Spent: 2h 20m (was: 2h 10m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major >
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=501467&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-501467 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 16/Oct/20 08:39 Start Date: 16/Oct/20 08:39 Worklog Time Spent: 10m Work Description: kgyrtkirk commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r506176698 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Review comment: > What was proposed (but @kgyrtkirk correct me if I'm wrong) as an alternative solution is store the signature together with the body and optionally add a signature string to the table as well (this would be redundant). I think regardless of the representation, backward compatibility will always be a concern. That was a suggestion to provide a way to store a human readable 1 liner for the function - I never thinked that we should parse it; the system should rely on the definition - the human readable field would be just for "show" to be used sysdb related tables/etc. > This is only true for now because HPL/SQL is an AST interpreter but if we ever want to make this performant probably we'll need to introduce a byte code VM at some point in the near future. If at some point in time the "parsing" will prove to be a bottle neck - then we should address it at that point ; rather than try to address a problem which we may not even face - correct me if I'm wrong but I think we will only parse the function 1 time during the HS2 life cycle - which doesn't seem to be a big deal to me. > One thing I don't like about storing the signature in text is that the assumption that invoking the procedure will always require parsing. This is only true for now because HPL/SQL is an AST interpreter but if we ever want to make this performant probably we'll need to introduce a byte code VM at some point in the near future. I think we should clarify/separate 2 things - for stored procedures usually there will be 2 languages at play: * the "host" language - in this case HiveQL - which will accept the function definition * the "foreign" language - which may or may not need parsing; in case of "HPL/SQL" it will but that will not be true for all languages because this patch doesn't contain any HiveQL parser changes - I don't know how that part will work; could you give some example how these functions could be used by the user? ``` create procedure x(a integer) as $$ insert into t values(a); $$ langugage hplsql; call x(12); ``` > For example postgres allows you to invoke procedures implemented in C where runtime information about the signature is not available. I don't know what feature you are refering to ; but afaik to register a function in postgres you should [run a `create function` stmt](https://www.postgresql.org/docs/9.1/sql-createfunction.html) - which will contain all the arguments/etc; and because of that I don't see any issue with other languages - as a create procedure call for any language; must contain the full defintion - which includes argument types/etc - so we will store that for any supported language.. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 501467) Time Spent: 2h 10m (was: 2h) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedpro
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=501083&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-501083 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 15/Oct/20 12:28 Start Date: 15/Oct/20 12:28 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r50545 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Review comment: Hey @jcamachor thanks for the feedback, I'm glad you chimed in. Maybe there is a misunderstanding, this information is not redundant in any way, we're making use of it when invoking a procedure. This information must be stored somewhere in some form the only question is what representation to use. Just to clarify, currently I store the signature in a structured way + procedure body (without the signature) in text. What was proposed (but @kgyrtkirk correct me if I'm wrong) as an alternative solution is store the signature together with the body and optionally add a signature string to the table as well (this would be redundant). I think regardless of the representation, backward compatibility will always be a concern. One thing I don't like about storing the signature in text is that the assumption that invoking the procedure will always require parsing. This is only true for now because HPL/SQL is an AST interpreter but if we ever want to make this performant probably we'll need to introduce a byte code VM at some point in the near future. While creating a procedure needs parsing but invoking it wouldn't, if we stored the byte code. How would this work in this case? I suppose we can add runtime information into the byte code, but that's not always an option. For example postgres allows you to invoke procedures implemented in C where runtime information about the signature is not available. This might be one reason why they also choose to store the signature separately in a structured way. Multi language support was already raised by customers and adding it would be the easiest if we had common bytecode for different languages. One might want to call a procedure implemented in language A from a different language B. Then A would need to use the parser of language B to get the signature information, if the signature was stored in text. We can keep speculating on this, but at this point this is still an experimental and undocumented feature, I'm open to change it later if we have proof that one way is better than the other. But if we decide to go with the alternative solution from now on, I suggest we choose a language agnostic representation (JSON or whatever) of the signature, instead of the unparsed text. > Also, checking the documentation, it seems HPL/SQL can apply some transformations to the field type. Are those transformations applied before storing the definition or later on? Yes, and that only affects create table statements. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 501083) Time Spent: 2h (was: 1h 50m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 2h > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=500843&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-500843 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 14/Oct/20 20:50 Start Date: 14/Oct/20 20:50 Worklog Time Spent: 10m Work Description: jcamachor commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r504964132 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Review comment: Thanks @zeroflag for working on this , this is very cool. There are a few challenges with keeping redundant information outside of the procedure text that I can think of. One of them is that while the semantics of the procedure definition may be well defined, the representation for the other fields may not be defined clearly. Another usual challenge is that if there are any changes in the future, you will have to ensure backwards compatibility for those fields too. Going through the specific implementation of type handling, it seems you are keeping length and scale in different fields. This is not done when we store types in HMS for Hive. Any reason for that? Also, checking the documentation, it seems HPL/SQL can apply some transformations to the field type. Are those transformations applied before storing the definition or later on? Based on that, I think keeping a lean representation in HMS has its advantages like @kgyrtkirk mentioned, specifically if those fields are not actually being used for the time being. It's preferable to add those fields later on if needed, rather than backtracking and removing fields. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 500843) Time Spent: 1h 50m (was: 1h 40m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 1h 50m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=498624&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-498624 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 09/Oct/20 15:33 Start Date: 09/Oct/20 15:33 Worklog Time Spent: 10m Work Description: kgyrtkirk commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r502512642 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Review comment: > I'm not sure if they never participate in a query. If one wants to discover the stored procedures which are currently stored in a DB and find out on what data they operate they would need to do some clumsy string manipulations on the signature. I believe you are thinking about `information_schema` stuff - its not set in stone that we have to get all that data from the metastore db - for this case we might add a few UDFs parameter info into an array or something ; so we will still store simple things in the metastore - but we could transform it into more readable in. > Considering that other DB engines also store these information separately I would like to keep it as it is for now and see how it works in practice. Later on when we have multi language support we can revisit this issue. yes it might be..but it would be better to revisit stuff like this if its really needed; and not after we have introduced "something" which later we should care for even if we don't want to I still think there will be no real benefit of "storing it" in a decomposed manner - it will be harder to go forward in case stuff changes - and right now will not use it for anything ; so let's remove it..and add it only if there is a real need for it. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 498624) Time Spent: 1h 40m (was: 1.5h) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 1h 40m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=495360&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-495360 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 05/Oct/20 14:33 Start Date: 05/Oct/20 14:33 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r499644273 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + Review comment: I changed it to CLOB, that is already used at multiple places. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 495360) Time Spent: 1.5h (was: 1h 20m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 1.5h > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=493985&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-493985 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 02/Oct/20 15:04 Start Date: 02/Oct/20 15:04 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r498878756 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + Review comment: I'll double check it, I remember having some problem with another textual datatype, I can't remember which one was that, that's why MEDIUMTEXT was chosen. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 493985) Time Spent: 1h 20m (was: 1h 10m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 1h 20m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=493984&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-493984 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 02/Oct/20 15:03 Start Date: 02/Oct/20 15:03 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r498877894 ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Review comment: The signature is already parsed by the time the procedure is being created. We would need to drop that information, get back the textual representation of the signature to store it in HMS, and reparse it on the client side when someone calls the procedure. That's maybe not a big deal but still unnecessary to parse it twice. Storing it in a structured way also ensures some degree of validity, you can't store a syntactically incorrect signature if we store it in a structured way. I'm not sure if they never participate in a query. If one wants to discover the stored procedures which are currently stored in a DB and find out on what data they operate they would need to do some clumsy string manipulations on the signature. Considering that other DB engines also store these information separately I would like to keep it as it is for now and see how it works in practice. Later on when we have multi language support we can revisit this issue. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 493984) Time Spent: 1h 10m (was: 1h) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 1h 10m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=493978&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-493978 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 02/Oct/20 14:51 Start Date: 02/Oct/20 14:51 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r498868959 ## File path: standalone-metastore/metastore-common/src/main/thrift/hive_metastore.thrift ## @@ -2830,6 +2848,11 @@ PartitionsResponse get_partitions_req(1:PartitionsRequest req) void add_replication_metrics(1: ReplicationMetricList replicationMetricList) throws(1:MetaException o1) ReplicationMetricList get_replication_metrics(1: GetReplicationMetricsRequest rqst) throws(1:MetaException o1) GetOpenTxnsResponse get_open_txns_req(1: GetOpenTxnsRequest getOpenTxnsRequest) + + void create_stored_procedure(1: string catName, 2: StoredProcedure proc) throws(1:NoSuchObjectException o1, 2:MetaException o2) + StoredProcedure get_stored_procedure(1: string catName, 2: string db, 3: string name) throws (1:MetaException o1, 2:NoSuchObjectException o2) + void drop_stored_procedure(1: string catName, 2: string dbName, 3: string funcName) throws (1:MetaException o1, 2:NoSuchObjectException o2) + list get_all_stored_procedures(1: string catName) throws (1:MetaException o1) Review comment: You mean putting (1: string catName, 2: string dbName, 3: string funcName) into a request object? I can do that. But if we have only one parameter, like in the last case that would be an overkill in my opinion. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 493978) Time Spent: 1h (was: 50m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 1h > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=493976&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-493976 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 02/Oct/20 14:48 Start Date: 02/Oct/20 14:48 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r498868959 ## File path: standalone-metastore/metastore-common/src/main/thrift/hive_metastore.thrift ## @@ -2830,6 +2848,11 @@ PartitionsResponse get_partitions_req(1:PartitionsRequest req) void add_replication_metrics(1: ReplicationMetricList replicationMetricList) throws(1:MetaException o1) ReplicationMetricList get_replication_metrics(1: GetReplicationMetricsRequest rqst) throws(1:MetaException o1) GetOpenTxnsResponse get_open_txns_req(1: GetOpenTxnsRequest getOpenTxnsRequest) + + void create_stored_procedure(1: string catName, 2: StoredProcedure proc) throws(1:NoSuchObjectException o1, 2:MetaException o2) + StoredProcedure get_stored_procedure(1: string catName, 2: string db, 3: string name) throws (1:MetaException o1, 2:NoSuchObjectException o2) + void drop_stored_procedure(1: string catName, 2: string dbName, 3: string funcName) throws (1:MetaException o1, 2:NoSuchObjectException o2) + list get_all_stored_procedures(1: string catName) throws (1:MetaException o1) Review comment: You mean putting (1: string catName, 2: string dbName, 3: string funcName) into a request object? I can do that. But if we have only can parameter, like in the last case that would be an overkill in my opinion. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 493976) Time Spent: 50m (was: 40m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 50m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=493974&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-493974 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 02/Oct/20 14:46 Start Date: 02/Oct/20 14:46 Worklog Time Spent: 10m Work Description: zeroflag commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r498867496 ## File path: standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0.derby.sql ## @@ -786,6 +786,35 @@ CREATE TABLE "APP"."REPLICATION_METRICS" ( CREATE INDEX "POLICY_IDX" ON "APP"."REPLICATION_METRICS" ("RM_POLICY"); CREATE INDEX "DUMP_IDX" ON "APP"."REPLICATION_METRICS" ("RM_DUMP_EXECUTION_ID"); +-- Create stored procedure tables +CREATE TABLE "APP"."STORED_PROCS" ( + "SP_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "LAST_ACCESS_TIME" INTEGER NOT NULL, Review comment: the intention was to have something the represents the last modification date (maybe the name was chosen poorly), but ok I'll remove it, it is not used This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 493974) Time Spent: 40m (was: 0.5h) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 40m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=493970&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-493970 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 02/Oct/20 14:41 Start Date: 02/Oct/20 14:41 Worklog Time Spent: 10m Work Description: kgyrtkirk commented on a change in pull request #1542: URL: https://github.com/apache/hive/pull/1542#discussion_r498830563 ## File path: standalone-metastore/metastore-common/src/main/thrift/hive_metastore.thrift ## @@ -2830,6 +2848,11 @@ PartitionsResponse get_partitions_req(1:PartitionsRequest req) void add_replication_metrics(1: ReplicationMetricList replicationMetricList) throws(1:MetaException o1) ReplicationMetricList get_replication_metrics(1: GetReplicationMetricsRequest rqst) throws(1:MetaException o1) GetOpenTxnsResponse get_open_txns_req(1: GetOpenTxnsRequest getOpenTxnsRequest) + + void create_stored_procedure(1: string catName, 2: StoredProcedure proc) throws(1:NoSuchObjectException o1, 2:MetaException o2) + StoredProcedure get_stored_procedure(1: string catName, 2: string db, 3: string name) throws (1:MetaException o1, 2:NoSuchObjectException o2) + void drop_stored_procedure(1: string catName, 2: string dbName, 3: string funcName) throws (1:MetaException o1, 2:NoSuchObjectException o2) + list get_all_stored_procedures(1: string catName) throws (1:MetaException o1) Review comment: could you please follow the convention of other methods and define a struct for the requests arguments ## File path: standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0.derby.sql ## @@ -786,6 +786,35 @@ CREATE TABLE "APP"."REPLICATION_METRICS" ( CREATE INDEX "POLICY_IDX" ON "APP"."REPLICATION_METRICS" ("RM_POLICY"); CREATE INDEX "DUMP_IDX" ON "APP"."REPLICATION_METRICS" ("RM_DUMP_EXECUTION_ID"); +-- Create stored procedure tables +CREATE TABLE "APP"."STORED_PROCS" ( + "SP_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "LAST_ACCESS_TIME" INTEGER NOT NULL, Review comment: I think we should only add fields which are actually usefull and in use - because right now the accesstime would not be updated at all I don't think we should add it. ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Review comment: I think instead of storing the return_type/argument types and such in the metastore - as they would never participate in a query or anything "usefull"; they will just travel as payload in the messages. Given the fact that they are effectively implicit data which can be figured out from the function defintion - I think we may leave it to the execution engine; it should be able to figure it out (since it should be able to use it) . optionally; to give ourselfs(and users) some type of clarity we could add a "signature" string to the table - which could provide a human readable signature ## File path: standalone-metastore/metastore-server/src/main/resources/package.jdo ## @@ -1549,6 +1549,83 @@ + + + + + + + + + + + + + + + + + + + + + + Review comment: this is the first occurence of MEDIUMTEXT in package.jdo - I don't know how well that will work we had quite a few problems with "long" tableproperty values - and PARAM_VALUE was updated to use CLOB in oracle/etc the most important would be to make sure that we can store the procedure in all supported metastore databases - if possible this should also be tested in some way (at least by hand) This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 493970) Time Spent: 0.5h (was: 20m) > HMS storage backend for HPL/SQL stored procedur
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=493512&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-493512 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 01/Oct/20 14:24 Start Date: 01/Oct/20 14:24 Worklog Time Spent: 10m Work Description: zeroflag commented on pull request #1542: URL: https://github.com/apache/hive/pull/1542#issuecomment-702171541 cc: @ashutoshc, @nrg4878, @maheshk114 This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 493512) Time Spent: 20m (was: 10m) > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Labels: pull-request-available > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 20m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
[ https://issues.apache.org/jira/browse/HIVE-24217?focusedWorklogId=493066&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-493066 ] ASF GitHub Bot logged work on HIVE-24217: - Author: ASF GitHub Bot Created on: 30/Sep/20 16:38 Start Date: 30/Sep/20 16:38 Worklog Time Spent: 10m Work Description: zeroflag opened a new pull request #1542: URL: https://github.com/apache/hive/pull/1542 work in progress This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking --- Worklog Id: (was: 493066) Remaining Estimate: 0h Time Spent: 10m > HMS storage backend for HPL/SQL stored procedures > - > > Key: HIVE-24217 > URL: https://issues.apache.org/jira/browse/HIVE-24217 > Project: Hive > Issue Type: Bug > Components: Hive, hpl/sql, Metastore >Reporter: Attila Magyar >Assignee: Attila Magyar >Priority: Major > Attachments: HPL_SQL storedproc HMS storage.pdf > > Time Spent: 10m > Remaining Estimate: 0h > > HPL/SQL procedures are currently stored in text files. The goal of this Jira > is to implement a Metastore backend for storing and loading these procedures. > This is an incremental step towards having fully capable stored procedures in > Hive. > > See the attached design for more information. -- This message was sent by Atlassian Jira (v8.3.4#803005)