Editing and Managing Schemas

Schema Evolution

One of the key objectives is to permit schemas to change while preserving your data. This works as follows:

1. When you import a schema and first edit it, the schema is a draft and cannot be used by recipes.

2. When you publish a schema, it can then be used by recipes—specifically in a staging activity (which loads data into the system).

3. When you define a recipe, each of its activities is initially a draft.

4. When you publish a staging activity of a recipe, the system then generates the table that will store the data to be loaded.

5. When you edit or reimport a schema, you are implicitly creating a draft that is not yet used by anything. The existing schema is unchanged; existing data is also unchanged.

   While a schema is in draft state, you have the option of deleting the draft (and reverting to the previous published version).

6. When you republish a schema, if the change affects the structure needed to contain the data, the system automatically increments the version number of the schema and automatically generates a new draft of any recipe staging activity that is affected by this change. The new staging activity draft is not automatically published, and the previous staging activity is unchanged.

7. When you republish a staging activity, the system generates a new table as needed, considering the structural change that has occurred. This leaves the old table (and its data) unchanged.

Viewing the Data Catalog

To view the Data Catalog and the schemas currently contained in it:

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

2. Click Data Catalog Browser.

   This page enables you to see all available schemas, either as a table or as a set of rectangular cards.

   For each schema, the page displays the name of the data source that contains this schema, the actual name of the schemas, and any categories to which it belongs. If you click Card View, the cards also display the descriptions.

3. Optionally filter the display by using the options at the top of the page:

   • Schema Definition Item Name—Filters the display to include only the schemas whose short names contain the given string. After typing into this field, click the Find icon.

   • Data Source—Filters the display to include only schemas from a specific data source.

   • Category—Filters the display to include only schemas labelled with a specific category. Note that schemas can be labelled with any number of categories, including no categories at all.

   • Recipe—Filters the display to include only schemas used in a specific recipe.

   • Drafts —If selected, displays only the schemas that are drafts.

   • Reset —Clears all the filter options.

Click any schema in order to view it or make changes.

Displaying a Schema

If you have just imported the schema and you are viewing the Results of Last Import tab, click the Inspect icon in the row for a schema.

Otherwise, to display a schema:

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

2. Click Data Catalog Browser.

3. Click the schema name.

   If there is a draft of the schema, you can choose to display the published version or display the draft (which is shown as a separate item as in the following example).

   

Editing a Schema

To edit a schema, first display it. If the schema has been published, click Edit Schema in the upper right to make the schema editable. (If there is a draft of the schema and you are viewing the published version, this button opens the draft version of the schema.)

Then make any of the following changes:

• To add or edit a description, click the Edit icon next to Description and then make edits.

• To add a category, click Add Category... and then click a category.

• To remove a category, click the X icon for the category.

• To modify the details in the box on the right, click the Edit icon in that right box. This displays a dialog box where you can make changes. The details depend on the kind of schema.

  For a file-based schema, Extraction Strategy is always Simple Load, but you can specify the following items:

  • FileNamePattern—Specifies which files are read and used as input, for this schema. That is, input files whose names conform to this pattern are processed, and other files are ignored. You can specify any legal filename string or a filename wildcard expression, consistent with the operating system.

  • Header Row—Specifies whether the files have a header row. Select Yes or No.

  For an SQL-based schema (either a table or a custom query), you can specify Extraction Strategy, which specifies the records to get from this table or query, when running a recipe that uses this schema. The options are as follows:

  • General table—Retrieves all new records and any changed records.

    A new record is a record with a new primary key value. A changed record is a record with an existing primary key value but a change to one or more other fields.

  • Append only transactional table with a numeric sequential key —Retrieves all new records since the last sequential primary key.

    If you specify this, also specify Extraction Strategy Field, which must be the name of the field that contains the sequential primary key.

  • Transactional table with a changed timestamp field—Retrieves all records changed since the last timestamp.

    If you specify this, also specify Extraction Strategy Field, which must be the name of the field that contains the timestamp.

  • Simple load—Retrieves all records, whether or not the system has them already.

  For an SQL-based schema (other than one using a custom query), you can specify the following additional items:

  • SchemaName—Specifies the SQL schema (group of tables) to which the source table belongs.

  • TableName—Specifies the short table name of the source table.

  • UsePrimaryKeyPseudocolumn—Specifies whether to use a pseudocolumn in this table as the primary key. If you select this option, also specify PrimaryKeyPseudocolumnName, which must be the name of the pseudocolumn.

  When you are done, click Save to save these changes or Cancel to discard them.

• To make changes to the schema fields, see the next section.

When you are done with the schema, click Save Draft to save these changes or Delete Draft to discard them (and return to the previous schema definition).

Note that if you leave this page without saving your changes, the system automatically saves your changes as a draft.

Editing Schema Fields

For any kind of data source, when you edit the schema, the bottom part of the page displays a table labeled Schema Fields. Here you can modify the schema fields as follows:

• To specify the primary key for this schema, click the Primary Key check box for all the fields that make up the primary key.

• To modify other details for a specific field, click the Edit Attributes icon for that field. This displays a dialog box where you can edit the following:

  • Data Type—Specifies the data type for this field.

  • Required—Specifies whether this field is permitted to be null. If a field is required, an error is thrown if the value is null.

  • Default Value—Specifies the value to use if this field is null.

  • Data Format (for date, time, and timestamp fields)—Select the format that data is expected to be in for this field.

  • Min Value (for double and integer fields)—Specifies the minimum allowed value of this field.

  • Max Value (for double and integer fields)—Specifies the maximum allowed value of this field.

  • Scale Value (for double fields)—Specifies the number of digits to display after the decimal point. This has no effect on how data is stored.

  • Length Value (for string fields)—Specifies the maximum number of characters allowed in this field.

  When you are done with this dialog box, click Save to save these changes or Cancel to discard them.

Publishing a Schema

A schema cannot be used in recipes unless it has been published. To publish a schema:

1. Display it.

2. Click Publish Schema in the upper right.

Reimporting a Schema

To reimport a schema:

1. Display it.

2. Click Re-import Schema in the upper right. This change automatically creates a new draft of the schema.

3. Edit the schema again as needed.

If the schema has not yet been published, you cannot reimport it. You can, however, delete it and then import it again.

Deleting a Schema

To delete a schema:

1. Display it.

2. Click Delete in the upper right.

3. Click Delete to confirm.

You cannot delete a schema that is used in a recipe; if you attempt to do so, you are notified about any recipe activities that depend on this schema. You can then go edit the recipe to remove the dependency.

See Also

• Importing Schemas

• Defining Categories

• Defining Recipes