将 Impala 表从 HDFS 导出到 MySQL [英] Export Impala Table from HDFS to MySQL

问题描述

我正在尝试使用 Sqoop 将一个黑斑羚表从 HDFS 导出到 MySQL.该表已经在 MySQL 中创建，并且两个表的架构应该匹配.

I'm trying to use Sqoop to export an impala table from HDFS to MySQL. The table has already been made in MySQL and the schema of the two tables should match.

Impala table information: 
1   start_date  string  
2   start_station_code  string  
3   end_date    string  
4   end_station_code    string  
5   duration_sec    int 
6   is_member   int 
7   cnt bigint  

Impala table sample:
2019-05-05 14:07:42100022019-05-05 14:31:087143140611322
2019-05-08 17:51:57100022019-05-08 17:55:29705221101322
2019-05-05 14:07:40100022019-05-05 14:31:087143140711322
2019-05-07 09:55:48100022019-05-07 10:02:28672439911322
2019-05-03 06:54:38100022019-05-03 06:59:51705231201322
2019-05-07 09:56:33100022019-05-07 10:02:17705234311322
2019-05-05 14:06:40100022019-05-05 14:18:04642768411322
2019-05-01 08:54:36100022019-05-01 08:58:20705222301322
2019-05-02 09:17:22100022019-05-02 09:22:16692129401322
2019-05-02 09:16:37100022019-05-02 09:19:30705217201322
2019-05-06 07:09:54100022019-05-06 07:18:45608453111322

MySQL Table information:
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| start_date         | varchar(10) | YES  |     | NULL    |       |
| start_station_code | varchar(20) | YES  |     | NULL    |       |
| end_date           | varchar(20) | YES  |     | NULL    |       |
| end_station_code   | varchar(20) | YES  |     | NULL    |       |
| duration_sec       | int(11)     | YES  |     | NULL    |       |
| is_member          | int(11)     | YES  |     | NULL    |       |
| cnt                | bigint(20)  | YES  |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+



Export code:
sqoop export --connect jdbc:mysql://localhost/oozie --username root --password root --table bixirides_export --export-dir /user/hive/warehouse/impala_out/6* -m 1 --input-fields-terminated-by "|";

由于某种原因，一旦 Map 任务达到 100%，sqoop 导出就会失败.架构应该正确匹配，但由于某种原因导出失败.

For some reason the sqoop export fails as soon as the Map task reaches 100%. The schema should match properly, but for some reason the export fails.

Error Message:
ERROR tool.ExportTool: Error during export:
Export job failed!

推荐答案

我看到几个问题.. 基于你的 qn

I see couple of issues.. based on your qn

1. 开始和结束日期是 varchar(10) 但数据大小似乎比这更长.2019-05-05 14:07:42

1. start and end date are varchar(10) but data size seems to longer than that. 2019-05-05 14:07:42

我认为分隔符为 |但在 Hive 表中看不到.

I see delimiter as | but don't see that in Hive table.

您是否使用

行格式以|"结尾的分隔字段以 '\n' 结尾的行存储为文本文件

ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile

这篇关于将 Impala 表从 HDFS 导出到 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！