Schema-Flexible Configuration with JDBC (Apache Hive 6.0) External System

Sneha Muthukumar
Sneha Muthukumar Member
in General

We've successfully configured an external system using Connection type: JDBC and DBMS: Apache Hive 6.0.

I’m looking for guidance on how to set up a Schema-Flexible configuration. Specifically:

  • Is it possible to use Schema-Flexible JDBC connections to support workflows that export data to multiple schemas?
  • For example, consider two databases:
    • Database A with tables T1 and T2
    • Database B with tables T3 and T4
      My workflow needs to export data into A.T1 and B.T4.
      Can this be achieved using a single Schema-Flexible JDBC external connection?
JDBC - Schema Screenshot.png

Any insights or examples would be greatly appreciated!

Tagged:

Comments

  • Mirjam Schuke
    Mirjam Schuke Administrator
    edited September 16

    Hi Sneha

    My colleague @Ian Buckle is suggesting the following:

    Two separate databases:

    • Create two separate External System configurations for each database connection. 
    • Create separate dataset for each table
    • Map and Export to the desired tables from one workflow

    Multiple schemas in one Database:

    • leave the schema field blank which will create a schema flexible configuration 
    • ensure the Force JDBC schema name is set to false
    • ensure credentials has appropriate permissions to the Schemas in the database
    • Create separate dataset for each table (Ensure to add Schema name in the dataset name to distinguish as Aperture doesn't do this natively during dataset configuration)
    • Map and Export to the desired tables from one workflow

    Schemas will appear like below for the same connection  

     

    image-91e35154786358-d852.jpeg

    Also to note, this is using the External System flexible schema functionality that introduced in 3.0.7.

    In regards to naming the default Dataset name should include both the schema name and table name, at least in the scenario where two schemas have the same table:

    image-60e27a51b6f04-9d87.jpeg

    The workaround is to refer to the Dataset metadata but that's only available in a workflow:

    image-cf0fef89c7fe7-8736.jpeg

    Hopefully this will help you.

Categories