Database Development Environment

Abstract

Huawei's GaussDB (for MySQL) supports the development of applications based on C, Java and other languages. Understanding GaussDB (for MySQL) related system structure and concepts help to develop and use GaussDB (for MySQL) database better.

Huawei's GaussDB (for MySQL) supports the development of applications based on C, Java and other languages. Understanding GaussDB (for MySQL) related system structure and concepts help to develop and use GaussDB (for MySQL) database better.

This chapter explains the use of GaussDB (for MySQL) tools. Before this, readers need to have knowledge of operating systems, as well as C and Java languages, and be familiar with the IDE and SQL syntaxes of C or Java languages.

6.1 GaussDB Database Driver

6.1.1 What Is a Driver

A database driver is an interface between an application and a database store. A driver application is a translator-like program developed by the database vendor to enable a particular development language (e.g. Java and C) to implement database calls. It is able to abstract complex database operations and communications into an access interface for the current development language, as shown in Fig. 6.1.

Fig. 6.1

Database driver

To meet the requirements, GaussDB (for MySQL) supports database drivers such as JDBC and ODBC.

The data source contains information such as database location and database type, which is actually an abstraction of a data connection. The data source manager shown in Fig. 6.1 is used to manage data sources.

6.1.2 JDBC

Java database connectivity (JDBC) is a Java API for executing SQL statements that provides a unified access interface to a variety of relational databases. Applications manipulate data through JDBC. The flow of JDBC connection to database is shown in Fig. 6.2.

Fig. 6.2

Flow of JDBC connection to database

GaussDB (for MySQL) database provides support for JDBC4.0 features. To compile the program code, you need to use JDK1.8.

The installation and configuration steps of JDBC are as follows.

• (1) Configure the JDBC package.

  Download the driver package from the relevant website, decompress it and configure it in the project.

  JDBC package name: com.huawei.gauss.jdbc.ZenithDriver.jar.

• (2) Load the driver.

  Before creating a database connection, you need to load the database driver class by loading Class.forName("com.huawei.gauss.jdbc.ZenithDriver") implicitly in the code.

• (3) Connect to the database.

  Before remotely accessing the database, you need to set the IP address and port number for LSNR_IP and LSNR_PORT monitoring in the configuration file zengine.ini.

  When creating a database connection using JDBC, the following function is required.

  DriverManager.getConnection(String url, String user, String password);

  Another way to load the database driver classes is to pass the parameters at the start of the JVM (Java Virtual Machine), where jdbctes is the name of the test case program.

  java -Djdbc.drivers=com.huawei.gauss.jdbc.ZenithDriver jdbctest;

This method is not commonly used, so you just need to know about it without going into particular detail.

Up to 8 monitoring IP addresses can be set at a time, with the IP addresses separated by commas.

After the database driver class is loaded, you need to connect to the database. Before remote access to the database, set the IP address and port number to be monitored by the corresponding parameters in the configuration file, and then use the JDBC to create a database connection. The database connection includes three parameters: url, user, and password, as shown in Table 6.1.

Table 6.1 Database connection parameters

In the url parameter, ip is the database server name, port is the database server port, and the url connection attributes are split by the & symbol. Each property is a key/value pair.

Table 6.2 shows the common interfaces of the JDBC.

Table 6.2 JDBC's common interfaces

The following introduces the development and debugging of JDBC application with the Eclipse environment under Windows operating system as an example.

• Operating system environment: Win10-64bit.

• Compiling and debugging environment: Eclipse SDK version:3.6.1.

The steps of running JDBC application are shown below.

• (1) Create a project in Eclipse.

  New→Project→Java Project→Next→enter ProjectName (such as test_jdbc)→Finish.

• (2) Create a class.

  src→New→Class→enter ClassName (jdbc_test, choose main)→Finish.

• (3) Load the library.

  src→build path→configure build path→libraries →add external jars→jdbcjar.

• (4) Run the JDBC application.

  Write jdbc_test.java document, right click on jdbc_test →run as→Java Application.

The code for compiling and running the JDBC application is as follows.

package com.huawei.gauss.jdbc.executeType; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import com.huawei.gauss.jdbc.inner.GaussConnectionImpl; public class jdbc_test{ public void test() { // 驱动类 String driver = "com.huawei.gauss.jdbc.ZenithDriver"; // 数据库连接描述 String sourceURL = "jdbc:zenith:@10.255.255.1:1888"; Connection conn = NULL; try { // 加载数据库驱动类 Class.forName(driver).newInstance(); } catch (Exception e) { // 抛出异常 e.printStackTrace(); } try { // 数据库连接，test_1为用户名，Gauss_234为密码 conn = DriverManager.getConnection (sourceURL, "test_1", "Gauss_234"); WHILE(TRUE) { // 执行SQL语句，若在数据库中能查看到此条数据，说明成功 PreparedStatement ps = conn.prepareStatement ("INSERT INTO t1 values (1, 2)"); ps.execute(); // 若执行成功，控制台会输出“Connection succeed!” System.out.println("Connection succeed!"); } } catch (Exception e) { // 抛出异常 e.printStackTrace(); } } }

6.1.3 ODBC

Open database connectivity (ODBC) was proposed by Microsoft as an application programming interface for accessing databases. The ODBC connection to database includes the flows of requesting handle resources, setting environment properties, connecting to data sources, executing SQL statements, processing result sets, disconnecting, and releasing handle resources, as shown in Fig. 6.3.

Fig. 6.3

Flow of ODBC connection to database

The application interacts with the database through the API provided by ODBC, which enhances the portability, scalability and maintainability of the application while avoiding the application to operate the database system directly.

The steps to install ODBC driver manager are as follows.

• (1) Obtain the unixODBC source code package.

  Download the file unixODBC-2.3.7.tar.gz or a higher version.

• (2) Compile and install unixODBC.

  In the process of compiling and installing unixODBC, unixODBC is installed to “/usr/local” directory by default, and the data source file is generated to “/usr/local/etc” directory by default, and the library file is generated in the “/usr/local/lib” directory.

  tar -zxvf unixODBC-2.3.7.tar.gz cd unixODBC-2.3.7 ./configure --enable-gui=no make make install

• (3) Configure the ODBC driver file.

  The ODBC driver package for GaussDB (for MySQL) is named as GAUSSDB100-VxxxRxxxCxx-CLIENT-ODBC- SUSE11SP3-64bit.tar.gz. Extract it to the ODBC driver directory “/usr/local/lib”.

  tar -zxvf GAUSSDB100-VxxxRxxxCxx-CLIENT-ODBC-SUSE11SP3-64bit.tar.gz

  Add the following to the “/usr/local/etc/odbcinst.ini” file.

  [ GaussDB ] Driver64=/usr/local/odbc/lib/libzeodbc.so setup=/usr/local/lib/libzeodbc.so

  Explanation

  The parameters of odbcinst.ini file are explained as follows. [DriverName]: driver name, corresponding to the driver name in the data source DSN, e.g. [DRIVER_N]. Driver64: path of the dynamic library for the driver, e.g. Driver64=/xxx/odbc/lib/libzeodbc.so. setup: the path of driver installation, same as the path of dynamic library in Driver64, e.g. setup=/xxx/odbc/ lib/libzeodbc.so.

  Append the following to the “/usr/local/etc/odbc.ini” file.

  [zenith] Driver=DRIVER_N Servername=192.168.0.1(数据库Server IP) Port=1888 (数据库监听端口)

  Explanation

  The parameters of odbc.ini file are explained as follows. [DSN]: name of the data source, e.g. [zenith]. Driver: driver name, corresponding to DriverName in odbcinst.ini, e.g. Driver=DRIVER_N. Servername: IP address of the server, e.g. Servername=192.168.0.1. Port: port number of the server, e.g. Port=1888.

• (4) Configure environment variables.

  export LD_LIBRARY_PATH=/usr/local/lib/:$LD_LIBRARY_PATH export ODBCSYSINI=/usr/local/etc export ODBCINI=/usr/local/etc/odbc.ini

  Table 6.3 shows the commonly used interfaces of the ODBC.

Some ODBC interfaces are described as follows.

• (1) The interface to allocate ODBC handles.

  SQLRETURN SQL_API SQLAllocHandle(SQLSMALLINT HandleType,SQLHANDLE InputHandle, SQLHANDLE *OutputHandle)

  SQLAllocHandle parameters are introduced as follows.

  Input parameters: HandleType, the type of handle to be allocated (SQL_HANDLE_ENV, SQL_HANDLE _DBC, SQL_HANDLE_STMT); InputHandle, the dependent handle.

  Output parameter: OutputHandle, the allocated handle.

  Return value: SQL_SUCCESS indicates success; !=SQL_SUCCESS indicates failure.

• (2) The interface to allocate the ODBC environment handle.

  SQLRETURN SQL_API SQLAllocEnv(SQLHENV *EnvironmentHandle)

  SQLAllocEnv parameter is introduced as follows.

  Output parameter: EnvironmentHandle, the environment handle assigned to it.

  Return value: SQL_SUCCESS indicates success; !=SQL_SUCCESS indicates failure.

• (3) The interface to assign the ODBC link handle.

  SQLRETURN SQL_API SQLAllocConnect(SQLHENV EnvironmentHandle,SQLHDBC *Connection Handle)

  SQLAllocConnect parameters are introduced as follows.

  Input parameter: EnvironmentHandle, the environment handle.

  Output parameter: ConnectionHandle, the link handle assigned to it.

  Return value: SQL_SUCCESS indicates success; !=SQL_SUCCESS indicates failure.

• (4) The interface to assign the ODBC execution handle.

  SQLRETURN SQL_API SQLAllocStmt(SQLHDBC ConnectionHandle,SQLHSTMT *Statement Handle)

  SQLAllocStmt parameters are introduced as follows.

  Input parameter: ConnectionHandle, the link handle.

  Output parameter: StatementHandle, the execution handle assigned to it.

  Return value: SQL_SUCCESS indicates success; !=SQL_SUCCESS indicates failure.

• (5) The interface to release ODBC handles.

  SQLRETURN SQL_API SQLFreeHandle(SQLSMALLINT HandleType, SQLHANDLE Handle)

  SQLFreeHandle parameters are introduced as follows.

  Input parameters: HandleType, the type of handle to be released (SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT); Handle, the handle.

  Return value: SQL_SUCCESS indicates success; !=SQL_SUCCESS indicates failure.

• (6) The interface to release ODBC environment handle.

  SQLRETURN SQL_API SQLFreeEnv(SQLHENV EnvironmentHandle)

  SQLFreeEnv parameters are introduced as follows.

  Input parameter: EnvironmentHandle, the environment handle to be released.

  Return value: SQL_SUCCESS indicates success; !=SQL_SUCCESS indicates failure.

• (7) The interface to release ODBC link handle.

  SQLRETURN SQL_API SQLFreeConnect(SQLHDBC ConnectionHandle)

  SQLFreeConnect parameters are introduced as follows.

  Input parameter: ConnectionHandle, the link handle to be released.

  Return value: SQL_SUCCESS indicates success; !=SQL_SUCCESS indicates failure.

• (8) The interface to release ODBC execution handle.

  SQLRETURN SQL_API SQLFreeStmt(SQLHSTMT StatementHandle,SQLUSMALLINT Option)

  SQLFreeStmt parameters are introduced as follows.

  Input parameters: StatementHandle, the execution handle to be released; Option, the type to be released (SQL_DROP).

  Return value: SQL_SUCCESS indicates success; !=SQL_SUCCESS indicates failure.

  ODBC application debugging under Windows operating system can be performed using the common VC (Visual C++) compilation environment. The following is an example of the debugging process of ODBC application for Linux platform.

  • Operating system environment: Linux.

  • Compiler: GCC 4.3.4.

  • Debugger: CGDB 0.6.6/GDB 7.6.

    The steps of running and debugging JDBC application are shown below.

• (1) Write the JDBC application.

  Write the corresponding code and name the document as test_odbc.c.

• (2) Compile.

  Use the gcc -o test -g test_odbc.c -L/home/test/ -lzeodbc -lodbc -lodbcinst command to compile the test_odbc.c file into a test binary program. This compilation requires the GCC compiler.

• (3) Run.

  Execute the ./test command to run the binary program.

• (4) Debug.

  Use the gdb/cgdb test command for debugging.

  The code for compiling and running the ODBC application is as follows.

#if WIN32 #include <windows.h> #endif #include <stdlib.h> #include <stdio.h> #include "sql.h" #include "sqlext.h" int main() { SQLHANDLE h_env, h_conn, h_stmt; SQLINTEGER ret; SQLCHAR *dsn = (SQLCHAR *)"myzenith";/*数据源名称*/ SQLCHAR *username = (SQLCHAR *)"sys";/*用户名*/ SQLCHAR *password = (SQLCHAR *)"sys";/*密码*/ SQLSMALLINT dsn_len = (SQLSMALLINT)strlen((const CHAR *)dsn); SQLSMALLINT username_len = (SQLSMALLINT)strlen((const CHAR *) username); SQLSMALLINT password_len = (SQLSMALLINT)strlen((const CHAR *) password); h_env = h_conn = h_stmt = NULL; //申请句柄资源 ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &h_env); if ((ret != SQL_SUCCESS)&&(ret != SQL_SUCCESS_WITH_INFO)) { return SQL_ERROR; } //设置环境句柄属性 if (SQL_SUCCESS != SQLSetEnvAttr(h_env, SQL_ATTR_ODBC_VERSION, (void*)SQL_ OV_ODBC3, 0)) { SQLFreeHandle(SQL_HANDLE_ENV, h_env); return SQL_ERROR; } //分配链接句柄 if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, h_env, & h_conn)) { return SQL_ERROR; } //设置链接句柄自动提交属性 if (SQL_SUCCESS != SQLSetConnectAttr(h_conn, SQL_ATTR_AUTOCOMMIT, (void *)1, 0)) { SQLFreeHandle(SQL_HANDLE_DBC, h_conn); // 用于释放ODBC的句柄 SQLFreeHandle(SQL_HANDLE_ENV, h_env); return SQL_ERROR; } //链接数据源 if (SQL_SUCCESS != SQLConnect(h_conn, dsn, dsn_len, username, username_len, password, password_len)){ SQLFreeHandle(SQL_HANDLE_DBC, h_conn); SQLFreeHandle(SQL_HANDLE_ENV, h_env); return SQL_ERROR; } //申请执行句柄 if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, h_conn, &h_stmt)) { SQLFreeHandle(SQL_HANDLE_DBC, h_conn); SQLFreeHandle(SQL_HANDLE_ENV, h_env); return SQL_ERROR; } //创建表并插入一条记录 SQLCHAR* create_table_sql = (SQLCHAR*)"CREATE TABLE test(col INT)"; SQLExecDirect(h_stmt, create_table_sql, strlen (create_table_sql)); // 直接执行SQL语句 SQLCHAR* insert_sql = (SQLCHAR*)"INSERT INTO test (col) values (:col)"; SQLPrepare(h_stmt, insert_sql, strlen(insert_sql)); // 准备要 执行的SQL语句 int col = 1; SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, sizeof(int), 0, &col, 0, NULL); // 往准备好SQL的执行句柄上绑定参数 SQLExecute(h_stmt); // 执行SQL语句 printf("Connection succeed!\n"); //断开数据库链接 SQLDisconnect(h_conn); //释放句柄资源 SQLFreeHandle(SQL_HANDLE_DBC, h_conn); SQLFreeHandle(SQL_HANDLE_ENV, h_env); return SQL_SUCCESS; }

Table 6.3 Commonly Used Interfaces of the ODBC

6.1.4 Others

In addition to supporting development based on JDBC and ODBC drivers, GaussDB (for MySQL) also supports development based on GSC (C-API), Python and Go drivers.

• (1) GSC (C-API) driver: the dependent library is libzeclient.so and the header file is gsc.h.

  When creating a database connection using the GSC (C-API), the following function is required.

  int gsc_connect(gsc_conn_t conn, const CHAR * url, const CHAR * user, const CHAR * password);

  The code to create a connection object using the GSC (C-API) is as follows.

  int test_conn_db(CHAR * url, CHAR * user, CHAR * password) { gsc_conn_t conn; if (gsc_alloc_conn(&conn) != GSC_SUCCESS) { return GSC_ERROR; } if (gsc_connect(conn, url, user, password) != GSC_SUCCESS) { return GSC_ERROR; } gsc_free_conn(conn); conn = NULL; //to avoid using wild pointer, user should set conn NULL after free return GSC_SUCCESS; }

• (2) Go driver. The Go driver is released as source code, and the upper-level application brings the code into the application project and compiles it with the application for use. From the file level, Go driver is divided into three parts: Go API, C driver library and C header file. The Zenith Go driver is based on the Zenith C driver, which is obtained through cgo technology packaging. The lib subdirectory is the dynamic library for C driver, and the include subdirectory is the C driver cgo involved in the header files. The Go driver relies on GCC 5.4 and above, and use the GO 1.12.1 or an higher version.

• (3) Dynamic library of the Python driver: pyzenith.so. When using the Python driver to connect to a database, get the Connection and establish the connection by calling pyzenith.connect. GaussDB (for MySQL) uses Python, on the basis of the Linux operating system. Python supports time objects, using the following functions to get the time.

  • Date(year,month,day)—constructs an object containing the date.

  • Time(hour,minute,second)—constructs an object containing the time.

  • Timestamp(year,month,day,hour,minute,second,usec)—constructs an object containing the timestamp.

  • DateFromTicks(ticks)—construct the date value given with ticks value.

  • TimeFromTicks(ticks)—constructs the time value given with ticks value.

  • TimestampFromTicks(ticks)—constructs the timestamp value given with ticks value.

  • The sample code to execute the SQL statement and get all the tuples is as follows.

    import pyzenith conn=pyzenith.connect('192.168.0.1','gaussdba','database_123','1888') c=conn.cursor() c.execute("CREATE TABLE testexecute(a INT,b CHAR(10),c DATE)") c.execute("INSERT INTO testexecute values(1,'s','2012-12-13')") c.execute("SELECT * FROM testexecute") row =c.fetchall() c.close() conn.close()

6.2 Database Tools

6.2.1 DDM

Distributed database middleware (DDM) service is a middleware service for distributed relational databases provided by Huawei Public Cloud. It provides applications with distributed and transparent access to multiple database instances in the form of a service, which completely solves the database scalability problem and realizes storage of massive data and high concurrent access; it features easy-to-use, unlimited expansion and excellent performance. Easy-to-use refers to compatibility with MySQL protocol and zero changes to application code; unlimited expansion refers to supporting automatic horizontal splitting, completely solving the database problem of single machine restriction and realizing smooth expansion of service department terminals; excellent performance refers to the combination of high-performance cluster networking (unipolar during public beta) and horizontal expansion functions, thus realizing the linear improvement of performance. The DDM service flow is shown in Fig. 6.4.

Fig. 6.4

DDM service flow

DDM service puts all data into one database in the primary stage, whether it is small-, medium- or large-scale database or above. Small-scale (<500 qps or 100 tps, <100 read users, <10 write users): the performance of concurrent reads is improved by read/write separation in a single database. Medium-scale (<5000 qps or 1000 tps, <5000 read users, <100 write users): vertical splitting, distributing different services to different databases. Large-scale and above (10k+ qps, 10k+ tps, 10k+ read users, 1k+ write users): data sharding, dividing the data set into mutually independent and orthogonal data subsets according to certain rules, and then distributing the data subsets to different nodes.

Data sharding is used to solve the bottlenecks of database scaling. Commonly used data sharding solutions are application-layer sharding solution and middleware sharding solution, as shown in Fig. 6.5.

Fig. 6.5

Common data sharding solutions

The advantage of application-level sharding solutions (such as Dangdang's Sharding-JDBC, Taobao's distributed data framework, etc.) is that they are directly connected to the database and have less additional overhead. Its disadvantages are the inability to achieve convergence in the number of connections, the application intrusion approach used leading to a large number of subsequent upgrades and updates and the high cost of operation and maintenance, and the fact that only Java is supported in most cases. The advantages of middleware sharding solutions (e.g., open source Mycat, Cobar, commercial software Ekoson, etc.) are zero changes to the application, language-independence, full transparency to the application for database scaling, and effective convergence of the number of connections through connection sharing. The disadvantage is the possibility of additional latency (<4%).

The key features of DDM are read/write separation, data sharding, and smooth database scaling. In the past, the read/write separation was controlled by the application itself, including configuring all database information in the client and realizing the read/write separation; database adjustment requires synchronous modification of the application, and database failure requires modification of the application, at which time the operation and maintenance and development need to synchronize the adjustment configuration. Nowadays, DDM achieves read/write separation, including: plug-and-play—automatic read/write separation and support for configuring performance weights for different nodes; application transparency—the application still operates a single node and database adjustments are not application-aware; and high availability—master-slave switchover or slave node failure is transparent to the application, as shown in Fig. 6.6.

Fig. 6.6

Comparison of read/write separation

The sharding application logic implemented by the application itself is complex: the application rewrites SQL statements, routes the SQL to different databases, and aggregates the results; database failure and adjustment require synchronous adjustment by the application, which makes operation and maintenance more difficult dramatically; the application upgrade and update maintenance workload is large and unacceptable for large systems. Today, data sharding is implemented by DDM with zero application changes: large table sharding, which supports automatic sharding by hash and other algorithms; automatic routing, which routes SQL to the real data source according to the sharding rules; connection multiplexing, which is used to substantially improve concurrent database access through connection pool multiplexing of MySQL instances. A comparison of data sharding is shown in in Fig. 6.7.

Fig. 6.7

A comparison of data sharding

If the application itself implements the horizontal expansion of the database, the expansion is prone to application downtime and service interruption, so tools for data migration are required. Nowadays, horizontal expansion of database by DDM can automatically balance data, achieve unlimited expansion (unlimited number of supported shards, ease coping with massive data), full automation (one-click expansion, automatic rollback of abnormalities), and small impact on services (second-level interruption, no service awareness at other times). A comparison of database horizontal scaling is shown in Fig. 6.8.

Fig. 6.8

A comparison of database horizontal scaling

The applicable scenarios of DMM are as follows.

• (1) High-frequency transactions on large applications: e-commerce, finance, O2O, retail, and social applications. Characteristics: large user base, frequent marketing activities, and increasingly slow response of the core database. Countermeasures: The linear horizontal scaling function provided by DDM can easily cope with the high concurrent real-time transaction scenarios.

• (2) IoT massive sensors: industrial monitoring, smart city, and Internet of Vehicles. Characteristics: many sensing devices, high sampling frequency, large data scale, breakthrough of single database bottleneck. Countermeasures: The capacity horizontal expansion function provided by DDM can help users to store massive data at low cost.

• (3) Massive video and picture data index: Internet, social applications, etc. Characteristics: billions pieces of picture, document, video and other data, and extremely high performance requirements for indexing these files and providing real-time addition, deletion, change and query operations. Countermeasures: The ultra-high performance and distributed expansion function provided by DDM can effectively improve the search efficiency of the index.

• (4) Traditional program, hardware and government agencies: large enterprises and banks. Characteristics: Traditional solutions rely on commercial solutions with high hardware cost such as minicomputers and high-end storage. Countermeasures: The linear horizontal scaling function provided by DDM can easily cope with highly concurrent real-time transaction scenarios.

How to use DDM - buy a database middleware.

• Step 1: Console > Database > Distributed Database Middleware (DDM) Instance Management.

• Step 2: Click the “Buy Database Middleware Instance” button, as shown in Fig. 6.9.

• Step 3: Select “Pay As You Go” for the billing mode. Leave the default settings for region, available partitions and instance specifications if there are no special needs, as shown in Fig. 6.10.

• Step 4: Enter the instance name, select the corresponding virtual private cloud, subnet and security group (the virtual private cloud must be consistent with the database instance ), and then click the “Buy Now” button, as shown in Fig. 6.11.

• Step 5: Confirm the specification, check the check box to agree to the service agreement, and click the “Submit” button, as shown in Fig. 6.12.

Multi-instance and distributed cluster are shown in Fig. 6.13.

Fig. 6.9

Steps of instance purchase (1)

Fig. 6.10

Steps of instance purchase (2)

Fig. 6.11

Steps of instance purchase (3)

Fig. 6.12

Example purchase step diagram (4)

Fig. 6.13

The instance is purchased successfully

How to use DDM - data sharding.

• Step 1: Console > Database > Distributed Database Middleware (DDM) Instance Management.

• Step 2: Select the instance that needs to be sharded, and click the “Create Logical Library” text hyperlink, as shown in Fig. 6.14.

• Step 3: Select the split mode, set the logical library name and transaction model, and select the associated RDS instance, as shown in Fig. 6.15.

• Step 4: Select the RDS instance where the logical library can be created (same as the case of virtual private cloud), and click the “Create” button, as shown in Fig. 6.16.

  The data is successfully sharded, as shown in Fig. 6.17.

Fig. 6.14

Data sharding (1)

Fig. 6.15

Data sharding (2)

Fig. 6.16

Data sharding (3)

Fig. 6.17

Data sharding succeeded

6.2.2 DRS

Data replication service (DRS) is an easy-to-use, stable, and efficient cloud service for online database migration and real-time database synchronization. DRS targets cloud databases, reducing the complexity of data flow between databases and effectively reducing the cost of data transfer.

It features the following capabilities. Online migration: It supports a variety of service scenarios such as cross-cloud platform database migration, under-cloud database migration to the cloud or cross-region database migration on the cloud through various network links. It is characterized by incremental migration technology that allows continuous service during migration to the maximum extent, effectively minimizing service system interruption time and impact on service, and realizing smooth migration of database to the cloud. Data synchronization: Realizes the real-time flow of key service data between distinctly different systems. Database migration is aimed at overall relocation, while synchronization is to maintain the continuous flow of data between different service systems. Common scenarios are real-time analysis, reporting system, and data warehouse environment. It is characterized by focusing on tables and data to meet the need for multiple synchronization flexibility, such as many-to-one, one-to-many, which synchronizes data between different tables. Multi-live disaster recovery: Through off-site near-real-time data synchronization, it enables the establishment of database disaster recovery relationships cross regions, cross clouds, between local and cloud, and between hybrid clouds, providing disaster recovery features such as one-key master-standby reversal, data comparison, delay monitoring, data replenishment, supporting disaster recovery rehearsal, real disaster recovery and other scenarios, and supporting various disaster recovery architectures such as master-slave disaster recovery and master-master disaster recovery. It features offsite long-distance transmission optimization and provides features around disaster recovery, unlike the solutions formed based on simple data synchronization. Data subscription: It obtains data change information of key service \ (often needed by downstream service) in the database and caches such information and provides a unified SDK interface to facilitate downstream service subscription, acquisition and consumption, thus decoupling the database from downstream systems and sesrvice processes. Common scenarios include Kafka's subscription to MySQL incremental data.

DRS's key features—guided migration. The operation flow of guided migration is shown in Fig. 6.18.

Fig. 6.18

Operation flow of guided migration

Figure 6.19 shows how to use DRS—scenario-based selection.

Fig. 6.19

Scenario-based selection

Figure. 6.20 shows how to use DRS—network and security.

Fig. 6.20

Network and security

DRS contains multiple migration modes. If service interruption is acceptable, select the full migration mode. This mode is a one-time database migration, suitable for database migration scenarios where service is allowed to interrupt. Full migration migrates all database objects and data from a non-system database to the target database at one time, including tables, views, stored procedures, triggers, etc.

If service interruption is required to keep minimal, select the full + incremental migration mode. This mode is a continuous migration of database, which is suitable for scenarios sensitive to service interruption. After completing the migration of historical data to the target database through full migration, the incremental migration phase keeps the source database and target database data consistent through techniques such as catch logs and application logs.

The migration objects that can be selected include databases, tables, views, stored procedures, and triggers, as shown in Fig. 6.21.

Fig. 6.21

Multiple migration modes

DRS classifies users who need to be migrated into three categories, i.e. users who can be migrated completely, users who need to be downgraded and users who cannot be migrated, as shown in Fig. 6.22.

Fig. 6.22

User migration

In DRS parameter migration, most of the parameters that are not migrated do not cause the migration to fail, but they often have a direct impact on the operation and performance of the service. DRS supports parameter migration to make the service and application run more smoothly and worry-free after database migration. Service parameters include character set settings, maximum number of connections, scheduling related settings, lock wait time, Timestamp default behavior and connection wait time. The performance parameters include *_buffer size and -_cache size, as shown in Fig. 6.23.

Fig. 6.23

Parameter migration

Figure 6.24 shows how to use DRS - precheck.

Fig. 6.24

Precheck

Migration monitoring of DRS: You can understand the migration progress in real time by observing the macro display, and view the percentage progress of the full migration objects, such as table data, table structure, table indexes, etc. with long migration time. You can view the migration progress of specific migration objects through the table, and when the “number of objects” and “number of migrated objects” are equal, the migration of the object is complete. You can view the migration progress of each object through the “View Details” hyperlink, and when the progress is 100%, the migration is complete, as shown in Fig. 6.25.

Fig. 6.25

Migration monitoring

The macro comparison at object level is used to determine whether data objects are missing; the data is proofread in detail by data-level comparison. The comparison of rows and contents at different levels is shown in Fig. 6.26.

Fig. 6.26

Migration comparison

6.2.3 DAS

Data Admin Service (DAS) is a professional tool to simplify database management. It provides a good visual operation interface, which can greatly improve the efficiency and make data management both secure and simple. DAS features the following characteristics.

• (1) Realizes managing database on the cloud, and visual interface to connect and manage database.

• (2) Manages data through a dedicated channel in the cloud, where Huawei Cloud strictly controls access to the database with high security.

• (3) Accesses to data simply and conveniently, and manage operations via visual objects, easy to use.

• (4) Realizes cloud R&D testing, fast deployment, fast access to the database, and improved R&D efficiency.

The DAS includes console, standard version and enterprise version. The console (for database administrators and operation and maintenance personnel) provides basic host and instance performance data, slow SQL and full SQL analysis, covering from real-time performance analysis and diagnosis to comprehensive analysis of historical operation data, and can quickly find out every problem in database operation and potential risk points in advance. Standard version (for developers) is the best database client with advantages of no local client installation, WYSIWYG visual operation experience, synchronization of data and table structure, online editing, intelligent prompt of SQL input, and rich database development functions. Enterprise version (enterprise DevOPS platform) provides database DevOPS platforms for data protection, change auditing, operation auditing and R&D self-service based on permission minimization control and approval process mechanism, helping enterprises to realize standardized, efficient and ultra-secure management means for large-scale database.

DAS can help users build tables like filling out tables, and view, edit, insert and delete table data like editing Excel files; automated SQL input prompts can help users write SQL statements, while chain dependency diagrams show real-time lock waiting session relationships, so users can also be a professional database manager; automatical generation of table data make development work more convenient; when the data is modified or deleted by mistake, a rollback task can be initiated to help users retrieve the data; the automatic timeout mechanism allows users not to worry about bringing down the database due to the long execution time of SQL statements.

DAS application scenario: standard version, as shown in Figs. 6.27 and 6.28.

Fig. 6.27

DAS application scenario: standard version (1)

Fig. 6.28

DAS application scenario: standard version (2)

The Console in Fig. 6.27 is further divided into Conn Console and DAS Console. Conn Console is the console for DAS connection management, and DAS Console is the unified portal for data administration services. API-for-DAS is the unified entrance of external API, responsible for conversion of internal and external API protocols, as well as permission control and API audit. DAS provides database maintenance and management services, including addition, deletion, change and check of database, tables, indexes, fields, views and other objects.

DAS application scenario: enterprise version.

Data is the core asset of the enterprise. How to control the access rights of sensitive data, realize the security of database changes, audit the operation retroactively and reduce the labor cost of DBA is an important demand of enterprises when the number of database instances reaches a certain scale.

The advantages of DAS enterprise version are as follows.

• (1) Secure data access: Employees do not have access to database login name and password, and need to apply for permission first for querying the library; it supports multi-dimensional query control on total number of queries per day, total data rows, maximum number of rows returned per query, etc.

• (2) Sensitive data protection: Sensitive fields are automatically identified and marked; sensitive data will be desensitized and displayed when employees perform query and export operations.

• (3) Change security: All operations on the library are recorded in audit logs, and the database operation behavior is traceable.

• (4) Operation audit: It features risk identification of SQL change, service audit control; automatic detection of database water level when change is executed; and data cleaning for large data tables.

• (5) Improved efficiency and reduced cost: It features flexible security risk and approval process customization; the empowerment of the roles of service head and database administrator on the library delegates the low-risk library change operations to the service supervisor, reducing the labor cost of the database administrator in the enterprise.

How to use DAS—add a database connection. The steps are as follows.

• Step 1: Console > Database > Data Admin Service (DAS).

• Step 2: Click the “Add Database Login” button, as shown in Fig. 6.29.

• Step 3: Select the database type as GaussDB (for MySQL).

• Step 4: Select the database source (RDS instance), and select the instance under the corresponding source, as shown in Fig. 6.30.

• Step 5: Fill in the login user name and password under the selected instance, and it is recommended to check the “Remember Password” checkbox, as shown in Fig. 6.31.

• Step 6: Click the “Add Now” button, as shown in Fig. 6.32.

• Step 7: Select the database instance to log in, and click the “Login” hyperlink, as shown in Fig. 6.33.

• Step 8: Log in to the DAS Administration page successfully, as shown in Fig. 6.34.

How to use DAS—create an object.

Fig. 6.29

Add a database connection (1)

Fig. 6.30

Add a database connection (2)

Fig. 6.31

Add a database connection (3)

Fig. 6.32

Add Now

Fig. 6.33

Add a database connection (4)

Fig. 6.34

Add a database connection (5)

In the Library Management page, we can create and manage database objects, diagnose SQL, and collect metadata, following the steps as follows.

• Step 1: Click “New Database” on the homepage, fill in the database name and click "OK", as shown in Fig. 6.35.

• Step 2: After successful login, you can enter the Library Management page, as shown in Fig. 6.36.

• Step 3: Click the “New Table” button, as shown in Fig. 6.37.

• Step 4: Enter the New Table page, set the table's basic information, fields, indexes and other information, as shown in Fig. 6.38.

• Step 5: After setting up, click the “Create Now” button., as shown in Fig. 6.39.

• Step 6: In addition to tables, we can also create new views, stored procedures, events and other objects, as shown in Fig. 6.40.

How to use DAS—create an object.

Fig. 6.35

Create an object (1)

Fig. 6.36

Create an object (2)

Fig. 6.37

Create an object (3)

Fig. 6.38

Create an object (4)

Fig. 6.39

Create an object (5)

Fig. 6.40

Create an object (6)

Open the SQL Operations page, there will be automatic SQL input prompt for assisting to finish the SQL statement.

• Step 1: Click the “SQL Window” button at the top of the page, or the “SQL Query” hyperlink at the bottom to open the SQL Operations page, as shown in Fig. 6.41.

• On the SQL Operations page, we can perform SQL operations, such as query, etc.

• Step 2: Write SQL statements. DAS provides SQL prompt function to facilitate writing SQL statements, as shown in Fig. 6.42.

• Step 3: After the execution of SQL statement, you can check the operation result and execution record at the bottom, as shown in Fig. 6.43.

How to use DAS—import and export.

Fig. 6.41

SQL operation (1)

Fig. 6.42

SQL operation (2)

Fig. 6.43

SQL operation (3)

In the Import and Export page, we can import the existing SQL statements into the database for execution, and export the database file or SQL result set for saving.

• Step 1: Create a new import task. You can import an SQL file or CSV file.

• Step 2: Select the file source, either imported locally or from the OBS.

• Step 3: Select the database. The imported file will be executed within the corresponding database, as shown in Fig. 6.44.

• Step 4: Create a new export task and select the database file to be exported, or choose to export the SQL result set, as shown in Fig. 6.45.

How to use DAS - compare the table structures.

Fig. 6.44

Import and export (1)

Fig. 6.45

Import and export (2)

In the Structure Scheme page, we can compare the structures of the tables within the two databases and choose whether to synchronize after the comparison, as shown in Fig. 6.46.

• Step 1: Create a table structure comparison and synchronization tasks.

• Step 2: Select benchmark database and target database.

• Step 3: Select the synchronization type.

• Step 4: Start the comparison task.

• Step 5: Start the synchronization task.

Fig. 6.46

Compare the table structures

6.3 Client Tools

Client tools are mainly for users to connect, operate and debug databases more conveniently.

• (1) gsql is an interactive database connection tool run by GaussDB (DWS ) at the command line.

• (2) Data Studio is a graphical interface tool that allows users to connect to GaussDB (for MySQL) and debug and execute SQL statements and stored procedures through Data Studio.

6.3.1 zsql

The prerequisites for installing zsql are as follows.

• (1) Linux operating system is supported.

• (2) Python 2.7 needs to be deployed on the host where the client is located, and Python 2.7.x is mandatory for the host.

• (3) zsql client user and user group are created with permissions ≤0750.

• (4) The client installation package has been obtained and the integrity check of the zsql client installation package has been completed.

  For example, create the user group dbgrp and user omm, and add a password for omm.

  groupadd dbgrp useradd -g dbgrp -d /home/omm -m -s /bin/bash omm passwd omm

The method of integrity check of the zsql client installation package is as follows.

• (1) Execute the following command to output the check value of the installation package.

  sha256sum GAUSSDB100-V300R001C00-ZSQL-EULER20SP8-64bit.tar.gz

• (2) Check the contents of the sha256 file GAUSSDB100-V300R001C00-ZSQL-EULER20SP8-64bit.sha256.

• (3) Compare the check value with the contents of the check file. If they are consistent, the check passes, otherwise the check fails.

It should be noted that if you reinstall GaussDB100 zsql client program, you need to make sure the zsql client directory has been deleted, otherwise it will lead to reinstallation failure. It is not allowed to install the client program under the server program directory. For example, if the GaussDB100 database server program is already stored in the “/home/omm/app” directory, you cannot install the zsql client program in this directory, but need to plan the zsql client program to another directory for independent installation. After installation, you need to execute the su command to switch to the client user again.

If you need to uninstall the zsql client program, delete the files in the installation directory first. For example, if the client installer directory is “/home/omm/app”, you can delete this directory. Configure the user environment variable, open "~/.bashrc" environment variable (the command is “vi ~/.bashrc”), and delete the following contents.

export PATH=/home/omm/app/bin:$PATH export LD_LIBRARY_PATH=/home/omm/app/lib:/home/omm/app/add-ons:$LD_LIBRARY_PATH

After completing the installation of zsql, you need to log in to the server where GaussDB100 is located as the root user, i.e. the zsql client user. Take omm as an example, put the client installation package under the directory “/home/omm”, and modify the installation package user group.

cd /home/omm chown omm:dbgrp GAUSSDB100-V300R001C00-ZSQL-EULER20SP8-64bit.tar.gz

Next, make changes to the user group and execute the su command to switch to the user under which the zsql client is running.

su - omm

Unpack the installation package accordingly.

cd /home/omm tar -zxvf GAUSSDB100-V300R001C00-ZSQL-EULER20SP8-64bit.tar.gz

If the database user's password contains the special character $, you must escape it with the escape character \ when connecting to the database via zsql, otherwise the login will fail.

Go to the directory where the host was unziped.

cd GAUSSDB100-V300R001C00-ZSQL-EULER20SP8-64bit

Run the install_zsql.py script to install the zsql client.

python install_zsql.py -U omm:dbgrp -R /home/omm/app

Here -U is the user running the zsql client, e.g. omm. -R is the directory where the zsql client is installed.

After completing the installation of the zsql client, use zsql to connect.

Log in as the database administrator with the following code format.

zsql { CONNECT | CONN } / AS SYSDBA [ip:port] [-D /home/gaussdba/data1] [-q] [-s "silent_file"] [-w connect_timeout]

The sample code is as follows.

[gaussdba@plat1~]$ zsql / AS sysdba -q Connected

CONNECT|CONN: Connects to the database; where [ip:port] is optional, if not specified, the local host is connected by default.

When the database system administrator creates multiple database instances , the −D parameter is required to specify the database directory in order to connect to the specified database. The −D parameter is only required when the database system administrator creates multiple database instances ; if it is not specified, the host does not know which database to connect to. Usually users create only one database instance so do not need to specify it. Therefore, the −D parameters are only for HCIA users to understand, no need to learn it in depth.

• (1) −q: This parameter is used to cancel SSL login authentication view, which can be used together with the -w parameter.

• (2) −s: This parameter is used to set the prompt-free mode to execute SQL statement.

• (3) −w: This parameter indicates the waiting timeout time when the client connects to the database, currently 10s by default; can be used with the −q parameter. The value meanings of waiting timeout are as follows.

  • −1: means wait for the server response, no timeout.

  • 0: means do not wait for the timeout, and return the result directly.

  • n: means wait for n seconds.

After using the -w parameter, when zsql starts to connect to the database, the waiting timeout is set to the specified value. After starting, the waiting response timeout for the currently established connection, the waiting response timeout for the new connection re-established and the query timeout are all specified values; the setting expires after exiting the zsql process.

When logging in as a normal database user, the following three types of logins are available.

• (1) Interactive Login Method 1.

  zsql user@ip:port [-D /home/gaussdba/data1] [-q] [-s "silent_file"] [-w connect_timeout] Please enter password:

• (2) Interactive Login Method 2.

  zsql conn user/user_password@ip:port [-D /home/gaussdba/data1] [-q] [-s "silent_file"] [-w connect_timeout]

• (3) Non-Interactive Login Method.

  zsql user/user_password@ip:port [-D /home/gaussdba/data1] [-q] [-s "silent_file"] [-w connect_timeout]

  user is the database user name and user_password is the database user password. ip:port is the IP address and port number of the host where the database is located, which is 1888 by default.

Interactive Login Method 1 has no conn, where you need to connect and then enter the password. Interactive Login Method 2 has conn, where you can enter the password as connect. Non-Interactive Login Method has no conn, where you can enter the password as connect in a different manner. The most commonly used is the Non-Interactive Login Method, while for the interactive login methods, you just need to know about them, because they all have the same result.

Example: User gaussdba logs in locally to the database.

[gaussdba@plat1~]$ zsql SQL> CONN gaussdba/Changeme_123@127.0.0.1:1611 connected. //启动zsql进程时设置等待响应超时时间 [gaussdba@plat1~]$ zsql gaussdba/Changeme_123@127.0.0.1:1611 -w 20 connected. //创建新用户jim，并赋予新用户CREATE SESSION权限 SQL> DROP USER IF EXISTS jim; CREATE USER jim IDENTIFIED BY database_123; GRANT CREATE SESSION TO jim; //切换用户，再次建立的新连接的等待响应超时时间也是20s CONN jim/Changeme_123@127.0.0.1:1611 connected. EXIT

When starting the zsql process, set the response timeout to 20s. After starting, the response timeout for the current connection is 20s. After exiting the zsql process, the setting expires and the waiting response timeout for new connections remains at the default value of 20s.

When connecting to zsql, you can set the parameters to meet your specific functional requirements. If you set the -s parameter to execute SQL statements in promptless mode, the results will be output to the specified file instead of being displayed back on the current screen. This parameter should be placed at the end of the command.

Example: User hr connects to the database in the silent mode, specifying the output log name as silent.log.

[gaussdba@plat1~]$ zsql hr@127.0.0.1:1611 -s silent.log //创建表training CREATE TABLE training(staff_id INT NOT NULL,course_name CHAR(50),course_start_ date DATETIME,course_end_date DATETIME,exam_date DATETIME,score INT); INSERT INTO training(staff_id,course_name,course_start_date,course_end_date, exam_date,score) values(10,'SQL majorization','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90); //退出数据库系统 EXIT //查看日志silent.log cat silent.log Succeed.

The -c parameter refers to the execution of a single SQL statement at startup, which needs to be placed at the end of the command.

zsql user/password@ip:port -c "SQL_Statement"

Multiple normal SQL statements can be entered in the -c parameter, but the statements need to be separated by a semicolon (;). When entering procedure statements in the -c parameter, only a single entry is supported, and the procedure needs to be ended with a slash /.

The sample code is as follows.

[gaussdba@plat1~]$ zsql gaussdba/Changeme_123@127.0.0.1:1611 -c "SELECT ABS(-10) FROM dual;" connected. SQL> ABS(-10) ---------------------------------------- 10 1 rows fetched.

Objects with $ in their names need to add the escape character \. The maximum length of a single executable SQL statement should be no longer than 1MB.

-f refers to the execution of SQL scripts, which cannot be used with the -c or -s parameters. The setting of the -f parameter is the same as that of the -c and -s parameters, which are placed at the end of the command.

zsql user@ip:port [-a] -f sql_script_file

Or as follows.

zsql user@ip:port [-a] -f "sql_script_file"

The -a parameter is used to output the executed SQL statement, which can be used together with the -f parameter, and must be in front of the -f parameter. This means output and execute the SQL statement in the SQL script. If the -a parameter is not set then output the execution result of the statement in the SQL script directly, and no SQL script will be output.

[gaussdba@plat1~]$ zsql gaussdba/Changeme_123@127.0.0.1:1611 –a connected. SQL> SELECT ABS(-10); SELECT ABS(-10); /* 打印SQL脚本*/ ABS(-10) ---------------------------------------- 10 1 rows fetched.

Execute the file test.sql.

SELECT ABS(-10) FROM dual; SELECT * FROM dual; SELECT 123; COMMIT;

Execute the -f “test.sql” command

[gaussdba@plat1~]$ zsql gaussdba/Changeme_123@127.0.0.1:1611 –f “test.sql”

Execute the -a -f “test.sql” command.

[gaussdba@plat1~]$ zsql gaussdba/Changeme_123@127.0.0.1:1611 –a –f "test.sql"

The result of the -f “test.sql” command is as follows.

[gaussdba@plat1~]$ zsql gaussdba/Changeme_123@127.0.0.1:1611 –f "test.sql" connected. SQL> ABS(-10) ---------------------------------------- 10 1 rows fetched. SQL> DUMMY ----- X 1 rows fetched. SQL> 123 ------------ 123 1 rows fetched. SQL> Succeed.

The result of the -a-f “test.sql” command is as follows.

[gaussdba@plat1~]$ zsql gaussdba/Changeme_123@127.0.0.1:1611 –a –f "test.sql" connected. SQL> SELECT ABS(-10) FROM dual; ABS(-10) ---------------------------------------- 10 1 rows fetched. SQL> SELECT * FROM dual; DUMMY ----- X 1 rows fetched. SQL> SELECT 123; 123 ------------ 123 1 rows fetched. SQL> COMMIT; Succeed.

The format of the statement to view the database object definition information is as follows.

DESCRIBE [-o | -O] object

Or as follows.

DESC [-o | -O] object

-o or -O indicates the object, which is optional.

The SQL statement of query: DESC -q SELECT expression (just know it).

Displays column description information when querying with the SELECT statement, including name, nullable, type, and size(char or byte).

The DESC column size shows the derived value (maximum derived value) at the time of SQL parsing, and the execution returns column data values that do not exceed that size.

expression is a query statement.

Query the definition information of the table privilege.

SQL> DROP TABLE IF EXISTS privilege; SQL> CREATE TABLE privilege(staff_id INT PRIMARY KEY, privilege_name VARCHAR(64) NOT NULL,privilege_description VARCHAR(64), privilege_approver VARCHAR(10)); SQL> DESC privilege; Name Null? Type ----------------------------------- - ------- ------------------------------- STAFF_ID NOT NULL BINARY_INTEGER PRIVILEGE_NAME NOT NULL VARCHAR(64 BYTE) PRIVILEGE_DESCRIPTION VARCHAR(64 BYTE) PRIVILEGE_APPROVER VARCHAR(10 BYTE)

Execute the SPOOL command to output the execution results to an operating system file.

Specify the output file, either in a relative path or an absolute path.

SPOOL file_path

Save the execution result and close the current output file stream.

SPOOL off

Execute the SPOOL command.

SQL> SPOOL ./spool.txt SQL> CREATE TABLE COUNTRY(Code INT,Name VARCHAR(20),Population INT); SQL> SELECT Code, Name, Population FROM COUNTRY WHERE Population > 100000; SQL> SELECT 'This SQL will be output into ./spool.txt' FROM SYS_DUMMY; SQL> SPOOL OFF; SQL> SELECT 'This SQL will not be output into ./spool.txt' FROM SYS_DUMMY;

When the SPOOL file is specified, zsql results are output to a file. The contents of the file are approximately the same as those displayed on the zsql command line, and the output is closed only after SPOOL OFF is specified.

If the file specified by the SPOOL command does not exist, zsql will create a file. If the specified file already exists, zsql appends the execution result to the original result.

Exit zsql and enter cat spool.txt to view the contents of the spool.txt file, as follows.

SQL> CREATE TABLE COUNTRY(Code int,Name varchar(20),Population int); Succeed. SQL> SELECT Code, Name, Population 2 FROM COUNTRY 3 WHERE Population > 100000; CODE NAME POPULATION ------------ -------------------- ------------ 0 rows fetched. SQL> SELECT 'This SQL will be output into ./spool.txt' FROM SYS_DUMMY; 'THIS SQL WILL BE OUTPUT INTO ./SPOOL.TXT' ------------------------------------------ This SQL will be output into ./spool.txt 1 rows fetched. SQL> SPOOL OFF;

Note that spool.txt does not have the SELECT 'This SQL will not be output into . /spool.txt' FROM SYS_DUMMY; statement, because the SPOOL OFF statement has been executed before the execution of this statement.

Logical import IMP and logical export EXP.

{EXP | EXPORT}[ keyword =param [ , … ] ] [ … ]; {IMP | IMPORT} [ keyword =param [ , … ] ] [ … ];

• (1) Logical import and logical export do not support the export of SYS user data.

• (2) During the logical import and logical export of data, you need to have the corresponding operation permission for the object to be exported.

• (3) If execute FILETYPE=BIN during logical import and logical export, three types of files are exported: metadata files (user-specified files), data files (.D files), and LOB files (.L files).

• (4) If there is a file with the same name existing in the directory during logical import and logical export, the file will be overwritten directly without any prompt.

• (5) When logically exporting data, a metadata file and a subdirectory named data will be generated under the specified export file path. If no export file path is specified, a metadata file and a subdirectory named data will be generated under the current path by default. When executing FILETYPE=BIN, the generated subfiles (data file, LOB file) will be placed under the secondary directory data; if the specified metadata file and the generated subfiles already exist, an error will be reported.

Generate the analysis report WSR.

WSR (Workload Statistics Report) is used to generate the performance analysis report. By default, only SYS users have permission to perform the related operations. If an ordinary user needs to use it, he/she needs the SYS user permission—grant statistics to user, which means that the statistics role is granted to the ordinary user. After authorization , the ordinary user has the permissions to create snapshots, delete snapshots, view snapshots, and generate WSR reports, but does not have the permission to change WSR parameters. When an ordinary user performs an operation, he/she needs to carry the SYS name to execute the corresponding stored procedure, such as CALL SYS.WSR$CREATE_SNAPSHOT.

Other functions include SHOW (query parameter information), SET (set parameters), DUMP (export data), LOAD (import data), COL (set column width), WHENEVER (set whether to continue or exit the connection operation when the script runs abnormally), etc.

6.3.2 gsql

To configure the database server using gsql, and log in to any node in the GaussDB (DWS ) cluster as the omm user, execute the related command source ${BIGDATA_HOME}/mppdb/.mppdbgs_profile to start the environment variables.

Execute the following command to add the IP address or host name (separated by a comma) of the external service, where NodeName is the current node name and 10.11.12.13 is the IP address of the network card of the server where CN is located.

gs_guc reload -Z coordinator -N NodeName -I all -c "listen_addresses='localhost, 192.168.0.100,10.11.12.13'"

listen_addresses can also be configured as *. This configuration will monitor all NICs, but there are security risks, so it is not recommended for users. It is recommended that users configure IP address or host name to open the monitoring as needed.

Add the client IP address authentication information (please replace the client_ip/mask below with the real client IP address).

gs_guc SET -Z coordinator -N all -I all -h "host all client_ip/mask sha256"

Unzip the GaussDB-Kernel-VXXXRXXXCXX-XXXX-64bit-gsql.tar.gz archive, and you will get the following files.

bin: The location where the gsql executable is stored.

gsql_env.sh: environment variable file.

lib: the dynamic database that gsql depends on.

Load the environment variable file source gsql_env.sh that you just extracted, and you can use gsql normally.

gsql –d postgres –h 10.11.12.13 –U username –W password –p 25308

The -d parameter specifies the database name.

The -h parameter specifies the database CN address.

The -U parameter specifies the database user name.

The -W parameter specifies the password of the database user

The -p parameter specifies the port of the database CN.

Download gsql: Visit the https://console.huaweicloud.com/dws to log in to the GaussDB (for DWS ) administration console; in the left navigation bar, click the “Connection Management” button; in the “gsql Command Line Client” drop-down box, select the corresponding version of GaussDB (DWS ) client; click the “Download” button to download the gsql matching the existing cluster version.

Configure the server: Log in to the ECS (cloud server) using PuTTY. In the PuTTY login page, Host Name is the ECS public IP address, Port is 22, Connection type is SSH. Click the “Open” button and click the “YES” button in the pop-up box, where login as is root, and password is the root user password (the password is not explicitly displayed, so make sure it is entered correctly and press Enter). Execute the command cd <client path> (please replace <client path> with the actual client path). The execution command punzip dws_client_1.5.x_redhat_x64.zip (dws_client_redhat_ x64.tar.gz is the client toolkit name corresponding to “RedHat x64”, which should be replaced with the actual downloaded package name). Execute the command source gsql_env.sh; if the command line information shown in Fig. 6.47 is prompted, it means that the client has been successfully configured.

Fig. 6.47

Configure the server

Connect to database: Use gsql client to connect to the database in GaussDB (DWS ) cluster, where the execution statement format is gsql -d <database name> -h <cluster address> -U <database user> -p <database port> -r. The database name is the name of the database to be connected. When connecting to the cluster for the first time using a client, please specify the default database of the cluster as “postgres”. If you connect via public address, please specify the cluster address as the public access address or public access domain name; if you connect via intranet address, please specify the cluster address as the intranet access address or intranet access domain name. Database user is the user name of the cluster database. When you connect to the cluster for the first time using the client, please specify the default administrator user set when creating the cluster, such as “dbadmin”. The database port is the “database port” set when creating the cluster, as shown in Fig. 6.48.

Fig. 6.48

Connect to the database

Usage: gsql can directly send query statements to the database for execution, and return the execution results.

postgres=# SELECT * FROM dual; dummy ------- X (1 row)

The gsql tool also provides some more useful meta-commands for quick interaction with the database. For example, to quickly view the object definition, the code is as follows.

postgres=# \d dual View "pg_catalog.dual" Column | Type | Modifiers --------+------+----------- dummy | text | Column:字段名; Type:字段类型; Modifiers:约束信息;

For more commands, you can use \? to view the usage instructions.

6.3.3 Data Studio

Data Studio is a graphical user interface (GUI) tool that can be used to connect to GaussDB databases, execute SQL statements, manage stored procedures, and manage database objects. Data Studio currently supports most of the basic features of GaussDB , providing database developers with a user-friendly graphical interface that simplifies database development and application development tasks, and can significantly improve the efficiency of building programs.

Let's download, install and run Data Studio.

• (1) Download and install Data Studio under Windows.

  Download: Login to Huawei support website, go to “Technical Support > Cloud Computing > FusionInsight >FusionInsight Tool” page, and select the corresponding version of Data Studio to download.

  Installation: After downloading, unzip the Data Studio installation package.

• (2) Set the Data Studio profile (optional).

  You can modify the configuration file “Data Studio.ini” to personalize the Data Studio operating parameters. The modified parameters will take effect after restarting the Data Studio.

  The Data Studio user's manual teaches how to use each parameter.

• (3) Run Data Studio.

  Double-click the “Data Studio.exe” file to run it (Note: Java 1.8.0_141 or higher is required).

  When using Data Studio to connect to GaussDB (for MySQL) database, select the type of database to connect to. Enter a custom name, the IP address of the database, the port of the database, the user name, and the password, as shown in Fig. 6.49.

The connection to GaussDB (DWS ) is similar, but you must select the database type as GaussDB (DWS ).

Fig. 6.49

Connect to GaussDB (for MySQL) database using Data Studio

The main interface of Data Studio is divided into five sections, as shown in Fig. 6.50.

Fig. 6.50

The main interface of Data Studio

Area 1 is the top menu bar, Area 2 is the object browser, Area 3 is the editing window for SQL, Area 4 is the query window for SQL statement execution results, and Area 5 is the syntax assistant area for SQL.

The object browser takes database connection as the root node and uses a tree-like hierarchy to display database objects; it provides entrances to various object management operations in the form of right-click menus, such as creating database, disconnecting, creating objects, editing table data, viewing object property information, executing stored procedures, etc.

The SQL editing window provides a window for users to edit, format and execute various SQL statements. After executing SQL statements, users can query the results returned by the statements in the query window, and also sort, filter, copy, and export the results. The syntax assistant automatically associates and provides complementary suggestions based on user input during SQL edition.

The query window is used to display the results returned by the query statement, and users can sort, dynamically filter, copy, export, and edit the results.

The SQL syntax assistant dynamically matches and displays the corresponding SQL statements based on the user input in the SQL edition window.

Stored procedure management includes viewing, modifying and compiling stored procedure code, executing or debugging stored procedures, and providing templates for creating stored procedures for GaussDB syntax.

Preferences configuration allows users to personalize some of the features of the data source according to their own habits, such as the time interval for automatic saving, the number of records loaded per query result, SQL statement highlighting rules, custom shortcuts, etc.

6.3.4 MySQL Workbench

MySQL Workbench is a GUI tool for designing and creating new database icons, building database documentation, and performing complex MySQL migrations. As a next-generation tool for visual database design and management, it is available in both open source and commercial versions. This software supports Windows and Linux operating systems. MySQL Workbench provides database developers with a user-friendly graphical interface that simplifies database development and application development tasks, and can significantly improve the efficiency of building programs.

It comes with the MySQL Workbench client for Windows-based platforms.

MySQL Workbench download: Log in to the MySQL official website and select MySQL MySQL Workbench from the DOWNLOADS option at the bottom of the page; unzip the downloaded client installation package (32-bit or 64-bit) to the path you need to install (e.g. D:\MySQL Workbench); open the installation directory and double-click MySQL Workbench.exe (or click the right mouse button and run as administrator).

Connection to the database using MySQL Workbench: Enter the connection information in MySQL Workbench.

The main interface of MySQL Workbench includes: navigation bar, SQL edition window, query result window, and basic database information, as shown in Fig. 6.51.

Fig. 6.51

The main interface of MySQL Workbench

The basic functions of MySQL Workbench mainly comes in the following aspects. Navigation bar: Shows the management functions of the database, such as status check, connection management, user management, data import and export; provides the entrance of various object management operations, such as starting and stopping instances, querying logs, viewing operation files; shows the performance of the database, where reports can be set or generated. SQL edition window: Edits, formats and executes various SQL statements; in the process of editing SQL statements, grammar assistant will automatically associate according to user input and provide suggestions for completion. Query result window: Displays the results returned by the query statements, where users can sort, dynamically filter, copy, export, and edit the results. Database basic situation: Shows the existing database and the basic situation of objects at all levels under the database. Database backup: According to customer's demand, provides enterprise-level online backup and backup recovery functions. Audit check: The search field provides narrow-displayed operation events, including exhibition activity acquisition of inclusion type and display events of query type, and all activities are displayed by default. Custom filters are also available.

6.4 Summary

This chapter introduces GaussDB database related tools, including JDBC, ODBC, gsql. The drivers in GaussDB database include JDBC, ODBC, etc. GaussDB database provides some related connection tools, including zsql, gsql, Data Studio.

6.5 Exercises

• 1. [Multiple Choice] Which of the following functions can be implemented by the JDBC common interface? ( )

  • A. Executes SQL statements

  • B. Executes stored procedures

  • C. Unloads database

  • D. Deletes database

• 2. [Single Choice] Through ODBC interaction, while avoiding the direct operation of the database system by the application program, what characteristics of the application program are enhanced? ( )

  • A. Portability, compatibility and maintainability

  • B. Portability, compatibility and scalability

  • C. Maintainability, scalability, and portability

  • D. Compatibility, maintainability and scalability

• 3. [Multiple Choice] The key features of DDM include ( ).

  • A. Smooth scalability

  • B. Read/write separation

  • C. Intelligent management

  • D. Data sharding

• 4. [True or False] When DDM database middleware is purchased, the virtual private cloud can be different from the database virtual private cloud it uses. ( )

  • A. True

  • B. False

• 5. [Single Choice] Which of the following is not a capability of DRS? ( )

  • A. Online migration

  • B. Data synchronization

  • C. Multi-live disaster recovery

  • D. Smooth scalability

• 6. [True or False] The migration function provided by DRS does not support the service interruption function. ( )

  • A. True

  • B. False

• 7. [Multiple Choice] The gsql connection command contains the ( ) parameters.

  • A. Database name

  • B. Cluster address

  • C. Database user

  • D. Database port

• 8. [True or False] gsql is an interactive database connection tool provided by GaussDB (DWS ) that runs at the command line. ( )

  • A. True

  • B. False

• 9. [Multiple Choice] The basic features of MySQL Workbench include ( ).

  • A. Navigation bar

  • B. Database situation display

  • C. Data backup

  • D. Audit check

• 10. [Multiple Choice] Which of the following functions does Data Studio support? ( )

  • A. Browses database objects

  • B. Creates and manages database objects

  • C. Manages stored procedures

  • D. Edits SQL statements

• 11. [Short Answer Question] Briefly describe the process of developing applications with ODBC.

• 12. [Short Answer Question] Briefly describe the process of DDM data sharding.

• 13. [Short Answer Question] Briefly describe the process of DRS data migration.

• 14. [Short Answer Question] Briefly describe the process of connecting to a database using gsql and explain what are the important parameters involved.