Chapter 4 Temporary and Analytic Tables
“A Journey of a thousand miles begins with a single step.”
-Lao Tzu
Aster has Three Types of Data
There are three different types of tables with different levels of persistence:
Regular or Persistent Tables – These are normal tables that continue to exist along with its data until they are dropped. The table persists across sessions as well as through multiple transactions. The table survives system restarts, adding or removing of nodes, any partition splitting, all backup and restore operations, and importing or exporting.
Temporary Tables – These persistent only for one transaction. Once the transaction ends, the temporary table is deleted.
Analytic Tables – These have a persistence that is a compromise between regular and temporary tables. This special type of table was created to hold data that is useful for operations across the span of several transactions, sessions, or days. The data in an analytic table is not replicated and will not survive a system restart. Analytic tables are used for derived data only.
Above are the three types of tables in Aster data.
Create a Permanent Table Using Create Table AS (CTAS)
CREATE FACT TABLE Employee_Table (
Employee_No INT,
Dept_No INT,
First_Name VARCHAR,
Last_Name VARCHAR,
Salary REAL
)
DISTRIBUTE BY HASH( Employee_No )
AS SELECT
Emp_No,
Dept_No,
First_Name,
Last_Name,
Salary
FROM Emp_Intl ;
Tables can be created and the data inserted in a single step using a CTAS table, which stands for CREATE TABLE AS SELECT command. CREATE TABLE AS SELECT requires the table type, table name, column information, constraints and optionally the distribution key. The command also specifies a SELECT query to populate the table with the data. The NULL, NOT NULL and CHECK constraints are not supported in CREATE TABLE AS SELECT statements. Above is an example of a CTAS table.
Create a Logically Partitioned Table and Populate It
You can create a logically partitioned table using data from another table, but you have to do an Insert/Select. You cannot create the table using CREATE TABLE...AS SELECT in one statement as you would with a flat table. You must first create the table with its partitions, and then perform an Insert/Select into the partitioned table. Data will automatically be inserted into the correct partitions. Above, we show you this two step process.
Create a Temporary Table with using Create Table AS (CTAS)
BEGIN;
CREATE TEMP FACT TABLE TeraTom (
Employee_No BIGINT,
Dept_No INT,
First_Name VARCHAR,
Last_Name CHAR(20),
Salary Decimal(10,2)
)
DISTRIBUTE BY HASH( Employee_No )
AS SELECT
Emp_No, Dept_No, First_Name, Last_Name, Salary
FROM Employee_Table
WHERE Dept_No IN (300, 400) ;
SELECT * FROM TeraTom ;
END;
Above, we have created a temporary table with a CTAS (CREATE Temporary Table AS). Temporary tables are automatically dropped upon transaction commit. Temporary tables are created using CREATE TEMP TABLE and CREATE TEMP TABLE AS SELECT commands which have the same syntax as CREATE TABLE and CREATE TABLE AS SELECT commands. The only difference is the keyword TEMP.
A Temporary Table in Action
A temporary table only persists until the end of the transaction. Our temporary table holds the average salary per department. It is then joined to a permanent table to produce the desired results. The temporary table is then deleted at transaction end.
A Temporary Table That Uses an Insert/Select
A temporary table only persists until the end of the transaction. Our temporary table holds the average salary per department. It is then joined to a permanent table to produce the desired results. The temporary table is then deleted at transaction end.
Create an Analytic Table Using an Insert/Select
Version 5.10 of Aster Database introduces analytic tables that have a persistence which provide a compromise between regular and temporary tables. An Analytic table will stay materialized over several transactions, sessions, or days.
Create an Analytic Table Using CREATE TABLE AS (CTAS)
Version 5.10 of Aster Database introduces analytic tables that have a persistence which provide a compromise between regular and temporary tables. An Analytic table will stay materialized over several transactions, sessions, or days. If the analysis of the data will last longer than a few days, be prepared to re-generate the table if necessary or else use a permanent table. Analytic tables are always hashed and never replicated.
Operations that Invalidate an Analytic Table
The operations that invalidate an Analytic table are:
• Soft or Hard restart
• Node failover
• Activation of a different vworker
• Activation of a new vworker
• Balance data
• Balance process
• Addition of a node
• Partition splitting
• Queen replacement
• Activate
Any of the above operations make the data in an Analytic table inaccessible, but the table still persists. An Analytic table is created to hold data that is needed for operations across the span of several transactions, sessions, or days. If the analysis of the data will last longer, be prepared to re-generate the table if necessary, or you might consider using a permanent table.
If an Analytic Table is Invalid
Version 5.10 of Aster Database introduces analytic tables. If, for some reason, your table becomes invalid, follow the instructions above. You will be back in business in no time.
Tera-Tom History
Tom Coffing was a two-time All-American wrestler for the University of Arizona. In 1979, Tom was “Sophomore Athlete of the Year” for the University of Arizona. That year, Tom placed 3rd in the NCAA (Division 1) and is pictured above at the awards ceremony in Ames, Iowa.