Creating an Aster Table - Teradata Studio

Teradata Studio User Guide
Product
Teradata Studio
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
sbq1486739977986.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2041
lifecycle
previous
Product Category
Teradata Tools and Utilities
At minimum, an Aster table must have a table name, table type, and one column defined. Fact tables also require that you identify a column for distributing data. If you need to undo a table definition at any time, click Reset to delete your entries and begin again.
Several of the options available for tables created in a regular Aster database connection are not available for tables created in an Aster Execution Engine (Aster-on-Hadoop) instance connections. Where there are differences, they are noted in the steps below.
  1. In the Administration perspective, click an Aster connection.
  2. In the Navigator, double-click on the Databases category. The database list opens in the Object List Viewer.
  3. Locate the database and schema where you want to create a table.
  4. In the Object List Viewer, click . The Create Table form displays.
  5. In the General tab, type a Name for the new table.
  6. [Optional] For a regular Aster database connection select the Persistence level.in
    Option Description
    Regular (persistent) Regular or Persistent tables are persistent across sessions and transactions and continue to exist along with its data until the table is dropped. This type of table survives system restarts, node changes, partition splitting, exports and loads, backups, restores, and other types of operations. If data needs to persist longer than a few days, consider using a Regular table.
    This is the default for a regular Aster database connection, but is not available for an Aster Execution Engine instance (Aster-on-Hadoop) table.
    Analytic Analytic tables hold data across the span of several transactions, sessions, or days. Use Analytic tables only for derived data and never for original source data. Analytic tables are not replicated, and do not survive a system restart.
  7. [Optional] Select the Table Type:
    Option Description
    Fact A single, large table that records raw data as facts, such as measurements or metrics for a specific event. Fact tables typically contain numeric values and foreign keys to dimensional data stored in other tables. A fact table is always a distributed table. This is the default.
    Dimension A set of tables that contain values and descriptive attributes for data recorded in a fact table. Dimension tables can be replicated or distributed.
    If you selected Fact, you must open the Storage tab and in the Distribute by field, select the column you want to be the key distribution column from the list.
  8. Click Columns and for each column you add to the table, complete the following information:
    1. Type the name of the new column in Column Name.
    2. Select the Data Type.
    3. In Length, type the maximum data length allowed for column data if appropriate for the selected type.
    4. Type the maximum Precision allowed for numeric column data. The precision of a numeric is the total count of digits in the whole number (both sides of a decimal). For example, the number 3068.443 has a precision of 7. The precision must be a positive number.
    5. Type the maximum Scale allowed for numeric column data. The scale of a numeric is the count of decimal digits in the fractional part of the number to the right of the decimal point. For example, the number 3068.443 has a scale of 3. The scale of an integer is considered zero, therefore, scale can be zero or a positive number.
    6. Select Null if data values in this column can be null.
    7. Select Unique if data values in this column must be unique.
    8. [Optional] Type a Default value if appropriate for the column type. By defining a default value, you ensure that the column is set to the default value if no value is provided when a row is inserted or updated. When you add a column, if a default value is specified, all existing rows in the table are initialized with the column default value. If no default value is defined, existing rows are initialized with NULL.
  9. [Optional] Click Indexes and for each index you Add, complete the following information:
    1. Type the name of the index to be created in Index Name.
    2. Select a Compression option.
    3. Click Add beside the Index Members table.
    4. In Column Name/Expression select a column to become a member of this index or an expression computed from the values of one or more columns of the table row. The column you added as an index member displays in the Member Summary.
    5. [Optional] Select the Ascending check box to specify the ordering of values in the column as ASC (ascending).
    6. [Optional] Select the Nulls First check box to specify that null values sort before non-null values. If not selected, null values sort after all non-null values.
      The Ascending and Nulls First selections determine the sorting order of the result rows in the result column.
    7. In WHERE insert the WHERE constraint expression for a partial index. If the WHERE clause is present, a partial index is created that contains only that portion of the table which is most useful for indexing.
  10. [Optional] Click Constraints and for each constraint you Add, complete the following information:
    1. Type a name for the constraint in the Constraint Name.
    2. Select a Constraint Type. UNIQUE and PRIMARY KEY constraint specifications consist of one or more column names. CHECK constraints require that you specify the conditions of the check.
    3. If you selected UNIQUE or PRIMARY KEY type, click Add beside the Index Members table, then select one or more column names in the Columns section.
    4. If you selected CHECK type, specify the check constraint condition in the Check Constraint Condition section.
    5. Review the Constraint Summary to ensure the constraint values you set are correct.
  11. Click Storage.
    1. In Distributed by, indicate how you want the data distributed for this table; default is REPLICATION. If you selected Fact as the Table Type in the General tab, you must select the column you want to be the key distribution column . The options listed include the names of all columns identified in the Column tab.
    2. Select Storage by Row if you want the table data stored by the table row. For an Aster Execution Engine (Aster-on-Hadoop) table, storage must be by row; there is no column storage. If you do not select the check box, data storage is by column. However, for an Aster Execution Engine (Aster-on-Hadoop) table, storage must be by row so this option is selected and cannot be changed.
    3. [Optional] In Compress, select the level of compression for an existing partition. This option is not available for an Aster Execution Engine (Aster-on-Hadoop) table.
    4. [Optional] In Inherits, accept the default value or type a different value. Inherits indicates whether the table has an inheritance relationship with a parent table.
    5. [Optional] In Partition By, use the PARTITION BY RANGE or PARTITION BY LIST clause to define child partitions and to specify which partition each row belongs to. The PARTITION BY clause creates the table as logically partitioned in separate files, and defines the layout of the partition.
  12. Review the generated SQL and click Commit to create the table in the schema. You must create the table before you can assign table privileges.
  13. [Optional] Click Privileges and define the privileges:
    Grantee Type Description
    Users Select a user to whom you want to grant rights for this table, then select the rights.
    Roles Select a role to which you want to grant rights for this table, then select the rights.
    Public Gives the public role to this table.
  14. Click SQL and review the generated statement. To modify the CREATE TABLE statement, return to previous steps to change your selections. You can also copy and paste the text into an SQL Editor to make edits.
  15. Click Commit to execute the statement and update the table definition on the Aster Database server.