Skip to main content
Last updated on

Work with metric view metadata using the Databricks JDBC Driver

The Databricks JDBC Driver supports enhanced metadata operations that allow business intelligence (BI) tools and applications to discover metric views and identify measure columns within them using standard JDBC metadata methods.

BI tools can filter and display metric views separately in data source browsers, generate appropriate SQL by distinguishing measures from dimensions, and build rich semantic layer capabilities. Data exploration tools can help users discover and understand available business metrics across your catalog.

Enable enhanced metadata​

Enhanced metadata for metric views is disabled by default to maintain backward compatibility. Enable it using either a JDBC connection property or the Spark SQL configuration setting.

Set the EnableMetricViewMetadata connection property to 1 when establishing a JDBC connection.

Java
Properties properties = new Properties();
properties.setProperty("UID", "<username>");
properties.setProperty("PWD", "<password>");
properties.setProperty("EnableMetricViewMetadata", "1");

String url = "jdbc:databricks://<workspace-host>:443/default;httpPath=<http-path>";
Connection connection = DriverManager.getConnection(url, properties);

Alternatively, set spark.databricks.metadata.metricview.enabled to 1 in your SQL session:

Java
Connection connection = DriverManager.getConnection(url, properties);
Statement statement = connection.createStatement();
statement.execute("SET spark.databricks.metadata.metricview.enabled=1");
// Metadata operations now return enhanced metric view information

Identify metric views​

Use the DatabaseMetaData.getTables() method to discover metric views. With enhanced metadata enabled, this method returns METRIC_VIEW as the TABLE_TYPE for metric views, so you can distinguish them from regular views and tables.

Java
Connection connection = DriverManager.getConnection(url, properties);
DatabaseMetaData metadata = connection.getMetaData();
ResultSet tables = metadata.getTables(catalog, schema, "<metric_view>", null);

while (tables.next()) {
    String tableName = tables.getString("TABLE_NAME");
    String tableType = tables.getString("TABLE_TYPE"); // Returns "METRIC_VIEW"
    System.out.println("Metric View: " + tableName);
}

Identify measure columns​

Use the DatabaseMetaData.getColumns() method to identify measure columns within a metric view. With enhanced metadata enabled, the TYPE_NAME column returns <data_type> measure for measure columns (for example, int measure, double measure).

Java
Connection connection = DriverManager.getConnection(url, properties);
DatabaseMetaData metadata = connection.getMetaData();
ResultSet columns = metadata.getColumns(catalog, schema, metricViewName, "%");

while (columns.next()) {
    String columnName = columns.getString("COLUMN_NAME");
    String typeName = columns.getString("TYPE_NAME");

    if (typeName.endsWith(" measure")) {
        System.out.println("Measure column: " + columnName + " (" + typeName + ")");
    } else {
        System.out.println("Dimension column: " + columnName + " (" + typeName + ")");
    }
}

Filter by table type​

Use the DatabaseMetaData.getTableTypes() method to discover available table types in your catalog. With enhanced metadata enabled, this method includes METRIC_VIEW in the list of available table types.

Java
Connection connection = DriverManager.getConnection(url, properties);
DatabaseMetaData metadata = connection.getMetaData();
ResultSet tableTypes = metadata.getTableTypes();

while (tableTypes.next()) {
    String tableType = tableTypes.getString("TABLE_TYPE");
    System.out.println("Available table type: " + tableType);
    // Output includes: TABLE, VIEW, METRIC_VIEW, ...
}

Backward compatibility​

By default, EnableMetricViewMetadata is set to 0 to maintain backward compatibility with existing applications. Databricks returns metric views as regular VIEW type, and measure columns show their base data type without the measure suffix.

Next steps​