Skip to main content

Importing Schemas

This page describes how to import the schemas that make up the Data Catalog. Each of these schemas (not to be confused with SQL schemas) is associated with a specific external data source and describes the structure of a single data element such as a table or a delimited file provided by that data source.

After importing a schema, it is generally necessary to edit the details such as the data extraction strategy and field types.

Adding Tables from a JDBC Data Source

To add one or more tables from a JDBC data source:

  1. Click the Data Catalog icon in the application menu.

  2. Click Data Schema Importer.

  3. Select a JDBC data source from the Data Source dropdown menu.

  4. Select an SQL schema (group of tables) from the Schema dropdown list.

    The page then displays the tables in that SQL schema.

  5. Add tables to be imported in either of two ways:

    • Click Add All Members. This option adds all tables in the currently displayed SQL schema (group of tables).

    • Click the Add to Imports button in the row next to the table or tables.

    The table or tables are then listed on the right side of the page; the system has not yet imported their metadata.

  6. Optionally select a different item from the Schema dropdown list and repeat these steps as needed. This process then adds to the list on the right side of the page; the system has not yet imported their metadata.

  7. Optionally, to remove a table from the list on the right side of the page, click the Delete icon in the applicable row.

  8. To import the metadata for the selected tables, click Import.

    The system then imports metadata for all the selected tables and displays the Results of Last Import tab, which lists all the tables whose metadata it just imported.

  9. From the Results of Last Import tab, optionally start refining any of the imported schemas. To do so, click the Inspect icon in the applicable row. Then edit that item as described in Editing Schemas.

Adding an SQL Query from a JDBC Data Source

Instead of (or in addition to) adding tables from a JDBC data source, you can directly use a custom SQL SELECT query. To do so:

  1. Click the Data Catalog icon in the application menu.

  2. Click Data Schema Importer.

  3. Select a JDBC data source from the Data Source dropdown menu.

  4. Click the Query tab.

  5. Type an SQL SELECT statement into the box.

  6. Optionally modify Row Count, which controls the number of rows that are sampled and displayed.

  7. Click Run Query.

    The page then displays the query results, which you can use to verify that the query is as expected.

  8. To add a schema to the catalog based on this query, click Save to Catalog.

    The system then displays a dialog box.

  9. For Name, type a short, unique name for the new schema. You cannot change this name later.

  10. For Description, type an optional description of the new schema.

    The system then generates metadata for the query and displays the new schema, which you can now edit as described in Editing Schemas.

Adding a File-Based Schema

A file-based data source is a UTF-8 encoded file with one record per line, where each line follows a convention that uses a specific delimiter between fields (typically a comma or a tab). This convention implicitly defines a schema. To define this schema within InterSystems TotalView™ For Asset Management, you need to upload and then import a sample file and specify the delimiter and the field names. (Excel files are a special case of a file-based data source in which you do not need to specify the delimiter that separates fields.)

FileDir, ExcelSingleFileDir, and S3Delimited data sources are all file-based data sources.

To add a file-based schema:

  1. Obtain a sample file for the schema.

    The sample file can consist of only one line. Also, the sample file must have a header row—an initial line that contains the names of the fields. (In other words, the sample file may or may not contain any actual data.)

  2. Rename the sample file so it has an appropriate and useful short filename, because this short filename becomes the schema name within the system (with punctuation characters removed), and it cannot be edited.

    For example, if you load a sample file named sampledata.csv, that becomes a schema named sampledatacsv.

  3. Click the Data Catalog icon in the application menu.

  4. Click Data Schema Importer.

  5. Select the appropriate file-based data source (the data source to which this schema belongs).

    The Available Members section lists any files that you have previously uploaded to the file system. (Specifically this section lists the files in the Samples subdirectory for this data source.)

    Here you can do either or both of the following:

    • Upload new schema files to the file system, possibly overwriting the files listed in Available Members

    • Import the files listed in Available Members (creating or updating schemas)

    No single file can be larger than 500 MB.

  6. Drag and drop one or more files from your local computer to the box labeled Upload file(s) no bigger than 500 MB.

    Or click this box, browse to the directory that contains the files, and then select one or more files.

    As you do this, the list in Available Members is updated.

  7. Click the Add to Imports button in the row next to the file or files to import.

  8. If the right side of the page contains a long list of files to import, you may want to double check the list. In doing so, if you want to filter this display, you can type into the Members Selected for Import filter. This option affects what this page displays, but does not affect the import of metadata.

  9. Optionally, to remove a file from this list, click the Delete icon in the applicable row.

  10. To import the selected files, click Import.

    The system then imports all the selected files and generates metadata from them.

    The page then displays the Results of Last Import tab, which lists all the files it imported.

  11. From the Results of Last Import tab, optionally start refining any of the imported schemas. To do so, click the Inspect icon in the applicable row. Then edit that item as described in Editing a Schema.

Filter Options When Importing Schemas

Depending on the type of data source, there may be a large number of schemas to choose among. The import page provides options to help you filter the display, as follows:

  • On the left side, below Select Schemas to Import, there are options to filter the schemas that are shown on this side of the page. First, optionally select a different display option from the following set:

    • All—all the tables in this SQL schema (default)

    • In Catalog—only the tables that are also currently in catalog

    • Not in Catalog—only the tables that are not currently in the catalog

    The list is updated automatically when you select an option.

    Second, optionally type a string into the Available Members filter. This filters the display so that only names containing the given string are shown.

  • On the right side, next to Members Selected for Import, you can type a string to filter the list shown in this section of the page. This filters the display so that only names containing the given string are shown.

    For a JDBC data source, the page also provides the Filter Selected Schemas option. For this option, you can select a SQL schema (group of tables) to display, as another way to filter what is shown.

    Important:

    These filters do not affect what is imported when you click Import. The only change is to the display of the list on the right side of the page.

See Also

Editing and Managing SchemasNext section
Previous sectionWelcome, Data Engineers
FeedbackOpens in a new tab