Forum Stats

  • 3,740,578 Users
  • 2,248,276 Discussions
  • 7,861,333 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

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 fhmb_gb_test_dat[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.