HiveQL操作简要 数据库和表的相关

--01建数据库
CREATE DATABASE IF NOT EXISTS DATABASE_NAME;
--02查询数据库
SHOW DATABASE;
SHOW DATABASE LIKE 'h.*'--Hive 并非支持所有的正则表达式功能
--03 修改数据库默认位置
CREATE DATABASE DATABASE_NAME LOCATION '/my/preferred/directory';
--04 查询数据库默认位置
describe DATABASE DATABASE_NAME;
--05 使用数据库
USE DATABASE_NAME;
--06 显示数据库名称
SET hive.cli.print.current.db=ture;
--07 关闭显示数据库名称
SET hive.cli.print.current.db=false;
--08 删除数据库
DROP DATABASE IF EXIT DATABASE_NAME;
默认情况下，hive 不允许删除一个包含有表的数据库，用户需要删除库中 的表，然后删除数据库；或者在删除表命令后加上关键字CASCADE,这样可以使hive自行删除该库中的表。
DROP DATABASE IF EXIT DATABASE_NAME CASCADE;
--09 删除数据库表
USE DATABASE_NAME;
DROP TABLE DATABASE_NAME;
--10 修改数据库
用户可以适用ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名
和数据库坐在的目录位置：
 ALTER DATABASE DATABASE_NAME SET DBPROPERTIES('edited-by','joe dba' );
删除或者充值数据库的信息是没有办法的。
--11 创建表
use ODS_CDH;
drop table if exists CDH_PRODUCT_ZLJ_TEST;
create table CDH_PRODUCT_ZLJ_TEST
(
  PRO_ID          string,
  PRODUCT_NAME    string,
  PRODUCT_NAME_TW string,
  PRODUCT_NAME_US string,
  SHOW_FLAG       INT,
  EDIT_FLAG       INT,
  SHOW_SORT       INT,
  PRODUCT_TYPE_ID int,
  QUERY_FLAG      INT,
  EXPORT_FLAG     INT
)comment 'CDH_PRODUCT_ZLJ_TEST oracle 到 hive 全量'
row format delimited fields terminated by '\001'
location '/ods/cdh/cbsdb/cdh_product_zlj_test';
--12 查询表 数据
SELECT * FROM CDH_PRODUCT_ZLJ_TEST;
--13 查询表结构
describe extended schema.DATABASE_NAME;
--14 查看字段信息
describe extended schema.DATABASE_NAME.cloumn_name;
--15 管理表与外部表
建表的时候如果是 带有 EXTERNAL 就是外部表，否则就是内部表。
CREATE EXTERNAL TABLE IF NOT EXISTS STOCKS (
EXCHANGE  STRING,
SYMBOL    STRING,
YMD       STRING,
PRICE_OPEN FLOAT,
PRICE_HIGH FLOAT,
PRICE_LOW  FLOAT,
PRICE_CLOSE FLOAT,
VOLUME      INT,
PRICE_ADJ_CLOSE FLOAT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/DATA/STOCKS';


用describe extended table_name;  Detailed Table Information 的最后可以看到  
tableType:MANAGED_TABLE (管理表)
或者  tableType:EXTERNAL_TABLE（外部表）;
--16 复制表结构不复制数据
CREATE EXTERNAL TABLE IF NOT EXISTS schema.tablename
LIKE mydb.employees
LOCATION '/path/to/data';
--17 分区表
用国家，省两个字段来对雇员进行分区建表
CREATE TABLE employees(
NAME STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deducations MAP<STRING,FLOAT>,
address struct<street:STRING,city:STRING,state:STRING,zip:INT>
)
PARTITION BY (country STRING,state STRING);--分区列列并没真正存储列，也就是不存于你的数据中。


--18 显示表的所有分区
SHOW PARTITIONS employees;
--19 查指定分区
SHOW PARTITIONS employees(country='US');
Detailed table employees; 也可以看到employees 信息。
--20 外部表分区 
CREATE EXTERNAL TABLE IF NOT EXISTS log_message(
hms INT,
severity STRING,
server STRING,
process_id INT,
message STRING
)PARTITION BY (YEAR INT,MONTH INT ,DAY INT)
ROW format delimted fields terminated BY '/t';
--21 加分区

ALTER TABLE log_message ADD PARTITION (YEAR = 2012,MONTH = 1 ,DAY = 1) LOCATION 'hdfs://master_server/data/log_message/2012/01/02';

--22 将分区下的数据拷贝到 Amazon S3 廉价的存储设备中 

hadoop distcp /DATA/log_message/2011/12/02 s3n: //ourbucket/logs/2011/12/02

--23 修改表，将分区路径指向到S3 路径

ALTER TABLE log_message ADD PARTITION (YEAR = 2011,MONTH = 12 ,DAY = 2) SET LOCATION 's3n://ourbucket/logs/2011/01/02';

--24 使用Hadoop fs -rmr 命令删除掉HDFS 中的这个分区的数据：

Hadoop fs -rmr /DATA/log_message/2011/12/02

--25 查看分区路径 
describe Extended log_message; 

describe Extended log_message partition（YEAR = 2012,MONTH = 1 ,DAY = 1）; 

--26 建表自定义表的存储格式
CREATE TABLE employees(
NAME STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deducations MAP<STRING,FLOAT>,
address struct<street:STRING,city:STRING,state:STRING,zip:INT>
)
ROW format delimited
fileds terminated BY '\001'
collection items terminated BY '\002'
MAP keys terminated BY '\003'
lines terminated BY '\n'

STORED AS textfile;--26 建表自定义表的存储格式 显示指定

--27 其他建表
用户可以指定第三方的输入以及serde。
还有一些额外 建表子句：


--28 删除表

DROP TABLE IF EXIT employees;

--29 表重命名

ALTER TABLE employees RENAME TO new_name;

--30 删除分区

ALTER TABLE employees DROP IF EXISTS PARTITION (YEAR = 2012,MONTH = 1 ,DAY = 1);

--31 修改列信息
--修改列的名字并改变列的位置 
--上面的语句作用是，修改hms列的名字为hours_minutes_seconds，并把它放在severity列之后。
ALTER TABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INT
COMMENT 'Thehours, minutes, and seconds part of the timestamp'

AFTER severity;

--32 增加列信息
--添加列
ALTER TABLE log_messages ADD COLUMNS (
app_name  STRING COMMENT 'Application name',

session_id STRING  COMMENT 'The current sessionid');

--33 删除或替换列信息
--替换列
ALTER TABLE log_messages REPLACE COLUMNS (
hours_mins_secs INT  COMMENT 'hour, minute, seconds fromtimestamp',
severity  STRING COMMENT 'The message severity'
message  STRING COMMENT 'The rest of the message');
 
上面的语句是重命名原来的hms列为hours_mins_secs，删除掉原来的server和process_id列。

但注意REPLACE语句只有在本地的SerDe 模式的表上使用，后面的章节会提到。

--34 修改表属性
--修改表的属性测试报错
ALTER TABLE log_messages SET TBLPROPERTIES

('notes' = 'The process idis no longer captured; this column is always NULL');

--35 修改存储属性
--修改存储属性
ALTER TABLE log_messages
PARTITION(year =2011, month = 1, day = 1)
SET FILEFORMAT SEQUENCEFILE;
 
ALTER TABLE stocks
CLUSTERED BY(exchange, symbol)
SORTED BY(symbol)

INTO 48 BUCKETS;

--36 其他修改表语句
--钩回语句
ALTER TABLE log_messages TOUCH
PARTITION (year =2012, month = 1, day = 1);
 
执行上面的语句后，当hive的外部文件被修改时，会触发一个钩回操作
 
--归档分区语句
ALTER TABLE log_messages ARCHIVE
PARTITION(year =2012, month = 1, day = 1);
 
归档分区仅是减少文件系统文件的数量，减少namenode的压力，不会减少空间使用。反操作语句是NOARCHIVE
 
--保护分区不会被删除
ALTER TABLE log_messages
PARTITION(year =2012, month = 1, day = 1) ENABLE NO_DROP;
 
hive (mydb)>ALTER TABLE logmsgs
           > PARTITION(year = 2014, month =1, day = 21) ENABLE NO_DROP;
hive (mydb)>ALTER TABLE logmsgs DROP IF EXISTS PARTITION(year = 2014, month = 1, day = 21);
FAILED:SemanticException [Error 30011]: Partition protected from being droppedmydb@logmsgs@year=2014/month=1/day=21
hive (mydb)>ALTER TABLE logmsgs
           > PARTITION(year = 2014, month =1, day = 21) disABLE NO_DROP;
OK
Time taken: 0.25seconds
hive (mydb)>ALTER TABLE logmsgs DROP IF EXISTS PARTITION(year = 2014, month = 1, day = 21);
Dropping thepartition year=2014/month=1/day=21
OK
Time taken:0.429 seconds
hive (mydb)>show partitions logmsgs;
OK
year=2014/month=1/day=20
year=2014/month=1/day=22
Time taken:0.105 seconds, Fetched: 2 row(s)
 
--保护分区不能被查询
ALTER TABLE log_messages
PARTITION(year =2012, month = 1, day = 1) ENABLE OFFLINE;