## 簡介

目標是讓雲上資料倉儲使用者及開發者透過簡單的步驟體驗基於AnalyticDB MySQL版和DMS構建雲原生資料倉儲的主要流程，場景將透過例項的開通、結構與資料的初始化、報表的開發、報表視覺化等環節，用3個具體的應用場景來體驗AnalyticDB MySQL版在新零售場景下的互動查詢和ETL計算速度，以及透過DMS進行資料倉儲資料包表開發的流程。

提供的資料集是一個零售場景的模擬資料，包括客戶資訊、訂單記錄、貨物資訊、國家地域資訊等內容，資料總量10GB，最大資料表記錄數為5999萬條。

## 產品簡介

雲原生資料倉儲AnalyticDB MySQL版是一種支援高併發低延時查詢的新一代雲原生資料倉儲，高度相容MySQL協議以及SQL:2003 語法標準，可以對海量資料進行即時的多維分析透視和業務探索，快速構建企業雲上資料倉儲。

資料管理DMS是基於阿里巴巴集團十餘年的資料庫服務平臺的雲版本，提供免安裝、免運維、即開即用、多種資料庫型別與多種環境統一的web資料庫管理終端；可以為企業使用者快速複製搭建與阿里集團同等安全、高效、規範的資料庫DevOps研發流程、數倉開發解決方案。

## 建立高許可權賬號

ADB叢集詳情頁，按需自定義高許可權的賬號與密碼

每個叢集僅限1個高許可權賬號，可修改密碼；
 後續可透過DMS新建普通賬號（ADB例項在DMS上直接以免費的自由操作管控模式使用即可）
地址：https://ads.console.aliyun.com/adb/cn-shenzhen/instances

選擇已建立ADB例項所在的地域，並點選例項名稱。

## 建立資料庫

資料庫庫名可按需自定義。

地址：https://ads.console.aliyun.com/adb/cn-shenzhen/instances

選擇已建立ADB例項所在的地域，並點選例項名稱。

![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20210420161051702.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dhbmd5aWtlamk=,size_16,color_FFFFFF,t_70)

## 建立OSS外表
說明：

下列建表語句需要根據ADB的地域分別替換endpoint和URL引數中的字串。例如ADB在北京區域。
需要將endpoint ”oss-cn-shenzhen-internal.aliyuncs.com“ 替換成”oss-cn-beijing-internal.aliyuncs.com“，
需要將URL中的”oss://adb-tpch-shenzhen“ 替換成”oss://adb-tpch-beijing“
各個地域替換的程式碼：

深圳：shenzhen
北京：beijing
杭州：hangzhou
上海：shanghai
張家口：zhangjiakou
注意：下列建表語句需要根據您雲賬號的accessid、accesskey 替換後方執行。

地址：https://dms.aliyun.com/

路徑：全部功能-資料方案-資料匯入
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20210420161122724.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dhbmd5aWtlamk=,size_16,color_FFFFFF,t_70)
初始化表結構語句如下：（注，指令碼需要本地編輯替換相關資訊後，儲存為一個.sql字尾的文字用於工單的提交）

```
####1、CUSTOMER 外表

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外表

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外表

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"
}';
```

## 建立ADB表
建立ADB表

步驟同OSS外表建立

地址：https://dms.aliyun.com/
路徑：全部功能-資料方案-資料匯入
初始化表結構語句如下：（注，指令碼需要複製到本地編輯，儲存為一個.sql字尾的文字用於工單的提交）

```
#### 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表

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表

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

## 轉儲OSS的資料到ADB中

步驟同建表操作

地址：https://dms.aliyun.com/

路徑：全部功能-資料方案-資料匯入

初始化指令碼如下（注，指令碼需要複製到本地編輯，儲存為一個.sql字尾的文字用於工單的提交）：

```
###1、CUSTOMER表  
### 記錄數：150W，儲存空間：234M，匯入耗時：1分鐘

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;

###2、ORDERS表   
### 記錄數：1500W，儲存空間：1.6GB，匯入耗時：10分鐘

INSERT INTO ORDERS
(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;

###3、LINEITEM表   
### 記錄數：5999W，儲存空間：7.24GB，匯入耗時：40分鐘

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;

###4、NATION表  
### 記錄數：25，儲存空間：2KB，匯入耗時：1秒

INSERT INTO NATION
(N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT)
SELECT N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT  FROM OSS_NATION;

###5、PART表  
### 記錄數：200W，儲存空間：232M，匯入耗時：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;

###6、PARTSUPP表  
### 記錄數：800W，儲存空間：1.12GB，匯入耗時：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;

###7、REGION表  
### 記錄數：5，儲存空間：0.38KB，匯入耗時：10秒

INSERT INTO REGION
(R_REGIONKEY,R_NAME,R_COMMENT)
SELECT R_REGIONKEY,R_NAME,R_COMMENT from OSS_REGION;

###8、SUPPLIER表  
### 記錄數：10W，儲存空間：14M，匯入耗時：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;
```

## 匯入資料驗證
地址：https://dms.aliyun.com/
路徑：全部功能-SQLConsole-單庫查詢
此處選最大的1個表的資料匯入結果進行確認，LINEITEM表得到預期數值與匯入資料量一致，為5999W

```
select count(*) from LINEITEM;
```

使用AnalyticDB MySQL建立資料庫及表過程

相關文章