hive常用sql語句有哪些

這篇文章主要講解了“hive常用sql語句有哪些”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“hive常用sql語句有哪些”吧！

hive常用sql

1.數據類型

Hive有三種復雜數據類型ARRAY、MAP 和 STRUCT。ARRAY和MAP與Java中的Array和Map類似，而STRUCT與C語言中的Struct類似，它封裝了一個命名字段集合，復雜數據類型允許任意層次的嵌套。

2.類型轉換

2.1 隱式類型轉換規則如下

（1）任何整數類型都可以隱式地轉換為一個范圍更廣的類型，如TINYINT可以轉換成INT，INT可以轉換成BIGINT。

（2）所有整數類型、FLOAT和STRING類型都可以隱式地轉換成DOUBLE。

（3）TINYINT、SMALLINT、INT都可以轉換為FLOAT。

（4）BOOLEAN類型不可以轉換為任何其它的類型。

2.2 可以使用CAST操作顯示進行數據類型轉換

例如CAST('1' AS INT)將把字符串'1' 轉換成整數1；如果強制類型轉換失敗，如執行CAST('X' AS INT)，表達式返回空值 NULL。

0: jdbc:hive2://hadoop102:10000> select '1'+2, cast('1'as int) + 2;
+------+------+--+
| _c0  | _c1  |
+------+------+--+
| 3.0  | 3    |
+------+------+--+

3.DDL數據定義

3.1 創建數據庫

hive (default)> create database db_hive;
hive (default)> create database if not exists db_hive; --避免創建已存在的報錯
hive (default)> create database db_hive2 location '/db_hive2.db';--創建并指定在hdfs的存儲位置

3.2 查詢數據庫

hive> show databases;
hive> show databases like 'db_hive*'; --模糊查詢
hive> desc database db_hive; --顯示數據庫信息
hive> desc database extended db_hive; --顯示數據庫詳細信息

3.3 切換數據庫

hive (default)> use db_hive;

3.4 修改數據庫

用戶可以使用ALTER DATABASE命令為某個數據庫的DBPROPERTIES設置鍵-值對屬性值，來描述這個數據庫的屬性信息。數據庫的其他元數據信息都是不可更改的，包括數據庫名和數據庫所在的目錄位置。

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');
--查看修改結果
hive> desc database extended db_hive;
db_name comment location        owner_name      owner_type      parameters
db_hive         hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db    atguigu USER    {createtime=20170830}

3.5 刪除數據庫

hive>drop database db_hive2;
hive> drop database if exists db_hive2; --避免刪除的數據庫不存在
hive> drop database db_hive cascade; --避免數據庫不為空報錯

3.6 創建表

--基本語法
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]
--創建內部表
create table if not exists student2(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student2';
--根據查詢結果創建表
create table if not exists student3 as select id, name from student;
--根據已經存在的表創建表
create table if not exists student4 like student;
--查詢表的類型(外部表還是內部表)
hive (default)> desc formatted student2;
--創建外部表(上傳數據)
hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/datas/student.txt /student;

hive (default)> create external table stu_external(
id int, 
name string) 
row format delimited fields terminated by '\t' 
location '/student';
 drop table stu_external; --刪除外部表,只刪除元數據不會刪除數據。

3.7 管理表(內部表)與外部表的交換

hive (default)> desc formatted student2;  --查看表的類型
Table Type:             MANAGED_TABLE

alter table student2 set tblproperties('EXTERNAL'='TRUE'); --將內部表修改為外部表

hive (default)> desc formatted student2;  --查看表的類型
Table Type:             EXTERNAL_TABLE

alter table student2 set tblproperties('EXTERNAL'='FALSE'); --將外部表改成內部表

hive (default)> desc formatted student2;--查看表的類型
Table Type:             MANAGED_TABLE

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')為固定寫法，區分大小寫！

3.8 分區表

--引入分區表
/user/hive/warehouse/log_partition/20170702/20170702.log
/user/hive/warehouse/log_partition/20170703/20170703.log
/user/hive/warehouse/log_partition/20170704/20170704.log
--創建分區表 注意：分區字段不能是表中已經存在的數據，可以將分區字段看作表的偽列。
hive (default)> create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
--加載數據到分區表中 注意：分區表加載數據時，必須指定分區
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201708');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201707’);
--查詢分區表數據
hive (default)> select * from dept_partition where month='201709';
hive (default)> select * from dept_partition where month='201709'
              union
              select * from dept_partition where month='201708'
              union
              select * from dept_partition where month='201707';

--增加分區
hive (default)> alter table dept_partition add partition(month='201706') ;
--同時創建多個分區
hive (default)> alter table dept_partition add partition(month='201705') partition(month='201704');
--刪除分區
hive (default)> alter table dept_partition drop partition (month='201704');
hive (default)> alter table dept_partition drop partition (month='201705'), partition (month='201706');
--查看分區表有多少分區
hive> show partitions dept_partition;
--查看分區表結構
hive> desc formatted dept_partition;

3.9 兩級分區表

--創建二級分區表
hive (default)> create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (month string, day string)
               row format delimited fields terminated by '\t';
--正常加載數據
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table
 default.dept_partition2 partition(month='201709', day='13');
--查詢分區數據
hive (default)> select * from dept_partition2 where month='201709' and day='13';

3.10 分區關聯修復

方式一:上傳數據后修復

--上傳數據
hive (default)> dfs -mkdir -p
 /user/hive/warehouse/dept_partition2/month=201709/day=12;
hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;
--查詢不到剛上傳的數據
hive (default)> select * from dept_partition2 where month='201709' and day='12';
--執行修復命令
hive> msck repair table dept_partition2;
--再次查詢數據
hive (default)> select * from dept_partition2 where month='201709' and day='12';

方式二:上傳數據后添加分區

--上傳數據
hive (default)> dfs -mkdir -p
 /user/hive/warehouse/dept_partition2/month=201709/day=11;
hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=11;
--執行添加分區
hive (default)> alter table dept_partition2 add partition(month='201709',day='11');
--查詢數據
hive (default)> select * from dept_partition2 where month='201709' and day='11';

方式三:創建文件夾后load數據到分區

--創建目錄
hive (default)> dfs -mkdir -p  /user/hive/warehouse/dept_partition2/month=201709/day=10;
--上傳數據
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');
-- 查詢數據
hive (default)> select * from dept_partition2 where month='201709' and day='10';

3.11 修改表

hive (default)> alter table dept_partition2 rename to dept_partition3; --重命名
hive (default)> alter table dept_partition add columns(deptdesc string); --添加列
hive (default)> alter table dept_partition change column deptdesc desc int; --更新列
hive (default)> alter table dept_partition replace columns(deptno string, dname
 string, loc string); --替換列

4.DML操作

4.1 數據導入

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

insert into table  student partition(month='201709') values(1,'wangwu'),(2,’zhaoliu’);

insert overwrite table student partition(month='201708') select id, name from student where month='201709';

insert into：以追加數據的方式插入到表或分區，原有數據不會刪除

insert overwrite：會覆蓋表或分區中已存在的數據

注意：insert不支持插入部分字段

hive (default)> from student
              insert overwrite table student partition(month='201707')
              select id, name where month='201709'
              insert overwrite table student partition(month='201706')
              select id, name where month='201709';

hive (default)> import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

4.2 數據導出

--將查詢結果導出到本地
insert overwrite local directory '/opt/module/datas/export/student' select * from student;
--將查詢的結果格式化導出到本地
insert overwrite local directory '/opt/module/datas/export/student1'
           ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;
--將查詢結果導出到hdfs上
insert overwrite directory '/user/atguigu/student2'
             ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
             select * from student;

感謝各位的閱讀，以上就是“hive常用sql語句有哪些”的內容了，經過本文的學習后，相信大家對hive常用sql語句有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！