Sparksql Statement Summary

Verify the statements supported by hive, and the following statements are also supported by 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&NBSP;T1 (id int,name string);    show tables;     insert into  t1 values  (1, ' Smith '), (Ten, ' 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 '), (' 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;

Statements organized according to the Sparksql document (Http://spark.apache.org/docs/2.0.2/sql-programming-guide.html#supported-hive-features):

&LT;CREATE&NBSP;TABLE&NBSP;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 ', ';    create table t1  ') (species string,number  int, abstract string)     insert into t1 values ("A", 5, "a2002"), (" A "," a2200 "), (" B "," C "," b2000 "), (" B "," Ten, "" b2003 "), (" A "," a "," a "," a2003 "), (" C "," a "," c2002 "), (" C. "," 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); "Error"      SELECT * FROM t1 WHERE  (species =  ' a ')  OR  (number = &NBSP;5);    SELECT * FROM t1 WHERE  (species =  ' a ')  | |   (NUMBER&NBSP;=&NBSP;5); "Error"     select  get_json_object (' {' "store": {"fruit") : \[{"Weight": 8, "type": "Apple"},{"weight": 9, "type": "Pear"}],   "bicycle": {"Price": 19.95, "Color": "Red"} },  "Email": "[email protected]_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;  &Nbsp; 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&NBSP;T1&NBSP;WHERE&NBSP;NUMBER&NBSP;&GT;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; 

This article is from the "CVV" blog, make sure to keep this source http://2723554.blog.51cto.com/2713554/1963628

Sparksql Statement Summary