CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

--显示数据库
show databases;
show databases like 'db_hive*';

--显示数据库信息
desc database db_hive;
--显示数据库详细信息
desc database extended db_hive;
--切换数据库
use db_hive;
--修改数据库
alter database db_hive set dbproperties('createtime'='20170830');
--删除数据库
drop database db_hive2;
--强制删除
drop database db_hive cascade;

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS ] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

--内部表外部表转换
alter table student2 set tblproperties('EXTERNAL'='FALSE');

--查询表结构
desc dept_partition;

--修改表名
ALTER TABLE table_name RENAME TO new_table_name

--更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

--增加、替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

--删除表
drop table dept_partition;

load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

insert overwrite directory '/user/atguigu/student2'
	ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
    select * from student;
    
 export table default.student to
 '/user/hive/warehouse/export/student';

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [HAVING having_condition]
  [ORDER BY col_list]
  [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ]
  [LIMIT number]

CASE WHEN condition THEN result
	 WHEN condition THEN result
	 ELSE result
END

--用法
concat_ws('|', collect_set(t1.name)) name

select
    movie,
    category_name
from 
    movie_info 
    lateral view explode(category) table_tmp as category_name;

select 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from business;

select 
name,
orderdate,
cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, --前往第1行的数据
lag(orderdate,2) over (partition by name order by orderdate) as time2 --往前第2行的数据
from business;

select 
name, 
ntile(5) over(order by orderdate) sorted --把第5行分发到所有行
from business;

select 
name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

show functions;--查看所有函数
desc function upper;--查看函数用法
desc function extended upper;--查看函数详细

<property>
	<name>javax.jdo.option.ConnectionURL</name>     
	<value>jdbc:mysql://hadoop102:3306/metastore?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
	<description>JDBC connect string for a JDBC metastore</description>
</property>

（1）修改表字段注解和表注解：
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8；
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8；

（2）修改分区字段注解：
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set
utf8;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

hive (default)> create table student(id int COMMENT '学生ID', name string COMMENT '
学生姓名')
              > COMMENT '学生表'
              > PARTITIONED BY ( `date` string COMMENT '日期')
              > row format delimited
              > fields terminated by '\t';
desc formatted student;
查看是否能够正常显示中文