hive数据导入

最新推荐文章于 2024-02-04 12:25:32 发布

jinliwei1990

最新推荐文章于 2024-02-04 12:25:32 发布

阅读量847

点赞数

分类专栏： hive 数据导入文章标签：数据库 hive 数据导入

本文链接：https://blog.csdn.net/jinliwei1990/article/details/56479460

版权

hive 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

数据导入

1 篇文章 0 订阅

订阅专栏

hive数据导入

hive数据导入

删除hive数据库

DROP DATABASE是删除所有的表并删除数据库的语句。它的语法如下：

DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name 
[RESTRICT|CASCADE];

下面的查询用于删除数据库。假设要删除的数据库名称为userdb。

DROP DATABASE IF EXISTS userdb;

以下是使用CASCADE查询删除数据库。这意味着要全部删除相应的表在删除数据库之前。

DROP DATABASE IF EXISTS userdb CASCADE;

以下使用SCHEMA查询删除数据库。

DROP SCHEMA userdb;

创建数据库

创建数据库是用来创建数据库在Hive中语句。在Hive数据库是一个命名空间或表的集合。此语法声明如下：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] <database name>

在这里，IF NOT EXISTS是一个可选子句，通知用户已经存在相同名称的数据库。可以使用SCHEMA 在DATABASE的这个命令。下面的查询执行创建一个名为userdb数据库：

CREATE DATABASE [IF NOT EXISTS] userdb;

或

CREATE SCHEMA userdb;

下面的查询用于验证数据库列表：

SHOW DATABASES;

sqoop导入数据

列出mysql中所有的库

利用sqoop的list-databases命令连接mysql并打印出mysql中所有的库，通过这一操作可以测试sqoop对mysql的连接是否正确。

sqoop list-databases --connect jdbc:mysql://localhost:3306 --username Test --password dbPWD

利用sqoop执行SQL命令

可以通过sqoop执行SQL命令。

sqoop eval  --connect jdbc:mysql://localhost:3306/databaseName --username Test --password dbPWD -e "select * from tableName_1_pool limit 1"

生成hive表

sqoop create-hive-table \
--connect jdbc:mysql://localhost:3306/databaseName \
--username Test --password dbPWD \
--table tableName_1_pool \
--hive-table databaseName.tableName_1_pool

上述命令在hive的databaseName数据库中生成名为tableName_1_pool的表，该表的schema是sqoop从mysql原表中推测得到的。

将外部数据库导入hive数据库

sqoop import \
--hive-import \
--connect jdbc:mysql://localhost:3306/databaseName \
--username Test --password dbPWD \
--table tableName_1_pool \
--hive-database databaseName \
--hive-table tableName_1_pool \
--split-by Id \
--as-parquetfile \
--hive-overwrite

--split-by Id表示以Id列作为划分并行度的依据，实际导入的过程中，sqoop会按照Id列的值将数据库划分为若干个map任务，分别导入Hive中。
-m 10增加mapper的数量以提高并行度。
--as-parquetfile以parquet格式导入。Parquet是面向分析型业务的列式存储格式，由Twitter和Cloudera合作开发。Parquet列式存储和传统的行式存储相比有如下优势：可以跳过不符合条件的数据，只读取需要的数据，降低IO数据量；压缩编码可以降低磁盘存储空间，由于同一列的数据类型是一样的，可以使用更高效的压缩编码进一步节约存储空间；只读取需要的列，支持向量运算，能够获取更好的扫描性能。
--hive-overwrite覆盖已有数据。
--where "Id>500000"实现条件导入。
--check-column Id --incremental append --last-value 699999增量导入Hive数据库
--map-column-java DateTime=java.sql.Timestamp --map-column-hive DateTime=Timestamp修改列值的类型。从Mysql到Hive的导入操作中，Sqoop会根据输入数据库列的类型寻找Hive中相对应的类型进行转换。但有时候，这些转换可能不能满足实际的要求，这样就需要显式对列的类型进行制定。以tableName_1_pool中为例DateTime列的类型为：DATETIME，但在转换到Hive后，其属性变为了bigint，也就是说，其值转换为了从1970年1月1日至今的毫秒数。在某些情况下，这并不是我们想要的结果，为了改变转换后DateTime列的类型，可利用如下命令在导入时对其进行指定。

自动增量导入

由于每次增量导入前都需要知道上次导入的Id的值，颇为不便，可以使用sqoop job功能实现自动的增量导入。为了自动增量导入，首先在mysql数据库中建立新表，该表为原表的一部分（Id<500000），生成的新表名称为：tableName_3_pool。
创建sqoop job，该job将tableName_3_pool表同步到hive的tableName_3_pool_parquet新表中。注意在这个job中我们在一开始就设置了–check-column –incremental 和–last-value 三个参数。

sqoop job \
--create sync-data-job \
-- import \
--hive-import \
--connect jdbc:mysql://localhost:3306/databaseName \
--username Test --password dbPWD \
--table tableName_3_pool \
--hive-database databaseName \
--hive-table tableName_3_pool_parquet \
--split-by Id \
--as-parquetfile \
--check-column Id \
--incremental append \
--last-value 1

该命令生成了一个sqoop job，可以用如下命令查看：sqoop job --list
可以用如下命令删除该job：sqoop job --delete sync-data-job
运行该job：sqoop job --exec sync-data-job
运行结束后，可用如下命令查看同步的情况：sqoop job --show sync-data-job
其中可以看到具体的同步点：incremental.last.value = 499999

在mysql中，将原表剩余的部分导入到tableName_3_pool表中。
此时再次启动 sqoop job，此次启动不需要制定从何处开始增量导入。sqoop job --exec sync-data-job
此次运行结束后，再次命令查看同步的情况：sqoop job --show sync-data-job
可以已经完成了所有增量的同步：incremental.last.value = 771500
使用sqoop job的方法实现增量导入无需记录–last-value的值，每次想要同步，只要运行该job即可，job会自动从job保存的数据中获取到上次保存的–last-value的值。

实现partition

Hive Select查询一般会扫描整个表内容，但在很多场景下，我们只需要关注表中的一部分数据，此时可以在建表时引入partition概念。Hive的一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。为了演示分区导入的操作，我们在mysql数据库中创建一个新表，这个表在tableName_1_pool表的基础上增加一列acqdate，这一列类型为Date。
mysql中运行如下命令

create table tableName_1_pool_tag as select *,DATE_FORMAT(DateTime, '%Y-%m-%d') as acqdate from tableName_1_pool;

新表的名称为tableName_1_pool_tag，新增acqdate列，其值为DateTime列中仅包含日期的字符串。
在导入Hive时，我们将会以acqdate列的值作为partition的key，为了实现这一目标，我们首先将mysql数据库中tableName_1_pool_tag表的数据导入一个Hive临时表，接着建立结构与tableName_1_pool_tag表相同并且以acqdate日期为partition key的Hive表，最后，我们通过Hiveql语句将临时表导入到最终的分区表。具体步骤如下：
1.将mysql数据库中tableName_1_pool_tag表的数据导入一个Hive临时表

sqoop import \
--hive-import \
--connect jdbc:mysql://localhost:3306/databaseName \
--username Test --password dbPWD \
--table tableName_1_pool_tag \
--hive-database databaseName \
--hive-table tableName_1_pool_tag \
--as-parquetfile \
--split-by Id \
-m 10

2.建立Hive表tableName_1_pool_partition，其partition的key为：acqdate

CREATE TABLE `tableName_1_pool_partition`(
    `id` bigint,
    `DateTime` bigint
    )
       partitioned by (`acqdate` string)
  ROW FORMAT SERDE
    'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  LOCATION 
 'hdfs://nameservice1/user/hive/warehouse/databaseName.db/tableName_1_pool_partition';

连接Hive，并运行命令：

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
insert into table tableName_1_pool_partition partition (acqdate) select * from tableName_1_pool_tag;