shardingsphere proxy 是否支持 sqoop 传输数据？

Apache ShardingSphere
#1

shardingsphere proxy 是否支持 sqoop 传输数据？

#2

sqoop 是支持使用 mysql 协议的，我身边有大数据同学使用过，从 shardingsphere proxy 抽取数据。

#3

您好，我们这边试了一下sqoop通过proxy 入库，结果不支持，能否提供一个示例让我们参考一下呢？

#4

只是知道有团队用过，内部系统，无法提供 demo。
@novice-gamer 你可以把具体的版本和问题抛出来，针对问题进行解答。

#5

shardingsphere-proxy 5.1.0
语句：

sqoop-export \
--connect "jdbc:mysql://x.x.x.x:3307/customer_delivery_dev?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 'xxxxxx' \
--table csys_product_demand_statis_sx61 \
--input-null-string '\\N' \
--input-null-non-string '\\N' \
--input-fields-terminated-by '\t' \
--export-dir /daas/dsol/dal/bdcbv/sttt/building/dal_bdcbv_building_product_demand_sttt_m/month_id=202204/adcode=61 \
--m 1

错误信息：

22/04/22 15:37:41 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
22/04/22 15:37:41 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
22/04/22 15:37:41 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
22/04/22 15:37:41 INFO tool.CodeGenTool: Beginning code generation
Fri Apr 22 15:37:41 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
22/04/22 15:37:41 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `csys_product_demand_statis_sx61` AS t LIMIT 1
22/04/22 15:37:41 ERROR manager.CatalogQueryManager: Failed to list columns from query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = (SELECT SCHEMA())   AND TABLE_NAME = 'csys_product_demand_statis_sx61' 
java.sql.SQLException: Runtime exception: [unsupported table : `COLUMNS`]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2439)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1365)
        at org.apache.sqoop.manager.CatalogQueryManager.getColumnNames(CatalogQueryManager.java:150)
        at org.apache.sqoop.orm.ClassWriter.getColumnNames(ClassWriter.java:1827)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1680)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
        at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:63)
        at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:99)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
22/04/22 15:37:41 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: java.sql.SQLException: Runtime exception: [unsupported table : `COLUMNS`]
java.lang.RuntimeException: java.sql.SQLException: Runtime exception: [unsupported table : `COLUMNS`]
        at org.apache.sqoop.manager.CatalogQueryManager.getColumnNames(CatalogQueryManager.java:165)
        at org.apache.sqoop.orm.ClassWriter.getColumnNames(ClassWriter.java:1827)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1680)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
        at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:63)
        at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:99)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Caused by: java.sql.SQLException: Runtime exception: [unsupported table : `COLUMNS`]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2439)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1365)
        at org.apache.sqoop.manager.CatalogQueryManager.getColumnNames(CatalogQueryManager.java:150)
        ... 11 more
#6

这是 sqoop 发送了查询元数据的 SQL，而 INFORMATION_SCHEMA.COLUMNS 这个表还没有被支持，导致的异常。
在分布式场景下，INFORMATION_SCHEMA 的信息并不能由底层数据库查询获得，而是需要 SS 进行模拟和数据聚合。这一部分工作社区还在进行中，COLUMNS 预计很快就会得到支持。

#7

好的，多谢解答，期待此问题可以解决

#8

我查找到一个方法可以让sqoop方法通过sharding-proxy导入数据，sqoop export时加上--driver com.mysql.jdbc.Driver参数。

1 个赞
京ICP备2021015875号