文档

迁移Hive表和分区数据到OSS-HDFS服务

更新时间:

本文介绍如何使用JindoTable MoveTo命令将Hive表和分区数据迁移至OSS-HDFS服务。

前提条件

  • 已创建EMR-3.36.0及以上版本(除3.39.x版本以外)或EMR-5.2.0(除5.5.x版本以外)及以上版本的集群。具体步骤,请参见创建集群

  • 已通过Hive命令创建分区表,且表中已写入数据。本教程以创建名为test_table的表,分区名称为dt,分区值为value为例。

  • 已开通并授权访问OSS-HDFS服务。具体步骤,请参见非EMR集群接入OSS-HDFS服务快速入门

背景信息

MoveTo命令可以在拷贝底层数据结束后,自动更新元数据,使表和分区的数据完整地迁移到新路径;可以通过条件筛选,一次拷贝大量分区。在数据迁移过程中,还使用了多种措施保护数据的完整性,确保数据安全。

操作步骤

重要

集群上每次仅允许运行一个MoveTo进程。如果集群上有正在运行的MoveTo进程,启动新的MoveTo进程时会因为获取不到配置锁而退出,并告知正在运行的MoveTo进程。此时,您可以终止掉正在运行的MoveTo进程,启动新的MoveTo进程,或者等待正在运行的MoveTo进程结束。

  1. 通过SSH方式登录集群,详情请参见登录集群
  2. 执行以下命令,获取帮助信息。

    sudo jindo table -help moveTo

    帮助信息如下所示。

    <dbName.tableName>      The table to move.
    <destination path>      The destination base directory which is always at the
                              same level of a 'table location', where the moved
                              partitions or un-partitioned data would located in.
    <condition>/-fullTable  A filter condition to determine which partitions should
                              be moved, supporting common operators (like '>') and
                              built-in UDFs (like to_date) (UDFs not supported
                              yet...), while -fullTable means that all partitions (or
                              a whole un-partitioned table) should be moved. One but
                              only one option must be specified among -c
                              "<condition>" and -fullTable.
    <before days>           Optional, saying that table/partitions should be moved
                              only when they are created (not updated or modified)
                              more than some days before from now.
    <parallelism>           The maximum concurrency when copying partitions, 1 by
                              default.
    <OSS storage policy>    Storage policy for OSS destination, which can be Standard
                              (by default), IA, Archive, or ColdArchive. Not applicable for destinations other
                              than OSS. NOTE: if you are willing to use ColdArchive storage policy, please
                              make sure that Cold Archive has been enabled for your OSS bucket.
    
    -o/-overWrite           Overwriting the final paths where the data would be moved.
                              For partitioned tables this overwrites partitions locations
                              which are subdirectories of <destination path>; for
                              un-partitioned table this overwrites the <destination path>
                              itself.
    -r/-removeSource        Let the source data be removed when the corresponding
                              table/partition is successfully moved to the new destination.
                              Otherwise (by default), the source data would be left as it
                              was.
    -skipTrash              Applicable only when [-r/-removeSource] is enabled. If
                              present, source data would be immediately deleted from the
                              file system, bypassing the trash.
    -e/-explain             If present, the command would not really move data, but only
                              prints the table/partitions that would be moved for given
                              conditions.
    <log directory>         A directory to locate log files, '/tmp/<current user>/' by
                              default.
    • 命令格式

      sudo jindo table -moveTo \
        -t <dbName.tableName> \
        -d <destination path> \
        [-c "<condition>" | -fullTable] \
        [-b/-before <before days>] \
        [-p/-parallel <parallelism>] \
        [-s/-storagePolicy <OSS storage policy>] \
        [-o/-overWrite] \
        [-r/-removeSource] \
        [-skipTrash] \
        [-e/-explain] \
        [-l/-logDir <log directory>]
    • 命令说明

      参数

      是否必选

      描述

      -t <dbName.tableName>

      待移动的表名称,格式为数据库名.表名

      数据库和表名之前以半角句号(.)分隔。表可以是分区表或非分区表。

      -d <destination path>

      待移动的目标位置。无论是移动分区还是移动非分区表的整表,该位置都对应 "表" 一级的位置。如果移动的是分区,则分区的完整路径是该路径+分区名。例如<destination path>/p1=v1/p2=v2/

      -c "<condition>" | -fullTable

      两者必须指定其中一个。即您可以指定-c "<condition>",或者指定-fullTable

      • 指定-fullTable时,则为移动整表,既可以是非分区表也可以是分区表。
      • 指定-c "<condition>"时,则提供了一个过滤条件,用来选择希望移动的分区,支持常见运算符,例如大于号(>)。

        例如,数据类型为String的分区ds,希望分区名大于 'd',则代码为-c " ds > 'd' "

      -b/before <before days>

      仅创建时间距离当前时间超过一定天数的表或者分区才会被移动。

      -p/-parallel <parallelism>

      迁移操作的并行度。

      -s/-storagePolicy <OSS storage policy>

      OSS-HDFS服务不支持该选项。

      -o/-overWrite

      是否强制覆盖目标写入路径。如果是分区表,则只会清空待移动分区的分区路径,不会清空整个表路径。

      -r/-removeSource

      移动完成,元数据也同步更新后,是否清理源路径。如果是分区表,则只会清理成功移动的分区的源路径。

      -skipTrash

      清理源路径时是否跳过Trash。

      说明

      该选项需与-r/-removeSource选项同时使用。

      -e/-explain

      如果出现该选项,则为解释(explain )模式,只会显示待移动的分区列表,而不会真正移动数据。

      -l/-logDir <log directory>

      指定Log文件目录。

      默认值:/tmp/<current user>/

  3. 将分区数据迁移至OSS-HDFS服务。

    1. 查看待迁移的分区是否符合预期。

      结合-e 选项仅列举待迁移的分区,但不会真正执行迁移任务。

      sudo jindotable -moveTo -t tdb.test_table -d oss://examplebucket.cn-hangzhou.oss-dls.aliyuncs.com/data/tdb.test_table -c " dt > 'v' " -e

      返回结果如下:

      Found 1 partitions to move:
            dt=value-2
      MoveTo finished for table tdb.test_table to destination oss://examplebucket.cn-hangzhou.oss-dls.aliyuncs.com/data/tdb.test_table with condition " dt > 'v' " (explain only).
    2. 将分区迁移至OSS-HDFS服务。

      sudo jindotable -moveTo -t tdb.test_table -d oss://examplebucket.cn-hangzhou.oss-dls.aliyuncs.com/data/tdb.test_table  -c " dt > 'v' " 

      返回结果如下:

      Found 1 partitions in total, and all are successfully moved.
      Successfully moved partitions:
          dt=value-2
      No failed partition.
      MoveTo finished for table tdb.test_table to destination oss://examplebucket.cn-hangzhou.oss-dls.aliyuncs.com/data/tdb.test_table with condition " dt > 'v' ".
    3. 通过查看Location属性,验证分区是否成功迁移。

      sudo hive> desc formatted test_table partition (dt='value-2');

      返回结果如下:

      OK
      # col_name              data_type               comment
      id                      int
      content                 string
      
      # Partition Information
      # col_name              data_type               comment
      dt                      string
      
      # Detailed Partition Information
      Partition Value:        [value-2]
      Database:               tdb
      Table:                  test_table
      CreateTime:             UNKNOWN
      LastAccessTime:         UNKNOWN
      Location:               oss://examplebucket.cn-hangzhou.oss-dls.aliyuncs.com/data/tdb.test_table/dt=value-2
    4. 可选:将分区从OSS-HDFS迁移至HDFS。

      sudo jindotable -moveTo -t tdb.test_table -d hdfs://<hdfs-path>/user/hive/warehouse/tdb.db/test_table  -c " dt > 'v' "

      返回结果如下:

      No successfully moved partition.
      Failed partitions:
          dt=value-2    New location is not empty but -overWrite is not enabled.
      MoveTo finished for table tdb.test_table to destination hdfs://<hdfs-path>/user/hive/warehouse/tdb.db/test_table with condition -c " dt > 'v' ".

      返回结果提示No successfully moved partition.,原因是HDFS目标目录非空。如果确认目标目录可以丢弃,您可以使用-overWrite选项强制覆盖目标目录,确保将分区从OSS-HDFS迁移至HDFS。

      sudo jindotable -moveTo -t tdb.test_table -d hdfs://<hdfs-path>/user/hive/warehouse/tdb.db/test_table  -c " dt > 'v' "

      迁移成功后,返回结果如下:

      Found 1 partitions in total, and all are successfully moved.
      Successfully moved partitions:
          dt=value-2
      No failed partition.
      MoveTo finished for table tdb.test_table to destination hdfs:///user/hive/warehouse/tdb.db/test_table with condition " dt > 'v' ", overwriting new locations.

异常处理

如果迁移表或分区时迁移失败并提示Conflicts found,请通过以下方法处理该问题。

  • 确保同一时间不存在其他命令向相同的目标路径迁移数据,例如DistCp、JindoDistCp等分布式拷贝命令。

  • 删除目标目录。对于非分区表,删除表一级目录。对于分区表,删除存在冲突的分区级目录。

  • 请勿删除源目录。