Importing Schemas (2.12)

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 drop-down menu.

4. Select an SQL schema (group of tables) from the Schema drop-down list.

   The section Available Items from JDBC Source then displays the tables in the selected SQL schema.

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

   • Click the check box next to each table name and then click Select for Import .

   • Click the Add to Imports button in the applicable row or rows.

   The table or tables are then moved to the section Items Selected For Import ; the system has not yet imported their metadata.

6. Optionally select a different item from the Schema drop-down list and repeat these steps as needed. This process adds to the list in Items Selected For Import ; the system has not yet imported their metadata.

7. Optionally, to remove a table from the list in Items Selected For Import , 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.

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 drop-down menu.

4. Click the SQL 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 the product, 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, applicable to a specific file-based data source.

   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. Use the File Manager to upload this file to the Samples directory used by the chosen file-based data source.

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

5. Click Data Schema Importer .

6. Select the chosen file-based data source.

   The section Available Items from Source lists the files in the Samples subdirectory for this data source.

7. Add files to be imported in either of two ways:

   • Click the check box next to each filename and then click Select for Import .

   • Click the Add to Imports button in the applicable row or rows.

   The filename or filenames are then moved to the section Items Selected For Import ; the system has not yet imported their metadata.

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.

Adding a REST Schema

After you create a REST data source, you must define both the response-schema metadata and the API metadata in the REST Registry before you can import your schema into the Data Catalog. The REST Registry is available at Data Catalog > REST Registry.

To define a REST schema:

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

2. Click REST Registry.

3. On the REST Schema tab, define the response schema metadata:

   • Vendor (Required) — Type the vendor name. This value becomes the schema name within the system and cannot be changed later.

   • Data Set (Required) — Type the name of the data set represented by this schema.

   • Field Name (Required) — Type the name of a field returned in the API response.

   • IRIS Field Type (Required) — Choose a field type from the list.

   • Description (Optional) — Type an optional description for the schema or individual fields.

4. Click Insert to add the schema entries.

5. On the REST Endpoint tab, define the API metadata that tells Data Studio how to retrieve data from the REST API:

   • Vendor (Required) — Type the vendor name. This value must match the vendor defined in the REST Schema tab.

   • Data Set (Required) — Type the data set name that this endpoint returns.

   • API Verb (Required) — Select GET or POST.

   • API Path (Optional) — Type the path or full URL for the REST API call (for example, /api/csaperiods). If you provide a relative path, it is appended to the API Server defined for the data source.

   • API Input (Optional) — Provide JSON arguments for the API request (for example, {"sinceDateTimeUtc":"20250121134009"}).

   • Load Type (Optional) — Choose simple or transactional. Simple loads retrieve data exactly as defined. Transactional loads update input parameters on subsequent runs and are typically used when retrieving incremental or time-based data.

   • Redirected Path (Optional) — Specify the JSON or XML path that identifies the target data within the API response. Use this field when the response contains metadata, nested objects, or references to the data you want to ingest.

     Depending on the structure of the API response, the target data may be:

     • The entire response object.

     • A nested object within the response.

     • Data referenced indirectly, which requires Data Studio to follow response values to retrieve the final dataset.

6. Click Insert.

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. You can filter and sort the items listed in the Available Items From Source section on the left, as well as the items listed in the Items Selected For Import on the right. In both areas, you can do the following:

• To show only names containing a specific string, type that string into the filter above the Item Name column.

• To display items based on whether they are in the catalog, click the drop-down list above the In Catalog column and select either or both of the following check boxes as needed:

  • Yes —display the items that are currently in the catalog.

  • No —display the items that are not currently in catalog.

• You can click the Item Name and Item Name column headers to sort those columns.

See Also

• Defining Data Sources

• Using the File Manager

• Editing and Managing Schemas

• Defining Categories