question Re: import the views of a sql server database in Archives of Support Questions (Read Only)

import the views of a sql server database

nanyim_alain — Thu, 19 May 2016 15:14:42 GMT

Hi,

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

To import the tables i do:

for table in $@

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 !

Re: import the views of a sql server database

sluangsay — Thu, 19 May 2016 17:09:10 GMT

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/AlterView ).

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

Re: import the views of a sql server database

jyadav — Thu, 19 May 2016 17:13:08 GMT

@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

Re: import the views of a sql server database

nanyim_alain — Mon, 23 May 2016 16:14:41 GMT

Hi !!!

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

Re: import the views of a sql server database

nanyim_alain — Mon, 23 May 2016 16:15:25 GMT

Thank you for your suggestions

Re: import the views of a sql server database

abhishekkundu86 — Tue, 13 Aug 2019 19:44:39 GMT

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