Skip to Main Content

Data Science & Machine Learning

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ODM Extension in SQLDeveloper is failing while mining from Hive View

chakraa4Aug 27 2014 — edited Aug 28 2014

We have created a view on a table from Hadoop Hive using http://cloudera.com/content/support/en/downloads/connectors/hive/hive-odbc-v2-5-5.html. Effectively, the view is created from a dblink which uses the Hive ODBC connection.

While running ODM(Explore Data) on the view, we see the below error.

Message:

None

Detail:

Error in DATAPROFILE_PROG: ORA-01086: savepoint 'DATAPROFILE_PROG' never established in this session or is invalid ORA-20999: Error in create_sample_table: ORA-20999: Error in internal_create_sample_table: ORA-20999: Error in create_table_from_query: ORA-01010: invalid OCI operation ORA-02063: preceding line from HIVE4PC068162 ORA-06512: at "SYS.DBMS_SQL", line 1321 ORA-06512: at "ODMRSYS.ODMR_INTERNAL_UTIL", line 765 ORA-06512: at "ODMRSYS.ODMR_INTERNAL_UTIL", line 782 ORA-06512: at "ODMRSYS.ODMR_ENGINE", line 1118 ORA-06 ORA-06512: at "ODMRSYS.ODMR_ENGINE_DATA", line 1663

At least let us know the reason of failure so that we can also try to resolve or find workaround.

Comments

Denny Wong-Oracle

Hi

What version of database are you using?

Do you have any issue if accessing the dblink view out side of Data Miner?  Try SELECT * FROM <dblink view> to see if you get any access issue (e.g. missing required privileges).  Also, try creating a view on top of this dblink view (e.g. CREATE VIEW TEST_V AS SELECT * FROM <dblink view>).

Please describe the view, I want to see what data types you have for this view:

desc <dblink view>

Thanks,
Denny

chakraa4

Hi Denny,

Database version 11.2.0.2. Answers of your rest of the questions are in the attached.

Thanks.

Denny Wong-Oracle

Please try creating this table.  The Explore Data node internally creates this sample table, so we want to make sure the query works.  - Denny

CREATE TABLE ODMR$10_16_04_333756EWYOTIX  NOPARALLEL NOLOGGING COMPRESS

AS SELECT /*+ NO_PARALLEL */ * FROM (SELECT * FROM (SELECT /*+ no_merge */ t.*, ROWNUM ODMR$10_16_04_191280QFCQLMB FROM (

WITH "N$10007" as (select /*+ inline */

active_flag,

addr_key,

address_changed_flag,

asset_cat_active,

bb_circuit_changed_flag,

chapter_end,

chapter_end_mod,

chapter_start,

chapter_start_mod,

cust_type,

cust_type_bb,

datalab_update_date,

district,

end_date,

end_date_bb,

line_condition,

line_condition_bb,

line_type,

mdf_site,

mdf_site_bb,

num_lines_changed_flag,

orbit_instln_date,

orbit_instln_date_bb,

orbit_last_update_date,

orbit_last_update_date_bb,

prev_asset_cat_active,

prod_code,

prod_code_bb,

start_date,

start_date_bb,

status,

status_bb,

transition_type,

transition_type_mod

from five_gb_test_data@hive4pc068162  )

SELECT * FROM "N$10007") t) WHERE ORA_HASH(ODMR$10_16_04_191280QFCQLMB,14000000,12345) <= 2000 )

chakraa4

Unless the columns are double quoted, the create table was failing. We have noticed this while selecting a single column from hive table also. Please see below. Is this the reason of failure in ODM?

SQL> CREATE TABLE ODMR$10_16_04_333756EWYOTIX  NOPARALLEL NOLOGGING COMPRESS
  2  AS SELECT /*+ NO_PARALLEL */ * FROM (SELECT * FROM (SELECT /*+ no_merge */ t.*, ROWNUM ODMR$10_16_04_191280QFCQLMB FROM (
  3  WITH "N$10007" as (select /*+ inline */
active_flag,
  4    5  addr_key,
  6  address_changed_flag,
  7  asset_cat_active,
  8  bb_circuit_changed_flag,
  9  chapter_end,
10  chapter_end_mod,
11  chapter_start,
chapter_start_mod,
12   13  cust_type,
14  cust_type_bb,
15  datalab_update_date,
16  district,
17  end_date,
end_date_bb,
18   19  line_condition,
20  line_condition_bb,
21  line_type,
22  mdf_site,
23  mdf_site_bb,
24  num_lines_changed_flag,
orbit_instln_date,
25   26  orbit_instln_date_bb,
27  orbit_last_update_date,
28  orbit_last_update_date_bb,
prev_asset_cat_active,
29   30  prod_code,
prod_code_bb,
31   32  start_date,
33  start_date_bb,
34  status,
35  status_bb,
36  transition_type,
37  transition_type_mod
38  from five_gb_test_data@hive4pc068162  )
39  SELECT * FROM "N$10007") t) WHERE ORA_HASH(ODMR$10_16_04_191280QFCQLMB,14000000,12345) <= 2000 );
transition_type_mod
*
ERROR at line 37:
ORA-00904: "TRANSITION_TYPE_MOD": invalid identifier


SQL> CREATE TABLE ODMR$10_16_04_333756EWYOTIX  NOPARALLEL NOLOGGING COMPRESS
  2  AS SELECT /*+ NO_PARALLEL */ * FROM (SELECT * FROM (SELECT /*+ no_merge */ t.*, ROWNUM ODMR$10_16_04_191280QFCQLMB FROM (
WITH "N$10007" as (select /*+ inline */
  3    4  "active_flag",
  5  "addr_key",
  6  "address_changed_flag",
  7  "asset_cat_active",
  8  "bb_circuit_changed_flag",
"chapter_end",
  9   10  "chapter_end_mod",
11  "chapter_start",
"chapter_start_mod",
12   13  "cust_type",
14  "cust_type_bb",
15  "datalab_update_date",
16  "district",
"end_date",
17   18  "end_date_bb",
"line_condition",
19   20  "line_condition_bb",
21  "line_type",
22  "mdf_site",
23  "mdf_site_bb",
24  "num_lines_changed_flag",
"orbit_instln_date",
25   26  "orbit_instln_date_bb",
27  "orbit_last_update_date",
28  "orbit_last_update_date_bb",
"prev_asset_cat_active",
29   30  "prod_code",
"prod_code_bb",
"start_date",
31   32   33  "start_date_bb",
34  "status",
"status_bb",
35   36  "transition_type",
37  "transition_type_mod"
38  from five_gb_test_data@hive4pc068162  )
39  SELECT * FROM "N$10007") t) WHERE ORA_HASH(ODMR$10_16_04_191280QFCQLMB,14000000,12345) <= 2000 );

Table created.

Elapsed: 00:15:13.87

chakraa4

I also created a single column view on Hive table and ODM is also failing while exploring data. Is it becasue of the doublee quote issue mentioned in my earlier reply?

create view onecol_fhmb_gb_test_data_V("active_flag") as select "active_flag" from fhmb_gb_test_data@hive;

Denny Wong-Oracle

Not sure what the problem is yet.

Please run the following diagnostic test:

1. Log into SYS account

2. Run this statement to enable debugging:

UPDATE "ODMRSYS"."ODMR$REPOSITORY_PROPERTIES" SET PROPERTY_STR_VALUE = 'TRUE' WHERE PROPERTY_NAME = 'DEBUG_LOG';

COMMIT;

3. Run the workflow until the Explore Data node fails again

4. Run this statement to retrieve the problematic SQL that causes the Explore Data node to fail in your case:

SELECT OUTPUT_CLOB FROM ODMRSYS.odmr$debug_log WHERE output_msg like '%EXPLAIN PLAN DDL: internal_create_sample_table 2:COMPRESS:%'

AND PROJECT_NAME = '1stProject' AND WORKFLOW_NAME = 'HiveTest' ORDER BY LOG_TIMESTAMP DESC

5. Run this statement to disable debugging:

UPDATE "ODMRSYS"."ODMR$REPOSITORY_PROPERTIES" SET PROPERTY_STR_VALUE = 'FALSE' WHERE PROPERTY_NAME = 'DEBUG_LOG';

COMMIT;

Send me the result from step 4 above.  Also, try executing the SQL statement returned, and see if any issue with the query.


Thanks.

chakraa4

1. Log into SYS account

2. Run this statement to enable debugging:

SQL> show user
USER is "SYS"
SQL> UPDATE "ODMRSYS"."ODMR$REPOSITORY_PROPERTIES" SET PROPERTY_STR_VALUE = 'TRUE' WHERE PROPERTY_NAME = 'DEBUG_LOG';

1 row updated.

SQL> COMMIT;

Commit complete.


3. Run the workflow until the Explore Data node fails again

Failed with the same error.

Message:
Explore Data failed due to Error in DATAPROFILE_PROG: ORA-20999: Error in create_sample_table: ORA-20999: Error in internal_create_sample_table: ORA-20999: Error in create_table_from_query: ORA-01010: invalid OCI operation ORA-02063: preceding line from HIVE ORA-06512: at "SYS.DBMS_SQL", line 1321 ORA-06512: at "ODMRSYS.ODMR_INTERNAL_UTIL", line 765 ORA-06512: at "ODMRSYS.ODMR_INTERNAL_UTIL", line 782 ORA-06512: at "ODMRSYS.ODMR_ENGINE", line 1118 ORA-06512: at "ODMRSYS.ODMR_ENGINE", line 1163 ORA-06512: at "ODMRSYS.ODMR_ENGINE", line 2019 ORA-06512: at "ODMRSYS.ODMR_ENGINE", line 2051 ORA-06512: at "ODMRSYS.ODMR_ENGINE_DATA", line 1397 .
Detail:
Error in DATAPROFILE_PROG: ORA-20999: Error in create_sample_table: ORA-20999: Error in internal_create_sample_table: ORA-20999: Error in create_table_from_query: ORA-01010: invalid OCI operation ORA-02063: preceding line from HIVE ORA-06512: at "SYS.DBMS_SQL", line 1321 ORA-06512: at "ODMRSYS.ODMR_INTERNAL_UTIL", line 765 ORA-06512: at "ODMRSYS.ODMR_INTERNAL_UTIL", line 782 ORA-06512: at "ODMRSYS.ODMR_ENGINE", line 1118 ORA-06512: at "ODMRSYS.ODMR_ENGINE", line 1163 ORA-06512: at "ODMRSYS.ODMR_ENGINE", line 2019 ORA-06512: at "ODMRSYS.ODMR_ENGINE", line 2051 ORA-06512: at "ODMRSYS.ODMR_ENGINE_DATA", line 1397


4. Run this statement to retrieve the problematic SQL that causes the Explore Data node to fail in your case:
SELECT OUTPUT_CLOB FROM ODMRSYS.odmr$debug_log WHERE output_msg like '%EXPLAIN PLAN DDL: internal_create_sample_table 2:COMPRESS:%'
AND PROJECT_NAME = '1stProject' AND WORKFLOW_NAME = 'HiveTest' ORDER BY LOG_TIMESTAMP DESC

SQL>  SELECT OUTPUT_CLOB FROM ODMRSYS.odmr$debug_log WHERE output_msg like '%EXPLAIN PLAN DDL: internal_create_sample_table 2:COMPRESS:%' ORDER BY LOG_TIMESTAMP DESC;

OUTPUT_CLOB
--------------------------------------------------------------------------------

CREATE TABLE ODMR$16_06_50_118493YOXMHZT   COMPRESS AS SELECT  * FROM (SELECT * FROM (SELECT /*+ no_merge */ t.*, ROWNUM ODMR$16_06_20_871330OHJMTLN FROM (WITH
"N$10001" as (select /*+ inline */ "FIVE_GB_TEST_DATA_V"."prod_code_bb",
"FIVE_GB_TEST_DATA_V"."line_type",
"FIVE_GB_TEST_DATA_V"."prod_code",
"FIVE_GB_TEST_DATA_V"."chapter_end_mod",
"FIVE_GB_TEST_DATA_V"."asset_cat_active",
"FIVE_GB_TEST_DATA_V"."mdf_site",
"FIVE_GB_TEST_DATA_V"."datalab_update_date",
"FIVE_GB_TEST_DATA_V"."bb_circuit_changed_flag",
"FIVE_GB_TEST_DATA_V"."end_date",
"FIVE_GB_TEST_DATA_V"."orbit_instln_date",
"FIVE_GB_TEST_DATA_V"."chapter_end",
"FIVE_GB_TEST_DATA_V"."address_changed_flag",
"FIVE_GB_TEST_DATA_V"."district",
"FIVE_GB_TEST_DATA_V"."num_lines_changed_flag",
"FIVE_GB_TEST_DATA_V"."active_flag",
"FIVE_GB_TEST_DATA_V"."transition_type",
"FIVE_GB_TEST_DATA_V"."chapter_start",
"FIVE_GB_TEST_DATA_V"."addr_key",
"FIVE_GB_TEST_DATA_V"."status",
"FIVE_GB_TEST_DATA_V"."cust_type_bb",
"FIVE_GB_TEST_DATA_V"."orbit_last_update_date",
"FIVE_GB_TEST_DATA_V"."chapter_start_mod",
"FIVE_GB_TEST_DATA_V"."end_date_bb",
"FIVE_GB_TEST_DATA_V"."start_date_bb",
"FIVE_GB_TEST_DATA_V"."line_condition_bb",
"FIVE_GB_TEST_DATA_V"."mdf_site_bb",
"FIVE_GB_TEST_DATA_V"."status_bb",
"FIVE_GB_TEST_DATA_V"."cust_type",
"FIVE_GB_TEST_DATA_V"."prev_asset_cat_active",
"FIVE_GB_TEST_DATA_V"."transition_type_mod",
"FIVE_GB_TEST_DATA_V"."line_condition",
"FIVE_GB_TEST_DATA_V"."orbit_instln_date_bb",
"FIVE_GB_TEST_DATA_V"."start_date",
"FIVE_GB_TEST_DATA_V"."orbit_last_update_date_bb"
from "DMUSER"."FIVE_GB_TEST_DATA_V"  )
SELECT * FROM "N$10001") t) WHERE ORA_HASH(ODMR$16_06_20_871330OHJMTLN,13999999,12345) <= 2000 )

5. Run this statement to disable debugging:

SQL> UPDATE "ODMRSYS"."ODMR$REPOSITORY_PROPERTIES" SET PROPERTY_STR_VALUE = 'FALSE' WHERE PROPERTY_NAME = 'DEBUG_LOG';

1 row updated.

SQL> COMMIT;

Commit complete.


Send me the result from step 4 above.  Also, try executing the SQL statement returned, and see if any issue with the query.

SQL> CREATE TABLE ODMR$16_06_50_118493YOXMHZT   COMPRESS AS SELECT  * FROM (SELECT * FROM (SELECT /*+ no_merge */ t.*, ROWNUM ODMR$16_06_20_871330OHJMTLN FROM (WITH
  2  "N$10001" as (select /*+ inline */ "FIVE_GB_TEST_DATA_V"."prod_code_bb",
  3  "FIVE_GB_TEST_DATA_V"."line_type",
  4  "FIVE_GB_TEST_DATA_V"."prod_code",
  5  "FIVE_GB_TEST_DATA_V"."chapter_end_mod",
  6  "FIVE_GB_TEST_DATA_V"."asset_cat_active",
  7  "FIVE_GB_TEST_DATA_V"."mdf_site",
  8  "FIVE_GB_TEST_DATA_V"."datalab_update_date",
  9  "FIVE_GB_TEST_DATA_V"."bb_circuit_changed_flag",
10  "FIVE_GB_TEST_DATA_V"."end_date",
11  "FIVE_GB_TEST_DATA_V"."orbit_instln_date",
"FIVE_GB_TEST_DATA_V"."chapter_end",
12   13  "FIVE_GB_TEST_DATA_V"."address_changed_flag",
"FIVE_GB_TEST_DATA_V"."district",
14   15  "FIVE_GB_TEST_DATA_V"."num_lines_changed_flag",
16  "FIVE_GB_TEST_DATA_V"."active_flag",
17  "FIVE_GB_TEST_DATA_V"."transition_type",
18  "FIVE_GB_TEST_DATA_V"."chapter_start",
19  "FIVE_GB_TEST_DATA_V"."addr_key",
20  "FIVE_GB_TEST_DATA_V"."status",
21  "FIVE_GB_TEST_DATA_V"."cust_type_bb",
"FIVE_GB_TEST_DATA_V"."orbit_last_update_date",
22   23  "FIVE_GB_TEST_DATA_V"."chapter_start_mod",
24  "FIVE_GB_TEST_DATA_V"."end_date_bb",
25  "FIVE_GB_TEST_DATA_V"."start_date_bb",
26  "FIVE_GB_TEST_DATA_V"."line_condition_bb",
27  "FIVE_GB_TEST_DATA_V"."mdf_site_bb",
28  "FIVE_GB_TEST_DATA_V"."status_bb",
29  "FIVE_GB_TEST_DATA_V"."cust_type",
30  "FIVE_GB_TEST_DATA_V"."prev_asset_cat_active",
"FIVE_GB_TEST_DATA_V"."transition_type_mod",
31   32  "FIVE_GB_TEST_DATA_V"."line_condition",
33  "FIVE_GB_TEST_DATA_V"."orbit_instln_date_bb",
34  "FIVE_GB_TEST_DATA_V"."start_date",
"FIVE_GB_TEST_DATA_V"."orbit_last_update_date_bb"
35   36  from "DMUSER"."FIVE_GB_TEST_DATA_V"  )
37  SELECT * FROM "N$10001") t) WHERE ORA_HASH(ODMR$16_06_20_871330OHJMTLN,13999999,12345) <= 2000 ) ;

Table created.

I also have uploaded the entire ODMRSYS.odmr$debug_log, if that helps. We have an open SR 3-9526401421 which does not seem to be progressing though. Please help.

Denny Wong-Oracle

We will take this issue offline.

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 25 2014
Added on Aug 27 2014
8 comments
2,848 views