Hive入门笔记03

3.6 表（table）

3.6.1 创建表语法

1）普通建表

（1）完整语法

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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, ...)]

（2）关键字说明：

1、TEMPORARY

临时表，该表只在当前会话可见，会话结束，表会被删除。

2、EXTERNAL（重点）

外部表，与之相对应的是内部表（管理表）。管理表意味着Hive会完全接管该表，包括元数据和HDFS中的数据。而外部表则意味着Hive只接管元数据，而不完全接管HDFS中的数据。

3、data_type（重点）

Hive中的字段类型可分为基本数据类型和复杂数据类型。

基本数据类型如下：

Hive	说明	定义
tinyint	1byte有符号整数
smallint	2byte有符号整数
int	4byte有符号整数
bigint	8byte有符号整数
boolean	布尔类型，true或者false
float	单精度浮点数
double	双精度浮点数
decimal	十进制精准数字类型	decimal(16,2)
varchar	字符序列，需指定最大长度，最大长度的范围是[1,65535]	varchar(32)
string	字符串，无需指定最大长度
timestamp	时间类型
binary	二进制数据

复杂数据类型如下；

类型	说明	定义	取值
array	数组是一组相同类型的值的集合	array<string>	arr[0]
map	map是一组相同类型的键-值对集合	map<string, int>	map['key']
struct	结构体由多个属性组成，每个属性都有自己的属性名和数据类型	struct<id:int, name:string>	struct.id

注：类型转换

Hive的基本数据类型可以做类型转换，转换的方式包括隐式转换以及显示转换。

方式一：隐式转换

具体规则如下：

a. 任何整数类型都可以隐式地转换为一个范围更广的类型，如tinyint可以转换成int，int可以转换成bigint。

b. 所有整数类型、float和string类型都可以隐式地转换成double。

c. tinyint、smallint、int都可以转换为float。

d. boolean类型不可以转换为任何其它的类型。

方式二：显示转换

可以借助cast函数完成显示的类型转换

（1）语法

cast(expr as <type>)

（2）案例

hive (default)> select '1' + 2, cast('1' as int) + 2;

_c0    _c1

3.0     3

4、PARTITIONED BY（重点）

创建分区表

5、CLUSTERED BY ... SORTED BY...INTO ... BUCKETS（重点）

创建分桶表

6、ROW FORMAT（重点）

指定SERDE，SERDE是Serializer and Deserializer的简写。Hive使用SERDE序列化和反序列化每行数据。语法说明如下：

语法一：DELIMITED关键字表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的SERDE对每行数据进行序列化和反序列化。

ROW FORAMT DELIMITED 

[FIELDS TERMINATED BY char]

[COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char]

[LINES TERMINATED BY char]

[NULL DEFINED AS char]

注：

• fields terminated by ：列分隔符
• collection items terminated by ： map、struct和array中每个元素之间的分隔符
• map keys terminated by ：map中的key与value的分隔符
• lines terminated by ：行分隔符  语法二：SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE，可用于处理JSON字符串。

• ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]

  7、STORED AS（重点）

  指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。

  8、LOCATION

  指定表所对应的HDFS路径，若不指定路径，其默认值为

  ${hive.metastore.warehouse.dir}/db_name.db/table_name

  9、TBLPROPERTIES

  用于配置表的一些KV键值对参数

  2）Create Table As Select（CTAS）建表

  该语法允许用户利用select查询语句返回的结果，直接建表，表的结构和查询语句的结构保持一致，且保证包含select查询语句放回的内容。

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name

  [COMMENT table_comment] 

  [ROW FORMAT row_format] 

  [STORED AS file_format] 

  [LOCATION hdfs_path]

  [TBLPROPERTIES (property_name=property_value, ...)]

  [AS select_statement]

  3）Create Table Like语法

  该语法允许用户复刻一张已经存在的表结构，与上述的CTAS语法不同，该语法创建出来的表中不包含数据。

  CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

  [LIKE exist_table_name]

  [ROW FORMAT row_format] 

  [STORED AS file_format] 

  [LOCATION hdfs_path]

  [TBLPROPERTIES (property_name=property_value, ...)]

  3.2.1.2 案例

  1）内部表与外部表

  （1）内部表

  Hive中默认创建的表都是的内部表，有时也被称为管理表。对于内部表，Hive会完全管理表的元数据和数据文件。

  创建内部表如下：

  create table if not exists student(

      id int,

      name string

  )

  row format delimited fields terminated by '\t'

  location '/user/hive/warehouse/student';

  准备其需要的文件如下，注意字段之间的分隔符。

  [atguigu@hadoop102 datas]$ vim /opt/module/datas/student.txt

  1001 student1

  1002 student2

  1003 student3

  1004 student4

  1005 student5

  1006 student6

  1007 student7

  1008 student8

  1009 student9

  1010 student10

  1011 student11

  1012 student12

  1013 student13

  1014 student14

  1015 student15

  1016 student16

  上传文件到Hive表指定的路径

  [atguigu@hadoop102 datas]$ hadoop fs -put student.txt /user/hive/warehouse/student

  删除表，观察数据HDFS中的数据文件是否还在

  hive (default)> drop table student;

  （2）外部表

  外部表通常可用于处理其他工具上传的数据文件，对于外部表，Hive只负责管理元数据，不负责管理HDFS中的数据文件。

  创建外部表如下：

  create external table if not exists student(

      id int,

      name string

  )

  row format delimited fields terminated by '\t'

  location '/user/hive/warehouse/student';

  上传文件到Hive表指定的路径

  [atguigu@hadoop102 datas]$ hadoop fs -put student.txt /user/hive/warehouse/student

  删除表，观察数据HDFS中的数据文件是否还在

  hive (default)> drop table student;

  2）SERDE和复杂数据类型

  重点练习SERDE和复杂数据类型的使用。

  若现有如下格式的JSON文件需要由Hive进行分析处理，如何设计表？

  注：以下内容为格式化之后的结果，文件中每行数据为一个完整的JSON字符串。

  {

      "name": "dasongsong",

      "friends": [

          "bingbing",

          "lili"

      ],

      "students": {

          "xiaohaihai": 18,

          "xiaoyangyang": 16

      },

      "address": {

          "street": "hui long guan",

          "city": "beijing",

          "postal_code": 10010

      }

  }

  可以考虑使用专门负责JSON文件的JSON Serde，设计表字段时，表的字段与JSON字符串中的一级字段保持一致，对于具有嵌套结构的JSON字符串，考虑使用合适复杂数据类型保存其内容。最终设计出的表结构如下：

  hive>

  create table teacher
  (
      name     string,
      friends  array<string>,
      students map<string,int>,
      address  struct<city:string,street:string,postal_code:int>
  )
  row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'

  location '/user/hive/warehouse/teacher';

  创建该表，并准备以下文件。注意，需要确保文件中每行数据都是一个完整的JSON字符串，JSON SERDE才能正确的处理。

  [atguigu@hadoop102 datas]$ vim /opt/module/datas/teacher.txt

  {"name":"dasongsong","friends":["bingbing","lili"],"students":{"xiaohaihai":18,"xiaoyangyang":16},"address":{"street":"hui long guan","city":"beijing","postal_code":10010}}

  上传文件到Hive表指定的路径

  [atguigu@hadoop102 datas]$ hadoop fs -put teacher.txt /user/hive/warehouse/teacher

  尝试从复杂数据类型的字段中取值

  3）create table as select和create table like

  （1）create table as select

  hive>

  create table teacher1 as select * from teacher;

  （2）create table like

  hive>

  create table teacher2 like teacher;

  3.2.2 查看表

  1）展示所有表

  （1）语法

  SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];

  注：like通配表达式说明：*表示任意个任意字符，|表示或的关系。

  （2）案例

  hive> show tables like 'stu*';

  2）查看表信息

  （1）语法

  DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

  注：EXTENDED：展示详细信息

  FORMATTED：对详细信息进行格式化的展示

  （2）案例

  1、查看基本信息

  hive> desc stu;

  2、查看更多信息

  hive> desc formatted stu;

  3.2.3 修改表

  1）重命名表

  （1）语法

  ALTER TABLE table_name RENAME TO new_table_name

  （2）案例

  hive (default)> alter table stu rename to stu1;

  2）修改列信息

  （1）语法

  1、增加列

  该语句允许用户增加新的列，新增列的位置位于末尾。

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

  2、更新列

  该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置。

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

  3、替换列

  该语句允许用户用新的列集替换表中原有的全部列。

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

  2）案例

  （1）查询表结构

  hive (default)> desc stu;

  （2）添加列

  hive (default)> alter table stu add columns(age int);

  （3）查询表结构

  hive (default)> desc stu;

  （4）更新列

  hive (default)> alter table stu change column age ages double;

  （6）替换列

  hive (default)> alter table stu replace columns(id int, name string);

  3.2.4 删除表

  1）语法

  DROP TABLE [IF EXISTS] table_name;

  2）案例

  hive (default)> drop table stu;

  3.2.5 清空表

  1）语法

  TRUNCATE [TABLE] table_name

  注意：truncate只能清空管理表，不能删除外部表中数据。

  2）案例

  hive (default)> truncate table student;

  4、 DML（Data Manipulation Language）数据操作

  4.1 Load

  Load语句可将文件导入到Hive表中。

  1）语法

  hive>

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

  关键字说明：

  （1）local：表示从本地加载数据到Hive表；否则从HDFS加载数据到Hive表。

  （2）overwrite：表示覆盖表中已有数据，否则表示追加。

  （3）partition：表示上传到指定分区，若目标是分区表，需指定分区。

  2）实操案例

  （0）创建一张表

  hive (default)>

  create table student(

      id int,

      name string

  )

  row format delimited fields terminated by '\t';

  （1）加载本地文件到hive

  hive (default)> load data local inpath '/opt/module/datas/student.txt' into table student;

  （2）加载HDFS文件到hive中

  ①上传文件到HDFS

  [atguigu@hadoop102 ~]$ hadoop fs -put /opt/module/datas/student.txt /user/atguigu

  ②加载HDFS上数据，导入完成后去HDFS上查看文件是否还存在

  hive (default)>

  load data inpath '/user/atguigu/student.txt'

  into table student;

  （3）加载数据覆盖表中已有的数据

  ①上传文件到HDFS

  [atguigu@hadoop102 ~]$ hadoop fs -put /opt/module/datas/student.txt /user/atguigu

  ②加载HDFS上数据，导入完成后去HDFS上查看文件是否还存在

  hive (default)>

  load data inpath '/user/atguigu/student.txt'

  into table student;

  （3）加载数据覆盖表中已有的数据

  ①上传文件到HDFS

  hive (default)> dfs -put /opt/module/datas/student.txt /user/atguigu;

  ②加载数据覆盖表中已有的数据

  hive (default)>

  load data inpath '/user/atguigu/student.txt'

  overwrite into table student;

  4.2 Insert

  4.2.1  将查询结果插入表中

  1）语法

  INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement;

  关键字说明：

  （1）INTO：将结果追加到目标表

  （2）OVERWRITE：用结果覆盖原有数据

  2）案例

  （1）新建一张表

  hive (default)>

  create table student1(

      id int,

      name string

  )

  row format delimited fields terminated by '\t';

  （2）根据查询结果插入数据

  hive (default)> insert overwrite table student3 

  select

      id,

      name

  from student;

  4.2.2 将给定Values插入表中

  1）语法

  INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

  2）案例

  hive (default)> insert into table  student1 values(1,'wangwu'),(2,'zhaoliu');

  4.2.3 将查询结果写入目标路径

  1）语法

  INSERT OVERWRITE [LOCAL] DIRECTORY directory

    [ROW FORMAT row_format] [STORED AS file_format] select_statement;

  2）案例

  insert overwrite local directory '/opt/module/datas/student' ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'

  select id,name from student;

  4.3 Export&Import

  Export导出语句可将表的数据和元数据信息一并到处的HDFS路径，Import可将Export导出的内容导入Hive，表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移。

  1）语法

  导出

  EXPORT TABLE tablename TO 'export_target_path'

  导入

  IMPORT [EXTERNAL] TABLE new_or_original_tablename FROM 'source_path' [LOCATION 'import_target_path']

  2）案例

  --导出

  hive>

  export table default.student to '/user/hive/warehouse/export/student';

  --导入

  hive>

  import table student2 from '/user/hive/warehouse/export/student';

  5、 查询

  5.1 基础语法

  1）查询语句语法：

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

    FROM table_reference       -- 从什么表查

    [WHERE where_condition]   -- 过滤

    [GROUP BY col_list]        -- 分组查询

     [HAVING col_list]          -- 分组后过滤

    [ORDER BY col_list]        -- 排序

    [CLUSTER BY col_list

      | [DISTRIBUTE BY col_list] [SORT BY col_list]

    ]

   [LIMIT number]                -- 限制输出的行数

  5.2 基本查询（Select…From）

  5.2.1 数据准备

  （0）原始数据

  ①在/opt/module/hive/datas/路径上创建dept.txt文件，并赋值如下内容：

  部门编号 部门名称 部门位置id

  [atguigu@hadoop102 datas]$ vim dept.txt

  10 行政部 1700

  20 财务部 1800

  30 教学部 1900

  40 销售部 1700

  ②在/opt/module/hive/datas/路径上创建emp.txt文件，并赋值如下内容：

  员工编号 姓名 岗位    薪资  部门

  [atguigu@hadoop102 datas]$ vim emp.txt

  7369 张三 研发 800.00 30

  7499 李四 财务 1600.00 20

  7521 王五 行政 1250.00 10

  7566 赵六 销售 2975.00 40

  7654 侯七 研发 1250.00 30

  7698 马八 研发 2850.00 30

  7782 金九 \N 2450.0 30

  7788 银十 行政 3000.00 10

  7839 小芳 销售 5000.00 40

  7844 小明 销售 1500.00 40

  7876 小李 行政 1100.00 10

  7900 小元 讲师 950.00 30

  7902 小海 行政 3000.00 10

  7934 小红明 讲师 1300.00 30

  （1）创建部门表

  hive (default)>

  create table if not exists dept(

      deptno int,    -- 部门编号

      dname string,  -- 部门名称

      loc int        -- 部门位置

  )

  row format delimited fields terminated by '\t';

  （2）创建员工表

  hive (default)>

  create table if not exists emp(

      empno int,      -- 员工编号

      ename string,   -- 员工姓名

      job string,     -- 员工岗位（大数据工程师、前端工程师、java工程师）

      sal double,     -- 员工薪资

      deptno int      -- 部门编号

  )

  row format delimited fields terminated by '\t';

  （3）导入数据

  hive (default)>

  load data local inpath '/opt/module/hive/datas/dept.txt' into table dept;

  load data local inpath '/opt/module/hive/datas/emp.txt' into table emp;

  5.2.2 全表和特定列查询

  1）全表查询

  hive (default)> select * from emp;

  2）选择特定列查询

  hive (default)> select empno, ename from emp;

  注意：

  （1）SQL 语言大小写不敏感。 

  （2）SQL 可以写在一行或者多行。

  （3）关键字不能被缩写也不能分行。

  （4）各子句一般要分行写。

  （5）使用缩进提高语句的可读性。

  5.2.3 列别名

  1）重命名一个列

  2）便于计算

  3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

  4）案例实操

  查询名称和部门。

  hive (default)>

  select

      ename AS name,

      deptno dn

  from emp;

  5.2.4 Limit语句

  典型的查询会返回多行数据。limit子句用于限制返回的行数。

  hive (default)> select * from emp limit 5; 

  hive (default)> select * from emp limit 2,3; -- 表示从第2行开始，向下抓取3行

  5.2.5 Where语句

  1）使用where子句，将不满足条件的行过滤掉

  2）where子句紧随from子句

  3）案例实操

  查询出薪水大于1000的所有员工。

  hive (default)> select * from emp where sal > 1000;

  注意：where子句中不能使用字段别名。

  5.2.6 关系运算函数

  1）基本语法

  如下操作符主要用于where和having语句中。

  操作符	支持的数据类型	描述
  A=B	基本数据类型	如果A等于B则返回true，反之返回false
  A<=>B	基本数据类型	如果A和B都为null或者都不为null，则返回true，如果只有一边为null，返回false
  A<>B, A!=B	基本数据类型	A或者B为null则返回null；如果A不等于B，则返回true，反之返回false
  A<B	基本数据类型	A或者B为null，则返回null；如果A小于B，则返回true，反之返回false
  A<=B	基本数据类型	A或者B为null，则返回null；如果A小于等于B，则返回true，反之返回false
  A>B	基本数据类型	A或者B为null，则返回null；如果A大于B，则返回true，反之返回false
  A>=B	基本数据类型	A或者B为null，则返回null；如果A大于等于B，则返回true，反之返回false
  A [not] between B and C	基本数据类型	如果A，B或者C任一为null，则结果为null。如果A的值大于等于B而且小于或等于C，则结果为true，反之为false。如果使用not关键字则可达到相反的效果。
  A is null	所有数据类型	如果A等于null，则返回true，反之返回false
  A is not null	所有数据类型	如果A不等于null，则返回true，反之返回false
  in（数值1，数值2）	所有数据类型	使用 in运算显示列表中的值
  A [not] like B	string 类型	B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回true；反之返回false。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母‘x’结尾，而‘%x%’表示A包含有字母‘x’,可以位于开头，结尾或者字符串中间。如果使用not关键字则可达到相反的效果。
  A rlike B, A regexp B	string 类型	B是基于java的正则表达式，如果A与其匹配，则返回true；反之返回false。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

  5.2.7 逻辑运算函数

  1）基本语法（and/or/not）

  操作符	含义
  and	逻辑并
  or	逻辑或
  not	逻辑否

  2）案例实操

  （1）查询薪水大于1000，部门是30

  hive (default)>

  select

      *

  from emp

  where sal > 1000 and deptno = 30;

  （2）查询薪水大于1000，或者部门是30

  hive (default)>

  select

      *

  from emp

  where sal>1000 or deptno=30;

  （3）查询除了20部门和30部门以外的员工信息

  hive (default)>

  select

      *

  from emp

  where deptno not in(30, 20);

  5.2.8 聚合函数

  1）语法

  count(*)，表示统计所有行数，包含null值；

  count(某列)，表示该列一共有多少行，不包含null值；

  max()，求最大值，不包含null，除非所有值都是null；

  min()，求最小值，不包含null，除非所有值都是null；

  sum()，求和，不包含null。

  avg()，求平均值，不包含null。

  2）案例实操

  （1）求总行数（count）

• hive (default)> select count(*) cnt from emp;

（2）求工资的最大值（max）

hive (default)> select max(sal) max_sal from emp;

hive sql执行过程：

（3）求工资的最小值（min）

hive (default)> select min(sal) min_sal from emp;

 

hive sql执行过程：

（4）求工资的总和（sum）

hive (default)> select sum(sal) sum_sal from emp;

 

hive sql执行过程：

（5）求工资的平均值（avg）

hive (default)> select avg(sal) avg_sal from emp;

hive sql执行过程：