import the views of a sql server database

[#1]

Hi,

I have a problem with importing views of a sql server database.

To import the tables i do:

for table in $@

do

sqoop import \ --connect "jdbc:jtds:sqlserver://xxxxxxxxx:xxxxxx;databaseName=xxxxxx;user=xxxxxx;password=xxxxxxxx;instance=xxxxxx" \ --driver net.sourceforge.jtds.jdbc.Driver \ --username xxxx \ --table $table \ --hive-import \ --hive-table xxxxxxxxx.$table -m 1

done

If I want to import the views how should I change my script please

Thank !

[#2]

You could use the same script to import the data from your views: Sqoop will fetch the data from your view and store it into Hive/HDFS.

If you don't want to import the data but just want to create a view on Hive, then take the definition of your view in SQLserver (DDL) and create the same view in Hive (some few adaptations might be needed, check the documentation (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create/Drop/Al... ).

A recommendation I would also give you, is to do the Sqoop commands in parallel. Otherwise, if you have many tables and you use "-m 1", it will take a lot of time. You can check the script I wrote for that:

https://community.hortonworks.com/articles/23602/sqoop-fetching-lot-of-tables-in-parallel.html

[#3]

Thank you for your suggestions

[#4 · Accepted solution]

@alain TSAFACK

i don't think sqoop has any different way to import views, it should work same as for tables. Can you please let us know what issue you are facing i.e any error messages? You can also use free form queries to import the view data.

Thanks

[#5]

Hi !!!

Thank you. But I found what to do . I just had to add in my script, --split-by "colonne_id"

[#6]

@alain TSAFACK can u share the script how u imported view using sqoop to hdfs