千机游戏提供最新游戏下载和手游攻略!

大数据开发全攻略:初学者必看指南

发布时间:2024-10-26浏览:18

大家好,今天小编来为大家解答大数据开发全攻略:初学者必看指南这个问题,很多人还不知道,现在让我们一起来看看吧!

笔者不才,有任何错误纰漏,欢迎大家指正。

基础功能介绍

▐功能分类

一般来说,数据开发包括了以下几个类型:

▐MaxCompute功能

在此,我们重点介绍一下其中MaxCompute模块(MaxCompute是适用于数据分析场景的企业级SaaS模式云数据仓库)的功能:

基础SQL

▐DDL

具体语句1:

--创建新表。 create [external] table [if not exists][primary key (,),([not null] [default] [comment], ...)] [comment] [partitioned by ([comment], ...)]--用于创建聚簇表时设置表的Shuffle和Sort属性。 [clustered by | range clustered by ([,, ...]) [sorted by ([asc | desc] [,[asc | desc] ...])] intobuckets] --仅限外部表。 [stored by StorageHandler] --仅限外部表。 [with serdeproperties (options)] --仅限外部表。 [location] --指定表为Transactional1.0表,后续可以对该表执行更新或删除表数据操作,但是Transactional表有部分使用限制,请根据需求创建。 [tblproperties("transactional"="true")]--指定表为Transactional2.0表,后续可以做upsert,增量查询,time-travel等操作 [tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [lifecycle];---------------------------------------------------------------------例子:CREATE TABLE IF NOT EXISTS xxx.xxxx_xxxx_xxxx_hh( xxxxx STRING COMMENT '商品' ,xxxxx STRING COMMENT '名字')COMMENT 'xxx表'PARTITIONED BY ( ds STRING COMMENT 'yyyymmddhh')LIFECYCLE 7;

参数说明:

external:可选。表示创建的表为外部表。

if not exists:可选。如果不指定if not exists选项而存在同名表,会报错。

table_name:必填。表名。

primary key(pk):可选。表的主键。

col_name:可选,表的列名。

col_comment:可选。列的注释内容。

data_type:可选。列的数据类型。

not null:可选。禁止该列的值为NULL。default_value:可选。指定列的默认值。

table_comment:可选。表注释内容。

lifecycle:可选。表的生命周期。

partitioned by ([comment], ...:可选。指定分区表的分区字段。

具体语句2:修改表的所有人

alter tablechangeowner to;----------------------------------------------------------例子--将表test1的所有人修改为ALIYUN$xxx@aliyun.comalter table test1 changeowner to 'ALIYUN$xxx@aliyun.com';--将表test1的所有人修改为名称为ram_test的RAM用户alter table test1 changeowner to 'RAM$13xxxxxxxxxxx:ram_test';

参数说明:

table_name:必填。待修改Owner的表名。

new_owner:必填。修改后的Owner账号。如果要修改Owner为RAM用户,格式为:RAMlt;UID>:,其中UID为阿里云账号的账号ID,ram_name为RAM用户显示名称。

具体语句3:修改表的注释

alter tableset comment '';----------------------------------------------------------例子alter table sale_detail set comment 'new coments for table sale_detail';

参数说明:

table_name:必填。待修改注释的表的名称。

new_comment:必填。修改后的注释名称。

具体语句4:修改表的修改时间

alter tabletouch;----------------------------------------------------------例子alter table sale_detail touch;

参数说明:

table_name:必填。待修改表的修改时间的表名称。

具体语句5:重命名表

alter tablerename to;----------------------------------------------------------例子alter table sale_detail rename to sale_detail_rename;

参数说明:

table_name:必填。待修改名称的表。

new_table_name:必填。修改后的表名称。如果已存在与new_table_name同名的表,会返回报错。

具体语句6:删除表

drop table [if exists];----------------------------------------------------------例子drop table if exists sale_detail;

参数说明:

if exists:可选。如果不指定if exists且表不存在,则返回异常。如果指定if exists,无论表是否存在,均返回成功。

table_name:必填。待删除的表名。

具体语句7:查看表或视图信息

--查看表或视图信息。desc[partition ()]; --查看外部表、聚簇表或Transactional表信息。也可以查看内部表的扩展信息。desc extended;----------------------------------------------------------例子desc test1;

参数说明:

table_name:必填。待查看表的名称。

view_name:必填。待查看视图的名称。

pt_spec:可选。待查看分区表的指定分区。

extended:如果表为外部表、聚簇表或Transactional表,需要包含此参数。

具体语句8:查看分区信息

descpartition ();----------------------------------------------------------例子--查询分区表sale_detail的分区信息。desc sale_detail partition (xxxx_date='201310',region='beijing');

参数说明:

table_name:必填。待查看分区信息的分区表名称。

pt_spec:必填。待查看的分区信息。

具体语句9:查看建表语句

show create table;----------------------------------------------------------例子--查看表sale_detail的建表语句。show create table sale_detail;

参数说明:

table_name:必填。待查看建表语句的表的名称。

具体语句10:列出所有分区

show partitions;----------------------------------------------------------例子--列出sale_detail中的所有分区。show partitions sale_detail;

参数说明:

table_name:必填。待查看分区信息的分区表名称。

具体语句11:清空列数据

ALTER TABLE[partition ([,....] )] CLEAR COLUMN column1[, column2, column3, ...] [without touch];

参数说明:

table_name:将要执行清空列数据的表名称。

column1 , column2...:将要被清空数据的列名称。

partition:指定分区。

pt_spec:分区描述。

without touch:表示不更新LastDataModifiedTime。

src_project_name:可选。源表所属MaxCompute项目名称。

src_table_name:必填。源表名称。

pt_spec:可选。源表的分区信息。

dest_project_name:可选。

dest_table_name:必填。目标表名称。

▐DML

具体语句1:插入或覆写数据

--插入:直接向表或静态分区中插入数据,可以在insert语句中直接指定分区值,将数据插入指定的分区。如果您需要插入少量测试数据,可以配合VALUES使用。--覆写:先清空表或静态分区中的原有数据,再向表或静态分区中插入数据。insert {into|overwrite} table[partition ()] [([,...)]]from[zorder by[,...]];------------------------------------------------------------------------------例子--向源表追加数据。其中:insert into table table_name可以简写为insert into table_name,但insert overwrite table table_name不可以省略table关键字。insert into xxxx_detail partition (xxxx_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);--执行insert overwrite命令向表xxxx_detail_insert中覆写数据,调整select子句中列的顺序。insert overwrite table xxxx_detail_insert partition (xxxx_date='2013', region='china') select xxxx_id, xxxx_name, xxxx_price from xxxx_detail;

参数说明:

table_name:必填。需要插入数据的目标表名称。

pt_spec:可选。需要插入数据的分区信息。

col_name:可选。需要插入数据的目标表的列名称。

select_statement:必填。select子句,从源表中查询需要插入目标表的数据。

zorder by[,...]:可选。向表或分区写入数据时,支持根据指定的一列或多列,把排序列数据相近的行排列在一起,提升查询时的过滤性能,在一定程度上降低存储成本。

具体语句2:插入或覆写动态分区数据

--在使用MaxCompute SQL处理数据时,分区列的值在select子句中提供,系统自动根据分区列的值将数据插入到相应分区。insert {into|overwrite} tablepartition ([,...])from;------------------------------------------------------------------------------例子--指定一级分区,将数据插入目标表。insert overwrite table sale_detail_dypart partition (sale_date='2013', region)select shop_name,customer_id,total_price,region from sale_detail;--将源表sale_detail中的数据插入到目标表sale_detail_dypart。insert overwrite table sale_detail_dypart partition (sale_date, region)select shop_name,customer_id,total_price,sale_date,region from sale_detail;

参数说明:

table_name:必填。需要插入数据的目标表名。

ptcol_name:必填。目标表分区列的名称。

select_statement:必填。select子句,从源表中查询需要插入目标表的数据。

具体语句3:更新或删除数据

--删除操作:用于删除Transactional或Delta Table表中满足指定条件的单行或多行数据。delete from[where];--清空列数据:将不再使用的列数据从磁盘删除并置NULL,从而达到降低存储成本的目的。ALTER TABLE[partition ([,....] )] CLEAR COLUMN column1[, column2, column3, ...] [without touch];--更新操作:用于将Transactional表或Delta Table表中行对应的单列或多列数据更新为新值。--方式1updateset=[,=...] [WHERE];--方式2updateset ([,...]) = ([,...])[WHERE];--方式3UPDATESET=[ ,=, ... ] [ FROM] [ WHERE]

参数说明:

table_name:必填。

where_condition:可选。WHERE子句,用于筛选满足条件的数据。

partition:指定分区,若未指定,则表示操作所有分区。

pt_spec:分区描述。

without touch:表示不更新LastDataModifiedTime。

col1_name、col2_name:待修改行对应的列名称。

value1、value2:至少更新一个列值。修改后的新值。

where_condition:可选。WHERE子句,用于筛选满足条件的数据。

additional_tables:可选,from子句。

具体语句4:merge into

merge intoasusingas--从on开始对源表和目标表的数据进行关联判断。on--when matched…then指定on的结果为True的行为。多个when matched…then之间的数据无交集。when matched [and] then update setwhen matched [and] then delete --when not matched…then指定on的结果为False的行为。when not matched [and] then insert values------------------------------------------------------------------------------例子--执行merge into操作,对符合on条件的数据用源表的数据对目标表进行更新操作,对不符合on条件并且源表中满足event_type为I的数据插入目标表。命令示例如下:merge into acid_address_book_base1 as t using tmp_table1 as s on s.id = t.id and t.year='2020' and t.month='08' and t.day='20' and t.hour='16' when matched then update set t.first_name = s.first_name, t.last_name = s.last_name, t.phone = s.phone when not matched and (s._event_type_='I') then insert values(s.id, s.first_name, s.last_name,s.phone,'2020','08','20','16');

参数说明:

target_table:必填。目标表名称,必须是实际存在的表。

alias_name_t:必填。目标表的别名。

source expression|table_name:必填。关联的源表名称、视图或子查询。

alias_name_s:必填。关联的源表、视图或子查询的别名。

boolean expression1:必填。BOOLEAN类型判断条件,判断结果必须为True或False。

boolean expression2:可选。update、delete、insert操作相应的BOOLEAN类型判断条件。

set_clause_list:当出现update操作时必填。

value_list:当出现insert操作时必填。

具体语句5:Values

--insert … valuesinsert into table[partition ()][(,,...)] values (,,...),(,,...),...--values tablevalues (,,...),(,,...),(,,...)...

参数说明:

table_name:必填。待插入数据的表名称。

pt_spec:可选。需要插入数据的目标分区信息。

col_name:可选。需要插入数据的目标列名称。

col_value:可选。目标表中列对应的列值。

具体语句6:Load

--将Hologres、OSS、Amazon Redshift、BigQuery外部存储的CSV格式或其他开源格式数据导入MaxCompute的表或表的分区。{load overwrite|into} table[partition ()]from locationstored by[with serdeproperties ()];------------------------------------------------------------------------------例子load overwrite table xxxx_data_csv_loadfromlocation 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/'stored by 'com.aliyun.odps.CsvStorageHandler'with serdeproperties ( 'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole', --AliyunODPSDefaultRole的ARN信息,可通过RAM角色管理页面获取。 'odps.text.option.delimiter'=',');

参数说明:

table_name:必填。需要插入数据的目标表名称。

pt_spec:可选。需要插入数据的目标表分区信息。

external_location:必填。指定读取外部存储数据的OSS目录。

StorageHandler:必填。指定内置的StorageHandler名称。

Options:可选。指定外部表相关参数。

具体语句7:Unload

--将MaxCompute的数据导出至OSS、Hologres外部存储,OSS支持以CSV格式或其他开源格式存储数据。unload from {|[partition ()]} into locationstored by[with serdeproperties (''='',...)];------------------------------------------------------------------------------例子--控制导出文件个数:设置单个Worker读取MaxCompute表数据的大小,单位为MB。由于MaxCompute表有压缩,导出到OSS的数据一般会膨胀4倍左右。set odps.stage.mapper.split.size=256;--导出数据。unload from sale_detail partition (sale_date='2013',region='china')intolocation 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-unload/data_location'stored by 'com.aliyun.odps.TsvStorageHandler'with serdeproperties ('odps.properties.rolearn'='acs:ram::139699392458****:role/AliyunODPSDefaultRole', 'odps.text.option.gzip.output.enabled'='true');

参数说明:

select_statement:select查询子句,

table_name、pt_spec:使用表名称或表名称加分区名称的方式指定需要导出的数据。

external_location:必填。

StorageHandler:必填。指定内置的StorageHandler名称。

'='':可选。property_name为属性名称,property_value为属性值。

具体语句8:Explain

--分析查询语句或表结构来分析性能瓶颈explain;------------------------------------------------------------------------------例子explain select a.customer_id as ashop, sum(a.total_price) as ap,count(b.total_price) as bp from (select * from sale_detail_jt where sale_date='2013' and region='china') a inner join (select * from sale_detail where sale_date='2013' and region='china') b on a.customer_id=b.customer_id group by a.customer_id order by a.customer_id limit 10;

参数说明:

dml query:必填。select语句。

具体语句9:公用表表达式

--临时命名结果集,用于简化SQL,可以更好地提高SQL语句的可读性与执行效率withas () [,as () ,……]------------------------------------------------------------------------------例子 with a as (select * from src where key is not null), b as (select * from src2 where value >0), c as (select * from src3 where value >0), d as (select a.key, b.value from a join b on a.key=b.key), e as (select a.key,c.value from a left outer join c on a.key=c.key and c.key is not null)insert overwrite table srcp partition (p='abc')select * from d union all select * from e;

参数说明:

cte_name:必填。CTE的名称,不能与当前with子句中的其他CTE的名称相同。查询中任何使用到cte_name标识符的地方,均指CTE。

cte_query:必填。一个select语句。select的结果集用于填充CTE。

▐DQL

  • SELECT语句

1. SELECT语法

[with[, ...] ]SELECT [all | distinct][,][,] ... from[where] [group by {|rollup()}] [having] [window] [order by] [distribute by[sort by]|[ cluster by] ] [limit]

下面将介绍SELECT命令格式及如何实现嵌套查询、分组查询、排序等操作。

2. SELECT语序

--语法顺序from[where][group by][having][windowAS ()][qualify]select [all | distinct],, ...[order by][distribute by[sort by] ][limit]

场景1:from->where->group by->having->select->order by->limit

场景2:from->where->select->distribute by->sort by

3. WITH子句

with A as (SELECT 1 as C),B as (SELECT * from A) SELECT * from B;

在同一WITH子句中的CTE必须具有唯一的名字。

在WITH子句中定义的CTE仅对在同一WITH子句中的其他CTE可以使用。

4. 列表达式

------------------------------------------------------------------------------例子 --读取表xxxx_detail的列shop_nameSELECT xxxx_name from xxxx_detail;--查询表xxxx_detail中region列数据,如果有重复值时仅显示一条。SELECT distinct region from xxxx_detail;--选出xxxx_detail表中列名不为xxxx_name的所有列SELECT `(xxxx_name)?+.+` from xxxx_detail;--去重多列时,distinct的作用域是SELECT的列集合,不是单个列。SELECT distinct region, xxxx_date from xxxx_detail;

用列名指定要读取的列。

用星号(*)代表查询所有的列。

可以使用正则表达式。

在选取的列名前可以使用distinct去掉重复字段,只返回去重后的值。

5. 排除列

--读取xxxx_detail表的数据,并排除region列的数据。------------------------------------------------------------------------------例子SELECT * except(region) from xxxx_detail;

当希望读取表内大多数列的数据,同时要排除表中少数列的数据时。

表示读取表数据时会排除指定列(col1、col2)的数据。

6. WHERE

--配合关系运算符,筛选满足指定条件的数据。关系运算符包含: >、<、=、>=、<=、<>like、rlike in、not in between…and------------------------------------------------------------------------------例子SELECT * from xxxx_detailwhere xxxx_date >= '2008' and xxxx_date<= '2014';--等价于如下语句。SELECT * from xxxx_detail where xxxx_date between '2008' and '2014';

where子句为过滤条件。如果表是分区表,可以实现列裁剪。

7. GROUP BY

------------------------------------------------------------------------------例子--直接使用输入表列名region作为group by的列,即以region值分组SELECT region from xxxx_detail group by region;--以region值分组,返回每一组的销售额总量。SELECT sum(xxxx_price) from xxxx_detail group by region;--以region值分组,返回每一组的region值(组内唯一)及销售额总量。SELECT region, sum (xxxx_price) from xxxx_detail group by region;

group by操作优先级高于SELECT操作,因此group by的取值是SELECT输入表的列名或由输入表的列构成的表达式。需要注意的是:

group by取值为正则表达式时,必须使用列的完整表达式。

SELECT语句中没有使用聚合函数的列必须出现在GROUP BY中。

8. HAVING

------------------------------------------------------------------------------例子--为直观展示数据呈现效果,向sale_detail表中追加数据。insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);--使用having子句配合聚合函数实现过滤。SELECT region,sum(total_price) from sale_detail group by region having sum(total_price)<305;

通常HAVING子句与聚合函数一起使用,实现过滤。

9. ORDER BY

------------------------------------------------------------------------------例子--查询表xxxx_detail的信息,并按照xxxx_price升序排列前2条。SELECT * from xxxx_detail order by xxxx_price limit 2;--将表xxx_detail按照xxxx_price升序排序后,输出从第3行开始的3行数据。SELECT xxxx_id,xxxx_price from xxxx_detail order by xxxx_price limit 3 offset 2;

默认对数据进行升序排序,如果降序排序,需要使用desc关键字。

order by默认要求带limit数据行数限制,没有limit会返回报错。

10. DISTRIBUTE BY哈希分片

------------------------------------------------------------------------------例子--查询表xxxx_detail中的列region值并按照region值进行哈希分片。SELECT region from xxxx_detail distribute by region;--等价于如下语句。SELECT region as r from xxxx_detail distribute by region;SELECT region as r from xxxx_detail distribute by r;

distribute by控制Map(读数据)的输出在Reducer中是如何划分的,如果不希望Reducer的内容存在重叠,或需要对同一分组的数据一起处理,可以使用distribute by来保证同组数据分发到同一个Reducer中。

11. SORT BY局部排序

------------------------------------------------------------------------------例子--查询表xxxx_detail中的列region和xxxx_price的值并按照region值进行哈希分片,然后按照xxxx_price对哈希分片结果进行局部升序排序。SELECT region,xxxx_price from xxxx_detail distribute by region sort by xxxx_price;--查询表xxxx_detail中的列region和xxxx_price的值并按照region值进行哈希分片,然后按照xxxx_price对哈希分片结果进行局部降序排序。SELECT region,xxxx_price from xxxx_detail distribute by region sort by xxxx_price desc;--如果sort by语句前没有distribute by,sort by会对每个Reduce中的数据进行局部排序。SELECT region,xxxx_price from xxxx_detail sort by xxxx_price desc;

sort by默认对数据进行升序排序,如果降序排序,需要使用desc关键字。

如果sort by语句前有distribute by,sort by会对distribute by的结果按照指定的列进行排序。

12. LIMIT限制输出行数

SELECT * FROM xxxxx.xxxx_xxxx_xxxxWHERE ds = 20240520LIMIT 100;

limit中的number是常数,用于限制输出行数,取值范围为int32位取值范围。

  • 子查询

1. 基础子查询

--格式1selectfrom () [];--格式2select () from;

普通查询操作的对象是目标表,但是查询的对象也可以是另一个select语句,这种查询为子查询。在from子句中,子查询可以被当作一张表,与其他表或子查询进行join操作。

2. IN SUBQUERY

--in subquery与left semi join用法类似--格式一selectfromwherein(selectfrom);--等效于leftsemijoin如下语句。selectfromleftsemijoinon.=.;--格式二selectfromwherein(selectfromwhere.=.);------------------------------------------------------------------------------例子set odps.sql.allow.fullscan=true;select * from xxxx_detail where xxxx_price in (select xxxx_price from shop);set odps.sql.allow.fullscan=true;select * from xxxx_detail where xxxx_price in (select xxxx_price from shop where xxxx_id = shop.xxxx_id);

select_expr1:必填。格式为col1_name, col2_name, 正则表达式,...,表示待查询的普通列、分区列或正则表达式。

table_name1、table_name2:必填。表的名称。

select_expr2、select_expr3:必填。表示table_name1和table_name2互相映射的列名。

col_name:必填。表的列名。

3. NOT IN SUBQUERY

--如果查询目标表的指定列名中有任意一行为NULL,则not in表达式值为NULL,导致where条件不成立,无数据返回selectfromwherenot in (selectfrom);--等效于left anti join如下语句。selectfromleft anti joinon.=.;------------------------------------------------------------------------------例子--创建一张新表shop1并追加数据。create table shop1 as select xxxx_name,xxxx_id,xxxx_price from xxxx_detail;insert into shop1 values ('s8','c1',100.1);select * from shop1 where xxxx_name not in (select xxxx_name from xxxx_detail);set odps.sql.allow.fullscan=true;select * from shop1 where xxxx_name not in (select xxxx_name from xxxx_detail where xxxx_id = shop1.xxxx_id);

select_expr1:必填。格式为col1_name, col2_name, 正则表达式,...,表示待查询的普通列、分区列或正则表达式。

table_name1、table_name2:必填。表的名称。

select_expr2、select_expr3:必填。表示table_name1和table_name2互相映射的列名。

col_name:必填。表的列名。

4. EXISTS SUBQUERY

--使用exists subquery时,当子查询中有至少一行数据时,返回True,否则返回False。selectfromwhere exists (selectfromwhere=.);------------------------------------------------------------------------------例子set odps.sql.allow.fullscan=true;select * from xxxx_detail where exists (select * from shop where customer_id = xxxx_detail.xxxx_id);--等效于以下语句。select * from xxxx_detail a left semi join shop b on a.xxxx_id = b.xxxx_id;

select_expr:必填。格式为col1_name, col2_name, 正则表达式,...,表示待查询的普通列、分区列或正则表达式。

table_name1、table_name2:必填。表的名称。

col_name:必填。表的列名。

5. NOT EXISTS SUBQUERY

--当子查询中无数据时,返回True,否则返回Falseselectfromwhere not exists (selectfromwhere=.);------------------------------------------------------------------------------例子set odps.sql.allow.fullscan=true;select * from xxxx_detail where not exists (select * from shop where xxxx_name = xxxx_detail.xxxx_name);--等效于以下语句。select * from xxxx_detail a left anti join shop b on a.shop_name = b.xxxx_name;

select_expr:必填。格式为col1_name, col2_name, 正则表达式,...,表示待查询的普通列、分区列或正则表达式。

table_name1、table_name2:必填。表的名称。

col_name:必填。表的列名。

6. SCALAR SUBQUERY

--当子查询的输出结果为单行单列时,可以做为标量使用,即可以参与标量运算。selectfromwhere (