Quickly try out OceanBase AP|V4.6.0|OceanBase Database| docs|Distributed Database

This topic uses the deployment of OceanBase Database Community Edition as an example. It is intended for scenarios where only a single machine is available, enabling you to quickly set up a functional OceanBase Database environment. The deployed environment provides basic database functionality and serves as an effective way to familiarize yourself with OceanBase Database; however, it lacks distributed capabilities and high availability features, so it is not recommended for long-term use.

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: Quickly set up the 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 quickly deploy OceanBase Database.
```
[admin@test001 ~]$ obd demo
```
By default, the obd demo command deploys OceanBase Database and its components (ODP, OBAgent, Grafana, and Prometheus) with minimum specifications, and then starts them in the home directory of the current user. 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, it is possible that an intranet IP address is returned due to the inability to obtain a public IP address. You must use the 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 to connect to OceanBase Database through OBClient. Here are some examples:
- Directly connect to the database through port 2881
```
[admin@test001 ~]$ obclient -h127.0.0.1 -P2881 -uroot@sys -Doceanbase -A
```
- Connect to the database through ODP
```
[admin@test001 ~]$ obclient -h127.0.0.1 -P2883 -uroot@sys -Doceanbase -A
```
(Optional) Configure the password.

After you deploy OceanBase Database in the demo cluster 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 above command to open the configuration file, add root_password: xxxx under the oceanbase-ce or oceanbase component, based on the edition, 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
```
  As shown in the output, after you modify the password of the root@sys user in the configuration file, you must run obd cluster reload demo to restart the demo cluster.

Step 2: Create a columnstore table

Create a database.

You can run the CREATE DATABASE statement to create a database.

Here is an example:

Create a database named test_db, set its character set to utf8mb4, and set its read and write properties.
```
obclient> CREATE DATABASE test_db
    DEFAULT CHARACTER SET utf8mb4 READ WRITE;
```
Use the test_db database.
```
obclient> USE test_db;
```

Create a columnstore table.

You can run the CREATE TABLE statement to create a table in the database.

Here is an example:

Create a columnstore table named test_tbl1 in the test_db database.

obclient> CREATE TABLE test_tbl1 (
    place VARCHAR(500),
    latitude VARCHAR(50),
    longitude VARCHAR(50),
    country VARCHAR(1000),
    continent VARCHAR(100),
    magnitude DECIMAL(3, 1)
    )
    WITH COLUMN GROUP(each column);

Step 3: Import data from an external URL table

Note

This topic uses the sample dataset earthquake_dataset.csv from Kaggle as an example.

Prepare the external data source.
1. Download the earthquake_dataset.csv file from Kaggle.
2. 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
```
Set the file path for importing data.

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.
1. Run the following command to log in to the server where the OBServer node to connect to resides.
```
ssh admin@10.10.10.1
```
2. Connect to the mysql001 tenant through a local Unix socket.
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. Run the following SQL statement to set the file path 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

Switch to the test_db database.
```
obclient> USE test_db;
```

Preview external data.

You can use an URL external table to directly query the CSV file and preview the data.

Here is an example:

Use an URL external table to directly query the CSV file and preview the data.

obclient> SELECT *
    FROM FILES (
        LOCATION = '/home/admin/test_data',
        FORMAT = (
            TYPE = 'CSV'
            FIELD_DELIMITER = ','
            FIELD_OPTIONALLY_ENCLOSED_BY ='"'
            SKIP_HEADER = 1),
        PATTERN = 'earthquake_dataset.csv'
    )
    LIMIT 10;

The returned result is as follows:

+-------------------+----------+----------+---------------+----------------+------+
| c1                | c2       | c3       | c4            | c5             | c6   |
+-------------------+----------+----------+---------------+----------------+------+
|Bamako            | 12.6354  | -8.0023  |  Mali         |  Africa        | 4.7
|Niamey            | 13.513   | 2.1151   |  Niger        |  Africa        | 5.7
|Southern Chile    | -39.8234 | -73.0691 |  Chile        |  South America | 4.9
|Freetown          | 8.4815   | -13.2315 |  Sierra Leone |  Africa        | 4.8
|Bamako            | 12.6422  | -7.999   |  Mali         |  Africa        | 5.3
|Fort-de-France    | 14.6132  | -61.06   |  Martinique   |  North America | 5.7
|Santiago          | -33.4463 | -70.6682 |  Chile        |  South America | 4.8
|East African Rift | -1.2921  | 36.8219  |  Kenya        |  Africa        | 4.6
|Manila            | 13.4165  | 122.5589 |  Philippines  |  Asia          | 5.1
|San Salvador      | 13.6943  | -89.2216 |  El Salvador  |  North America | 5.7
+-------------------+----------+----------+---------------+----------------+------+
10 rows in set

Use the INSERT INTO statement to import data into the test_tbl1 table.

obclient> INSERT INTO test_tbl1
    SELECT *
    FROM (
        SELECT *
        FROM FILES (
            LOCATION = '/home/admin/test_data',
            FORMAT = (
                TYPE = 'CSV'
                FIELD_DELIMITER = ','
                FIELD_OPTIONALLY_ENCLOSED_BY ='"'
                SKIP_HEADER = 1),
            PATTERN = 'earthquake_dataset.csv'
            )
    );

The returned result is as follows:

Query OK, 1265 rows affected
Records: 1265  Duplicates: 0  Warnings: 0

Step 4: Perform query analysis

Query the number of earthquakes in a specific place.

obclient> SELECT *
    FROM test_tbl1
    WHERE place = 'Santiago';

The returned result is as follows:

+----------+----------+-----------+---------+----------------+-----------+
| 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> SELECT continent, count(*)
    FROM test_tbl1
    GROUP BY continent;

The result is as follows:

+----------------+----------+
| continent      | count(*) |
+----------------+----------+
|  Africa        |      580 |
|  South America |       90 |
|  North America |      190 |
|  Asia          |      155 |
|  Oceania       |       45 |
|  Europe        |      135 |
|  Antarctica    |       70 |
+----------------+----------+
7 rows in set

References

Notice

Step 1: Quickly set up the 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 quickly deploy OceanBase Database.
```
[admin@test001 ~]$ obd demo
```
By default, the obd demo command deploys OceanBase Database and its components (ODP, OBAgent, Grafana, and Prometheus) with minimum specifications, and then starts them in the home directory of the current user. 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, it is possible that an intranet IP address is returned due to the inability to obtain a public IP address. You must use the 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 to connect to OceanBase Database through OBClient. Here are some examples:
- Directly connect to the database through port 2881
```
[admin@test001 ~]$ obclient -h127.0.0.1 -P2881 -uroot@sys -Doceanbase -A
```
- Connect to the database through ODP
```
[admin@test001 ~]$ obclient -h127.0.0.1 -P2883 -uroot@sys -Doceanbase -A
```
(Optional) Configure the password.

After you deploy OceanBase Database in the demo cluster 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 above command to open the configuration file, add root_password: xxxx under the oceanbase-ce or oceanbase component, based on the edition, 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
```
  As shown in the output, after you modify the password of the root@sys user in the configuration file, you must run obd cluster reload demo to restart the demo cluster.

Step 2: Create a columnstore table

Create a database.

You can run the CREATE DATABASE statement to create a database.

Here is an example:

Create a database named test_db, set its character set to utf8mb4, and set its read and write properties.
```
obclient> CREATE DATABASE test_db
    DEFAULT CHARACTER SET utf8mb4 READ WRITE;
```
Use the test_db database.
```
obclient> USE test_db;
```

Create a columnstore table.

You can run the CREATE TABLE statement to create a table in the database.

Here is an example:

Create a columnstore table named test_tbl1 in the test_db database.

obclient> CREATE TABLE test_tbl1 (
    place VARCHAR(500),
    latitude VARCHAR(50),
    longitude VARCHAR(50),
    country VARCHAR(1000),
    continent VARCHAR(100),
    magnitude DECIMAL(3, 1)
    )
    WITH COLUMN GROUP(each column);

Step 3: Import data from an external URL table

Note

This topic uses the sample dataset earthquake_dataset.csv from Kaggle as an example.

Prepare the external data source.
1. Download the earthquake_dataset.csv file from Kaggle.
2. 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
```
Set the file path for importing data.

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.
1. Run the following command to log in to the server where the OBServer node to connect to resides.
```
ssh admin@10.10.10.1
```
2. Connect to the mysql001 tenant through a local Unix socket.
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. Run the following SQL statement to set the file path 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

Switch to the test_db database.
```
obclient> USE test_db;
```

Preview external data.

You can use an URL external table to directly query the CSV file and preview the data.

Here is an example:

Use an URL external table to directly query the CSV file and preview the data.

obclient> SELECT *
    FROM FILES (
        LOCATION = '/home/admin/test_data',
        FORMAT = (
            TYPE = 'CSV'
            FIELD_DELIMITER = ','
            FIELD_OPTIONALLY_ENCLOSED_BY ='"'
            SKIP_HEADER = 1),
        PATTERN = 'earthquake_dataset.csv'
    )
    LIMIT 10;

The returned result is as follows:

+-------------------+----------+----------+---------------+----------------+------+
| c1                | c2       | c3       | c4            | c5             | c6   |
+-------------------+----------+----------+---------------+----------------+------+
|Bamako            | 12.6354  | -8.0023  |  Mali         |  Africa        | 4.7
|Niamey            | 13.513   | 2.1151   |  Niger        |  Africa        | 5.7
|Southern Chile    | -39.8234 | -73.0691 |  Chile        |  South America | 4.9
|Freetown          | 8.4815   | -13.2315 |  Sierra Leone |  Africa        | 4.8
|Bamako            | 12.6422  | -7.999   |  Mali         |  Africa        | 5.3
|Fort-de-France    | 14.6132  | -61.06   |  Martinique   |  North America | 5.7
|Santiago          | -33.4463 | -70.6682 |  Chile        |  South America | 4.8
|East African Rift | -1.2921  | 36.8219  |  Kenya        |  Africa        | 4.6
|Manila            | 13.4165  | 122.5589 |  Philippines  |  Asia          | 5.1
|San Salvador      | 13.6943  | -89.2216 |  El Salvador  |  North America | 5.7
+-------------------+----------+----------+---------------+----------------+------+
10 rows in set

Use the INSERT INTO statement to import data into the test_tbl1 table.

obclient> INSERT INTO test_tbl1
    SELECT *
    FROM (
        SELECT *
        FROM FILES (
            LOCATION = '/home/admin/test_data',
            FORMAT = (
                TYPE = 'CSV'
                FIELD_DELIMITER = ','
                FIELD_OPTIONALLY_ENCLOSED_BY ='"'
                SKIP_HEADER = 1),
            PATTERN = 'earthquake_dataset.csv'
            )
    );

The returned result is as follows:

Query OK, 1265 rows affected
Records: 1265  Duplicates: 0  Warnings: 0

Step 4: Perform query analysis

Query the number of earthquakes in a specific place.

obclient> SELECT *
    FROM test_tbl1
    WHERE place = 'Santiago';

The returned result is as follows:

+----------+----------+-----------+---------+----------------+-----------+
| 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> SELECT continent, count(*)
    FROM test_tbl1
    GROUP BY continent;

The result is as follows:

+----------------+----------+
| continent      | count(*) |
+----------------+----------+
|  Africa        |      580 |
|  South America |       90 |
|  North America |      190 |
|  Asia          |      155 |
|  Oceania       |       45 |
|  Europe        |      135 |
|  Antarctica    |       70 |
+----------------+----------+
7 rows in set

OceanBase

Customer Stories

Documentation

Quickly try out OceanBase AP

Notice

Step 1: Quickly set up the experience environment

Note

Step 2: Create a columnstore table

Step 3: Import data from an external URL table

Note

Notice

Step 4: Perform query analysis

References

Quickly try out OceanBase AP

Notice

Step 1: Quickly set up the experience environment

Note

Step 2: Create a columnstore table

Step 3: Import data from an external URL table

Note

Notice

Step 4: Perform query analysis

References