sparkSQL语句总结

<CREATE DATABASE cvv_db;
SHOW DATABASES;
ALTER DATABASE cvv_db SET DBPROPERTIES(‘edited-by’=‘cvv54’);
USE DEFAULT;
DESCRIBE DATABASE cvv_db;
DROP DATABASE cvv_db;
CREATE TABLE t2_part(user_id BIGINT, firstname STRING, lastname STRING)
PARTITIONED BY(ds STRING);
CREATE TABLE t1(id int,name string);
SHOW TABLES;
INSERT INTO t1 VALUES (1,‘Smith’),(10,‘Joy’),(101,‘Jack’);
LOAD DATA LOCAL INPATH ‘/home/samba/data.sql’ INTO TABLE t1;
SELECT * FROM t1 WHERE id > 2;
INSERT OVERWRITE TABLE t1 VALUES(1,‘Smith’),(13,‘Joy’),(104,‘Jack’);
CREATE TABLE t3 AS SELECT * from t1;
CREATE VIEW t1_view AS SELECT id,name FROM t1 WHERE id>1;
ALTER VIEW t1_view SET TBLPROPERTIES (‘id’=‘view_id’);
ALTER VIEW t1_view RENAME TO new_t1_view;
ALTER VIEW new_t1_view AS SELECT id,name FROM t1 WHERE id<100;
DROP VIEW new_t1_view;
ALTER TABLE t1 RENAME TO new_t1;
ALTER TABLE new_t1 SET TBLPROPERTIES (‘ocean’=‘blue’);
SHOW TBLPROPERTIES new_t1;
ALTER TABLE new_t1 SET LOCATION ‘file:///home/samba/share’;
ALTER TABLE t2_part ADD PARTITION (ds=‘aaa’);
ALTER TABLE t2_part ADD PARTITION (ds=‘bbb’) LOCATION ‘file:///home/samba/share/bbb_new/’;
SHOW COLUMNS FROM new_t1;
DESCRIBE new_t1;
DESCRIBE t2_part PARTITION (ds=‘aaa’);
ALTER TABLE new_t1 SET SERDEPROPERTIES (‘id’=‘1’);
ALTER TABLE t2_part PARTITION (ds=‘aaa’) SET SERDEPROPERTIES (‘id’=‘1’);
SHOW PARTITIONS t2_part;
SHOW CREATE TABLE new_t1;
ANALYZE TABLE new_t1 COMPUTE STATISTICS FOR COLUMNS;
ALTER TABLE t2_part DROP PARTITION (ds=‘bbb’);
ALTER TABLE t2_part ENABLE NO_DROP;
ALTER TABLE t2_part ENABLE OFFLINE;
ALTER TABLE t2_part PARTITION (ds=‘aaa’) RENAME TO PARTITION (ds=‘new_aaa’);
DROP TABLE new_t1;
DROP TABLE t2_part;
DROP TABLE t2_part;
SHOW FUNCTIONS;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

根据sparkSQL文档（http://spark.apache.org/docs/2.0.2/sql-programming-guide.html#supported-hive-features）整理出的语句：

<create table t3(id int, name string,species string);
insert into table t3 values(1,“butterfly”,“a”),(2,“bee”,“b”),(3,“swallow”,“C”);
create table t2(species string,location string, height string);
insert into t2 values(‘a’,‘Hunan’,‘10000000000’),(‘b’,‘Beijing’,‘1231’),(‘c’,“Sili”,‘2000’);
create table t1 (species string,number int, abstract string);
insert into t1 values(“a”,5,“a2002”),(“a”,10,“a2200”),(“b”,20,“b2000”),(“b”,10,“b2003”),(“a”,32,“a2003”),(“c”,92,“c2002”),(“c”,20,“c2011”);
CREATE TABLE variables_new ( id INT, a_cost INT , b_cost INT ) STORED AS ORC TBLPROPERTIES(“transactional”=“true”);
select species,sum(number) as total from t1 group by species;
select species,number,abstract from t1 order by species;
select species,number,abstract from t1 cluster by species;
select species,number,abstract from t1 sort by species;
select species,number,abstract from t1 where number=10;
select species,number,abstract from t1 where (number=10) <=> (species=‘a’);
select species,number,abstract from t1 where number==10;
select species,number,abstract from t1 where number<>10;
select species,number,abstract from t1 where number<10;
select species,number,abstract from t1 where number>10;
select species,number,abstract from t1 where number<=10;
select species,number,abstract from t1 where number>=10;
SELECT 20+30 FROM t1;
SELECT 20-30 FROM t2;
SELECT 20*30 FROM t3;
SELECT 20/30 FROM t4;
SELECT 20%30 FROM t5;
SELECT * FROM t1 WHERE (species = ‘a’) AND (number = 5);
SELECT * FROM t1 WHERE (species = ‘a’) && (number = 5);【报错】
SELECT * FROM t1 WHERE (species = ‘a’) OR (number = 5);
SELECT * FROM t1 WHERE (species = ‘a’) || (number = 5);【报错】
select get_json_object(’{“store”:{“fruit”:[{“weight”:8,“type”:“apple”},{“weight”:9,“type”:“pear”}], “bicycle”:{“price”:19.95,“color”:“red”}}, “email”:“amy@only_for_json_udf_test.net”, “owner”:“amy” } ‘,’$.owner’) from t1;
select round(number) from t1;
select floor(number) from t1;
select count(number) from t1;
select sum(number) from t1;
select count(species) from t1;
select min(number) from t1;
select max(number) from t1;
select avg(number) from t1;
select round(avg(number)) from t1;
select ceil(avg(number)) from t1;
select rand() from t1;
select concat(species,abstract) from t1;
select substr(‘csdfslk’,2) from t1;
select substr(‘csdfslk’,2,3) from t1;
select upper(abstract) from t1;
select ucase(abstract) from t1;
select lcase(abstract) from t1;
select lower(abstract) from t1;
select trim(abstract) from t1;
select ltrim(abstract) from t1;
select rtrim(abstract) from t1;
select regexp_replace(“aaacdsfsd”,“a”,“c”) from t1;
select to_date(“1970-01-01 00:00:00”) from t1;
select sign(number) from t1;
select ln(number) from t1;
select cos(number) from t1;
select length(abstract) from t1;
select instr(abstract,“a”) from t1;
select printf(abstract) from t1;
ALTER TABLE new_t1 SET SERDEPROPERTIES (‘id’=‘1’);
select t1.species,t1.number,t1.abstract,t2.location,t2.height from t1,t2 where t1.species=t2.species;
select t1.species,t1.number,t1.abstract,t2.location,t2.height from t1 inner join t2 on t1.species=t2.species order by t1.number;
select t1.species,t1.number,t1.abstract,t2.location,t2.height from t1 full join t2 on t1.species=t2.species order by t1.number;
select t1.species,t1.number,t1.abstract,t2.location,t2.height from t1 left join t2 on t1.species=t2.species order by t2.number;
select t1.species,t1.number,t1.abstract,t2.location,t2.height from t1 right join t2 on t1.species=t2.species order by t3.number;
select t1.species,t1.number,t1.abstract,t2.location,t2.height from t1 join t2 on t1.species=t2.species order by t3.number;
select * from t1 outer join t2 ;
select * from t1 left semi join t2 on t1.species=t2.species order by t1.number;
select t1.species,t2.species,t1.number,t1.abstract,t2.location,t2.height from t1 cross join t2 on t1.species=t2.species order by t1.number;
select * from(select * from t1 where t1.number>5 UNION ALL select * from t2 ) nature JOIN t3 on (nature.species = t3.species);
select * from t3 where species in (select species from t1 where number >10);
EXPLAIN create table t6 as select * from t3;
create table t5 like t3;
EXPLAIN create table t6 as select * from t3;
alter table t6 rename to t66;

©著作权归作者所有：来自51CTO博客作者cvv54的原创作品，请联系作者获取转载授权，否则将追究法律责任
sparkSQL语句总结
https://blog.51cto.com/u_2723554/1963628