hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (Jira)" <j...@apache.org>
Subject [jira] [Work logged] (HIVE-24217) HMS storage backend for HPL/SQL stored procedures
Date Fri, 02 Oct 2020 14:42:00 GMT

     [ 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<StoredProcedure> 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 @@
         <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:
       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 @@
         <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"/>

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:
users@infra.apache.org


Issue Time Tracking
-------------------

    Worklog Id:     (was: 493970)
    Time Spent: 0.5h  (was: 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: 0.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)

Mime
View raw message