MaxCompute常用语句汇总(更新ing)

CREATE TABLE table_name (name string); 

CREATE TABLE table_name (name string) PARTITIONED BY (ds string); 

CREATE TABLE table_name (name string) 
PARTITIONED BY (ds string) LIFECYCLE 365;

CREATE TABLE table_name AS SELECT * FROM table_name2 ;

创建内部表,并从其他表复制表结构

CREATE TABLE table_name LIKE table_name2 ;

-- 创建表
CREATE TABLE IF NOT EXISTS dual (
    value BIGINT COMMENT 'value'
)
COMMENT 'dual';

-- 插入一条数据
INSERT OVERWRITE TABLE dual
SELECT COUNT(*)
FROM dual;

CREATE EXTERNAL TABLE IF NOT EXISTS table_name
(
a type,
)
STORED BY 'com.aliyun.odps.CsvStorageHandler' -- (1)
WITH SERDEPROPERTIES (
'odps.properties.rolearn'='acs:ram::1811270634786818:role/aliyunodpsdefaultrole'
) -- (2)
LOCATION 'oss://oss-cn-shanghai-internal.aliyuncs.com/oss-odps-test/Demo/'; -- (3)(4)

CREATE TABLE  IF NOT EXISTS  table_name (name string); 

SHOW TABLES;

SHOW TABLES LIKE 'yinlin*';

SHOW PARTITIONS <table_name>

ALTER TABLE table_name ADD COLUMNS (col_name1 type1)

ALTER TABLE table_name ADD COLUMNS (test type COMMENT '测试');

ALTER TABLE table_name RENAME TO new_table_name;

DROP TABLE table_name;

ALTER TABLE table_name CHANGE COLUMN old_col_name RENAME TO new_col_name;

1、创建一张表，结果为预期的表结构；

CREATE TABLE table2 (xxx);

2、把数据从原表导入到目标表，可以用SQL,比如:

INSERT OVERWRITE TABLE table2 SELECT * FROM table1;

3、更换表名：

ALTER TABLE table1 RENAME TO table1_bak;

ALTER TABLE table2 RENAME TO table1;

TUNNEL upload -fd '\t' c:\file.txt table_name;

TUNNEL d table_name c:\file.txt;

DESC table_name; 

SELECT * FROM table_name  WHERE foo='';

统计数据总量

SELECT COUNT(1) FROM table_name;

 INSERT OVERWRITE TABLE table_name SELECT a.bar, count(1) FROM table_name a WHERE a.foo > 0 GROUP BY a.bar;