Physical Tables (Manual Creation)

A physical table is a standard dataspace, like a Hive table.

The key difference is the possibility to add Hyperindexes to an Indexima table.

There are three different types of physical tables: fact tables, dimension tables, and limited tables

Fact tables

In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is located at the center of a star schema or a snowflake schema surrounded by dimension tables.

You can create it with the SQL command CREATE TABLE

CREATE TABLE mytable (id BIGINT, name STRING, start_date DATE, zipcode INT) INDEX (start_date, zipcode))

This means that you will create a table named "mytable" that contains 4 columns: id, name, start_date, and zipcode, each with their respective data type.

In Indexima you should try to put the columns with the lowest cardinality as Hyperindexes, in order to have the best performance with data aggregation.

Dimension tables

In a data warehouse, dimensions provide structured labeling information to otherwise unordered numeric measures. The dimension is a data set composed of individual, non-overlapping data elements. The primary functions of dimensions are threefold: to provide filtering, grouping and labeling.

You can create it with the SQL command CREATE DIMENSION TABLE

CREATE DIMENSION TABLE mydim (id BIGINT, country STRING, continent STRING))

This means that you will create a table named "mydim" that contains 3 columns: id, country, and continent, each with their respective data type.

Dimension tables don't have Hyperindexes. Every columns of a dimension table is stored in memory, as a dimension table is not meant to contains very large amount of data.

Limited tables

In Indexima, you do not always know what columns you want to index the first time you create it. Instead of picking a random column to index (or worse, to pick the primary key), you can create a limited table, which has every columns indexed, but has a limit to the amount of data it can contain. You will be able to change this and build more efficients indexes later on, after you started using your table and you have a better visibility on what you want to do.

You can create it with the SQL command CREATE TABLE. The difference with the fact table is that you must not specify any Index for it to be considered a limited table.

CREATE TABLE mytable (id BIGINT, name STRING, start_date DATE, zipcode INT))

This will create the same table as in the Fact Table chapter, except all columns will be indexed.

More

You can learn more about the CREATE TABLE commands here

You can learn more about creating customized Hyperindexes here