Data Science & Machine Learning

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

Hi Denny,

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

Thanks.

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

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;

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.

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.

We will take this issue offline.