开发者学堂课程【数据仓库 ACP 认证课程:【视频】云原生数据仓库 Analyticdb MYSQL 版-解析与实践-3】学习笔记,与课程紧密联系,让用户快速学习知识。
课程地址:https://developer.aliyun.com/learning/course/928/detail/14625
【视频】云原生数据仓库 Analyticdb MYSQL 版-解析与实践-3
前言
上节课介绍云原生数据仓库analyticDB MySQL版的一些基本概念、操作等基础之上,本节课进行继续介绍。
内容介绍
一、数据导入导出与同步链路搭建
二、SQL优化与慢查询解决
三、数据更新
四、演示
五、试题
六、回顾与总结
一、数据导入导出与同步链路搭建
1. 导入方案
众所周知,Ananylyticdb mySQ不生产数据,其数据大多数来自业务数据库及生产数据库,通过ETL过程导入到数据中,所以数据的导入导出是数据库的一项最基本的功能。同时针对阿里云的系列产品,可以通过数据传入服务来实现同步链路搭建。
Ananylyticdb mySQL版提供多种导入方案,可满足不同场景下的数据导入需求
数据库 |
RDS MYSQL ;POLAR DB-X (原DRDS);POLARDB MYSQL ;ORACE ;SQL SERVER |
Oss |
INSERT外表;通过外表导入oss数据 Dataworks数据工厂:配置oss数据源 |
大数据 Max computer |
INSERT外表;通过INSERT外表方式导入MAx compute数据 Dataworks:配置max computer数据源 Hadoop:flink |
消息队列 kafka |
使用logstash插件:使用logstash将kafka数据写入analyticdbmySQL Dataworks:配置Kafka读取 |
日志类数据 |
SLS;将SLS数据投递到analyticdb MYSQL Dataworks 配置sls读取 日志数据使用logstash插件:使用实时采集日志数据 |
本地数据 |
Load DATA 使用LOAD DATA导入不同场景下的数据导入需求 Dataworks:现将数据导入oss或FTP,在使用oss读取或者FTP读取的方式导入 导入工具:使用AnalyticDBmyqsl版导入工具导入本地数据 |
其中:
Dataworks是阿里云当中非常重要的一个数据加工工具,通过它,可以开发设计应用一系列的数据处理工具。
大数据Max computer,也是阿里云当中针对更大数据集的批量处理的一个产品。
2. DML 本地数据导入
步骤
1. 本地客户端
配置my.cnf配置文件,开启local-file功能(可以读取本地文件)
2. AnalyticDBmyqsl创建目标表(也就是导入的数据能够放置到哪个表内)
3. DML命令导入
以下是LOAD DATA的代码(其中有些是非必须的)
LOAD DATA LOC AL
INFLE “FILE-name’
//对应的文件名从那个文件导入数据
【REPLACE\IGNROE]
INTO TABLE TABLE-NAMES
//作为创建的目标表
【{FELDS\COLUMNS]
【terminated / BY‘ string’】
//需要制定数列中的分隔符,比如逗号,竖杠等等
{(opTIDNART } ENCLOSED BY CHAR}}
【lines}
【terminated / BY STRING】
{,column-name-or- user-war }...0】
通过LOAD 实现本地数据的导入
3. DML 外表式数据导入导出
(1)说明讲解
通过AnalyticDB MySQL映射表进行数据导入导出操作。支持oss、 mysol 、max compute,所有外表为映射表,创建在analyticdb数据库中,所有外表指向数据源。
说明:
通过RDS进行产品数据的导入时,RDSMySQL与AnalyticDB必须在同一个vpc下,此时ADB可以通过VPC的支持访问MySQL也可以是ECS自建MySQL ,此外OSS与AnalyticDB所属区域必须相同。
(2)通过DML外表是数据导入导出-创建外部表:
①首先在AnalyticDB MySQL中创建adb-demo数据库,并在数据库中创建一个外部映射表,简称外表courses-extenal-table,表的名字根据自己的业务需求创建。
CREATE TABLE IF NOT EXISTS courses-extenal-table
//如果表格不存在,就创建表
Id bigint not null
Name varchar (32)NOT NULL
Grade varchar (32)NOT NULL
Sumbmission-date TIMESTEAMP NOT NULL
)
ENGINE=’mysql’
TABLE_PROPERTIES=’{
“url”:“jdbc:mysql://mysql-vpc-address:3306/test_adb”
“tablename”:”courses”,
“username”:”mysql-user-name”
“password”:”mysql-user-password”
}’
//表的主键为id
②参数说明:
ENGINE=’MYSQL’
——表示该表是外部表,使用存储引擎为MySQL
若是创建普通表和维表是直接在 mysql创建,不包含ENGINE=格式
TABLE_PROPERTIES
——用于告知AnalyticDB如何访问mysql中的数据
通过该语句即可访问到所需外部数据
Url
——mysql中的内网地址,即VPC链接地址,地址格式为:
“jdbc:mysql://mysql-vpc-address:3306/test_adb”
tablename所需要的表,
username&password
——mysql中访问数据库的用户名和密码
如果有开发的经验只需访问MySQL即可访问到对应的数据库
(3)DML外表使数据导入导出MySQL
通过AnalyticDB MySQL映射表进行数据导入导出操作。
导入实际案例,完成该过程需要三个步骤:
①前提准备:首先做好准备工作,完成实例的创建,设置白名单、创建账号和数据库、创建表。示例:分别在两个数据库中创建表,导入导出数据的表。
首先,先在ids创建一张表,
CREATE TABLE rds courses{
Id bigint NOT NULL,
Name varchar(32)NOT NULL,
PRIMARY KEY(id)
然后在adb当中也创建一张表,
CREATE TABLE adb courses{
Id bigint AUTO INCREMENT,
Name varchar NOT NULL,
}DISTRIBUTE BY HASH(ID)}
前提:已经完成集群、实例的创建,设置白名单、创建账号和数据库。
之后登录AnalyticDB mySQL 创建需导入导出数据的表的映射表
创建一张表与上述两张表结构相同,表中存在引擎的说明
ENGINE “mysql”访问表的名称
“url”“:”Jdbc,mysql://mysql.Vpc.address:3306/xxx-db”
以及相关计算引擎的信息,对应的数据库的那张表,对应的用户名,密码。代码如下:
CREATA TABLE IF NOT EXISTS courses_external_table(
Id bigint NOT NULL,
Name varchar(32)NOT NULL,
PRIMARY KEY(id)
)
ENGINE=’mysql’
TABLE_PROPERTIES=’{
“url”:“jdbc:mysql://mysql-vpc-address:3306/test_adb”
“tablename”:”courses”,
“username”:”mysql-user-name”
“password”:”mysql-user-password”
}’
根据这些信息,保证我们可以访问到对应表中对应数据的信息。
外部表创建完成后,通过insert into语句实现数据的导入导出
存在两条insert into语句,注意语句表的名称。
insert into courses-external-table
Select*from adb-courses
,-AnyalyticDB导入到Rds
从adb中取得数据,添加到外物表中,对于AnyalyticDB而言实现数据导出的功能AnyalyticDB导出到rds中
如果向实现rds导入到AnyalyticDB,则更换语句
insert into adb-courses
Select*from courses-external-table
即可实现rds导入到AnyalyticDB
4. DML 外表式数据导入导出-Maxcomputer
步骤:
(1)首先确定目标和源表
Maxcompute:数据表
AnalyticDB:数据表
AnyalyticDB和maxcomputer 两者之间的数据的导入导出
(2)创建映射表
AnalyticDB中创建该数据表的映射表
(3)DML命令导出
通过INSERT INTO或者INSERT overwrite来实现数据的导入
Maxcomputer是阿里云产品中的一款大数据计算产品,支持海量数据的批处理计算
参数定义
ENGINE=”ODPS” 存储引擎为odps
ENGINE= ’ODPS’---储存引擎是 MaxCompute
TABLE_PROPERTIES=’{
“endpoint" :"http://service.cn.maxcompute.aliyun-inc.com/api",
---MaxCompute的EndPoint (域名节点)
“accessid”: "LTAIF*****5FsE・,
---访问 MaxCompute的AccessKey ID
“AccessKey”:“Ccw****iWjv*“,
---对应的ID 的Access key Secret
“project_name”"odps-project1”,
---MaxCompute中的工作空间名称
“table_name”: *xxxx_xxxx"
---MaxCompute中的数据表名称
说明:目前仅支持analyticDB MYSQL 通过maxcomputer的vpc网络endpoint访问maxcomputer