Quick start with OceanBase Database AP

This topic describes how to deploy OceanBase Database Community Edition. This solution is suitable for deploying a single-node OceanBase Database environment. The deployed OceanBase Database environment has the basic features of a database and can help you understand OceanBase Database. However, this environment does not support distributed capabilities or high availability. We recommend that you do not use this environment for a long time.

Step 1: Set up a quick experience environment

1. Download and install the all-in-one installation package.

   Download the community edition all-in-one installation package from OceanBase Download Center and upload it to any directory on your server.

2. Decompress and install the package in the directory where it is located.

   [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
   

3. Execute the following command to quickly deploy OceanBase Database.

   [admin@test001 ~]$ obd demo
   

   By default, the obd demo command deploys and starts OceanBase Database and its related components (ODP, OBAgent, Grafana, and Prometheus) in the minimum specification in the home directory of the current user. The deployment name is fixed to demo.

   Note

   When you install Grafana or Prometheus, the IP address of Grafana or Prometheus is displayed. In Alibaba Cloud or other cloud environments, the IP address may be an intranet IP address if it cannot obtain a public IP address. You must use the correct IP address.

4. Connect to the database by using the connection command in the output.

   After the obd demo command is executed, the command for connecting to OceanBase Database by using OBClient is displayed. Here is an example:

   • Connect to the database directly by using port 2881.

     [admin@test001 ~]$ obclient -h127.0.0.1 -P2881 -uroot@sys -Doceanbase -A
     

   • Connect to the database by using ODP.

     [admin@test001 ~]$ obclient -h127.0.0.1 -P2883 -uroot@sys -Doceanbase -A
     

5. (Optional) Configure a password.

   After you deploy OceanBase Database by using the obd demo command, you can configure a password for the demo cluster as follows:

   1. Modify the configuration file.

      obd cluster edit-config demo
      

      After you execute the preceding command to open the configuration file, add root_password: xxxx to the oceanbase-ce (Community Edition) or oceanbase (Enterprise Edition) component in the configuration file. Then, save and exit. Here is an example:

      oceanbase-ce:
        servers:
          - 127.0.0.1
        global:
          home_path: /home/admin/oceanbase-ce
          ... # Other configurations are omitted.
          log_disk_size: 13G
          root_password: ******
      

   2. Restart the cluster.

      After you modify and save the configuration file, obd displays the restart command. You can directly copy and execute the command. 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
      

      From the output, you can see that after you modify the password of the root@sys user in the configuration file, you must execute the obd cluster reload demo command to restart the demo cluster.

Step 2: Create a columnstore table

1. Create a database.

   Execute the CREATE DATABASE statement.

   Here is an example:

   Create a database named test_db, specify the character set as utf8mb4, and create read/write attributes.

   obclient> CREATE DATABASE test_db
       DEFAULT CHARACTER SET utf8mb4 READ WRITE;
   

2. Create a table.

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

   Here is an 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 the earthquake_dataset.csv file from Kaggle.

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
   

3. Log in to the server where the OBServer node resides.

   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 a URL

1. Prepare an 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 is located.

      scp earthquake_dataset.csv admin@10.10.10.1:/home/admin/test_data
      

2. Set the import file path.

   Notice

   Due to security reasons, when you set the system variable secure_file_priv, you can only modify the global variable by executing an SQL statement through a local Unix socket. For more information, see secure_file_priv.

   1. Execute the following command to log in to the server where the OBServer node is located.

      ssh admin@10.10.10.1
      

   2. Execute the following command to connect to the mysql001 tenant through a local Unix socket.

      obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
      

   3. Execute the following SQL statement to set the file directory to /, which means that any path can be accessed.

      SET GLOBAL secure_file_priv = "/";
      

3. Reconnect to the database.

   Here is an example:

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

4. Switch to the test_db database.

   obclient> USE test_db;
   

5. Preview the external data.

   You can use OceanBase URL external tables to directly query the CSV file and preview the data.

   Here is an example:

   Use a 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
   

6. Use the INSERT INTO statement to import the 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: Query and analyze the data

1. Query the data for 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
   

2. Count the number of earthquakes for each continent.

   obclient> SELECT continent, count(*)
       FROM test_tbl1
       GROUP BY continent;
   

   The returned 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

• Create a database
• Create a table
• Migrate data between tables