All Products
Search
Document Center

Create an AnalyticDB for MySQL schema and a table

Last Updated: Apr 05, 2020

To write data from Object Storage Service (OSS) to AnalyticDB for MySQL by executing the INSERT INTO SELECT clause, you must create an AnalyticDB for MySQL schema and its external tables in Data Lake Analytics (DLA) first.

Create a schema

  1. Log on to the DLA console.

  2. In the left-side navigation pane, click Endpoint. In the endpoint list, find the target endpoint and click Log on in DMS. On the DMS for Data Lake Analytics page, execute the following statement to create an AnalyticDB for MySQL schema in DLA.

    1. CREATE SCHEMA ads_database_schema WITH DBPROPERTIES
    2. (
    3. CATALOG = 'adb3',
    4. LOCATION = 'jdbc:mysql://am-bp1m76i0ivxpc60v313****.ads.aliyuncs.com:3306/adb_demo',
    5. VPC_ID='your-adb-vpc_id',
    6. INSTANCE_ID='your-adb-instance_id',
    7. USER='account1',
    8. PASSWORD='Account1'
    9. );
    Parameter Description
    CATALOG = 'adb3' Specifies the AnalyticDB for MySQL 3.0 schema.
    LOCATION The connection information for the AnalyticDB for MySQL database.
    VPC_ID The ID of the VPC to which the AnalyticDB for MySQL database belongs.
    INSTANCE_ID The ID of the cluster to which the AnalyticDB for MySQL database belongs.
    USER The account created for the AnalyticDB for MySQL database.
    PASSWORD USERThe password corresponding to the account name.

Create a table

You can create an AnalyticDB for MySQL table in DLA in either of the following two scenarios:

  • The table name, field name, or field type is inconsistent with that of the corresponding table in AnalyticDB for MySQL.

  • The table name, field name, and field type are completely consistent with those of the corresponding table in AnalyticDB for MySQL.

The following example illustrates how to create the shipping table in DLA.

  1. CREATE EXTERNAL TABLE shipping (
  2. id bigint NOT NULL COMMENT '',
  3. origin_state varchar NOT NULL COMMENT '',
  4. origin_zip varchar NOT NULL COMMENT '',
  5. destination_state varchar NOT NULL COMMENT '',
  6. destination_zip varchar NOT NULL COMMENT '',
  7. package_weight int NOT NULL COMMENT '',
  8. PRIMARY KEY (id)
  9. )

Create a table with table name or field that is inconsistent with that in AnalyticDB for MySQL

The following example illustrates how to create order_table1 in DLA, which corresponds to order_table. Some field names in order_table1 are different from those in order_table, and the data type of order1_id is different from that of order_id.

  1. CREATE EXTERNAL TABLE order_table1 (
  2. customer1_id bigint NOT NULL COMMENT '',
  3. order1_id bigint NOT NULL COMMENT '',
  4. order1_time date NOT NULL COMMENT '',
  5. order1_amount double NOT NULL COMMENT '',
  6. order1_type varchar NOT NULL COMMENT '',
  7. address1 varchar NOT NULL COMMENT '',
  8. city1 varchar NOT NULL COMMENT '',
  9. order1_season bigint COMMENT '',
  10. PRIMARY KEY (customer1_id)
  11. )
  12. tblproperties (
  13. table_mapping = 'adb_demo.order_table',
  14. column_mapping = 'customer1_id,customer_id; order1_id,order_id; order1_time:order_time,
  15. order1_amount:order_amount, order1_type:order_type, address1:address,
  16. city1:city,order1_season:order_season'
  17. );
ParameterDescription
tblpropertiesThe mapping between the table in DLA and the source table.
table_mappingThe mapping between table names.
column_mappingThe mapping between fields (columns).