SAP HANA HDB Partition management (i)

SAP HANA HDB partition management:

Using the partitioning feature of the SAP HANA database, you can store the large data tables of a column store horizontally in separate partitions.

The partitions of SAP Hana can only be used on columnstore tables, and syntax errors can occur if you use tables stored on rows.

error message: SAP Dbtech JDBC: [257]: SQL syntax error:not supported for row table

The partition table for SAP Hana can contain multiple partitions, each of which is independent.

A bit of partitioning:

load balancing: with table partitioning, individual data for individual partitions can be stored in different distribution servers. When querying a table, it is not handled by a single server, but is decomposed to the servers of all the associated primary partitions for parallel processing. Data is distributed across partitions to reduce data corruption.

parallelism: operational parallelism, which operates in parallel using several execution threads on each table. queries, additions, and modifications to large tables can be decomposed into different partitions of the table to execute in parallel, making the operation faster

Partition Pruning : query analysis to see if a table matches a given partition specification. If a match is found, it is possible to determine the actual partition (save data problem). Using this method can reduce the load of the system, and the response time is better.

Explicit Partitioning Processing: The SAP Hana database proactively controls partitioning, for example: adding partitions to store data for the upcoming one months.

restriction : A non-partitioned table cannot store more than 200 million rows of data by using partitions, which can be overcome by distributing the tables to several partitions for storage. Note, however, that each partition cannot exceed 200 million rows of data.

Performance: The performance of an incremental merge of the SAP Hana database depends on the size of the primary index of the database, and if the data is modified only in a subset of the partitions, there will be a small amount of data that needs to be incrementally merged to perform better for the database.

Modify-delete partition:

ALTER TABLE mytab ADD PARTITION OTHERS
ALTER TABLE mytab DROP PARTITION OTHERS

to view the table partitioning situation:

SELECT * FROM sys. m_cs_partitions WHERE table_name = tablename

partition types provided by SAP HANA:

Single-stage partitioning (Single-level partitioning): HASH, Range,round-robin.

----------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------

range partition (range partitioning):

A range partition can be used to create a dedicated partition, which can be a fixed value or a range can be made. You can choose to have quarterly data in one partition or a partition for every month of the year.

Alternatively, you can choose to use a partition table that is actively managed using a range partition that can be created or deleted, such as: The database can create a partition for the upcoming one months, and new data will be inserted into the new partition.

If inserting a piece of data does not match the conditions of any one of the partitions, SAP Hana will feed back an error message, in order to avoid this situation, you will need to create a remaining partition (rest partition) when creating the partition.

Note: Range partitioning is not suitable for load distribution.

Scope partitioning restrictions on data types: only strings, integers, dates are available.

Syntax: The field to be partitioned must be part of the primary key .

Create an integer-type partition: INT

CREATE COLUMN TABLE mytab (a int, b int, c int, PRIMARY KEY (A, b)) PARTITION by RANGE (a)
(PARTITION 1 <= VALUES < 5,
PARTITION 5 <= VALUES < 20,
PARTITION VALUE = 44,
PARTITION OTHERS)

Create a range of partitions by: PARTITION <= VALUES <

Create a fixed-value partition by: PARTITION value =

To create a remaining partition by: PARTITION OTHERS

Delete a range of partitions by: ALTER TABLE Mytab drop partition 1 <= VALUES < 5

Delete a fixed-value partition by: ALTER TABLE MYTAB2 DROP partition value = 44

Delete one remaining partition by: ALTER TABLE MYTAB2 drop partition OTHERS

Create date type partition: Date

CREATE COLUMN TABLE test_range_partition_date (ID int,name VARCHAR), BIRTHDAY date,primary KEY (id,birthday)
PARTITION by RANGE (BIRTHDAY) (

PARTITION ' 2011-12-31 ' <= VALUES < ' 2012-04-01 ',

PARTITION OTHERS)

Create a String type partition: String

CREATE COLUMN TABLE test_range_patition_string (ID int,city varchar), DESCRIPTION varchar (PRIMARY KEY (id,city))
PARTITION by RANGE (
PARTITION VALUES = ' Guangzhou ',
PARTITION VALUES = ' Shanghai ',
PARTITION VALUES = ' Xiamen ',
PARTITION OTHERS
)

If the field of the partition is not part of the primary key:

CREATE COLUMN TABLE test_range_patition_string_nokey (ID int,city varchar), DESCRIPTION varchar,PRIMARY KEY ( ID))
PARTITION by RANGE (
PARTITION VALUES = ' Guangzhou ',
PARTITION VALUES = ' Shanghai ',
PARTITION VALUES = ' Xiamen ',
PARTITION OTHERS
)

error message: SAP Dbtech JDBC: [435] (at): invalid expression: partition column should is included in primary key columns: City:line 2 col (at POS 140)

no remaining partitions created: (rest partition)

CREATE COLUMN TABLE mytab_test (a int, b int, c int, PRIMARY KEY (A, b)) PARTITION by RANGE (a)
(PARTITION 1 <= VALUES < 5,
PARTITION 5 <= VALUES < 20,
PARTITION VALUE = 44
)

INSERT into Mytab_test VALUES (55,5,55); A value of 55 does not fall within any one partition.

error message: Could not execute ' insert into mytab_test values (55,5,55) '
SAP Dbtech JDBC: [2048]: Column store error: [2592] Error allocating rows to parts; Could not allocate value ' in ' for column ' A ' to a part. The table has no rest part. Create a new partition for the value.

Add Partition: Add rest partition to Mytab_test table

ALTER TABLE mytab_test ADD PARTITION OTHERS;

INSERT into Mytab_test VALUES (55,5,55);

Execution information:

Statement ' ALTER TABLE mytab_test ADD PARTITION OTHERS ' successfully executed in MS 439µs-rows affected:0
Statement ' INSERT into Mytab_test VALUES (55,5,55) ' successfully executed in + MS 119µs-rows Affected:1

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

SAP HANA HDB Partition management (ii) http://blog.csdn.net/qptufly/article/details/7965305