Can Sqoop export create a new table?

Question

It is possible to export data from HDFS to RDBMS table using Sqoop. But it seems like we need to have existing table.

Is there some parameter to tell Sqoop do the 'CREATE TABLE' thing and export data to this newly crated table?

If yes, is it going to work with Oracle?

Trimetric

Answer 1

I'm afraid that Sqoop do not support creating tables in the RDBMS at the moment. Sqoop uses the table in RDBMS to get metadata (number of columns and their data types), so I'm not sure where Sqoop could get the metadata to create the table for you.

Argonaut

Answer 2

You can actually execute arbitrary SQL queries and DDL via sqoop eval, at least with MySQL and MSSQL. I'd expect it to work with Oracle as well. MSSQL example:

sqoop eval --connect 'jdbc:sqlserver://<DB SERVER>:<DB PORT>;
database=<DB NAME>'  --query "CREATE TABLE..."
--username <USERNAME> -P

Novel

Sqoop user guide mentions that eval should not be used in production workflows - "The eval tool is provided for evaluation purpose only. You can use it to verify database connection from within the Sqoop or to test simple queries. It’s not suppose to be used in production workflows." – Yonkers

Answer 3

I noticed you use Oracle too. Certain sqoop vendor-specific sqoop connectors support that, including Oracle. Sqoop's Oracle direct connect mode has option to do that

https://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_create_oracle_tables

24.8.5.4. Create Oracle Tables

-Doraoop.template.table=TemplateTableName

Creates OracleTableName by replicating the structure and data types of TemplateTableName. TemplateTableName is a table that exists in Oracle prior to executing the Sqoop command.

ps. You'll have to use --direct sqoop export option to activate sqoop direct mode = 'Data Connector for Oracle and Hadoop' (aka OraOOP - older name).

Vespiary