Getting Column References Using the API
TDV provides a system procedure that you can use to get column references and discover what views and columns directly reference a specified view column.
This procedure can be executed on a data source table or composite view to understand what upstream references each column in the table or view has. Only one reference level at a time is returned, but you can run the GetColumnReferences procedure repeatedly to discover the path of the references.
The GetColumnReferences procedure lets you:
• List all views and columns that directly reference a view.
• See the type of reference which might be a SELECT clause, a FROM clause, a WHERE clause, or a TIMESERIES clause, or other type of reference.
• See if the column was transformed.
• See who originally created the resources involved and when.
• After you have determined the column reference, you can run the GetColumnReferences procedure again on the referenced view or table to determine the next level of reference.
• Publish the GetColumnReferences procedure:
— As a new Data Service. You can then access it with the supported protocols like JDBC, ODBC, ADO.NET.
— As a new Web service which you can access using REST, SOAP, and the other supported protocols.
To get the column references for a view
1. In Studio, open the data source table or view containing the columns you want to learn about.
2. Click the Info tab and copy the full contents of the Name field which is the path for the view.
3. In the <localhost> directory, navigate to the lib/resource folder and open the GetColumnReferences procedure.
4. Click Execute. Studio prompts you to enter the input parameters for this procedure.
5. Paste the table or view pathpathname copied earlier into the resourcePath field.
6. Enter the other input values for the procedure as follows:
Parameter | Value Description |
resourcePath | Required. Enter the path of the resource to be analyzed. The supported resource types are TDV SQL Views or data source tables. |
columnFilter | Optional. Specify if the column lineage results should be filtered. If empty, no filtering occurs. To filter the results, enter a comma-separated sequence of case-insensitive column names, indicating the columns whose references should be analyzed. |
Note: The analysis is done ignoring whether the specified view or table is cached or not.
7. Click OK.
TDV executes the procedure and displays the column references in the lower Result For columnReferences panel.
The contents of the columns are described in the following table.
Column | Description |
columnName | The name of the resource column having the column reference encoded in the row. |
referentResourcePath | The path to the resource containing the column reference. Empty if not applicable. |
referentContext | The context in which the column is referenced: • In a WITH clause, to indicate a reference within a WITH clause. • In a SELECT clause as output, to indicate a reference that is projected by a SELECT clause. • In a SELECT clause as input, to indicate a reference that is used, but not projected, by a SELECT clause. • In a FROM clause, to indicate a reference within a FROM clause. • In a WHERE clause, to indicate a reference within a WHERE clause. • In a TIMESERIES clause, to indicate a reference within a TIMESERIES clause. • In a GROUP BY clause, to indicate a reference within a GROUP BY clause. • In a HAVING clause, to indicate a reference within a HAVING clause. • In an ORDER BY clause, to indicate a reference within an ORDER BY clause. |
referentColumnName | The name of the referent column. Populated only if referenceContext is in a SELECT clause as output; otherwise, empty. |
derivationKind | Indicates the type of derivation: • Direct, to indicate that the value of the dependency is preserved by the dependent column. • Indirect, to indicate that the value of the dependency is transformed by the dependent column. Populated only if referenceContext is in a SELECT clause as output; otherwise, empty. |
cardinalityInfo | When applicable, one of the following: • Aggregate, to indicate that an aggregate function is involved in the derivation of the dependent column. • Analytic, to indicate that an analytic function is involved in the derivation of the dependent column. Populated only if referenceContext is in a SELECT clause as output; otherwise, empty. |
reference | A textual representation of the column reference. |
8. To view the details of a column reference, select the reference and click the Details button in the Result For columnReferences panel. Studio displays the full details for the selected column reference.
9. Optionally, run the GetColumnReferences procedure again on the referenced view or table in the Result For columnReferences panel to determine the next level of reference.
You can run the GetColumnReferences procedure as many times as needed to discover the reference path.
10. Optionally, save the results as a Comma-Separated Values (*.csv) file by clicking Save to File on the Result For columnReferences panel.
11. Optionally, open any of the involved resources to see who originally created the resource and when on the resource Info tab.
