This Snap outputs a list of tables in a database. The Snap will connect to the database, read its metadata, and output a document for each table found in the database. The table names are output in a topological order so that tables with the fewest dependencies are output first. In other words, if table A has a foreign key reference to table B, then table B will be output before A. The ordering is intended to ease the process of replicating a group of tables from one database to another.

• Expected input: [None]
• Expected output: Documents with the following fields:

• • name -  The fully-qualified name of the table. To use the table name in another Snap, like a Select or Insert, you can pass it through a ForEach Snap to another pipeline with the Select or Insert.
  • type - The type of table. This value is currently fixed to the string "TABLE".
  • dependents - (If Compute table graph is selected) A list of table names that have references to this table, including this table.

Replicating a Subset of Tables

The output of the Table List Snap can be directly used to replicate an entire database.  However, if you are only interested in a subset of tables, you can use a Filter Snap to select the table names you are interested in as well as the tables that they reference. For example, given the following diamond-shaped table graph where A depends on B and C and they both depend on D:

       A
      / \
     B   C
      \ /
       D

The Table List will output the following documents:

name=D; dependents=[A, B, C, D]
name=C; dependents=[A, C]
name=B; dependents=[A, B]
name=A; dependents=[A]

So, if you wanted to copy just table 'A' and its dependencies, you can add a Filter Snap with the following expression:

 $.dependents.indexOf('A') != -1

The filter will then remove any extra tables that happen to be in the schema.

ETL Transformations & Data Flow

This snap is a data source. It works by performing a standard JDBC DatabaseMetaData#getTables() query for the tables within the database. The Schema name value is used to populate the schemaPattern parameter in that query.

This snap does not require any temporary files or other external resources.

Input & Output

• Input:  [None]

  Note
  Any input documents are ignored.

  • Input Schema Provided: No
    
    
  • Output: This snap produces one document per table upon successful execution. The fields are:
    
  • 
    
    • name -  The fully-qualified name of the table. To use the table name in another Snap, like a Select or Insert, you can pass it through a ForEach Snap to another pipeline with the Select or Insert.
    • type - The type of table. This value is currently fixed to the string "TABLE".
    • dependents - (If Compute table graph is selected) A list of table names that have references to this table, including this table.
      
      
  • Output Schema Provided: Yes
    
    
  • Preview Supported: Yes
    
    
  • Passthrough Supported: No
    
    
  • Output Examples:
    
    

    • Example output upon successful execution without Compute table graph selected:

      Code Block
      [ { "name": "\"demo\".\"'redshiftbulkload'\"", "type": "TABLE" }, { "name": "\"demo\".\"'shankaradp'\"", "type": "TABLE" }, { "name": "\"demo\".\"'shankaradp1'\"", "type": "TABLE" }, { "name": "\"demo\".\"'shankardemo'\"", "type": "TABLE" }, { "name": "\"demo\".\"'shankardemo1'\"", "type": "TABLE" } ]

      
      

    • Example output upon successful execution with Compute table graph selected:

      Code Block
      { "name": "\"demo\".\"account\"", "type": "TABLE", "dependents": [ "\"demo\".\"account\"" ] }, { "name": "\"demo\".\"account_transaction\"", "type": "TABLE", "dependents": [ "\"demo\".\"account_transaction\"" ] }, { "name": "\"demo\".\"accounts_oy\"", "type": "TABLE", "dependents": [ "\"demo\".\"accounts_oy\"" ] } ]

      
      

Expected upstream Snaps: Any Snap with a document output view. Note: the contents of  the input view are ignored so pipelines should only be used to sequence operations.

Expected downstream Snaps: Any Snap with a document input view, such as JSON Formatter, Mapper, and so on. The CSV Formatter Snap cannot be connected directly to this Snap since the output document map data is not flat.

Modes

• Ultra pipelinesPipelines: Works in Ultra Pipelines.Spark mode: Not supported in Spark mode.

Computes the dependents among tables and returns each table with a list of tables it has foreign key references to. The ordering of outputted tables is from least dependent to most-dependent. 

Default value: Not selected