MaxCompute SQL 與 Hive 對比分析及使用注意事項 | 學習筆記

開發者學堂課程【SaaS 模式雲資料倉庫系列課程 —— 2021數倉必修課：MaxCompute SQL 與 Hive 對比分析及使用注意事項】學習筆記，與課程緊密聯系，讓使用者快速學習知識。

課程位址：

https://developer.aliyun.com/learning/course/55/detail/1051

MaxCompute SQL 與 Hive 對比分析及使用注意事項

内容簡介：

一、MaxCompute 和 Hive 對比内容介紹

二、MaxCompute 介紹

三、MaxCompute 和Hive 對比

四、注意事項

​

一、MaxCompute 和 Hive 對比内容介紹

對比内容

	MaxCompute  Hive
檔案系統
排程系統
用戶端
SQL對比	建表語句	分區分桶	資料上傳下載下傳
外部表	SQL函數	UDF
MapReduce	SQL調優	檔案格式

Web UI
界面話操作
權限

MaxCompute 主要服務于批量結構化資料的存儲和計算，可以提供海量資料倉庫的解決方案以及針對大資料的分析模組化服務。

基于 MaxCompute的 Serverless 無伺服器的設計思路，使用者隻需關心作業和資料，而無需關心底層分布式架構及運維。

三、MaxCompute 和 Hive 對比

1、檔案系統對比

MaxCompute	Hive
	MaxCompute的資料是在飛天檔案系統，對外不暴露檔案系統，底層優化會自動做好.	Hive的資料實際上是在HDFS上，中繼資料一般放在MySql，以表的形式展現。可以直接到HDFS上查到具體檔案.

飛天(Apsara )是由阿裡雲自主研發、服務全球的超大規模通用計算作業系統。

2、用戶端對比

(1)Hive 用戶端：

[rooteemr-header-1~]# hive

Hive Session ID-b005c924-1127-48bc-bb5-8ef546564898

Logging initialized using configuration in file:/etc/ecam/hive-conf-3.1.1-1.1.6/hive-log4j2.properties Async: true

Hive Session ID 25ecee88-fceb-44ea-a38f-4b98f94fe6c1

Hive-on-HR is deprecated in Hive 2 cnd may not be available in the future versions.Corsider using a different execution engine (i.e. spark,tez) or using Hive 1.X re es.

hive show databeses;

OK

28/02/05 88:57:05 INFO Configuration.deprecation:mcpred.input.dir is deprecated.Instecduse mapreduce.irput.fileinputformat.irputdir

20/02/0588:57:05 INFO 1zo.GPLNativeCodeLoader:Looded native gpl library from the erbedded bincries

20/02/05 08:57:85 INFO Lzo.LzoCodec: Successfully looded & initialized rative-Lzo library Dredoop-1zo rev 97184efe294f64651c4c5c17Zcbx221461036053]

20/02/0508:57:05 INFO mapred.FileIrputFornat:Total irput files to process:1

default

extra_demo

git_database

mno_deno_wei

mmo_demo_zyj

mma_hive

par_deno

Time taken: 0.936

(2)MaxCompute 用戶端：

Last login: Wed Feb 5 09:01:02 on ttys004

/Users/1jw/Desktop/odpscmd_public/bin/odpscmd;exit;

-bash:export:'CLASSPATH#': not a valid identifier

-bash:export:‘2019.10': not a valid identifier

(base)IT-CO2NG62KG5RN:~ ljw$ /Users/1jw/Desktop/odpscmd_public/bin/odpscmd ; exit;

/_/

Aliyun ODPS Command Line Tool

Version 0.32.1-public

@Copyright 2019 Alibaba Cloud Computing Co., Ltd. Al1 rights reserved.

Connectingto 

http://service.cn-hangzhou.maxcompute.aliyun.com/api

，project:WB_BestPractice_devProject timezone:Asia/Kolkata

Connected!

odoso WB BestPractice dev>

項目空間 (Project) 是 MaxCompute 的基本組織單元，它類似于傳統資料庫的Database 或 Schema 的概念，是進行多使用者隔離和通路控制的主要邊界。一個使用者可以同時擁有多個項目空間的權限。通過安全授權，可以在一個項目空間中通路另一個項目空間中的對象，例如表(Table)、資源(Resource)、函數(Function)、和執行個體 Instance 。

MaxCompute 除了指令行用戶端也提供了 Python 和 Java 的 SDK 來通路。

MaxCompute SDK 的入口，可通過此類來擷取項目空間下的所有對象集合，包括Projects、Tables、Resources、Functions、Instances。

可以通過傳入 Aliyu Account 執行個體來構造 MaxCompute 對象。程式示例如下。

Account account = new AliyunAccount("my_access_id", "my_access_key");

Odps odps = new Odps(account);

String odpsUrl="";

odps.setEndpoint(odpsUrl);

odps.setDefaultProject("my_project");

for (Table t: odps.tables())(

MaxCompute Tunnel 資料通道是基于 Tunnel SDK 編寫的。可以通過 Tunnel 向 MaxCompute 中上傳或者下載下傳資料

SQLTask

SQLTask 是用于運作、處理 SQL 任務的接口。可以通過運作接口直接運作 SQL。注意：每次隻能送出運作一個 SQL 語句，腳本模式可以一次送出多個SQL。

運作接口傳回 Instance 執行個體，通過 Instance 擷取 SQL 的運作狀态及運作結果。程式示例如下。

import java.util.List;

import com.aliyun.odps.Instance;

import com.aliyun.odps.Odps;

import com.aliyun.odps.OdpsException;

import com.aliyun.odps.account.Account;

import com.aliyun.odps.account.AliyunAccount;

import com.aliyun.odps.data.Record;

import com.aliyun.odps.task.SQLTask;

public class testsql(

private static final String accessId

private static final String accessKey

private static final String endPoint "http://service.odps.aliyun.com/api" private static final String project

private static final String sql = "select category from iris;";

public static void

main(String[】 args)(

Account account = new AliyunAccount(accessId, accessKey);

0dpsodps = newOdps(account); )

odps.setEndpoint(endPoint);

odps.setDefaultProject(project);

Instance i;

try(

i = SQLTask.run(odps,sql);

i.waitForSuccess();

List records SQLTask.getResult(i); for(Record r:records)

System.out.println(r.get(0).toString());

catch (OdpsException e) (

e.printStack7race();2

3、Hive SQL和MaxCompute SQL對比

⑴建表語句

	create table t1id int comment 'lD'，name string comment '名稱'，hobby string comment '愛好'，add1 map<string, string> partitioned by (ds string comment '目前時間，用于分區字段');	Create table t1( idint,namestring,hobby array<string>,addmap<String,string> row format delimitedfields terminated by ";collection items terminated by '-'map keys terminated by ":"

MaxCompute 建表不需要指定分隔符,指定檔案存儲路徑，指定檔案的存儲格式。

MaxCompute 是基于阿裡雲飛天檔案系統,使用者無需關心檔案存儲格式,壓縮格式,存儲路徑等,這些操作由阿裡雲來完成,使用者也不用來疲于檔案存儲成本，壓縮成本效益，讀寫速度等優化,可以将精力集中在業務的開發上。

(2)資料的上傳下載下傳對比

資料上傳和 下載下傳	MaxCompute是通過指令工具tunnel上 傳下載下傳。	Hive是通過指令上傳資料 load data [local] inpath ' /opt/module/datas/test.txt’[overwrite] into table test

Upload:

上傳資料到 MaxCompute 的表中。支援檔案或目錄(指一級目錄)的上傳，每一次上傳隻支援資料上傳到一張表或表的一個分區。分區表一定要指定上傳的分區，多級分區一定要指定到末級分區。

将log.txt中的資料上傳至項目空間 test_project 的表 test_table（二級分區表）中的p1="b1"，p2="b2"分區。tunnel upload log.txt test_project.test_table/p1="b1",p2="b2";

Download:

從 MaxCompute 的表中下載下傳資料。隻支援下載下傳到單個檔案，每一次下載下傳隻支援下載下傳一張表或一個分區到一個檔案。分區表一定要指定下載下傳的分區，多級分區一定要指定到未級分區。

将 test_project.test_table 表(二級分區表)中的資料下載下傳到 test_table.txt 檔案中。

tunnel downloadtest_project.test_table/p1="b1"，p2="b2"test_table.txt;

(3)分區和分桶對比

分區	分區用法一緻
分桶	實際上分桶是把一個大檔案根據某個字段hash成多個小檔案，适當的分桶會提高查詢效率，在MaxCompute中這些優化底層已經做了。可以在建表時通過指定clustered by中的Hash Key。MaxCompute将對指定列進行Hash運算，按照Hash值分散到各個Bucket中。為避免資料傾斜和熱點，取得較好的并行執行效果，clustered by列适宜選擇取值範圍大，重複鍵值少的列。此外，為了達到join優化的目的，也應該考慮選取常用的Join/Aggregation Key，即類似于傳統資料庫中的主鍵。	執行指令hive.enforce.bucketiong=true;使用關鍵字clustered by指定分區依據的列名，還要指定分為多少桶 create table test(id int,name string)clusteredby(id) into 3 buckets row format delimited fieldsterminated by "it

(4)外部表對比

外 部 表 對 比	外部表功能MaxCompute2.0中也是同樣支援的,MaxCompute通過外部表來映射阿裡雲的OTS和OSS兩個資料存儲産品來處理非結構化的資料，例如音頻視訊等。	Hive可以通過外部表的功能來操作例如Hbase和ES的資料。

MaxCompute非結構化資料架構通過External Table與各種資料的關聯，關聯OSS上開源格式資料的ExternalTable建表的DDL語句格式如下所示。

DROP TABLE[IF EXISTS] ;

CREATE EXTERNAL TABLE [IF NOT EXISTS] 

()

[PARTITIONED BY(partition column schemas)】【ROW FORMAT SERDE ''

[WITHSERDEPROPERTIES ('odps.properties,rolearn'='$(roleran]'[,name2'='value2',..])]

STORED AS 

LOCATION'oss://$tendpoint]/$(bucketY/$(userfilePath]/';

該文法格式與 Hive 的文法接近，但需注意以下問題：

column schemas :外部表的 column schemas 必須與具體 OSS 上存儲的資料的schema相符合。

ROW FORMAT SERDE：非必選選項，隻有在使用一些特殊的格式(例如TEXTFILE)時才需要使用。WITH SERDEPROPERTIES: 當關聯 OSS 權限使用 STS 模式授權時，需要該參數指定 odps.properties.rolearn 屬性，屬性值為 RAM 中具體使用的 Role 的 Arn 的資訊。您可以在配置 STORED AS 的同時也通過說明 fileformat 檔案格式。

如果不使用 STS 模式授權，則無需指定 odps.properties.rolearn 屬性，直接在Location 傳入明文 AccessKeyld 和 AccessKeySecret

授權方式有兩種：

(1)當 MaxCompute 和 OSS 的 Owner 是同一個賬号時，可以直接登入阿裡雲賬号後，單擊一鍵授權。在通路控制給改子賬号添加管理對象存儲服務 (OSS) 權限 (AliyunOSSFullAccess)。

https://help.aliyun.com/document_detail/72777.html?spm=a2c4g.11174283.6.772.559b590eFt4rNd

(2)自定義授權

a.新增一個 RAM 角色 oss-admin

b.修改角色政策内容設定

c.授予角色通路 OSS 必要的權限 AliyunODPSRolePolicy

d.将權限 AliyunODPSRolePolicy 授權給該角色。

(5)自定義函數對比

自定義函數	MaxCompute支援自定函數,udf,udtf，udaf	Hive支援自定函數, udf，udtf，udaf

MaxCompute 2.0 版本更新後，Java UDF 支援的資料類型從原來的 BIGINT、STRING、DOUBLE.

BOOLEAN 擴充了更多基本的資料類型，同時還擴充支援了 ARRAY、MAP、STRUCT以及Writable 等複雜類型

使用複雜資料類型的方法，STRUCT對應com.aliyun.odps.data.Struct。com.aliyun.odps.data.Struct 從反射看不出 Field Name 和 Field Type ，是以需要用 @Resolve 注解來輔助。即如果需要在 UDF 中使用 STRUCT，要求在UDFClass 上也标注上 @Resolve 注解。但是當我們 Struct 類型中的 field 有很多字段的時候，這個時候需要我們去手動的添加 @Resolve 注解就不是那麼的友好。

針對這一個問題，我們可以使用 Hive 中的 GenericUDF 去實作。MaxCompute 2.0支援 Hive 風格的 UDF，部分 HiveUDF、UDTF 可以直接在 MaxCompute上使用。

(6) MapReduce 開發

	MaxCompute提供三個版本的MapReduce程式設計接口∶ MaxCompute MapReduce、MapReduce(MR2)、Hadoop相容版本MapReduce	MapReduce是一個基于叢集的計算平台，是-個簡化分布式程式設計的計算架構，是一個将分布式計算抽象為Map和Reduce兩個階段的程式設計模型。

MaxCompute MapReduce : MaxCompute的原生接口，執行速度更快、開發更便捷、不暴露檔案系統。

MaxCompute 擴充 MapReduce ( MR2) :對 MaxCompute MapReduce 的擴充，支援更複雜的作業排程邏輯。MapReduce 的實作方式與 MaxCompute 原生接口一緻。

Hadoop 相容版本 MapReduce ∶高度相容 Hadoop MapReduce，與MaxCompute MapReduce MR2 不相容。

(7)Sql 優化

Sql優化	key分布不均勻，資料傾斜，join長尾,視窗函數	key分布不均勻，資料傾斜,join長尾，視窗函數

4、WebUI 對比​

WebuI	MaxCompute  中使用  Logview檢視 Job 資訊。通過  Logview  可看到一個 Job 的如下内容∶	Hive  任務依賴于 Hadoop的HDFS和yarn提供的WebUI  訪
任務的運作狀态。
任務的運作結果。
任務的細節和每個步驟的進度。
Job  送出到  MaxCompute  後，會生成
Logview  的連結。可以直接在浏覽器上打開  Logview  連結，進入檢視 Job  的資訊。

Logview 的首頁分成上下兩部分 :Instance 資訊

Task 資訊

Instance 資訊

在 Logview 頁面中，上半部分是您送出的 SQL 任務對應的 MaxCompute Instance 資訊,包含URL連結、項目、

InstancelD、送出人、開始時間、結束時間和狀态( Status )等。Instance資訊部分,您可以單擊 Status 檢視目前隊列的 Status 詳細資訊,包含四種狀态:

Waiting :說明目前作業正在 MaxCompute 中處理,并沒有送出到分布式排程系統( Fuxi )中運作。

Waiting List : n :說明作業已送出至分布式排程系統( Fuxi )排隊等待運作,目前在隊列中處于第n位。

Running :作業在分布式排程系統( Fuxi )中運作。

5、界面話操作

界 面 話 操 作	阿裡雲的産品基本上都是界面化操作，可拖拽等等，開發門檻非常低,是以也是非常适合初學大資料或者公司沒有相關開發人力的公司。	hive可以借助hue工具來操作查詢資料，但是實際上互動性不是很強。

Maxcompute 的界面化操作可以結合 Dataworks 做資料同步，權限控制，資料管理和其他資料源互動，定時排程等。

6、權限操作

權限操作	ACL+Policy	ACL

1、Java 沙箱限制

MaxCompute MapReduce 及 UDF 程式在分布式環境中運作時，受到 Java 沙箱的限制 (MapReduce 作業的主程式，例如MR Main則不受此限制)，具體限制如下所示。

不允許直接通路本地檔案，隻能通過 MaxCompute MapReduce/Graph 提供的接口間接通路。

讀取 resources 選項指定的資源，包括檔案、Jar 包和資源表等。

通過 System.out 和 System.err 輸出日志資訊，可以通過 MaxCompute 用戶端的Log 指令檢視日志資訊。不允許直接通路分布式檔案系統，隻能通過MaxCompute MapReduce/Graph 通路到表的記錄。

不允許JNI調用限制。

不允許建立 Java 線程，不允許啟動子程序執行 Linux 指令。

Java 反射限制：suppressAccessChecks 權限被禁止，無法 setAccessible 某個private 的屬性或方法，以達到讀取 private 屬性或調用 private 方法的目的。

2、Sql 使用權限

參照數值

3、MaxCompute 資料操作

MaxCompute 不支援直接對表資料删除 (Delete) 和更新 (Update) 的文法。

更新( Update）資料隻能把源分區或表資料導入到新分區或表（新分區或表可以與源分區或表相同），在導入過程中執行相應的更新邏輯。

對于非分區列，隻支援重命名和建立列，不支援對列的删除。

MaxCompute 不支援 Update 語句，建議把更新後的結果篩選出來，然後用Insert Overwrite 的方法寫回原表。

删除 (Delete) 的資料可以通過删除 (Drop) 表達到資料删除目的。

非分區表可以通過 TRUNCATE TABLE table_name; 語句清空表資料。

分區表可以通過

ALTER TABLE table_name DROP IF EXISTS PARTITION (分區名=‘具體分區值)删除分區達到删除整個分區資料的目的。

通過INSERT和WHERE條件把需要的資料導入到另一張新分區或表中或就地更新，INSERT語句支援源表和目的表是同一張表。

4、MaxCompute 單字段大于 8MB 的限制

處理思路：目前由于存儲機制限制，MaxCompute 表 中單個字段(多為 String 字段)的最大長度不能超過 8MB。對于超過 8MB的超大字段，建議拆分成多個字段。具體的拆分邏輯可以根據業務特性設計，保證每個字段不超過 8MB 即可。

常用方法：由于複雜結構的超大字段在資料開發和分析中會嚴重影響計算性能，是以建議根據數倉建設規範來設計您的資料架構，避免超大字段：

具有複雜結構的原始資料，作為 ODS 層，最好以壓縮的方式歸檔。

定時(例如每天)對 ODS 層的增量資料做資料清洗，複雜字段拆分為多個簡單字段，然後存儲在 CDM 層的表中，便于資料的統計分析。

5、設定相容 Flag

說明 預設模式是 MaxCompute 模式，如果要切換至 Hive 模式，需要開啟odps.sql.hive.compatible為True。--Project 級别切換為 Hive 模式。

setproject odps.sql.hive.compatible=True;

Session

級别切換為 Hive 模式。

set odps.sql.hive.compatible=True;