This topic describes how to use the ob-partition-calculator component in three scenarios.
Background information
The ob-partition-calculator component is a Java-based tool that provides an API for calculating the partitions to which data records belong. By using the tool, when you insert a large amount of data, you can insert the data in batches based on the partitions to which the data belongs. This improves the efficiency of data insertion.
Before you use the ob-partition-calculator component, read the following topics to familiarize yourself with some required concepts in OceanBase Database:
Configure dependencies
Add JAR package dependencies of the ob-partition-calculator component to the pom.xml file of the local Java project.
<dependency>
<groupId>com.oceanbase</groupId>
<artifactId>ob-partition-calculator</artifactId>
<version>1.8.0-RELEASE</version>
</dependency>
Use the ob-partition-calculator component
Calculate partition IDs for the input data when the partitioning key field is a non-generated column
When you insert a batch of data into OceanBase Database, the ob-partition-calculator component can calculate the partition to which each data record belongs. This way, you can insert the data records belonging to the same partition in the same batch of transactions, thus improving the efficiency of data insertion.
The following sample code shows how to calculate the partition ID in the destination table for a given data record:
public void test() throws Exception {
// You must create a username in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a username in a business tenant in OceanBase Database V4.0.0.0 and later.
String user = "";
// You must create a connection URL in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a connection URL in a business tenant in OceanBase Database V4.0.0.0 and later.
String url = "jdbc:oceanbase://ip:port/database?useUnicode=true&characterEncoding=utf8&verifyServerCertificate=false&useSSL=false&rewriteBatchedStatements=true&allowMultiQueries=true";
// You must create a user password in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a user password in a business tenant in OceanBase Database V4.0.0.0 and later.
String password = "";
// You must create a connection in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a connection in a business tenant in OceanBase Database V4.0.0.0 and later.
Connection conn = DriverManager.getConnection(url, user, password);
// The name of the cluster to connect to.
String cluster = "";
// The name of the business tenant to connect to.
String tenant = "";
// The name of the business database to connect to.
String database = "";
// The name of the table to query.
String table = defaultTableName;
// MySQL mode
TableEntryKey key = new TableEntryKey(cluster, tenant, database, table, ObServerMode.fromMySql("3.2.3.3"));
// Oracle mode
// TableEntryKey key = new TableEntryKey(cluster, tenant, database, table, ObServerMode.fromOracle("3.2.3.3"));
TableEntryExtractor tableEntryExtractor = new TableEntryExtractor();
TableEntry entry = tableEntryExtractor.queryTableEntry(conn, key,subsequentV4);
ObPartIdCalculator calculator = new ObPartIdCalculator(entry);
{
// You must specify all fields, including non-partitioning key fields, which can be set to null.
Object[] records = new Object[] {"aaa","123"};
// Obtain the partition ID of the given record.
Long partId = calculator.calculatePartId(records);
System.out.println("===> Partition ID: " + partId);
}
}
Calculate partition IDs for the input data when the partitioning key field is a generated column
The following sample code shows how to calculate the partition ID in the destination table for a given data record when the partitioning key field of the destination table is a generated column:
public void test() throws Exception {
// You must create a username in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a username in a business tenant in OceanBase Database V4.0.0.0 and later.
String user = "abc";
// You must create a connection URL in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a connection URL in a business tenant in OceanBase Database V4.0.0.0 and later.
String url = "";
// You must create a user password in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a user password in a business tenant in OceanBase Database V4.0.0.0 and later.
String password = "";
// You must create a connection in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a connection in a business tenant in OceanBase Database V4.0.0.0 and later.
Connection conn = DriverManager.getConnection(url, user, password);
// The name of the cluster to connect to.
String cluster = "";
// The name of the business tenant to connect to.
String tenant = "";
// The name of the business database to connect to.
String database = "";
// The name of the table to query.
String table = defaultTableName;
// MySQL mode
TableEntryKey key = new TableEntryKey(cluster, tenant, database, table, ObServerMode.fromMySql("3.2.3.3"));
// Oracle mode
// TableEntryKey key = new TableEntryKey(cluster, tenant, database, table, ObServerMode.fromOracle("3.2.3.3"));
TableEntryExtractor tableEntryExtractor = new TableEntryExtractor();
TableEntry entry = tableEntryExtractor.queryTableEntry(conn, key,subsequentV4);
ObPartIdCalculator calculator = new ObPartIdCalculator(entry);
{
// You must specify all fields, including non-partitioning key fields, which can be set to null.
Object[] records = new Object[] {"123","456"};
// Call the ObPartIdCalculator#addRefColumn method to add the names and values of the fields on which the generated column depends to the calculator.
calculator.addRefColumn(columnName, records[0]);
// Obtain the partition ID of the given record.
Long partId = calculator.calculatePartId(records);
System.out.println("===> Partition ID: " + partId);
}
}
Query the addresses of leaders
When you insert a batch of data with the same partition ID, the ob-partition-calculator component can query the address of the leader of the partition. The address is in the ip:port format. You can determine the memory usage of the node based on the address. This way, you can enable throttling on your client as needed to avoid excessive memory usage.
The following sample code shows how to query the address of the leader corresponding to a given partition ID:
public void test() throws Exception {
// You must create a username in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a username in a business tenant in OceanBase Database V4.0.0.0 and later.
String user = "";
// You must create a connection URL in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a connection URL in a business tenant in OceanBase Database V4.0.0.0 and later.
String url = "";
// You must create a user password in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a user password in a business tenant in OceanBase Database V4.0.0.0 and later.
String password = "";
// You must create a connection in the sys tenant in OceanBase Database earlier than V4.0.0.0.
// You only need to create a connection in a business tenant in OceanBase Database V4.0.0.0 and later.
Connection conn = DriverManager.getConnection(url, user, password);
// The name of the cluster to connect to.
String cluster = "";
// The name of the business tenant to connect to.
String tenant = "";
// The name of the business database to connect to.
String database = "";
// The name of the table to query.
String table = defaultTableName;
// MySQL mode
TableEntryKey key = new TableEntryKey(cluster, tenant, database, table, ObServerMode.fromMySql("3.2.3.3"));
// Oracle mode
// TableEntryKey key = new TableEntryKey(cluster, tenant, database, table, ObServerMode.fromOracle("3.2.3.3"));
TableEntryExtractor tableEntryExtractor = new TableEntryExtractor();
// The TableEntry field records the partition information of the specified table.
TableEntry entry = tableEntryExtractor.queryTableEntry(conn, key,subsequentV4);
// Obtain all partitions of the specified table.
List<Long> partIds = Lists.newArrayList((entry.getPartIdNameMap().keySet());
// Obtain the address of the leader corresponding to the partition.
// The value of the subsequentV4 field is a Boolean value. The value true indicates that the version of OceanBase Database is V4.0.0.0 or later. The value false indicates that the version of OceanBase Database is earlier than V4.0.0.0. The default value is false.
Map<Long, String> map = tableEntryExtractor.queryLeaderLocationMap(conn, entry, partIds, subsequentV4);
}