Research On Multi-Tenant Data Storage Mechanism Based On Universal Table In SaaS

Multi-tenant data storage is an important research emphasis in SaaS (Software as a service). In order to achieve the characteristic "single instance multi-tenancy" of SaaS, people have proposed many solutions of which schema-mapping technology is very popular and there are some specific methods, such as private table, universal table, extended table, column-oriented storage, pivot table and so on. All the methods above have their own advantages and scenarios.Universal table method means we have shared data storage for all the tenants and it is popular for transaction-oriented applications because it reduces the cost of data storage greatly. However, the universal table approach also needs certain improvements due to the large columns, the sparse data and the difficulty in schema mapping or query rewriting. The aims of the improvements will be better utilization of data storage space and higher efficiency of data query.Based on the talk above, this paper proposes the improving for both the utilization of data storage space and the query efficiency. The improved approach has multi-universal table data storage mechanism including the sparse data storage, the schema-mapping mechanism, the query rewriting method based on view-replacing and so on. The main work and achievements of the paper include:(1) Proposed multi-universal sparse tables data storage. As an improved method of universal table, this approach has replaced the original single table with several sparse tables which have different columns. So, the method has better utilization of data storage space and query efficiency by reducing the presence of nulls to improve the data-intensive.(2) Proposed the schema-mapping mechanism under the multi-sparse tables approach. At the same time, the paper gives the formal description of tenants' customization and the method to transform it to metadata. Also, it proposed the sparse tables dividing method based on normal distribution and application businesses, the table-choosing method base on reserved columns, the mapping form tenants' customization to metadata.(3) Proposed the view-replace query rewriting method. It makes the query rewriting easy, simplifies the metadata access interface and protects the metadata access by encapsulate it. At the same time, it rewrites the SQLs with a method of view definition replacing. The query rewriting method reduces the difficulty of schema mapping and thus improves the efficiency of data query.The multi-sparse tables approach has improved the traditional sparse table method. Using this method, tenant can have their suitable sparse table and have more intensive data storage. The approach reduces the impact on data storage and query. The view definition, cache and query rewriting based on view replacement protect the metadata and make the schema mapping easy. In a word, the multi-sparse tables approach improves the data storage and query while remaining the lower storage cost.