Search code examples
sqoop

List columns with sqoop

I have found the following commands very usefull to see what my source database looks like:

sqoop-list-databases
sqoop-list-tables

However, there does not appear to be a command to list the columns in a table, which would be a logical step.

My question is now:

How can I get the list of columns from a table via Sqoop?

Solution

  • Unfortunately there is no command like sqoop-list-columns, however with some creativity there is a workaround:

    Run an import, and import the fieldnames.

    Here is an example, for how this can be done when connecting to a SQL Server database:

    sqoop import --m 1 --connect 'jdbc:sqlserver://nameofmyserver; database=nameofmydatabase; username=dennisjaheruddin; password=mypassword' --query "SELECT column_name, DATA_TYPE FROM INFORMATION_SCHEMA.Columns WHERE table_name='mytableofinterest' AND \$CONDITIONS" --target-dir 'mytableofinterest_column_name'

    This will retreive the column names and write them to a file, which you can then inspect manually.

    Of course this can be expanded to get other metadata (e.g. field types). Note that you will need a slightly different SELECT statement if you are connecting to a different database type, but that should be easy to find.