全部產品
Search
文件中心

AnalyticDB:數倉版快速入門

更新時間:Feb 14, 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控制台
  2. 在頁面左上方,選擇叢集所在地區。
  3. 在左側導覽列,單擊集群清單
  4. 在頁簽,單擊目的地組群ID。

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

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

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

    參數

    說明

    数据库账号

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

    账号类型

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

    新密碼

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

    确认密码

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

    描述

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

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

建立和授權普通帳號

叢集通過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控制台
  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、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。

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

相關文檔

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