hive语句

1.      建表

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 DELIMITED  [FIELDS TERMINATED BY char] [LINES TERMINATED BY char]]

  [STORED AS file_format]

  [LOCATION hdfs_path]

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常

•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）

•LIKE 允许用户复制现有的表结构，但是不复制数据

•COMMENT可以为表与字段增加描述

•STORED AS

            SEQUENCEFILE

            | TEXTFILE

            | RCFILE   

            | INPUTFORMAT input_format_classname OUTPUTFORMAT             output_format_classname

       如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

创建简单表：

hive> CREATE TABLE pokes (foo INT, bar STRING);

创建外部表：

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,

     page_url STRING, referrer_url STRING,

     ip STRING COMMENT 'IP Address of the User',

     country STRING COMMENT 'country of origination')

 COMMENT 'This is the staging page view table'

 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'

 STORED AS TEXTFILE

 LOCATION '<hdfs_location>';

内部表存在于默认的目录下，删除后元数据和表的数据都没了，一般用于在事实表上抽取的各种纬度表。外部表存在与用户指定的目录下，只会删除元数据，表数据还存在，一般用于对接从外部load过来的事实表。

建分区表

CREATE TABLE par_table(viewTime INT, userid BIGINT,

     page_url STRING, referrer_url STRING,

     ip STRING COMMENT 'IP Address of the User')

 COMMENT 'This is the page view table'

 PARTITIONED BY(date STRING, pos STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘\t’

LINES TEMINATED BY '\n'

STORED AS SEQUENCEFILE;

建Bucket表

CREATE TABLE par_table(viewTime INT, userid BIGINT,

     page_url STRING, referrer_url STRING,

     ip STRING COMMENT 'IP Address of the User')

 COMMENT 'This is the page view table'

 PARTITIONED BY(date STRING, pos STRING)

 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

 ROW FORMAT DELIMITED

   FIELDS TERMINATED BY '\n'

STORED AS SEQUENCEFILE;

 

分区分桶表会在表目录下形成以分区值为名字的目录，然后该分区内的所有数据会按照桶数散列到相同数量的文件内，并按照指定的字段排好序，需要指出的是，向分桶表导入数据的时候需要指定分区和排序字段。然后hive会按照要求通过mapreduce来实现。

 

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

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list

  | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

注：1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。

2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。

3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。

例子：

1)建分区分桶表

hive> set hive.enforce.bucketing = true;

hive> set mapreduce.job.reduces=4;

hive> create table emp_buckets(id int,name string)

    > partitioned by (class string)

    > clustered by (name) into 4 buckets

    > row format delimited

    > fields terminated by ',';

2)通过select 语句向分桶表插入数据

insert overwrite table emp_bucket

select * from emp cluster by(name);

2.      展示目录结构

hive> dfs -ls /user/hive/warehouse/wyp ;

3.      Hive四种数据导入方式

3.1   load data local inpath 'wyp.txt' into table wyp;

3.2   load data inpath '/home/wyp/add.txt' into table wyp;

3.3    hive> insert into table test

    > partition (age='25')

> select id, name, tel from wyp;（静态分区）

           hive> insert overwrite table test

> PARTITION (age)

> select id, name, tel, age from wyp;（动态分区）

           Hive还支持多表插入

           hive> from wyp

           > insert into table test

           > partition(age)

           > select id, name, tel, age

           > insert into table test3

           > select id, name where age>25;

        3.4  hive> create table test4

               > as

               > select id, name, tel from wyp;

 

 

 

 

4.      创建一个表1和表2有一样的表结构

hive> create table new_table like old_table;

5.      展示表中的分区

hive> show partitions table_name;

6.      更新表的名称：

hive> ALTER TABLE old_name RENAME TO new_name;

7.      添加新一列

hive> ALTER TABLE table_name  ADD COLUMNS (new_col2 INT COMMENT 'a comment');

8.      删除表：

hive> DROP TABLE records;

删除表中数据，但要保持表的结构定义

hive> dfs -rmr /user/hive/warehouse/records;

9.      显示所有函数： hive> show functions;

查看函数用法： hive> describe function substr;

10.   模糊搜索表

hive模糊搜索表：    show tables like ‘*name*‘;

11.   将查询数据输出至目录

hive> INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='';

12.   将查询结果输出至本地目录

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

13.   创建视图

CREATE VIEW IF NOT EXISTS shipments(time, part 

COMMENT 'Time and parts for shipments.' 

TBLPROPERTIES ('creator' = 'me') 

AS SELECT ...;

14.   Hive-表连接

内连接：内连接指的是把符合两边连接条件的数据查询出来。

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

左外连接：左外连接：左边有，右边如果没有数据，那么为空。

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

右外连接：

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

全外连接

select * from user full outerjoin job on user.id=job.user_id;

左半连接

执行以下语句，左半连接用来代替in操作或者exists操作的

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

该语句相当于如下语句

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

但是，hive不支持in子句。所以只能变通，使用left semi子句。

 

Hive null值join会发生笛卡尔积

15.   Union

Hive12.0之前的版本不支持顶层union，只能将union封装在子查询中；且必须为union的查询输出定义别名，正确的hql如下：

SELECT *

FROM (

  select_statement

  UNION ALL

  select_statement

) unionResult

另外：

Union用于将多个SELECT语句的查询结果合并到一个结果集中，目前Hive只支持UNION ALL，也就是结果集中的重复记录不会被删除。SELECT语句返回列的数目和名称必须相同，否则会报schema错误。

16.   查看执行计划

Explain  hql语句  ：查看该语句的执行计划

17.   Hive数据类型

Int  double  string  bigint

18.   Hive子查询

1） Hive子查询0.12之前只能在from子句中使用，0.13后可以出现在where子句中

2） hive子查询必须为子查询设置别名

3） 子查询可以使用union all

4） 子查询中使用union all 时，在子查询里不能使用count、sum 等聚合函数

5） 两表在子查询中直接进行union all ，外围查询可以使用count、sum 等聚合函数

6）  

19.   Group by

1） 当使用group by字句，select语句，只能包含group by包含的列。当然，在select语句，可以有多个聚合函数(例如count)。

        cat命令使用：

cat主要有三大功能：
1.一次显示整个文件。$ cat filename
2.从键盘创建一个文件。$ cat > filename  
   只能创建新文件,不能编辑已有文件.
3.将几个文件合并为一个文件： $cat file1 file2 > file

覆盖原文件

# cat << EOF > test.sh
> #!/bin/bash
> #you Shell script writes here.
> EOF

追加文件

# cat << EOF >> test.sh
> #!/bin/bash
> #you Shell script writes here.
> EOF

 

UDF开发

}        继承org.apache.hadoop.hive.ql.UDF

}        •需要实现evaluate函数

}        •evaluate函数支持重载

}        •实现一进一出的操作

}        hive>add jar /opt/beifeng/jars/hive_UDF.jar;

}        hive>create temporary function getcmsid as ' com.ibeifeng.udf.GetCmsID ';

 

 

1

3

13

 

3.