Hive语法及其进阶(一)「建议收藏」

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说Hive语法及其进阶(一)「建议收藏」,希望您对编程的造诣更进一步.

Hive语法及其进阶(一)

1、Hive完整建表

 1 CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name(  2       [(col_name data_type [COMMENT col_comment], ...)]  3  )  4       [COMMENT table_comment]
 5       [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  6       [CLUSTERED BY (col_name, col_name, ...)  7  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]  8       [
 9  [ROW FORMAT row_format] 
10        [STORED AS file_format]
11        | STORED BY "storage.handler.class.name" [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0) 12  ] 13       [LOCATION hdfs_path]
14       [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0) 15       [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

注意:
　　　　[]:表示可选
　　　　EXTERNAL:外部表
　　　　(col_name data_type [COMMENT col_comment],…:定义字段名，字段类型
　　　　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:指定Hive储存格式：textFile、rcFile、SequenceFile 默认为：textFile
　　　　LOCATION hdfs_path:指定储存位置(默认位置在hive.warehouse目录下)
　　　　TBLPROPERTIES (property_name=property_value, …):跟外部表配合使用，比如：映射HBase表，然后可以使用HQL对hbase数据进行查询，当然速度比较慢
　　　　AS select_statement:从别的表中加载数据 select_statement=sql语句

2、使用默认方式建表

1 create table students01 2  ( 3             id bigint, 4  name string, 5             age int, 6  gender string, 7  clazz string 8  ) 9         ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

注意:
分割符不指定,默认不分割
通常指定列分隔符,如果字段只有一列可以不指定分割符：

ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

3、建表2：指定location

 1 create table students02  2  (  3             id bigint,  4  name string,  5             age int,  6  gender string,  7  clazz string  8  )  9         ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
10         LOCATION "data";

Hive语法及其进阶(一)「建议收藏」

4、建表3：指定存储格式

 1  create table student_rc  2  (  3             id bigint,  4  name string,  5             age int,  6  gender string,  7  clazz string  8  )  9         ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
10         STORED AS rcfile;

Hive语法及其进阶(一)「建议收藏」

注意:

　　　　指定储存格式为rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile

注意：

　　　　除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。

Hive语法及其进阶(一)「建议收藏」

5、建表4：从其他表中加载数据
　　格式:
　　　　create table xxxx as select_statement(SQL语句) (这种方式比较常用)

　　例子:
　　　　　create table students4 as select * from students2;

Hive语法及其进阶(一)「建议收藏」

6、建表5：从其他表中获取表结构

　　格式:
　　　　create table xxxx like table_name 只想建表，不需要加载数据

　　例子：

　　　 create table student04 like students;

Hive语法及其进阶(一)「建议收藏」

7.Hive加载数据

　　　　1、使用“`hadoop dfs -put “本地数据” “hive表对应的HDFS目录下

Hive语法及其进阶(一)「建议收藏」

　　　　2、使用 load data inpath（是对hdfs的文件移动，移动，移动，不是复制）

　　 3、使用load data local inpath（经常使用，从本地文件中上传）

Hive语法及其进阶(一)「建议收藏」　　　

　　　　// overwrite 覆盖加载
　　　　// 实际上就是hadoop执行了rmr然后put操作
　　　　例如：load data local inpath”/usr/local/data/students.txt” overwrite into table student01;

Hive语法及其进阶(一)「建议收藏」

方式1和方式2的区别:

　　　　　　　　　　1.上传数据到hdfs目录和hive表没有任何关系(不需要数据格式进行匹配,hive读取数据还是需要数据格式的匹配)

　　　　　　　　　　2.上传数据到hive表和hive表有关系(需要数据格式进行匹配)

8. 清空表
　　　　truncate table student01;

注意：清空代表清空数据，不是删除表

Hive语法及其进阶(一)「建议收藏」

11. insert into table xxxx SQL语句（没有as）传输给别的格式的hive table

　　例如：

　　　　insert into table student04 select * from student01;

Hive语法及其进阶(一)「建议收藏」

　　覆盖插入把into 换成 overwrite

　　　　例如：

　　　　　　insert overwrite table student04 select * from student01;

Hive语法及其进阶(一)「建议收藏」

9、Hive 内部表（Managed tables）vs 外部表（External tables）

区别:

　　　　内部表删除数据跟着删除
　　　　外部表只会删除表结构,数据依然存在

注意:

　　　　公司中实际应用场景为外部表,为了避免表意外删除数据也丢失
　　　　不能通过路径来判断是目录还是hive表(是内部表还是外部表)

建表：

 1 内部表  2 create table students_managed01  3 (  4     id bigint,  5  name string,  6     age int,  7  gender string,  8  clazz string  9 ) 10 ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

Hive语法及其进阶(一)「建议收藏」

 1 //内部表指定location  2 create table students_managed02  3 (  4     id bigint,  5  name string,  6     age int,  7  gender string,  8  clazz string  9 ) 10 ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
11 LOCATION "/managed";

Hive语法及其进阶(一)「建议收藏」

 1 // 外部表  2 create external table students_external01  3 (  4     id bigint,  5  name string,  6     age int,  7  gender string,  8  clazz string  9 ) 10 ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

Hive语法及其进阶(一)「建议收藏」

 1 // 外部表指定location  2 create external table students_external02  3 (  4     id bigint,  5  name string,  6     age int,  7  gender string,  8  clazz string  9 ) 10 ROW FORMAT DELIMITED FIELDS TERMINATED BY ","; 11 LOCATION "/external";

Hive语法及其进阶(一)「建议收藏」

上传数据：

hive> load data local inpath "/usr/local/data/students.txt"into table students_managed01;hive> load data local inpath "/usr/local/data/students.txt"into table students_managed02;
hive> load data local inpath "/usr/local/data/students.txt"into table students_external01;hive> load data local inpath "/usr/local/data/students.txt"into table students_external02;

删除数据：

hive> drop table students_managed01; hive> drop table students_managed02; hive> drop table students_external01; hive> drop table students_external02;

Hive语法及其进阶(一)「建议收藏」

外部表与内部表总结：

　　　　可以看出，删除内部表的时候，表中的数据（HDFS上的文件）会被同表的元数据一起删除

　　　　删除外部表的时候，只会删除表的元数据，不会删除表中的数据（HDFS上的文件）

　　　　一般在公司中，使用外部表多一点，因为数据可以需要被多个程序使用，避免误删，通常外部表会结合location一起使用

　　　　外部表还可以将其他数据源中的数据映射到 hive中，比如说：hbase，ElasticSearch……

　　　　设计外部表的初衷就是让表的元数据与数据解耦

10、Hive建立分区表

1.创建单级分区

 1 create table students_pt  2 (  3     id bigint,  4  name string,  5     age int,  6  gender string,  7  clazz string  8 )  9 PARTITIONED BY(month string) 10 ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";