精华内容
下载资源
问答
  • sqoop导出数据到mysql
    2021-01-30 09:41:41

    2015-12-28 11:09:19,403 INFO [Thread-12] org.apache.sqoop.mapreduce.AutoProgressMapper: Auto-progress thread is finished. keepGoing=false

    2015-12-28 11:09:19,484 ERROR [Thread-11] org.apache.sqoop.mapreduce.AsyncSqlOutputFormat: Got exception in update thread: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '66' for key 'PRIMARY'

    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)

    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)

    at com.mysql.jdbc.Util.getInstance(Util.java:387)

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)

    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)

    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)

    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)

    at org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:233)

    关键信息:

    ERROR [Thread-11] org.apache.sqoop.mapreduce.AsyncSqlOutputFormat: Got exception in update thread: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '66' for key 'PRIMARY'

    1、MySQLIntegrityConstraintViolationException是指外键出错了(我的问题是所改主键作为了其他表的外键)

    2、Duplicate entry '66'for key 'PRIMARY'是指主键重复了

    主键冲突了,检查下数据库的数据,跟写入的数据,更改后可以顺利写入。

    解决办法:主键冲突了,检查下数据库的数据,跟写入的数据,更改后可以顺利写入。

    空值处理

    导出的时候最好加上

    [plain] view

    plaincopy

    --input-null-string "\\\\N" --input-null-non-string "\\\\N"

    变成

    [plain] view

    plaincopy

    sqoop export --connect jdbc:mysql://localhost:3306/sqoop_test --username root --password root --table employee --m 1 --export-dir /user/hive/warehouse/h_employee_export/ --input-null-string "\\\\N" --input-null-non-string "\\\\N"

    这两句话的意思是如果遇到空值就插入null,要不然出现空值是会报错的

    更多相关内容
  • sqoop导出数据到MySQL

    2022-05-28 09:28:36
    目录1.1创建MySQL数据库1.2创建MySQL表1.3sqoop导出脚本创建gmall_report数据库 1.2创建MySQL表 创建表(mysql表和ADS中的表同步,但不需要全部创建,根据自己的可视化内容创建)下面给出我的一些mysql表:###...

    本文承接我的《大数据项目离线数仓》把ADS层的数据导出到MySQL,用于可视化的制作。

    《大数据项目离线数仓》的其它文章在我的专栏《项目》中。

    目录

    1.1创建MySQL数据库

    1.2创建MySQL表

    1.3sqoop导出脚本


    1.1创建MySQL数据库

    创建gmall_report数据库

    CREATE DATABASE `gmall_report` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

    1.2创建MySQL表

    创建表(mysql表和ADS中的表同步,但不需要全部创建,根据自己的可视化内容创建)

    下面给出我的一些mysql表:

    ###创建用户主题表

    CREATE TABLE `ads_user_topic`  (
      `dt` date NOT NULL,
      `day_users` bigint(255) NULL DEFAULT NULL,
      `day_new_users` bigint(255) NULL DEFAULT NULL,
      `day_new_payment_users` bigint(255) NULL DEFAULT NULL,
      `payment_users` bigint(255) NULL DEFAULT NULL,
      `users` bigint(255) NULL DEFAULT NULL,
      `day_users2users` double(255, 2) NULL DEFAULT NULL,
      `payment_users2users` double(255, 2) NULL DEFAULT NULL,
      `day_new_users2users` double(255, 2) NULL DEFAULT NULL,
      PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建地区主题表

    CREATE TABLE `ads_area_topic`  (
      `dt` date NOT NULL,
      `id` int(11) NULL DEFAULT NULL,
      `province_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `area_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `iso_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `region_id` int(11) NULL DEFAULT NULL,
      `region_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `login_day_count` bigint(255) NULL DEFAULT NULL,
      `order_day_count` bigint(255) NULL DEFAULT NULL,
      `order_day_amount` double(255, 2) NULL DEFAULT NULL,
      `payment_day_count` bigint(255) NULL DEFAULT NULL,
      `payment_day_amount` double(255, 2) NULL DEFAULT NULL,
      PRIMARY KEY (`dt`, `iso_code`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建活跃设备数表

    create table `ads_uv_count`  (
        `dt` date NOT NULL,
        `day_count` bigint(255) NULL DEFAULT NULL,
        `wk_count` bigint(255) NULL DEFAULT NULL,
        `mn_count` bigint(255) NULL DEFAULT NULL,
        `is_weekend` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        `is_monthend` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建漏斗分析表

    CREATE TABLE `ads_user_action_convert_day`  (
        `dt` DATE  NOT NULL,
        `home_count` BIGINT(255) NULL DEFAULT NULL,
        `good_detail_count` BIGINT(255) NULL DEFAULT NULL,
        `home2good_detail_convert_ratio` DOUBLE(255, 2) NULL DEFAULT NULL,
        `cart_count` BIGINT(255) NULL DEFAULT NULL,
        `good_detail2cart_convert_ratio` DOUBLE(255, 2) NULL DEFAULT NULL,
        `order_count` BIGINT(255) NULL DEFAULT NULL,
        `cart2order_convert_ratio` DOUBLE(255, 2) NULL DEFAULT NULL,
        `payment_amount` BIGINT(255) NULL DEFAULT NULL,
        `order2payment_convert_ratio` DOUBLE(255, 2) NULL DEFAULT NULL,
        PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

    ###创建每日新增设备表

    CREATE TABLE `ads_new_mid_count`  (
        `create_date` date NOT NULL,
        `new_mid_count` bigint(255) NULL DEFAULT NULL,
        PRIMARY KEY (`create_date`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建留存率表

    CREATE TABLE `ads_user_retention_day_rate`  (
        `stat_date` date NOT NULL,
        `create_date` date NOT NULL,
        `retention_day` int(11) NULL DEFAULT NULL,
        `retention_count` bigint(255) NULL DEFAULT NULL,
        `new_mid_count` bigint(255) NULL DEFAULT NULL,
        `retention_ratio` double(255, 2) NULL DEFAULT NULL,
        PRIMARY KEY (`stat_date`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建沉默用户数表

    CREATE TABLE `ads_silent_count`  (
        `dt` date NOT NULL,
        `silent_count` bigint(255) NULL DEFAULT NULL,
        PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建商品主题表

    CREATE TABLE `ads_product_info`  (
        `dt` date NOT NULL,
        `sku_num` bigint(255) NULL DEFAULT NULL,
        `spu_num` bigint(255) NULL DEFAULT NULL,
        PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建商品营销排名表

    CREATE TABLE `ads_product_sale_topN`  (
        `dt` date NOT NULL,
        `sku_id` bigint(255) NULL DEFAULT NULL,
        `payment_amount` bigint(255) NULL DEFAULT NULL,
        PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


    ###创建商品收藏排名表

    CREATE TABLE `ads_product_favor_topN`  (
        `dt` date NOT NULL,
        `sku_id` bigint(255) NULL DEFAULT NULL,
        `favor_count` bigint(255) NULL DEFAULT NULL,
        PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建商品加入购物车排名表

    CREATE TABLE `ads_product_cart_topN`  (
        `dt` date NOT NULL,
        `sku_id` bigint(255) NULL DEFAULT NULL,
        `cart_count` bigint(255) NULL DEFAULT NULL,
         PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建商品退款率排名表

    CREATE TABLE `ads_product_refund_topN`  (
        `dt` date NOT NULL,
        `sku_id` bigint(255) NULL DEFAULT NULL,
        `refund_ratio` double(255, 2) NULL DEFAULT NULL,
        PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建下单数目统计表

    CREATE TABLE `ads_order_daycount`  (
        `dt` date NOT NULL,
        `order_count` bigint(255) NULL DEFAULT NULL,
        `order_amount` bigint(255) NULL DEFAULT NULL,
        `order_users` bigint(255) NULL DEFAULT NULL,
        PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    ###创建支付信息统计表

    CREATE TABLE `ads_payment_daycount`  (
        `dt` date NOT NULL,
        `order_count` bigint(255) NULL DEFAULT NULL,
        `order_amount` bigint(255) NULL DEFAULT NULL,
        `payment_user_count` bigint(255) NULL DEFAULT NULL,
        `payment_sku_count` bigint(255) NULL DEFAULT NULL,
        `payment_avg_time` double(255, 2) NULL DEFAULT NULL,
        PRIMARY KEY (`dt`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

    1.3sqoop导出脚本

    1)编写Sqoop导出脚本

    在/bin目录下创建脚本hdfs_to_mysql.sh

    vim hdfs_to_mysql.sh

    在脚本中填写如下内容:

    #!/bin/bash
    
    hive_db_name=default
    mysql_db_name=gmall_report
    
    export_data() {
    /training/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/bin/sqoop export \
    -Dmapreduce.job.queuename=default \
    --connect "jdbc:mysql://hadoop01:3306/${mysql_db_name}?useUnicode=true&characterEncoding=utf-8"  \
    --username root \
    --password 123456 \
    --table $1 \
    --num-mappers 1 \
    --export-dir /warehouse/$hive_db_name/ads/$1 \
    --input-fields-terminated-by "\t" \
    --update-mode allowinsert \
    --update-key $2 \
    --input-null-string '\\N'    \
    --input-null-non-string '\\N'
    }
    
    case $1 in
      "ads_uv_count")
         export_data "ads_uv_count" "dt"
    ;;
      "ads_user_action_convert_day") 
         export_data "ads_user_action_convert_day" "dt"
    ;;
      "ads_user_topic")
         export_data "ads_user_topic" "dt"
    ;;
      "ads_area_topic")
         export_data "ads_area_topic" "dt,iso_code"
    ;;
       "all")
         export_data "ads_user_topic" "dt"
         export_data "ads_area_topic" "dt,iso_code"
         export_data "ads_uv_count" "dt"
         export_data "ads_user_action_convert_day" "dt"
         export_data "ads_new_mid_count" "dt"
         export_data "ads_user_retention_day_rate" "dt"
         export_data "ads_silent_count" "dt"
         export_data "ads_product_info" "dt"
         export_data "ads_product_sale_topN" "dt"
         export_data "ads_product_favor_topN" "dt"
         export_data "ads_product_cart_topN" "dt"
         export_data "ads_product_refund_topN" "dt"
         export_data "ads_order_daycount" "dt"
         export_data "ads_payment_daycount" "dt"
    
    ;;
    esac

    直接执行hdfs_to_mysql.sh

    注:

    (1)导出选择update还是insert

    --update-mode:updateonly 只更新,无法插入新数据;

    --update-mode:allowinsert 允许新增;

    (2)--update-key:

    允许更新的情况下,指定哪些字段匹配视为同一条数据,进行更新而不增加。多个字段用逗号分隔。

    (3)--input-null-string和--input-null-non-string:

    分别表示字符串列和非字符串列的空串和“null”转义。

    Hive中的Null在底层是以“\N”来存储,而MySQL中的Null在底层就是Null,为了保证数据两端的一致性。在导出数据时采用--input-null-string和--input-null-non-string两个参数。导入数据时采用--null-string和--null-non-string。

    查看数据:

    导出成功!!!! 

    作者水平低,如有错误,恳请指正!谢谢!!!!!

    展开全文
  • 版权声明:博主只是整理以前学习的笔记或者遇到的问题,内容太过零碎且现网上的资源已难分辨真实出处,如有侵权请及时联系博...当sqoop导出程序长时间执行未完成,且mr控制台也没有报错[code][w4xj@node202 sqoop-1...

    版权声明:博主只是整理以前学习的笔记或者遇到的问题,内容太过零碎且现网上的资源已难分辨真实出处,如有侵权请及时联系博主进行删除,谢谢。novicedriver@163.comhttps://blog.csdn.net/sltylzx/article/details/88926591

    1.当sqoop导出程序长时间执行未完成,且mr控制台也没有报错

    [code][w4xj@node202 sqoop-1.4.6.bin__hadoop-2.0.4-alpha]$ bin/sqoop export \

    > --connect jdbc:mysql://node202:3306/company \

    > --username root \

    > --password 123456 \

    > --table staff \

    > --num-mappers 1 \

    > --export-dir /user/hive/warehouse/staff_hive \

    > --input-fields-terminated-by "\t"

    Warning: /opt/module/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/bin/../../hbase does not exist! HBase imports will fail.

    Please set $HBASE_HOME to the root of your HBase installation.

    Warning: /opt/module/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/bin/../../hcatalog does not exist! HCatalog jobs will fail.

    Please set $HCAT_HOME to the root of your HCatalog installation.

    Warning: /opt/module/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/bin/../../accumulo does not exist! Accumulo imports will fail.

    Please set $ACCUMULO_HOME to the root of your Accumulo installation.

    19/03/31 11:02:55 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6

    19/03/31 11:02:55 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

    19/03/31 11:02:55 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

    19/03/31 11:02:55 INFO tool.CodeGenTool: Beginning code generation

    19/03/31 11:02:56 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `staff` AS t LIMIT 1

    19/03/31 11:02:56 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `staff` AS t LIMIT 1

    19/03/31 11:02:56 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/module/hadoop-2.7.2

    注: /tmp/sqoop-w4xj/compile/299e764abb8e42f075ad01d3ab61c92f/staff.java使用或覆盖了已过时的 API。

    注: 有关详细信息, 请使用 -Xlint:deprecation 重新编译。

    19/03/31 11:02:57 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-w4xj/compile/299e764abb8e42f075ad01d3ab61c92f/staff.jar

    19/03/31 11:02:57 INFO mapreduce.ExportJobBase: Beginning export of staff

    19/03/31 11:02:57 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar

    19/03/31 11:02:58 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative

    19/03/31 11:02:58 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative

    19/03/31 11:02:58 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps

    19/03/31 11:02:58 INFO client.RMProxy: Connecting to ResourceManager at node203/192.168.26.203:8032

    19/03/31 11:03:01 INFO input.FileInputFormat: Total input paths to process : 1

    19/03/31 11:03:01 INFO input.FileInputFormat: Total input paths to process : 1

    19/03/31 11:03:01 INFO mapreduce.JobSubmitter: number of splits:1

    19/03/31 11:03:01 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative

    19/03/31 11:03:01 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1553997792849_0005

    19/03/31 11:03:01 INFO impl.YarnClientImpl: Submitted application application_1553997792849_0005

    19/03/31 11:03:01 INFO mapreduce.Job: The url to track the job: http://node203:8088/proxy/application_1553997792849_0005/

    19/03/31 11:03:01 INFO mapreduce.Job: Running job: job_1553997792849_0005

    19/03/31 11:03:12 INFO mapreduce.Job: Job job_1553997792849_0005 running in uber mode : false

    19/03/31 11:03:12 INFO mapreduce.Job:  map 0% reduce 0%

    19/03/31 11:03:25 INFO mapreduce.Job:  map 100% reduce 0%

    19/03/31 11:13:40 INFO mapreduce.Job: Task Id : attempt_1553997792849_0005_m_000000_0, Status : FAILED

    AttemptID:attempt_1553997792849_0005_m_000000_0 Timed out after 600 secs

    19/03/31 11:13:42 INFO mapreduce.Job:  map 0% reduce 0%

    19/03/31 11:13:53 INFO mapreduce.Job:  map 100% reduce 0%

    2.那么我们打开yarn的控制台http://node203:8088/cluster,找到那个正在执行的job,点击ApplicationMaster

    37f26327d5d3a42dc28be60f3e587192

    3.点击Job_ID,查看当前job的执行情况

    7c0fd162d9e1997bad4dfdc32a38d237.png

    4.点击正在运行的map

    b3bfe67c0975ce82bdc296bfab997e6e

    5.点击任务

    a33314d1f3723b7729c959e83715a7d1.png

    6.点击logs

    28b8fc9abf5bd33f53d5de05a286dff3.png

    7.点击syslog

    262ee2ee8ec0169804bac1a45a5fd6ec

    8.我们可以看到mysql实际已经报错了,但是sqoop控制台并没有报错,等待最终job失败才返回job失败

    22ef96452914fd1fb25eadaf19d0e392

    9.关于返回错误

    [1].返回错误:

    [code]19/03/31 11:45:12 INFO mapreduce.Job: Job job_1553997792849_0005 failed with state FAILED due to: Task failed task_1553997792849_0005_m_000000

    Job failed as tasks failed. failedMaps:1 failedReduces:0

    19/03/31 11:45:12 INFO mapreduce.Job: Counters: 9

    Job Counters

    Failed map tasks=4

    Launched map tasks=4

    Other local map tasks=3

    Rack-local map tasks=1

    Total time spent by all maps in occupied slots (ms)=2512919

    Total time spent by all reduces in occupied slots (ms)=0

    Total time spent by all map tasks (ms)=2512919

    Total vcore-milliseconds taken by all map tasks=2512919

    Total megabyte-milliseconds taken by all map tasks=2573229056

    19/03/31 11:45:12 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead

    19/03/31 11:45:12 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 2,534.5903 seconds (0 bytes/sec)

    19/03/31 11:45:12 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead

    19/03/31 11:45:12 INFO mapreduce.ExportJobBase: Exported 0 records.

    19/03/31 11:45:12 ERROR tool.ExportTool: Error during export: Export job failed!

    [2].若不返回错误,程序一直卡在那儿,按Ctrl C 停止后,job还在继续运行,需要在ResourceManager上手动停止job,这里的jobId就是上面的那个Job ID

    [code]sh yarn application -kill jobId

    展开全文
  • CDH的坑之Sqoop导出数据到MySQL最近使用Sqoop从Hive导出数据到MySQL中,出现了一系列的问题,下面将这个问题记录一下,避免再度踩坑!导出语句sqoop export --connect jdbc:mysql://192.168.1.78:3306/data \--...

    CDH的坑之Sqoop导出数据到MySQL

    最近使用Sqoop从Hive导出数据到MySQL中,出现了一系列的问题,下面将这个问题记录一下,避免再度踩坑!

    导出语句

    sqoop export --connect jdbc:mysql://192.168.1.78:3306/data \

    --username root \

    -P \

    --export-dir '/user/hive/warehouse/personas.db/user_attribute/000000_0' \

    --table dm_user_attribute \

    --input-fields-terminated-by '|' \

    --input-null-non-string '\\N' \

    --input-null-string '\\N' \

    --lines-terminated-by '\n' \

    -m 1

    运行环境

    centOS7+CDH5.7.2+其中集成的Sqoop

    错误信息

    以下是我输入命令到服务器中,控制台打印的信息。

    Warning: /opt/cloudera/parcels/CDH-5.7.2-1.cdh5.7.2.p0.18/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.

    Please set $ACCUMULO_HOME to the root of your Accumulo installation.

    18/07/23 11:54:45 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.7.2

    18/07/23 11:54:45 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

    18/07/23 11:54:45 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

    18/07/23 11:54:45 INFO tool.CodeGenTool: Beginning code generation

    18/07/23 11:54:45 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `dm_user_attribute` AS t LIMIT 1

    18/07/23 11:54:45 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `dm_user_attribute` AS t LIMIT 1

    18/07/23 11:54:45 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/cloudera/parcels/CDH/lib/hadoop-mapreduce

    Note: /tmp/sqoop-root/compile/2322b82e8ef7190a66357528d5fbddae/dm_user_attribute.java uses or overrides a deprecated API.

    Note: Recompile with -Xlint:deprecation for details.

    18/07/23 11:54:47 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/2322b82e8ef7190a66357528d5fbddae/dm_user_attribute.jar

    18/07/23 11:54:47 INFO mapreduce.ExportJobBase: Beginning export of dm_user_attribute

    18/07/23 11:54:47 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar

    18/07/23 11:54:47 INFO Configuration.deprecation: mapred.map.max.attempts is deprecated. Instead, use mapreduce.map.maxattempts

    18/07/23 11:54:48 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative

    18/07/23 11:54:48 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative

    18/07/23 11:54:48 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps

    18/07/23 11:54:48 INFO client.RMProxy: Connecting to ResourceManager at 192.168.1.152:8032

    18/07/23 11:54:49 INFO input.FileInputFormat: Total input paths to process : 1

    18/07/23 11:54:49 INFO input.FileInputFormat: Total input paths to process : 1

    18/07/23 11:54:49 INFO mapreduce.JobSubmitter: number of splits:1

    18/07/23 11:54:49 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1528444677205_1338

    18/07/23 11:54:50 INFO impl.YarnClientImpl: Submitted application application_1528444677205_1338

    18/07/23 11:54:50 INFO mapreduce.Job: The url to track the job: http://daojia02:8088/proxy/application_1528444677205_1338/

    18/07/23 11:54:50 INFO mapreduce.Job: Running job: job_1528444677205_1338

    18/07/23 11:54:55 INFO mapreduce.Job: Job job_1528444677205_1338 running in uber mode : false

    18/07/23 11:54:55 INFO mapreduce.Job: map 0% reduce 0%

    18/07/23 11:55:00 INFO mapreduce.Job: map 100% reduce 0%

    18/07/23 11:55:01 INFO mapreduce.Job: Job job_1528444677205_1338 failed with state FAILED due to: Task failed task_1528444677205_1338_m_000000

    Job failed as tasks failed. failedMaps:1 failedReduces:0

    18/07/23 11:55:01 INFO mapreduce.Job: Counters: 8

    Job Counters

    Failed map tasks=1

    Launched map tasks=1

    Data-local map tasks=1

    Total time spent by all maps in occupied slots (ms)=2855

    Total time spent by all reduces in occupied slots (ms)=0

    Total time spent by all map tasks (ms)=2855

    Total vcore-seconds taken by all map tasks=2855

    Total megabyte-seconds taken by all map tasks=2923520

    18/07/23 11:55:01 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead

    18/07/23 11:55:01 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 13.576 seconds (0 bytes/sec)

    18/07/23 11:55:01 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead

    18/07/23 11:55:01 INFO mapreduce.ExportJobBase: Exported 0 records.

    18/07/23 11:55:01 ERROR tool.ExportTool: Error during export: Export job failed!

    当我看到这个控制台打印的信息时,犹如一万只草泥马狂奔而过,这是什么鬼?只告诉你导出失败,任务中断了,错误信息呢?你看到是不是也是一样的感觉呢?这该如何解决?从何入手呢?

    Sqoop的错误日志

    经过两天的各种搞头,最后终于知道了如何解决这个问题,这个问题不是具体的问题,但是想要知道具体的错误信息,在控制台是看不到的,只能到CDH的web管理界面去看,如下就告诉大家CDH的管理界面怎么找到Sqoop的这个任务日志。

    第一步

    如下图:点击YAEN进入YARN的详情界面。有人会问,为什么不是Sqoop的界面,Sqoop最终会转化为MR进行任务的执行,所以这里要看Sqoop的任务执行情况,还是要到YARN的详情界面去看。

    第二步

    如下图为YARN的详情界面,需要点击应用程序目录,进入任务的执行结果列表中,可以看到各个执行的任务,以及执行的结果,下图明显看到有一个错误。根据如下的操作进入下一个页面。

    第三步

    这个界面展示了单个任务的还算详细的任务信息,不过这不是我们最终要找的界面,看到如下图框起来的logs超链接字段,点击进入下一个页面。

    第四步

    看到这个界面,好像是找到了日志的界面,对不起,还没有,向下拉,你会看到如图的字样,这个页面只是展示了任务执行的流程,具体的错误信息还在另外一个页面。点击如图here超链接的字样,进入下一个页面。

    第五步

    经过前面的几个页面,我们终于进入了我们想要看到的页面,我们亲爱的错误页面,在这里,就可以看到这个任务的错误原因,这样就可以根据错误信息解决问题了。这个页面展示的错误信息的解决方法,网上基本都有,可以根据错误信息自行查找了。

    本人这里展现的问题,是因为Hive和MySQL的时间字段不匹配导致的,这里更改MySQL或者Hive的时间字段类型,让两边的类型保持一致,即可解决问题。

    真的没想到,CDH会这么坑,这个问题,整整折磨了我两天,不过还好,最终还是解决了,以后再遇到之后,就会可以立即解决了。

    展开全文
  • sqoop问题处理通用办法,首先详细描绘了一个sqoop实际问题得处理过程,然后延申完整得hadoop日志得处理过程。非常具体实用。
  • sqoop 导出hive数据到MySQL数据库当中

    千次阅读 2021-11-07 22:22:48
    我们想要将hive表中的数据导出到MySQL当中,MySQL中必须要有一个表来接收,并且该表的结构要和hive当中的表结构一致,不然会报错。 踩坑:hive当中的 string 字段对应 MySQL当中的 varchar 类型 直接上代码: ...
  • SQOOP 导出Hive数据到MySQL 汀桦坞 2018-07-08 12:31:52 50048 收藏 62 分类专栏: 大数据 Hive 版权 基本知识: Sqoop导出的基本用法:https://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_syntax_4的10....
  • 1、vim /etc/my.cnf添加 =========================== default-storage-engine = INNODB character-set-server = utf8 collation-server = utf8_general_ci [client] default-character-set = utf8 ...
  • 当我们使用mysql的时候一般情况下都会出现乱码情况,出现这种情况的主要原因一般就是导入的数据mysql数据的原始编码方式不同造成的,下面就是我的乱码案例: 咱们先看下自己的编码表 一般输入下面这个命令:...
  • Sqoop从数仓导出数据到Mysql sqoop export \ --connect "jdbc:mysql://node3:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8" \ --username root \ --password 123456 \ --driver ...
  • SQOOP 导出Hive数据到MySQL报错如下错误 Export job failed! at org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:445) at org.apache.sqoop.manager.SqlManager.exportTable(SqlManager....
  • mysql 表的字段和 hadoop 里的表字段一样。密码和用户也对但是就是报错。导出不了。各位有遇到这个情况吗?关键从输出上看不详细日志、。。。。19/04/03 21:44:35 INFO mapreduce.Job: The url to track the job: ...
  • sqoop从hive导出数据到mysql的三种方式

    千次阅读 2021-08-16 12:05:01
    –connectjdbc:mysql://localhost:3306/wht –username root –password cloudera –tablewht_test2 –fields-terminated-by ‘\t’ –export-dir /user/hive/warehouse/wht_test1 此方式需要考虑建hive表时...
  • sqoop export导出数据到mysql(追加/更新)

    千次阅读 2020-10-28 23:51:06
    准备数据: data.txt 1,zhangsan,18,2020-01-01 2,lisi,19,2020-02-02 3,wangwu,20,2020-03-03 4,zhaoliu,21,2020-04-04 5,tianqi,22,2020-05-05 6,luba,23,2020-06-06 7,sunjiu,24,2020-07-07 ...将数据h
  • sqoop导出hive数据到mysql避免空值

    千次阅读 2021-02-10 06:25:33
    问题(1)Sqoop导入导出Null存储一致性问题Hive中的Null在底层是以“”\N“”来存储,而mysql中的Null在底层就是Null,为了保证数据两端的一致性,在导出数据时采用--input-null-string和--input-null-non-string两个...
  • 今天在azkaban上查看定时任务调度时,发现一直运行的一个project竟然神奇的失败了,赶紧去查看,还好,只是最后一步ads_to_mysql出错,不会导致数据丢失,但是,后端报表无法显示正常数据了。 一、尝试手动运行 还是...
  • 从hdfs导出数据到mysql时使用命令bin/sqoop sqoop-export --connect jdbc:mysql://127.0.0.1:3306/databsename --username root --password 123456 --table tablename --export-dir exportdir命令是对的,但是有很多...
  • 【执行结果】数据成功导入但是有乱码2、网上查找原因后的Sqoop语句 【执行结果】数据导入失败并报错 3、不断试错后,注意关键点是编码问题检查数据库编码和表的编码发现都不是utf-8编码:数据库的编码: 表的编码...
  • sqoop导出数据到mysql

    千次阅读 2018-08-22 16:38:22
    bin/sqoop export \ --connect jdbc:mysql://node-1:3306/sqoopdb \ --username root \ --password hadoop \ --table t_oute_rate \--input-null-string '\\N' \ --input-null-non-string '\\N' \--input-fields-ter...
  • 导出数据到mysql数据库的时候一直有问题,在导入空字段的时候就出现下面这个错误。WARNhdfs.DFSClient:Caughtexceptionjava.lang.InterruptedExceptionatjava.lang.Object.wait(NativeMethod)atjava.lang.Thread....
  • 最近发现了sqoop的一个bug,在使用sqoop 1.4 7导出MySQL8.0以上版本的mysql表,采用整表导出时,列的顺序会发生颠倒、变化。 比如MySQL里是id,name。sqoop导出顺序的是name,id,...
  • --------------------export 导出--------------------------------- [root@mihaoyu151 tmp]# sqoop export \ > --connect jdbc:mysql://mihaoyu151:3306/mybatisdb \ > --username root \ > --password ...
  • 关于sqoop导出数据到mysql导出一半的时候出错的解决方案(:Data truncation: Data too long for column 'BORROWERNAME' at row 88)
  • 1 需求描述Hql统计出热搜关键词,使用Sqoop将结果从Hive导出到Mysql,其中关键字是中文,...2 Sqoop导出Hive数据到Mysql2.1 Mysql建表create table if not exists keyword_seach(dt varchar(30) not null,keyword ...
  • 我需要将数据从Hive传输到MySQL.这是我的sqoop命令:jdbc:mysql://mysqlserver --username username --password password --table test --columns "member_id,answer_id,answerer_id" -m 1 --export-dir /user/hive/...
  • 今天遇到一个问题,如下图所示,我用sqoop将hive中数据导出到mysql中,结果出现了以下情况:经过查找资料,终于解决了问题,下面我将自己的完整解决步骤做一下记录,如果能帮需要帮助的人,我会非常开心,最主要的...
  • 最近在做数据迁移的工作,使用到sqoop的导入和导出工具,具体操作是将hive当中的数据查询出来插入新的表当中,然后将数据又导入到mysql的关系型数据库当中。我将数据插入hive将的一个新的表当中,但是那插入...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 8,334
精华内容 3,333
关键字:

sqoop导出数据到mysql

mysql 订阅
友情链接: MFCLibraryReference.rar