This tutorials will explain what are the types of table available in SingleStore. This will also explain how users can create a copy of the table and check the size of the table.
➠
Columnstore Tables
- Data for these tables are stored on disks (Flash / SSD / disks).
- As name suggest, data is stored in columnar format.
- Table definition is stored in data dictionary.
- Data and structure can be shared across multiple sessions and users.
- Indexes can be created.
- COMPRESS column and DEFAULT clause are supported.
- Should be used where DELETE and Updates are rare.
- Ideal if table is used for Analytical purpose.
- Aggregation is faster in this table since data is stored in columnar format.
- Columnstore table is the default table storage format since SingleStore Version 7.3.
- Columnstore table can be created specifically by mentioning Key with "USING CLUSTERED COLUMNSTORE".
- Columnstore temporary table is not supported by SingleStore.
- Columnstore reference table is supported by SingleStore.
Example: Creating a columnstore table
CREATE Reference TABLE tutorial_db.employee_columnstore(
emp_no INTEGER,
emp_name VARCHAR(50),
job_title VARCHAR(30),
manager_id INTEGER,
hire_date Date,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER,
KEY (dept_no) USING CLUSTERED COLUMNSTORE
);
➠
Rowstore Tables
- Data for these tables are stored in Memory.
- Table(not temporary tables) definition is stored in data dictionary.
- Data and structure(not temporary tables) can be shared across multiple sessions and users.
- Indexes can be created.
- COMPRESS column and DEFAULT clause are supported.
- Should be used where DELETE and Updates are frequent.
- Ideal if table is used for transactional purpose.
- Rowstore table was the default table storage format prior to SingleStore Version 7.3.
- Rowstore table can be created specifically by mentioning "CREATE ROWSTORE TABLE".
- Rowstore temporary table is supported by SingleStore.
- Rowstore reference table is supported by SingleStore.
Example: Creating a rowstore table
CREATE ROWSTORE TABLE tutorial_db.employee_rowstore(
emp_no INTEGER,
emp_name VARCHAR(50),
job_title VARCHAR(30),
manager_id INTEGER,
hire_date Date,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER,
KEY (dept_no),
SHARD(emp_no)
);
➠
Reference Tables
- Entire table data is copied to each node of SingleStore cluster.
- Ideal for small master or dimension tables.
- This table can be either defined as Rowstore or Columnstore.
- Table definition is stored in data dictionary.
- Data and structure(not temporary tables) can be shared across multiple sessions and users.
- Indexes can be created.
- COMPRESS column and DEFAULT clause are supported.
- Ideal if table is used for transactional purpose.
- Rowstore Reference table can be created specifically by mentioning "CREATE ROWSTORE REFERENCE TABLE".
- Columnstore Reference table can be created specifically by mentioning Key with "USING CLUSTERED COLUMNSTORE".
- Sharded reference tables are not supported in SingleStore since each node store complete table.
- Primary key must be defined(manadatory) for Rowstore Reference table.
- Primary key is not manadatory for Columnstore Reference table.
Example: Rowstore Reference Table
CREATE ROWSTORE REFERENCE TABLE tutorial_db.employee_rowstore_reference(
emp_no INTEGER PRIMARY KEY,
emp_name VARCHAR(50),
job_title VARCHAR(30),
manager_id INTEGER,
hire_date Date,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER,
KEY (dept_no)
);
Example: Columnstore Reference Table
CREATE REFERENCE TABLE tutorial_db.employee_columnstore_reference(
emp_no INTEGER,
emp_name VARCHAR(50),
job_title VARCHAR(30),
manager_id INTEGER,
hire_date Date,
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER,
KEY (emp_no) USING CLUSTERED COLUMNSTORE
);
➠
Global Temporary Tables(GTT)
- This is one kind of temporary tables.
- Its a subtype of Rowstore table.
- Columnstore global temporary tables are not supported by SingleStore.
- Table definition is stored in data dictionary.
- Structure can be shared across multiple users but data will remain private to the session(and its user).
- Data is stored in the memory.
- Data will be purged for that session once session ends.
- Indexes can be created.
- COMPRESS column and DEFAULT clause are supported.
- Global temporary tables cannot be ALTERED after creation.
- Global Temporary tables cannot persist data after Node restart/failover.
Example: Creating a temporary table.
CREATE ROWSTORE GLOBAL TEMPORARY TABLE updatetable2 (
id int(11) PRIMARY KEY DEFAULT 0,
name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
indicator_flag char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
KEY(id) USING BTREE
, SHARD KEY (id)
)
COMPRESSION=SPARSE;
➠
Temporary Tables
- This is a kind of temporary tables.
- Its a subtype of Rowstore table.
- Columnstore temporary tables are not supported by SingleStore.
- Table definition is not stored in data dictionary.
- Structure & data is private to the session and its user.
- Data is stored in the Memory as in the case of Rowstore table.
- Table gets dropped once session ends.
- Indexes can be created at the time of table creation.
- COMPRESS column and DEFAULT clause are supported.
- Partition Primary Index(PPI) is supported
- Either Primary key or SHARD Key can be defined but not both.
- Temporary tables cannot be ALTERED after creation.
- Temporary tables cannot persist data after Node restart/failover.
- All the temporary tables in current session can be checked using below command:
SHOW TEMPORARY tables;
Example: Creating a temporary table.
CREATE ROWSTORE TEMPORARY TABLE updatetable (
id int(11) DEFAULT 0,
name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
indicator_flag char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
KEY(id) USING BTREE
, SHARD KEY (id)
)
COMPRESSION=SPARSE;
➠
Derived Tables
- This is one kind of temporary table that is derived from one or more other tables as the result of a sub-query.
- Derived tables are local to the query and exists only for the duration of the query.
- The table is automatically discarded once the query is done.
- Data is stored in the Memory.
- Table definition is not stored in data dictionary.
Example: Sub query named 'derived_table' is the example of derived table
SELECT employee.emp_name, employee.dept_no, employee.Salary, derived_table.Avgsal
FROM (SELECT dept_no, AVG(salary) Avgsal
FROM employee GROUP BY dept_no)
AS derived_table,
employee
WHERE derived_table.dept_no = employee.dept_no
AND employee.salary > derived_table.Avgsal;
➠
Copy Table
Example: Creating a table using other table with no data.
CREATE TABLE tutorial_db.employee_copy LIKE tutorial_db.employee;
Example: Creating a table using other table with data.
CREATE TABLE tutorial_db.employee_copy_with_data AS SELECT * FROM tutorial_db.employee;
➠
Table Size
Example: Finding table size
SELECT database_name,table_name, SUM(memory_use)/(1024*1024*1024) "TableSize in GB"
FROM information_schema.table_statistics
WHERE database_name = '[databasename]'
AND table_name = '[tablename]'
GROUP BY database_name,table_name ;