Hive1 HIVE QL

after you have configured jvm,sshd service with no private key authorized and hadoop .you can install the hive a dw tools based on the hadoop platform.It's a little different from the SQL,now let's start the basic hive ql.

HIVE QL

4.1DDL

显示数据库：showdatabases;

建数据库createdatabase db1;

切换数据库：usedb1;

显示所有表showtables;

显示一部分表名匹配的表 ：SHOW TABLES '.*s';

 

建表：

CREATE TABLE invites (foo INT, bar STRING)[ PARTITIONED BY (ds STRING)];

 

查看表结构

DESCRIBE  表名

修改表列名

Alter table PERSON age to age1

增加表列

Alter table PERSON  add columns (ageINT);

 

删除表DROPTABLE表名;

 

4.2DML

 

导入数据：

LOAD DATA  INPATH  ‘文件地址'  OVERWRITE INTO TABLE表名;

 

 

 

4.3SQL

 

SELECTS and FILTERS

SELECT a.foo FROM invites a WHEREa.ds='2008-08-15';

ORDER BY

SELECT a.foo FROM invites a ORDER BY a.dsdesc/asc

 

INSERT OVERWRITETABLE表名

[PARTITION(..)]

select语句

 

 

GROUP BY

FROM invites a INSERT OVERWRITE TABLEevents SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar;

 

JOIN

FROM pokes t1 JOIN invites t2 ON (t1.bar =t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

 

 

多表插入：

From源表名

Insert OVERWRITE table表1名select字句1

Insert OVERWRITE table表2名select字句2

Insert OVERWRITE table表3名select字句3

 

子查询：

Select * from (select * from … where. …) twhere t.name=’zhangsan’

视图：

Create view 视图名称

As select语句