[
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 @@
<column name="RM_DUMP_EXECUTION_ID"/>
</index>
</class>
+
+ <class name="MStoredProc" table="STORED_PROCS" identity-type="datastore"
detachable="true">
+ <datastore-identity>
+ <column name="SP_ID"/>
+ </datastore-identity>
+ <field name="createTime">
+ <column name="CREATE_TIME" jdbc-type="integer" allows-null="false"/>
+ </field>
+ <field name="lastAccessTime">
+ <column name="LAST_ACCESS_TIME" jdbc-type="integer"
allows-null="false"/>
+ </field>
+ <field name="database">
+ <column name="DB_ID" allows-null="false"/>
+ </field>
+ <field name="name">
+ <column name="NAME" length="256" jdbc-type="VARCHAR"
allows-null="false"/>
+ </field>
+ <field name="owner">
+ <column name="OWNER_NAME" length="128" jdbc-type="VARCHAR"
allows-null="false"/>
+ </field>
+ <field name="source">
+ <column name="SOURCE" jdbc-type="MEDIUMTEXT" allows-null="false"/>
+ </field>
+ <field name="language">
+ <column name="LANG" jdbc-type="varchar" length="128"
allows-null="false"/>
+ </field>
+ <field name="returnType">
+ <column name="RET_TYPE" jdbc-type="varchar" length="128"
allows-null="true"/>
+ </field>
+ <field name="parameters" table="SP_POS_ARGS" >
+ <collection element-type="MPosParam"/>
+ <join>
+ <column name="SP_ID"/>
+ </join>
+ <element>
+ <embedded>
+ <field name="name">
+ <column name="NAME" jdbc-type="varchar" length="256"
allows-null="false"/>
+ </field>
+ <field name="type">
+ <column name="TYPE" jdbc-type="varchar" length="128"
allows-null="false"/>
+ </field>
+ <field name="length">
+ <column name="length" jdbc-type="integer" allows-null="true"/>
+ </field>
+ <field name="scale">
+ <column name="scale" jdbc-type="integer" allows-null="true"/>
+ </field>
+ <field name="isOut">
+ <column name="OUT" allows-null="false"/>
+ </field>
+ </embedded>
+ </element>
+ </field>
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 storedproc HMS storage.pdf
>
> Time Spent: 2h 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)
