TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
Management Administration SettingAttribute
Management Administration SettingAttributeGroup
Management Administration SettingAttributeValue
Management Administration SettingValue
Management ape DatabaseScriptLog
Management ape DatabaseLog
Management Common Language
Management Common ThirdPartyType
Management Common Country
Management Company DistributorCow
Management Company CustomerSetting
Management Company CustomerSettingAttributeValue
The above output is of the following query executed on the SQL Server(note 'dbo' is excluded) :
USE Management
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA NOT IN ('dbo') order by TABLE_SCHEMA;HDP-2.4.2.0-258 installed using Ambari 2.2.2.0, Sqoop 1.4.6.2.4.2.0-258.
Now when I do a normal sqoop list-tables with 'database=Management' in the --connect string, I get the tables that are part of the dbo tables.
As per the Sqoop documentation for Microsoft SQL Connector, I tried using the --schema option(it's position in the command didn't seem to make any difference!) but it doesn't compile :
sqoop list-tables --connect 'jdbc:sqlserver://IP;database=Management' --username __ --password __ --schema Administration --verbose
16/08/09 16:44:32 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.3.2.0-2950
16/08/09 16:44:32 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
16/08/09 16:44:32 ERROR tool.BaseSqoopTool: Error parsing arguments for list-tables:
16/08/09 16:44:32 ERROR tool.BaseSqoopTool: Unrecognized argument: --schema
16/08/09 16:44:32 ERROR tool.BaseSqoopTool: Unrecognized argument: Administration
16/08/09 16:44:32 ERROR tool.BaseSqoopTool: Unrecognized argument: --verbose
My ultimate objective is to import the tables as hcatalog tables but NO perm-comb to specify non-default schema works !
e.g:
sqoop import --connect 'jdbc:sqlserver://IP;database=Management' --username __ --password __ --table "Administration.SettingAttribute" --hcatalog-database Management --hcatalog-table Administration_SettingAttribute_orc --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile"
16/08/09 16:51:54 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.3.2.0-2950
16/08/09 16:51:54 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
16/08/09 16:51:54 INFO manager.SqlManager: Using default fetchSize of 1000
16/08/09 16:51:54 INFO tool.CodeGenTool: Beginning code generation
16/08/09 16:51:54 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM [Administration.SettingAttribute] AS t WHERE 1=0
16/08/09 16:51:54 ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'Administration.SettingAttribute'.
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'Administration.SettingAttribute'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:758)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:767)
at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:270)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:241)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:227)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1845)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
at org.apache.sqoop.Sqoop.run(Sqoop.java:148)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)
at org.apache.sqoop.Sqoop.main(Sqoop.java:244)
16/08/09 16:51:54 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1651)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
at org.apache.sqoop.Sqoop.run(Sqoop.java:148)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)
at org.apache.sqoop.Sqoop.main(Sqoop.java:244)
