Multi-tenancy data storage and access method and apparatus

• US 8,489,550 B2
• Filed: 05/09/2012
• Issued: 07/16/2013
• Est. Priority Date: 06/30/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: An, Wenhao, Gao, Bo, Guo, Chang Jie, Su, Zhong, Sun, Wei, Wang, Zhi Hu, Zhang, Zhen
• Primary Examiner(s): Ng, Amy
• Application Number:
  US13/467,228
• Publication Number:
  US 20120221608A1
• Time in Patent Office: 433 Days
• Field of Search: 707/636, 707/713, 707/720, 707/809
• US Class Current: 707/636
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation
• CPC Class Codes: G06F 16/256 in federated or virtual databases

Abstract

A method, apparatus, and a computer program product for storing and accessing multi-tenancy data. The method includes the steps of: creating a plurality of table sets in one or more databases, wherein each table set is used to store data of a group of tenants selected from a plurality of tenants; accessing data of a tenant in a table set in response to receiving a data access request from the tenant; and recording relationships between the tenants and the table sets in a multi-tenancy metadata repository, wherein the step of accessing the data of the tenant comprises the steps of finding the table set by querying the metadata repository and accessing the data of the tenant in the table set based on the result received from the query of the metadata repository.

Claims

1. A method of storing and accessing multi-tenancy data, the method comprising the steps of:

creating a plurality of table sets in one or more databases, wherein each table set is used to store data of a group of tenants selected from a plurality of tenants;

accessing data of a tenant in a table set in response to receiving a data access request from the tenant;

recording relationships between the tenants and the table sets in a multi-tenancy metadata repository, wherein the step of accessing the data of the tenant comprises the steps of finding the table set by querying the metadata repository and accessing the data of the tenant in the table set based on the result received from the query of the metadata repository; and

determining a maximum number of table sets and a maximum table sharing rate of each database, wherein the step of creating a plurality of table sets is performed according to the determined maximum number of table sets and maximum table sharing rate of each database.

2. The method of claim 1, wherein the data access request contains a SQL statement for data access and the step of accessing the data of the tenant comprises the steps of:

(i) extracting the SQL statement contained in the data request;

(ii) parsing the SQL statement contained in the data request;

(iii) modifying the SQL statement into a corresponding SQL statement for accessing the data of the tenant in the table set; and

(iv) sending the modified SQL statement to a database storing the data of the tenant.

3. An apparatus for storing and accessing multi-tenancy data, comprising:

a memory;

a tangible non-transitory computer readable storage medium accessible to said memory;

at least one processor, coupled to said memory;

a creation processing module stored in said computer readable storage medium which, when loaded into said memory, causes said at least one processor to be operative to create a plurality of table sets in one or more databases, wherein each table set is used to store data of a group of tenants selected from a plurality of tenants;

a multi-tenancy data router stored in said computer readable storage medium which, when loaded into said memory, causes said at least one processor to be operative to access data of a tenant in a table set in response to receiving a data access request from the tenant; and

a multi-tenancy metadata repository stored in said computer readable storage medium which, when loaded into said memory, causes said at least one processor to be operative to record the corresponding relationships between tenants and table sets;

wherein the multi-tenancy data router, when loaded into said memory, further causes said at least one processor to be operative to find the table set by querying the metadata repository and access the data of the tenant in the table set based on a result received from the query of the metadata repository;

further comprising a determination module stored in said computer readable storage medium which, when loaded into said memory, causes said at least one processor to be operative to determine a maximum number of table sets and maximum table sharing rate of each database, wherein the creation module, when loaded into said memory, further causes said at least one processor to create the plurality of table sets in the one or more databases according to the determined maximum number of table sets and maximum table sharing rate of each database.

4. The apparatus of claim 3, wherein the data access request contains a SQL statement for data access and the multi-tenancy data router, when loaded into said memory, further causes said at least one processor to (i) extract the SQL statement contained in the data request;

(ii) parse the SQL statement contained in the data request;

(iii) modify the SQL statement into a corresponding SQL statement for accessing the data of the tenant in the table set; and

(iv) send the modified SQL statement to a database storing the data of the tenant.

5. An article of manufacture comprising a computer program product for storing and accessing multi-tenancy data, said computer program product comprising:

a tangible non-transitory computer-readable storage medium, storing in a non-transitory manner computer readable program code, the computer readable program code comprising;

computer readable program code configured to create a plurality of table sets in one or more databases, wherein each table set is used to store data of a group of tenants selected from a plurality of tenants;

computer readable program code configured to access data of a tenant in a table set in response to receiving a data access request from the tenant;

computer readable program code configured to record relationships between the tenants and the table sets in a multi-tenancy metadata repository, wherein the computer readable program code configured to access the data of the tenant comprises computer readable program code configured to perform the steps of finding the table set by querying the metadata repository and accessing the data of the tenant in the table set based on the result received from the query of the metadata repository; and

computer readable program code configured to determine a maximum number of table sets and a maximum table sharing rate of each database, wherein the computer readable program code configured to create a plurality of table sets creates the plurality of table sets according to the determined maximum number of table sets and maximum table sharing rate of each database.

6. The article of manufacture of claim 5, wherein the data access request contains a SQL statement for data access and the computer readable program code configured to access data of the tenant comprises computer readable program code configured to:

(i) extract the SQL statement contained in the data request;

(ii) parse the SQL statement contained in the data request;

(iii) modify the SQL statement into a corresponding SQL statement for accessing the data of the tenant in the table set; and

(iv) send the modified SQL statement to a database storing the data of the tenant.