Data Lake Analytics - Deprecated:OceanBase

Prerequisites

• DLA CU Edition is activated. For more information, see Use the DLA Presto-compatible Presto CU edition. Only DLA CU Edition can access an OceanBase database.
• The network of the virtual cluster (VC) must be in the same VPC as the OceanBase cluster. For more information about the network of the VC, see Use the DLA Presto-compatible Presto CU edition.

Preparations

Before you read data from and write data to the OceanBase database, you must create a test table in the OceanBase database.

create table person (
  id bigint, 
  name varchar(64),
  age int);

Procedure

1. Connect to DLA.
2. Create an OceanBase database.

   CREATE DATABASE `oceanbase_test`
   WITH DBPROPERTIES (
     catalog = 'oceanbase', 
     location = 'jdbc:oceanbase://oceanbaseIp:oceanbasePort/testDatabaseName',
     user = 'userName',
     password = 'password', 
     vpc_id = 'vpcId',
     instance_id = 'instanceId' );

   Parameters:

   • catalog: the schema you created. The value oceanbase indicates that an OceanBase schema is created.
   • location: the address and name of the OceanBase database.
   • user: the username that is used to log on to the OceanBase database.
   • password: the password that is used to log on to the OceanBase database.
   • vpc_id: the VPC ID of the network where the OceanBase database is deployed.
   • instance_id: the instance ID of the OceanBase database.
3. Create a table.

   CREATE EXTERNAL TABLE oceanbase_test.person (
     id int,
     name varchar,
     age int);

   Notice The table name and sequence and data type of fields must be the same as those of the table in the OceanBase database.
4. Add hint: /*+cluster=your-vc-name*/ to the SQL statements that are used to access tables in the OceanBase database. This step is required because only CUs of DLA CU Edition can access the OceanBase database. After this hint is added, the SQL statements can be properly executed on DLA CU Edition.
   Example:

   mysql> /*+ cluster=vc-test */ insert into oceanbase_test.person values (1, 'james', 10);
   +------+
   | rows |
   +------+
   |    1 |
   +------+
   1 row in set (0.46 sec)
   mysql> /*+ cluster=vc-test */ select id, name, age from oceanbase_test.person;
   +------+-------+------+
   | id   | name  | age  |
   +------+-------+------+
   |    1 | james |   10 |
   +------+-------+------+
   1 row in set (0.43 sec)

   For more information about SQL statements, see SQL Reference.