Data Warehouse Edition quick start

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. 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 page, click Create Privileged Account.

4. In the Create Account panel, configure the parameters.

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

5. Click OK to create the account.

Create and authorize 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. 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 page, find the default whitelist group and click Modify in the Actions column.

   Note

   You can also click Create Whitelist to create a custom group.

4. In the Edit Whitelist dialog box, delete the default IP address 127.0.0.1. Enter the IP addresses or CIDR blocks that need to access the cluster, and then click OK.

   Note

   To add your client's public IP address to the whitelist, you must first determine the IP address. For more information, see Connections.

Using DMS to connect to AnalyticDB 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. Find the cluster that you want to manage and click the cluster ID.

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

3. In the dialog box that appears, enter the logon information.

   Parameter	Description
   Database Type	The default value is AnalyticDB for MySQL 3.0. You do not need to change it.
   Instance Region	The default value is the region where the current instance is located. You do not need to change it.
   Cluster ID	The default value is the cluster ID of the current cluster. You do not need to change it.
   Database Account	The name of the database account for the cluster.
   Database Password	The password for the database account. Note You can select Remember Password to automatically log on to the current AnalyticDB for MySQL cluster in the future without entering the database account and password.

   Note

   • When you use DMS to log in to an AnalyticDB for MySQL cluster for the first time, the control mode is set to Flexible Management by default. After you log in, you can also change the control mode by editing the instance. For more information, see Edit an instance and Control modes.

   • After you configure the logon parameters, you can click Test Connection in the lower-left corner. If the test connection fails, check the cluster information, such as the account or password, based on the error message.

   • The system automatically adds the server IP addresses of DMS to the IP address whitelist of the database. If the IP addresses are not added automatically, add them manually. For more information, see Step 2: Configure an IP address whitelist and DMS IP address whitelist.

4. Click Log On.

Connect programmatically to AnalyticDB for MySQL

• JDBC

• Druid connection pool

• Python

• PHP

• C# (Mac)

• Golang

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

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

Use a client to connect to AnalyticDB for MySQL

• DBeaver

• DBVisualizer

• Navicat

• SQL WorkBench/J

Connect AnalyticDB for MySQL to data visualization tools

• FineBI

• Quick BI

• Yonghong BI

• DataV

• Tableau

• QlikView

• FineReport

• Smartbi

Prerequisites

• Create a directory in Object Storage Service (OSS) to store AnalyticDB for MySQL data.

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

  2. Create a bucket. For more information, see Create buckets.

     Important

     The OSS bucket must be in the same region as your AnalyticDB for MySQL cluster.

  3. Create a directory. For more information, see Create a directory.

  4. Upload a test 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. Rows are delimited by line breaks and columns are delimited by semicolons (;). The sample data is as follows:

     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
     ...     

• Follow the AnalyticDB for MySQL Getting Started Guide to complete preparations, such as creating a cluster, setting up an IP address whitelist, and creating an account and a database. For more information, see Procedure.

Procedure

1. Run the CREATE TABLE statement to create an external table in the adb_demo database. For information about the syntax for creating an OSS external table for data in CSV, Parquet, or TEXT format, see Syntax for OSS external tables.

2. Query the OSS data.

   Querying an external table uses the same syntax as querying an AnalyticDB for MySQL internal table. You can query the data directly, as shown in the following example:

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

   • For large CSV or TEXT data objects, we strongly recommend that you import the data into AnalyticDB for MySQL before querying for better query performance.

   • For Parquet data objects, direct queries generally have high performance. You can decide whether to import the data into AnalyticDB for MySQL before querying based on your needs.

3. Run 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. Run an INSERT statement to import data from the OSS external table into AnalyticDB for MySQL.

   Important

   By default, the INSERT INTO and INSERT OVERWRITE SELECT statements import data synchronously. If you import a large amount of data, such as hundreds of gigabytes, the connection between the client and the AnalyticDB for MySQL server must be maintained for a long time. During this period, network issues may interrupt the connection, which causes the data import to fail. Therefore, if you are importing a large amount of data, we recommend that you use the SUBMIT JOB INSERT OVERWRITE SELECT statement to import data asynchronously.

   • Method 1: Run the INSERT INTO statement to import data. If a primary key has duplicate values, the new data is ignored and the existing data is not updated. This behavior is equivalent to using INSERT IGNORE INTO. For more information, see INSERT INTO. The following is a sample statement:

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Method 2: Run the INSERT OVERWRITE statement to import data. This statement overwrites the existing data in the table. The following is a sample statement:

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Method 3: Run the INSERT OVERWRITE statement to import data asynchronously. You can use the SUBMIT JOB statement to submit an asynchronous task. To accelerate the write task, you can add the /*+ direct_batch_load=true*/ hint. For more information, see Asynchronous write. The following is a sample statement:

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     The following result is returned:

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

     For more information about how to submit an asynchronous task, see Submit an asynchronous import task.

5. Run the following command to query the data in the adb_oss_import_test table.

   SELECT * FROM adb_oss_import_test;

Syntax for OSS external tables

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":"CSV|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":"LTAI****************",
    "accesskey":"yourAccessKeySecret",
    "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 ways to import data, see Supported data sources.