Oracle 19cR2 DBMS_HADOOP

Oracle DBMS_HADOOP_INTERNAL Version 19.2.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

Purpose DBMS_HADOOP_INTERNAL is an internal package providing helper functions to DBMS_HADOOP

AUTHID DEFINER

Dependencies

HIVEMETADATA

HIVETYPESET

Documented No

First Available 12.2.0.1

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role.

Source {ORACLE_HOME}/rdbms/admin/dbmshadp1.sql and prvthadoop1.plb

See also dbmshadp.sql and cathive1.sql

Subprograms

ADDED_HIVE_PARTNS	GET_DEBUG_DIR	HYPT_COLUMNS_MATCHED
ADDED_PARTNS	GET_HIVE_COLUMNS	IS_HYPT
DROPPED_PARTNS	GET_HIVE_PKEYS	IS_METADATA_COMPATIBLE
DROP_ALL_PARTNS	GET_HIVE_TAB_INFO	IS_ORACLE_TABLE_COMPATIBLE
FIND_PARTN_SPECS	GET_HYPT_COLUMNS	IS_ORACLE_TABLE_ELIGIBLE
GETHIVETABLE	GET_INCOMPATIBILITY	IS_PARTITION_COMPATIBLE
GETNUMBEROFITEMS	GET_NAME	MERGE_PARTITIONS
GETPARTKEYVALUES	GET_OBJNO_FROM_PARTITIONED_ET	REMOVE_DOUBLE_QUOTE
GET_ALL_HIVE_PARTNS	GET_PARTN_SPEC	SYNC_USER_PRIVILEGED
GET_ALL_HYPT_PARTNS	GET_XT_COLUMNS	UNIX_TS_TO_DATE
GET_ALL_PET_PARTNS	GET_XT_PKEYS	USER_PRIVILEGED
GET_CONFIG_DIR	HIVE_TABLE_EXISTS	XT_COLUMNS_MATCHED
GET_DDL

ADDED_HIVE_PARTNS

Given a Hive table, this function returns all the partitions that appear in the Hive table, but missing in the corresponding Oracle external table. We use the function ADDED_PARTNS() to obtain the hashed partitioned names from DBA_HIVE_TAB_PARTITIONS and then find the corresponding original partition specs. dbms_hadoop_internal.added_hive_partns( clus_id IN VARCHAR2, db_name IN VARCHAR2, tab_name IN VARCHAR2, partnList IN CLOB, mdata_compatible IN VARCHAR2) RETURN CLOB;

TBD

ADDED_PARTNS

Given a Hive table, this function returns all the partitions that appear in the Hive table, but missing in the corresponding Oracle external table dbms_hadoop_internal.added_partns( clus_id IN VARCHAR2, db_name IN VARCHAR2, tab_name IN VARCHAR2, et_name IN VARCHAR2, et_owner IN VARCHAR2, mdata_compatible IN VARCHAR2) RETURN CLOB;

TBD

DROPPED_PARTNS

Given a Hive table, this function returns all the partitions that appear in the corresponding Oracle external table, but in the given Hive table dbms_hadoop_internal.dropped_partns( clus_id IN VARCHAR2, db_name IN VARCHAR2, tab_name IN VARCHAR2, et_name IN VARCHAR2, et_owner IN VARCHAR2, mdata_compatible IN VARCHAR2) RETURN CLOB;

TBD

DROP_ALL_PARTNS (new 19c)

Drop all external table partitions dbms_hadoop_internal.drop_all_partns( clus_id IN VARCHAR2, db_name IN VARCHAR2, tab_name IN VARCHAR2, et_name IN VARCHAR2, et_owner IN VARCHAR2, mdata_compatible IN VARCHAR2) RETURN CLOB;

TBD

FIND_PARTN_SPECS (new 19c)

Returns the partition specs for a given partition key dbms_hadoop_internal.find_partn_specs( curKey IN VARCHAR2, xt_tab_name_dqr IN VARCHAR2, xt_tab_owner_dqr IN VARCHAR2) RETURN CLOB;

TBD

GETHIVETABLE

getHiveTable() is a pipelined table function that returns the rows back from C external procedures via ODCI to PL/SQL. The rows sent from C external procedures actually originate from various Hive metastores and fetched via JNI calls made from hotspot JVM. dbms_hadoop_internal.getHiveTable( configDir IN VARCHAR2, debugDir IN VARCHAR2, clusterName IN VARCHAR2, dbName IN VARCHAR2, tblName IN VARCHAR2, createPartitions IN VARCHAR2, callType IN NUMBER) RETURN hiveTypeSet PIPELINED USING HiveMetadata;

TBD

GETNUMBEROFITEMS

Undocumented dbms_hadoop_internal.getNumberOfItems( instr IN CLOB, boundarykey IN CHAR) RETURN NUMBER;

TBD

GETPARTKEYVALUES

Returns the values of the specified partition key dbms_hadoop_internal.getPartKeyValues( curKey IN CLOB, partnCnt IN NUMBER) RETURN CLOB;

TBD

GET_ALL_HIVE_PARTNS (new 19c)

Returns all the partitions currently in the specified hive table dbms_hadoop_internal.get_all_hive_partns( clus_id IN VARCHAR2, db_name IN VARCHAR2, hive_tab_name IN VARCHAR2) RETURN CLOB;

TBD

GET_ALL_HYPT_PARTNS (new 19c)

Returns all partitions in the specified HyPT dbms_hadoop_internal.get_all_hypt_partns( et_name IN VARCHAR2, et_owner IN VARCHAR2) RETURN CLOB;

TBD

GET_ALL_PET_PARTNS (new 19c)

Returns all partitions in the specified PET table dbms_hadoop_internal.get_all_pet_partns( et_name IN VARCHAR2, et_owner IN VARCHAR2) RETURN CLOB;

TBD

GET_CONFIG_DIR

Undocumented dbms_hadoop_internal.get_config_dir RETURN VARCHAR2;

SQL> SELECT dbms_hadoop_internal.get_config_dir

        2  FROM dual;

      

      GET_CONFIG_DIR

      ------------------------------------------------

GET_DDL

Undocumented dbms_hadoop_internal.get_ddl( secureConfigDir IN VARCHAR2, secureDebugDir IN VARCHAR2, secureClusterId IN VARCHAR2, secureDbName IN VARCHAR2, secureHiveTableName IN VARCHAR2, createPartitions IN VARCHAR2) RETURN CLOB;

TBD

GET_DEBUG_DIR

Undocumented dbms_hadoop_internal.get_debug_dir(current_user IN VARCHAR2) RETURN VARCHAR2;

SQL> SELECT dbms_hadoop_internal.get_debug_dir(USER)

        2  FROM dual;

      

      DBMS_HADOOP_INTERNAL.GET_DEBUG_DIR(USER)

      -----------------------------------------

      InvalidDir

GET_HIVE_COLUMNS

Returns all the columns found in the given hive table dbms_hadoop_internal.get_hive_columns( clus_id IN VARCHAR2, db_name IN VARCHAR2, tbl_name IN VARCHAR2) RETURN CLOB;

TBD

GET_HIVE_PKEYS

Undocumented dbms_hadoop_internal.get_hive_pkeys( clus_id IN VARCHAR2, database_name IN VARCHAR2, tbl_name IN VARCHAR2) RETURN CLOB;

TBD

GET_HIVE_TAB_INFO

Undocumented dbms_hadoop_internal.get_hive_tab_info( xt_tab_name IN VARCHAR2, xt_tab_owner IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_HYPT_COLUMNS (new 19c)

Returns all the merging HYPT table columns dbms_hadoop_internal.get_hypt_columns(orig_ddl IN CLOB) RETURN CLOB;

TBD

GET_INCOMPATIBILITY

Given a Hive table and its corresponding Oracle external table, this function returns the first incompatibility that is encountered dbms_hadoop_internal.get_incompatibility( clus_id IN VARCHAR2, db_name IN VARCHAR2, hive_tbl_name IN VARCHAR2, et_tbl_name IN VARCHAR2, et_tbl_owner IN VARCHAR2) RETURN CLOB;

TBD

GET_NAME

Undocumented dbms_hadoop_internal.get_name( name IN VARCHAR2, myowner IN OUT VARCHAR2, myname IN OUT VARCHAR2, downer IN VARCHAR2);

TBD

GET_OBJNO_FROM_PARTITIONED_ET (new 19c)

Given a Hive table, this function returns the object number of the Oracle external table, if one is present dbms_hadoop_internal.get_objno_from_partitioned_et( cluster_id IN VARCHAR2, table_name IN VARCHAR2, XT_tab_name IN VARCHAR2, xt_tab_owner IN VARCHAR2) RETURN NUMBER;

TBD

GET_PARTN_SPEC

Undocumented dbms_hadoop_internal.get_partn_spec( hive_table_name IN VARCHAR2, text_of_ddl_ip IN CLOB) RETURN CLOB;

TBD

GET_XT_COLUMNS

Returns all external table columns dbms_hadoop_internal.get_xt_columns(orig_ddl IN CLOB) RETURN CLOB;

TBD

GET_XT_PKEYS

Undocumented dbms_hadoop_internal.get_xt_pkeys(orig_ddl IN CLOB) RETURN CLOB;

SQL> SELECT dbms_hadoop_internal.get_xt_pkeys('DROP TABLE t')

        2  FROM dual;

      

      DBMS_HADOOP_INTERNAL.GET_XT_PKEYS('DROPTABLET')

      ------------------------------------------------

HIVE_TABLE_EXISTS (new 19c)

Given the names of a hive table, cluster id and db_name, verifies that the hive table actually exists in hive metastore dbms_hadoop_internal.hive_table_exists( hive_table_name IN VARCHAR2, cluster_id IN VARCHAR2, db_name IN VARCHAR2) RETURN NUMBER;

TBD

HYPT_COLUMNS_MATCHED (new 19c)

Checks whether all HyPT columns match with those in the hive table dbms_hadoop_internal.hypt_columns_matched( clus_id IN VARCHAR2, db_name IN VARCHAR2, hive_tbl_name IN VARCHAR2, orig_ddl IN CLOB) RETURN VARCHAR2;

TBD

IS_HYPT (new 19c)

Check whether a given external table is a hypt dbms_hadoop_internal.is_hypt( xt_tab_name IN VARCHAR2, xt_tab_owner IN VARCHAR2) RETURN NUMBER;

TBD

IS_METADATA_COMPATIBLE

Given a Hive table and its corresponding Oracle external table, this function checks whether the external table is metadata-compatible with the Hive table. Metadata compatibility means (a) every column in the external table must be present in the Hive table (b) the datatype of each external table column must be the same or compatible with the datatype of the hive table column. (c) The partition keys in the external table must be in the same order as the partition keys in the hive table. dbms_hadoop_internal.is_metadata_compatible( clus_id IN VARCHAR2, db_name IN VARCHAR2, hive_tbl_name IN VARCHAR2, et_tbl_name IN VARCHAR2, et_tbl_owner IN VARCHAR2) RETURN VARCHAR2;

TBD

IS_ORACLE_TABLE_COMPATIBLE (new 19c)

Check whether the given Oracle table is compatible with the given Hive table. In order to be compatible, both of their columns should match, partition keys should match and all of their datatypes should be compatible. dbms_hadoop_internal.is_oracle_table_compatible( clus_id IN VARCHAR2, db_name IN VARCHAR2, hive_tbl_name IN VARCHAR2, hypt_tbl_name IN VARCHAR2, hypt_tbl_owner IN VARCHAR2) RETURN VARCHAR2;

IS_ORACLE_TABLE_ELIGIBLE (new 19c)

Check whether the given Oracle table is an eligible table for merging with a Hive table into a hybrid partitioned table. In order to be eligible, it needs to be an internal LIST partitioned table, and cannot be an external table. dbms_hadoop_internal.is_oracle_table_eligible( secureClusterId IN VARCHAR2, secureHiveTableName IN VARCHAR2, hypt_name_dqr IN VARCHAR2, hypt_owner_dqr IN VARCHAR2) RETURN NUMBER;

IS_PARTITION_COMPATIBLE

Given a Hive table and its corresponding Oracle external table, this function tells whether the external table is partition compatible with the hive table.

If the XT is exactly identical to the Hive table this function will return FALSE - the reason is that the user does not need to call the SYNC API. dbms_hadoop_internal.is_partition_compatible( mdata_compatible IN VARCHAR2, partns_added IN CLOB, partns_dropped IN CLOB) RETURN VARCHAR2;

TBD

MERGE_PARTITIONS (new 19c)

Given a local list partitioned Oracle table and details about a partitioned Hive table, merge the Hive partitions onto the local Oracle table dbms_hadoop_internal.merge_partitions( partnlist IN CLOB, cluster_id IN VARCHAR2, db_name IN VARCHAR2, hive_table_name IN VARCHAR2, hypt_tab_name IN VARCHAR2, hypt_tab_owner IN VARCHAR2, perform_ddl IN BOOLEAN) RETURN CLOB;

REMOVE_DOUBLE_QUOTE

Removes the double quotes from an enquoted string dbms_hadoop_internal.remove_double_quote(str IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_hadoop_internal.remove_double_quote('"MORGAN"')

      FROM dual;

SYNC_USER_PRIVILEGED

Undocumented dbms_hadoop_internal.sync_user_privileged( xt_tab_name IN VARCHAR2, xt_tab_owner IN VARCHAR2, current_user IN VARCHAR2) RETURN NUMBER;

TBD

UNIX_TS_TO_DATE

Ancillary function to convert Julian date/time value to calendar date dbms_hadoop_internal.unix_ts_to_date(julian_time IN NUMBER) RETURN DATE;

TBD

USER_PRIVILEGED

Undocumented dbms_hadoop_internal.user_privileged( cluster_id IN VARCHAR2, current_user IN VARCHAR2) RETURN NUMBER;

TBD

XT_COLUMNS_MATCHED

Undocumented dbms_hadoop_internal.xt_columns_matched( clus_id IN VARCHAR2, db_name IN VARCHAR2, hive_tbl_name IN VARCHAR2, orig_ddl IN CLOB) RETURN VARCHAR2;

TBD