本文目录
- hive select into 可以建表么
- 如何生成hive的建表语句
- hive存文本文件中第一行是表头解决办法
- hive中已有的表，查看其建表语句
- Hive 数据库表的基本操作，必须掌握的基本功
- hive里建表插入日期为null值的解决办法
- 如何获取hive建表语句
- 【Hive】Hive Join 介绍
hive select into 可以建表么
可以考虑用sed将双引号替换掉，然后加载到hive里建表可以用以下语句createtablet_name(t1String,t2String,t3String,t4String,t5String,t6String,t7String,t8String,t9String,t10String)rowformatdelimitedfieldsterminatedby’,’--逗号分隔
如何生成hive的建表语句
Java连接Hive利用jdbc连接到hive，使用Java程序写一个循环。先获取全部表，然后show create table每个表。没能尝试成功，一直连接不上：import java.sql.SQLException;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.sql.DriverManager;public class HiveTestCase { private static String driverName = "org.apache.hive.jdbc.HiveDriver"; public static void main(String args) throws SQLException { try { Class.forName(driverNa
hive存文本文件中第一行是表头解决办法
hive把纯文本放在表对应的位置，就可以查询到数据，但是如果纯文本里面存在表头，会把表头也作为第一行数据。如果又不想在纯文本中去掉表头只要在建表语句中加入如下‘tblproperties ("skip.header.line.count"="1")’即可。 实际建表语句如下所示：
hive中已有的表，查看其建表语句
show create table warehouse;+----------------------------------------------------+ | createtab_stmt | +----------------------------------------------------+ | CREATE EXTERNAL TABLE `warehouse`( | | `w_warehouse_sk` bigint, | | `w_warehouse_id` char(16), | | `w_warehouse_name` varchar(20), | | `w_warehouse_sq_ft` int, | | `w_street_number` char(10), | | `w_street_name` varchar(60), | | `w_street_type` char(15), | | `w_suite_number` char(10), | | `w_city` varchar(60), | | `w_county` varchar(30), | | `w_state` char(2), | | `w_zip` char(10), | | `w_country` varchar(20), | | `w_gmt_offset` decimal(5,2)) | | ROW FORMAT SERDE | | ’org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’ | | WITH SERDEPROPERTIES ( | | ’field.delim’=’|’, | | ’serialization.format’=’|’) | | STORED AS INPUTFORMAT | | ’org.apache.hadoop.mapred.TextInputFormat’ | | OUTPUTFORMAT | | ’org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’ | | LOCATION | | ’hdfs://mycluster/data/tpcds/2/warehouse’ | | TBLPROPERTIES ( | | ’bucketing_version’=’2’, | | ’serialization.null.format’=’’, | | ’transient_lastDdlTime’=’1616470752’) | +----------------------------------------------------+
Hive 数据库表的基本操作，必须掌握的基本功
说明：hive 的表存放位置模式是由 hive-site.xml 当中的一个属性指定的，默认是存放在该配置文件设置的路径下，也可在创建数据库时单独指定存储路径。
数据库有一些描述性的属性信息，可以在创建时添加：
查看数据库的键值对信息
修改数据库的键值对信息
与mysql查询语句是一样的语法
删除一个空数据库，如果数据库下面有数据表，那么就会报错
强制删除数据库，包含数据库下面的表一起删除(请谨慎操作)
里的属性为可选属性，不是必须的，但是如果有可选属性，会使 sql 语句的易读性更好，更标准与规范。
例如：属性除外
1. CREATE TABLE 创建一个指定名字的表，如果相同名字的表已存在，则抛出异常提示：表已存在，使用时可以使用IF NOT EXISTS语句来忽略这个异常。
如果创建的表名已存在，则不会再创建，也不会抛出异常提示：表已存在。否则则自动创建该表。
2. EXTERNAL 顾名思义是外部的意思，此关键字在建表语句中让使用者可以创建一个外部表，如果不加该关键字，则默认创建内部表。
外部表在创建时必须同时指定一个指向实际数据的路径（LOCATION），Hive在创建内部表时，会将数据移动到数据仓库指向的路径；
若创建外部表，仅记录数据所在的路径，不对数据的位置作任何改变。
内部表在删除后，其元数据和数据都会被一起删除。 外部表在删除后，只删除其元数据，数据不会被删除。
3. COMMENT 用于给表的各个字段或整张表的内容作解释说明的，便于他人理解其含义。
4. PARTITIONED BY 区分表是否是分区表的关键字段，依据具体字段名和类型来决定表的分区字段。
5. CLUSTERED BY 依据column_name对表进行分桶，在 Hive 中对于每一张表或分区，Hive 可以通过分桶的方式将数据以更细粒度进行数据范围划分。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
6. SORTED BY 指定表数据的排序字段和排序规则，是正序还是倒序排列。
7. ROW FORMAT DELIMITED FIELDS TERMINATED BY ’ ’ 指定表存储中列的分隔符，这里指定的是’ ’，也可以是其他分隔符。
8. STORED AS SEQUENCEFILE|TEXTFILE|RCFILE 指定表的存储格式，如果文件数据是纯文本格式，可以使用STORED AS TEXTFILE，如果数据需要压缩，则可以使用STORED AS SEQUENCEFILE。
9. LOCATION 指定 Hive 表在 hdfs 里的存储路径，一般内部表（Managed Table）不需要自定义，使用配置文件中设置的路径即可。 如果创建的是一张外部表，则需要单独指定一个路径。
1. 使用create table语句创建表 例子：
2. 使用create table ... as select...语句创建表 例子：
使用 create table ... as select ...语句来创建新表sub_student，此时sub_student 表的结构及表数据与 t_student 表一模一样， 相当于直接将 t_student 的表结构和表数据复制一份到 sub_student 表。
注意： (1). select 中选取的列名（如果是 * 则表示选取所有列名）会作为新表 sub_student 的列名。
(2). 该种创建表的方式会改变表的属性以及结构，例如不能是外部表，只能是内部表，也不支持分区、分桶。
如果as select后的表是分区表，并且使用select *，则分区字段在新表里只是作为字段存在，而不是作为分区字段存在。
在使用该种方式创建时，create 与 table 之间不能加 external 关键字，即不能通过该种方式创建外部目标表，默认只支持创建内部目标表。
(3). 该种创建表的方式所创建的目标表存储格式会变成默认的格式textfile。
3.使用like语句创建表 例子：
注意： (1). 只是将 t_student 的表结构复制给 sub1_student 表。
(2). 并不复制 t_student 表的数据给 sub1_student 表。
(3). 目标表可以创建为外部表，即:
hive里建表插入日期为null值的解决办法
问题： hive中建表语句如下：（其中包含日期字段）
插入数据如下：
发现日期字段显示为null
解决办法：
在建表语句中把日期字段的类型改为string即可
再次插入数据，即可正常显示：
如何获取hive建表语句
hivesql sql— 获取指定hive表或指定文件中所有hive表的DDL，如果有按天的分区则默认执行最近7天的分区DDL。同时，table支持符合sql语法的正则表达式，如果有多个表匹配，则提示用户选择(使用file则自动关闭该交互功能)。hivesql synctab— 基本同上，但是会将得到的DDL提交到当前的hive环境，可用来在不同的hive环境中复制表结构。如果需要多天分区DDL还可以这样用(前提是分区中含有日期信息，如pt=20100720):hivesql sql s_table 20100702 — 除建表语句外，获得自20100702以来的分区DDLhivesql sql s_table 20100702 20100725 — ………………………..自20100702-20100725的分区DDLhivesql sql s_table 20100702 10 — ………………………..自20100702起10天的分区DDLhivesql synctab和hivesql sql一样支持上述日期限定功能。此外，还提供了两个附加的功能(也很有用呃)hivesql loc — 根据关键字查找能够匹配到的hive表或对应的数据路径hivesql hdfswc — 获取指定数据目录下所有数据的总行数，支持普通文本，TextFile和SequenceFile的压缩格式，类似于linux下的wc -l
【Hive】Hive Join 介绍
Hive 中的 Join 只支持等值 Join，也就是说 Join on 中的 on 里面表之间连接条件只能是 = ，不能是 《,》 等符号。此外，on中的等值连接之间只能是 and，不能是or。
Hive 执行引擎会将 HQL “翻译” 成为map-reduce 任务，在执行表的 Join 操作时，如果多个表中每个表都使用同一个列进行连接（出现在 Join on 子句中），则只会生成一个 MR Job：
三个表 a、b、c 都分别使用了同一个字段进行连接，亦即同一个字段同时出现在两个 Join 子句中，从而只生成一个 MR Job。
如果多表中，其中存在一个表使用了至少 2 个字段进行连接（同一个表的至少2个列出现在 Join 子句中），则会至少生成 2 个MR Job：
三个表基于 2 个字段进行连接，这两个字段 b.key1 和 b.key2 同时出现在 b 表中。连接的过程是这样的：首先 a 和 b 表基于a.key 和 b.key1 进行连接，对应着第一个 MR Job；表 a 和 b 连接的结果，再和 c 进行连接，对应着第二个 MR Job。
这是因为 Map 输出时候以 Join on 条件中的列为 key，如果 Join 有多个关联键，则以这些关联键的组合作为 key，Map 根据 Key 分发数据给 Reduce 端，具体的 Join 是在 Reduce 操作中完成，因此，如果多表基于不同的列做 Join，则无法在一轮 MR 任务中将所有相关数据 shuffle 到同一个 Reduce 操作中。
Hive 支持常用的 SQL Join 语句，例如内连接、左外连接、右外连接以及 Hive 独有的 map 端连接。其中 map 端连接是用于优化 Hive 连接查询的一个重要技巧。
先准备三张表。
employee员工表：
dept部门表：
薪水表:
多张表进行内连接操作时，只有所有表中与 on 条件中相匹配的数据才会显示，类似取交集。
JOIN 操作符 左边表中符合 where 条件的所有记录都会被保留 ，JOIN 操作符右边表中如果没有符合 on 后面连接条件的记录，则从右边表中选出的列为NULL，如果没有 where 条件，则左边表中的记录都被保留。
标准查询关键字执行顺序为 from-》on-》where-》group by-》having-》order by ，on 是先对表进行筛选后再关联的，left 关联则 on 只对右表有效，左表都要选出来。
对于大量的数据，在编写 SQL 时尽量用 where 条件过滤掉不符合条件的数据是有益的。但是对于左外连接和右外连接， where 条件是在 on 条件执行之后才会执行，on 条件会产生一个临时表，where 条件是对这个临时表进行过滤 。
因此为了优化 Hive SQL 执行的效率， 在需要使用外连接的场景，如果是要条件查询后才连接应该把查询件放置于 on 后，如果是想再连接完毕后才筛选就应把条件放置于 where 后面，对主表的筛选要用 where 条件 。
特别要注意的是，如果是需要对主表过滤之后再和从表做左关联，最好将主表写成子查询的形式，可以减少主表的数据量 ：
RIGHT OUTER JOIN，与 LEFT OUTER JOIN 相对， JOIN 操作符右边表中符合where 条件的所有记录都会被保留 ，JOIN 操作符左边表中如果没有符合 on 后面连接条件的记录，则从左边表中选出的列为 NULL。
保留满足 where 条件的两个表的数据，类似并集，没有符合连接条件的字段使用 NULL 填充。
以 LEFT SEMI JOIN 关键字前面的表为主表，返回主表的 KEY 也在副表中的记录。在早期的 Hive 版本中，不支持标准 SQL 中的 IN 或 EXISTS 的功能，可以使用LEFT SEMI JOIN 实现类似的功能。
需要强调的是：
笛卡尔积是一种连接，表示左边表的行数乘以右边表的行数。
Hive中的 Join 可分为 Common Join（Reduce 阶段完成 join）和 Map Join（Map 阶段完成 join）。
如果不指定 Map Join 或者不符合 Map Join 的条件，那么 Hive 解析器会默认把执行 Common Join，即在 Reduce 阶段完成 join。整个过程包含 Map、Shuffle、Reduce 阶段。
以下面 HQL 为例，图解其过程：
Map Join 通常用于一个很小的表和一个大表进行 join 的场景，具体小表有多小，由参数 hive.mapjoin.smalltable.filesize 来决定，该参数表示小表的总大小，默认值为 25000000 字节，即 25M。
Hive 0.7 之前，需要使用 hint 提示 / + mapjoin(table) / 才会执行Map Join，否则执行 Common Join，但在 0.7 版本之后，默认自动会转换 Map Join，由参数hive.auto.convert.join 来控制，默认为 true。
如上图中的流程， 首先Task A 在客户端本地执行，负责扫描小表 b 的数据，将其转换成一个 HashTable 的数据结构，并写入本地的文件中，之后将该文件加载到DistributeCache 中。
接下来是 Task B，该任务是一个没有 Reduce 的 MR，启动 MapTasks 扫描大表 a,在 Map 阶段，根据 a 的每一条记录去和 DistributeCache 中 b 表对应的 HashTable 关联，并直接输出结果。
由于 MapJoin 没有 Reduce，所以由 Map 直接输出结果文件，有多少个 Map Task，就有多少个结果文件。
Map Join 效率比 Common Join 效率好，但总会有“小表”条件不满足的时候。这就需要 bucket map join 了。
Bucket map join 需要待连接的两个表在连接字段上进行分桶（每个分桶对应hdfs上的一个文件），而且小表的桶数需要时大表桶数的倍数。
建立分桶表的例子：
这样，my_user 表就对应 32 个桶，数据根据 uid 的 hash value 与32 取余，然后被分发导不同的桶中。
如果两个表在连接字段上分桶，则可以执行 bucket map join 了，具体的：
对于 bucket map join 中的两个表，如果每个桶内分区字段也是有序的，则还可以进行 sort merge bucket map join。
建表语句为：
这样一来当两边 bucket 要做局部 join 的时候，只需要用类似 merge sort 算法中的 merge 操作一样把两个 bucket 顺序遍历一遍即可完成，小表的数据可以每次只读取一部分，然后还是用大表一行一行的去匹配，这样的join 没有限制内存的大小. 并且也可以执行全外连接。
进行sort merge bucket map join时，需要设置的属性为：
Join 的过程中，Map 结束之后，会将相同的 Key 的数据 shuffle 到同一个 Reduce中，如果数据分布均匀的话，每个Reduce 处理的数据量大体上是比较均衡的，但是若明显存在数据倾斜的时候，会出现某些 Reducer 处理的数据量过大，从而使得该节点的处理时间过长，成为瓶颈。
大表与大表关联，如果其中一张表的多是空值或者 0 比较多，容易 shuffle 给一个reduce，造成运行慢。
这种情况可以对异常值赋一个随机值来分散 key，均匀分配给多个 reduce 去执行，比如：
当 key 值都是有效值时，解决办法为：
设置以下参数：
Hive 在运行的时候无法判断哪个 key 会产生倾斜，所以使用 hive.skewjoin.key 参数控制倾斜的阈值，如果超过这个值，新的值会发送给那些还没有达到的 reduce，一般可以设置成待处理的总记录数/reduce 个数的 2-4 倍。
1. Hive基础二（join原理和机制，join的几种类型，数据倾斜简单处理） 2. Hive:JOIN及JOIN优化 3. hive中关于常见数据倾斜的处理
