load data [local] inpath '...' [overwrite] into table t1 [partition(...)]

-- 如果有local是从linux磁盘加载，如果没带local是从hdfs的某个目录移动到hive中

-- 带上overwrite会覆盖掉表中原来的数据

-- partition 加载到指定分区

insert overwrite table t1 select * from t2;

--也可以不覆盖掉，直接导入，把关键词overwrite替换为into

-- 对于分区表稍微复杂

insert overwrite table t2 partition(day=2) select * from t1;

--有一张大表，可以通过这种方式构建小表。但是这种方式在多个分区的情况下效率很低，会对原始表执行多次扫描。

--将一张大表拆开写入分区表的时候应该使用下面的语法

from t1

insert overwrite table t2 partition(day=2) select * where day=2

insert overwrite table t2 partition(day=3) select * where day=3

insert overwrite table t4 select * where day=4

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.mode=nonstrict;

set hive.exec.max.dynamic.partitions.pernode=1000;

insert overwrite table t2 partition(province,city) select ...,province,city from a;

--动态分区的字段一定位于其他各个字段的最后

insert overwrite table t2 partition(province='beijing',city) select ...,province,city from a;

--前面指定了province的值的时候，在select中查不查province已经无所谓了（前面指定的值优先级高，但是不会对后面的查询结果进行过滤，就是说所有的province的值都会改为北京）

create table ... as select

create table t3 as select id from t1 where id =1

insert overwrite local directory 'localpath' select id from t1;

select * from t1 e;

select * from user join job on user.id=job.user_id;

-- 等价于

select * from user , job on user.id=job.user_id;

select * from user left outer join job on user.id=job.user_id;

select * from user right outer join job on user.id=job.user_id;

select * from user outer join job on user.id=job.user_id;

select * from user left semi join job on user.id=job.user_id;

--语句相当于：

select * from user where user.id in (select user_id from job);

select * from user join job;  

select * from user left join job on user.id=job.user_id where job.id=3;

select * from user left join (select * from job where id=3) job on user.id = job.user_id;

create view user_view as select * from user where id=1;

create table t1(...) stored as textfile;

--缺点：占用空间比较大

--表重命名

Alter TABLE table_name RENAME To new_table_name;

--修改表属性

ALTER TABLE table_name SET TBLPROPERTIES table_properties;

--修改注释

ALTER TABLE table_name SET SERDEPROPERTIES serde_paoperties;

--添加SerDe

ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];

ALTER TABLE table_name SET SERDEPROPERTIES serde_properties;

--修改表存储

ALTER TABLE table_name CLUSTERED BY(col_name,col_name...) [STORED BY(col_name...)] INTO num_buckets BUCKETS; 

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location1'] partition_spec [LOCATION 'location2'];

ALTER TABLE table_name Partition partition_spec RENAME TO PARTITION partition_spec;

MSCK REPAIR TABLE table_name;

--会把已经位于HDFS的分区目录中，但是在metastore中没有的分区信息，自动添加到metastore中。

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec, PARTITION partition_spec;

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec IGNORE PROTECTION;

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name col_type [COMMENT col_comment] [FIRST|ALTER column_name];

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment],...)] [COMMENT view_comment] [TBLPROPERTIES (property_name=property_value,...)] AS SELECT ...

--删除视图

DROP VIEW [IF EXISTS] view_name;

--修改视图定义

 ALTER VIEW view_name AS select_statement;

--显示数据库

SHOW DATABASES;

--显示表

SHOW TABLES [IN database_name];

--显示分区

SHOW PARTITIONS table_name;

--显示一部分分区

SHOW PARTITIONS [db_name].table_name [PARTITION(partition_desc)];

--显示表/分区扩展信息

SHOW TABLE EXTENDED [IN|FROM database_name]  LIKE identifier_with_wildcards [PARTITION(partition_desc)];  

[WITH CommonTableExpression (,CommonTableExpression)*]

SELECT [ALL|DISTINCT] select_expr,select_expr,...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list]

[[CLUSTER BY col_list] | [DISTRIBUTE BY col_list] [SORT BY col_list]]

[LIMIT number]

table_reference JOIN table_factor [join_condition]

| table_reference {LEFT | RIGHT |　FULL} [OUTER] JOIN table_reference join_condition

|table_reference LEFT SEMI JOIN table_reference join_condition

|table_reference CROSS JOIN table_reference [join_condition]

create table student_test(id INT,info struct<name:STRING,age:INT>)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY ':';

--FIELDS TERMINATED BY :字段之间的分隔符

--COLLECTION ITEMS TERMINATED BY :一个字段各item的分隔符

load data local  inpath 'data' into table student_test;

select info.age from student_test;

create table array_table(name STRING,student_id_list array<INT>)

row format delimited

fields terminated by ','

collection items terminated by ':';

load data local inpath 'data6' into table array_table;

select student_id_list[] from array_table;

create table map_table(id STRING,pref map<string,int>)

row format delimited

fields terminated by '\t'

collection items terminated by ','

map keys terminated by ':';

load data local inpath 'map_data' into table map_table;

select perf['person'] from map_table;

CREATE TABLE raw(line STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INPATH 'file.gz' INTO TABLE raw;

CREATE TABLE raw(line STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

CREATE TABLE raw_sequence(line STRING) STORED AS SEQUENCEFILE;

LOAD DATA LOCAL INPATH 'file.gz' INTO TABLE raw;

SET hive.exec.compress.output=true;

SET io.seqfile.compression.type=BLOCK; --NONE/RECORD/BLOCK

INSERT OVERWRITE TABLE raw_sequence SELECT * FROM raw;

select explode(array(1,23,4)) from dual;

select explode(map(1,11,2,22,4,44)) from dual;

select parse_url_tuple('http://www.test.com/a.html?key=k1&key2=k2#tag ','HOST','PATH','QUERY','QUERY:k1','QUERY:k2') from dual;