Adding Schemas and Tables to Metastore Cache
Intended audience: ANALYSTS DEVELOPERS ADMINISTRATORS
AO Platform: 4.3
Overview
This section contains the details for adding Data Source Tables from Database Schemas into a Metadata Cache. This is new functionality introduced in the 4.2 release of the AO Platform with a goal to improve performance when working with large databases with hundreds of Schemas and thousands of Tables. Any Tables accessed from a database data source will be loaded into the Metastore Cache from which all subsequent retrieval will be done relative to Tables and their field structures.
The functionality to select Tables to be added to the Metastore Cache as well as subsequent access to this information has been unified for both Ontologies and MSOs. This page highlights the key access points for the functionality and the various dialogs supporting these processes. Look for the options to View Tables or Select Tables.
Ontology Composer - Sourcing | Ontology Discovery - |
|---|---|
 |  |
MSO Composer - Sourcing | Create MSO |
 |  |
Viewing Tables
Whether in the Ontology Composer or the MSO Composer, whenever the View or Select Tables are available, the following dialog will show. Before the first Table has been added, the user will see the three button options to add Table(s) and a short description of what the options do.
Empty Metastore Cache
Populated Metastore Cache
This dialog will show the various options for adding Tables to the Metastore Cache, Search for Tables, Refresh, and Delete Tables. In the main content area, a list shows the Tables that have already been imported into the Metastore Cache with the following columns:
Table Name - this shows the Schema and Table name, using the [schema].[table] syntax, eg. public.meter - if a Table entry has been mapped to an MSO, it’ll also include the MSO name.
Status - this shows the Status of the Table relative to its Data Source and MSO mapping:
Available - the table is available in the data source, but not yet mapped to an MSO.
Not Found - the table is no longer available in the data source.
Rows - shows the number of records (rows) in the Table.
Columns - show the number of fields (columns) in the Table.
Refreshed By - shows the username who added/refreshed the Table from the Data Source.
Refreshed At - shows the time the user added/refreshed the Table from the Data Source.
User Actions
Checkbox - click the checkbox at the beginning of one or more entries to Refresh or Delete the entry/ies.
Options menu
View Field Metadata - opens a dialog showing a list of the fields in the Table.
Refresh - refreshes the Table in Metastore Cache from the data source.
Delete - deletes the Table in the Metastore Cache. For any Tables mapped to MSOs, the mapping between the Table and the MSO shall be removed before the Table can be deleted.
View Tables in Cache | View Field Metadata |
|---|---|
 |  |
Selecting Tables to be Added to Cache
There are three options for adding Tables from a Data Source Schema into the Metastore Cache:
Select Tables - opens a dialog that allows the user to select a Schema and then one or more Tables from that Schema.
Import All Tables - will import all tables from a Schema. Use this option if the Data Source Schema contains a limited amount of Tables.
A few dialogs will alert the user if the Schema contains many tables. If there are many Tables in the Schema, the Import All Table Configurations dialog will offer two options:
The first [100] Tables
All Tables (1000 maximum)
Both values for the options above can be configured as part of the masterdata in the Admin solution. See Admin Configurations section below.
Add Manually - opens a dialog that allows the user to enter the Schema and Table name path for import, using the [schema].[table] syntax, eg. public.meter
Select Tables | Import All Tables | Add Manually |
|---|---|---|
 |   |  |
Admin Configurations
Configurations > Configuration Properties
There are a few properties on the Configuration Properties page in the Admin solution where the masterdata can be configured relating to Table import from a Data Source Schema:
Default Table Count - default: 100
Maximum Table Limit - default: 1000
Connections > Databases and BigQuery
Three additional properties have been added to the Create/Edit Databases dialog and the Create/Edit BigQuery dialog. The main purpose of these properties is to improve access and performance when working with large database data source and schemas.
Use Information Schema for Metastore - on/off toggle - default: on. If enabled, the data source schema can be accessed and searched. If disabled, only the “Add Manually” option will be available in the Select Tables dialogs.
Is Schema Searchable - on/off toggle - default: on. If enabled, a Schema Pattern can be provided to search specific schemas in the data source.
Allow Schema Pattern - enter one or more schema names as a comma-separated list and/or a schema name using wildcards, including ? and * to indicate which schemas can be accessed. Restricting schema access to only a select few will greatly improve performance in large database environments. Example: ao* will allow access to any schemas, such as ao1, ao-abc
Create/Edit Databases | Create/Edit BigQuery |
|---|---|
 |  |