AnalyticDB:Getting started with Data Warehouse Edition

Create a privileged account

1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.

2. In the left-side navigation pane, click Accounts.

3. On the Accounts tab, click Create Privileged Account.

4. In the Create Account panel, configure the parameters that are described in the following table.

   Parameter	Description
   Database Account	The name of the privileged account. Enter a name that meets the on-screen requirements.
   Account Type	The account type. For a Data Warehouse Edition cluster, this parameter is automatically set to Privileged Account.
   New Password	The password of the privileged account. Enter a password that meets the on-screen requirements.
   Confirm Password	Enter the password of the privileged account again.
   Description	Optional. The description that is used to identify the account for future management.

5. Click OK.

Create and grant permissions to a standard account

Procedure

1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.

2. In the left-side navigation pane, click Data Security.

3. On the Whitelist Settings tab, click Modify to the right of the default whitelist.

   Note

   You can also click Create Whitelist to create an IP address whitelist.

4. In the Edit Whitelist panel, remove the default IP address 127.0.0.1 and enter the IP addresses or CIDR blocks that you want to allow. Then, click OK.

   Note

   To add the egress IP address of the client to the IP address whitelist, query the IP address first. For more information, see Connections.

Use DMS to connect to AnalyticDB for MySQLAnalyticDB for MySQL

1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.

2. In the upper-right corner of the Cluster Information page, click Log On to Database.

3. In the dialog box that appears, configure the parameters that are described in the following table.

   Parameter	Description
   Database Type	The database type of the cluster. By default, AnalyticDB MySQL 3.0 is displayed.
   Instance Region	The region of the cluster. By default, the region where the cluster resides is displayed. Note AnalyticDB for MySQL
   Instance ID	The cluster ID. By default, the ID of the current cluster is displayed. Note AnalyticDB for MySQL
   Database Account	The name of the database account used to connect to the cluster.
   Database Password	The password of the database account used to log on to the cluster. Note You can select Remember Password This way, the next time you connect to the current AnalyticDB for MySQL cluster, you do not need to enter the name and password of the database account.

   Note

   • The first time you connect to an AnalyticDB for MySQL cluster by using DMS, the data in the cluster is managed in Flexible Management mode. You can modify the control mode by editing the cluster. For more information, see Modify database instances and Control modes.

   • After you configure the parameters, you can click Test Connection in the lower-left corner of the dialog box. If the connection fails, follow the instructions to check the information you entered, such as the Database Account or Database Password parameter.

   • The system automatically adds the IP addresses or CIDR blocks of the DMS server to an IP address whitelist of the cluster. If the IP addresses or CIDR blocks fail to be added, you must manually add the IP addresses or CIDR blocks. For more information, see the "Step 2: Configure an IP address whitelist" section of this topic and Add DMS IP addresses and CIDR blocks to security settings.

4. Click Login.

Use code to connect to AnalyticDB for MySQL during application development

• JDBC

• Druid connection pool

• Python

• PHP

• C# for macOS

• Golang

Use the MySQL command-line tool to connect to AnalyticDB for MySQL

Use the MySQL command-line client to connect to AnalyticDB for MySQL

Use a client to connect to AnalyticDB for MySQL

• DBeaver

• DbVisualizer

• Navicat

• SQL WorkBench/J

Use a data visualization tool to connect to AnalyticDB for MySQL

• FineBI

• Quick BI

• Yonghong BI

• DataV

• Tableau

• QlikView

• FineReport

• Smartbi

Prerequisites

• A directory is created in an Object Storage Service (OSS) bucket by performing the following operations to store data imported from an AnalyticDB for MySQL cluster.

  1. Activate OSS. For more information, see Activate OSS.

  2. Create an OSS bucket. For more information, see Create buckets.

     Important

     Make sure that the OSS bucket resides in the same region as the AnalyticDB for MySQL cluster.

  3. Create a directory. For more information, see the "Create a directory".section of the Manage directories topic.

  4. Upload an object. For more information, see Upload objects.

     In this example, the oss_import_test_data.txt object is uploaded to the <bucket-name>.oss-cn-hangzhou.aliyuncs.com/adb/ directory in OSS. The row delimiter is a line feed, and the column delimiter is a semicolon (;). The following code shows part of the data contained in this object:

     uid;other
     12;hello_world_1
     27;hello_world_2
     28;hello_world_3
     33;hello_world_4
     37;hello_world_5
     40;hello_world_6
     ...     

• An AnalyticDB for MySQL cluster is created. An IP address whitelist is configured for the cluster. A database account and a database are created for the cluster. For more information, see Process of getting started with Data Warehouse Edition.

Procedure

1. Execute the CREATE TABLE statement to create an external table in the adb_demo database. For information about how to create an OSS external table for an object that is in the CSV, Parquet, or TEXT format, see the "Syntax for creating an OSS external table" section of this topic.

2. Query OSS data.

   You can query the data of external tables in the same manner as you query the data of internal tables of AnalyticDB for MySQL . Example:

   select uid, other from oss_import_test_external_table where uid < 100 limit 10;

   • If an object is in the CSV or TEXT format and contains a large amount of data, we recommend that you import the object to AnalyticDB for MySQL before you query the data. Otherwise, the query performance may be compromised.

   • If an object is in the Parquet format, you can determine whether to directly query the data or import the object to AnalyticDB for MySQL before you query the data.

3. Execute the CREATE TABLE statement to create a destination table named adb_oss_import_test in the adb_demo database to store the data imported from OSS.

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
       uid string,
       other string
   )
   DISTRIBUTED BY HASH(uid);                  

4. Execute an INSERT statement to import data from the OSS external table to AnalyticDB for MySQL.

   Important

   By default, the INSERT INTO and INSERT OVERWRITE SELECT statements import data synchronously. If hundreds of gigabytes of data is imported, the client must maintain a connection with the AnalyticDB for MySQL server for an extended period of time. During this process, the import may fail due to a network disconnection. Therefore, if you want to import a large amount of data, we recommend that you execute the SUBMIT JOB INSERT OVERWRITE SELECT statement to import data asynchronously.

   • Method 1: Execute the INSERT INTO statement to import data. If the primary key has duplicate values, data is not repeatedly inserted and the INSERT INTO statement is equivalent to the INSERT IGNORE INTO statement. For more information, see INSERT INTO.

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Method 2: Execute the INSERT OVERWRITE statement to import data. If the primary key has duplicate values, the original value is overwritten by the new value.

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Method 3: Execute the INSERT OVERWRITE statement to import data asynchronously. In most cases, the SUBMIT JOB statement is used to submit an asynchronous job. You can add a hint (/*+ direct_batch_load=true*/) before the data import statement to accelerate the job. For more information, see the "Asynchronous writing" section of the INSERT OVERWRITE SELECT topic.

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     Sample result:

     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303****** |

     For information about how to submit asynchronous jobs, see Asynchronously submit an import job.

5. Execute the following statement to query the data of the adb_oss_import_test table:

   SELECT * FROM adb_oss_import_test;

Syntax for creating an OSS external table

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, ...])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"txt|orc|parquet",
    "delimiter":";",
    "skip_header_line_count":1,
    "charset":"utf-8"
}';

CREATE TABLE adb_csv_hive_format_oss (
  a tinyint,
  b smallint,
  c int,
  d bigint,
  e boolean,
  f float,
  g double,
  h varchar,
  i varchar, -- binary
  j timestamp,
  k DECIMAL(10, 4),
  l varchar, -- char(10)
  m varchar, -- varchar(100)
  n date
) ENGINE = 'OSS' TABLE_PROPERTIES='{
    "format": "csv",
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "accessid":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';

parquet_partition_classic/
├── p1=2020-01-01
│ ├── p2=4
│ │ ├── p3=SHANGHAI
│ │ │ ├── 000000_0
│           └── 000000_1
│       └── p3=SHENZHEN
│ │     └── 000000_0
│ └── p2=6
│     └── p3=SHENZHEN
│         └── 000000_0
├── p1=2020-01-02
│ └── p2=8
│     ├── p3=SHANGHAI
│     │ └── 000000_0
│     └── p3=SHENZHEN
│         └── 000000_0
└── p1=2020-01-03
    └── p2=6
        ├── p2=HANGZHOU
        └── p3=SHENZHEN
            └── 000000_0

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, ...])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"parquet",
    "partition_column":"p1, p2, p3"
}';

References

For more information about how to import data to AnalyticDB for MySQL, see Supported data sources.