歡迎使用AnalyticDB for MySQL數倉版入門指南。AnalyticDB for MySQL是雲端託管的PB級高並發即時資料倉庫,是專註於服務OLAP領域的資料倉儲。本指南將指引您使用數倉版叢集。
數倉版目前已停止新購。您可以購買企業版或基礎版。如果您已購買數倉版叢集,仍可以參考本指南繼續使用數倉版叢集。
使用流程
如果您是首次使用AnalyticDB for MySQL數倉版的使用者,我們建議您先閱讀以下部分:
產品簡介:本內容概述了AnalyticDB for MySQL的產品概念、產品優勢及應用情境等內容。
產品定價:本內容介紹了AnalyticDB for MySQL的產品定價、計費方式等資訊。
入門指南(本指南):本指南提供了有關AnalyticDB for MySQL數倉版的使用教程。
在本教程中,操作流程概覽如下:
步驟一:建立資料庫帳號
AnalyticDB for MySQL支援高許可權帳號和普通帳號這兩種資料庫帳號,兩種帳號的區別,請參見資料庫帳號類型。
建立高許可權帳號
- 登入雲原生資料倉儲AnalyticDB MySQL控制台。
- 在頁面左上方,選擇叢集所在地區。
- 在左側導覽列,單擊集群清單。
在頁簽,單擊目的地組群ID。
在左側導覽列,單擊帳號管理。
在帳號管理頁面,單擊建立高許可權帳號。
在創建賬號面板,設定相關參數。
參數
說明
数据库账号
高許可權帳號的帳號名稱,根據控制台提示輸入符合要求的名稱。
账号类型
數倉版叢集固定為高許可權帳號,無需配置。
新密碼
高許可權帳號的密碼,根據控制台提示輸入符合要求的帳號密碼。
确认密码
再次輸入高許可權帳號的密碼。
描述
備忘該帳號的相關資訊,便於後續帳號管理。可選。
單擊確定完成帳號建立。
建立和授權普通帳號
叢集通過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分鐘後生效。
操作步驟
- 登入雲原生資料倉儲AnalyticDB MySQL控制台。
- 在頁面左上方,選擇叢集所在地區。
- 在左側導覽列,單擊集群清單。
在數倉版頁簽中,單擊目標集群ID。
在左側導覽列單擊資料安全。
在白名单设置頁面,單擊default白名單分組右側的修改。
說明您也可以單擊建立白名單群組建立自訂分組。
在修改白名单分组對話方塊中,刪除預設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
- 登入雲原生資料倉儲AnalyticDB MySQL控制台。
- 在頁面左上方,選擇叢集所在地區。
- 在左側導覽列,單擊集群清單。
在數倉版頁簽下,單擊目標集群ID。
在集群資訊頁面,單擊右上方登入資料庫。
在彈出的對話方塊中,填寫登入資訊。
參數
說明
資料庫類型
預設為AnalyticDB MySQL 3.0,無需選擇。
執行個體地區
預設為當前執行個體所在地區,無需選擇。
說明若您需要登入其他地區下的AnalyticDB for MySQL叢集,從下拉式清單中選擇目的地組群的所在地區即可。
執行個體ID
預設為當前叢集的叢集ID,無需選擇。
說明若您需要登入其他AnalyticDB for MySQL叢集,從下拉式清單中選擇目的地組群ID即可。
資料庫帳號
叢集的帳號名稱。
資料庫密碼
帳號名對應的密碼。
說明您可以選中記住密碼,方便之後再次登入當前AnalyticDB for MySQL叢集時,無需輸入資料庫帳號和密碼即可自動登入。
單擊登入即可。
應用開發中通過代碼串連到AnalyticDB for MySQL
通過MySQL命令列工具串連到AnalyticDB for MySQL
通過用戶端串連到AnalyticDB for MySQL
將AnalyticDB for MySQL串連到資料視覺化工具
步驟四:建立資料庫
每個叢集可建立資料庫的最大值為2048。
在SQL INFORMATION_SCHEMA頁簽下,在SQL Console中輸入CREATE DATABASE語句建立資料庫。
文法:
CREATE DATABASE [IF NOT EXISTS] $db_name
參數說明:
db_name
:資料庫名。以小寫字元開頭,可包含字母、數字以及底線(_),但不能包含連續兩個及以上的底線(_),長度不超過64個字元。說明資料庫名不能是analyticdb,analyticdb是內建資料庫。
樣本:
create database adb_demo;
create database if not exists adb_demo2;
單擊左上方的執行,資料庫建立成功。
步驟五:匯入資料並查詢
前提條件
通過以下步驟在OSS中建立儲存AnalyticDB for MySQL資料的目錄。
開通OSS服務。詳情請參見開通OSS服務。
建立儲存空間。詳情請參見控制台建立儲存空間。
重要OSS的儲存空間與AnalyticDB for MySQL所屬地區相同。
建立目錄。詳情請參見建立目錄。
上傳測試檔案。詳情請參見控制台上傳檔案。
本樣本將
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入門指南,完成建立叢集、設定白名單、建立帳號和資料庫等準備工作,詳情請參見使用流程。
操作步驟
通過CREATE TABLE,在
adb_demo
資料庫中建立外表。建立CSV、Parquet或TEXT格式OSS外表的建表文法請參見OSS外表文法。查詢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後再做查詢。
通過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);
執行INSERT語句將OSS外表資料匯入AnalyticDB for MySQL。
重要使用
INSERT INTO
或INSERT 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****** |
關於非同步提交任務詳情,請參見非同步提交匯入任務。
執行以下命令,查詢
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檔案或目錄所在的路徑。
| ||
accessid | 阿里雲帳號或者具備OSS系統管理權限的RAM使用者的AccessKey ID。 如何擷取AccessKey ID,請參見帳號與許可權。 | ||
accesskey | 阿里雲帳號或者具備OSS系統管理權限的RAM使用者的AccessKey Secret。 如何擷取AccessKey Secret,請參見帳號與許可權。 | ||
CSV格式外表 | delimiter | 定義CSV資料檔案的資料行分隔符號。 | |
Parquet格式、OSS ORC格式外表 | format | 資料檔案的格式。
說明
| |
CSV格式外表 | null_value | 否 | 定義CSV資料檔案的 重要 僅核心版本為3.1.4.2及以上的叢集支援配置該參數。 |
ossnull | 選擇CSV資料檔案中
說明 上述各樣本的前提為 | ||
skip_header_line_count | 定義匯入資料時需要在開頭跳過的行數。CSV檔案第一行為表頭,若設定該參數為1,匯入資料時可自動跳過第一行的表頭。 預設取值為0,即不跳過。 | ||
oss_ignore_quote_and_escape | 是否忽略欄位值中的引號和轉義。預設取值為false,即不忽略欄位值中的引號和轉義。 重要 僅核心版本為3.1.4.2及以上的叢集支援配置該參數。 | ||
charset | OSS外表字元集,取值說明:
重要 僅核心版本為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 |
| DECIMAL | DECIMAL |
BINARY | UTF-8 |
|
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 |
|
|
TIMESTAMP | TIMESTAMP或DATETIME |
DATE | DATE |
ORC格式外表暫不支援LIST
、STRUCT
和UNION
等複合類型,會導致建表失敗。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的資料類型一一對應,但BINARY
、CHAR(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
中分區列的順序保持一致。分區列支援的資料類型包括:
BOOLEAN
、TINYINT
、SMALLINT
、INT
、INTEGER
、BIGINT
、FLOAT
、DOUBLE
、DECIMAL
、VARCHAR
、STRING
、DATE
、TIMESTAMP
。查詢資料時,分區列和其它資料列的展示和用法沒有區別。
不指定format時,預設格式為CSV。
其他參數的詳細說明,請參見參數說明。
相關文檔
更多匯入資料方式,請參見支援的資料來源。