Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Sqoop import SQL Server NON-DEFAULT schema

Solved Go to solution
Highlighted

Sqoop import SQL Server NON-DEFAULT schema

Expert Contributor
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)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Sqoop import SQL Server NON-DEFAULT schema

Guru

Have you tried using -- --schema? You will need two sets of '--' there.

View solution in original post

3 REPLIES 3
Highlighted

Re: Sqoop import SQL Server NON-DEFAULT schema

Guru

Have you tried using -- --schema? You will need two sets of '--' there.

View solution in original post

Highlighted

Re: Sqoop import SQL Server NON-DEFAULT schema

Expert Contributor

While I am grateful for your answer, I'm unsure whether I'm stupid or lazy(or both!) ;)

Just in case someone needs in the future :

sqoop list-tables --connect 'jdbc:sqlserver://<hostname/IP>;database=<db-name>' --username <user-name> --password <password> -- --schema <schema-name>

Highlighted

Re: Sqoop import SQL Server NON-DEFAULT schema

New Contributor

Could you please explain why we need to add extra --  in giving command for --schema.

 

Don't have an account?
Coming from Hortonworks? Activate your account here