Get started with OceanBase AP|V4.3.1| docs|Distributed Database

Get started with OceanBase AP

Last Updated：2024-07-11 11:22:29 Updated

This topic describes how to get started with OceanBase AP in OceanBase Database Community Edition. If you have only one server, you can quickly build a demo environment to deploy OceanBase Database. The demo database provides basic features for you to quickly learn about OceanBase Database, but does not support distributed capabilities or high availability. Therefore, long-term use is not recommended.

Notice

This topic aims to help you get started with OceanBase Database. If you want to further experience analytical processing (AP) performance, we recommend that you use the recommended parameter configurations. For more information, see Recommended parameter configurations for AP scenarios.

Step 1: Build an experience environment

Download and install the all-in-one package.

Download the all-in-one package for the Community Edition from OceanBase Download Center and upload it to any directory on your server.

In the directory where the package is located, run the following commands to decompress and install the package:

[admin@test001 ~]$ tar -xzf oceanbase-all-in-one-*.tar.gz
[admin@test001 ~]$ cd oceanbase-all-in-one/bin/
[admin@test001 bin]$ ./install.sh
[admin@test001 bin]$ source ~/.oceanbase-all-in-one/bin/env.sh

Run the following command to deploy OceanBase Database:
```
[admin@test001 ~]$ obd demo
```
By default, the obd demo command deploys OceanBase Database and its components with minimum specifications in the home directory on your server and then starts them. The components include OceanBase Database Proxy (ODP), OBAgent, Grafana, and Prometheus. The name of the deployed cluster is fixed to demo.

Note

If you install Grafana or Prometheus, its access address is returned in the command output. On Alibaba Cloud or other cloud environments, the program may fail to obtain a public IP address but return an intranet IP address. You must use a correct public IP address.
Run the connection command in the output to connect to the database.

After the obd demo command succeeds, you will find the command for connecting to OceanBase Database through OBClient. The examples are as follows:
- Directly connect to the database through port 2881
```
[admin@test001 ~]$ obclient -h127.0.0.1 -P2881 -uroot@sys -Doceanbase -A
```
- Connect to the database in proxy mode through ODP
```
[admin@test001 ~]$ obclient -h127.0.0.1 -P2883 -uroot@sys -Doceanbase -A
```
(Optional) Configure the password.

After you deploy OceanBase Database by using the obd demo command, you can follow the steps below to configure a password for the demo cluster.
1. Modify the configuration file.
```
obd cluster edit-config demo
```
  After you run the preceding command to open the configuration file, add root_password: xxxx in the oceanbase-ce (Community Edition) or oceanbase (Enterprise Edition) section in the configuration file. Then save the file and exit. Here is an example:
```
oceanbase-ce:
  servers:
    - 127.0.0.1
  global:
    home_path: /home/admin/oceanbase-ce
    ... # Some parameters are omitted here.
    log_disk_size: 13G
    root_password: ******
```
2. Restart the cluster.
  
  After you modify and save the configuration file, OBD will output the restart command. You can directly copy and run it. Here is an example:
```
[admin@test001 ~]$ obd cluster edit-config demo
Search param plugin and load ok
Search param plugin and load ok
Parameter check ok
Save deploy "demo" configuration
Use `obd cluster reload demo` to make changes take effect.
Trace ID: 29dd12fa-3d73-11ee-91bc-00163e01cd7a
If you want to view detailed obd logs, please run: obd display-trace 29dd12fa-3d73-11ee-91bc-00163e01cd7a
```
  The output shows that you must run the obd cluster reload demo command to restart the demo cluster after you modify the password for the root@sys user in the configuration file.

Step 2: Create a table

Create a database.

Use the CREATE DATABASE statement to create a database.

For example, create a database named quickstart, set the character set to utf8mb4, and specify the read and write attributes.
```
obclient> CREATE DATABASE quickstart DEFAULT CHARACTER SET utf8mb4 READ WRITE;
Query OK, 1 row affected
```

Create a table.

Use the CREATE TABLE statement to create a table in the database.

For example, create a table named test in the quickstart database.

obclient> USE quickstart;
Database changed

obclient> CREATE TABLE test (place VARCHAR(500), latitude VARCHAR(50),longitude  VARCHAR(50), country  VARCHAR(1000), continent  VARCHAR(100), magnitude DECIMAL(3, 1));
Query OK, 0 rows affected

Step 3: Import data

The sample dataset used in this topic is sourced from the earthquake_dataset.csv file in Kaggle.

Download the earthquake_dataset.csv file from Kaggle.
Copy the earthquake_dataset.csv file to the server where the OBServer node resides.
```
scp earthquake_dataset.csv admin@10.10.10.1:/home/admin/test_data
```
Log on to the server where the OBServer node to connect to resides.

Here is an example:
```
ssh admin@10.10.10.1
```
Set the path where the file to be imported is located.

Notice

For security reasons, when you set the system variable secure_file_priv, you can connect to the database only through a local socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.

Here is an example:
1. Log on to the server where the OBServer node to connect to resides.
```
ssh admin@10.10.10.1
```
2. Run the following command to connect to the mysql001 tenant through a local Unix socket:
  
  Here is an example:
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. Set the file directory to /, which indicates that any path can be accessed.
```
SET GLOBAL SECURE_FILE_PRIV = "/";
```

Reconnect to the database.

Here is an example:

obclient -h127.0.0.1 -P2881 -utest_user001@mysql001 -p****** -A

Use the LOAD DATA statement to import data.

Here is an example:

Execute the following LOAD DATA statement to load data from the specified file to a data table. In this statement:
- Set the path and name of the file to be loaded to /home/admin/test_data/earthquake_dataset.csv.
- Set the name of the destination table to which the data is to be loaded to test.
- Specify comma (,) as the delimiter for fields in the data file.
- Specify to enclose fields (of the character type) in the data file by using double quotation marks (").
- Specify to use line breaks as the end characters of lines in the data file.
- Specify the mappings between columns in the source data file and those in the destination table. The first column in the data file will be mapped to the place column in the destination table, the second column to the latitude column, the third column to the longitude column, and so on.
```
obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/test_data/earthquake_dataset.csv'
    INTO TABLE test
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (place,latitude,longitude,country,continent,magnitude);
```

Step 4: Query and analyze data

Log on to the server where the OBServer node to connect to resides.
```
ssh admin@10.10.10.1
```

Connect to the database.

Here is an example:

obclient -h127.0.0.1 -P2881 -utest_user001@mysql001 -p****** -A

Query data of a specific place.

obclient [quickstart]> select * from test where place = 'Santiago';
+----------+----------+-----------+---------+----------------+-----------+
| place    | latitude | longitude | country | continent      | magnitude |
+----------+----------+-----------+---------+----------------+-----------+
| Santiago | -33.4463 | -70.6682  |  Chile  |  South America |       4.8 |
| Santiago | -33.4521 | -70.6647  |  Chile  |  South America |       4.5 |
| Santiago | -33.4505 | -70.6717  |  Chile  |  South America |       5.2 |
| Santiago | -33.4489 | -70.6693  |  Chile  |  South America |       5.6 |
| Santiago | -33.4472 | -70.6659  |  Chile  |  South America |       4.1 |
+----------+----------+-----------+---------+----------------+-----------+
5 rows in set

Count the number of earthquakes in each continent.

obclient [quickstart]> select continent,count(*) from test group by continent;
+----------------+----------+
| continent      | count(*) |
+----------------+----------+
|  South America |       90 |
|  Africa        |      580 |
|  Antarctica    |       70 |
|  North America |      190 |
|  Oceania       |       45 |
|  Asia          |      155 |
|  Europe        |      135 |
+----------------+----------+
7 rows in set

Get started with OceanBase AP

Notice

Step 1: Build an experience environment

Note

Step 2: Create a table

Step 3: Import data

Notice

Step 4: Query and analyze data