GBASE Financial Application Guide 5 | System Development Standards
To help financial institutions select distributed analytical database products and promote best practices for deploying such databases in the financial industry, GBASE General Data Technology, under the guidance of the Beijing Fintech Industry Alliance, has compiled the “GBase 8a Financial Application Guide”. This Guide provides an in-depth look at the entire deployment process of a distributed analytical database—from selection planning and development design specifications to data security, high availability, and operational optimization—and shares representative deployment cases of the GBase 8a MPP Cluster at national policy banks and large state-owned banks.
GBASE will release a series of articles to interpret and share insights from the Guide, with the hope of providing reference for database selection by financial users and supporting the efficient implementation and high-quality development of technology finance.
This article is the fifth in the series, introducing the system development specifications.
1. Model Design
GBase 8a offers multiple data distribution strategies. Changing the strategy later requires recreating tables and reloading data, which is costly. Similarly, altering the table model after it is set can affect upper-layer applications and involve significant effort. Model design is a time-consuming phase in project construction, requiring a thorough understanding of the business and close collaboration between business and technical personnel.
1. Data Model
Dimensional Model
GBase 8a is a relational database management system. The following three dimensional models are commonly implemented in such systems:
• Star Schema: All dimension tables are directly connected to the fact table. Redundant data may be added between dimension and fact tables to improve query performance. Suitable for scenarios with high query performance requirements.
• Snowflake Schema: An extension of the star schema where dimension tables are further normalized into hierarchies; some dimension tables connect to the fact table via other dimension tables, eliminating redundancy and reducing data storage. Suitable for scenarios with limited storage, lower cost requirements, and relatively lower query performance needs.
• Fact Constellation Model: An extension of the star schema with multiple fact tables, where a dimension table can be shared by multiple fact tables. Suitable for scenarios requiring multiple fact tables.
2. Data Distribution Strategy
GBase 8a supports three data distribution modes: replicated distribution, hash distribution, and random distribution, corresponding to three table types: replicated tables, hash-distributed tables, and random-distributed tables. Determine the table type during the model design phase and create tables accordingly.
• Replicated Table: Each node in the GBase 8a cluster stores the full dataset of the table. Suitable for dimension tables or tables with small data volumes. Its advantage is that join operations do not require cross-node data pulling.
• Hash-distributed Table: Each node stores a portion of the table’s data. A hash distribution key must be chosen at table creation to control data placement across nodes. Suitable for fact tables or large tables, helping to avoid performance degradation caused by hot data skew on individual nodes.
• Random-distributed Table: Each node stores a portion of the table’s data, distributed randomly and evenly across nodes without the ability to control placement. Can be used for fact tables or large tables, but because data placement is uncontrolled, hot data issues cannot be prevented, so it is less frequently used in projects.
3. Data Compression Algorithm
GBase 8a compresses data at the storage level and provides multiple compression modes for users to choose from. These algorithms can achieve compression ratios from 3:1 to 20:1, depending on the data characteristics and chosen algorithm.
• A higher compression ratio results in less disk space usage but relatively longer compression and decompression times;
• A lower compression ratio uses more disk space but yields faster compression and decompression.
• Users should choose a compression mode that balances actual hardware conditions and business performance requirements.
GBase 8a compression settings can be applied at the global, database, and table levels. When multiple levels are configured, the priority is: column-level compression > table-level compression > global compression setting.
4. Character Set
GBase 8a currently supports UTF8, UTF8MB4, GBK, and GB18030-2022 character sets.
Character set conversion flow when using GBase 8a.
Figure 1: Character Set Conversion Process
It is recommended to keep encoding consistent throughout the conversion pipeline. The GBase 8a Server’s character set can affect the following:
• Maximum length of character data types varies by encoding;
• Data comparison and sorting behavior within the database may differ.
Sorting rules for different character sets are shown in Table 1.
Table 1: Sorting Rules for Different Character Sets
2. SQL Coding Guidelines
The SQL execution engine and optimizer in GBase 8a have unique characteristics. The following SQL coding guidelines help leverage these features to maximize GBase 8a’s performance.
1. GBase 8a Identifier Rules:
1) If an identifier is a reserved word or contains special characters, it must be enclosed in backticks (`), e.g., SELECT * FROM `select`.id > 100;
2) If an identifier exceeds the maximum length limit, commands for databases, tables, columns, views, or stored procedures will raise an error. It is recommended to use meaningful aliases in SQL;
3) By default, GBase 8a does not support Chinese characters in table or column names. To enable Chinese names, a parameter must be configured;
4) Replicated table names must not end with _n[N], where [N] is a digit.
2. SQL Coding Specifications:
GBase 8a SQL complies with the SQL-92 standard (ANSI X3.135-1992, ISO/IEC 9075:1992) and supports most data types defined in SQL:1999 and SQL:2003 (ISO/IEC 9075:1999, SQL:1999 (SQL3) and ISO/IEC 9075:2003, SQL:2003 (SQL4)).
SQL features of GBase 8a:
1) Cluster table types differentiate between distributed tables, replicated tables, and temporary tables, with specific keywords used in table creation;
2) Each SQL command ends with a semicolon (;) by default; DELIMITER can be used to modify it:
DELIMITER delimiter (must be changed in stored procedures);
3) SQL execution defaults to auto-commit;
4) HASH distribution key does not support date/time data types;
5) Supports altering varchar column length and changing the table character set from UTF8 to UTF8MB4;
6) The SQL command SHRINK SPACE should not be executed frequently;
7) INSERT, UPDATE, and DELETE operations on views are prohibited;
8) INSERT supports standard SQL syntax and extended syntax. The batch form INSERT INTO t VALUES (v1,…vn), (v2,…vn),…(vm,…vn) performs well;
9) UPDATE does not support updating values of the distributed-by column. Multi-table update is not supported in a single statement, but the UPDATE syntax can join multiple tables while updating only one table. Fast update mode is supported;
10) DELETE does not support multi-table batch deletion;
11) MERGE operations are supported only on hash-distributed tables, and the MERGE condition must include the business primary key and the hash distribution column;
12) User-defined functions do not support DML, DDL, or creation of temporary tables;
13) Maximum 2000 columns per table, maximum total column width 300,000 bytes, and maximum 64 indexes per table.
3. Using Development Interfaces
The GBase 8a cluster provides interface drivers for mainstream development platforms. These drivers implement high availability and load balancing for application requests (by configuring multiple cluster management node IPs in the connection string, the driver handles high availability and load balancing internally).
You can select the following interfaces as needed: GBase ADO.NET, GBase JDBC, GBase ODBC, GBase C-API, GBase Python.
GBase 8a interfaces offer the following features for your selection:
• When using interfaces to execute load SQL, load results can be determined from the interface's return information;
• JDBC provides a batch insertion interface. When inserting large amounts of data via JDBC, use batch insertion parameters to send data in batches to the server, reducing network transmission and allowing the server to process multiple SQL statements at once for improved performance;
• When query result sets are large, use streaming read functionality of the interface. Streaming reads fetch data row by row from the cluster, reducing the impact of large result sets on application memory. Streaming reads create a temporary table on the cluster side, consuming temporary tablespace, and the connection remains open until all result data is consumed. Use streaming reads with caution as they affect the cluster;
• When applications require high security for connecting to the GBase 8a cluster, interface features such as SSL encrypted transmission, Kerberos authentication, and SSH tunneling can be used as needed.
4. Database Management Specifications
• GBase 8a is not suitable for OLTP transactional applications;
• GBase 8a cluster supports mixed use of UTF8 and GBK character sets, and allows simultaneous use of UTF8 and UTF8MB4, or GBK and GB18030;
• Data models should be normalized following data warehouse development principles;
• Separate development and production environments;
• Establish a proper task scheduling mechanism to minimize database concurrency while meeting business requirements;
• Conduct performance evaluation tests before going live.