sqoop 根据时间增量导入

布满荆棘的人生 2022-08-04 12:42 238阅读 0赞

**sqoop 根据时间增量导入**

sqoop 的基本功能 大家应该都知道，实现关系型数据库和非关系型数据库的数据转换；

官网[ http://archive.cloudera.com/cdh/3/sqoop/SqoopUserGuide.html\#\_incremental\_imports][http_archive.cloudera.com_cdh_3_sqoop_SqoopUserGuide.html_incremental_imports]；

下面一段详细介绍了 sqoop 的增量导入；

--------------------

7.2.7. Incremental Imports

Sqoop provides an incremental import mode which can be used to retrieve only rows newer than some previously-imported set of rows.

The following arguments control incremental imports:

Table 4. Incremental import arguments:

<table style="border-collapse:collapse; border-width:0.5pt 1px; border-style:solid; border-color:rgb(187,187,187)">     
 <thead> 
  <tr> 
   <th align="left" style="line-height:1.2; border-right-width:0.5pt; border-right-style:solid; border-bottom-width:0.5pt; border-bottom-style:solid"> Argument</th> 
   <th align="left" style="line-height:1.2; border-bottom-width:0.5pt; border-bottom-style:solid"> Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left" style="border-collapse:collapse; border-width:1px 0.5pt 0.5pt 1px; border-style:solid; border-color:rgb(187,187,187); line-height:1.2"> <code style="color:rgb(0,122,0)">--check-column (col)</code></td> 
   <td align="left" style="border-collapse:collapse; border-width:1px 1px 0.5pt; border-style:solid; border-color:rgb(187,187,187); line-height:1.2"> Specifies the column to be examined when determining which rows to import.</td> 
  </tr> 
  <tr> 
   <td align="left" style="border-collapse:collapse; border-width:1px 0.5pt 0.5pt 1px; border-style:solid; border-color:rgb(187,187,187); line-height:1.2"> <code style="color:rgb(0,122,0)">--incremental (mode)</code></td> 
   <td align="left" style="border-collapse:collapse; border-width:1px 1px 0.5pt; border-style:solid; border-color:rgb(187,187,187); line-height:1.2"> Specifies how Sqoop determines which rows are new. Legal values for&nbsp;<code style="color:rgb(0,122,0)">mode</code>&nbsp;include&nbsp;<code style="color:rgb(0,122,0)">append</code>&nbsp;and&nbsp;<code style="color:rgb(0,122,0)">lastmodified</code>.</td> 
  </tr> 
  <tr> 
   <td align="left" style="border-collapse:collapse; border-width:1px 0.5pt 1px 1px; border-style:solid; border-color:rgb(187,187,187); line-height:1.2"> <code style="color:rgb(0,122,0)">--last-value (value)</code></td> 
   <td align="left" style="border-collapse:collapse; border:1px solid rgb(187,187,187); line-height:1.2"> Specifies the maximum value of the check column from the previous import.</td> 
  </tr> 
 </tbody> 
</table>

Sqoop supports two types of incremental imports: `append` and `lastmodified`. You can use the `--incremental` argument to specify the type of incremental import to perform.

You should specify `append` mode when importing a table where new rows are continually being added with increasing row id values. You specify the column containing the row’s id with `--check-column`. Sqoop imports rows where the check column has a value greater than the one specified with `--last-value`.

An alternate table update strategy supported by Sqoop is called `lastmodified` mode. You should use this when rows of the source table may be updated, and each such update will set the value of a last-modified column to the current timestamp. Rows where the check column holds a timestamp more recent than the timestamp specified with `--last-value` are imported.

At the end of an incremental import, the value which should be specified as `--last-value` for a subsequent import is printed to the screen. When running a subsequent import, you should specify `--last-value` in this way to ensure you import only the new or updated data. This is handled automatically by creating an incremental import as a saved job, which is the preferred mechanism for performing a recurring incremental import. See the section on saved jobs later in this document for more information.

**通过官网不难看出 sqoop 的增量导入需要三个参数 \--check-column  \--incremental \--last-value **

--------------------

**时间增量举例：**

**首先数据库的数据如下图所示：**

![Center][]

![Image 1][]现在的时间是 8月27号。28,29 号 的日期是测试数据，观察结果使用；这里想实现的功能就是，每天12点导入一次数据，这样就得使用时间增量导入；

**脚本如下：**

#!/bin/bash
       
        
       
        
        
         #Set the RDBMS connection params
       
        
       
        
        
         rdbms_connstr="jdbc:oracle:thin:@192.168.0.105:1521:ORCL"
       
        
       
        
        
         rdbms_username="hbea"
       
        
       
        
        
         rdbms_pwd="fulong"
       
        
       
        
        
         
       
        
       
        
        
         #Set the source table in RDBMS
       
        
       
        
        
         rdbms_table="action_log"
       
        
       
        
        
         rdbms_columns="ACTION_TIME,USER_ID,RCOOKIE_ID,SESSION_ID,LOGIN_STATUS,USER_NEW_OR_OLD,ITEM_ID,ITEM_URL,ITEM_PRICE,ITEM_NUM,ORDER_TOTAL,ITEM_TYPE,ACTION_TYPE,SEARCH_WORD,SEARCH_USE_URL,SEARCH_USE_TITLE,NATION_ENGLISH,NATION_CHINESE,LOCATION_AREA_CODE,LOCATION_AREA_WORD,TERMINAL_SIZE,TERMINAL_TYPE,IP,LL_GRID,APP_SYSTEM_ID,OS,IF_FROMREC,EXPLORER,EQUIPMENT,PAGE_TYPE"
       
        
       
        
        
         
       
        
       
        
        
         #Set the hive tables
       
        
       
        
        
         hive_full_table="T_AC_RAW_ACTION_LOG"
       
        
       
        
        
         hive_database="HEBEI_SP_RECS"
       
        
       
        
        
         
       
        
       
        
        
         datenow=$(date -d last-day +%Y-%m-%d)
       
        
       
        
        
         #---------------------------------------------------------
       
        
       
        
        
         
       
        
       
        
        
         #Import init data in RDBMS into Hive
       
        
       
        
        
         $SQOOP_HOME/bin/sqoop import --connect ${rdbms_connstr} --username ${rdbms_username} --password ${rdbms_pwd} --table ${rdbms_table} --columns "${rdbms_columns}" --hive-import   --hive-database ${hive_database}  --hive-table ${hive_full_table} --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-drop-import-delims –-check-column ACTION_TIME –-incremental lastmodified --last-value ${datenow}  -m 5  --split-by "RCOOKIE_ID";

红色部分就是 增量的一个参数  意思是 根据 action\_time 去增量，时间增量  用lastmodified 这个参数，增量部分从 lastvalue开始，这里的datenow获取的是当天时间-1；到底这样当天时间-1会得到什么结果呢，下面来看一下脚本日志；

**执行脚本，得到脚本有用信息：**

![Center 1][]

从这里看出来 sqoop 是这样过滤数据的，使用的 **action\_time >= $\{datenow\} and action\_time< 当前时间** 过滤出来的数据；

拿这句话到oracle执行，得到结果：

![Center 2][]

![Image 1][]

然后去hive 看一下结果 ： 我的hive指向的是hdfs存储，所以这里我就不查询hive了，直接去hdfs 上看数据

![Center 3][]

![Image 1][]

数据一致；

这里大家可以观察到 他并没有把数据库28,29 日期的数据给导入进去，说明他的增量时间指的结束时间就是当前时间！而不是所有大于$\{datenow\}的时间；

还有一个问题就是，我想要的其实就是今天的数据，不包含26的数据，所以脚本中，可以吧datenow变量换成当天的时间；

datenow=$(date  +%Y-%m-%d)

再次执行

![Center 4][]

数据出来的就只有今天的时间；搞定OK；

--------------------

[http_archive.cloudera.com_cdh_3_sqoop_SqoopUserGuide.html_incremental_imports]: http://archive.cloudera.com/cdh/3/sqoop/SqoopUserGuide.html#_incremental_imports
[Center]: /images/20220731/4bec173bef0d46d199dbce2727ab34d9.png
[Image 1]: 
[Center 1]: /images/20220731/d46154b009f04daebd03938af04b74e1.png
[Center 2]: /images/20220731/ae9d8e88aabf4e0e85b2f3b40d2a4939.png
[Center 3]: /images/20220731/3f0a93c4821f4ce782011b0384f859b4.png
[Center 4]: /images/20220731/4a5ef9936e3f4684b49b5a6e5eeb7f42.png