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;