Adding & Managing Tables

Add a Managed Primary Table

  1. Navigate to Settings > Tables. 
  2. On the Tables page, click Add Table.
  3. Complete the General section. 
    1. Type a Name for the table. 
    2. Select a Schema for the table. The schema cannot be changed after the table is created. 
    3. Select Managed primary table as the Table type. 
  1. Expand the Advanced options section to utilize a periodic statement for the table. 
    1. Switch the Enable periodic statement toggle to enabled (a blue toggle).
    2. Enter the Periodic statement as an SQL statement. 
    3. Set a Period interval. 
  2. Type any Tags to be associated with the table. 
  3. You can type a Comment that will help users identify the table. 
  4. Click Next. 

  1. Complete the Parser Specification section.

    Parser Specification provides the source file structure, and any required data transformations and relates the input to target structures. For data types like CSV, XML, JSON, and Avro, the sources contain metadata for the fields, generally the field names. In these cases, Asset Manager uses this metadata to represent the table structure.  The number of columns in these sources could change, and Asset Manager would adapt to the representation of the structure.  This is particularly the case for XML and JSON. Sometimes, the parser specifications explicitly identifies each source field and relate the source fields to target fields. 

    Sample data. Asset Manager uses a sample of a source file to verify the data structure and field types. This is verified against the parser spec and instantiates the table within Asset Manager.

    Source data. Once the table is instantiated in Asset Manager, you can directly load data by dragging in the source file to this area.

    1. Upload a parser specification, sample data, and source data and then clickNext.
    2. ClickCreate Tableto instantiate the table in Asset Manager.  The new table displays among the list of tables.

Add an External (Built-in) Table

  1. On the Tables page, click Add Table.
  2. Complete the General section. 
    1. Type a Name for the table. 
    2. Select a Schema for the table. The schema cannot be changed after the table is created. 
    3. Select External table as the Table type. 
    4. Type any Tags to be associated with the table. 
    5. You can type a Comment that will help users identify the table. 
    6. Click Next. 
  3. For Connection options:
    • Select an external connection from the list. 
    • If not external connections, add one in Connections Manager. 
  4. Click Create table. 

Add a View Table

  1. On the Tables page, click Add Table.
  2. Complete the General section. 
    1. Type a Name for the table. 
    2. Select a Schema for the table. The schema cannot be changed after the table is created. 
    3. Select View as the Table type. 
    4. Type any Tags to be associated with the table. 
    5. You can type a Comment that will help users identify the table. 
    6. Click Next. 
  3. For View query, define the SQL query text to use. 
  4. Click Create table. 

Viewing, Editing & Deleting Tables

Table properties, fields, and indexes are available on the right-hand side of the Tables page. Buttons below the table properties enable you to view, edit, or delete the selected table.

  • Editpermits changing the table name, parser spec, sample file or source data file.
  • Deleteremoves the table.
  • Openshows all data in the table and the basic query (select * from table). 
  • Truncatecuts off a portion of the selected table. 

Exporting Managed Primary Tables from CLI 

Superusers can export table data to a JSON file for troubleshooting and analysis. Asset Manager may ask you to export your managed primary tables or to include that option when you gather diagnostics to see whether missing or problematic data in the table is causing a problem.

Asset Manager must be up and running before you start this procedure. To export managed primary tables, also known as "dumping the data warehouse," follow this procedure:

  1. Login to the CLI as a superuser such as admin.

  2. At the prompt, enter support db dump_x15 {destination} [port {port-number}]
    where . . . 
    destination     is either a file on the local system or a remote SCP target, and
    port-number is the port number of the remote SCP server if different from the default port (22)