odpscmd使用:
源端配置好 config.ini:project,ak,sk service地址,tunnel地址
odpscmd 下载
odps@ odps_project>tunnel download dim_trans_flg_c0087 ./dim_trans_flg_c0087.txt;
目标端配置好 config.ini:project,ak,sk service地址,tunnel地址
odpscmd 上传
odps@ odps_project>tunnel upload dim_trans_flg_c0087.txt dim_trans_flg_c0087;
表、视图导入:
./odpscmd/bin/odpscmd --config=./odps_config_dest.ini -f odps_project_view.sql
获取当前登录用户:
odps@project_name>whoami;
退出MaxCompute客户端:
odps@project_name>quit;
--等价于如下命令。
odps@project_name>q;
获取命令帮助
方式一:在MaxCompute客户端查看命令帮助信息。
odps@project_name>help;
--等价于如下命令。
odps@project_name>h;
odps@project_name>help table;
--列举项目空间
list projects;
--进入项目空间
use cy1_odps;
--列举表名称
show tables;
where options include:
--help (-h)for help
--config=<config_file> specify another config file
--project=<prj_name> use project
--endpoint=<http://host:port> set endpoint
-k <n> will skip begining queries and start from specified position
-r <n> set retry times
-f <"file_path;"> execute command in file
-e <"command;[command;]..."> execute command, include sql command
where -e option include:
-e "<sql>;" execute sql
-e "quit;" quit
-e "list projects;" list projects
-e "use <prj_name>;" open project
-e "read <table_name> [<(col_name>[,..])][PARTITION <(partition_spec)>][line_num];"
read data from table
-e "add file <local_file> [as alias] [comment 'cmt'][-f];"
add file
-e "add archive <local_file> [as alias] [comment 'cmt'][-f];"
add archive
-e "add table <table_name> [partition (spec)] [as alias] [comment 'cmt'][-f];"
add table
-e "add jar <local_file.jar> [comment 'cmt'][-f];"
add jar
-e "get resource [<project name>:]<resource name> <path>;" download a file resource from server to path.
-e "create function <fun_name> as '<classname>' using '<res>,...';"
create function
-e "drop resource <res_name>;" drop resource
-e "drop function <fun_name>;" drop function
-e "list resources;" list resources
-e "list functions;" list functions
-e "show p [from startdate to enddate] [number];" show instances, date format: eg. 2012-08-27
-e "kill [-sync] <instanceid>;" stop instance
-e "set <key>=<value>;" set config value
-e "alias <key>=<value>;" alias config value
-e "show flags;" show set & alias config
-e "status <instanceid>;" get instance status
-e "show securityconfiguration;" show the access control config of the project
-e "list users|roles;" list all users or roles
-e "create|drop role <name>;" create or drop role
-e "add|remove user <name>;" add or remove user
-e "describe|desc role <rolename>;" desc the role
-e "grant <rolenamelist> to <username>;"
-e "revoke <rolenamelist> from <username>;"
-e "grant <privlist> on <objecttype> <objectname> to user|role <name>;"
-e "revoke <privlist> on <objecttype> <objectname> from user|role <name>;"
-e "grant super <privlist> to user <username>"
-e "revoke super <privlist> from user <username>"
-e "show grants [for <username>] [on type <objecttype>];"
-e "show acl for <objectname> [on type <objecttype>];"
-e "put policy <local_file> [on role <rolename>];" e.g put policy e:\policy.txt
-e "get policy [on role <rolename>];"
-e "set checkpermissionusingacl|checkpermissionusingpolicy|objectcreatorhasaccesspermission
|objectcreatorhasgrantpermission|projectprotection=true|false;"
-e "set labelsecurity=true|false;"
-e "set label <num> to user <username>;"
-e "set label <num> to table <tablename>[column_list];"
-e "grant label <num> on table <tablename>[column_list] to user <username> [with exp <days>];"
-e "revoke label on table <tablename>[column_list] from user <username>;"
-e "clear expired grants;"
-e "show label [level] grants [on table <tablename>] [for user <username>];"
-e "set projectprotection=true|false with exception <policyfile>;"
-e "add|remove trustedproject <projectname>;"
-e "list trustedprojects;"
-e "create|drop package <package name>;" Create or drop a package
-e "add <resource type> <resource name> to package <package name>;" Add a resource to the resource list of the package
-e "desc|describe_resource <project name>.<resource name>;" Describe a resource
-e "remove <resource type> <resource name> from package <package name>;" Remove a resource from the resource list of the package
-e "allow|disallow project <project name> to install package <package name> [using label <num>];" Allow or disallow a project to install the package
-e "show packages;" Show created package list and installed package list
-e "desc package [<project name>.]<package name>;" Display the details of the package, include resource list
-e "install|uninstall package <project name>.<package name>;" Install or uninstall a package
-e "tunnel upload <file> <table>;" Tunnel Command, could find usage in ODPS Documents.
-e "tunnel download <table> <file>;"
-e "export table <tablename>;"
-e "export <projectname> <local_path> [-rftpd];"
筋斗云日常维护:
select project_name,REGEXP_SUBSTR(regexp_replace(source_xml,'\n',''),'Query.*Query') as viewbilud from m_task where ds>='20220321' and lower(source_xml) like '%create view%' limit 2;
for ip in {59..68};do mysql -h172.24.23.${ip} -P3306 -uadmin -p -e "select version();";done
--分区表行数统计:
select b.name,sum(a.source_count),sum(a.destination_count),a.physical_size,a.table_id from max_compute_partition a,max_compute_table b where a.project_id='12' and a.table_id=b.id and b.partitioned='1' group by b.name;
--非分区表行数统计:
select b.name,a.source_count,a.destination_count,a.physical_size,a.table_id from max_compute_partition a,max_compute_table b where a.project_id='12' and a.table_id=b.id and b.partitioned='0';
dataworks进入对应项目空间:
普通表:drop table ${table_name}
分区表:alter table ${table_name} drop partition ${partition_filtercondition}
odps筋斗云元数据库:
mysql -h127.0.0.1 -uqianyi -p -D ladder
delete from max_compute_partition where project_id='5' and check_state <> 'PASS';
--任务是insert overwrite方式的列表
select file_name,content from file where app_id='10029' and file_delete=0 and content like '%\"truncate\":true%';
select project_name,name,type,partition_spec,owner_name,view_text from m_table where name='dim_trans_flg_c0087' and ds='20220321';
视图依赖项目空间查询:
explain select * from dim_bill_medium_c0084;
select a.file_name,a.file_folder_id,b.folder_item_id,b.folder_item_name from file a,folder b where a.file_delete=0 and a.file_folder_id=b.folder_id and use_type=0;
select status,gmt_create from datapro_compare_task_instance_step where gmt_create > order by gmt_create desc;
select status,gmt_create from datapro_compare_task_instance_step where gmt_create > '2022-04-15 11:15:00' and status != 2 and status != 3;
select distinct(state) from max_compute_partition;
select distinct(check_state) from max_compute_partition;
select distinct(state) from max_compute_table where project_id=23 and virtual_view<>1;
update max_compute_project set check_state='PASS',state='Finish' where id='';
delete from max_compute_partition where project_id='12' and check_state <> 'PASS';
delete from max_compute_table where project_id='5' and state="Error";
from(\s)+
odps常用命令
于 2022-08-17 10:48:09 首次发布