hive常用sql語句有哪些

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

hive常用sql

1.數(shù)據(jù)類型

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

2.類型轉(zhuǎn)換

2.1 隱式類型轉(zhuǎn)換規(guī)則如下

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

（2）所有整數(shù)類型、FLOAT和STRING類型都可以隱式地轉(zhuǎn)換成DOUBLE。

（3）TINYINT、SMALLINT、INT都可以轉(zhuǎn)換為FLOAT。

（4）BOOLEAN類型不可以轉(zhuǎn)換為任何其它的類型。

2.2 可以使用CAST操作顯示進行數(shù)據(jù)類型轉(zhuǎn)換

例如CAST('1' AS INT)將把字符串'1' 轉(zhuǎn)換成整數(shù)1；如果強制類型轉(zhuǎn)換失敗，如執(zhí)行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數(shù)據(jù)定義

3.1 創(chuàng)建數(shù)據(jù)庫

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

3.2 查詢數(shù)據(jù)庫

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

3.3 切換數(shù)據(jù)庫

hive (default)> use db_hive;

3.4 修改數(shù)據(jù)庫

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

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');
--查看修改結(jié)果
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 刪除數(shù)據(jù)庫

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

3.6 創(chuàng)建表

--基本語法
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]
--創(chuàng)建內(nèi)部表
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';
--根據(jù)查詢結(jié)果創(chuàng)建表
create table if not exists student3 as select id, name from student;
--根據(jù)已經(jīng)存在的表創(chuàng)建表
create table if not exists student4 like student;
--查詢表的類型(外部表還是內(nèi)部表)
hive (default)> desc formatted student2;
--創(chuàng)建外部表(上傳數(shù)據(jù))
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; --刪除外部表,只刪除元數(shù)據(jù)不會刪除數(shù)據(jù)。

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

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

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

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

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

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

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

3.8 分區(qū)表

--引入分區(qū)表
/user/hive/warehouse/log_partition/20170702/20170702.log
/user/hive/warehouse/log_partition/20170703/20170703.log
/user/hive/warehouse/log_partition/20170704/20170704.log
--創(chuàng)建分區(qū)表 注意：分區(qū)字段不能是表中已經(jīng)存在的數(shù)據(jù)，可以將分區(qū)字段看作表的偽列。
hive (default)> create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
--加載數(shù)據(jù)到分區(qū)表中 注意：分區(qū)表加載數(shù)據(jù)時，必須指定分區(qū)
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’);
--查詢分區(qū)表數(shù)據(jù)
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';

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

3.9 兩級分區(qū)表

--創(chuàng)建二級分區(qū)表
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';
--正常加載數(shù)據(jù)
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table
 default.dept_partition2 partition(month='201709', day='13');
--查詢分區(qū)數(shù)據(jù)
hive (default)> select * from dept_partition2 where month='201709' and day='13';

3.10 分區(qū)關(guān)聯(lián)修復

方式一:上傳數(shù)據(jù)后修復

--上傳數(shù)據(jù)
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;
--查詢不到剛上傳的數(shù)據(jù)
hive (default)> select * from dept_partition2 where month='201709' and day='12';
--執(zhí)行修復命令
hive> msck repair table dept_partition2;
--再次查詢數(shù)據(jù)
hive (default)> select * from dept_partition2 where month='201709' and day='12';

方式二:上傳數(shù)據(jù)后添加分區(qū)

--上傳數(shù)據(jù)
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;
--執(zhí)行添加分區(qū)
hive (default)> alter table dept_partition2 add partition(month='201709',day='11');
--查詢數(shù)據(jù)
hive (default)> select * from dept_partition2 where month='201709' and day='11';

方式三:創(chuàng)建文件夾后load數(shù)據(jù)到分區(qū)

--創(chuàng)建目錄
hive (default)> dfs -mkdir -p  /user/hive/warehouse/dept_partition2/month=201709/day=10;
--上傳數(shù)據(jù)
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');
-- 查詢數(shù)據(jù)
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 數(shù)據(jù)導入

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：以追加數(shù)據(jù)的方式插入到表或分區(qū)，原有數(shù)據(jù)不會刪除

insert overwrite：會覆蓋表或分區(qū)中已存在的數(shù)據(jù)

注意：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 數(shù)據(jù)導出

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

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