MaxCompute SQL 与 Hive 对比分析及使用注意事项

开发者学堂课程【SaaS 模式云数据仓库系列课程 —— 2021数仓必修课：MaxCompute SQL 与 Hive 对比分析及使用注意事项】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/55/detail/1051

内容简介：

一、MaxCompute 和 Hive 对比内容介绍

二、MaxCompute 介绍

三、MaxCompute 和Hive 对比

四、注意事项

一、MaxCompute 和 Hive 对比内容介绍

对比内容

对比内容	MaxCompute Hive
文件系统
调度系统
客户端
SQL对比	建表语句	分区分桶	数据上传下载
	外部表	SQL函数	UDF
	MapReduce	SQL调优	文件格式

Web UI
界面话操作
权限

二、MaxCompute 介绍

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;

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

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对比

⑴建表语句

MaxCompute

Hive

建表语句

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

Hive

数据上传和

下载

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)分区和分桶对比

MaxCompute

Hive

分区

分区用法一致

分桶

实际上分桶是把一个大文件根据某个字段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)外部表对比

MaxCompute

Hive

外

部

表

对

比

外部表功能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	Hive
自定义函数	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	Hive
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 优化

	MaxCompute	Hive
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、界面话操作

MaxCompute

Hive

界

面

话

操

作

阿里云的产品基本上都是界面化操作，可拖拽等等，开发门槛非常低,所以也是非常适合初学大数据或者公司没有相关开发人力的公司。

hive可以借助hue工具来操作查询数据，但是实际上交互性不是很强。

Maxcompute 的界面化操作可以结合 Dataworks 做数据同步，权限控制，数据管理和其他数据源交互，定时调度等。

6、权限操作

	MaxCompute	Hive
权限操作	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;

MaxCompute SQL 与 Hive 对比分析及使用注意事项 | 学习笔记