Hadoop-Hive常用基礎HQL文

一.データベース#データベース#
1.データベース・リストの問合せ

show databases ;

2.指定したデータベースの使用

use default;

3.データベースの説明を表示する

desc database extended db_hive_03 ;

二.表
1.クエリー・テーブルのリスト

show tables ;

2.クエリー・テーブルの説明:

desc student ;
desc extended student ;
desc formatted student ;

3.テーブルの作成

create table student(
id int, 
name string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data local inpath '/opt/datas/student.txt'into table student ;

4.テーブルを作成し、テーブルの構造とデータをコピーする

create table if not exists default.dept_cats as select * from dept ;

5.別のテーブルの構造を使用して新しいテーブルを作成する

create table if not exists default.dept_like like default.dept ;

6.テーブルを空にする:

truncate table dept_cats ;

7.テーブルの削除

drop table if exists dept_like_rename ;

8.表名の変更

alter table dept_like rename to dept_like_rename ;

9.クエリー・テーブル

select * from student ;
select id from student ;

三.機能関数:
1.機能関数のリストを表示する

show functions ;

2.機能関数の説明を表示する

desc function upper ;

3.クエリー機能関数の拡張情報

desc function extended upper ;

4.機能関数のテスト

select id ,upper(name) uname from db_hive.student ;

四.ステップ:
1.外部テーブルを作成し、インポート・ファイルの場所とフィールド分割子を指定します.

create EXTERNAL table IF NOT EXISTS default.emp_ext2(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/user/hive/warehouse/emp_ext2';

2.パーティション表の作成:

create EXTERNAL table IF NOT EXISTS default.emp_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by (month string,day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

3.パーティション表へのデータのインポート:

load data local inpath '/usr/local/app/hive_test/emp.txt' into table default.emp_partition partition (month='201805',day='31') ;

4.パーティション表のリストを表示するには:

show partitions emp_partition ;

5.パーティション表のデータを問い合わせる:

select * from emp_partition where month = '201509' and day = '13' ;

6.hiveにデータをロードする:

1）       hive 
load data local inpath '/opt/datas/emp.txt' into table default.emp ;

2）  hdfs   hive 
load data inpath '/user/beifeng/hive/datas/emp.txt' overwrite into table default.emp ;

3）             
load data inpath '/user/beifeng/hive/datas/emp.txt' into table default.emp ;

4）      insert  
create table default.emp_ci like emp ;
insert into table default.emp_ci select * from default.emp ;

5）        location    

7.hiveからファイルへ:

insert overwrite local directory '/opt/datas/hive_exp_emp'
select * from default.emp ;

insert overwrite local directory '/opt/datas/hive_exp_emp2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '
'
select * from default.emp ;

bin/hive -e "select * from default.emp ;" > /opt/datas/exp_res.txt

8.クエリーの結果をローカルファイルにエクスポートします.

insert overwrite directory '/hive_test/export_emp.txt' select * from emp;

select * from emp ;
select t.empno, t.ename, t.deptno from emp t ;

五.ステップクエリー:
1. = >= <= between and

select * from emp limit 5 ;
select t.empno, t.ename, t.deptno from emp t where  t.sal between 800 and 1500 ;

2. is null/is not null/in/not in

select t.empno, t.ename, t.deptno from emp t where comm is null ;

3. max/min/count/sum/avg

select count(*) cnt from emp ;
select max(sal) max_sal from emp ;
select sum(sal) from emp ;
select avg(sal) from emp ;

4.groupby/havingグループ

emp 
*          
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno ;

*               
select t.deptno, t.job, max(t.sal) avg_sal from emp t group by t.deptno, job ;

5. >>>having

* where            
* having            

            2000   

select deptno, avg(sal) from emp group by deptno ;
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

6.join 2つのテーブルを接続する

##  jion  join ... on
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno ;

##     left join
select e.empno, e.ename, d.deptno, d.dname  from emp e left join dept d on e.deptno = d.deptno ;

##     right join
select e.empno, e.ename, e.deptno, d.dname  from emp e right join dept d on e.deptno = d.deptno ;

##     full join
select e.empno, e.ename, e.deptno, d.dname  from emp e full join dept d on e.deptno = d.deptno ;

六.クライアント構成と起動と停止
1.CLIクライアントコマンドを閉じる:

exit

#  hive  ,  exit,     ctrl+c,      ,         .

2.hive起動時に構成属性情報を設定する

$ bin/hive --hiveconf 

3.現在のすべての構成情報を表示

hive > set ;

hive (db_hive)> set system:user.name ;
system:user.name=beifeng

4.ヘルプの表示

[beifeng@hadoop-senior hive-0.13.1]$ bin/hive -help

5.sql文の実行

* bin/hive -e 
eg:
bin/hive -e "select * from db_hive.student ;"

6.指定したファイルの実行

* bin/hive -f 
eg:
$ touch hivef.sql
select * from db_hive.student ;
$ bin/hive -f /opt/datas/hivef.sql 

#              
$ bin/hive -f /opt/datas/hivef.sql > /opt/datas/hivef-res.txt

7.hive cliコマンドウィンドウでhdfsファイルシステムを表示する方法

hive (default)> dfs -ls / ;  

8.hive cliコマンドウィンドウでローカルファイルシステムを表示する方法

hive (default)> !ls /opt/datas ；