odps常用命令

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)+