All Products
Search
Document Center

Insert data into AnalyticDB for MySQL

Last Updated: Oct 09, 2020

This topic describes how to use Data Lake Analytics (DLA) to read data from tables, such as dla_table_1 and dla_table_2 in Object Storage Service (OSS), and execute the INSERT…SELECT statement to write the specified data in tables, such as shipping and order_tabel, on an AnalyticDB for MySQL instance.

Procedure

Step 1: Create an OSS schema

 CREATE SCHEMA oss_data_schema with DBPROPERTIES(
   catalog='oss',
  location = 'oss://oss_bucket_name/table/'
  );

Step 2: Create an OSS table

dla_table_1

CREATE EXTERNAL TABLE IF NOT EXISTS dla_table_1 (
  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 ''
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE 
LOCATION 'oss://oss_bucket_name/table/';

dla_table_2

CREATE EXTERNAL TABLE IF NOT EXISTS dla_table_2 (
   customer_id bigint NOT NULL COMMENT '',
   order_id varchar NOT NULL COMMENT '',
   order_time date NOT NULL COMMENT '',
   order_amount double NOT NULL COMMENT '',
   order_type varchar NOT NULL COMMENT '',
   address varchar NOT NULL COMMENT '',
   city varchar NOT NULL COMMENT '',
   order_season bigint COMMENT '',
   PRIMARY KEY (customer_id)
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE 
LOCATION 'oss://oss_bucket_name/table/';

Step 3: Create an AnalyticDB for MySQL schema

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'
 );

Step 4: Create an AnalyticDB for MySQL table

shipping

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

order_table1

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'
);

Step 5: Execute the INSERT... SELECT statement to insert data from OSS into the AnalyticDB for MySQL instance

Asynchronously execute the following SQL statement to insert data from the dla_table_1 file in the OSS table folder into the shipping table in the ads_database database on the AnalyticDB for MySQL instance.

-- Insert full data from OSS to the AnalyticDB for MySQL instance.
/*+run-async=true*/
INSERT INTO ads_database_schema.shipping
SELECT * FROM oss_data_schema.dla_table_1;

Asynchronously execute the following SQL statement to insert data with order_amount greater than 2 into the order_table table of the ads_database database on the AnalyticDB for MySQL instance.

-- Insert data from OSS to the AnalyticDB for MySQL instance. The following SQL statement contains the filtering logic for OSS data.
/*+run-async=true*/
INSERT INTO ads_database_schema.order_table1 (customer1_id, order1_id, order1_time, order1_amount,order1_type,address1,city1,order1_season)
SELECT customer_id, order_id, order_time, order_amount,order_type,address,city,order_season
FROM oss_data_schema.dla_table_2 
WHERE order_amount > 2 
LIMIT 10000;