Introduction to the
The goal is to let users and developers of data warehouse on the cloud experience the main process of building cloud native data warehouse based on AnalyticDB MySQL edition and DMS through simple steps. The scene will be through instance opening, structure and data initialization, report development, report visualization and other links. Three specific application scenarios are used to experience the interactive query and ETL calculation speed of AnalyticDB MySQL edition in the new retail scenario, as well as the process of data warehouse data report development by DMS.
The data set provided is the simulated data of a retail scenario, including customer information, order records, goods information, national and regional information, etc. The total data amount is 10GB, and the maximum data table record number is 59.99 million.
Product introduction
AnalyticDB MySQL edition is a new generation of cloud native data warehouse that supports high concurrency and low delay query. It is highly compatible with MySQL protocol and SQL:2003 syntax standard. AnalyticDB can carry out real-time multidimensional analysis perspective and business exploration on massive data, and quickly build data warehouse on enterprise cloud.
Data management DMS is a cloud version based on the database service platform of Alibaba Group for more than ten years. It provides a web database management terminal free of installation, free of operation and maintenance, out-of-the-box, unified with multiple database types and multiple environments. It can quickly copy and build the same safe, efficient and standardized database DevOps research and development process and number warehouse development solution as Ali Group for enterprise users.
Create a high-permission account
ADB cluster details page, customized high – permission accounts and passwords on demand
Each cluster has only one high-permission account and its password can be changed. Follow-up by DMS new ordinary account (ADB instance free free operating control mode in the DMS directly use) address: ads.console.aliyun.com/adb/cn-shen…
Select the region where the ADB instance is created, and click the instance name.
Creating a database
The database library name can be customized as required.
Address: ads.console.aliyun.com/adb/cn-shen…
Select the region where the ADB instance is created, and click the instance name.
Creating an OSS appearance
Description:
The following build sentence needs to replace the string in the endpoint and URL parameter, respectively, based on the ADB locale. For example, ADB is in the Beijing region. Oss-cn-shenzhen-internal.aliyuncs.com needs to be replaced by OSS-CN-internal.aliyuncs.com Replace “oss:// adb-tpCH-shenzhen” with “oss:// adb-tpCH-Beijing”
Shenzhen: Shenzhen Beijing: Beijing Hangzhou: Hangzhou Shanghai: Shanghai Zhangjiakou: Zhangjiakou Note: The following construction statements should be executed according to your cloud account accessiD and AccessKey.
Address: dms.aliyun.com/
Path: All Functions – Data scheme – Data import
####1, CUSTOMER appearance CREATE TABLE 'OSS_CUSTOMER' (' C_CUSTKEY 'int NOT NULL,' C_NAME 'varchar NOT NULL, `C_ADDRESS` varchar NOT NULL, `C_NATIONKEY` int NOT NULL, `C_PHONE` varchar NOT NULL, `C_ACCTBAL` decimal(12, 2) NOT NULL, `C_MKTSEGMENT` varchar NOT NULL, `C_COMMENT` varchar NOT NULL ,`dummy` varchar ) engine='oss' TABLE_PROPERTIES='{ "endpoint":"oss-cn-shenzhen-internal.aliyuncs.com", "accessid":"xxxxxxxx", "accesskey":"xxxxxxxx", "delimiter":"|", "URL":"oss://adb-tpch-shenzhen/tpch/10g/customer/customer.tbl" }'; ####2, ORDERS CREATE TABLE 'OSS_ORDERS' (' O_ORDERKEY 'bigint NOT NULL,' O_CUSTKEY 'int NOT NULL, `O_ORDERSTATUS` varchar NOT NULL, `O_TOTALPRICE` decimal(12, 2) NOT NULL, `O_ORDERDATE` date NOT NULL, `O_ORDERPRIORITY` varchar NOT NULL, `O_CLERK` varchar NOT NULL, `O_SHIPPRIORITY` int NOT NULL, `O_COMMENT` varchar NOT NULL ,`dummy` varchar ) engine='oss' TABLE_PROPERTIES='{ "endpoint":"oss-cn-shenzhen-internal.aliyuncs.com", "accessid":"xxxxxxxx", "accesskey":"xxxxxxxx", "delimiter":"|", "URL":"oss://adb-tpch-shenzhen/tpch/10g/orders/orders.tbl" }'; ####3, LINEITEM CREATE TABLE 'OSS_LINEITEM' (' L_ORDERKEY 'bigint NOT NULL,' L_PARTKEY 'int NOT NULL, `L_SUPPKEY` int NOT NULL, `L_LINENUMBER` bigint NOT NULL, `L_QUANTITY` decimal(12, 2) NOT NULL, `L_EXTENDEDPRICE` decimal(12, 2) NOT NULL, `L_DISCOUNT` decimal(12, 2) NOT NULL, `L_TAX` decimal(12, 2) NOT NULL, `L_RETURNFLAG` varchar NOT NULL, `L_LINESTATUS` varchar NOT NULL, `L_SHIPDATE` date NOT NULL, `L_COMMITDATE` date NOT NULL, `L_RECEIPTDATE` date NOT NULL, `L_SHIPINSTRUCT` varchar NOT NULL, `L_SHIPMODE` varchar NOT NULL, `L_COMMENT` varchar NOT NULL ,`dummy` varchar ) engine='oss' TABLE_PROPERTIES='{ "endpoint":"oss-cn-shenzhen-internal.aliyuncs.com", "accessid":"xxxxxxxx", "accesskey":"xxxxxxxx", "delimiter":"|", "URL":"oss://adb-tpch-shenzhen/tpch/10g/lineitem/lineitem.tbl" }'; ####4, NATION CREATE TABLE 'OSS_NATION' (' N_NATIONKEY 'int NOT NULL,' N_NAME 'varchar NOT NULL, `N_REGIONKEY` int NOT NULL, `N_COMMENT` varchar ,`dummy` varchar ) engine='oss' TABLE_PROPERTIES='{ "endpoint":"oss-cn-shenzhen-internal.aliyuncs.com", "accessid":"xxxxxxxx", "accesskey":"xxxxxxxx", "delimiter":"|", "URL":"oss://adb-tpch-shenzhen/tpch/10g/nation/nation.tbl" }'; ####5, PART CREATE TABLE 'OSS_PART' (' P_PARTKEY 'int NOT NULL,' P_NAME 'varchar NOT NULL,' P_MFGR 'varchar NOT NULL, `P_BRAND` varchar NOT NULL, `P_TYPE` varchar NOT NULL, `P_SIZE` int NOT NULL, `P_CONTAINER` varchar NOT NULL, `P_RETAILPRICE` decimal(12, 2) NOT NULL, `P_COMMENT` varchar NOT NULL ,`dummy` varchar ) engine='oss' TABLE_PROPERTIES='{ "endpoint":"oss-cn-shenzhen-internal.aliyuncs.com", "accessid":"xxxxxxxx", "accesskey":"xxxxxxxx", "delimiter":"|", "URL":"oss://adb-tpch-shenzhen/tpch/10g/part/part.tbl" }'; ####6, PARTSUPP CREATE TABLE 'OSS_PARTSUPP' (' PS_PARTKEY 'int NOT NULL,' PS_SUPPKEY 'int NOT NULL, `PS_AVAILQTY` int NOT NULL, `PS_SUPPLYCOST` decimal(12, 2) NOT NULL, `PS_COMMENT` varchar NOT NULL ,`dummy` varchar ) engine='oss' TABLE_PROPERTIES='{ "endpoint":"oss-cn-shenzhen-internal.aliyuncs.com", "accessid":"xxxxxxxx", "accesskey":"xxxxxxxx", "delimiter":"|", "URL":"oss://adb-tpch-shenzhen/tpch/10g/partsupp/partsupp.tbl" }'; ####7, REGION appearance CREATE TABLE 'OSS_REGION' (' R_REGIONKEY 'int NOT NULL,' R_NAME 'varchar NOT NULL, `R_COMMENT` varchar ,`dummy` varchar ) engine='oss' TABLE_PROPERTIES='{ "endpoint":"oss-cn-shenzhen-internal.aliyuncs.com", "accessid":"xxxxxxxx", "accesskey":"xxxxxxxx", "delimiter":"|", "URL":"oss://adb-tpch-shenzhen/tpch/10g/region/region.tbl" }'; ####8, SUPPLIER appearance CREATE TABLE 'OSS_SUPPLIER' (' S_SUPPKEY 'int NOT NULL,' S_NAME 'varchar NOT NULL, `S_ADDRESS` varchar NOT NULL, `S_NATIONKEY` int NOT NULL, `S_PHONE` varchar NOT NULL, `S_ACCTBAL` decimal(12, 2) NOT NULL, `S_COMMENT` varchar NOT NULL ,`dummy` varchar ) engine='oss' TABLE_PROPERTIES='{ "endpoint":"oss-cn-shenzhen-internal.aliyuncs.com", "accessid":"xxxxxxxx", "accesskey":"xxxxxxxx", "delimiter":"|", "URL":"oss://adb-tpch-shenzhen/tpch/10g/supplier/supplier.tbl" }';Copy the codeCreate a table of ADB
Create a table of ADB
The procedure is the same as the OSS appearance creation
Address: dms.aliyun.com/ Path: All features – data scheme – data import initialization table structure statement as follows: (Note, the script needs to be copied to the local editor, save as a. SQL suffix text for work order submission)
#### CUSTOMER CREATE TABLE 'CUSTOMER' (' C_CUSTKEY 'int NOT NULL,' C_NAME 'varchar NOT NULL, `C_ADDRESS` varchar NOT NULL, `C_NATIONKEY` int NOT NULL, `C_PHONE` varchar NOT NULL, `C_ACCTBAL` decimal(12, 2) NOT NULL, `C_MKTSEGMENT` varchar NOT NULL, `C_COMMENT` varchar NOT NULL, primary key (c_custkey) ) DISTRIBUTE BY HASH(`c_custkey`) INDEX_ALL='Y'; #### ORDERS CREATE TABLE 'ORDERS' (' O_ORDERKEY 'bigint NOT NULL,' O_CUSTKEY 'int NOT NULL, `O_ORDERSTATUS` varchar NOT NULL, `O_TOTALPRICE` decimal(12, 2) NOT NULL, `O_ORDERDATE` date NOT NULL, `O_ORDERPRIORITY` varchar NOT NULL, `O_CLERK` varchar NOT NULL, `O_SHIPPRIORITY` int NOT NULL, `O_COMMENT` varchar NOT NULL, primary key (o_orderkey,o_orderdate) ) DISTRIBUTE BY HASH(`o_orderkey`) PARTITION BY VALUE(`date_format(O_ORDERDATE, '%Y%m')`) LIFECYCLE 90 INDEX_ALL='Y'; #### LINEITEM CREATE TABLE 'LINEITEM' (' L_ORDERKEY 'bigint NOT NULL,' L_PARTKEY 'int NOT NULL, `L_SUPPKEY` int NOT NULL, `L_LINENUMBER` bigint NOT NULL, `L_QUANTITY` decimal(12, 2) NOT NULL, `L_EXTENDEDPRICE` decimal(12, 2) NOT NULL, `L_DISCOUNT` decimal(12, 2) NOT NULL, `L_TAX` decimal(12, 2) NOT NULL, `L_RETURNFLAG` varchar NOT NULL, `L_LINESTATUS` varchar NOT NULL, `L_SHIPDATE` date NOT NULL, `L_COMMITDATE` date NOT NULL, `L_RECEIPTDATE` date NOT NULL, `L_SHIPINSTRUCT` varchar NOT NULL, `L_SHIPMODE` varchar NOT NULL, `L_COMMENT` varchar NOT NULL, primary key (l_orderkey,l_linenumber,l_shipdate) ) DISTRIBUTE BY HASH(`l_orderkey`) PARTITION BY VALUE(`date_format(l_shipdate, '%Y%m')`) LIFECYCLE 90 INDEX_ALL='Y'; #### NATION CREATE TABLE 'NATION' (' N_NATIONKEY 'int NOT NULL,' N_NAME 'varchar NOT NULL,' N_REGIONKEY 'int NOT NULL, `N_COMMENT` varchar, primary key (n_nationkey) ) DISTRIBUTE BY BROADCAST INDEX_ALL='Y'; #### PART CREATE TABLE 'PART' (' P_PARTKEY 'int NOT NULL,' P_NAME 'varchar NOT NULL,' P_MFGR 'varchar NOT NULL, `P_BRAND` varchar NOT NULL, `P_TYPE` varchar NOT NULL, `P_SIZE` int NOT NULL, `P_CONTAINER` varchar NOT NULL, `P_RETAILPRICE` decimal(12, 2) NOT NULL, `P_COMMENT` varchar NOT NULL, primary key (p_partkey) ) DISTRIBUTE BY HASH(`p_partkey`) INDEX_ALL='Y'; #### PARTSUPP CREATE TABLE 'PARTSUPP' (' PS_PARTKEY 'int NOT NULL,' PS_SUPPKEY 'int NOT NULL, `PS_AVAILQTY` int NOT NULL, `PS_SUPPLYCOST` decimal(12, 2) NOT NULL, `PS_COMMENT` varchar NOT NULL, primary key (ps_partkey,ps_suppkey) ) DISTRIBUTE BY HASH(`ps_partkey`) INDEX_ALL='Y'; #### REGION TABLE CREATE TABLE 'REGION' (' R_REGIONKEY 'int NOT NULL,' R_NAME 'varchar NOT NULL,' R_COMMENT 'varchar, primary key (r_regionkey) ) DISTRIBUTE BY BROADCAST INDEX_ALL='Y'; #### SUPPLIER TABLE CREATE TABLE 'SUPPLIER' (' S_SUPPKEY 'int NOT NULL,' S_NAME 'varchar NOT NULL, `S_ADDRESS` varchar NOT NULL, `S_NATIONKEY` int NOT NULL, `S_PHONE` varchar NOT NULL, `S_ACCTBAL` decimal(12, 2) NOT NULL, `S_COMMENT` varchar NOT NULL, primary key (s_suppkey) ) DISTRIBUTE BY HASH(`s_suppkey`) INDEX_ALL='Y';Copy the codeDump OSS data to ADB
Step 1 Create a table
Address: dms.aliyun.com/
Path: All Functions – Data scheme – Data import
The initialization script is as follows (note: The script needs to be copied to a local directory for editing and saved as a text with the suffix of. SQL for submitting work orders) :
###1, CUSTOMER table ### number of records: 150W, storage space: 234M, import time: 1 minute INSERT INTO CUSTOMER (C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_MKTSEGMENT,C_COMMENT) SELECT C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_MKTSEGMENT,C_COMMENT FROM OSS_CUSTOMER; ### ### ### # number of records: 1500W, storage space: 1.6GB, import time: INSERT INTO ORDERS 10分钟 (O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT) SELECT O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT FROM OSS_ORDERS; ### number of records: 5999W, storage space: 7.24GB, import time: INSERT INTO LINEITEM (L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT ,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT) SELECT L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT ,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT FROM OSS_LINEITEM; ### ### ### ### ### ### # 1 SEC INSERT INTO NATION (N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT) SELECT N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT FROM OSS_NATION; Number of records: 200W, storage space: 232M, import time: 23秒 INSERT INTO PART (P_PARTKEY,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_SIZE,P_CONTAINER,P_RETAILPRICE,P_COMMENT) SELECT P_PARTKEY,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_SIZE,P_CONTAINER,P_RETAILPRICE,P_COMMENT FROM OSS_PART; Number of records: 800W, storage space: 1.12GB, import time: 3分钟 INSERT INTO PARTSUPP (PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_SUPPLYCOST,PS_COMMENT) SELECT PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_SUPPLYCOST,PS_COMMENT FROM OSS_PARTSUPP; ### ### ### ### ### ### ### ### 10 secs INSERT INTO REGION (R_REGIONKEY,R_NAME,R_COMMENT) SELECT R_REGIONKEY,R_NAME,R_COMMENT from OSS_REGION; SUPPLIER table ### number of records: 10W, storage space: 14M, import time: 10秒 INSERT INTO SUPPLIER (S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT) SELECT S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT FROM OSS_SUPPLIER;Copy the codeImport data validation
Address: dms.aliyun.com/ Path: All Functions -SQLConsole- Single database Query Select the data import result of the largest table to confirm. The expected value of the LINEITEM table is 5999W, which is consistent with the amount of imported data
select count(*) from LINEITEM;
Copy the code