All Products
Search
Document Center

Create an AnalyticDB for MySQL schema

Last Updated: Nov 25, 2020

Create a schema

Note: If the VPC to which your AnalyticDB for MySQL 2.0 instance belongs is different from the VPC to which Data Lake Analytics (DLA) belongs, follow Step 4 of Preparations to change the VPC to which your AnalyticDB for MySQL 2.0 instance belongs to the VPC to which DLA belongs. Otherwise, you cannot create the schema of AnalyticDB for MySQL 2.0 in the DLA console.

  1. Log on to the DLA console.

  2. In the left-side navigation pane, click SQL access point, find the required VPC, and then click Log on in DMS in the Actions column. In the DMS console, create a schema of AnalyticDB for MySQL 2.0.

     CREATE SCHEMA ads_database_schema WITH DBPROPERTIES 
     ( 
       CATALOG = 'ads', 
       LOCATION = 'jdbc:mysql://ads-database-********-vpc.cn-shanghai-1.ads.aliyuncs.com:10001/ads_database',
       USER='AnalyticDB AccessKey ID',
       PASSWORD='AnalyticDB AccessKey Secret'
     );
    • CATALOG = 'ads': indicates that a schema of AnalyticDB for MySQL 2.0 is created.

    • LOCATION: indicates the endpoint of an AnalyticDB for MySQL 2.0 cluster. The format is Endpoint:Port number/Name of the AnalyticDB for MySQL 2.0 cluster. You can obtain the endpoint from the AnalyticDB for MySQL console.

Create a table

After you create a schema, you can create a table in the DLA console. The table you create involves the following scenarios:

  • The table name, field names, or field types of the table created in the DLA console are different from those of the table of AnalyticDB for MySQL 2.0.

  • The table name, field names, or field types of the table created in the DLA console are the same as those of the table of AnalyticDB for MySQL 2.0.

Scenario 1: The table name and field names of the table created in the DLA console are the same as those of the table of AnalyticDB for MySQL 2.0. We recommend this configuration.

The following example demonstrates how to create a table named shipping in the DLA console.

CREATE EXTERNAL TABLE shipping (
  id bigint NOT NULL COMMENT '',
  origin_state varchar NOT NULL COMMENT '',
  origin_zip varchar NOT NULL COMMENT '',
  destination_state varchar NOT NULL COMMENT '',
  destination_zip varchar NOT NULL COMMENT '',
  package_weight int NOT NULL COMMENT '',
  PRIMARY KEY (id)
)

Scenario 2: The table name and field names of the table created in the DLA console are different from those of the table of AnalyticDB for MySQL 2.0.

The following example demonstrates how to create a table named order_table1 that corresponds to order_table. Some field names of the two tables are different and the data types of the order1_id and order_id fields are different.

CREATE EXTERNAL TABLE order_table1 (
  customer1_id bigint NOT NULL COMMENT '',
  order1_id bigint NOT NULL COMMENT '',
  order1_time date NOT NULL COMMENT '',
  order1_amount double NOT NULL COMMENT '',
  order1_type varchar NOT NULL COMMENT '',
  address1 varchar NOT NULL COMMENT '',
  city1 varchar NOT NULL COMMENT '',
  order1_season bigint COMMENT '',
  PRIMARY KEY (customer1_id)
)
tblproperties (
   table_mapping = 'ads_database.order_table', 
   column_mapping = 'customer1_id,customer_id; order1_id,order_id; order1_time:order_time, 
   order1_amount:order_amount, order1_type:order_type, address1:address, 
   city1:city,order1_season:order_season'
);
  • tblproperties: the mapping between the table in the DLA console and the source table.

  • table_mapping: the mapping between table names.

  • column_mapping: the mapping between the fields of the two tables.

What to do next

Execute the INSERT FROM SELECT statement to write OSS data to AnalyticDB for MySQL 2.0.