ADDING A PARTITION TO AN EXISTING TABLE STORED ON A DISTRIBUTED FILE SYSTEM

• US 20190236169A1
• Filed: 01/31/2018
• Published: 08/01/2019
• Est. Priority Date: 01/31/2018
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Nambiar, Gopi Krishnan, Mehta, Siddhi, Pradeep, Aakash, Mayanglambam, Shangkar Meitei
• Granted Patent: US 10,642,803 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/182 Distributed file systems
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2445 Data retrieval commands; View definitions
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

Abstract

Systems and methods are described for registering a new column in an existing table on a distributed file system as a partition. In response to receiving a request to add a new column to a selected table, a new table may be created that includes all fields of the selected table, the new column, and a new partition, and the selected table may be renamed within the distributed file system. A view may then be created that points to data in the new table and the renamed selected table, the created view being a virtual table that receives and processes queries. All data from the renamed selected table may be transferred the new table. When the transfer is complete, the new table may be renamed to an original name of the selected table, and any data received after the renaming may be included in the renamed new table.

Claims

1. A method comprising:

receiving, by a database system, a request to add a new column to a first table stored within a distributed file system, the first table having an associated name;

creating, by the database system, in response to the request, a second table that includes all fields of the first table, the new column, and a new partition including data from the new column, the second table having an associated name;

causing, by the database system, any new data sent for the first table to be included in the second table;

causing, by the database system, in response to the request, the first table to be renamed, the renaming comprising changing a pointer of the distributed file system to refer to the renamed first table, the renaming being performed using an atomic operation;

creating, by the database system, a view pointing to data in each of the second table and the renamed first table, the view being a virtual table created on the distributed file system;

causing, by the database system, all data from the renamed first table to be transferred to the second table by adding pointers to the data to the second table; and

causing, by the database system, the second table to be renamed to the name of the first table when the transfer of the data from the renamed first table to the second table is complete, the renaming comprising changing the pointer of the distributed file system to refer to the renamed second table, being performed using an atomic operation, and causing any data received after renaming the second table to be included in the renamed second table.

2. The method of claim 1, wherein the transferring all data from the renamed first table is performed using a “

load data inpath”

command.

3. The method of claim 1, wherein the transferring all data further comprises validating if unmoved data exists within the renamed first table and validating if all data has been moved to the second table.

4. The method of claim 1, wherein the transferring all data is performed without copying any data between tables.

5. The method of claim 1, wherein the renaming the first table and renaming the second table each further comprise changing write scripts from the original table to the renamed table.

6. The method of claim 1, further comprising validating whether the renamed second table has any requisite permissions related to accessing data within the renamed second table, and executing a chmod command when any requisite permissions are not included in the renamed second table.

7. The method of claim 1, further comprising validating whether the renamed second table has data quotas set to match data quotas of the first table.

8. The method of claim 1, further comprising routing, while the transferring all data is being performed, received queries to the renamed first table when data in the first table is being queried and routing the received queries to the second table when new data being queried.

9. A system comprising:

one or more processors; and

a non-transitory computer readable medium storing a plurality of instructions, which when executed, cause the one or more processors to;

receive a request to add a new column to a first table stored within a distributed file system, the first table having an associated name;

create in response to the request, a second table that includes all fields of the first table, the new column, and a new partition including data from the new column, the second table having an associated name;

cause any new data sent for the first table to be included in the second table;

cause in response to the request, the first table to be renamed, the renaming comprising changing a pointer of the distributed file system to refer to the renamed first table, the renaming being performed using an atomic operation;

create a view pointing to data in each of the second table and the renamed first table, the view being a virtual table created on the distributed file system;

cause all data from the renamed first table to be transferred to the second table by adding pointers to the data to the second table; and

cause the second table to be renamed to the name of the first table when the transfer of the data from the renamed first table to the second table is complete, the renaming comprising changing the pointer of the distributed file system to refer to the renamed second table, being performed using an atomic operation, and causing any data received after renaming the second table to be included in the renamed second table.

10. The system of claim 9, wherein the transfer all data from the renamed first table is performed using a “

load data inpath”

command.

11. The system of claim 9, wherein the transfer all data further comprises validating if unmoved data exists within the renamed first table and validating if all data has been moved to the second table.

12. The system of claim 9, wherein the transfer all data is performed without copying any data between tables.

13. The system of claim 9, wherein the renaming the first table and renaming the second table each further comprise changing write scripts from the original table to the renamed table.

14. The system of claim 9, wherein the plurality of instructions, when executed, further cause the one or more processors to validate whether the renamed second table has any requisite permissions related to accessing data within the renamed second table, and execute a chmod command when any requisite permissions are not included in the renamed second table.

15. The system of claim 9, wherein the plurality of instructions, when executed, further cause the one or more processors to route, while the transferring all data is being performed, received queries to the renamed first table when data in the first table is being queried and routing the received queries to the second table when new data being queried.

16. A computer program product comprising computer-readable program code to be executed by one or more processors when retrieved from a non-transitory computer-readable medium, the program code including instructions to:

receive a request to add a new column to a first table stored within a distributed file system, the first table having an associated name;

create in response to the request, a second table that includes all fields of the first table, the new column, and a new partition including data from the new column, the second table having an associated name;

cause any new data sent for the first table to be included in the second table;

cause in response to the request, the first table to be renamed, the renaming comprising changing a pointer of the distributed file system to refer to the renamed first table, the renaming being performed using an atomic operation;

create a view pointing to data in each of the second table and the renamed first table, the view being a virtual table created on the distributed file system;

cause all data from the renamed first table to be transferred to the second table by adding pointers to the data to the second table; and

cause the second table to be renamed to the name of the first table when the transfer of the data from the renamed first table to the second table is complete, the renaming comprising changing the pointer of the distributed file system to refer to the renamed second table, being performed using an atomic operation, and causing any data received after renaming the second table to be included in the renamed second table.

17. The computer program product of claim 16, wherein the transfer all data from the renamed first table is performed using a “

load data inpath”

command.

18. The computer program product of claim 16, wherein the transfer all data further comprises validating if unmoved data exists within the renamed first table and validating if all data has been moved to the second table.

19. The computer program product of claim 16, wherein the transfer all data is performed without copying any data between tables.

20. The computer program product of claim 16, wherein the renaming the selected table and renaming the new table each further comprise changing write scripts from the original table to the renamed table.

21. The computer program product of claim 16, wherein the program code includes further instructions to validate whether the renamed second table has any requisite permissions related to accessing data within the renamed second table, and execute a chmod command when any requisite permissions are not included in the renamed second table.

22. The computer program product of claim 16, f wherein the program code includes further instructions to validate whether the renamed second table has data quotas set to match data quotas of the first table.

23. The computer program product of claim 16, wherein the program code includes further instructions to route, while the transferring all data is being performed, received queries to the renamed first table when data in the first table is being queried and routing the received queries to the second table when new data being queried.