全部產品
Search
文件中心

AnalyticDB:數倉版快速入門

更新時間:Feb 21, 2025

歡迎使用AnalyticDB for MySQL數倉版入門指南。AnalyticDB for MySQL是雲端託管的PB級高並發即時資料倉庫,是專註於服務OLAP領域的資料倉儲。本指南將指引您使用數倉版叢集。

重要

數倉版目前已停止新購。您可以購買企業版基礎版。如果您已購買數倉版叢集,仍可以參考本指南繼續使用數倉版叢集。

使用流程

如果您是首次使用AnalyticDB for MySQL數倉版的使用者,我們建議您先閱讀以下部分:

  • 產品簡介:本內容概述了AnalyticDB for MySQL的產品概念、產品優勢及應用情境等內容。

  • 產品定價:本內容介紹了AnalyticDB for MySQL的產品定價、計費方式等資訊。

  • 入門指南(本指南):本指南提供了有關AnalyticDB for MySQL數倉版的使用教程。

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

步驟一:建立資料庫帳號

AnalyticDB for MySQL支援高許可權帳號和普通帳號這兩種資料庫帳號,兩種帳號的區別,請參見資料庫帳號類型

建立高許可權帳號

  1. 登入雲原生資料倉儲AnalyticDB MySQL控制台,在左上方選擇叢集所在地區。在左側導覽列,單擊集群清單。在集群清單上方,選擇產品系列,然後單擊目的地組群ID。

  2. 在左側導覽列,單擊帳號管理

  3. 帳號管理頁面,單擊建立高許可權帳號

  4. 創建賬號面板,設定相關參數。

    參數

    說明

    数据库账号

    高許可權帳號的帳號名稱,根據控制台提示輸入符合要求的名稱。

    账号类型

    數倉版叢集固定為高許可權帳號,無需配置。

    新密碼

    高許可權帳號的密碼,根據控制台提示輸入符合要求的帳號密碼。

    确认密码

    再次輸入高許可權帳號的密碼。

    描述

    備忘該帳號的相關資訊,便於後續帳號管理。可選。

  5. 單擊確定完成帳號建立。

建立和授權普通帳號

叢集通過SQL語句建立的普通帳號,不會在控制台顯示。

  • 建立資料庫帳號,請參見CREATE USER

  • 授權資料庫帳號,請參見GRANT

  • 撤銷資料庫帳號許可權,請參見REVOKE

  • 更改資料庫帳號名,請參見RENAME USER

  • 刪除資料庫帳號,請參見DROP USER

步驟二:設定白名單

  • 叢集預設的白名單只包含IP地址127.0.0.1,表示任何裝置均無法訪問該叢集。您可以通過設定白名單允許其他裝置訪問叢集,例如填寫IP段10.10.10.0/24,表示10.10.10.X的IP地址都可以訪問該叢集。若您需要添加多個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控制台,在左上方選擇叢集所在地區。在左側導覽列,單擊集群清單。在集群清單上方,選擇產品系列,然後單擊目的地組群ID。

  2. 在左側導覽列單擊資料安全

  3. 白名单设置頁面,單擊default白名單分組右側的修改

    說明

    您也可以單擊建立白名單群組建立自訂分組。

  4. 修改白名单分组對話方塊中,刪除預設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控制台,在左上方選擇叢集所在地區。在左側導覽列,單擊集群清單。在集群清單上方,選擇產品系列,然後單擊目的地組群ID。

  2. 集群資訊頁面,單擊右上方登入資料庫

  3. 在彈出的對話方塊中,填寫登入資訊。

    參數

    說明

    資料庫類型

    預設為AnalyticDB MySQL 3.0,無需選擇。

    執行個體地區

    預設為當前執行個體所在地區,無需選擇。

    說明

    若您需要登入其他地區下的AnalyticDB for MySQL叢集,從下拉式清單中選擇目的地組群的所在地區即可。

    執行個體ID

    預設為當前叢集的叢集ID,無需選擇。

    說明

    若您需要登入其他AnalyticDB for MySQL叢集,從下拉式清單中選擇目的地組群ID即可。

    資料庫帳號

    叢集的帳號名稱。

    資料庫密碼

    帳號名對應的密碼。

    說明

    您可以選中記住密碼,方便之後再次登入當前AnalyticDB for MySQL叢集時,無需輸入資料庫帳號和密碼即可自動登入。

    說明
    • 首次通過DMS登入AnalyticDB for MySQL叢集時,管控模式預設為自由操作。登入成功後,您還可以通過編輯執行個體功能來修改管控模式。更多資訊,請參見編輯執行個體管控模式

    • 配置完登入參數後,您可以單擊左下角測試連接。如果測試連接失敗,請按照報錯提示檢查錄入的叢集資訊,如帳號或密碼是否正確。

    • 系統會自動嘗試往雲資料庫的白名單中添加DMS的伺服器訪問地址,若自動添加失敗請手動添加。詳情資訊,請參見步驟二:設定白名單DMS白名單列表

  4. 單擊登入即可。

應用開發中通過代碼串連到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、Parquet或TEXT格式OSS外表的建表文法請參見OSS外表文法

  2. 查詢OSS資料。

    查詢外表映射表和查詢AnalyticDB for MySQL內表文法沒有區別,您可以方便地直接進行查詢,如本步驟的範例程式碼所示。

    select uid, other from oss_import_test_external_table where uid < 100 limit 10;
    • 對於資料量較大的CSV或TEXT資料檔案,強烈建議您按照後續步驟匯入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

OSS的EndPoint(地區節點)。 目前僅支援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及以上的叢集支援配置該參數。

說明
  • 外表建立語句中的列名需與Parquet或ORC檔案中該列的名稱完全相同(可忽略大小寫),且列的順序需要一致。

  • 建立外表時,可以僅選擇Parquet或ORC檔案中的部分列作為外表中的列,未被選擇的列不會被匯入。

  • 如果建立外表建立語句中出現了Parquet或ORC檔案中不存在的列,針對該列的查詢結果均會返回NULL。

Parquet檔案、ORC檔案與AnalyticDB for MySQL的資料類型映射關係
Parquet檔案與AnalyticDB for MySQL的資料類型映射關係

Parquet基本類型

Parquet的logicalType類型

AnalyticDB for MySQL的資料類型

BOOLEAN

BOOLEAN

INT32

INT_8

TINYINT

INT32

INT_16

SMALLINT

INT32

INT或INTEGER

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

TIMESTAMP或DATETIME

INT96

TIMESTAMP或DATETIME

重要

Parquet格式外表暫不支援STRUCT類型,會導致建表失敗。

ORC檔案與AnalyticDB for MySQL的資料類型映射關係

ORC檔案中的資料類型

AnalyticDB for MySQL中的資料類型

BOOLEAN

BOOLEAN

BYTE

TINYINT

SHORT

SMALLINT

INT

INT或INTEGER

LONG

BIGINT

DECIMAL

DECIMAL

FLOAT

FLOAT

DOUBLE

DOUBLE

  • BINARY

  • STRING

  • VARCHAR

  • VARCHAR

  • STRING

  • JSON(如果已知ORC該列內容為JSON格式)

TIMESTAMP

TIMESTAMP或DATETIME

DATE

DATE

重要

ORC格式外表暫不支援LISTSTRUCTUNION等複合類型,會導致建表失敗。ORC格式外表的列使用MAP類型可以建表,但ORC的查詢會失敗。

AnalyticDB for MySQL支援通過OSS的CSV格式的外表讀寫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
}';
說明

在建立OSS的CSV格式的外表來讀取Hive TEXT檔案時,需注意如下幾點:

  • Hive TEXT檔案的預設資料行分隔符號為\1。若您需要通過OSS的CSV格式的外表讀寫Hive TEXT檔案,您可以在配置delimiter參數時將其轉義為\\1

  • Hive TEXT檔案的預設NULL值為\N。若您需要通過OSS的CSV格式的外表讀寫Hive TEXT檔案,您可以在配置null_value參數時將其轉義為 \\\\N

  • Hive的其他基礎資料型別 (Elementary Data Type)(如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。

  • 其他參數的詳細說明,請參見參數說明

相關文檔

更多匯入資料方式,請參見支援的資料來源