数仓版快速入门

更新时间:2025-02-13 06:14:03
重要

本文中含有需要您注意的重要提示信息,忽略该信息可能对您的业务造成影响,请务必仔细阅读。

欢迎使用云原生数据仓库 AnalyticDB MySQL 版数仓版入门指南。云原生数据仓库 AnalyticDB MySQL 版(AnalyticDB for MySQL)是云端托管的PB级高并发实时数据仓库,是专注于服务OLAP领域的数据仓库。本指南将指引您使用数仓版集群。

重要

数仓版目前已停止新购。您可以购买企业版基础版。如果您已购买数仓版集群,仍可以参考本指南继续使用数仓版集群。

数仓版快速入门视频指导

数仓版快速入门

使用流程

如果您是首次使用AnalyticDB for MySQL数仓版的用户,我们建议您先阅读以下部分:

  • 产品简介:本内容概述了AnalyticDB for MySQL的产品概念、产品优势及应用场景等内容。

  • 产品定价:本内容介绍了AnalyticDB for MySQL的产品定价、计费方式等信息。

  • 入门指南(本指南):本指南提供了有关AnalyticDB for MySQL数仓版的使用教程。

在本教程中,操作流程概览如下:

image

步骤一:创建数据库账号

AnalyticDB for MySQL支持高权限账号和普通账号这两种数据库账号,两种账号的区别,请参见数据库账号类型

创建高权限账号

  1. 登录云原生数据仓库AnalyticDB MySQL控制台
  2. 在页面左上角,选择集群所在地域。
  3. 在左侧导航栏,单击集群列表
  4. 在页签,单击目标集群ID。

  5. 在左侧导航栏,单击账号管理

  6. 账号管理页面,单击创建高权限账号

  7. 创建账号面板,设置相关参数。

    参数

    说明

    参数

    说明

    数据库账号

    高权限账号的账号名称,根据控制台提示输入符合要求的名称。

    账号类型

    数仓版集群固定为高权限账号,无需配置。

    新密码

    高权限账号的密码,根据控制台提示输入符合要求的账号密码。

    确认密码

    再次输入高权限账号的密码。

    描述

    备注该账号的相关信息,便于后续账号管理。可选。

  8. 单击确定完成账号创建。

创建和授权普通账号

集群通过SQL语句创建的普通账号,不会在控制台显示。

  • 创建数据库账号,请参见CREATE USER

  • 授权数据库账号,请参见GRANT

  • 撤销数据库账号权限,请参见REVOKE

  • 更改数据库账号名,请参见RENAME USER

  • 删除数据库账号,请参见DROP USER

步骤二:设置白名单

  • 集群默认的白名单只包含IP地址127.0.0.1,表示任何设备均无法访问该集群。您可以通过设置白名单允许其他设备访问集群,例如填写IP10.10.10.0/24,表示10.10.10.XIP地址都可以访问该集群。若您需要添加多个IP地址或IP段,请用英文逗号(,)隔开(逗号前后都不能有空格),例如192.168.0.1,172.16.213.9。

    警告

    设置白名单时,禁止输入IP:0.0.0.0。

  • 若您的公网IP经常变动,需要开放所有公网IP访问AnalyticDB for MySQL集群,请联系技术支持。

  • 白名单可以为AnalyticDB for MySQL集群得到高级别的访问安全保护,建议您定期维护白名单。

  • 设置白名单不会影响AnalyticDB for MySQL集群的正常运行。设置白名单后,新的白名单将于1分钟后生效。

操作步骤

  1. 登录云原生数据仓库AnalyticDB MySQL控制台
  2. 在页面左上角,选择集群所在地域。
  3. 在左侧导航栏,单击集群列表
  4. 数仓版页签中,单击目标集群ID

  5. 在左侧导航栏单击数据安全

  6. 白名单设置页面,单击default白名单分组右侧的修改

    说明

    您也可以单击创建白名单分组创建自定义分组。

  7. 修改白名单分组对话框中,删除默认IP 127.0.0.1,填写需要访问该集群的IP地址或IP段,然后单击确定

    说明

    如果需要将客户端出口IP地址添加到白名单中,请先查询IP地址,详情请参见连接

步骤三:连接集群

AnalyticDB for MySQL支持通过DMS(Data Management Service)、MySQL客户端(Navicat for MySQL、DBeaver、DBVisualizer、SQL WorkBench/J)、BI可视化工具、或者MySQL命令行工具连AnalyticDB for MySQL集群。您也可以在应用程序中通过配置集群连接地址、端口、数据库账号等信息连AnalyticDB for MySQL集群。

使用DMS连接AnalyticDB for MySQL

  1. 登录云原生数据仓库AnalyticDB MySQL控制台
  2. 在页面左上角,选择集群所在地域。
  3. 在左侧导航栏,单击集群列表
  4. 数仓版页签下,单击目标集群ID

  5. 集群信息页面,单击右上角登录数据库

  6. 在弹出的对话框中,填写登录信息。

    参数

    说明

    参数

    说明

    数据库类型

    默认为AnalyticDB MySQL 3.0,无需选择。

    实例地区

    默认为当前实例所在地域,无需选择。

    说明

    若您需要登录其他地域下的AnalyticDB for MySQL集群,从下拉列表中选择目标集群的所在地域即可。

    实例ID

    默认为当前集群的集群ID,无需选择。

    说明

    若您需要登录其他AnalyticDB for MySQL集群,从下拉列表中选择目标集群ID即可。

    数据库账号

    集群的账号名称。

    数据库密码

    账号名对应的密码。

    说明

    您可以选中记住密码,方便之后再次登录当前AnalyticDB for MySQL集群时,无需输入数据库账号和密码即可自动登录。

    说明
    • 首次通过DMS登录AnalyticDB for MySQL集群时,管控模式默认为自由操作。登录成功后,您还可以通过编辑实例功能来修改管控模式。更多信息,请参见编辑实例管控模式

    • 配置完登录参数后,您可以单击左下角测试连接。如果测试连接失败,请按照报错提示检查录入的集群信息,如账号或密码是否正确。

    • 系统会自动尝试往云数据库的白名单中添加DMS的服务器访问地址,若自动添加失败请手动添加。详情信息,请参见步骤二:设置白名单DMS白名单列表

  7. 单击登录即可。

应用开发中通过代码连接到AnalyticDB for MySQL

通过MySQL命令行工具连接到AnalyticDB for MySQL

MySQL命令行连接AnalyticDB for MySQL

通过客户端连接到AnalyticDB for MySQL

AnalyticDB for MySQL连接到数据可视化工具

步骤四:创建数据库

说明

每个集群可创建数据库的最大值为2048。

  1. SQL INFORMATION_SCHEMA页签下,在SQL Console中输入CREATE DATABASE语句创建数据库。

    SQL Console

    • 语法:CREATE DATABASE [IF NOT EXISTS] $db_name

    • 参数说明:db_name:数据库名。以小写字符开头,可包含字母、数字以及下划线(_),但不能包含连续两个及以上的下划线(_),长度不超过64个字符。

      说明

      数据库名不能是analyticdb,analyticdb是内置数据库。

    • 示例:

      create database adb_demo;                          
      create database if not exists adb_demo2;                         
  2. 单击左上角的执行,数据库创建成功。

    数据库创建成功

步骤五:导入数据并查询

前提条件

  • 通过以下步骤在OSS中创建存储AnalyticDB for MySQL数据的目录。

    1. 开通OSS服务。详情请参见开通OSS服务

    2. 创建存储空间。详情请参见控制台创建存储空间

      重要

      OSS的存储空间与AnalyticDB for MySQL所属地域相同。

    3. 创建目录。详情请参见创建目录

    4. 上传测试文件。详情请参见控制台上传文件

      本示例将oss_import_test_data.txt文件上传至OSS中的<bucket-name>.oss-cn-hangzhou.aliyuncs.com/adb/目录,数据行分隔符为换行符,列分隔符为;,文件示例数据如下所示:

      uid;other
      12;hello_world_1
      27;hello_world_2
      28;hello_world_3
      33;hello_world_4
      37;hello_world_5
      40;hello_world_6
      ...     
  • 根据AnalyticDB for MySQL入门指南,完成创建集群、设置白名单、创建账号和数据库等准备工作,详情请参见使用流程

操作步骤

  1. 通过CREATE TABLE,在adb_demo数据库中创建外表。创建CSV、ParquetTEXT格式OSS外表的建表语法请参见OSS外表语法

  2. 查询OSS数据。

    查询外表映射表和查询AnalyticDB for MySQL内表语法没有区别,您可以方便地直接进行查询,如本步骤的示例代码所示。

    select uid, other from oss_import_test_external_table where uid < 100 limit 10;
    • 对于数据量较大的CSVTEXT数据文件,强烈建议您按照后续步骤导入AnalyticDB for MySQL后再做查询,否则查询性能可能会较差。

    • 对于Parquet格式数据文件,直接查询的性能一般也比较高,您可以根据需要决定是否进一步导入到AnalyticDB for MySQL后再做查询。

  3. 通过CREATE TABLE,在adb_demo数据库中创建目标表adb_oss_import_test存储从OSS中导入的数据。

    CREATE TABLE IF NOT EXISTS adb_oss_import_test
    (
        uid string,
        other string
    )
    DISTRIBUTED BY HASH(uid);                  
  4. 执行INSERT语句将OSS外表数据导入AnalyticDB for MySQL

    重要

    使用INSERT INTOINSERT OVERWRITE SELECT导入数据时,默认是同步执行流程。如果数据量较大,达到几百GB,客户端到AnalyticDB for MySQL服务端的连接需要保持较长时间。在此期间,可能会因为网络因素导致连接中断,进而导致数据导入失败。因此,如果您的数据量较大时,推荐使用SUBMIT JOB INSERT OVERWRITE SELECT异步执行导入。

    • 方式一:执行INSERT INTO导入数据,当主键重复时会自动忽略当前写入数据,不进行更新覆盖,作用等同于INSERT IGNORE INTO,详情请参见INSERT INTO。示例如下:

      INSERT INTO adb_oss_import_test
      SELECT * FROM oss_import_test_external_table;
    • 方式二:执行INSERT OVERWRITE导入数据,会覆盖表中原有的数据。示例如下:

      INSERT OVERWRITE adb_oss_import_test
      SELECT * FROM oss_import_test_external_table;
    • 方式三:异步执行INSERT OVERWRITE导入数据。 通常使用SUBMIT JOB提交异步任务,由后台调度,可以在写入任务前增加Hint(/*+ direct_batch_load=true*/)加速写入任务。详情请参见异步写入。示例如下:

      SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
      SELECT * FROM oss_import_test_external_table;

      返回结果如下:

      +---------------------------------------+
      | job_id                                |
      +---------------------------------------+
      | 2020112122202917203100908203303****** |

      关于异步提交任务详情,请参见异步提交导入任务

  5. 执行以下命令,查询adb_oss_import_test表的数据。

    SELECT * FROM adb_oss_import_test;

OSS外表语法

OSS非分区外表

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"text|orc|parquet",
    "delimiter":";",
    "skip_header_line_count":1,
    "charset":"utf-8"
}';

外表类型

参数

是否必填

说明

外表类型

参数

是否必填

说明

CSV格式、Parquet格式或OSS ORC格式外表

ENGINE='OSS'

表引擎,固定填写为OSS。

endpoint

OSSEndPoint(地域节点)。 目前仅支持AnalyticDB for MySQL通过VPC网络访问OSS。

说明

您可登录OSS控制台,单击目标Bucket,在Bucket概览页面查看EndPoint(地域节点)

url

指定OSS文件或目录所在的路径。

  • OSS文件需填写绝对路径。例如:oss://testBucketname/adb/oss_import_test_data.csv

  • 目录路径以正斜线(/)结尾。例如:oss://testBucketname/adb/

    说明

    若指定为目录路径,成功创建外表后,外表中的数据为该文件夹下的所有数据。

  • 路径末尾支持通配符*,用于匹配该路径下所有符合该模式的文件或文件夹。例如:oss://testBucketname/adb/list_file_with_prefix/test*

    说明

    该模糊查询示例将匹配到满足前缀条件的所有文件和文件夹,例如:oss://testBucketname/adb/list_file_with_prefix/testfile1

    oss://testBucketname/adb/list_file_with_prefix/test1/file2

accessid

阿里云账号或者具备OSS管理权限的RAM用户的AccessKey ID。

如何获取AccessKey ID,请参见账号与权限

accesskey

阿里云账号或者具备OSS管理权限的RAM用户的AccessKey Secret。

如何获取AccessKey Secret,请参见账号与权限

CSV格式外表

delimiter

定义CSV数据文件的列分隔符。

Parquet格式、OSS ORC格式外表

format

数据文件的格式。

  • 创建Parquet格式文件的外表时需设置为parquet

  • 创建ORC格式文件的外表时需设置为orc

说明
  • 仅创建OSS Parquet格式或OSS ORC格式外表填写该参数。

  • 不指定format时,默认格式为CSV。

CSV格式外表

null_value

定义CSV数据文件的NULL值。默认将空值定义为NULL,即"null_value": ""

重要

仅内核版本为3.1.4.2及以上的集群支持配置该参数。

ossnull

选择CSV数据文件中NULL值的对应规则。取值如下:

  • 1(默认值):表示EMPTY_SEPARATORS,即仅将空值定义为NULL

    示例:a,"",,c --> "a","",NULL,"c"

  • 2:表示EMPTY_QUOTES,即仅将""定义为NULL

    示例:a,"",,c --> "a",NULL,"","c"

  • 3:表示BOTH,即同时将空值和""定义为NULL

    示例:a,"",,c --> "a",NULL,NULL,"c"

  • 4:表示NEITHER,即空值和""均不定义为NULL

    示例:a,"",,c --> "a","","","c"

说明

上述各示例的前提为"null_value": ""

skip_header_line_count

定义导入数据时需要在开头跳过的行数。CSV文件第一行为表头,若设置该参数为1,导入数据时可自动跳过第一行的表头。

默认取值为0,即不跳过。

oss_ignore_quote_and_escape

是否忽略字段值中的引号和转义。默认取值为false,即不忽略字段值中的引号和转义。

重要

仅内核版本为3.1.4.2及以上的集群支持配置该参数。

charset

OSS外表字符集,取值说明:

  • utf-8(默认值)

  • gbk

重要

仅内核版本为3.1.10.4及以上的集群支持配置该参数。

说明
  • 外表创建语句中的列名需与ParquetORC文件中该列的名称完全相同(可忽略大小写),且列的顺序需要一致。

  • 创建外表时,可以仅选择ParquetORC文件中的部分列作为外表中的列,未被选择的列不会被导入。

  • 如果创建外表创建语句中出现了ParquetORC文件中不存在的列,针对该列的查询结果均会返回NULL。

Parquet文件、ORC文件与AnalyticDB for MySQL的数据类型映射关系
Parquet文件与AnalyticDB for MySQL的数据类型映射关系
ORC文件与AnalyticDB for MySQL的数据类型映射关系

Parquet基本类型

ParquetlogicalType类型

AnalyticDB for MySQL的数据类型

BOOLEAN

BOOLEAN

INT32

INT_8

TINYINT

INT32

INT_16

SMALLINT

INT32

INTINTEGER

INT64

BIGINT

FLOAT

FLOAT

DOUBLE

DOUBLE

  • FIXED_LEN_BYTE_ARRAY

  • BINARY

  • INT64

  • INT32

DECIMAL

DECIMAL

BINARY

UTF-8

  • VARCHAR

  • STRING

  • JSON(如果已知Parquet该列内容为JSON格式)

INT32

DATE

DATE

INT64

TIMESTAMP_MILLIS

TIMESTAMPDATETIME

INT96

TIMESTAMPDATETIME

重要

Parquet格式外表暂不支持STRUCT类型,会导致建表失败。

ORC文件中的数据类型

AnalyticDB for MySQL中的数据类型

BOOLEAN

BOOLEAN

BYTE

TINYINT

SHORT

SMALLINT

INT

INTINTEGER

LONG

BIGINT

DECIMAL

DECIMAL

FLOAT

FLOAT

DOUBLE

DOUBLE

  • BINARY

  • STRING

  • VARCHAR

  • VARCHAR

  • STRING

  • JSON(如果已知ORC该列内容为JSON格式)

TIMESTAMP

TIMESTAMPDATETIME

DATE

DATE

重要

ORC格式外表暂不支持LISTSTRUCTUNION等复合类型,会导致建表失败。ORC格式外表的列使用MAP类型可以建表,但ORC的查询会失败。

AnalyticDB for MySQL支持通过OSSCSV格式的外表读写Hive TEXT文件。建表语句如下:

CREATE TABLE adb_csv_hive_format_oss (
  a tinyint,
  b smallint,
  c int,
  d bigint,
  e boolean,
  f float,
  g double,
  h varchar,
  i varchar, -- binary
  j timestamp,
  k DECIMAL(10, 4),
  l varchar, -- char(10)
  m varchar, -- varchar(100)
  n date
) ENGINE = 'OSS' TABLE_PROPERTIES='{
    "format": "csv",
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "accessid":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';
说明

在创建OSSCSV格式的外表来读取Hive TEXT文件时,需注意如下几点:

  • Hive TEXT文件的默认列分隔符为\1。若您需要通过OSSCSV格式的外表读写Hive TEXT文件,您可以在配置delimiter参数时将其转义为\\1

  • Hive TEXT文件的默认NULL值为\N。若您需要通过OSSCSV格式的外表读写Hive TEXT文件,您可以在配置null_value参数时将其转义为 \\\\N

  • Hive的其他基本数据类型(如BOOLEAN)与AnalyticDB for MySQL的数据类型一一对应,但BINARYCHAR(n)VARCHAR(n)类型均对应AnalyticDB for MySQL中的VARCHAR类型。

OSS分区外表

如果OSS数据源是包含分区的,会在OSS上形成一个分层目录,类似如下内容:

parquet_partition_classic/
├── p1=2020-01-01
│ ├── p2=4
│ │ ├── p3=SHANGHAI
│ │ │ ├── 000000_0
│ │ │ └── 000000_1
│ │ └── p3=SHENZHEN
│ │     └── 000000_0
│ └── p2=6
│     └── p3=SHENZHEN
│         └── 000000_0
├── p1=2020-01-02
│ └── p2=8
│     ├── p3=SHANGHAI
│     │ └── 000000_0
│     └── p3=SHENZHEN
│         └── 000000_0
└── p1=2020-01-03
    └── p2=6
        ├── p2=HANGZHOU
        └── p3=SHENZHEN
            └── 000000_0

上述数据中p1为第1级分区,p2为第2级分区,p3为第3级分区。对应这种数据源,一般都希望以分区的模式进行查询,那么就需要在创建OSS外表时指明分区列。以Parquet格式为例,创建带有分区的OSS外表的语句如下:

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"parquet",
    "partition_column":"p1, p2, p3"
}';
说明
  • TABLE_PROPERTIES中的partition_column参数用于指定分区列(本例中的p1、p2、p3)。且partition_column参数中的分区列必须按照第1级、第2级、第3级的顺序声明(本例中p1为第1级分区,p2为第2级分区,p3为第3级分区)。

  • 列定义中必须定义分区列(本例中的p1、p2、p3)及类型,且分区列需要置于列定义的末尾。

  • 列定义中分区列的先后顺序需要与partition_column中分区列的顺序保持一致。

  • 分区列支持的数据类型包括:BOOLEANTINYINTSMALLINTINTINTEGERBIGINTFLOATDOUBLEDECIMALVARCHARSTRINGDATETIMESTAMP

  • 查询数据时,分区列和其它数据列的展示和用法没有区别。

  • 不指定format时,默认格式为CSV。

  • 其他参数的详细说明,请参见参数说明

相关文档

更多导入数据方式,请参见支持的数据源

  • 本页导读 (1)
  • 数仓版快速入门视频指导
  • 使用流程
  • 步骤一:创建数据库账号
  • 创建高权限账号
  • 创建和授权普通账号
  • 步骤二:设置白名单
  • 操作步骤
  • 步骤三:连接集群
  • 使用DMS连接AnalyticDB for MySQL
  • 应用开发中通过代码连接到AnalyticDB for MySQL
  • 通过MySQL命令行工具连接到AnalyticDB for MySQL
  • 通过客户端连接到AnalyticDB for MySQL
  • 将AnalyticDB for MySQL连接到数据可视化工具
  • 步骤四:创建数据库
  • 步骤五:导入数据并查询
  • 前提条件
  • 操作步骤
  • OSS外表语法
  • 相关文档
AI助理

点击开启售前

在线咨询服务

你好,我是AI助理

可以解答问题、推荐解决方案等