阿里MaxComputer基本操作

注意

as 后面不支持单引号
表名称不支持中文

查看数据

read table 10;

flag

-- order by 
set  odps.sql.validate.orderby.limit=false;
-- 付费
set odps.task.quota.preference.tag=payasyougo;
--使用python函数
set odps.sql.python.version=cp37;
-- group by 1,2,3
set hive.groupby.position.alias=true;
-- 合并小文件
set odps.merge.cross.paths=true;
set odps.merge.smallfile.filesize.threshold=128;
set odps.merge.max.filenumber.per.instance = 2000;
-- 允许mapjoin
set odps.sql.allow.cartesian=true;
-- 设置资源池
set odps.task.quota.preference.tag = 资源池名称

新建表

  CREATE TABLE dc_channelid
    ( publisher_id bigint, APP string, site_channelid string,  name string, label_name string)
    ##加载oss数据
    LOAD INTO TABLE dc_channelid 
    FROM LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/path'
    STORED AS `PARQUET`
    ;

新建函数

#ipv4
create or replace SQL functioninet_ntoa(@ip bigint)
returns @ipv4 string
as BEGIN 
@ipv4:=concat(shiftright(@ip, 24), '.', bitand(shiftright(@ip, 16), 255), '.'
, bitand(shiftright(@ip, 8), 255), '.', bitand(@ip, 255));
end;

create sql function inet6_aton_part(@ip string, @partId bigint) as begin
@part := split(@ip, ':')[@partId];
@inet6_aton_part := unhex(if(@part is null, '', if (@part = '', '0000', lpad(@part, 4, '0'))));
end;
#ipv6
create sql function inet6_aton(@ip string) as
concat(
inet6_aton_part(@ip, 0),
inet6_aton_part(@ip, 1),
inet6_aton_part(@ip, 2),
inet6_aton_part(@ip, 3),
inet6_aton_part(@ip, 4),
inet6_aton_part(@ip, 5),
inet6_aton_part(@ip, 6),
inet6_aton_part(@ip, 7));

ipv4转数字inet_aton

 select bigint(split_part(ip, '.', 1)) * 256 * 256 * 256 
 + bigint(split_part(ip, '.', 2)) * 256 * 256 
 + bigint(split_part(ip, '.', 3)) * 256 + bigint(split_part(ip, '.', 4)) from values ('1.2.3.4'), ('209.207.224.40') t (ip);

非标准日期处理

数据：2021-1-2
concat(lpad(split_part(listing_date, '-', 1), 4, '0'), lpad(split_part(listing_date, '-', 2), 2, '0'), lpad(split_part(listing_date, '-', 3), 2, '0')) as listing_date
结果：20210102

更新数据

CREATE TABLE IF NOT EXISTS ali_pai_dev.lq_dc_feature_tag_202012_end_new_update
(degree_adopt STRING,degree_school STRING,life_style STRING,is_marrige STRING,is_child STRING,mother_baby STRING,child_stage STRING,is_college_students INT,income_power STRING,
consume_power STRING,consumer_grade STRING,profession STRING) tblproperties ("transactional"="true");
insert into lq_dc_feature_tag_202012_end_new_update
select * from lq_dc_feature_tag_202012_end_new

导出到OSS

 create  EXTERNAL  table test_demo(id STRING)
	STORED AS  `PARQUET`  LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/bucket/test_demo';
	INSERT  into test_demo
	select * from id_tables ;