hive使用过的基本命令
命令：完成操作
hive：进去hive
show databases：显示 所有database
use wizad： 使用database wizad，或者如use aso
show tables：显示当前database下的所有表
show columns from table_name from database_name; 或show columns from database_name.table_name; ：显示表中列名称
show grants for user_name@localhost;：显示一个用户的权限，显示结果类似于grant 命令
show index from table_name;：显示表的索引
show logs;：显示BDB存储引擎的日志
show warnings;：显示最后一个执行的语句所产生的错误、警告和通知
show errors;：只显示最后一个执行语句所产生的错误
desc table_name：显示表信息(字段、分区)
desc formatted wizad_mdm_raw_hdfs;：显示表详细信息 (字段、分区、路径、格式等)
效果如下：
limit使用:
select * from app_data limit 100;
count:
select count(*) from app_data;
count非常耗费时间，下面有个别人写的小例子。
SELECT type ,
count(*) ,
count(DISTINCT u) ,
count(CASE WHEN plat=1 THEN u ELSE NULL) ,
count(DISTINCT CASE WHEN plat=1 THEN u ELSE NULL) ,
count(CASE WHEN (type=2 OR type=6) THEN u ELSE NULL) ,
count(DISTINCT CASE WHEN (type=2 OR type=6) THEN u ELSE NULL)
FROM t WHERE dt in ("2012-1-12-02", "2012-1-12-03")
GROUP BY type
ORDER BY type ;
hive表按partition查询，效率高：
show partitions table_name;：显示表table_name分区
show partitions wizad_mdm_raw_hdfs; ：
day=2014-12-20/adn=3
day=2014-12-21/adn=3
day=2014-12-21/adn=5
descformatted t2 partition (pt = '2014-10-17');：查看分区属性
建分区两种：
1，建表时建分区：
CREATE TABLE tab(
column1 STRING,
columnN STRING
) partitioned BY (day STRING);
alter table wizad_mdm_raw_hdfs add partition (day='2014-12-20',adn='3') location '/user/wizad/data/wizad/raw/2014-12-20/3_1/';
alter table wizad_mdm_raw_hdfs add partition (day='2014-12-20',adn='5') location '/user/wizad/data/wizad/raw/2014-12-20/5_1/';
删除分区：
ALTER TABLE wizad_mdm_raw_hdfs DROP PARTITION (day='2014-12-23',adn='3');
ALTER TABLE wizad_mdm_raw_hdfs DROP PARTITION (day='2014-12-23',adn='5');
修改表
ALTER TABLE old_table_name RENAME TO new_table_name;：重命名
ALTER TABLE tab1 ADD COLUMNS (c1 INT, c2 STRING);：添加字段
创建外部表：
CREATE EXTERNAL TABLE wizad_mdm_dev_lmj_mapping_cookie (
cookie_id STRING,
guid STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
stored as textfile
location '/user/wizad/tmp/mapping_cookie/';
格式是：
CREATE EXTERNAL TABLE tab (
column1STRING,
column2STRING,
column3STRING,
column4STRING,
columnNSTRING
) ROW FORMATDELIMITED
FIELDSTERMINATED BY '\t'
stored astextfile
location'hdfs://namenode/tmp/lmj/tab/';
hive语句执行环境相关参数设定：
SET mapred.job.queue.name=queue3;
SET hbase.client.scanner.caching=5000;
SET hbase.zookeeper.quorum=datanode06,datanode07,datanode08;
set zookeeper.znode.parent=/hbase;
set hbase.regionserver.lease.period=180000;
将查询结果保存到本地
INSERT OVERWRITE LOCAL DIRECTORY '/home/wizad/lmj/'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
select cookie_id,rowkey,fixeddim_map
from wizad_mdm_main_from_hbase as a JOIN wizad_mdm_dev_lmj_edition_20141120 as w
ON (w.guid = a.rowkey);
这里有一个优化的小例子：http://www.myexception.cn/internet/477451.html
