Forum Stats

  • 3,854,813 Users
  • 2,264,424 Discussions
  • 7,905,797 Comments

Discussions

Categories

ODM Extension in SQLDeveloper is failing while mining from Hive View

chakraa4
chakraa4 Member Posts: 26 Blue Ribbon
edited Aug 28, 2014 1:14PM in Machine Learning

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.

· Share on TwitterShare on Facebook

Answers

  • Denny Wong-Oracle
    Denny Wong-Oracle Member Posts: 183
    edited Aug 27, 2014 12:20PM

    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

    · Share on TwitterShare on Facebook
  • chakraa4
    chakraa4 Member Posts: 26 Blue Ribbon

    Hi Denny,

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

    Thanks.

    · Share on TwitterShare on Facebook
  • Denny Wong-Oracle
    Denny Wong-Oracle Member Posts: 183

    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 [email protected]  )

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

    · Share on TwitterShare on Facebook
  • chakraa4
    chakraa4 Member Posts: 26 Blue Ribbon

    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 [email protected]  )
    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 [email protected]  )
    39  SELECT * FROM "N$10007") t) WHERE ORA_HASH(ODMR$10_16_04_191280QFCQLMB,14000000,12345) <= 2000 );

    Table created.

    Elapsed: 00:15:13.87

    · Share on TwitterShare on Facebook
  • chakraa4
    chakraa4 Member Posts: 26 Blue Ribbon

    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 [email protected];

    · Share on TwitterShare on Facebook
  • Denny Wong-Oracle
    Denny Wong-Oracle Member Posts: 183

    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.

    · Share on TwitterShare on Facebook
  • chakraa4
    chakraa4 Member Posts: 26 Blue Ribbon

    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.

    · Share on TwitterShare on Facebook
  • Denny Wong-Oracle
    Denny Wong-Oracle Member Posts: 183

    We will take this issue offline.

    · Share on TwitterShare on Facebook
This discussion has been closed.