十三（1）、sqoop遇到的问题

最新推荐文章于 2023-02-13 16:01:28 发布

原创最新推荐文章于 2023-02-13 16:01:28 发布 · 926 阅读

0 ·

CC 4.0 BY-SA版权

CDH 专栏收录该内容

2 篇文章

订阅专栏

本文描述了使用sqoop从MySQL导入数据到Hive时遇到的两个问题：1) 当Hive表为Parquet格式时，导入报错；2) 使用sqoop连接PostgreSQL列出数据库失败。针对第一个问题，当不使用`--as-parquetfile`选项，数据能正常导入Textfile格式的Hive表。尝试直接指定HDFS路径，数据未导入且目标目录消失。第二个问题通过修改pg_hba.conf配置解决了连接问题。

1、从mysql抽数到hive，hive表为parquet存储格式，导入语句为：

sqoop import
–connect jdbc:mysql://${MYSQL_HOST}😒{MYSQL_PORT}/sx_channel?tinyInt1isBit=false
–username ${MYSQL_USER}
–password ${MYSQL_PSW}
–table $1
–columns $3
–hive-delims-replacement “”
–hive-table test.$2
–hive-partition-key part_dt
–hive-partition-value ‘2019-05-13’
–hive-import
–hive-overwrite
–delete-target-dir
–as-parquetfile
–m 1
–null-string ‘\N’
–null-non-string ‘\N’

报错：

19/05/13 17:33:04 ERROR sqoop.Sqoop: Got exception running Sqoop: org.kitesdk.data.ValidationException: Dataset name test.xiaoneng_improt is not alphanumeric (plus '_')
org.kitesdk.data.ValidationException: Dataset name test.xiaoneng_improt is not alphanumeric (plus '_')
	at org.kitesdk.data.ValidationException.check(ValidationException.java:60)
	at org.kitesdk.data.spi.Compatibility.checkDatasetName(Compatibility.java:109)
	at org.kitesdk.data.spi.hive.HiveAbstractMetadataProvider.exists(HiveAbstractMetadataProvider.java:158)
	at org.kitesdk.data.spi.filesystem.FileSystemDatasetRepository.exists(FileSystemDatasetRepository.java:266)
	at org.kitesdk.data.Datasets.exists(Datasets.java:633)
	at org.kitesdk.data.Datasets.exists(Datasets.java:650)
	at org.apache.sqoop.mapreduce.ParquetJob.configureImportJob(ParquetJob.java:118)
	at org.apache.sqoop.mapreduce.DataDrivenImportJob.configureMapper(DataDrivenImportJob.java:132)
	at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:263)
	at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
	at org.apache.sqoop.manager.MySQLManager.importTable(MySQLManager.java:124)
	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:526)
	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:634)
	at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:232)
	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:241)
	at org.apache.sqoop.Sqoop.main(Sqoop.java:250)

但是hive表如果是textfile格式，然后sqoop导入语句去掉–as-parquetfile，就没问题，能正常导入

如果sqoop导入语句改为：

sqoop import
–connect jdbc:mysql://${MYSQL_HOST}😒{MYSQL_PORT}/sx_channel?tinyInt1isBit=false
–username ${MYSQL_USER}
–password ${MYSQL_PSW}
–table $1
–columns $3
–hive-delims-replacement “”
–target-dir /user/hive/warehouse/test.db/xiaoneng_improt/part_dt=2019-05-13
–hive-import
–hive-overwrite
–delete-target-dir
–as-parquetfile
–m 1
–null-string ‘\N’
–null-non-string ‘\N’

执行上面这个导入语句之后，查看hdfs，数据并没有导入，而且目录part_dt=2019-05-13还消失了

2、cdh上的sqoop连接postgresql，
sqoop list-databases --connect jdbc:postgresql://192.168.30.246:5432 --username postgres --password *******
报错：

no pg_hba.conf entry for host "192.168.74.81", user "postgres", database "postgres", SSL off

解决办法：https://www.cnblogs.com/chinadyw/p/3507207.html
在pg_hba.conf配置文件里面添加：host all all 0.0.0.0/0 md5