How does AWS Glue crawler to exclude oracle schemas or oracle tables?

user_12205588 0
Viewed 705

The customer plans to exclude oracle schemas or oracle tables using Glue crawler?

Glue crawler doesn't support /database/schema/tables for Oracle and MySQL database, I tried many different patterns to exclude oracle schemas or oracle tables, but the result isn't predicated.

How do I design the exclude patterns for Oracle database or MySQL database?

include-pattern: test/%

the test is the oracle service name.

I have added the following patterns to exclude schema APEX_030200 and WWV staring tables:

  • APEX_030200.WWV*
  • APEX_030200*
  • test_apex_030200_wwv*
  • test.APEX_030200.WWV_*
  • test.APEX_030200.WWV*
  • test.APEX_030200*
  • test.APEX_030200*
  • WWV*

But the result is the crawler includes schema APEX_030200's all tables.

aws-glue
2 Answers

According to the Glue Crawler include and exclude patterns documentation, you can't specify a schema-name in your include path for database engines such as MySQL or Oracle, because they don't support a schema, at least in the sense of a namespace in a database.

  • In most RDBMSs, like MS SQL Server, a schema represents a namespace.
  • Oracle databases use the term differently, though. A schema is essentially the same thing as a user, as it represents objects owned by that user, independent of the database they're in.

So in the case of Oracle and MySQL, the exclude syntax is database-name/table-name, and not database-name/schema-name/table-name.

I don't know if there is a way to explicitly exlude what Oracle calls a schema.

user_1586680

To be able to exclude tables in Oracle, the pattern needs to have the following format

USER/EXCLUDE-PATTERN

Where USER is the table owner or the Oracle schema. PATTERN does honor the glob format described in the documentation. For example, I added

XXAPPS/P[!EORU]*

and in the crawler logs, got the confirmation that the table was excluded

enter image description here

user_12429850
Related