Overview of GBase 8c Syntax

GBase 8c is a high-performance, highly available, and secure database management system widely used for big data processing and analysis. It supports standard SQL, enabling users to perform data operations and queries easily. This article introduces the SQL syntax of GBase 8c, helping users better understand and utilize the system.

1. Basic SQL Syntax

1.1 Data Definition Language (DDL)

DDL is used to define database structures, such as creating or modifying tables. In addition to supporting standard SQL syntax, GBase 8c offers some unique features.

Creating a Distributed Table:

CREATE TABLE employees (
   emp_id INT PRIMARY KEY,
   emp_name VARCHAR(100),
   department VARCHAR(100),
   salary DECIMAL(10, 2)
)
DISTRIBUTE BY HASH(emp_id);

This example creates a table named employees with four columns: emp_id as the primary key, emp_name for storing employee names, department for department names, and salary for salary data. The table is distributed by the emp_id field.

Creating a Replicated Table:

CREATE TABLE employees (
   emp_id INT PRIMARY KEY,
   emp_name VARCHAR(100),
   department VARCHAR(100),
   salary DECIMAL(10, 2)
)
DISTRIBUTE BY REPLICATION;

This example creates a replicated table named employees with no data distribution.

1.2 Creating Partitioned Tables

Creating a Range Partitioned Table:

CREATE TABLE employees_table
(
   employee_id              INTEGER          NOT NULL,
   employee_name            CHAR(20)         NOT NULL,
   onboarding_date          DATE             NOT NULL,
   position                 CHAR(20)
)
PARTITION BY RANGE(onboarding_date)
(
   PARTITION founders VALUES LESS THAN ('2000-01-01 00:00:00'),
   PARTITION senate VALUES LESS THAN ('2010-01-01 00:00:00'),
   PARTITION seniors VALUES LESS THAN ('2020-01-01 00:00:00'),
   PARTITION newcomer VALUES LESS THAN (MAXVALUE)
);

Creating a Hash Partitioned Table:

CREATE TABLE test_hash (col1 INT, col2 INT)
PARTITION BY HASH(col1)
(
   PARTITION p1,
   PARTITION p2
);

Creating a List Partitioned Table:

CREATE TABLE test_list (col1 INT, col2 INT)
PARTITION BY LIST(col1)
(
   PARTITION p1 VALUES (2000),
   PARTITION p2 VALUES (3000),
   PARTITION p3 VALUES (4000),
   PARTITION p4 VALUES (5000)
);

1.3 Views

Regular View:

In SQL, a view is a virtual table derived from other tables. A view can include columns from one or more tables and may contain complex SQL statements. Example:

CREATE VIEW view_name AS 
SELECT column1, column2, ...
FROM table_name 
WHERE condition;

CREATE VIEW max_salaries_per_department AS 
SELECT department, MAX(salary) AS max_salary 
FROM employees 
GROUP BY department;

Materialized View:

A materialized view is a physical copy of query results stored on disk. Example:

CREATE MATERIALIZED VIEW sales_summary AS 
SELECT product_id, SUM(sale_amount) AS total_sales 
FROM sales 
GROUP BY product_id;

This example creates a materialized view sales_summary to quickly query the total sales for each product.

To refresh the data in the materialized view, you need to manually execute the refresh command:

REFRESH MATERIALIZED VIEW view_name;

1.4 Sequences

In GBase 8c, a sequence is a database object used to generate a sequence of numbers, which is useful for creating unique identifiers like order numbers or user IDs.

Basic Syntax for Creating a Sequence:

CREATE SEQUENCE sequence_name
   START WITH start_value
   INCREMENT BY increment_value
   MINVALUE min_value
   MAXVALUE max_value
   CYCLE | NO CYCLE
   CACHE cache_value;

Example:

CREATE SEQUENCE order_id_seq
   START WITH 1000
   INCREMENT BY 1
   MINVALUE 1000
   MAXVALUE 9999
   NO CYCLE
   CACHE 10;

2. Function Syntax Support

2.1 Date and Time Functions

Refer to the official GBase 8c documentation for the supported date and time functions.

Time and Date Functions	Description	Example
age(timestamp, timestamp)	Subtracts two timestamps and returns the result in years, months, and days. If the result is negative, the function returns a negative value. Both parameters must have the same type and either both include or exclude timezones.	age(timestamp '2001-04-10', timestamp '1957-06-13') -- Result: 43 years 9 mons 27 days
clock_timestamp()	The current timestamp from the real-time clock.	clock_timestamp(); -- Result: 2022-05-17 16:34:14.629575+08
current_date	The current date.	current_date; -- Result: 2022-05-17
current_timestamp	The current date and time.	current_timestamp; -- Result: 2022-05-17 16:35:08.018834+08
date_trunc(text, timestamp)	Truncates a timestamp to the precision specified by the text argument.	date_trunc('hour', timestamp '2001-02-16 20:38:40'); -- Result: 2001-02-16 20:00:00
trunc(timestamp)	Truncates a timestamp, defaulting to days.	trunc(timestamp '2001-02-16 20:38:40'); -- Result: 2001-02-16 00:00:00
now()	The current date and time.	now(); -- Result: 2022-05-17 16:47:22.123899+08
add_months(d, n)	Adds n months to the date d.	add_months(to_date('2017-5-29', 'yyyy-mm-dd'), 11); -- Result: 2018-04-29 00:00:00
last_day(d)	Returns the last day of the month for the given date d.	last_day(to_date('2017-01-01', 'YYYY-MM-DD')) AS cal_result; -- Result: 2017-01-31 00:00:00

2.2 Type Conversion Functions

CAST(x AS y): Converts x to the specified type y. Example:

SELECT CAST('22-oct-1997' AS TIMESTAMP);

Simpler type conversion using :::

SELECT '5'::INT, '2014-07-11'::DATE;

TO_CHAR(int, text): Converts an integer to a string in the specified format. Example:

SELECT TO_CHAR(123, '999');

TO_DATE(text): Converts a string to a date in the specified format. Example:

SELECT TO_DATE('05 dec 2000', 'DD MON YYYY');

TO_NUMBER(expr, [fmt]): Converts expr to a number in the specified format. Example:

SELECT TO_NUMBER('12,454.8-', '99G999D9S');
-- Result: -12454.8

TO_TIMESTAMP(text, text): Converts a string to a timestamp in the specified format. Example:

SELECT TO_TIMESTAMP('05 dec 2000', 'DD MON YYYY');
-- Result: 2000-12-05 00:00:00

TO_BIGINT(varchar): Converts a string to a BIGINT.

2.3 System Information Functions

• CURRENT_CATALOG: Returns the name of the current database.
• CURRENT_DATABASE(): Returns the name of the current database.
• CURRENT_SCHEMA[()]: Returns the name of the current schema.
• CURRENT_USER: Returns the current user.
• SESSION_USER: Returns the session user.

3. Conclusion

GBase 8c's SQL syntax not only supports standard SQL operations but also provides unique features like distributed tables and materialized views. These features make GBase 8c highly effective in big data processing. With the introduction and examples provided in this article, users should have a better understanding of how to use GBase 8c's SQL syntax for data operations and analysis. The SQL syntax and examples in this article serve as a guide, and actual usage may vary depending on the specific version and configuration of GBase 8c.