DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

GBase 8a Operations Cheat Sheet: Essential Commands for DBAs

#cli #database #sql #tutorial

Managing a GBase 8a MPP cluster requires fluency in a core set of operational commands. This cheat sheet distills the most frequently used ones into six categories: cluster mode, user privileges, DDL, system metadata, data import/export, and space reclamation — all tailored for the China‑domestically developed database from GBASE.

Cluster Mode

  • Set to read‑only gcadmin switchmode readonly
  • Back to normal gcadmin switchmode normal

User and Privilege Management

Create a User 

CREATE USER username IDENTIFIED BY 'password';
Enter fullscreen mode Exit fullscreen mode

Grant Privileges 

-- Database level
GRANT ALL ON db_name.* TO username;

-- Global FILE privilege for data export
GRANT FILE ON *.* TO username;
Enter fullscreen mode Exit fullscreen mode

Check Privileges 

SHOW GRANTS FOR username;
SHOW GRANTS FOR CURRENT_USER();
Enter fullscreen mode Exit fullscreen mode

DDL: Creating Tables from Existing Ones 

-- Hash‑distributed table with distribution key 'no'
CREATE TABLE hashinfo DISTRIBUTED BY ('no') AS SELECT * FROM userinfo;

-- Replicated table
CREATE TABLE replinfo REPLICATED AS SELECT * FROM userinfo;

-- Clone schema only
CREATE TABLE infocopy LIKE userinfo;
Enter fullscreen mode Exit fullscreen mode

System Information Queries

Parameters and Objects 

-- Compression settings
SHOW VARIABLES LIKE '%compress%';

-- All tables in a schema
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'test';

-- All views
SELECT * FROM information_schema.tables WHERE TABLE_TYPE LIKE 'VIEW';

-- Load progress
SELECT TB_NAME, IP, ELAPSED_TIME, AVG_SPEED, PROGRESS, SKIPPED_RECORDS
FROM information_schema.load_status ORDER BY tb_name;
Enter fullscreen mode Exit fullscreen mode

Table Size and Structure 

-- Disk usage
SELECT table_schema, table_name, table_data_size, table_storage_size
FROM information_schema.cluster_tables
WHERE table_schema = 'test' AND table_name = 'test';

-- Column metadata
SELECT column_name, table_name, column_type FROM information_schema.columns;

-- Distribution key (identify replicated tables or those missing a hash key)
SELECT dbName, tbName, isReplicate, hash_column
FROM gbase.table_distribution
WHERE dbName = 'usr_sod' AND hash_column IS NULL;
Enter fullscreen mode Exit fullscreen mode

Data Import and Export

Loading Text Files 

-- Delimited file with custom date format and encoding
LOAD DATA INFILE 'ftp://user:password@10.33.37.130//home/data/bms.dat'
INTO TABLE test.bms
DATA_FORMAT 3
CHARACTER SET gbk
NULL_VALUE 'nullnull'
FIELDS TERMINATED BY '|'
DATETIME FORMAT '%Y-%m-%d日 %H:%i:%s'
LINES TERMINATED BY '/n'
AUTOFILL TRACE 1;

-- Fixed‑width file with filler columns
LOAD DATA INFILE 'http://127.0.0.1/data/b.tbl'
INTO TABLE test.b
DATA_FORMAT 4
FIELDS DEFINER '4,6,10,12,1,19'
TABLE_FIELDS 'no,pwd,birth date "%Y年%m月%d日",filler,sex,loginTime date "%Y-%m-%d %T"';
Enter fullscreen mode Exit fullscreen mode

HDFS Integration 

-- Set HA NameNodes before loading
SET gbase_hdfs_namenodes = "192.168.10.1,192.168.10.2";

-- Load from HDFS
LOAD DATA INFILE 'hdp://root@192.168.10.1:50070/why/test6/test.txt'
INTO TABLE test.test1;

-- Export to HDFS
SELECT * FROM test.test1
INTO OUTFILE 'hdp://root@10.10.3.117:50070/why/test1'
OUTFILEMODE BY HDFS
FIELDS TERMINATED BY '|' ENCLOSED BY '"';
Enter fullscreen mode Exit fullscreen mode

Export to Local Disk 

SELECT * FROM nation
INTO OUTFILE '/home/gbase/new.txt'
FIELDS TERMINATED BY '|' ENCLOSED BY '"'
LINES TERMINATED BY '@@@@@';
Enter fullscreen mode Exit fullscreen mode

Space Reclamation

After heavy DML, shrink tables manually to release disk space:

ALTER TABLE table_name SHRINK SPACE FULL;
Enter fullscreen mode Exit fullscreen mode

These commands cover the most common operational tasks for a gbase database. Always verify the cluster mode and your privileges before running them against a production environment.

Top comments (0)