AnalyticDB:Data Warehouse Edition Quick Start

Create a privileged account

1. Log on to the AnalyticDB for MySQL console. In the upper-left corner, select a region. In the left-side navigation pane, click Clusters. Find the cluster 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 following parameters.

   Parameter	Description
   Database Account	The name of the privileged account. Must meet the on-screen requirements.
   Account Type	Automatically set to Privileged Account for Data Warehouse Edition clusters.
   New Password	The account password. Must meet the on-screen requirements.
   Confirm Password	Re-enter the password.
   Description	(Optional) A note to identify the account.

5. Click OK.

Create a standard account

Standard accounts created via SQL statements are not shown in the console. Use the following SQL commands to manage them:

• Create an account: CREATE USER

• Grant permissions: GRANT

• Revoke permissions: REVOKE

• Rename an account: RENAME USER

• Delete an account: DROP USER

Configure the whitelist

1. In the AnalyticDB for MySQL console, navigate to the cluster detail page.

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.

   To create a separate whitelist instead of editing the default one, click Create Whitelist.

4. In the Edit Whitelist panel, remove 127.0.0.1, enter the IP addresses or CIDR blocks to allow, and click OK.

   To find the egress IP address of your client machine, see Connections.

Use DMS

1. In the AnalyticDB for MySQL console, navigate to the cluster detail page.

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

3. In the dialog box, fill in the connection details.

   - The first time you connect via DMS, the cluster is managed in Flexible ManagementEdit an instance mode. To change the control mode, see Modify database instances. For available control modes, see Control modes. - Click Test Connection in the lower-left corner to verify the connection before logging in. - DMS automatically adds its server IPs to the cluster's whitelist. If this fails, add them manually — see Add DMS IP addresses and CIDR blocks to security settings.

   Parameter	Description
   Database Type	Defaults to AnalyticDB MySQL 3.0.
   Instance Region	Defaults to the cluster's region.
   Instance ID	Defaults to the current cluster ID.
   Database Account	The name of the database account.
   Database Password	The password for the account. Select Remember Password to skip entering credentials next time.

4. Click Login.

Use a client or tool

MySQL clients:

• MySQL command-line client

• DBeaver

• DbVisualizer

• Navicat

• SQL WorkBench/J

Business intelligence (BI) tools:

• FineBI

• Quick BI

• DataV

• Tableau

• QlikView

• FineReport

• Smartbi

Application code:

• JDBC

• Druid connection pool

• Python

• PHP

• C# for macOS

• Golang

Import data from OSS

1. In the SQL Console, select the adb_demo database and create an OSS external table:

   CREATE TABLE oss_import_test_external_table
   (
       uid    STRING,
       other  STRING
   )
   ENGINE='OSS'
   TABLE_PROPERTIES='{
       "endpoint":  "oss-cn-hangzhou-internal.aliyuncs.com",
       "url":       "oss://<bucket-name>/adb/oss_import_test_data.txt",
       "accessid":  "<your-access-key-id>",
       "accesskey": "<your-access-key-secret>",
       "format":    "csv",
       "delimiter": ";"
   }';

   For the full syntax and all supported parameters, see OSS external table reference.

2. Run a test query to verify the external table reads correctly:

   For CSV and TEXT format files with large amounts of data, import the data into an internal table before querying. Direct queries against large external tables may have lower performance. For Parquet format files, direct queries are also supported.

   SELECT uid, other
   FROM oss_import_test_external_table
   WHERE uid < 100
   LIMIT 10;

   The query returns rows from the OSS file. If no rows are returned, check that the endpoint, URL, and credentials are correct.

3. Create an internal destination table:

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

4. Import the OSS data into the internal table. Choose one of the following methods based on your data volume: Method 1 — Synchronous import (small datasets): Use INSERT INTO when duplicate primary keys should be ignored (equivalent to INSERT IGNORE INTO):

   Important

   Both INSERT INTO and INSERT OVERWRITE SELECT run synchronously. For large datasets (hundreds of gigabytes), a network disconnection during a long-running import will cause the job to fail. Use Method 2 instead.

   INSERT INTO adb_oss_import_test
   SELECT * FROM oss_import_test_external_table;

   Use INSERT OVERWRITE when duplicate primary keys should overwrite existing rows:

   INSERT OVERWRITE adb_oss_import_test
   SELECT * FROM oss_import_test_external_table;

   Method 2 — Asynchronous import (large datasets, recommended): Submit the import as a background job using SUBMIT JOB. You can add a hint (/*+ direct_batch_load=true*/) to accelerate the load:

   SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
   SELECT * FROM oss_import_test_external_table;

   The command returns a job ID immediately:

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

   To monitor the job status, see Asynchronously submit an import job.

5. After the import completes, query the internal table to verify the data:

   SELECT * FROM adb_oss_import_test;

OSS external table reference

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, ...])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":                   "endpoint",
    "url":                        "oss://bucket-name/path/",
    "accessid":                   "your-access-key-id",
    "accesskey":                  "your-access-key-secret",
    "format":                     "text|orc|parquet",
    "delimiter":                  ";",
    "skip_header_line_count":     1,
    "charset":                    "utf-8"
}';

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://bucket-name/parquet_partition_classic/",
    "accessid":         "your-access-key-id",
    "accesskey":        "your-access-key-secret",
    "format":           "parquet",
    "partition_column": "p1, p2, p3"
}';

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 in Hive
    j TIMESTAMP,
    k DECIMAL(10, 4),
    l VARCHAR,  -- CHAR(10) in Hive
    m VARCHAR,  -- VARCHAR(100) in Hive
    n DATE
)
ENGINE='OSS'
TABLE_PROPERTIES='{
    "format":                       "csv",
    "endpoint":                     "oss-cn-hangzhou-internal.aliyuncs.com",
    "accessid":                     "LTAI****************",
    "accesskey":                    "<your-access-key-secret>",
    "url":                          "oss://testBucketname/adb_data/",
    "delimiter":                    "\\1",
    "null_value":                   "\\\\N",
    "oss_ignore_quote_and_escape":  "true",
    "ossnull":                      2
}';