Creating an Aster Table - Teradata Studio

Teradata® Studio™ User Guide

Product
Teradata Studio
Release Number
16.20
Published
September 2019
Language
English (United States)
Last Update
2019-10-10
dita:mapPath
pfg1542649412202.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 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 Navigator, double-click Databases.
  2. In the Object List Viewer:
    1. Double-click the database containing the schema where you want to create a table.
    2. Double-click the schema.
    3. Click .
  3. In the General tab:
    1. Type a Name for the new table.
    2. [Optional] For a regular Aster database connection, select the Persistence level.
      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.
    3. [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.
      If you select Fact, click the Storage tab and select the column you want to be the key distribution column in the Distribute by drop-down list.
      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.
  4. Click the Columns tab and for each column you add to the table:
    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.
  5. [Optional] Click the Indexes tab and for each index:
    1. Click Add.
    2. Type the name of the index to be created in Index Name.
    3. Select a Compression option.
    4. 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.
    5. Click Add beside the Index Members table.
    6. 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.
    7. [Optional] Select the Ascending check box to specify the ordering of values in the column as ASC (ascending).
    8. [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.
  6. [Optional] Click the Constraints tab and for each constraint you Add:
    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 Columns table, then select one or more column names.
    4. If you selected CHECK, specify the check constraint condition in the Check Constraint Condition section.
  7. Click the Storage tab to specify how you want data distributed in the table.
    1. In Distribute by, indicate how you want the data distributed for this table. 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 Columns 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.
  8. Review the generated SQL and click Commit to create the table in the schema. Table privileges can now be assigned to the table. Specify the privileges granted to this table for specific users and roles in the Aster Database. You can also specify PUBLIC privileges for the table object.
  9. [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.
  10. 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.
  11. Click Commit.