How to provide runtime parameter in sql query to import data in hive.

Database

How to provide runtime parameter in sql query to import data in hive.

Hi,

I want to import data from mysql to hive. My working query is as follows:

./sqoop import --connect jdbc:mysql://ip:3306/sqoop_demo --username root -P  --query 'SELECT * FROM products2 WHERE productID=1005 AND $CONDITIONS' -m 1 --target-dir /user/hive/warehouse/products2

I dont want to hard code productID=1005, how can i provide value to it at runtime?

Any help would be appreciated.

2 REPLIES

Re: How to provide runtime parameter in sql query to import data in hive.

Have got solution...:)

Re: How to provide runtime parameter in sql query to import data in hive.

Trying to import data from TD to IBM Biginsight V3.0.1

Have my jars in /opt/ibm/biginsights/sqoop/lib

teradata-connector-1.3.3.jar

terajdbc4.jar

ANy guidance is greatly appreciated

sqoop import --connect jdbc:teradata://TDDEV/database=EDI_SPT --username MYUSER --password V_DVE2014 --query 'select * from ab_test where ln = 1 and $CONDITIONS' --verbose --target-dir /gpfs/user/biadmin/ --split-by ln_1

14/12/05 13:38:08 DEBUG tool.BaseSqoopTool: Enabled debug logging.

14/12/05 13:38:08 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

14/12/05 13:38:08 DEBUG sqoop.ConnFactory: sqoop.connection.factories is set; ignoring managers.d

14/12/05 13:38:08 DEBUG sqoop.ConnFactory: Loaded manager factory: com.ibm.biginsights.ie.sqoop.BIConnectionFactory

14/12/05 13:38:08 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory

14/12/05 13:38:08 DEBUG sqoop.ConnFactory: Trying ManagerFactory: com.ibm.biginsights.ie.sqoop.BIConnectionFactory

14/12/05 13:38:08 INFO manager.SqlManager: Using default fetchSize of 1000

14/12/05 13:38:08 DEBUG sqoop.ConnFactory: Instantiated ConnManager com.ibm.biginsights.ie.sqoop.td.TeradataFastManager@74d8171b

14/12/05 13:38:08 INFO tool.CodeGenTool: Beginning code generation

14/12/05 13:38:08 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.

org.ietf.jgss.GSSException, major code: 11, minor code: -520,093,543

        major string: General failure, unspecified at GSSAPI level

        minor string: UserFile parameter null

        at com.teradata.tdgss.jtdgss.TdgssParseXml.<init>(TdgssParseXml.java:81)

        at com.teradata.tdgss.jtdgss.TdgssConfigApi.<init>(TdgssConfigApi.java:81)

        at com.teradata.tdgss.jtdgss.TdgssConfigApi.<clinit>(TdgssConfigApi.java:44)

        at com.teradata.tdgss.jtdgss.TdgssManager.<init>(TdgssManager.java:40)

        at com.teradata.tdgss.jtdgss.TdgssManager.<clinit>(TdgssManager.java:29)

        at com.teradata.jdbc.jdbc.GenericTeraEncrypt.getTDgssVersion(GenericTeraEncrypt.java:640)

        at com.teradata.jdbc.jdbc_4.parcel.ClientConfigParcel.toStream(ClientConfigParcel.java:54)

        at com.teradata.jdbc.jdbc_4.io.TDPacket.addParcel(TDPacket.java:98)

        at com.teradata.jdbc.jdbc.GenericLogonController.run(GenericLogonController.java:94)

        at com.teradata.jdbc.jdbc_4.TDSession.<init>(TDSession.java:205)

        at com.teradata.jdbc.jdk6.JDK6_SQL_Connection.<init>(JDK6_SQL_Connection.java:35)

        at com.teradata.jdbc.jdk6.JDK6ConnectionFactory.constructSQLConnection(JDK6ConnectionFactory.java:25)

        at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:179)

        at com.teradata.jdbc.jdbc.ManagerConnectionBase.<init>(ManagerConnectionBase.java:47)

        at com.teradata.jdbc.jdbc.fastexport.FastExportManagerConnection.<init>(FastExportManagerConnection.java:61)

        at com.teradata.jdbc.jdk6.JDK6_FastExportManager_Connection.<init>(JDK6_FastExportManager_Connection.java:28)

        at com.teradata.jdbc.jdk6.JDK6ConnectionFactory.constructFastExportManagerConnection(JDK6ConnectionFactory.java:43)

        at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:210)

        at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:169)

        at com.teradata.jdbc.TeraDriver.doConnect(TeraDriver.java:232)

        at com.teradata.jdbc.TeraDriver.connect(TeraDriver.java:158)

        at java.sql.DriverManager.getConnection(DriverManager.java:582)

        at java.sql.DriverManager.getConnection(DriverManager.java:226)

        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:753)

        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)

        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:613)

        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:636)

        at com.ibm.biginsights.ie.sqoop.td.TeradataFastManager.executeStmt(TeradataFastManager.java:182)

        at com.ibm.biginsights.ie.sqoop.td.TeradataFastManager.getColumnTypesForRawQuery(TeradataFastManager.java:168)

        at org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.java:226)

        at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:292)

        at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1262)

        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1072)

        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:82)

        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:390)

        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)

        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)

        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)

        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)

        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)

        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)

        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)

Exception in thread "main" java.lang.ExceptionInInitializerError

        at java.lang.J9VMInternals.ensureError(J9VMInternals.java:167)

        at java.lang.J9VMInternals.recordInitializationFailure(J9VMInternals.java:156)

        at com.teradata.jdbc.jdbc.GenericTeraEncrypt.getTDgssVersion(GenericTeraEncrypt.java:640)

        at com.teradata.jdbc.jdbc_4.parcel.ClientConfigParcel.toStream(ClientConfigParcel.java:54)

        at com.teradata.jdbc.jdbc_4.io.TDPacket.addParcel(TDPacket.java:98)

        at com.teradata.jdbc.jdbc.GenericLogonController.run(GenericLogonController.java:94)

        at com.teradata.jdbc.jdbc_4.TDSession.<init>(TDSession.java:205)

        at com.teradata.jdbc.jdk6.JDK6_SQL_Connection.<init>(JDK6_SQL_Connection.java:35)

        at com.teradata.jdbc.jdk6.JDK6ConnectionFactory.constructSQLConnection(JDK6ConnectionFactory.java:25)

        at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:179)

        at com.teradata.jdbc.jdbc.ManagerConnectionBase.<init>(ManagerConnectionBase.java:47)

        at com.teradata.jdbc.jdbc.fastexport.FastExportManagerConnection.<init>(FastExportManagerConnection.java:61)

        at com.teradata.jdbc.jdk6.JDK6_FastExportManager_Connection.<init>(JDK6_FastExportManager_Connection.java:28)

        at com.teradata.jdbc.jdk6.JDK6ConnectionFactory.constructFastExportManagerConnection(JDK6ConnectionFactory.java:43)

        at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:210)

        at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:169)

        at com.teradata.jdbc.TeraDriver.doConnect(TeraDriver.java:232)

        at com.teradata.jdbc.TeraDriver.connect(TeraDriver.java:158)

        at java.sql.DriverManager.getConnection(DriverManager.java:582)

        at java.sql.DriverManager.getConnection(DriverManager.java:226)

        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:753)

        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)

        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:613)

        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:636)

        at com.ibm.biginsights.ie.sqoop.td.TeradataFastManager.executeStmt(TeradataFastManager.java:182)

        at com.ibm.biginsights.ie.sqoop.td.TeradataFastManager.getColumnTypesForRawQuery(TeradataFastManager.java:168)

        at org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.java:226)

        at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:292)

        at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1262)

        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1072)

        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:82)

        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:390)

        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)

        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)

        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)

        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)

        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)

        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)

        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)

Caused by: java.lang.NullPointerException

        at com.teradata.tdgss.jtdgss.TdgssConfigApi.GetMechanisms(TdgssConfigApi.java:141)

        at com.teradata.tdgss.jtdgss.TdgssManager.<init>(TdgssManager.java:43)

        at com.teradata.tdgss.jtdgss.TdgssManager.<clinit>(TdgssManager.java:29)

        ... 37 more