Datamart Migration Actions¶

A datamart migration action refers to the process of transferring, transforming, and adapting a datamart from Aunsight to Exasol. A datamart is a subset of a data warehouse that is designed to support the needs of a specific user community. It contains a focused set of data that is relevant to the analytical and reporting requirements of that particular group.

Users can utilize datamart actions to quickly execute a specific datamart migration tasks, an example would be renaming a column. This method allows users to easily make the changes they want without having to make complex alterations to the overall schema. This makes the process of moving the data smoother and more efficient.

Below you will find Aunsights supported Migration Actions, JSON objects ready to copy and paste, and the instructions for filling them out.

Create Table¶

action: create-table: Keep this as it is, as it specifies the action type as create-table.
name: Create Table Action: You can modify the value Create Table Action to give the action a different name if desired.
table: your_table_id: Replace your_table_id with the actual ID or name you want to assign to the new table.
columns: [...]: This key represents an array of column definitions for the new table. You can add, modify, or remove columns as needed. Each column definition consists of the following properties:
column: column1: Replace column1 with the name of the first column in the table.
type: string: Replace string with the appropriate data type for the first column.
exasolType: VARCHAR(20000): Replace VARCHAR(20000) with the desired Exasol data type for the first column.
position: 0: Replace 0 with the position of the first column in the table (zero-based index).
metadata: {...}: This section contains additional metadata about the column. You can adjust the properties as needed.
metadata: {...}: This key contains metadata related to the table. Update the following properties:
description: This is the table description: Replace the value with a description of the new table.
owner: John Doe: Replace John Doe with the actual owner's name.

Note: Metadata types are not limited to the examples provided

{ 
  "action": "create-table",
  "name": "Create Table Action",
  "table": "your_table_id",
  "columns": [
    {
      "column": "column1",
      "type": "string",
      "exasolType": "VARCHAR(20000)",
      "position": 0,
      "metadata": {
        "type": "string",
        "pigType": "chararray"
      }
    },
    {
      "column": "column2",
      "type": "integer",
      "exasolType": "INTEGER",
      "position": 1,
      "metadata": {
        "type": "integer",
        "pigType": "long"
      }
    }
  ],
  "metadata": {
    "description": "This is the table description",
    "owner": "John Doe"
  }
}

Drop Table¶

action: drop-table: This key-value pair specifies the action you want to perform, which is to drop (delete) a table. The value drop-table indicates the action type and should not be changed.
name: Drop Table Action: This key-value pair represents the name of the action, which is Drop Table Action. You can modify the value Drop Table Action to set a different name for this action if needed.
table: your_table_id: Here, replace your_table_id with the actual ID or name of the table you want to drop (delete).

{
  "action": "drop-table",
  "name": "Drop Table Action",
  "table": "your_table_id"
}

Rename Table¶

action: rename-table: This key-value pair specifies the action you want to perform, which is to rename a table. The value rename-table indicates the action type and should remain unchanged.
name: Rename Table Action: This key-value pair represents the name of the action, which is Rename Table Action. You can modify the value Rename Table Action to assign a different name to this action if needed.
table: your_new_table_id: Replace your_new_table_id with the new ID or name that you want to assign to the table after renaming.
from: your_old_table_id: Replace your_old_table_id with the current ID or name of the table that you want to rename.

{
  "action": "rename-table",
  "name": "Rename Table Action",
  "table": "your_new_table_id",
  "from": "your_old_table_id"
}

Set Table Metadata¶

action: set-table-metadata: This key-value pair specifies the action you want to perform, which is to set metadata for a table. The value set-table-metadata indicates the action type and should remain unchanged.
name: Set Table Metadata Action: This key-value pair represents the name of the action, which is Set Table Metadata Action. You can modify the value Set Table Metadata Action to assign a different name to this action if needed.
table: your_table_id: Replace your_table_id with the ID or name of the table for which you want to update the metadata.
metadata: {...}: This key contains the metadata information that you want to set for the table. Update the following properties:
description: This is the updated table description: Replace the value with the updated description for the table.
owner: John Doe: Replace John Doe with the name of the new owner.
created_at: 2023-07-07: Replace 2023-07-07 with the desired creation date for the table.

Note: Metadata types are not limited to the examples provided

{
  "action": "set-table-metadata",
  "name": "Set Table Metadata Action",
  "table": "your_table_id",
  "metadata": {
    "description": "This is the updated table description",
    "owner": "John Doe",
    "created_at": "2023-07-07"
  }
}

Create View¶

Creates a native view in Exasol with the provided query. The declared columns must exactly match what the query would produce. Datamart builders should use the query tool to determine the appropriate query and its schema against the existing tables/views before attempting to create the view. As such, it can be error-prone to create views, but once the correct query & schema are determined, it can greatly improve the datamart’s storage footprint as data does not need to be duplicated.

The query is written using the AU('table/view') format and can only refer to tables/views within the datamart. All referenced tables/views must either already exist or be created as part of that migration. Aunsight will validate that all view/table dependencies exist and automatically rearrange the create-view actions so that dependencies are created before they are needed. While it is possible for views to depend on other views, it is not recommended for better performance and easier management. It is not possible to alter a view in place. Instead it must be dropped and re-created.

action: create-view: This key-value pair specifies the action to be performed, which is to create a view. The value create-view should not be changed as it indicates the action type.
name: Create View Action: This key-value pair represents the name of the action, which is Create View Action. You can modify the value Create View Action to set a different name for this action if needed.
view: your_view_id: Here, you need to provide the ID of the view you want to create. Replace your_view_id with the actual ID you want to use for the view.
query: SELECT column1, column2 FROM AU('your_table_id'): This key-value pair contains the SQL query that will be executed to create the view. Replace your_table_id with the actual ID of the table you want to reference in the query.

Note: Columns need to accurately reflect the schema which the query would produce

columns: [...]: This key represents an array of column definitions for the view. The example contains two columns with their respective properties. You can modify or add more columns as needed. Each column definition has the following properties:
column: column1: Replace column1 with the name of the first column in the view.
type: string: Replace string with the appropriate data type of the first column.
exasolType: VARCHAR(20000): Replace VARCHAR(20000) with the appropriate Exasol data type for the first column.
position: 0: Replace 0 with the position of the first column in the view (zero-based index).
metadata: {...}: This section contains additional metadata about the column. You can modify or add more properties as needed.
metadata: {...}: This key contains metadata related to the view. You can update the following properties:
description: This is the view description: Replace the value with a description of the view.
owner: John Doe: Replace John Doe with the actual owner's name.
created_at: 2023-07-07: Replace 2023-07-07 with the date the view was created.

Note: Metadata types are not limited to the examples provided

{
  "action": "create-view",
  "name": "Create View Action",
  "view": "your_view_id",
  "query": "SELECT column1, column2 FROM AU('your_table_id')",
  "columns": [
    {
      "column": "column1",
      "type": "string",
      "exasolType": "VARCHAR(20000)",
      "position": 0,
      "metadata": {
        "type": "string",
        "pigType": "chararray"
      }
    },
    {
      "column": "column2",
      "type": "integer",
      "exasolType": "INTEGER",
      "position": 1,
      "metadata": {
        "type": "integer",
        "pigType": "long"
      }
    }
  ],
  "metadata": {
    "description": "This is the view description",
    "owner": "John Doe",
    "created_at": "2023-07-07"
  }
}

Drop View¶

action: drop-view: This key-value pair specifies the action you want to perform, which is to drop (delete) a view. The value drop-view indicates the action type and should remain unchanged.
name: Drop View Action: This key-value pair represents the name of the action, which is Drop View Action. You can modify the value Drop View Action to assign a different name to this action if needed.
view: your_view_id: Replace your_view_id with the ID or name of the view that you want to drop (delete).

{
  "action": "drop-view",
  "name": "Drop View Action",
  "view": "your_view_id"
}

Set View Metadata¶

action: set-view-metadata: This key-value pair specifies the action you want to perform, which is to set metadata for a view. The value set-view-metadata indicates the action type and should remain unchanged.
name: Set View Metadata Action: This key-value pair represents the name of the action, which is Set View Metadata Action. You can modify the value Set View Metadata Action to assign a different name to this action if needed.
view: your_view_id: Replace your_view_id with the ID or name of the view for which you want to update the metadata.
metadata: {...}: This key contains the metadata information that you want to set for the view. Update the following properties:
description: This is the updated view description: Replace the value with the updated description for the view.
owner: John Doe: Replace John Doe with the name of the new owner.
created_at: 2023-07-07: Replace 2023-07-07 with the desired creation date for the view.

Note: Metadata types are not limited to the examples provided

{
  "action": "set-view-metadata",
  "name": "Set View Metadata Action",
  "view": "your_view_id",
  "metadata": {
    "description": "This is the updated view description",
    "owner": "John Doe",
    "created_at": "2023-07-07"
  }
}

Create Column¶

action: create-column: This key-value pair specifies the action you want to perform, which is to create a new column. The value create-column indicates the action type and should remain unchanged.
name: Create Column Action: This key-value pair represents the name of the action, which is Create Column Action. You can modify the value Create Column Action to assign a different name to this action if needed.
table: your_table_id: Replace your_table_id with the ID or name of the table to which you want to add the new column.
column: your_column_id: Replace your_column_id with the ID or name you want to assign to the new column.
type: string: Replace string with the desired data type for the new column.
position: 0: Replace 0 with the position where you want the new column to be inserted (zero-based index).
metadata: {...}: This key contains additional metadata about the new column. Update the following properties:
description: This is the column description: Replace the value with a description of the new column.
nullable: true: Replace true with false if the column should not allow NULL values.
type: string: Replace string with the appropriate data type of the new column.
pigType: chararray: Replace chararray with the Pig data type, if applicable.

{ 
  "action": "create-column",
  "name": "Create Column Action",
  "table": "your_table_id",
  "column": "your_column_id",
  "type": "string",
  "position": 0,
  "metadata": {
    "description": "This is the column description",
    "nullable": true,
    "type": "string",
    "pigType": "chararray"
  }
}

Drop Column¶

action: drop-column: This key-value pair specifies the action you want to perform, which is to drop (delete) a column. The value drop-column indicates the action type and should remain unchanged.
name: Drop Column: This key-value pair represents the name of the action, which is Drop Column. You can modify the value Drop Column to assign a different name to this action if needed.
table: your_table_id: Replace your_table_id with the ID or name of the table from which you want to drop the column.
column: your_column_id: Replace your_column_id with the ID or name of the column you want to drop.

{
  "action": "drop-column",
  "name": "Drop Column",
  "table": "your_table_id",
  "column": "your_column_id"
}

Rename Column¶

action: rename-column: This key-value pair specifies the action you want to perform, which is to rename a column. The value rename-column indicates the action type and should remain unchanged.
name: Rename Column Action: This key-value pair represents the name of the action, which is Rename Column Action. You can modify the value Rename Column Action to assign a different name to this action if needed.
table: your_table_id: Replace your_table_id with the ID or name of the table containing the column you want to rename.
column: your_new_column_id: Replace your_new_column_id with the new ID or name you want to assign to the column after renaming.
from: your_old_column_id: Replace your_old_column_id with the current ID or name of the column that you want to rename.

{
  "action": "rename-column",
  "name": "Rename Column Action",
  "table": "your_table_id",
  "column": "your_new_column_id",
  "from": "your_old_column_id"
}

Set Column Metadata¶

action: set-column-metadata: This key-value pair specifies the action you want to perform, which is to set metadata for a column. The value set-column-metadata indicates the action type and should remain unchanged.
name: Set Column Metadata Action: This key-value pair represents the name of the action, which is Set Column Metadata Action. You can modify the value Set Column Metadata Action to assign a different name to this action if needed.
table: your_table_id: Replace your_table_id with the ID or name of the table containing the column for which you want to set metadata.
column: your_column_id: Replace your_column_id with the ID or name of the column for which you want to set metadata.
metadata: {...}: This key contains the metadata information that you want to set for the column. Update the key1 and key2 properties with the desired metadata keys and values.

{
  "action": "set-column-metadata",
  "name": "Set Column Metadata Action",
  "table": "your_table_id",
  "column": "your_column_id",
  "metadata": {
    "key1": "value1",
    "key2": "value2"
  }
}