2017/12/28

SQOOP Import direct option --ORA-00942: Table or view does not exist error

While testing out "--direct" option available to invoke "OraOOP" with Sqoop imports into HDFS, I have stumbled across the error -

"Ora-00942 Table or View Does not exist"

I am pretty sure that the table exist as the Sqoop Import has been tested using Generic JDBC Driver.

Going through Sqoop docs, it is mentioned that it requires several privileges as below:

The Oracle user for The Data Connector for Oracle and Hadoop requires the following roles and privileges:
  • create session
In addition, the user must have the select any dictionary privilege or select_catalog_role role or all of the following object privileges:
  • select on v_$instance
  • select on dba_tables
  • select on dba_tab_columns
  • select on dba_objects
  • select on dba_extents
  • select on dba_segments — Required for Sqoop imports only
  • select on dba_constraints — Required for Sqoop imports only
  • select on v_$database — Required for Sqoop imports only
  • select on v_$parameter — Required for Sqoop imports only

In addition to the listed roles and privileges in the doc, it also requires "SELECT_CATALOG_ROLE" for the Sqoop Import to succeed using "--direct" option.

After granting the role to the user using which the import is being performed, the Sqoop Import process succeeded.


4 comments:

Hive OpenCSVSerde - Impala error

SerDe library 'org.apache.hadoop.hive.serde2.OpenCSVSerde' I have a Hive external table created on a CSV file using the SerDe - &...