Hive笔记3-HQL操作

朱雀 2022-03-02 12:38 1068阅读 0赞

## **HQL操作** ##

参考资料：

*  [Hive详细介绍及简单应用][Hive]
 *  [Hive基本操作，DDL操作(创建表，修改表，显示命令)，DML操作(Load Insert Select),Hive Join,Hive Shell参数(内置运算符、内置函数)等][Hive_DDL_DML_Load Insert Select_Hive Join_Hive Shell]
 *  [Hive入门及常用指令][Hive 1]
 *  [Hive分区、分桶操作及其比较][Hive 2]
 *  [函数分类，HIVE CLI命令，简单函数，聚合函数，集合函数，特殊函数(窗口函数，分析函数，混合函数，UDTF)，常用函数Demo][HIVE CLI_UDTF_Demo]

### **1、DDL操作** ###

#### **（1）显示命令** ####

*  显示数据库：`show databases;`
 *  创建数据库：`CREATE DATABASE|SCHEMA [IF NOT EXISTS] <database name>`
 *  删除数据库：`DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];`
 *  进入数据库：`use database_name;`
 *  显示表：`show tables;`
 *  描述表结构：`desc table_name;`
 *  显示分区：`show partitions;`

#### **（2）创建表** ####

##### **建表语法** #####

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]

说明：

1.  CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 `IF NOT EXISTS` 选项来忽略这个异常。
2.  EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3.  LIKE 允许用户复制现有的表结构，但是不复制数据。
4.  ROW FORMAT row\_format
    
        ROW FORMAT 
        
        DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] 
        
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
        
        | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
    
    用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。
5.  STORED AS
    
    SEQUENCEFILE|TEXTFILE|RCFILE
    
    如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
6.  CLUSTERED BY
    
    对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
    
    把表（或者分区）组织成桶（Bucket）有两个理由：
    
    （1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
    
    （2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
7.  字段类型
    
     *  基础数据类型：tinyint, smallint, int, bigint, float, decimal, boolean, varchar, char, string
     *  复合数据类型：struct, array, map

##### **具体实例** #####

1.  创建内部表mytable
    
        hive> create table if not exists mytable(sid int,sname string)  
        > row format delimited fields terminated by ',' stored as textfile;
    
    示例， 显示如下：
    
    ![70][]
2.  创建外部表pageview
    
        hive> create external table if not exists pageview(  
        > pageid int,  
        > page_url string comment 'The page URL'  
        > )  
        > row format delimited fields terminated by ','  
        > location 'hdfs://192.168.158.171:9000/user/hivewarehouse/';
3.  创建分区表invites
    
        hive> create table student_p(  
        > Sno int,  
        > Sname string,  
        > Sex string,  
        > Sage int,  
        > Sdept string)   
        > partitioned by(part string)   
        > row format delimited fields terminated by ','stored as textfile;
4.  创建带桶的表student
    
        hive> create table student(id int,age int,name string)  
        > partitioned by(stat_data string)  
        > clustered by(id) sorted by(age) into 2 buckets  
        > row format delimited fields terminated by ',';
    
    注意：
    
    1.  分桶之前要执行命令`hive.enforce.bucketiong=true;`
    2.  要使用关键字clustered by 指定分区依据的列名，还要指定分为多少桶。
    3.  与分区不同的是，分区依据的不是真实数据表文件中的列，而是我们指定的伪列，但是分桶是依据数据表中真实的列而不是伪列。所以在指定分区依据的列的时候要指定列的类型，因为在数据表文件中不存在这个列，相当于新建一个列。而分桶依据的是表中已经存在的列，这个列的数据类型显然是已知的，所以不需要指定列的类型。
5.  创建带复合数据类型的表

# array
    create table person(name string,work_locations array<string>)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t'
    COLLECTION ITEMS TERMINATED BY ',';
    # 数据
    biansutao beijing,shanghai,tianjin,hangzhou
    linan changchu,chengdu,wuhan
    # 入库数据
    LOAD DATA LOCAL INPATH '/home/hadoop/person.txt' OVERWRITE INTO TABLE person;
    select * from person;
    # biansutao       ["beijing","shanghai","tianjin","hangzhou"]
    # linan           ["changchu","chengdu","wuhan"]
    
    # map
    create table score(name string, score map<string,int>)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t'
    COLLECTION ITEMS TERMINATED BY ','
    MAP KEYS TERMINATED BY ':';
    # 数据
    biansutao '数学':80,'语文':89,'英语':95
    jobs '语文':60,'数学':80,'英语':99
    # 入库数据
    LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' OVERWRITE INTO TABLE score;
    select * from score;
    # biansutao       {"数学":80,"语文":89,"英语":95}
    # jobs            {"语文":60,"数学":80,"英语":99}
    
    # struct
    CREATE TABLE test(id int,course struct<course:string,score:int>)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t'
    COLLECTION ITEMS TERMINATED BY ',';
    # 数据
    1 english,80
    2 math,89
    3 chinese,95
    # 入库
    LOAD DATA LOCAL INPATH '/home/hadoop/test.txt' OVERWRITE INTO TABLE test;
    # 查询
    select * from test;
    # 1       {"course":"english","score":80}
    # 2       {"course":"math","score":89}
    # 3       {"course":"chinese","score":95}

#### **（3）修改表** ####

##### 内外部表转化 #####

alter table table_name set TBLPROPROTIES ('EXTERNAL'='TRUE'); # 内部表转外部表
    alter table table_name set TBLPROPROTIES ('EXTERNAL'='FALSE');# 外部表转内部表

##### **增加/删除分区** #####

语法结构

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
    
    partition_spec:
    
    : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
    
    ALTER TABLE table_name DROP partition_spec, partition_spec,...

具体实例

`alter table student_p add partition(part='a') partition(part='b');`

##### **重命名表** #####

语法结构

ALTER TABLE table_name RENAME TO new_table_name

具体实例

`hive> alter table student rename to student1;`

![70 1][]

##### **增加/更新列** #####

语法结构

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

*注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。*

具体实例

![70 2][]

![70 3][]

##### 修改字段 #####

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

### **2、DML操作** ###

#### **（1）Load** ####

**语法结构**

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO 
    
    TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明：

1.  Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。
2.  filepath：
    
     *  相对路径，例如：project/data1
     *  绝对路径，例如：/user/hive/project/data1
     *  包含模式的完整 URI，列如：hdfs://namenode:9000/user/hive/project/data1
3.  LOCAL关键字
    
     *  如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。
     *  如果没有指定 LOCAL 关键字，则根据inpath中的uri查找文件
4.  OVERWRITE 关键字
    
     *  如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
     *  如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

**具体实例**

1.  加载相对路径数据

`hive> load data local inpath 'sc.txt' overwrite into table sc;`

1.  加载绝对路径数据

`hive> load data local inpath '/home/hadoop/hivedata/students.txt' overwrite into table student;`

1.  加载包含模式数据

`hive> load data inpath 'hdfs://mini1:9000/hivedata/course.txt' overwrite into table course;`

#### **(2)Insert** ####

##### 将查询结果插入Hive表 #####

**语法结构**

INSERT OVERWRITE [INTO] TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

Multiple inserts:

FROM from_statement 
    
    INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 
    
    [INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

Dynamic partition inserts:

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

**具体实例**

1.  基本模式插入
    
        insert overwrite table student partition(stat_date='20140101') 
        select id,age,name from student where stat_date='20131229';
2.  多插入模式
    
        from student 
        insert overwite table student partition(stat_date='20140102') 
        select id,age,name from student where stat_date='20131229';
        insert overwite table student partition(stat_date='20140103') 
        select id,age,name from student where stat_date='20131229';
3.  自动分区模式
    
        insert overwrite table student1 partition(stat_date) 
        select id,age,name,stat_date from student where stat_date='20140101';

##### 导出表数据 #####

**语法结构**

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

multiple inserts:

FROM from_statement
    
    INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
    
    [INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

**具体实例**

1.  导出文件到本地
    
        hive> insert overwrite local directory '/home/hadoop/hivedata/outdata'  
        > select * from student;
    
    说明：
    
    *数据写入到文件系统时进行文本序列化，且每列用^A来区分，\\n为换行符。用more命令查看时不容易看出分割符，*
    
    *可以使用: `sed -e 's/\x01/|/g' filename`来查看。*
    
    如：`sed -e 's/\x01/,/g' 000000_0`
2.  导出数据到HDFS
    
        hive> insert overwrite directory 'hdfs://mini1:9000/hivedata/outdatasc'  
        > select * from sc;

#### **（3）SELECT** ####

基本的Select操作

**语法结构**

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根据distribute by指定的内容将数据分到同一个reducer。
4.  Cluster by 除了具有Distribute by的功能外，还会对该字段进行排序。因此，常常认为cluster by = distribute by + sort by

**具体实例**

1.  获取年龄大的3个学生。
    
        hive> select sno,sname,sage from student order by sage desc limit 3;
2.  查询学生信息按年龄，降序排序。
    
        hive> select sno,sname,sage from student sort by sage desc;
        
        hive> select sno,sname,sage from student order by sage desc;  
        
        hive> select sno,sname,sage from student distribute by sage;
3.  按学生名称汇总学生年龄。
    
        hive> select sname,sum(sage) from student group by sname;

#### （4）综合示例 ####

# 增加分区
    insert overwrite table table_name partition (d='${pre_date}')
    
    # 建表语句
    # 进行分区，每个分区相当于是一个文件夹，如果是双分区，则第二个分区作为第一个分区的子文件夹
    drop table if exists employees;  
    create table  if not exists employees(  
           name string,  
           salary float,  
           subordinate array<string>,  
           deductions map<string,float>,  
           address struct<street:string,city:string,num:int>  
    ) partitioned by (date_time string, type string)
    row format delimited
    fields terminated by '\t'
    collection items terminated by ','
    map keys terminated by ':'
    lines terminated by '\n'
    stored as textfile
    location '/hive/...';
    
    # hive桶
    # 分区是粗粒度的，桶是细粒度的
    # hive针对某一列进行分桶，对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶中
    create table bucketed_user(id int, name string)
    clustered by (id) sorted by (name) into 4 buckets
    row format delimited
    fields terminated by '\t'
    stored as textfile;
    # 注意，使用桶表的时候我们要开启桶表
    set hive.enforce.bucketing=true;
    # 将employee表中的name和salary查询出来插入到表中
    insert overwrite table bucketed_user select salary, name from employees
    
    如果字段类型是string，则通过get_json_object提取数据；
    如果字段类型是struct或map,则通过col['xx']方式提取数据；

### **3、Hive Join** ###

#### 语法结构 ####

table_reference JOIN table_factor [join_condition]
    
    | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
    
    | table_reference LEFT SEMI JOIN table_reference join_condition

Hive 支持等值连接（equality joins）、外连接（outer joins）和（left/right joins）。Hive **不支持非等值的连接（后续版本已经支持）**，因为非等值连接非常难转化到 map/reduce 任务。

写 join 查询时，需要注意几个关键点：

**1. 只支持等值join**

例如：

SELECT a.* FROM a JOIN b ON (a.id = b.id)
    
    SELECT a.* FROM a JOIN b
    
    ON (a.id = b.id AND a.department = b.department)

是正确的，然而:

`SELECT a.* FROM a JOIN b ON (a.id>b.id)`

是错误的。

tips:后续版本已经可以支持不等值

**2. 可以 join 多于 2 个表**

例如

SELECT a.val, b.val, c.val FROM a JOIN b
    
    ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务，例如：

SELECT a.val, b.val, c.val FROM a JOIN b
    
    ON (a.key = b.key1) JOIN c
    
    ON (c.key = b.key1)

被转化为单个 map/reduce 任务，因为 join 中只使用了 b.key1 作为 join key。

而第一个例子的 join 被转化为 2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件，而 b.key2 用于第二次 join。

**3．join 时，每次 map/reduce 任务的逻辑：**

reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。例如：

SELECT a.val, b.val, c.val FROM a
    
    JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

所有表都使用同一个 join key（使用 1 次 map/reduce 任务计算）。Reduce 端会缓存 a 表和 b 表的记录，然后每次取得一个 c 表的记录就计算一次 join 结果，类似的还有：

SELECT a.val, b.val, c.val FROM a
    
    JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

这里用了 2 次 map/reduce 任务。第一次缓存 a 表，用 b 表序列化；第二次缓存第一次 map/reduce 任务的结果，然后用 c 表序列化。

**4．LEFT，RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况**

例如：

SELECT a.val, b.val FROM 
    
    a LEFT OUTER  JOIN b ON (a.key=b.key)

对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val，当 a.key=b.key 时，而当 b.key 中找不到等值的 a.key 记录时也会输出:

`a.val, NULL`

所以 a 表中的所有记录都被保留了；

`a RIGHT OUTER JOIN b`会保留所有 b 表的记录。

**5. Join 发生在 WHERE 子句之前**。

如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写。这里面一个容易混淆的问题是表分区的情况：

SELECT a.val, b.val FROM a
    
    LEFT OUTER JOIN b ON (a.key=b.key)
    
    WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

会 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的记录。WHERE 从句中可以使用其他列作为过滤条件。但是，如前所述，如果 b 表中找不到对应 a 表的记录，b 表的所有列都会列出 NULL，**包括 ds 列**。也就是说，join 会过滤 b 表中不能找到匹配 a 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b
    
    ON (a.key=b.key AND
    
    b.ds='2009-07-07' AND
    
    a.ds='2009-07-07')

这一查询的结果是预先在 join 阶段过滤过的，所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的 join 中。

**6. Join 是不能交换位置的。**

无论是 LEFT 还是 RIGHT join，都是左连接的。

SELECT a.val1, a.val2, b.val, c.val
    
    FROM a
    
    JOIN b ON (a.key = b.key)
    
    LEFT OUTER JOIN c ON (a.key = c.key)

先 join a 表到 b 表，丢弃掉所有 join key 中不匹配的记录，然后用这一中间结果和 c 表做 join。这一表述有一个不太明显的问题，就是当一个 key 在 a 表和 c 表都存在，但是 b 表中不存在的时候：整个记录在第一次 join，即 a JOIN b 的时候都被丢掉了（包括a.val1，a.val2和a.key），然后我们再和 c 表 join 的时候，如果 c.key 与 a.key 或 b.key 相等，就会得到这样的结果：`NULL, NULL, NULL, c.val`

#### 具体实例 ####

1.  获取已经分配班级的学生姓名

hive> select name,classname from student a join class b on (a.name=b.std_name);

1.  获取尚未分配班级的学生姓名

hive> select name,classname from student a left join class b on (a.name=b.std_name) where b.std_name is null;

1.  LEFT SEMI JOIN是IN/EXISTS的高效实现

hive> select id,name from student a left semi join class b on (a.name=b.std_name);

### 4、视图和索引 ###

创建视图的语法如下：

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]
    [COMMENT table_comment]
    AS SELECT ...

创建索引的语法如下：

CREATE INDEX index_name
    ON TABLE base_table_name (col_name, ...)
    AS 'index.handler.class.name'
    [WITH DEFERRED REBUILD]
    [IDXPROPERTIES (property_name=property_value, ...)]
    [IN TABLE index_table_name]
    [PARTITIONED BY (col_name, ...)]
    [
       [ ROW FORMAT ...] STORED AS ...
       | STORED BY ...
    ]
    [LOCATION hdfs_path]
    [TBLPROPERTIES (...)]

### 5、常用函数 ###

**![Center][]**

*  显示当前会话有多少函数可用：`SHOW FUNCTIONS;`
 *  显示函数的描述信息：`DESC FUNCTION concat;`
 *  显示函数的扩展描述信息：`DESC FUNCTION EXTENDED concat;`

注意：

*  hive 中最高精度的数据类型是 double, 只精确到小数点后 16 位，在做除法运算的时候要特别注意；
 *  如果A乘以B的结果超过默认结果类型的数值范围，则需要通过cast将结果转换成范围更大的数值类型；

# if 函数，如果满足条件，则返回A， 否则返回B
    if (boolean condition, T A, T B)
    # case 条件判断函数, 当a为b时则返回c；当a为d时，返回e；否则返回f
    case a when b then c when d then e else f end
    # 返回列表中第一个非空元素,如果所有值都为空，则返回null
    coalesce(v1, v2, v3, ...)
    # 去除两边空格
    trim()
    # 大小写转换
    lower(), upper()
    # 返回第二个参数在待查找字符串中的位置（找不到返回0）
    instr(string str, string search_str)
    # 字符串连接
    concat(string A, string B, string C, ...)
    # 自定义分隔符sep的字符串连接
    concat_ws(string sep, string A, string B, string C, ...)
    # 返回字符串长度
    length()
    # 反转字符串
    reverse()
    # 字符串截取
    substring(string A, int start, int len)
    # 按照pat字符串分割str，返回分割后的字符串数组
    split(string str, string pat)
    # 将字符串类型的数据读取为json类型，并得到其中的元素key的值
    # 第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用.读取对象或数组；
    get_json_object(string s, '$.key')
    # url解析
    # parse_url('http://facebook.com/path/p1.php?query=1','HOST')返回'facebook.com' 
    # parse_url('http://facebook.com/path/p1.php?query=1','PATH')返回'/path/p1.php' 
    # parse_url('http://facebook.com/path/p1.php?query=1','QUERY')返回'query=1'，
    parse_url()
    # 将字符串A中的符合java正则表达式pat的部分替换为C;
    regexp_replace(string A, string pat, string C)
    # 将字符串subject按照pattern正则表达式的规则进行拆分，返回index制定的字符
    # 0:显示与之匹配的整个字符串， 1：显示第一个括号里的， 2：显示第二个括号里的
    regexp_extract(string subject, string pattern, int index)
    # 类型转换
    cast(expr as type)
    # 将字符串转为map, item_pat指定item之间的间隔符号，dict_pat指定键与值之间的间隔
    str_to_map(string A, string item_pat, string dict_pat)
    # 提取出map的key, 返回key的array
    map_keys(map m)
    # 日期函数
    # 日期比较函数，返回相差天数，datediff('${cur_date},d)
    datediff(date1, date2)
    # 返回当前时间
    from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')
    # explode就是将hive一行中复杂的array或者map结构拆分成多行
    explode(colname)
    # lateral view 将一行数据adid_list拆分为多行adid后，使用lateral view使之成为一个虚表adTable，使得每行的数据adid与之前的pageid一一对应, 因此最后pageAds表结构已发生改变，增加了一列adid
    select pageid, adid from pageAds
    lateral view explode(adid_list) adTable as adid

### **6、自定义函数UDF和Transform** ###

当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

#### 1. 自定义函数类别 ####

*  UDF作用于单个数据行，产生一个数据行作为输出。（数学函数，字符串函数）
 *  UDAF（用户定义聚集函数）：接收多个输入数据行，并产生一个输出数据行。（count，max）

#### 2. UDF开发实例 ####

前期准备，要把hive的lib包导入到工程中，其中UDF依赖的是hive-exec-1.2.1.jar。也就是说要把apache-hive-1.2.1-bin\\lib中内容都引入到工程中。若用到hadoop中的一些api，请把hadoop的api也引入进去。

1、先开发一个java类，继承UDF，并重载evaluate方法

package cn.lyx.bigdata.udf
    
    import org.apache.hadoop.hive.ql.exec.UDF;
    import org.apache.hadoop.io.Text;
    
    public final class ToLowerCase extends UDF{
    
        public Text evaluate(final Text s){
            if(s==null){return null;}        
            return new Text(s.toString().toLowerCase());
        }
    }

2、打成jar包上传到服务器

3、将jar包添加到hive的classpath

`hive>add JAR /home/hadoop/udf.jar;`

4、创建临时函数与开发好的java class关联

`hive>create temporary function toLowerCase as 'cn.lyx.bigdata.udf.ToLowerCase';`

5、即可在hql中使用自定义的函数toLowerCase

`select toLowerCase(name),age from t_test;`

#### **3、Transform实现** ####

Hive的 TRANSFORM 关键字\*\**提供了在SQL中调用自写脚本的功能*\*\*

适合实现Hive中没有的功能又不想写UDF的情况

使用示例1：下面这句sql就是借用了weekday\_mapper.py对数据进行了处理.

CREATE TABLE u_data_new (
    movieid INT,
    rating INT,
    weekday INT,
    userid INT)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t';
    add FILE weekday_mapper.py;
    
    INSERT OVERWRITE TABLE u_data_new
    SELECT
    TRANSFORM (movieid, rating, unixtime,userid)
    USING 'python weekday_mapper.py'
    AS (movieid, rating, weekday,userid)
    FROM u_data;

其中weekday\_mapper.py内容如下

#!/bin/python
    
    import sys
    import datetime
    
    for line in sys.stdin:
        line = line.strip()
        movieid, rating, unixtime,userid = line.split('\t')
        weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
        print '\t'.join([movieid, rating, str(weekday),userid])

使用示例2：下面的例子则是使用了shell的cat命令来处理数据

FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-08';

[Hive]: https://blog.csdn.net/l1212xiao/article/details/80432759
[Hive_DDL_DML_Load Insert Select_Hive Join_Hive Shell]: https://yq.aliyun.com/articles/100907?spm=a2c4e.11153940.blogcont100908.17.6499593bkmkYe8#15
[Hive 1]: https://blog.csdn.net/ddydavie/article/details/80667727
[Hive 2]: https://yq.aliyun.com/articles/100908?spm=a2c4e.11153940.blogcont100909.15.643c2d2d5EOtn8
[HIVE CLI_UDTF_Demo]: https://yq.aliyun.com/articles/100909?spm=a2c4e.11153940.blogcont100910.22.54efc3ceJEMMqo
[70]: /images/20220302/29fe62e6d3f74edaa302e95b50fe9dbf.png
[70 1]: /images/20220302/c1983d9538684ebaa93382f8a674ab17.png
[70 2]: /images/20220302/52900eecd1184e16b71509d20886bea8.png
[70 3]: /images/20220302/d1d1fbcc83ea4b9eae52be133360b551.png
[Center]: /images/20220302/ae0fa9ecd0e8406c82358692de202049.png