乐趣区

关于mysql:使用AnalyticDB-MySQL创建数据库及表过程

jiezi

简介

指标是让云上数据仓库用户及开发者通过简略的步骤体验基于 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.co…

抉择已创立 ADB 实例所在的地区，并点击实例名称。

创立数据库

数据库库名可按需自定义。

地址：https://ads.console.aliyun.co…

抉择已创立 ADB 实例所在的地区，并点击实例名称。

创立 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/

门路：全副性能 - 数据计划 - 数据导入

初始化表构造语句如下：（注，脚本须要本地编辑替换相干信息后，保留为一个.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;
退出移动版