You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by ayan guha <gu...@gmail.com> on 2016/04/06 06:26:32 UTC
Sqoop on Spark
Hi All
Asking opinion: is it possible/advisable to use spark to replace what sqoop
does? Any existing project done in similar lines?
--
Best Regards,
Ayan Guha
Re: Sqoop on Spark
Posted by Mich Talebzadeh <mi...@gmail.com>.
Pretty simple assuming that you have ojdbc6.jar in $SQOOP_HOME/lib
sqoop import --connect "jdbc:oracle:thin:@rhes564:1521:mydb" --username
hddtester -P \
--query "select * from hddtester.t where \
\$CONDITIONS" \
--split-by object_id \
--hive-import
--create-hive-table
--hive-table "OracleHadoop.t"
--target-dir "t"
sqoop import --connect "jdbc:oracle:thin:@rhes564:1521:mydb" --username
hddtester -P \
--query "select * from hddtester.t where \
\$CONDITIONS" \
--split-by object_id \
--as-avrodatafile \
--target-dir "/tmp/myt" \
--compress \
--num-mappers 20 \
--incremental append \
--check-column object_id \
--last-value 1000000
HTH
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 6 April 2016 at 05:47, ayan guha <gu...@gmail.com> wrote:
> Hi
>
> Thanks for reply. My use case is query ~40 tables from Oracle (using index
> and incremental only) and add data to existing Hive tables. Also, it would
> be good to have an option to create Hive table, driven by job specific
> configuration.
>
> What do you think?
>
> Best
> Ayan
>
> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
> wrote:
>
>> Hi,
>>
>> It depends on your use case using sqoop.
>> What's it like?
>>
>> // maropu
>>
>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>
>>> Hi All
>>>
>>> Asking opinion: is it possible/advisable to use spark to replace what
>>> sqoop does? Any existing project done in similar lines?
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>
>>
>>
>> --
>> ---
>> Takeshi Yamamuro
>>
>
>
>
> --
> Best Regards,
> Ayan Guha
>
Re: Sqoop on Spark
Posted by Mich Talebzadeh <mi...@gmail.com>.
Yes JDBC is another option. Need to be aware of some conversion issues like
spark does like CHAR types etc. You best bet is to do the conversion when
fetching data from Oracle itself.
var _ORACLEserver : String = "jdbc:oracle:thin:@rhes564:1521:mydb"
var _username : String = "sh"
var _password : String = "xxxx"
val c = HiveContext.load("jdbc",
Map("url" -> _ORACLEserver,
"dbtable" -> "(SELECT to_char(CHANNEL_ID) AS CHANNEL_ID, CHANNEL_DESC FROM
sh.channels)",
"user" -> _username,
"password" -> _password))
c.registerTempTable("t_c")
Then put the data from t_c table into Oracle table
HTH
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 6 April 2016 at 10:34, Jorge Sánchez <jo...@gmail.com> wrote:
> Ayan,
>
> there was a talk in spark summit
> https://spark-summit.org/2015/events/Sqoop-on-Spark-for-Data-Ingestion/
> Apparently they had a lot of problems and the project seems abandoned.
>
> If you just have to do simple ingestion of a full table or a simple query,
> just use Sqoop as suggested by Mich, but if your use case requires further
> transformation of the data, I'd suggest you try Spark connecting to Oracle
> using JDBC and then having the data as a Dataframe.
>
> Regards.
>
> 2016-04-06 6:59 GMT+01:00 ayan guha <gu...@gmail.com>:
>
>> Thanks guys for feedback.
>>
>> On Wed, Apr 6, 2016 at 3:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>>> I do not think you can be more resource efficient. In the end you have
>>> to store the data anyway on HDFS . You have a lot of development effort for
>>> doing something like sqoop. Especially with error handling.
>>> You may create a ticket with the Sqoop guys to support Spark as an
>>> execution engine and maybe it is less effort to plug it in there.
>>> Maybe if your cluster is loaded then you may want to add more machines
>>> or improve the existing programs.
>>>
>>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>>
>>> One of the reason in my mind is to avoid Map-Reduce application
>>> completely during ingestion, if possible. Also, I can then use Spark stand
>>> alone cluster to ingest, even if my hadoop cluster is heavily loaded. What
>>> you guys think?
>>>
>>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com>
>>> wrote:
>>>
>>>> Why do you want to reimplement something which is already there?
>>>>
>>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>>
>>>> Hi
>>>>
>>>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>>>> index and incremental only) and add data to existing Hive tables. Also, it
>>>> would be good to have an option to create Hive table, driven by job
>>>> specific configuration.
>>>>
>>>> What do you think?
>>>>
>>>> Best
>>>> Ayan
>>>>
>>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <linguin.m.s@gmail.com
>>>> > wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> It depends on your use case using sqoop.
>>>>> What's it like?
>>>>>
>>>>> // maropu
>>>>>
>>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>>
>>>>>> Hi All
>>>>>>
>>>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>>>> sqoop does? Any existing project done in similar lines?
>>>>>>
>>>>>> --
>>>>>> Best Regards,
>>>>>> Ayan Guha
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> ---
>>>>> Takeshi Yamamuro
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Best Regards,
>>>> Ayan Guha
>>>>
>>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>
>
Re: Sqoop on Spark
Posted by Jorge Sánchez <jo...@gmail.com>.
Ayan,
there was a talk in spark summit
https://spark-summit.org/2015/events/Sqoop-on-Spark-for-Data-Ingestion/
Apparently they had a lot of problems and the project seems abandoned.
If you just have to do simple ingestion of a full table or a simple query,
just use Sqoop as suggested by Mich, but if your use case requires further
transformation of the data, I'd suggest you try Spark connecting to Oracle
using JDBC and then having the data as a Dataframe.
Regards.
2016-04-06 6:59 GMT+01:00 ayan guha <gu...@gmail.com>:
> Thanks guys for feedback.
>
> On Wed, Apr 6, 2016 at 3:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>
>> I do not think you can be more resource efficient. In the end you have to
>> store the data anyway on HDFS . You have a lot of development effort for
>> doing something like sqoop. Especially with error handling.
>> You may create a ticket with the Sqoop guys to support Spark as an
>> execution engine and maybe it is less effort to plug it in there.
>> Maybe if your cluster is loaded then you may want to add more machines or
>> improve the existing programs.
>>
>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>
>> One of the reason in my mind is to avoid Map-Reduce application
>> completely during ingestion, if possible. Also, I can then use Spark stand
>> alone cluster to ingest, even if my hadoop cluster is heavily loaded. What
>> you guys think?
>>
>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>>> Why do you want to reimplement something which is already there?
>>>
>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>
>>> Hi
>>>
>>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>>> index and incremental only) and add data to existing Hive tables. Also, it
>>> would be good to have an option to create Hive table, driven by job
>>> specific configuration.
>>>
>>> What do you think?
>>>
>>> Best
>>> Ayan
>>>
>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> It depends on your use case using sqoop.
>>>> What's it like?
>>>>
>>>> // maropu
>>>>
>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>
>>>>> Hi All
>>>>>
>>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>>> sqoop does? Any existing project done in similar lines?
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> ---
>>>> Takeshi Yamamuro
>>>>
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>
>
> --
> Best Regards,
> Ayan Guha
>
Re: Sqoop on Spark
Posted by ayan guha <gu...@gmail.com>.
Thanks guys for feedback.
On Wed, Apr 6, 2016 at 3:44 PM, Jörn Franke <jo...@gmail.com> wrote:
> I do not think you can be more resource efficient. In the end you have to
> store the data anyway on HDFS . You have a lot of development effort for
> doing something like sqoop. Especially with error handling.
> You may create a ticket with the Sqoop guys to support Spark as an
> execution engine and maybe it is less effort to plug it in there.
> Maybe if your cluster is loaded then you may want to add more machines or
> improve the existing programs.
>
> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>
> One of the reason in my mind is to avoid Map-Reduce application completely
> during ingestion, if possible. Also, I can then use Spark stand alone
> cluster to ingest, even if my hadoop cluster is heavily loaded. What you
> guys think?
>
> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>
>> Why do you want to reimplement something which is already there?
>>
>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>
>> Hi
>>
>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>> index and incremental only) and add data to existing Hive tables. Also, it
>> would be good to have an option to create Hive table, driven by job
>> specific configuration.
>>
>> What do you think?
>>
>> Best
>> Ayan
>>
>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> It depends on your use case using sqoop.
>>> What's it like?
>>>
>>> // maropu
>>>
>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>
>>>> Hi All
>>>>
>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>> sqoop does? Any existing project done in similar lines?
>>>>
>>>> --
>>>> Best Regards,
>>>> Ayan Guha
>>>>
>>>
>>>
>>>
>>> --
>>> ---
>>> Takeshi Yamamuro
>>>
>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
--
Best Regards,
Ayan Guha
Re: Sqoop on Spark
Posted by Mich Talebzadeh <mi...@gmail.com>.
Sorry are you referring to Hive as a relational Data Warehouse in this
scenario. The assumption here is that data is coming from a relational
database (Oracle) so IMO the best storage for it in Big Data World is
another DW adaptive to SQL. Spark is a powerful query tool and together
with Hive as a backbone of storage provides a powerful framework to
anything. the performance is pretty fast indeed much faster compared to
MapR that Sqoop uses be default.
Anyway you are not confined to a table in Hive. You can take that data from
JDBC and do whatever is needed. There is no constraint in here.
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 6 April 2016 at 23:29, Jörn Franke <jo...@gmail.com> wrote:
> Well I am not sure, but using a database as a storage, such as relational
> databases or certain nosql databases (eg MongoDB) for Spark is generally a
> bad idea - no data locality, it cannot handle real big data volumes for
> compute and you may potentially overload an operational database.
> And if your job fails for whatever reason (eg scheduling ) then you have
> to pull everything out again. Sqoop and HDFS seems to me the more elegant
> solution together with spark. These "assumption" on parallelism have to be
> anyway made with any solution.
> Of course you can always redo things, but why - what benefit do you
> expect? A real big data platform has to support anyway many different tools
> otherwise people doing analytics will be limited.
>
> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
>
> I don’t think its necessarily a bad idea.
>
> Sqoop is an ugly tool and it requires you to make some assumptions as a
> way to gain parallelism. (Not that most of the assumptions are not valid
> for most of the use cases…)
>
> Depending on what you want to do… your data may not be persisted on HDFS.
> There are use cases where your cluster is used for compute and not storage.
>
> I’d say that spending time re-inventing the wheel can be a good thing.
> It would be a good idea for many to rethink their ingestion process so
> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
> that term from Dean Wampler. ;-)
>
> Just saying. ;-)
>
> -Mike
>
> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> I do not think you can be more resource efficient. In the end you have to
> store the data anyway on HDFS . You have a lot of development effort for
> doing something like sqoop. Especially with error handling.
> You may create a ticket with the Sqoop guys to support Spark as an
> execution engine and maybe it is less effort to plug it in there.
> Maybe if your cluster is loaded then you may want to add more machines or
> improve the existing programs.
>
> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>
> One of the reason in my mind is to avoid Map-Reduce application completely
> during ingestion, if possible. Also, I can then use Spark stand alone
> cluster to ingest, even if my hadoop cluster is heavily loaded. What you
> guys think?
>
> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>
>> Why do you want to reimplement something which is already there?
>>
>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>
>> Hi
>>
>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>> index and incremental only) and add data to existing Hive tables. Also, it
>> would be good to have an option to create Hive table, driven by job
>> specific configuration.
>>
>> What do you think?
>>
>> Best
>> Ayan
>>
>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> It depends on your use case using sqoop.
>>> What's it like?
>>>
>>> // maropu
>>>
>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>
>>>> Hi All
>>>>
>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>> sqoop does? Any existing project done in similar lines?
>>>>
>>>> --
>>>> Best Regards,
>>>> Ayan Guha
>>>>
>>>
>>>
>>>
>>> --
>>> ---
>>> Takeshi Yamamuro
>>>
>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
Re: Sqoop on Spark
Posted by Mich Talebzadeh <mi...@gmail.com>.
Hi,
"SQOOP just extracted for me 1,253,015,160 records in 30 minutes running in
4 threads, that is 246 GB of data."
Could you please give the source of the database and where was it (on the
same host as Hive or another host).
thanks
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 14 April 2016 at 16:31, Jörn Franke <jo...@gmail.com> wrote:
> They wanted to have alternatives. I recommended the original approach of
> simply using sqoop.
>
> On 14 Apr 2016, at 16:09, Gourav Sengupta <go...@gmail.com>
> wrote:
>
> Hi,
>
> SQOOP just extracted for me 1,253,015,160 records in 30 minutes running in
> 4 threads, that is 246 GB of data.
>
> Why is the discussion about using anything other than SQOOP still so
> wonderfully on?
>
>
> Regards,
> Gourav
>
> On Mon, Apr 11, 2016 at 6:26 PM, Jörn Franke <jo...@gmail.com> wrote:
>
>> Actually I was referring to have a an external table in Oracle, which is
>> used to export to CSV (insert into). Then you have a csv on the database
>> server which needs to be moved to HDFS.
>>
>> On 11 Apr 2016, at 17:50, Michael Segel <ms...@hotmail.com>
>> wrote:
>>
>> Depending on the Oracle release…
>>
>> You could use webHDFS to gain access to the cluster and see the CSV file
>> as an external table.
>>
>> However, you would need to have an application that will read each block
>> of the file in parallel. This works for loading in to the RDBMS itself.
>> Actually you could use sqoop in reverse to push data to the RDBMS provided
>> that the block file is splittable. This is a classic M/R problem.
>>
>> But I don’t think this is what the OP wants to do. They want to pull data
>> from the RDBMs. If you could drop the table’s underlying file and can read
>> directly from it… you can do a very simple bulk load/unload process.
>> However you need to know the file’s format.
>>
>> Not sure what IBM or Oracle has done to tie their RDBMs to Big Data.
>>
>> As I and other posters to this thread have alluded to… this would be a
>> block bulk load/unload tool.
>>
>>
>> On Apr 10, 2016, at 11:31 AM, Jörn Franke <jo...@gmail.com> wrote:
>>
>>
>> I am not 100% sure, but you could export to CSV in Oracle using external
>> tables.
>>
>> Oracle has also the Hadoop Loader, which seems to support Avro. However,
>> I think you need to buy the Big Data solution.
>>
>> On 10 Apr 2016, at 16:12, Mich Talebzadeh <mi...@gmail.com>
>> wrote:
>>
>> Yes I meant MR.
>>
>> Again one cannot beat the RDBMS export utility. I was specifically
>> referring to Oracle in above case that does not provide any specific text
>> bases export except the binary one Exp, data pump etc).
>>
>> In case of SAPO ASE, Sybase IQ, and MSSQL, one can use BCP (bulk copy)
>> that can be parallelised either through range partitioning or simple round
>> robin partitioning that can be used to get data out to file in parallel.
>> Then once get data into Hive table through import etc.
>>
>> In general if the source table is very large you can used either SAP
>> Replication Server (SRS) or Oracle Golden Gate to get data to Hive. Both
>> these replication tools provide connectors to Hive and they do a good job.
>> If one has something like Oracle in Prod then there is likely a Golden Gate
>> there. For bulk setting of Hive tables and data migration, replication
>> server is good option.
>>
>> HTH
>>
>>
>> Dr Mich Talebzadeh
>>
>>
>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>>
>> On 10 April 2016 at 14:24, Michael Segel <ms...@hotmail.com>
>> wrote:
>>
>>> Sqoop doesn’t use MapR… unless you meant to say M/R (Map Reduce)
>>>
>>> The largest problem with sqoop is that in order to gain parallelism you
>>> need to know how your underlying table is partitioned and to do multiple
>>> range queries. This may not be known, or your data may or may not be
>>> equally distributed across the ranges.
>>>
>>> If you’re bringing over the entire table, you may find dropping it and
>>> then moving it to HDFS and then doing a bulk load to be more efficient.
>>> (This is less flexible than sqoop, but also stresses the database
>>> servers less. )
>>>
>>> Again, YMMV
>>>
>>>
>>> On Apr 8, 2016, at 9:17 AM, Mich Talebzadeh <mi...@gmail.com>
>>> wrote:
>>>
>>> Well unless you have plenty of memory, you are going to have certain
>>> issues with Spark.
>>>
>>> I tried to load a billion rows table from oracle through spark using
>>> JDBC and ended up with "Caused by: java.lang.OutOfMemoryError: Java heap
>>> space" error.
>>>
>>> Sqoop uses MapR and does it in serial mode which takes time and you can
>>> also tell it to create Hive table. However, it will import data into Hive
>>> table.
>>>
>>> In any case the mechanism of data import is through JDBC, Spark uses
>>> memory and DAG, whereas Sqoop relies on MapR.
>>>
>>> There is of course another alternative.
>>>
>>> Assuming that your Oracle table has a primary Key say "ID" (it would be
>>> easier if it was a monotonically increasing number) or already partitioned.
>>>
>>>
>>> 1. You can create views based on the range of ID or for each
>>> partition. You can then SELECT COLUMNS co1, col2, coln from view and spool
>>> it to a text file on OS (locally say backup directory would be fastest).
>>> 2. bzip2 those files and scp them to a local directory in Hadoop
>>> 3. You can then use Spark/hive to load the target table from local
>>> files in parallel
>>> 4. When creating views take care of NUMBER and CHAR columns in
>>> Oracle and convert them to TO_CHAR(NUMBER_COLUMN) and varchar CAST(coln
>>> AS VARCHAR2(n)) AS coln etc
>>>
>>>
>>> HTH
>>>
>>>
>>>
>>> Dr Mich Talebzadeh
>>>
>>>
>>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>>
>>> On 8 April 2016 at 10:07, Gourav Sengupta <go...@gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> Some metrics thrown around the discussion:
>>>>
>>>> SQOOP: extract 500 million rows (in single thread) 20 mins (data size
>>>> 21 GB)
>>>> SPARK: load the data into memory (15 mins)
>>>>
>>>> SPARK: use JDBC (and similar to SQOOP difficult parallelization) to
>>>> load 500 million records - manually killed after 8 hours.
>>>>
>>>> (both the above studies were done in a system of same capacity, with 32
>>>> GB RAM and dual hexacore Xeon processors and SSD. SPARK was running
>>>> locally, and SQOOP ran on HADOOP2 and extracted data to local file system)
>>>>
>>>> In case any one needs to know what needs to be done to access both the
>>>> CSV and JDBC modules in SPARK Local Server mode, please let me know.
>>>>
>>>>
>>>> Regards,
>>>> Gourav Sengupta
>>>>
>>>> On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <ja...@hotmail.com>
>>>> wrote:
>>>>
>>>>> Good to know that.
>>>>>
>>>>> That is why Sqoop has this "direct" mode, to utilize the vendor
>>>>> specific feature.
>>>>>
>>>>> But for MPP, I still think it makes sense that vendor provide some
>>>>> kind of InputFormat, or data source in Spark, so Hadoop eco-system can
>>>>> integrate with them more natively.
>>>>>
>>>>> Yong
>>>>>
>>>>> ------------------------------
>>>>> Date: Wed, 6 Apr 2016 16:12:30 -0700
>>>>> Subject: Re: Sqoop on Spark
>>>>> From: mohajeri@gmail.com
>>>>> To: java8964@hotmail.com
>>>>> CC: mich.talebzadeh@gmail.com; jornfranke@gmail.com;
>>>>> msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com;
>>>>> user@spark.apache.org
>>>>>
>>>>>
>>>>> It is using JDBC driver, i know that's the case for Teradata:
>>>>>
>>>>> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
>>>>>
>>>>> Teradata Connector (which is used by Cloudera and Hortonworks) for
>>>>> doing Sqoop is parallelized and works with ORC and probably other formats
>>>>> as well. It is using JDBC for each connection between data-nodes and their
>>>>> AMP (compute) nodes. There is an additional layer that coordinates all of
>>>>> it.
>>>>> I know Oracle has a similar technology I've used it and had to supply
>>>>> the JDBC driver.
>>>>>
>>>>> Teradata Connector is for batch data copy, QueryGrid is for
>>>>> interactive data movement.
>>>>>
>>>>> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com>
>>>>> wrote:
>>>>>
>>>>> If they do that, they must provide a customized input format, instead
>>>>> of through JDBC.
>>>>>
>>>>> Yong
>>>>>
>>>>> ------------------------------
>>>>> Date: Wed, 6 Apr 2016 23:56:54 +0100
>>>>> Subject: Re: Sqoop on Spark
>>>>> From: mich.talebzadeh@gmail.com
>>>>> To: mohajeri@gmail.com
>>>>> CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com;
>>>>> guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
>>>>>
>>>>>
>>>>> SAP Sybase IQ does that and I believe SAP Hana as well.
>>>>>
>>>>> Dr Mich Talebzadeh
>>>>>
>>>>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>>>
>>>>>
>>>>> http://talebzadehmich.wordpress.com
>>>>>
>>>>>
>>>>> On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com>
>>>>> wrote:
>>>>>
>>>>> For some MPP relational stores (not operational) it maybe feasible to
>>>>> run Spark jobs and also have data locality. I know QueryGrid (Teradata) and
>>>>> PolyBase (microsoft) use data locality to move data between their MPP and
>>>>> Hadoop.
>>>>> I would guess (have no idea) someone like IBM already is doing that
>>>>> for Spark, maybe a bit off topic!
>>>>>
>>>>> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com>
>>>>> wrote:
>>>>>
>>>>> Well I am not sure, but using a database as a storage, such as
>>>>> relational databases or certain nosql databases (eg MongoDB) for Spark is
>>>>> generally a bad idea - no data locality, it cannot handle real big data
>>>>> volumes for compute and you may potentially overload an operational
>>>>> database.
>>>>> And if your job fails for whatever reason (eg scheduling ) then you
>>>>> have to pull everything out again. Sqoop and HDFS seems to me the more
>>>>> elegant solution together with spark. These "assumption" on parallelism
>>>>> have to be anyway made with any solution.
>>>>> Of course you can always redo things, but why - what benefit do you
>>>>> expect? A real big data platform has to support anyway many different tools
>>>>> otherwise people doing analytics will be limited.
>>>>>
>>>>> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com>
>>>>> wrote:
>>>>>
>>>>> I don’t think its necessarily a bad idea.
>>>>>
>>>>> Sqoop is an ugly tool and it requires you to make some assumptions as
>>>>> a way to gain parallelism. (Not that most of the assumptions are not valid
>>>>> for most of the use cases…)
>>>>>
>>>>> Depending on what you want to do… your data may not be persisted on
>>>>> HDFS. There are use cases where your cluster is used for compute and not
>>>>> storage.
>>>>>
>>>>> I’d say that spending time re-inventing the wheel can be a good thing.
>>>>> It would be a good idea for many to rethink their ingestion process so
>>>>> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
>>>>> that term from Dean Wampler. ;-)
>>>>>
>>>>> Just saying. ;-)
>>>>>
>>>>> -Mike
>>>>>
>>>>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>>
>>>>> I do not think you can be more resource efficient. In the end you have
>>>>> to store the data anyway on HDFS . You have a lot of development effort for
>>>>> doing something like sqoop. Especially with error handling.
>>>>> You may create a ticket with the Sqoop guys to support Spark as an
>>>>> execution engine and maybe it is less effort to plug it in there.
>>>>> Maybe if your cluster is loaded then you may want to add more machines
>>>>> or improve the existing programs.
>>>>>
>>>>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>>>>
>>>>> One of the reason in my mind is to avoid Map-Reduce application
>>>>> completely during ingestion, if possible. Also, I can then use Spark stand
>>>>> alone cluster to ingest, even if my hadoop cluster is heavily loaded. What
>>>>> you guys think?
>>>>>
>>>>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com>
>>>>> wrote:
>>>>>
>>>>> Why do you want to reimplement something which is already there?
>>>>>
>>>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>>>
>>>>> Hi
>>>>>
>>>>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>>>>> index and incremental only) and add data to existing Hive tables. Also, it
>>>>> would be good to have an option to create Hive table, driven by job
>>>>> specific configuration.
>>>>>
>>>>> What do you think?
>>>>>
>>>>> Best
>>>>> Ayan
>>>>>
>>>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <
>>>>> linguin.m.s@gmail.com> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> It depends on your use case using sqoop.
>>>>> What's it like?
>>>>>
>>>>> // maropu
>>>>>
>>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>>
>>>>> Hi All
>>>>>
>>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>>> sqoop does? Any existing project done in similar lines?
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> ---
>>>>> Takeshi Yamamuro
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>>
>
Re: Sqoop on Spark
Posted by Jörn Franke <jo...@gmail.com>.
They wanted to have alternatives. I recommended the original approach of simply using sqoop.
> On 14 Apr 2016, at 16:09, Gourav Sengupta <go...@gmail.com> wrote:
>
> Hi,
>
> SQOOP just extracted for me 1,253,015,160 records in 30 minutes running in 4 threads, that is 246 GB of data.
>
> Why is the discussion about using anything other than SQOOP still so wonderfully on?
>
>
> Regards,
> Gourav
>
>> On Mon, Apr 11, 2016 at 6:26 PM, Jörn Franke <jo...@gmail.com> wrote:
>> Actually I was referring to have a an external table in Oracle, which is used to export to CSV (insert into). Then you have a csv on the database server which needs to be moved to HDFS.
>>
>>> On 11 Apr 2016, at 17:50, Michael Segel <ms...@hotmail.com> wrote:
>>>
>>> Depending on the Oracle release…
>>>
>>> You could use webHDFS to gain access to the cluster and see the CSV file as an external table.
>>>
>>> However, you would need to have an application that will read each block of the file in parallel. This works for loading in to the RDBMS itself. Actually you could use sqoop in reverse to push data to the RDBMS provided that the block file is splittable. This is a classic M/R problem.
>>>
>>> But I don’t think this is what the OP wants to do. They want to pull data from the RDBMs. If you could drop the table’s underlying file and can read directly from it… you can do a very simple bulk load/unload process. However you need to know the file’s format.
>>>
>>> Not sure what IBM or Oracle has done to tie their RDBMs to Big Data.
>>>
>>> As I and other posters to this thread have alluded to… this would be a block bulk load/unload tool.
>>>
>>>
>>>> On Apr 10, 2016, at 11:31 AM, Jörn Franke <jo...@gmail.com> wrote:
>>>>
>>>>
>>>> I am not 100% sure, but you could export to CSV in Oracle using external tables.
>>>>
>>>> Oracle has also the Hadoop Loader, which seems to support Avro. However, I think you need to buy the Big Data solution.
>>>>
>>>>> On 10 Apr 2016, at 16:12, Mich Talebzadeh <mi...@gmail.com> wrote:
>>>>>
>>>>> Yes I meant MR.
>>>>>
>>>>> Again one cannot beat the RDBMS export utility. I was specifically referring to Oracle in above case that does not provide any specific text bases export except the binary one Exp, data pump etc).
>>>>>
>>>>> In case of SAPO ASE, Sybase IQ, and MSSQL, one can use BCP (bulk copy) that can be parallelised either through range partitioning or simple round robin partitioning that can be used to get data out to file in parallel. Then once get data into Hive table through import etc.
>>>>>
>>>>> In general if the source table is very large you can used either SAP Replication Server (SRS) or Oracle Golden Gate to get data to Hive. Both these replication tools provide connectors to Hive and they do a good job. If one has something like Oracle in Prod then there is likely a Golden Gate there. For bulk setting of Hive tables and data migration, replication server is good option.
>>>>>
>>>>> HTH
>>>>>
>>>>>
>>>>> Dr Mich Talebzadeh
>>>>>
>>>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>>
>>>>> http://talebzadehmich.wordpress.com
>>>>>
>>>>>
>>>>>> On 10 April 2016 at 14:24, Michael Segel <ms...@hotmail.com> wrote:
>>>>>> Sqoop doesn’t use MapR… unless you meant to say M/R (Map Reduce)
>>>>>>
>>>>>> The largest problem with sqoop is that in order to gain parallelism you need to know how your underlying table is partitioned and to do multiple range queries. This may not be known, or your data may or may not be equally distributed across the ranges.
>>>>>>
>>>>>> If you’re bringing over the entire table, you may find dropping it and then moving it to HDFS and then doing a bulk load to be more efficient.
>>>>>> (This is less flexible than sqoop, but also stresses the database servers less. )
>>>>>>
>>>>>> Again, YMMV
>>>>>>
>>>>>>
>>>>>>> On Apr 8, 2016, at 9:17 AM, Mich Talebzadeh <mi...@gmail.com> wrote:
>>>>>>>
>>>>>>> Well unless you have plenty of memory, you are going to have certain issues with Spark.
>>>>>>>
>>>>>>> I tried to load a billion rows table from oracle through spark using JDBC and ended up with "Caused by: java.lang.OutOfMemoryError: Java heap space" error.
>>>>>>>
>>>>>>> Sqoop uses MapR and does it in serial mode which takes time and you can also tell it to create Hive table. However, it will import data into Hive table.
>>>>>>>
>>>>>>> In any case the mechanism of data import is through JDBC, Spark uses memory and DAG, whereas Sqoop relies on MapR.
>>>>>>>
>>>>>>> There is of course another alternative.
>>>>>>>
>>>>>>> Assuming that your Oracle table has a primary Key say "ID" (it would be easier if it was a monotonically increasing number) or already partitioned.
>>>>>>>
>>>>>>> You can create views based on the range of ID or for each partition. You can then SELECT COLUMNS co1, col2, coln from view and spool it to a text file on OS (locally say backup directory would be fastest).
>>>>>>> bzip2 those files and scp them to a local directory in Hadoop
>>>>>>> You can then use Spark/hive to load the target table from local files in parallel
>>>>>>> When creating views take care of NUMBER and CHAR columns in Oracle and convert them to TO_CHAR(NUMBER_COLUMN) and varchar CAST(coln AS VARCHAR2(n)) AS coln etc
>>>>>>>
>>>>>>> HTH
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Dr Mich Talebzadeh
>>>>>>>
>>>>>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>>>>
>>>>>>> http://talebzadehmich.wordpress.com
>>>>>>>
>>>>>>>
>>>>>>>> On 8 April 2016 at 10:07, Gourav Sengupta <go...@gmail.com> wrote:
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> Some metrics thrown around the discussion:
>>>>>>>>
>>>>>>>> SQOOP: extract 500 million rows (in single thread) 20 mins (data size 21 GB)
>>>>>>>> SPARK: load the data into memory (15 mins)
>>>>>>>>
>>>>>>>> SPARK: use JDBC (and similar to SQOOP difficult parallelization) to load 500 million records - manually killed after 8 hours.
>>>>>>>>
>>>>>>>> (both the above studies were done in a system of same capacity, with 32 GB RAM and dual hexacore Xeon processors and SSD. SPARK was running locally, and SQOOP ran on HADOOP2 and extracted data to local file system)
>>>>>>>>
>>>>>>>> In case any one needs to know what needs to be done to access both the CSV and JDBC modules in SPARK Local Server mode, please let me know.
>>>>>>>>
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>> Gourav Sengupta
>>>>>>>>
>>>>>>>>> On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <ja...@hotmail.com> wrote:
>>>>>>>>> Good to know that.
>>>>>>>>>
>>>>>>>>> That is why Sqoop has this "direct" mode, to utilize the vendor specific feature.
>>>>>>>>>
>>>>>>>>> But for MPP, I still think it makes sense that vendor provide some kind of InputFormat, or data source in Spark, so Hadoop eco-system can integrate with them more natively.
>>>>>>>>>
>>>>>>>>> Yong
>>>>>>>>>
>>>>>>>>> Date: Wed, 6 Apr 2016 16:12:30 -0700
>>>>>>>>> Subject: Re: Sqoop on Spark
>>>>>>>>> From: mohajeri@gmail.com
>>>>>>>>> To: java8964@hotmail.com
>>>>>>>>> CC: mich.talebzadeh@gmail.com; jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> It is using JDBC driver, i know that's the case for Teradata:
>>>>>>>>> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
>>>>>>>>>
>>>>>>>>> Teradata Connector (which is used by Cloudera and Hortonworks) for doing Sqoop is parallelized and works with ORC and probably other formats as well. It is using JDBC for each connection between data-nodes and their AMP (compute) nodes. There is an additional layer that coordinates all of it.
>>>>>>>>> I know Oracle has a similar technology I've used it and had to supply the JDBC driver.
>>>>>>>>>
>>>>>>>>> Teradata Connector is for batch data copy, QueryGrid is for interactive data movement.
>>>>>>>>>
>>>>>>>>> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com> wrote:
>>>>>>>>> If they do that, they must provide a customized input format, instead of through JDBC.
>>>>>>>>>
>>>>>>>>> Yong
>>>>>>>>>
>>>>>>>>> Date: Wed, 6 Apr 2016 23:56:54 +0100
>>>>>>>>> Subject: Re: Sqoop on Spark
>>>>>>>>> From: mich.talebzadeh@gmail.com
>>>>>>>>> To: mohajeri@gmail.com
>>>>>>>>> CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> SAP Sybase IQ does that and I believe SAP Hana as well.
>>>>>>>>>
>>>>>>>>> Dr Mich Talebzadeh
>>>>>>>>>
>>>>>>>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>>>>>>
>>>>>>>>> http://talebzadehmich.wordpress.com
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
>>>>>>>>> For some MPP relational stores (not operational) it maybe feasible to run Spark jobs and also have data locality. I know QueryGrid (Teradata) and PolyBase (microsoft) use data locality to move data between their MPP and Hadoop.
>>>>>>>>> I would guess (have no idea) someone like IBM already is doing that for Spark, maybe a bit off topic!
>>>>>>>>>
>>>>>>>>> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>>>>>> Well I am not sure, but using a database as a storage, such as relational databases or certain nosql databases (eg MongoDB) for Spark is generally a bad idea - no data locality, it cannot handle real big data volumes for compute and you may potentially overload an operational database.
>>>>>>>>> And if your job fails for whatever reason (eg scheduling ) then you have to pull everything out again. Sqoop and HDFS seems to me the more elegant solution together with spark. These "assumption" on parallelism have to be anyway made with any solution.
>>>>>>>>> Of course you can always redo things, but why - what benefit do you expect? A real big data platform has to support anyway many different tools otherwise people doing analytics will be limited.
>>>>>>>>>
>>>>>>>>> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
>>>>>>>>>
>>>>>>>>> I don’t think its necessarily a bad idea.
>>>>>>>>>
>>>>>>>>> Sqoop is an ugly tool and it requires you to make some assumptions as a way to gain parallelism. (Not that most of the assumptions are not valid for most of the use cases…)
>>>>>>>>>
>>>>>>>>> Depending on what you want to do… your data may not be persisted on HDFS. There are use cases where your cluster is used for compute and not storage.
>>>>>>>>>
>>>>>>>>> I’d say that spending time re-inventing the wheel can be a good thing.
>>>>>>>>> It would be a good idea for many to rethink their ingestion process so that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing that term from Dean Wampler. ;-)
>>>>>>>>>
>>>>>>>>> Just saying. ;-)
>>>>>>>>>
>>>>>>>>> -Mike
>>>>>>>>>
>>>>>>>>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>>>>>>
>>>>>>>>> I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling.
>>>>>>>>> You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.
>>>>>>>>> Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
>>>>>>>>>
>>>>>>>>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>>>>>>>>
>>>>>>>>> One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
>>>>>>>>>
>>>>>>>>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>>>>>> Why do you want to reimplement something which is already there?
>>>>>>>>>
>>>>>>>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>>>>>>>
>>>>>>>>> Hi
>>>>>>>>>
>>>>>>>>> Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
>>>>>>>>>
>>>>>>>>> What do you think?
>>>>>>>>>
>>>>>>>>> Best
>>>>>>>>> Ayan
>>>>>>>>>
>>>>>>>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com> wrote:
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> It depends on your use case using sqoop.
>>>>>>>>> What's it like?
>>>>>>>>>
>>>>>>>>> // maropu
>>>>>>>>>
>>>>>>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>>>>>> Hi All
>>>>>>>>>
>>>>>>>>> Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Best Regards,
>>>>>>>>> Ayan Guha
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> ---
>>>>>>>>> Takeshi Yamamuro
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Best Regards,
>>>>>>>>> Ayan Guha
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Best Regards,
>>>>>>>>> Ayan Guha
>
Re: Sqoop on Spark
Posted by Gourav Sengupta <go...@gmail.com>.
Hi,
SQOOP just extracted for me 1,253,015,160 records in 30 minutes running in
4 threads, that is 246 GB of data.
Why is the discussion about using anything other than SQOOP still so
wonderfully on?
Regards,
Gourav
On Mon, Apr 11, 2016 at 6:26 PM, Jörn Franke <jo...@gmail.com> wrote:
> Actually I was referring to have a an external table in Oracle, which is
> used to export to CSV (insert into). Then you have a csv on the database
> server which needs to be moved to HDFS.
>
> On 11 Apr 2016, at 17:50, Michael Segel <ms...@hotmail.com> wrote:
>
> Depending on the Oracle release…
>
> You could use webHDFS to gain access to the cluster and see the CSV file
> as an external table.
>
> However, you would need to have an application that will read each block
> of the file in parallel. This works for loading in to the RDBMS itself.
> Actually you could use sqoop in reverse to push data to the RDBMS provided
> that the block file is splittable. This is a classic M/R problem.
>
> But I don’t think this is what the OP wants to do. They want to pull data
> from the RDBMs. If you could drop the table’s underlying file and can read
> directly from it… you can do a very simple bulk load/unload process.
> However you need to know the file’s format.
>
> Not sure what IBM or Oracle has done to tie their RDBMs to Big Data.
>
> As I and other posters to this thread have alluded to… this would be a
> block bulk load/unload tool.
>
>
> On Apr 10, 2016, at 11:31 AM, Jörn Franke <jo...@gmail.com> wrote:
>
>
> I am not 100% sure, but you could export to CSV in Oracle using external
> tables.
>
> Oracle has also the Hadoop Loader, which seems to support Avro. However, I
> think you need to buy the Big Data solution.
>
> On 10 Apr 2016, at 16:12, Mich Talebzadeh <mi...@gmail.com>
> wrote:
>
> Yes I meant MR.
>
> Again one cannot beat the RDBMS export utility. I was specifically
> referring to Oracle in above case that does not provide any specific text
> bases export except the binary one Exp, data pump etc).
>
> In case of SAPO ASE, Sybase IQ, and MSSQL, one can use BCP (bulk copy)
> that can be parallelised either through range partitioning or simple round
> robin partitioning that can be used to get data out to file in parallel.
> Then once get data into Hive table through import etc.
>
> In general if the source table is very large you can used either SAP
> Replication Server (SRS) or Oracle Golden Gate to get data to Hive. Both
> these replication tools provide connectors to Hive and they do a good job.
> If one has something like Oracle in Prod then there is likely a Golden Gate
> there. For bulk setting of Hive tables and data migration, replication
> server is good option.
>
> HTH
>
>
> Dr Mich Talebzadeh
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> On 10 April 2016 at 14:24, Michael Segel <ms...@hotmail.com>
> wrote:
>
>> Sqoop doesn’t use MapR… unless you meant to say M/R (Map Reduce)
>>
>> The largest problem with sqoop is that in order to gain parallelism you
>> need to know how your underlying table is partitioned and to do multiple
>> range queries. This may not be known, or your data may or may not be
>> equally distributed across the ranges.
>>
>> If you’re bringing over the entire table, you may find dropping it and
>> then moving it to HDFS and then doing a bulk load to be more efficient.
>> (This is less flexible than sqoop, but also stresses the database servers
>> less. )
>>
>> Again, YMMV
>>
>>
>> On Apr 8, 2016, at 9:17 AM, Mich Talebzadeh <mi...@gmail.com>
>> wrote:
>>
>> Well unless you have plenty of memory, you are going to have certain
>> issues with Spark.
>>
>> I tried to load a billion rows table from oracle through spark using JDBC
>> and ended up with "Caused by: java.lang.OutOfMemoryError: Java heap space"
>> error.
>>
>> Sqoop uses MapR and does it in serial mode which takes time and you can
>> also tell it to create Hive table. However, it will import data into Hive
>> table.
>>
>> In any case the mechanism of data import is through JDBC, Spark uses
>> memory and DAG, whereas Sqoop relies on MapR.
>>
>> There is of course another alternative.
>>
>> Assuming that your Oracle table has a primary Key say "ID" (it would be
>> easier if it was a monotonically increasing number) or already partitioned.
>>
>>
>> 1. You can create views based on the range of ID or for each
>> partition. You can then SELECT COLUMNS co1, col2, coln from view and spool
>> it to a text file on OS (locally say backup directory would be fastest).
>> 2. bzip2 those files and scp them to a local directory in Hadoop
>> 3. You can then use Spark/hive to load the target table from local
>> files in parallel
>> 4. When creating views take care of NUMBER and CHAR columns in Oracle
>> and convert them to TO_CHAR(NUMBER_COLUMN) and varchar CAST(coln AS
>> VARCHAR2(n)) AS coln etc
>>
>>
>> HTH
>>
>>
>>
>> Dr Mich Talebzadeh
>>
>>
>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>>
>> On 8 April 2016 at 10:07, Gourav Sengupta <go...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> Some metrics thrown around the discussion:
>>>
>>> SQOOP: extract 500 million rows (in single thread) 20 mins (data size 21
>>> GB)
>>> SPARK: load the data into memory (15 mins)
>>>
>>> SPARK: use JDBC (and similar to SQOOP difficult parallelization) to load
>>> 500 million records - manually killed after 8 hours.
>>>
>>> (both the above studies were done in a system of same capacity, with 32
>>> GB RAM and dual hexacore Xeon processors and SSD. SPARK was running
>>> locally, and SQOOP ran on HADOOP2 and extracted data to local file system)
>>>
>>> In case any one needs to know what needs to be done to access both the
>>> CSV and JDBC modules in SPARK Local Server mode, please let me know.
>>>
>>>
>>> Regards,
>>> Gourav Sengupta
>>>
>>> On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <ja...@hotmail.com>
>>> wrote:
>>>
>>>> Good to know that.
>>>>
>>>> That is why Sqoop has this "direct" mode, to utilize the vendor
>>>> specific feature.
>>>>
>>>> But for MPP, I still think it makes sense that vendor provide some kind
>>>> of InputFormat, or data source in Spark, so Hadoop eco-system can integrate
>>>> with them more natively.
>>>>
>>>> Yong
>>>>
>>>> ------------------------------
>>>> Date: Wed, 6 Apr 2016 16:12:30 -0700
>>>> Subject: Re: Sqoop on Spark
>>>> From: mohajeri@gmail.com
>>>> To: java8964@hotmail.com
>>>> CC: mich.talebzadeh@gmail.com; jornfranke@gmail.com;
>>>> msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com;
>>>> user@spark.apache.org
>>>>
>>>>
>>>> It is using JDBC driver, i know that's the case for Teradata:
>>>>
>>>> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
>>>>
>>>> Teradata Connector (which is used by Cloudera and Hortonworks) for
>>>> doing Sqoop is parallelized and works with ORC and probably other formats
>>>> as well. It is using JDBC for each connection between data-nodes and their
>>>> AMP (compute) nodes. There is an additional layer that coordinates all of
>>>> it.
>>>> I know Oracle has a similar technology I've used it and had to supply
>>>> the JDBC driver.
>>>>
>>>> Teradata Connector is for batch data copy, QueryGrid is for interactive
>>>> data movement.
>>>>
>>>> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com>
>>>> wrote:
>>>>
>>>> If they do that, they must provide a customized input format, instead
>>>> of through JDBC.
>>>>
>>>> Yong
>>>>
>>>> ------------------------------
>>>> Date: Wed, 6 Apr 2016 23:56:54 +0100
>>>> Subject: Re: Sqoop on Spark
>>>> From: mich.talebzadeh@gmail.com
>>>> To: mohajeri@gmail.com
>>>> CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com;
>>>> guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
>>>>
>>>>
>>>> SAP Sybase IQ does that and I believe SAP Hana as well.
>>>>
>>>> Dr Mich Talebzadeh
>>>>
>>>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>>
>>>>
>>>> http://talebzadehmich.wordpress.com
>>>>
>>>>
>>>> On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
>>>>
>>>> For some MPP relational stores (not operational) it maybe feasible to
>>>> run Spark jobs and also have data locality. I know QueryGrid (Teradata) and
>>>> PolyBase (microsoft) use data locality to move data between their MPP and
>>>> Hadoop.
>>>> I would guess (have no idea) someone like IBM already is doing that for
>>>> Spark, maybe a bit off topic!
>>>>
>>>> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com>
>>>> wrote:
>>>>
>>>> Well I am not sure, but using a database as a storage, such as
>>>> relational databases or certain nosql databases (eg MongoDB) for Spark is
>>>> generally a bad idea - no data locality, it cannot handle real big data
>>>> volumes for compute and you may potentially overload an operational
>>>> database.
>>>> And if your job fails for whatever reason (eg scheduling ) then you
>>>> have to pull everything out again. Sqoop and HDFS seems to me the more
>>>> elegant solution together with spark. These "assumption" on parallelism
>>>> have to be anyway made with any solution.
>>>> Of course you can always redo things, but why - what benefit do you
>>>> expect? A real big data platform has to support anyway many different tools
>>>> otherwise people doing analytics will be limited.
>>>>
>>>> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com>
>>>> wrote:
>>>>
>>>> I don’t think its necessarily a bad idea.
>>>>
>>>> Sqoop is an ugly tool and it requires you to make some assumptions as a
>>>> way to gain parallelism. (Not that most of the assumptions are not valid
>>>> for most of the use cases…)
>>>>
>>>> Depending on what you want to do… your data may not be persisted on
>>>> HDFS. There are use cases where your cluster is used for compute and not
>>>> storage.
>>>>
>>>> I’d say that spending time re-inventing the wheel can be a good thing.
>>>> It would be a good idea for many to rethink their ingestion process so
>>>> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
>>>> that term from Dean Wampler. ;-)
>>>>
>>>> Just saying. ;-)
>>>>
>>>> -Mike
>>>>
>>>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>
>>>> I do not think you can be more resource efficient. In the end you have
>>>> to store the data anyway on HDFS . You have a lot of development effort for
>>>> doing something like sqoop. Especially with error handling.
>>>> You may create a ticket with the Sqoop guys to support Spark as an
>>>> execution engine and maybe it is less effort to plug it in there.
>>>> Maybe if your cluster is loaded then you may want to add more machines
>>>> or improve the existing programs.
>>>>
>>>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>>>
>>>> One of the reason in my mind is to avoid Map-Reduce application
>>>> completely during ingestion, if possible. Also, I can then use Spark stand
>>>> alone cluster to ingest, even if my hadoop cluster is heavily loaded. What
>>>> you guys think?
>>>>
>>>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com>
>>>> wrote:
>>>>
>>>> Why do you want to reimplement something which is already there?
>>>>
>>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>>
>>>> Hi
>>>>
>>>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>>>> index and incremental only) and add data to existing Hive tables. Also, it
>>>> would be good to have an option to create Hive table, driven by job
>>>> specific configuration.
>>>>
>>>> What do you think?
>>>>
>>>> Best
>>>> Ayan
>>>>
>>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <linguin.m.s@gmail.com
>>>> > wrote:
>>>>
>>>> Hi,
>>>>
>>>> It depends on your use case using sqoop.
>>>> What's it like?
>>>>
>>>> // maropu
>>>>
>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>
>>>> Hi All
>>>>
>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>> sqoop does? Any existing project done in similar lines?
>>>>
>>>> --
>>>> Best Regards,
>>>> Ayan Guha
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> ---
>>>> Takeshi Yamamuro
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Best Regards,
>>>> Ayan Guha
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Best Regards,
>>>> Ayan Guha
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>
>
Re: Sqoop on Spark
Posted by Jörn Franke <jo...@gmail.com>.
Actually I was referring to have a an external table in Oracle, which is used to export to CSV (insert into). Then you have a csv on the database server which needs to be moved to HDFS.
> On 11 Apr 2016, at 17:50, Michael Segel <ms...@hotmail.com> wrote:
>
> Depending on the Oracle release…
>
> You could use webHDFS to gain access to the cluster and see the CSV file as an external table.
>
> However, you would need to have an application that will read each block of the file in parallel. This works for loading in to the RDBMS itself. Actually you could use sqoop in reverse to push data to the RDBMS provided that the block file is splittable. This is a classic M/R problem.
>
> But I don’t think this is what the OP wants to do. They want to pull data from the RDBMs. If you could drop the table’s underlying file and can read directly from it… you can do a very simple bulk load/unload process. However you need to know the file’s format.
>
> Not sure what IBM or Oracle has done to tie their RDBMs to Big Data.
>
> As I and other posters to this thread have alluded to… this would be a block bulk load/unload tool.
>
>
>> On Apr 10, 2016, at 11:31 AM, Jörn Franke <jo...@gmail.com> wrote:
>>
>>
>> I am not 100% sure, but you could export to CSV in Oracle using external tables.
>>
>> Oracle has also the Hadoop Loader, which seems to support Avro. However, I think you need to buy the Big Data solution.
>>
>>> On 10 Apr 2016, at 16:12, Mich Talebzadeh <mi...@gmail.com> wrote:
>>>
>>> Yes I meant MR.
>>>
>>> Again one cannot beat the RDBMS export utility. I was specifically referring to Oracle in above case that does not provide any specific text bases export except the binary one Exp, data pump etc).
>>>
>>> In case of SAPO ASE, Sybase IQ, and MSSQL, one can use BCP (bulk copy) that can be parallelised either through range partitioning or simple round robin partitioning that can be used to get data out to file in parallel. Then once get data into Hive table through import etc.
>>>
>>> In general if the source table is very large you can used either SAP Replication Server (SRS) or Oracle Golden Gate to get data to Hive. Both these replication tools provide connectors to Hive and they do a good job. If one has something like Oracle in Prod then there is likely a Golden Gate there. For bulk setting of Hive tables and data migration, replication server is good option.
>>>
>>> HTH
>>>
>>>
>>> Dr Mich Talebzadeh
>>>
>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>>> On 10 April 2016 at 14:24, Michael Segel <ms...@hotmail.com> wrote:
>>>> Sqoop doesn’t use MapR… unless you meant to say M/R (Map Reduce)
>>>>
>>>> The largest problem with sqoop is that in order to gain parallelism you need to know how your underlying table is partitioned and to do multiple range queries. This may not be known, or your data may or may not be equally distributed across the ranges.
>>>>
>>>> If you’re bringing over the entire table, you may find dropping it and then moving it to HDFS and then doing a bulk load to be more efficient.
>>>> (This is less flexible than sqoop, but also stresses the database servers less. )
>>>>
>>>> Again, YMMV
>>>>
>>>>
>>>>> On Apr 8, 2016, at 9:17 AM, Mich Talebzadeh <mi...@gmail.com> wrote:
>>>>>
>>>>> Well unless you have plenty of memory, you are going to have certain issues with Spark.
>>>>>
>>>>> I tried to load a billion rows table from oracle through spark using JDBC and ended up with "Caused by: java.lang.OutOfMemoryError: Java heap space" error.
>>>>>
>>>>> Sqoop uses MapR and does it in serial mode which takes time and you can also tell it to create Hive table. However, it will import data into Hive table.
>>>>>
>>>>> In any case the mechanism of data import is through JDBC, Spark uses memory and DAG, whereas Sqoop relies on MapR.
>>>>>
>>>>> There is of course another alternative.
>>>>>
>>>>> Assuming that your Oracle table has a primary Key say "ID" (it would be easier if it was a monotonically increasing number) or already partitioned.
>>>>>
>>>>> You can create views based on the range of ID or for each partition. You can then SELECT COLUMNS co1, col2, coln from view and spool it to a text file on OS (locally say backup directory would be fastest).
>>>>> bzip2 those files and scp them to a local directory in Hadoop
>>>>> You can then use Spark/hive to load the target table from local files in parallel
>>>>> When creating views take care of NUMBER and CHAR columns in Oracle and convert them to TO_CHAR(NUMBER_COLUMN) and varchar CAST(coln AS VARCHAR2(n)) AS coln etc
>>>>>
>>>>> HTH
>>>>>
>>>>>
>>>>>
>>>>> Dr Mich Talebzadeh
>>>>>
>>>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>>
>>>>> http://talebzadehmich.wordpress.com
>>>>>
>>>>>
>>>>>> On 8 April 2016 at 10:07, Gourav Sengupta <go...@gmail.com> wrote:
>>>>>> Hi,
>>>>>>
>>>>>> Some metrics thrown around the discussion:
>>>>>>
>>>>>> SQOOP: extract 500 million rows (in single thread) 20 mins (data size 21 GB)
>>>>>> SPARK: load the data into memory (15 mins)
>>>>>>
>>>>>> SPARK: use JDBC (and similar to SQOOP difficult parallelization) to load 500 million records - manually killed after 8 hours.
>>>>>>
>>>>>> (both the above studies were done in a system of same capacity, with 32 GB RAM and dual hexacore Xeon processors and SSD. SPARK was running locally, and SQOOP ran on HADOOP2 and extracted data to local file system)
>>>>>>
>>>>>> In case any one needs to know what needs to be done to access both the CSV and JDBC modules in SPARK Local Server mode, please let me know.
>>>>>>
>>>>>>
>>>>>> Regards,
>>>>>> Gourav Sengupta
>>>>>>
>>>>>>> On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <ja...@hotmail.com> wrote:
>>>>>>> Good to know that.
>>>>>>>
>>>>>>> That is why Sqoop has this "direct" mode, to utilize the vendor specific feature.
>>>>>>>
>>>>>>> But for MPP, I still think it makes sense that vendor provide some kind of InputFormat, or data source in Spark, so Hadoop eco-system can integrate with them more natively.
>>>>>>>
>>>>>>> Yong
>>>>>>>
>>>>>>> Date: Wed, 6 Apr 2016 16:12:30 -0700
>>>>>>> Subject: Re: Sqoop on Spark
>>>>>>> From: mohajeri@gmail.com
>>>>>>> To: java8964@hotmail.com
>>>>>>> CC: mich.talebzadeh@gmail.com; jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
>>>>>>>
>>>>>>>
>>>>>>> It is using JDBC driver, i know that's the case for Teradata:
>>>>>>> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
>>>>>>>
>>>>>>> Teradata Connector (which is used by Cloudera and Hortonworks) for doing Sqoop is parallelized and works with ORC and probably other formats as well. It is using JDBC for each connection between data-nodes and their AMP (compute) nodes. There is an additional layer that coordinates all of it.
>>>>>>> I know Oracle has a similar technology I've used it and had to supply the JDBC driver.
>>>>>>>
>>>>>>> Teradata Connector is for batch data copy, QueryGrid is for interactive data movement.
>>>>>>>
>>>>>>> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com> wrote:
>>>>>>> If they do that, they must provide a customized input format, instead of through JDBC.
>>>>>>>
>>>>>>> Yong
>>>>>>>
>>>>>>> Date: Wed, 6 Apr 2016 23:56:54 +0100
>>>>>>> Subject: Re: Sqoop on Spark
>>>>>>> From: mich.talebzadeh@gmail.com
>>>>>>> To: mohajeri@gmail.com
>>>>>>> CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
>>>>>>>
>>>>>>>
>>>>>>> SAP Sybase IQ does that and I believe SAP Hana as well.
>>>>>>>
>>>>>>> Dr Mich Talebzadeh
>>>>>>>
>>>>>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>>>>
>>>>>>> http://talebzadehmich.wordpress.com
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
>>>>>>> For some MPP relational stores (not operational) it maybe feasible to run Spark jobs and also have data locality. I know QueryGrid (Teradata) and PolyBase (microsoft) use data locality to move data between their MPP and Hadoop.
>>>>>>> I would guess (have no idea) someone like IBM already is doing that for Spark, maybe a bit off topic!
>>>>>>>
>>>>>>> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>>>> Well I am not sure, but using a database as a storage, such as relational databases or certain nosql databases (eg MongoDB) for Spark is generally a bad idea - no data locality, it cannot handle real big data volumes for compute and you may potentially overload an operational database.
>>>>>>> And if your job fails for whatever reason (eg scheduling ) then you have to pull everything out again. Sqoop and HDFS seems to me the more elegant solution together with spark. These "assumption" on parallelism have to be anyway made with any solution.
>>>>>>> Of course you can always redo things, but why - what benefit do you expect? A real big data platform has to support anyway many different tools otherwise people doing analytics will be limited.
>>>>>>>
>>>>>>> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
>>>>>>>
>>>>>>> I don’t think its necessarily a bad idea.
>>>>>>>
>>>>>>> Sqoop is an ugly tool and it requires you to make some assumptions as a way to gain parallelism. (Not that most of the assumptions are not valid for most of the use cases…)
>>>>>>>
>>>>>>> Depending on what you want to do… your data may not be persisted on HDFS. There are use cases where your cluster is used for compute and not storage.
>>>>>>>
>>>>>>> I’d say that spending time re-inventing the wheel can be a good thing.
>>>>>>> It would be a good idea for many to rethink their ingestion process so that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing that term from Dean Wampler. ;-)
>>>>>>>
>>>>>>> Just saying. ;-)
>>>>>>>
>>>>>>> -Mike
>>>>>>>
>>>>>>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>>>>
>>>>>>> I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling.
>>>>>>> You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.
>>>>>>> Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
>>>>>>>
>>>>>>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>>>>>>
>>>>>>> One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
>>>>>>>
>>>>>>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>>>> Why do you want to reimplement something which is already there?
>>>>>>>
>>>>>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>>>>>
>>>>>>> Hi
>>>>>>>
>>>>>>> Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
>>>>>>>
>>>>>>> What do you think?
>>>>>>>
>>>>>>> Best
>>>>>>> Ayan
>>>>>>>
>>>>>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com> wrote:
>>>>>>> Hi,
>>>>>>>
>>>>>>> It depends on your use case using sqoop.
>>>>>>> What's it like?
>>>>>>>
>>>>>>> // maropu
>>>>>>>
>>>>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>>>> Hi All
>>>>>>>
>>>>>>> Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
>>>>>>>
>>>>>>> --
>>>>>>> Best Regards,
>>>>>>> Ayan Guha
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> ---
>>>>>>> Takeshi Yamamuro
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Best Regards,
>>>>>>> Ayan Guha
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Best Regards,
>>>>>>> Ayan Guha
>
Re: Sqoop on Spark
Posted by Michael Segel <ms...@hotmail.com>.
Depending on the Oracle release…
You could use webHDFS to gain access to the cluster and see the CSV file as an external table.
However, you would need to have an application that will read each block of the file in parallel. This works for loading in to the RDBMS itself. Actually you could use sqoop in reverse to push data to the RDBMS provided that the block file is splittable. This is a classic M/R problem.
But I don’t think this is what the OP wants to do. They want to pull data from the RDBMs. If you could drop the table’s underlying file and can read directly from it… you can do a very simple bulk load/unload process. However you need to know the file’s format.
Not sure what IBM or Oracle has done to tie their RDBMs to Big Data.
As I and other posters to this thread have alluded to… this would be a block bulk load/unload tool.
> On Apr 10, 2016, at 11:31 AM, Jörn Franke <jo...@gmail.com> wrote:
>
>
> I am not 100% sure, but you could export to CSV in Oracle using external tables.
>
> Oracle has also the Hadoop Loader, which seems to support Avro. However, I think you need to buy the Big Data solution.
>
> On 10 Apr 2016, at 16:12, Mich Talebzadeh <mich.talebzadeh@gmail.com <ma...@gmail.com>> wrote:
>
>> Yes I meant MR.
>>
>> Again one cannot beat the RDBMS export utility. I was specifically referring to Oracle in above case that does not provide any specific text bases export except the binary one Exp, data pump etc).
>>
>> In case of SAPO ASE, Sybase IQ, and MSSQL, one can use BCP (bulk copy) that can be parallelised either through range partitioning or simple round robin partitioning that can be used to get data out to file in parallel. Then once get data into Hive table through import etc.
>>
>> In general if the source table is very large you can used either SAP Replication Server (SRS) or Oracle Golden Gate to get data to Hive. Both these replication tools provide connectors to Hive and they do a good job. If one has something like Oracle in Prod then there is likely a Golden Gate there. For bulk setting of Hive tables and data migration, replication server is good option.
>>
>> HTH
>>
>>
>> Dr Mich Talebzadeh
>>
>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>
>>
>> http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/>
>>
>>
>> On 10 April 2016 at 14:24, Michael Segel <msegel_hadoop@hotmail.com <ma...@hotmail.com>> wrote:
>> Sqoop doesn’t use MapR… unless you meant to say M/R (Map Reduce)
>>
>> The largest problem with sqoop is that in order to gain parallelism you need to know how your underlying table is partitioned and to do multiple range queries. This may not be known, or your data may or may not be equally distributed across the ranges.
>>
>> If you’re bringing over the entire table, you may find dropping it and then moving it to HDFS and then doing a bulk load to be more efficient.
>> (This is less flexible than sqoop, but also stresses the database servers less. )
>>
>> Again, YMMV
>>
>>
>>> On Apr 8, 2016, at 9:17 AM, Mich Talebzadeh <mich.talebzadeh@gmail.com <ma...@gmail.com>> wrote:
>>>
>>> Well unless you have plenty of memory, you are going to have certain issues with Spark.
>>>
>>> I tried to load a billion rows table from oracle through spark using JDBC and ended up with "Caused by: java.lang.OutOfMemoryError: Java heap space" error.
>>>
>>> Sqoop uses MapR and does it in serial mode which takes time and you can also tell it to create Hive table. However, it will import data into Hive table.
>>>
>>> In any case the mechanism of data import is through JDBC, Spark uses memory and DAG, whereas Sqoop relies on MapR.
>>>
>>> There is of course another alternative.
>>>
>>> Assuming that your Oracle table has a primary Key say "ID" (it would be easier if it was a monotonically increasing number) or already partitioned.
>>>
>>> You can create views based on the range of ID or for each partition. You can then SELECT COLUMNS co1, col2, coln from view and spool it to a text file on OS (locally say backup directory would be fastest).
>>> bzip2 those files and scp them to a local directory in Hadoop
>>> You can then use Spark/hive to load the target table from local files in parallel
>>> When creating views take care of NUMBER and CHAR columns in Oracle and convert them to TO_CHAR(NUMBER_COLUMN) and varchar CAST(coln AS VARCHAR2(n)) AS coln etc
>>>
>>> HTH
>>>
>>>
>>>
>>> Dr Mich Talebzadeh
>>>
>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>
>>>
>>> http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/>
>>>
>>>
>>> On 8 April 2016 at 10:07, Gourav Sengupta <gourav.sengupta@gmail.com <ma...@gmail.com>> wrote:
>>> Hi,
>>>
>>> Some metrics thrown around the discussion:
>>>
>>> SQOOP: extract 500 million rows (in single thread) 20 mins (data size 21 GB)
>>> SPARK: load the data into memory (15 mins)
>>>
>>> SPARK: use JDBC (and similar to SQOOP difficult parallelization) to load 500 million records - manually killed after 8 hours.
>>>
>>> (both the above studies were done in a system of same capacity, with 32 GB RAM and dual hexacore Xeon processors and SSD. SPARK was running locally, and SQOOP ran on HADOOP2 and extracted data to local file system)
>>>
>>> In case any one needs to know what needs to be done to access both the CSV and JDBC modules in SPARK Local Server mode, please let me know.
>>>
>>>
>>> Regards,
>>> Gourav Sengupta
>>>
>>> On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <java8964@hotmail.com <ma...@hotmail.com>> wrote:
>>> Good to know that.
>>>
>>> That is why Sqoop has this "direct" mode, to utilize the vendor specific feature.
>>>
>>> But for MPP, I still think it makes sense that vendor provide some kind of InputFormat, or data source in Spark, so Hadoop eco-system can integrate with them more natively.
>>>
>>> Yong
>>>
>>> Date: Wed, 6 Apr 2016 16:12:30 -0700
>>> Subject: Re: Sqoop on Spark
>>> From: mohajeri@gmail.com <ma...@gmail.com>
>>> To: java8964@hotmail.com <ma...@hotmail.com>
>>> CC: mich.talebzadeh@gmail.com <ma...@gmail.com>; jornfranke@gmail.com <ma...@gmail.com>; msegel_hadoop@hotmail.com <ma...@hotmail.com>; guha.ayan@gmail.com <ma...@gmail.com>; linguin.m.s@gmail.com <ma...@gmail.com>; user@spark.apache.org <ma...@spark.apache.org>
>>>
>>>
>>> It is using JDBC driver, i know that's the case for Teradata:
>>> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available <http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available>
>>>
>>> Teradata Connector (which is used by Cloudera and Hortonworks) for doing Sqoop is parallelized and works with ORC and probably other formats as well. It is using JDBC for each connection between data-nodes and their AMP (compute) nodes. There is an additional layer that coordinates all of it.
>>> I know Oracle has a similar technology I've used it and had to supply the JDBC driver.
>>>
>>> Teradata Connector is for batch data copy, QueryGrid is for interactive data movement.
>>>
>>> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <java8964@hotmail.com <ma...@hotmail.com>> wrote:
>>> If they do that, they must provide a customized input format, instead of through JDBC.
>>>
>>> Yong
>>>
>>> Date: Wed, 6 Apr 2016 23:56:54 +0100
>>> Subject: Re: Sqoop on Spark
>>> From: mich.talebzadeh@gmail.com <ma...@gmail.com>
>>> To: mohajeri@gmail.com <ma...@gmail.com>
>>> CC: jornfranke@gmail.com <ma...@gmail.com>; msegel_hadoop@hotmail.com <ma...@hotmail.com>; guha.ayan@gmail.com <ma...@gmail.com>; linguin.m.s@gmail.com <ma...@gmail.com>; user@spark.apache.org <ma...@spark.apache.org>
>>>
>>>
>>> SAP Sybase IQ does that and I believe SAP Hana as well.
>>>
>>> Dr Mich Talebzadeh
>>>
>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>
>>>
>>> http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/>
>>>
>>> On 6 April 2016 at 23:49, Peyman Mohajerian <mohajeri@gmail.com <ma...@gmail.com>> wrote:
>>> For some MPP relational stores (not operational) it maybe feasible to run Spark jobs and also have data locality. I know QueryGrid (Teradata) and PolyBase (microsoft) use data locality to move data between their MPP and Hadoop.
>>> I would guess (have no idea) someone like IBM already is doing that for Spark, maybe a bit off topic!
>>>
>>> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jornfranke@gmail.com <ma...@gmail.com>> wrote:
>>> Well I am not sure, but using a database as a storage, such as relational databases or certain nosql databases (eg MongoDB) for Spark is generally a bad idea - no data locality, it cannot handle real big data volumes for compute and you may potentially overload an operational database.
>>> And if your job fails for whatever reason (eg scheduling ) then you have to pull everything out again. Sqoop and HDFS seems to me the more elegant solution together with spark. These "assumption" on parallelism have to be anyway made with any solution.
>>> Of course you can always redo things, but why - what benefit do you expect? A real big data platform has to support anyway many different tools otherwise people doing analytics will be limited.
>>>
>>> On 06 Apr 2016, at 20:05, Michael Segel <msegel_hadoop@hotmail.com <ma...@hotmail.com>> wrote:
>>>
>>> I don’t think its necessarily a bad idea.
>>>
>>> Sqoop is an ugly tool and it requires you to make some assumptions as a way to gain parallelism. (Not that most of the assumptions are not valid for most of the use cases…)
>>>
>>> Depending on what you want to do… your data may not be persisted on HDFS. There are use cases where your cluster is used for compute and not storage.
>>>
>>> I’d say that spending time re-inventing the wheel can be a good thing.
>>> It would be a good idea for many to rethink their ingestion process so that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing that term from Dean Wampler. ;-)
>>>
>>> Just saying. ;-)
>>>
>>> -Mike
>>>
>>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jornfranke@gmail.com <ma...@gmail.com>> wrote:
>>>
>>> I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling.
>>> You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.
>>> Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
>>>
>>> On 06 Apr 2016, at 07:33, ayan guha <guha.ayan@gmail.com <ma...@gmail.com>> wrote:
>>>
>>> One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
>>>
>>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jornfranke@gmail.com <ma...@gmail.com>> wrote:
>>> Why do you want to reimplement something which is already there?
>>>
>>> On 06 Apr 2016, at 06:47, ayan guha <guha.ayan@gmail.com <ma...@gmail.com>> wrote:
>>>
>>> Hi
>>>
>>> Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
>>>
>>> What do you think?
>>>
>>> Best
>>> Ayan
>>>
>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <linguin.m.s@gmail.com <ma...@gmail.com>> wrote:
>>> Hi,
>>>
>>> It depends on your use case using sqoop.
>>> What's it like?
>>>
>>> // maropu
>>>
>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <guha.ayan@gmail.com <ma...@gmail.com>> wrote:
>>> Hi All
>>>
>>> Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>>
>>> --
>>> ---
>>> Takeshi Yamamuro
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
Re: Sqoop on Spark
Posted by Jörn Franke <jo...@gmail.com>.
I am not 100% sure, but you could export to CSV in Oracle using external tables.
Oracle has also the Hadoop Loader, which seems to support Avro. However, I think you need to buy the Big Data solution.
> On 10 Apr 2016, at 16:12, Mich Talebzadeh <mi...@gmail.com> wrote:
>
> Yes I meant MR.
>
> Again one cannot beat the RDBMS export utility. I was specifically referring to Oracle in above case that does not provide any specific text bases export except the binary one Exp, data pump etc).
>
> In case of SAPO ASE, Sybase IQ, and MSSQL, one can use BCP (bulk copy) that can be parallelised either through range partitioning or simple round robin partitioning that can be used to get data out to file in parallel. Then once get data into Hive table through import etc.
>
> In general if the source table is very large you can used either SAP Replication Server (SRS) or Oracle Golden Gate to get data to Hive. Both these replication tools provide connectors to Hive and they do a good job. If one has something like Oracle in Prod then there is likely a Golden Gate there. For bulk setting of Hive tables and data migration, replication server is good option.
>
> HTH
>
>
> Dr Mich Talebzadeh
>
> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>
> http://talebzadehmich.wordpress.com
>
>
>> On 10 April 2016 at 14:24, Michael Segel <ms...@hotmail.com> wrote:
>> Sqoop doesn’t use MapR… unless you meant to say M/R (Map Reduce)
>>
>> The largest problem with sqoop is that in order to gain parallelism you need to know how your underlying table is partitioned and to do multiple range queries. This may not be known, or your data may or may not be equally distributed across the ranges.
>>
>> If you’re bringing over the entire table, you may find dropping it and then moving it to HDFS and then doing a bulk load to be more efficient.
>> (This is less flexible than sqoop, but also stresses the database servers less. )
>>
>> Again, YMMV
>>
>>
>>> On Apr 8, 2016, at 9:17 AM, Mich Talebzadeh <mi...@gmail.com> wrote:
>>>
>>> Well unless you have plenty of memory, you are going to have certain issues with Spark.
>>>
>>> I tried to load a billion rows table from oracle through spark using JDBC and ended up with "Caused by: java.lang.OutOfMemoryError: Java heap space" error.
>>>
>>> Sqoop uses MapR and does it in serial mode which takes time and you can also tell it to create Hive table. However, it will import data into Hive table.
>>>
>>> In any case the mechanism of data import is through JDBC, Spark uses memory and DAG, whereas Sqoop relies on MapR.
>>>
>>> There is of course another alternative.
>>>
>>> Assuming that your Oracle table has a primary Key say "ID" (it would be easier if it was a monotonically increasing number) or already partitioned.
>>>
>>> You can create views based on the range of ID or for each partition. You can then SELECT COLUMNS co1, col2, coln from view and spool it to a text file on OS (locally say backup directory would be fastest).
>>> bzip2 those files and scp them to a local directory in Hadoop
>>> You can then use Spark/hive to load the target table from local files in parallel
>>> When creating views take care of NUMBER and CHAR columns in Oracle and convert them to TO_CHAR(NUMBER_COLUMN) and varchar CAST(coln AS VARCHAR2(n)) AS coln etc
>>>
>>> HTH
>>>
>>>
>>>
>>> Dr Mich Talebzadeh
>>>
>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>>> On 8 April 2016 at 10:07, Gourav Sengupta <go...@gmail.com> wrote:
>>>> Hi,
>>>>
>>>> Some metrics thrown around the discussion:
>>>>
>>>> SQOOP: extract 500 million rows (in single thread) 20 mins (data size 21 GB)
>>>> SPARK: load the data into memory (15 mins)
>>>>
>>>> SPARK: use JDBC (and similar to SQOOP difficult parallelization) to load 500 million records - manually killed after 8 hours.
>>>>
>>>> (both the above studies were done in a system of same capacity, with 32 GB RAM and dual hexacore Xeon processors and SSD. SPARK was running locally, and SQOOP ran on HADOOP2 and extracted data to local file system)
>>>>
>>>> In case any one needs to know what needs to be done to access both the CSV and JDBC modules in SPARK Local Server mode, please let me know.
>>>>
>>>>
>>>> Regards,
>>>> Gourav Sengupta
>>>>
>>>>> On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <ja...@hotmail.com> wrote:
>>>>> Good to know that.
>>>>>
>>>>> That is why Sqoop has this "direct" mode, to utilize the vendor specific feature.
>>>>>
>>>>> But for MPP, I still think it makes sense that vendor provide some kind of InputFormat, or data source in Spark, so Hadoop eco-system can integrate with them more natively.
>>>>>
>>>>> Yong
>>>>>
>>>>> Date: Wed, 6 Apr 2016 16:12:30 -0700
>>>>> Subject: Re: Sqoop on Spark
>>>>> From: mohajeri@gmail.com
>>>>> To: java8964@hotmail.com
>>>>> CC: mich.talebzadeh@gmail.com; jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
>>>>>
>>>>>
>>>>> It is using JDBC driver, i know that's the case for Teradata:
>>>>> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
>>>>>
>>>>> Teradata Connector (which is used by Cloudera and Hortonworks) for doing Sqoop is parallelized and works with ORC and probably other formats as well. It is using JDBC for each connection between data-nodes and their AMP (compute) nodes. There is an additional layer that coordinates all of it.
>>>>> I know Oracle has a similar technology I've used it and had to supply the JDBC driver.
>>>>>
>>>>> Teradata Connector is for batch data copy, QueryGrid is for interactive data movement.
>>>>>
>>>>> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com> wrote:
>>>>> If they do that, they must provide a customized input format, instead of through JDBC.
>>>>>
>>>>> Yong
>>>>>
>>>>> Date: Wed, 6 Apr 2016 23:56:54 +0100
>>>>> Subject: Re: Sqoop on Spark
>>>>> From: mich.talebzadeh@gmail.com
>>>>> To: mohajeri@gmail.com
>>>>> CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
>>>>>
>>>>>
>>>>> SAP Sybase IQ does that and I believe SAP Hana as well.
>>>>>
>>>>> Dr Mich Talebzadeh
>>>>>
>>>>> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>>
>>>>> http://talebzadehmich.wordpress.com
>>>>>
>>>>>
>>>>>
>>>>> On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
>>>>> For some MPP relational stores (not operational) it maybe feasible to run Spark jobs and also have data locality. I know QueryGrid (Teradata) and PolyBase (microsoft) use data locality to move data between their MPP and Hadoop.
>>>>> I would guess (have no idea) someone like IBM already is doing that for Spark, maybe a bit off topic!
>>>>>
>>>>> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>> Well I am not sure, but using a database as a storage, such as relational databases or certain nosql databases (eg MongoDB) for Spark is generally a bad idea - no data locality, it cannot handle real big data volumes for compute and you may potentially overload an operational database.
>>>>> And if your job fails for whatever reason (eg scheduling ) then you have to pull everything out again. Sqoop and HDFS seems to me the more elegant solution together with spark. These "assumption" on parallelism have to be anyway made with any solution.
>>>>> Of course you can always redo things, but why - what benefit do you expect? A real big data platform has to support anyway many different tools otherwise people doing analytics will be limited.
>>>>>
>>>>> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
>>>>>
>>>>> I don’t think its necessarily a bad idea.
>>>>>
>>>>> Sqoop is an ugly tool and it requires you to make some assumptions as a way to gain parallelism. (Not that most of the assumptions are not valid for most of the use cases…)
>>>>>
>>>>> Depending on what you want to do… your data may not be persisted on HDFS. There are use cases where your cluster is used for compute and not storage.
>>>>>
>>>>> I’d say that spending time re-inventing the wheel can be a good thing.
>>>>> It would be a good idea for many to rethink their ingestion process so that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing that term from Dean Wampler. ;-)
>>>>>
>>>>> Just saying. ;-)
>>>>>
>>>>> -Mike
>>>>>
>>>>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>>
>>>>> I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling.
>>>>> You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.
>>>>> Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
>>>>>
>>>>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>>>>
>>>>> One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
>>>>>
>>>>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>>> Why do you want to reimplement something which is already there?
>>>>>
>>>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>>>
>>>>> Hi
>>>>>
>>>>> Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
>>>>>
>>>>> What do you think?
>>>>>
>>>>> Best
>>>>> Ayan
>>>>>
>>>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com> wrote:
>>>>> Hi,
>>>>>
>>>>> It depends on your use case using sqoop.
>>>>> What's it like?
>>>>>
>>>>> // maropu
>>>>>
>>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>> Hi All
>>>>>
>>>>> Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> ---
>>>>> Takeshi Yamamuro
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>
Re: Sqoop on Spark
Posted by Mich Talebzadeh <mi...@gmail.com>.
Yes I meant MR.
Again one cannot beat the RDBMS export utility. I was specifically
referring to Oracle in above case that does not provide any specific text
bases export except the binary one Exp, data pump etc).
In case of SAPO ASE, Sybase IQ, and MSSQL, one can use BCP (bulk copy) that
can be parallelised either through range partitioning or simple round robin
partitioning that can be used to get data out to file in parallel. Then
once get data into Hive table through import etc.
In general if the source table is very large you can used either SAP
Replication Server (SRS) or Oracle Golden Gate to get data to Hive. Both
these replication tools provide connectors to Hive and they do a good job.
If one has something like Oracle in Prod then there is likely a Golden Gate
there. For bulk setting of Hive tables and data migration, replication
server is good option.
HTH
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 10 April 2016 at 14:24, Michael Segel <ms...@hotmail.com> wrote:
> Sqoop doesn’t use MapR… unless you meant to say M/R (Map Reduce)
>
> The largest problem with sqoop is that in order to gain parallelism you
> need to know how your underlying table is partitioned and to do multiple
> range queries. This may not be known, or your data may or may not be
> equally distributed across the ranges.
>
> If you’re bringing over the entire table, you may find dropping it and
> then moving it to HDFS and then doing a bulk load to be more efficient.
> (This is less flexible than sqoop, but also stresses the database servers
> less. )
>
> Again, YMMV
>
>
> On Apr 8, 2016, at 9:17 AM, Mich Talebzadeh <mi...@gmail.com>
> wrote:
>
> Well unless you have plenty of memory, you are going to have certain
> issues with Spark.
>
> I tried to load a billion rows table from oracle through spark using JDBC
> and ended up with "Caused by: java.lang.OutOfMemoryError: Java heap space"
> error.
>
> Sqoop uses MapR and does it in serial mode which takes time and you can
> also tell it to create Hive table. However, it will import data into Hive
> table.
>
> In any case the mechanism of data import is through JDBC, Spark uses
> memory and DAG, whereas Sqoop relies on MapR.
>
> There is of course another alternative.
>
> Assuming that your Oracle table has a primary Key say "ID" (it would be
> easier if it was a monotonically increasing number) or already partitioned.
>
>
> 1. You can create views based on the range of ID or for each
> partition. You can then SELECT COLUMNS co1, col2, coln from view and spool
> it to a text file on OS (locally say backup directory would be fastest).
> 2. bzip2 those files and scp them to a local directory in Hadoop
> 3. You can then use Spark/hive to load the target table from local
> files in parallel
> 4. When creating views take care of NUMBER and CHAR columns in Oracle
> and convert them to TO_CHAR(NUMBER_COLUMN) and varchar CAST(coln AS
> VARCHAR2(n)) AS coln etc
>
>
> HTH
>
>
>
> Dr Mich Talebzadeh
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> On 8 April 2016 at 10:07, Gourav Sengupta <go...@gmail.com>
> wrote:
>
>> Hi,
>>
>> Some metrics thrown around the discussion:
>>
>> SQOOP: extract 500 million rows (in single thread) 20 mins (data size 21
>> GB)
>> SPARK: load the data into memory (15 mins)
>>
>> SPARK: use JDBC (and similar to SQOOP difficult parallelization) to load
>> 500 million records - manually killed after 8 hours.
>>
>> (both the above studies were done in a system of same capacity, with 32
>> GB RAM and dual hexacore Xeon processors and SSD. SPARK was running
>> locally, and SQOOP ran on HADOOP2 and extracted data to local file system)
>>
>> In case any one needs to know what needs to be done to access both the
>> CSV and JDBC modules in SPARK Local Server mode, please let me know.
>>
>>
>> Regards,
>> Gourav Sengupta
>>
>> On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <ja...@hotmail.com> wrote:
>>
>>> Good to know that.
>>>
>>> That is why Sqoop has this "direct" mode, to utilize the vendor specific
>>> feature.
>>>
>>> But for MPP, I still think it makes sense that vendor provide some kind
>>> of InputFormat, or data source in Spark, so Hadoop eco-system can integrate
>>> with them more natively.
>>>
>>> Yong
>>>
>>> ------------------------------
>>> Date: Wed, 6 Apr 2016 16:12:30 -0700
>>> Subject: Re: Sqoop on Spark
>>> From: mohajeri@gmail.com
>>> To: java8964@hotmail.com
>>> CC: mich.talebzadeh@gmail.com; jornfranke@gmail.com;
>>> msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com;
>>> user@spark.apache.org
>>>
>>>
>>> It is using JDBC driver, i know that's the case for Teradata:
>>>
>>> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
>>>
>>> Teradata Connector (which is used by Cloudera and Hortonworks) for doing
>>> Sqoop is parallelized and works with ORC and probably other formats as
>>> well. It is using JDBC for each connection between data-nodes and their AMP
>>> (compute) nodes. There is an additional layer that coordinates all of it.
>>> I know Oracle has a similar technology I've used it and had to supply
>>> the JDBC driver.
>>>
>>> Teradata Connector is for batch data copy, QueryGrid is for interactive
>>> data movement.
>>>
>>> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com> wrote:
>>>
>>> If they do that, they must provide a customized input format, instead of
>>> through JDBC.
>>>
>>> Yong
>>>
>>> ------------------------------
>>> Date: Wed, 6 Apr 2016 23:56:54 +0100
>>> Subject: Re: Sqoop on Spark
>>> From: mich.talebzadeh@gmail.com
>>> To: mohajeri@gmail.com
>>> CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com;
>>> linguin.m.s@gmail.com; user@spark.apache.org
>>>
>>>
>>> SAP Sybase IQ does that and I believe SAP Hana as well.
>>>
>>> Dr Mich Talebzadeh
>>>
>>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>> On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
>>>
>>> For some MPP relational stores (not operational) it maybe feasible to
>>> run Spark jobs and also have data locality. I know QueryGrid (Teradata) and
>>> PolyBase (microsoft) use data locality to move data between their MPP and
>>> Hadoop.
>>> I would guess (have no idea) someone like IBM already is doing that for
>>> Spark, maybe a bit off topic!
>>>
>>> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com>
>>> wrote:
>>>
>>> Well I am not sure, but using a database as a storage, such as
>>> relational databases or certain nosql databases (eg MongoDB) for Spark is
>>> generally a bad idea - no data locality, it cannot handle real big data
>>> volumes for compute and you may potentially overload an operational
>>> database.
>>> And if your job fails for whatever reason (eg scheduling ) then you have
>>> to pull everything out again. Sqoop and HDFS seems to me the more elegant
>>> solution together with spark. These "assumption" on parallelism have to be
>>> anyway made with any solution.
>>> Of course you can always redo things, but why - what benefit do you
>>> expect? A real big data platform has to support anyway many different tools
>>> otherwise people doing analytics will be limited.
>>>
>>> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com>
>>> wrote:
>>>
>>> I don’t think its necessarily a bad idea.
>>>
>>> Sqoop is an ugly tool and it requires you to make some assumptions as a
>>> way to gain parallelism. (Not that most of the assumptions are not valid
>>> for most of the use cases…)
>>>
>>> Depending on what you want to do… your data may not be persisted on
>>> HDFS. There are use cases where your cluster is used for compute and not
>>> storage.
>>>
>>> I’d say that spending time re-inventing the wheel can be a good thing.
>>> It would be a good idea for many to rethink their ingestion process so
>>> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
>>> that term from Dean Wampler. ;-)
>>>
>>> Just saying. ;-)
>>>
>>> -Mike
>>>
>>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>
>>> I do not think you can be more resource efficient. In the end you have
>>> to store the data anyway on HDFS . You have a lot of development effort for
>>> doing something like sqoop. Especially with error handling.
>>> You may create a ticket with the Sqoop guys to support Spark as an
>>> execution engine and maybe it is less effort to plug it in there.
>>> Maybe if your cluster is loaded then you may want to add more machines
>>> or improve the existing programs.
>>>
>>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>>
>>> One of the reason in my mind is to avoid Map-Reduce application
>>> completely during ingestion, if possible. Also, I can then use Spark stand
>>> alone cluster to ingest, even if my hadoop cluster is heavily loaded. What
>>> you guys think?
>>>
>>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com>
>>> wrote:
>>>
>>> Why do you want to reimplement something which is already there?
>>>
>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>
>>> Hi
>>>
>>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>>> index and incremental only) and add data to existing Hive tables. Also, it
>>> would be good to have an option to create Hive table, driven by job
>>> specific configuration.
>>>
>>> What do you think?
>>>
>>> Best
>>> Ayan
>>>
>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>>> wrote:
>>>
>>> Hi,
>>>
>>> It depends on your use case using sqoop.
>>> What's it like?
>>>
>>> // maropu
>>>
>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>
>>> Hi All
>>>
>>> Asking opinion: is it possible/advisable to use spark to replace what
>>> sqoop does? Any existing project done in similar lines?
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>>
>>>
>>> --
>>> ---
>>> Takeshi Yamamuro
>>>
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>>
>>>
>>>
>>>
>>
>
>
Re: Sqoop on Spark
Posted by Michael Segel <ms...@hotmail.com>.
Sqoop doesn’t use MapR… unless you meant to say M/R (Map Reduce)
The largest problem with sqoop is that in order to gain parallelism you need to know how your underlying table is partitioned and to do multiple range queries. This may not be known, or your data may or may not be equally distributed across the ranges.
If you’re bringing over the entire table, you may find dropping it and then moving it to HDFS and then doing a bulk load to be more efficient.
(This is less flexible than sqoop, but also stresses the database servers less. )
Again, YMMV
> On Apr 8, 2016, at 9:17 AM, Mich Talebzadeh <mi...@gmail.com> wrote:
>
> Well unless you have plenty of memory, you are going to have certain issues with Spark.
>
> I tried to load a billion rows table from oracle through spark using JDBC and ended up with "Caused by: java.lang.OutOfMemoryError: Java heap space" error.
>
> Sqoop uses MapR and does it in serial mode which takes time and you can also tell it to create Hive table. However, it will import data into Hive table.
>
> In any case the mechanism of data import is through JDBC, Spark uses memory and DAG, whereas Sqoop relies on MapR.
>
> There is of course another alternative.
>
> Assuming that your Oracle table has a primary Key say "ID" (it would be easier if it was a monotonically increasing number) or already partitioned.
>
> You can create views based on the range of ID or for each partition. You can then SELECT COLUMNS co1, col2, coln from view and spool it to a text file on OS (locally say backup directory would be fastest).
> bzip2 those files and scp them to a local directory in Hadoop
> You can then use Spark/hive to load the target table from local files in parallel
> When creating views take care of NUMBER and CHAR columns in Oracle and convert them to TO_CHAR(NUMBER_COLUMN) and varchar CAST(coln AS VARCHAR2(n)) AS coln etc
>
> HTH
>
>
>
> Dr Mich Talebzadeh
>
> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>
>
> http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/>
>
>
> On 8 April 2016 at 10:07, Gourav Sengupta <gourav.sengupta@gmail.com <ma...@gmail.com>> wrote:
> Hi,
>
> Some metrics thrown around the discussion:
>
> SQOOP: extract 500 million rows (in single thread) 20 mins (data size 21 GB)
> SPARK: load the data into memory (15 mins)
>
> SPARK: use JDBC (and similar to SQOOP difficult parallelization) to load 500 million records - manually killed after 8 hours.
>
> (both the above studies were done in a system of same capacity, with 32 GB RAM and dual hexacore Xeon processors and SSD. SPARK was running locally, and SQOOP ran on HADOOP2 and extracted data to local file system)
>
> In case any one needs to know what needs to be done to access both the CSV and JDBC modules in SPARK Local Server mode, please let me know.
>
>
> Regards,
> Gourav Sengupta
>
> On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <java8964@hotmail.com <ma...@hotmail.com>> wrote:
> Good to know that.
>
> That is why Sqoop has this "direct" mode, to utilize the vendor specific feature.
>
> But for MPP, I still think it makes sense that vendor provide some kind of InputFormat, or data source in Spark, so Hadoop eco-system can integrate with them more natively.
>
> Yong
>
> Date: Wed, 6 Apr 2016 16:12:30 -0700
> Subject: Re: Sqoop on Spark
> From: mohajeri@gmail.com <ma...@gmail.com>
> To: java8964@hotmail.com <ma...@hotmail.com>
> CC: mich.talebzadeh@gmail.com <ma...@gmail.com>; jornfranke@gmail.com <ma...@gmail.com>; msegel_hadoop@hotmail.com <ma...@hotmail.com>; guha.ayan@gmail.com <ma...@gmail.com>; linguin.m.s@gmail.com <ma...@gmail.com>; user@spark.apache.org <ma...@spark.apache.org>
>
>
> It is using JDBC driver, i know that's the case for Teradata:
> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available <http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available>
>
> Teradata Connector (which is used by Cloudera and Hortonworks) for doing Sqoop is parallelized and works with ORC and probably other formats as well. It is using JDBC for each connection between data-nodes and their AMP (compute) nodes. There is an additional layer that coordinates all of it.
> I know Oracle has a similar technology I've used it and had to supply the JDBC driver.
>
> Teradata Connector is for batch data copy, QueryGrid is for interactive data movement.
>
> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <java8964@hotmail.com <ma...@hotmail.com>> wrote:
> If they do that, they must provide a customized input format, instead of through JDBC.
>
> Yong
>
> Date: Wed, 6 Apr 2016 23:56:54 +0100
> Subject: Re: Sqoop on Spark
> From: mich.talebzadeh@gmail.com <ma...@gmail.com>
> To: mohajeri@gmail.com <ma...@gmail.com>
> CC: jornfranke@gmail.com <ma...@gmail.com>; msegel_hadoop@hotmail.com <ma...@hotmail.com>; guha.ayan@gmail.com <ma...@gmail.com>; linguin.m.s@gmail.com <ma...@gmail.com>; user@spark.apache.org <ma...@spark.apache.org>
>
>
> SAP Sybase IQ does that and I believe SAP Hana as well.
>
> Dr Mich Talebzadeh
>
> LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>
>
> http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/>
>
> On 6 April 2016 at 23:49, Peyman Mohajerian <mohajeri@gmail.com <ma...@gmail.com>> wrote:
> For some MPP relational stores (not operational) it maybe feasible to run Spark jobs and also have data locality. I know QueryGrid (Teradata) and PolyBase (microsoft) use data locality to move data between their MPP and Hadoop.
> I would guess (have no idea) someone like IBM already is doing that for Spark, maybe a bit off topic!
>
> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jornfranke@gmail.com <ma...@gmail.com>> wrote:
> Well I am not sure, but using a database as a storage, such as relational databases or certain nosql databases (eg MongoDB) for Spark is generally a bad idea - no data locality, it cannot handle real big data volumes for compute and you may potentially overload an operational database.
> And if your job fails for whatever reason (eg scheduling ) then you have to pull everything out again. Sqoop and HDFS seems to me the more elegant solution together with spark. These "assumption" on parallelism have to be anyway made with any solution.
> Of course you can always redo things, but why - what benefit do you expect? A real big data platform has to support anyway many different tools otherwise people doing analytics will be limited.
>
> On 06 Apr 2016, at 20:05, Michael Segel <msegel_hadoop@hotmail.com <ma...@hotmail.com>> wrote:
>
> I don’t think its necessarily a bad idea.
>
> Sqoop is an ugly tool and it requires you to make some assumptions as a way to gain parallelism. (Not that most of the assumptions are not valid for most of the use cases…)
>
> Depending on what you want to do… your data may not be persisted on HDFS. There are use cases where your cluster is used for compute and not storage.
>
> I’d say that spending time re-inventing the wheel can be a good thing.
> It would be a good idea for many to rethink their ingestion process so that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing that term from Dean Wampler. ;-)
>
> Just saying. ;-)
>
> -Mike
>
> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jornfranke@gmail.com <ma...@gmail.com>> wrote:
>
> I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling.
> You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.
> Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
>
> On 06 Apr 2016, at 07:33, ayan guha <guha.ayan@gmail.com <ma...@gmail.com>> wrote:
>
> One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
>
> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jornfranke@gmail.com <ma...@gmail.com>> wrote:
> Why do you want to reimplement something which is already there?
>
> On 06 Apr 2016, at 06:47, ayan guha <guha.ayan@gmail.com <ma...@gmail.com>> wrote:
>
> Hi
>
> Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
>
> What do you think?
>
> Best
> Ayan
>
> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <linguin.m.s@gmail.com <ma...@gmail.com>> wrote:
> Hi,
>
> It depends on your use case using sqoop.
> What's it like?
>
> // maropu
>
> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <guha.ayan@gmail.com <ma...@gmail.com>> wrote:
> Hi All
>
> Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
>
> --
> Best Regards,
> Ayan Guha
>
>
>
> --
> ---
> Takeshi Yamamuro
>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
>
>
>
Re: Sqoop on Spark
Posted by Mich Talebzadeh <mi...@gmail.com>.
Well unless you have plenty of memory, you are going to have certain issues
with Spark.
I tried to load a billion rows table from oracle through spark using JDBC
and ended up with "Caused by: java.lang.OutOfMemoryError: Java heap space"
error.
Sqoop uses MapR and does it in serial mode which takes time and you can
also tell it to create Hive table. However, it will import data into Hive
table.
In any case the mechanism of data import is through JDBC, Spark uses memory
and DAG, whereas Sqoop relies on MapR.
There is of course another alternative.
Assuming that your Oracle table has a primary Key say "ID" (it would be
easier if it was a monotonically increasing number) or already partitioned.
1. You can create views based on the range of ID or for each partition.
You can then SELECT COLUMNS co1, col2, coln from view and spool it to a
text file on OS (locally say backup directory would be fastest).
2. bzip2 those files and scp them to a local directory in Hadoop
3. You can then use Spark/hive to load the target table from local files
in parallel
4. When creating views take care of NUMBER and CHAR columns in Oracle
and convert them to TO_CHAR(NUMBER_COLUMN) and varchar CAST(coln AS
VARCHAR2(n)) AS coln etc
HTH
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 8 April 2016 at 10:07, Gourav Sengupta <go...@gmail.com> wrote:
> Hi,
>
> Some metrics thrown around the discussion:
>
> SQOOP: extract 500 million rows (in single thread) 20 mins (data size 21
> GB)
> SPARK: load the data into memory (15 mins)
>
> SPARK: use JDBC (and similar to SQOOP difficult parallelization) to load
> 500 million records - manually killed after 8 hours.
>
> (both the above studies were done in a system of same capacity, with 32 GB
> RAM and dual hexacore Xeon processors and SSD. SPARK was running locally,
> and SQOOP ran on HADOOP2 and extracted data to local file system)
>
> In case any one needs to know what needs to be done to access both the CSV
> and JDBC modules in SPARK Local Server mode, please let me know.
>
>
> Regards,
> Gourav Sengupta
>
> On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <ja...@hotmail.com> wrote:
>
>> Good to know that.
>>
>> That is why Sqoop has this "direct" mode, to utilize the vendor specific
>> feature.
>>
>> But for MPP, I still think it makes sense that vendor provide some kind
>> of InputFormat, or data source in Spark, so Hadoop eco-system can integrate
>> with them more natively.
>>
>> Yong
>>
>> ------------------------------
>> Date: Wed, 6 Apr 2016 16:12:30 -0700
>> Subject: Re: Sqoop on Spark
>> From: mohajeri@gmail.com
>> To: java8964@hotmail.com
>> CC: mich.talebzadeh@gmail.com; jornfranke@gmail.com;
>> msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com;
>> user@spark.apache.org
>>
>>
>> It is using JDBC driver, i know that's the case for Teradata:
>>
>> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
>>
>> Teradata Connector (which is used by Cloudera and Hortonworks) for doing
>> Sqoop is parallelized and works with ORC and probably other formats as
>> well. It is using JDBC for each connection between data-nodes and their AMP
>> (compute) nodes. There is an additional layer that coordinates all of it.
>> I know Oracle has a similar technology I've used it and had to supply the
>> JDBC driver.
>>
>> Teradata Connector is for batch data copy, QueryGrid is for interactive
>> data movement.
>>
>> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com> wrote:
>>
>> If they do that, they must provide a customized input format, instead of
>> through JDBC.
>>
>> Yong
>>
>> ------------------------------
>> Date: Wed, 6 Apr 2016 23:56:54 +0100
>> Subject: Re: Sqoop on Spark
>> From: mich.talebzadeh@gmail.com
>> To: mohajeri@gmail.com
>> CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com;
>> linguin.m.s@gmail.com; user@spark.apache.org
>>
>>
>> SAP Sybase IQ does that and I believe SAP Hana as well.
>>
>> Dr Mich Talebzadeh
>>
>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>> On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
>>
>> For some MPP relational stores (not operational) it maybe feasible to run
>> Spark jobs and also have data locality. I know QueryGrid (Teradata) and
>> PolyBase (microsoft) use data locality to move data between their MPP and
>> Hadoop.
>> I would guess (have no idea) someone like IBM already is doing that for
>> Spark, maybe a bit off topic!
>>
>> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>> Well I am not sure, but using a database as a storage, such as relational
>> databases or certain nosql databases (eg MongoDB) for Spark is generally a
>> bad idea - no data locality, it cannot handle real big data volumes for
>> compute and you may potentially overload an operational database.
>> And if your job fails for whatever reason (eg scheduling ) then you have
>> to pull everything out again. Sqoop and HDFS seems to me the more elegant
>> solution together with spark. These "assumption" on parallelism have to be
>> anyway made with any solution.
>> Of course you can always redo things, but why - what benefit do you
>> expect? A real big data platform has to support anyway many different tools
>> otherwise people doing analytics will be limited.
>>
>> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com>
>> wrote:
>>
>> I don’t think its necessarily a bad idea.
>>
>> Sqoop is an ugly tool and it requires you to make some assumptions as a
>> way to gain parallelism. (Not that most of the assumptions are not valid
>> for most of the use cases…)
>>
>> Depending on what you want to do… your data may not be persisted on
>> HDFS. There are use cases where your cluster is used for compute and not
>> storage.
>>
>> I’d say that spending time re-inventing the wheel can be a good thing.
>> It would be a good idea for many to rethink their ingestion process so
>> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
>> that term from Dean Wampler. ;-)
>>
>> Just saying. ;-)
>>
>> -Mike
>>
>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>> I do not think you can be more resource efficient. In the end you have to
>> store the data anyway on HDFS . You have a lot of development effort for
>> doing something like sqoop. Especially with error handling.
>> You may create a ticket with the Sqoop guys to support Spark as an
>> execution engine and maybe it is less effort to plug it in there.
>> Maybe if your cluster is loaded then you may want to add more machines or
>> improve the existing programs.
>>
>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>
>> One of the reason in my mind is to avoid Map-Reduce application
>> completely during ingestion, if possible. Also, I can then use Spark stand
>> alone cluster to ingest, even if my hadoop cluster is heavily loaded. What
>> you guys think?
>>
>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>> Why do you want to reimplement something which is already there?
>>
>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>
>> Hi
>>
>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>> index and incremental only) and add data to existing Hive tables. Also, it
>> would be good to have an option to create Hive table, driven by job
>> specific configuration.
>>
>> What do you think?
>>
>> Best
>> Ayan
>>
>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>> wrote:
>>
>> Hi,
>>
>> It depends on your use case using sqoop.
>> What's it like?
>>
>> // maropu
>>
>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>
>> Hi All
>>
>> Asking opinion: is it possible/advisable to use spark to replace what
>> sqoop does? Any existing project done in similar lines?
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>>
>>
>> --
>> ---
>> Takeshi Yamamuro
>>
>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>>
>>
>>
>>
>
Re: Sqoop on Spark
Posted by Gourav Sengupta <go...@gmail.com>.
Hi,
Some metrics thrown around the discussion:
SQOOP: extract 500 million rows (in single thread) 20 mins (data size 21 GB)
SPARK: load the data into memory (15 mins)
SPARK: use JDBC (and similar to SQOOP difficult parallelization) to load
500 million records - manually killed after 8 hours.
(both the above studies were done in a system of same capacity, with 32 GB
RAM and dual hexacore Xeon processors and SSD. SPARK was running locally,
and SQOOP ran on HADOOP2 and extracted data to local file system)
In case any one needs to know what needs to be done to access both the CSV
and JDBC modules in SPARK Local Server mode, please let me know.
Regards,
Gourav Sengupta
On Thu, Apr 7, 2016 at 12:26 AM, Yong Zhang <ja...@hotmail.com> wrote:
> Good to know that.
>
> That is why Sqoop has this "direct" mode, to utilize the vendor specific
> feature.
>
> But for MPP, I still think it makes sense that vendor provide some kind of
> InputFormat, or data source in Spark, so Hadoop eco-system can integrate
> with them more natively.
>
> Yong
>
> ------------------------------
> Date: Wed, 6 Apr 2016 16:12:30 -0700
> Subject: Re: Sqoop on Spark
> From: mohajeri@gmail.com
> To: java8964@hotmail.com
> CC: mich.talebzadeh@gmail.com; jornfranke@gmail.com;
> msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com;
> user@spark.apache.org
>
>
> It is using JDBC driver, i know that's the case for Teradata:
>
> http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
>
> Teradata Connector (which is used by Cloudera and Hortonworks) for doing
> Sqoop is parallelized and works with ORC and probably other formats as
> well. It is using JDBC for each connection between data-nodes and their AMP
> (compute) nodes. There is an additional layer that coordinates all of it.
> I know Oracle has a similar technology I've used it and had to supply the
> JDBC driver.
>
> Teradata Connector is for batch data copy, QueryGrid is for interactive
> data movement.
>
> On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com> wrote:
>
> If they do that, they must provide a customized input format, instead of
> through JDBC.
>
> Yong
>
> ------------------------------
> Date: Wed, 6 Apr 2016 23:56:54 +0100
> Subject: Re: Sqoop on Spark
> From: mich.talebzadeh@gmail.com
> To: mohajeri@gmail.com
> CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com;
> linguin.m.s@gmail.com; user@spark.apache.org
>
>
> SAP Sybase IQ does that and I believe SAP Hana as well.
>
> Dr Mich Talebzadeh
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
> http://talebzadehmich.wordpress.com
>
>
> On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
>
> For some MPP relational stores (not operational) it maybe feasible to run
> Spark jobs and also have data locality. I know QueryGrid (Teradata) and
> PolyBase (microsoft) use data locality to move data between their MPP and
> Hadoop.
> I would guess (have no idea) someone like IBM already is doing that for
> Spark, maybe a bit off topic!
>
> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> Well I am not sure, but using a database as a storage, such as relational
> databases or certain nosql databases (eg MongoDB) for Spark is generally a
> bad idea - no data locality, it cannot handle real big data volumes for
> compute and you may potentially overload an operational database.
> And if your job fails for whatever reason (eg scheduling ) then you have
> to pull everything out again. Sqoop and HDFS seems to me the more elegant
> solution together with spark. These "assumption" on parallelism have to be
> anyway made with any solution.
> Of course you can always redo things, but why - what benefit do you
> expect? A real big data platform has to support anyway many different tools
> otherwise people doing analytics will be limited.
>
> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
>
> I don’t think its necessarily a bad idea.
>
> Sqoop is an ugly tool and it requires you to make some assumptions as a
> way to gain parallelism. (Not that most of the assumptions are not valid
> for most of the use cases…)
>
> Depending on what you want to do… your data may not be persisted on HDFS.
> There are use cases where your cluster is used for compute and not storage.
>
> I’d say that spending time re-inventing the wheel can be a good thing.
> It would be a good idea for many to rethink their ingestion process so
> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
> that term from Dean Wampler. ;-)
>
> Just saying. ;-)
>
> -Mike
>
> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> I do not think you can be more resource efficient. In the end you have to
> store the data anyway on HDFS . You have a lot of development effort for
> doing something like sqoop. Especially with error handling.
> You may create a ticket with the Sqoop guys to support Spark as an
> execution engine and maybe it is less effort to plug it in there.
> Maybe if your cluster is loaded then you may want to add more machines or
> improve the existing programs.
>
> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>
> One of the reason in my mind is to avoid Map-Reduce application completely
> during ingestion, if possible. Also, I can then use Spark stand alone
> cluster to ingest, even if my hadoop cluster is heavily loaded. What you
> guys think?
>
> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> Why do you want to reimplement something which is already there?
>
> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>
> Hi
>
> Thanks for reply. My use case is query ~40 tables from Oracle (using index
> and incremental only) and add data to existing Hive tables. Also, it would
> be good to have an option to create Hive table, driven by job specific
> configuration.
>
> What do you think?
>
> Best
> Ayan
>
> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
> wrote:
>
> Hi,
>
> It depends on your use case using sqoop.
> What's it like?
>
> // maropu
>
> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>
> Hi All
>
> Asking opinion: is it possible/advisable to use spark to replace what
> sqoop does? Any existing project done in similar lines?
>
> --
> Best Regards,
> Ayan Guha
>
>
>
>
> --
> ---
> Takeshi Yamamuro
>
>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
>
>
>
RE: Sqoop on Spark
Posted by Yong Zhang <ja...@hotmail.com>.
Good to know that.
That is why Sqoop has this "direct" mode, to utilize the vendor specific feature.
But for MPP, I still think it makes sense that vendor provide some kind of InputFormat, or data source in Spark, so Hadoop eco-system can integrate with them more natively.
Yong
Date: Wed, 6 Apr 2016 16:12:30 -0700
Subject: Re: Sqoop on Spark
From: mohajeri@gmail.com
To: java8964@hotmail.com
CC: mich.talebzadeh@gmail.com; jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
It is using JDBC driver, i know that's the case for Teradata:http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
Teradata Connector (which is used by Cloudera and Hortonworks) for doing Sqoop is parallelized and works with ORC and probably other formats as well. It is using JDBC for each connection between data-nodes and their AMP (compute) nodes. There is an additional layer that coordinates all of it.I know Oracle has a similar technology I've used it and had to supply the JDBC driver.
Teradata Connector is for batch data copy, QueryGrid is for interactive data movement.
On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com> wrote:
If they do that, they must provide a customized input format, instead of through JDBC.
Yong
Date: Wed, 6 Apr 2016 23:56:54 +0100
Subject: Re: Sqoop on Spark
From: mich.talebzadeh@gmail.com
To: mohajeri@gmail.com
CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
SAP Sybase IQ does that and I believe SAP Hana as well.
Dr Mich Talebzadeh
LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
http://talebzadehmich.wordpress.com
On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
For some MPP relational stores (not operational) it maybe feasible to run Spark jobs and also have data locality. I know QueryGrid (Teradata) and PolyBase (microsoft) use data locality to move data between their MPP and Hadoop. I would guess (have no idea) someone like IBM already is doing that for Spark, maybe a bit off topic!
On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
Well I am not sure, but using a database as a storage, such as relational databases or certain nosql databases (eg MongoDB) for Spark is generally a bad idea - no data locality, it cannot handle real big data volumes for compute and you may potentially overload an operational database. And if your job fails for whatever reason (eg scheduling ) then you have to pull everything out again. Sqoop and HDFS seems to me the more elegant solution together with spark. These "assumption" on parallelism have to be anyway made with any solution.Of course you can always redo things, but why - what benefit do you expect? A real big data platform has to support anyway many different tools otherwise people doing analytics will be limited.
On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
I don’t think its necessarily a bad idea.
Sqoop is an ugly tool and it requires you to make some assumptions as a way to gain parallelism. (Not that most of the assumptions are not valid for most of the use cases…)
Depending on what you want to do… your data may not be persisted on HDFS. There are use cases where your cluster is used for compute and not storage.
I’d say that spending time re-inventing the wheel can be a good thing. It would be a good idea for many to rethink their ingestion process so that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing that term from Dean Wampler. ;-)
Just saying. ;-)
-Mike
On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling. You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
Why do you want to reimplement something which is already there?
On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
Hi
Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
What do you think?
BestAyan
On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com> wrote:
Hi,
It depends on your use case using sqoop.What's it like?
// maropu
On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
Hi All
Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
--
Best Regards,
Ayan Guha
--
---
Takeshi Yamamuro
--
Best Regards,
Ayan Guha
--
Best Regards,
Ayan Guha
Re: Sqoop on Spark
Posted by Peyman Mohajerian <mo...@gmail.com>.
It is using JDBC driver, i know that's the case for Teradata:
http://developer.teradata.com/connectivity/articles/teradata-connector-for-hadoop-now-available
Teradata Connector (which is used by Cloudera and Hortonworks) for doing
Sqoop is parallelized and works with ORC and probably other formats as
well. It is using JDBC for each connection between data-nodes and their AMP
(compute) nodes. There is an additional layer that coordinates all of it.
I know Oracle has a similar technology I've used it and had to supply the
JDBC driver.
Teradata Connector is for batch data copy, QueryGrid is for interactive
data movement.
On Wed, Apr 6, 2016 at 4:05 PM, Yong Zhang <ja...@hotmail.com> wrote:
> If they do that, they must provide a customized input format, instead of
> through JDBC.
>
> Yong
>
> ------------------------------
> Date: Wed, 6 Apr 2016 23:56:54 +0100
> Subject: Re: Sqoop on Spark
> From: mich.talebzadeh@gmail.com
> To: mohajeri@gmail.com
> CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com;
> linguin.m.s@gmail.com; user@spark.apache.org
>
>
> SAP Sybase IQ does that and I believe SAP Hana as well.
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
>
> For some MPP relational stores (not operational) it maybe feasible to run
> Spark jobs and also have data locality. I know QueryGrid (Teradata) and
> PolyBase (microsoft) use data locality to move data between their MPP and
> Hadoop.
> I would guess (have no idea) someone like IBM already is doing that for
> Spark, maybe a bit off topic!
>
> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> Well I am not sure, but using a database as a storage, such as relational
> databases or certain nosql databases (eg MongoDB) for Spark is generally a
> bad idea - no data locality, it cannot handle real big data volumes for
> compute and you may potentially overload an operational database.
> And if your job fails for whatever reason (eg scheduling ) then you have
> to pull everything out again. Sqoop and HDFS seems to me the more elegant
> solution together with spark. These "assumption" on parallelism have to be
> anyway made with any solution.
> Of course you can always redo things, but why - what benefit do you
> expect? A real big data platform has to support anyway many different tools
> otherwise people doing analytics will be limited.
>
> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
>
> I don’t think its necessarily a bad idea.
>
> Sqoop is an ugly tool and it requires you to make some assumptions as a
> way to gain parallelism. (Not that most of the assumptions are not valid
> for most of the use cases…)
>
> Depending on what you want to do… your data may not be persisted on HDFS.
> There are use cases where your cluster is used for compute and not storage.
>
> I’d say that spending time re-inventing the wheel can be a good thing.
> It would be a good idea for many to rethink their ingestion process so
> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
> that term from Dean Wampler. ;-)
>
> Just saying. ;-)
>
> -Mike
>
> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> I do not think you can be more resource efficient. In the end you have to
> store the data anyway on HDFS . You have a lot of development effort for
> doing something like sqoop. Especially with error handling.
> You may create a ticket with the Sqoop guys to support Spark as an
> execution engine and maybe it is less effort to plug it in there.
> Maybe if your cluster is loaded then you may want to add more machines or
> improve the existing programs.
>
> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>
> One of the reason in my mind is to avoid Map-Reduce application completely
> during ingestion, if possible. Also, I can then use Spark stand alone
> cluster to ingest, even if my hadoop cluster is heavily loaded. What you
> guys think?
>
> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> Why do you want to reimplement something which is already there?
>
> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>
> Hi
>
> Thanks for reply. My use case is query ~40 tables from Oracle (using index
> and incremental only) and add data to existing Hive tables. Also, it would
> be good to have an option to create Hive table, driven by job specific
> configuration.
>
> What do you think?
>
> Best
> Ayan
>
> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
> wrote:
>
> Hi,
>
> It depends on your use case using sqoop.
> What's it like?
>
> // maropu
>
> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>
> Hi All
>
> Asking opinion: is it possible/advisable to use spark to replace what
> sqoop does? Any existing project done in similar lines?
>
> --
> Best Regards,
> Ayan Guha
>
>
>
>
> --
> ---
> Takeshi Yamamuro
>
>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
>
>
RE: Sqoop on Spark
Posted by Yong Zhang <ja...@hotmail.com>.
If they do that, they must provide a customized input format, instead of through JDBC.
Yong
Date: Wed, 6 Apr 2016 23:56:54 +0100
Subject: Re: Sqoop on Spark
From: mich.talebzadeh@gmail.com
To: mohajeri@gmail.com
CC: jornfranke@gmail.com; msegel_hadoop@hotmail.com; guha.ayan@gmail.com; linguin.m.s@gmail.com; user@spark.apache.org
SAP Sybase IQ does that and I believe SAP Hana as well.
Dr Mich Talebzadeh
LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
http://talebzadehmich.wordpress.com
On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
For some MPP relational stores (not operational) it maybe feasible to run Spark jobs and also have data locality. I know QueryGrid (Teradata) and PolyBase (microsoft) use data locality to move data between their MPP and Hadoop. I would guess (have no idea) someone like IBM already is doing that for Spark, maybe a bit off topic!
On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
Well I am not sure, but using a database as a storage, such as relational databases or certain nosql databases (eg MongoDB) for Spark is generally a bad idea - no data locality, it cannot handle real big data volumes for compute and you may potentially overload an operational database. And if your job fails for whatever reason (eg scheduling ) then you have to pull everything out again. Sqoop and HDFS seems to me the more elegant solution together with spark. These "assumption" on parallelism have to be anyway made with any solution.Of course you can always redo things, but why - what benefit do you expect? A real big data platform has to support anyway many different tools otherwise people doing analytics will be limited.
On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
I don’t think its necessarily a bad idea.
Sqoop is an ugly tool and it requires you to make some assumptions as a way to gain parallelism. (Not that most of the assumptions are not valid for most of the use cases…)
Depending on what you want to do… your data may not be persisted on HDFS. There are use cases where your cluster is used for compute and not storage.
I’d say that spending time re-inventing the wheel can be a good thing. It would be a good idea for many to rethink their ingestion process so that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing that term from Dean Wampler. ;-)
Just saying. ;-)
-Mike
On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling. You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
Why do you want to reimplement something which is already there?
On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
Hi
Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
What do you think?
BestAyan
On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com> wrote:
Hi,
It depends on your use case using sqoop.What's it like?
// maropu
On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
Hi All
Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
--
Best Regards,
Ayan Guha
--
---
Takeshi Yamamuro
--
Best Regards,
Ayan Guha
--
Best Regards,
Ayan Guha
Re: Sqoop on Spark
Posted by Mich Talebzadeh <mi...@gmail.com>.
SAP Sybase IQ does that and I believe SAP Hana as well.
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 6 April 2016 at 23:49, Peyman Mohajerian <mo...@gmail.com> wrote:
> For some MPP relational stores (not operational) it maybe feasible to run
> Spark jobs and also have data locality. I know QueryGrid (Teradata) and
> PolyBase (microsoft) use data locality to move data between their MPP and
> Hadoop.
> I would guess (have no idea) someone like IBM already is doing that for
> Spark, maybe a bit off topic!
>
> On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
>
>> Well I am not sure, but using a database as a storage, such as relational
>> databases or certain nosql databases (eg MongoDB) for Spark is generally a
>> bad idea - no data locality, it cannot handle real big data volumes for
>> compute and you may potentially overload an operational database.
>> And if your job fails for whatever reason (eg scheduling ) then you have
>> to pull everything out again. Sqoop and HDFS seems to me the more elegant
>> solution together with spark. These "assumption" on parallelism have to be
>> anyway made with any solution.
>> Of course you can always redo things, but why - what benefit do you
>> expect? A real big data platform has to support anyway many different tools
>> otherwise people doing analytics will be limited.
>>
>> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com>
>> wrote:
>>
>> I don’t think its necessarily a bad idea.
>>
>> Sqoop is an ugly tool and it requires you to make some assumptions as a
>> way to gain parallelism. (Not that most of the assumptions are not valid
>> for most of the use cases…)
>>
>> Depending on what you want to do… your data may not be persisted on
>> HDFS. There are use cases where your cluster is used for compute and not
>> storage.
>>
>> I’d say that spending time re-inventing the wheel can be a good thing.
>> It would be a good idea for many to rethink their ingestion process so
>> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
>> that term from Dean Wampler. ;-)
>>
>> Just saying. ;-)
>>
>> -Mike
>>
>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>> I do not think you can be more resource efficient. In the end you have to
>> store the data anyway on HDFS . You have a lot of development effort for
>> doing something like sqoop. Especially with error handling.
>> You may create a ticket with the Sqoop guys to support Spark as an
>> execution engine and maybe it is less effort to plug it in there.
>> Maybe if your cluster is loaded then you may want to add more machines or
>> improve the existing programs.
>>
>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>
>> One of the reason in my mind is to avoid Map-Reduce application
>> completely during ingestion, if possible. Also, I can then use Spark stand
>> alone cluster to ingest, even if my hadoop cluster is heavily loaded. What
>> you guys think?
>>
>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>>> Why do you want to reimplement something which is already there?
>>>
>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>
>>> Hi
>>>
>>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>>> index and incremental only) and add data to existing Hive tables. Also, it
>>> would be good to have an option to create Hive table, driven by job
>>> specific configuration.
>>>
>>> What do you think?
>>>
>>> Best
>>> Ayan
>>>
>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> It depends on your use case using sqoop.
>>>> What's it like?
>>>>
>>>> // maropu
>>>>
>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>
>>>>> Hi All
>>>>>
>>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>>> sqoop does? Any existing project done in similar lines?
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> ---
>>>> Takeshi Yamamuro
>>>>
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>>
>
Re: Sqoop on Spark
Posted by Peyman Mohajerian <mo...@gmail.com>.
For some MPP relational stores (not operational) it maybe feasible to run
Spark jobs and also have data locality. I know QueryGrid (Teradata) and
PolyBase (microsoft) use data locality to move data between their MPP and
Hadoop.
I would guess (have no idea) someone like IBM already is doing that for
Spark, maybe a bit off topic!
On Wed, Apr 6, 2016 at 3:29 PM, Jörn Franke <jo...@gmail.com> wrote:
> Well I am not sure, but using a database as a storage, such as relational
> databases or certain nosql databases (eg MongoDB) for Spark is generally a
> bad idea - no data locality, it cannot handle real big data volumes for
> compute and you may potentially overload an operational database.
> And if your job fails for whatever reason (eg scheduling ) then you have
> to pull everything out again. Sqoop and HDFS seems to me the more elegant
> solution together with spark. These "assumption" on parallelism have to be
> anyway made with any solution.
> Of course you can always redo things, but why - what benefit do you
> expect? A real big data platform has to support anyway many different tools
> otherwise people doing analytics will be limited.
>
> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
>
> I don’t think its necessarily a bad idea.
>
> Sqoop is an ugly tool and it requires you to make some assumptions as a
> way to gain parallelism. (Not that most of the assumptions are not valid
> for most of the use cases…)
>
> Depending on what you want to do… your data may not be persisted on HDFS.
> There are use cases where your cluster is used for compute and not storage.
>
> I’d say that spending time re-inventing the wheel can be a good thing.
> It would be a good idea for many to rethink their ingestion process so
> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
> that term from Dean Wampler. ;-)
>
> Just saying. ;-)
>
> -Mike
>
> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> I do not think you can be more resource efficient. In the end you have to
> store the data anyway on HDFS . You have a lot of development effort for
> doing something like sqoop. Especially with error handling.
> You may create a ticket with the Sqoop guys to support Spark as an
> execution engine and maybe it is less effort to plug it in there.
> Maybe if your cluster is loaded then you may want to add more machines or
> improve the existing programs.
>
> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>
> One of the reason in my mind is to avoid Map-Reduce application completely
> during ingestion, if possible. Also, I can then use Spark stand alone
> cluster to ingest, even if my hadoop cluster is heavily loaded. What you
> guys think?
>
> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>
>> Why do you want to reimplement something which is already there?
>>
>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>
>> Hi
>>
>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>> index and incremental only) and add data to existing Hive tables. Also, it
>> would be good to have an option to create Hive table, driven by job
>> specific configuration.
>>
>> What do you think?
>>
>> Best
>> Ayan
>>
>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> It depends on your use case using sqoop.
>>> What's it like?
>>>
>>> // maropu
>>>
>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>
>>>> Hi All
>>>>
>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>> sqoop does? Any existing project done in similar lines?
>>>>
>>>> --
>>>> Best Regards,
>>>> Ayan Guha
>>>>
>>>
>>>
>>>
>>> --
>>> ---
>>> Takeshi Yamamuro
>>>
>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
Re: Sqoop on Spark
Posted by Mich Talebzadeh <mi...@gmail.com>.
Sorry are you referring to Hive as a relational Data Warehouse in this
scenario. The assumption here is that data is coming from a relational
database (Oracle) so IMO the best storage for it in Big Data World is
another DW adaptive to SQL. Spark is a powerful query tool and together
with Hive as a backbone of storage provides a powerful framework to
anything. the performance is pretty fast indeed much faster compared to
MapR that Sqoop uses be default.
Anyway you are not confined to a table in Hive. You can take that data from
JDBC and do whatever is needed. There is no constraint in here.
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 6 April 2016 at 23:29, Jörn Franke <jo...@gmail.com> wrote:
> Well I am not sure, but using a database as a storage, such as relational
> databases or certain nosql databases (eg MongoDB) for Spark is generally a
> bad idea - no data locality, it cannot handle real big data volumes for
> compute and you may potentially overload an operational database.
> And if your job fails for whatever reason (eg scheduling ) then you have
> to pull everything out again. Sqoop and HDFS seems to me the more elegant
> solution together with spark. These "assumption" on parallelism have to be
> anyway made with any solution.
> Of course you can always redo things, but why - what benefit do you
> expect? A real big data platform has to support anyway many different tools
> otherwise people doing analytics will be limited.
>
> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
>
> I don’t think its necessarily a bad idea.
>
> Sqoop is an ugly tool and it requires you to make some assumptions as a
> way to gain parallelism. (Not that most of the assumptions are not valid
> for most of the use cases…)
>
> Depending on what you want to do… your data may not be persisted on HDFS.
> There are use cases where your cluster is used for compute and not storage.
>
> I’d say that spending time re-inventing the wheel can be a good thing.
> It would be a good idea for many to rethink their ingestion process so
> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
> that term from Dean Wampler. ;-)
>
> Just saying. ;-)
>
> -Mike
>
> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> I do not think you can be more resource efficient. In the end you have to
> store the data anyway on HDFS . You have a lot of development effort for
> doing something like sqoop. Especially with error handling.
> You may create a ticket with the Sqoop guys to support Spark as an
> execution engine and maybe it is less effort to plug it in there.
> Maybe if your cluster is loaded then you may want to add more machines or
> improve the existing programs.
>
> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>
> One of the reason in my mind is to avoid Map-Reduce application completely
> during ingestion, if possible. Also, I can then use Spark stand alone
> cluster to ingest, even if my hadoop cluster is heavily loaded. What you
> guys think?
>
> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>
>> Why do you want to reimplement something which is already there?
>>
>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>
>> Hi
>>
>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>> index and incremental only) and add data to existing Hive tables. Also, it
>> would be good to have an option to create Hive table, driven by job
>> specific configuration.
>>
>> What do you think?
>>
>> Best
>> Ayan
>>
>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> It depends on your use case using sqoop.
>>> What's it like?
>>>
>>> // maropu
>>>
>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>
>>>> Hi All
>>>>
>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>> sqoop does? Any existing project done in similar lines?
>>>>
>>>> --
>>>> Best Regards,
>>>> Ayan Guha
>>>>
>>>
>>>
>>>
>>> --
>>> ---
>>> Takeshi Yamamuro
>>>
>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
Re: Sqoop on Spark
Posted by Jörn Franke <jo...@gmail.com>.
Well I am not sure, but using a database as a storage, such as relational databases or certain nosql databases (eg MongoDB) for Spark is generally a bad idea - no data locality, it cannot handle real big data volumes for compute and you may potentially overload an operational database.
And if your job fails for whatever reason (eg scheduling ) then you have to pull everything out again. Sqoop and HDFS seems to me the more elegant solution together with spark. These "assumption" on parallelism have to be anyway made with any solution.
Of course you can always redo things, but why - what benefit do you expect? A real big data platform has to support anyway many different tools otherwise people doing analytics will be limited.
> On 06 Apr 2016, at 20:05, Michael Segel <ms...@hotmail.com> wrote:
>
> I don’t think its necessarily a bad idea.
>
> Sqoop is an ugly tool and it requires you to make some assumptions as a way to gain parallelism. (Not that most of the assumptions are not valid for most of the use cases…)
>
> Depending on what you want to do… your data may not be persisted on HDFS. There are use cases where your cluster is used for compute and not storage.
>
> I’d say that spending time re-inventing the wheel can be a good thing.
> It would be a good idea for many to rethink their ingestion process so that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing that term from Dean Wampler. ;-)
>
> Just saying. ;-)
>
> -Mike
>
>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>> I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling.
>> You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.
>> Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
>>
>>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>>
>>> One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
>>>
>>>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>>>> Why do you want to reimplement something which is already there?
>>>>
>>>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>>>
>>>>> Hi
>>>>>
>>>>> Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
>>>>>
>>>>> What do you think?
>>>>>
>>>>> Best
>>>>> Ayan
>>>>>
>>>>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com> wrote:
>>>>>> Hi,
>>>>>>
>>>>>> It depends on your use case using sqoop.
>>>>>> What's it like?
>>>>>>
>>>>>> // maropu
>>>>>>
>>>>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>>>> Hi All
>>>>>>>
>>>>>>> Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
>>>>>>>
>>>>>>> --
>>>>>>> Best Regards,
>>>>>>> Ayan Guha
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> ---
>>>>>> Takeshi Yamamuro
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>
Re: Sqoop on Spark
Posted by Mich Talebzadeh <mi...@gmail.com>.
I just created an example of how to use JDBC to get Oracle data into Hive
table using Sqoop. Please see thread below
How to use Spark JDBC to read from RDBMS table, create Hive ORC table and
save RDBMS data in it
HTH
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 6 April 2016 at 22:41, Ranadip Chatterjee <ra...@gmail.com> wrote:
> I know of projects that have done this but have never seen any advantage
> of "using spark to do what sqoop does" - at least in a yarn cluster. Both
> frameworks will have similar overheads of getting the containers allocated
> by yarn and creating new jvms to do the work. Probably spark will have a
> slightly higher overhead due to creation of RDD before writing the data to
> hdfs - something that the sqoop mapper need not do. (So what am I
> overlooking here?)
>
> In cases where a data pipeline is being built with the sqooped data being
> the only trigger, there is a justification for using spark instead of sqoop
> to short circuit the data directly into the transformation pipeline.
>
> Regards
> Ranadip
> On 6 Apr 2016 7:05 p.m., "Michael Segel" <ms...@hotmail.com>
> wrote:
>
>> I don’t think its necessarily a bad idea.
>>
>> Sqoop is an ugly tool and it requires you to make some assumptions as a
>> way to gain parallelism. (Not that most of the assumptions are not valid
>> for most of the use cases…)
>>
>> Depending on what you want to do… your data may not be persisted on
>> HDFS. There are use cases where your cluster is used for compute and not
>> storage.
>>
>> I’d say that spending time re-inventing the wheel can be a good thing.
>> It would be a good idea for many to rethink their ingestion process so
>> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
>> that term from Dean Wampler. ;-)
>>
>> Just saying. ;-)
>>
>> -Mike
>>
>> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>> I do not think you can be more resource efficient. In the end you have to
>> store the data anyway on HDFS . You have a lot of development effort for
>> doing something like sqoop. Especially with error handling.
>> You may create a ticket with the Sqoop guys to support Spark as an
>> execution engine and maybe it is less effort to plug it in there.
>> Maybe if your cluster is loaded then you may want to add more machines or
>> improve the existing programs.
>>
>> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>>
>> One of the reason in my mind is to avoid Map-Reduce application
>> completely during ingestion, if possible. Also, I can then use Spark stand
>> alone cluster to ingest, even if my hadoop cluster is heavily loaded. What
>> you guys think?
>>
>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>>
>>> Why do you want to reimplement something which is already there?
>>>
>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>
>>> Hi
>>>
>>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>>> index and incremental only) and add data to existing Hive tables. Also, it
>>> would be good to have an option to create Hive table, driven by job
>>> specific configuration.
>>>
>>> What do you think?
>>>
>>> Best
>>> Ayan
>>>
>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> It depends on your use case using sqoop.
>>>> What's it like?
>>>>
>>>> // maropu
>>>>
>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>
>>>>> Hi All
>>>>>
>>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>>> sqoop does? Any existing project done in similar lines?
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> ---
>>>> Takeshi Yamamuro
>>>>
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>>
Re: Sqoop on Spark
Posted by Ranadip Chatterjee <ra...@gmail.com>.
I know of projects that have done this but have never seen any advantage of
"using spark to do what sqoop does" - at least in a yarn cluster. Both
frameworks will have similar overheads of getting the containers allocated
by yarn and creating new jvms to do the work. Probably spark will have a
slightly higher overhead due to creation of RDD before writing the data to
hdfs - something that the sqoop mapper need not do. (So what am I
overlooking here?)
In cases where a data pipeline is being built with the sqooped data being
the only trigger, there is a justification for using spark instead of sqoop
to short circuit the data directly into the transformation pipeline.
Regards
Ranadip
On 6 Apr 2016 7:05 p.m., "Michael Segel" <ms...@hotmail.com> wrote:
> I don’t think its necessarily a bad idea.
>
> Sqoop is an ugly tool and it requires you to make some assumptions as a
> way to gain parallelism. (Not that most of the assumptions are not valid
> for most of the use cases…)
>
> Depending on what you want to do… your data may not be persisted on HDFS.
> There are use cases where your cluster is used for compute and not storage.
>
> I’d say that spending time re-inventing the wheel can be a good thing.
> It would be a good idea for many to rethink their ingestion process so
> that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing
> that term from Dean Wampler. ;-)
>
> Just saying. ;-)
>
> -Mike
>
> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> I do not think you can be more resource efficient. In the end you have to
> store the data anyway on HDFS . You have a lot of development effort for
> doing something like sqoop. Especially with error handling.
> You may create a ticket with the Sqoop guys to support Spark as an
> execution engine and maybe it is less effort to plug it in there.
> Maybe if your cluster is loaded then you may want to add more machines or
> improve the existing programs.
>
> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>
> One of the reason in my mind is to avoid Map-Reduce application completely
> during ingestion, if possible. Also, I can then use Spark stand alone
> cluster to ingest, even if my hadoop cluster is heavily loaded. What you
> guys think?
>
> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>
>> Why do you want to reimplement something which is already there?
>>
>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>
>> Hi
>>
>> Thanks for reply. My use case is query ~40 tables from Oracle (using
>> index and incremental only) and add data to existing Hive tables. Also, it
>> would be good to have an option to create Hive table, driven by job
>> specific configuration.
>>
>> What do you think?
>>
>> Best
>> Ayan
>>
>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> It depends on your use case using sqoop.
>>> What's it like?
>>>
>>> // maropu
>>>
>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>
>>>> Hi All
>>>>
>>>> Asking opinion: is it possible/advisable to use spark to replace what
>>>> sqoop does? Any existing project done in similar lines?
>>>>
>>>> --
>>>> Best Regards,
>>>> Ayan Guha
>>>>
>>>
>>>
>>>
>>> --
>>> ---
>>> Takeshi Yamamuro
>>>
>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
>
Re: Sqoop on Spark
Posted by Michael Segel <ms...@hotmail.com>.
I don’t think its necessarily a bad idea.
Sqoop is an ugly tool and it requires you to make some assumptions as a way to gain parallelism. (Not that most of the assumptions are not valid for most of the use cases…)
Depending on what you want to do… your data may not be persisted on HDFS. There are use cases where your cluster is used for compute and not storage.
I’d say that spending time re-inventing the wheel can be a good thing.
It would be a good idea for many to rethink their ingestion process so that they can have a nice ‘data lake’ and not a ‘data sewer’. (Stealing that term from Dean Wampler. ;-)
Just saying. ;-)
-Mike
> On Apr 5, 2016, at 10:44 PM, Jörn Franke <jo...@gmail.com> wrote:
>
> I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling.
> You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.
> Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
>
> On 06 Apr 2016, at 07:33, ayan guha <guha.ayan@gmail.com <ma...@gmail.com>> wrote:
>
>> One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
>>
>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jornfranke@gmail.com <ma...@gmail.com>> wrote:
>> Why do you want to reimplement something which is already there?
>>
>> On 06 Apr 2016, at 06:47, ayan guha <guha.ayan@gmail.com <ma...@gmail.com>> wrote:
>>
>>> Hi
>>>
>>> Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
>>>
>>> What do you think?
>>>
>>> Best
>>> Ayan
>>>
>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <linguin.m.s@gmail.com <ma...@gmail.com>> wrote:
>>> Hi,
>>>
>>> It depends on your use case using sqoop.
>>> What's it like?
>>>
>>> // maropu
>>>
>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <guha.ayan@gmail.com <ma...@gmail.com>> wrote:
>>> Hi All
>>>
>>> Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>>
>>>
>>> --
>>> ---
>>> Takeshi Yamamuro
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>
>>
>>
>> --
>> Best Regards,
>> Ayan Guha
Re: Sqoop on Spark
Posted by Jörn Franke <jo...@gmail.com>.
I do not think you can be more resource efficient. In the end you have to store the data anyway on HDFS . You have a lot of development effort for doing something like sqoop. Especially with error handling.
You may create a ticket with the Sqoop guys to support Spark as an execution engine and maybe it is less effort to plug it in there.
Maybe if your cluster is loaded then you may want to add more machines or improve the existing programs.
> On 06 Apr 2016, at 07:33, ayan guha <gu...@gmail.com> wrote:
>
> One of the reason in my mind is to avoid Map-Reduce application completely during ingestion, if possible. Also, I can then use Spark stand alone cluster to ingest, even if my hadoop cluster is heavily loaded. What you guys think?
>
>> On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
>> Why do you want to reimplement something which is already there?
>>
>>> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>>>
>>> Hi
>>>
>>> Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
>>>
>>> What do you think?
>>>
>>> Best
>>> Ayan
>>>
>>>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com> wrote:
>>>> Hi,
>>>>
>>>> It depends on your use case using sqoop.
>>>> What's it like?
>>>>
>>>> // maropu
>>>>
>>>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>>>> Hi All
>>>>>
>>>>> Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
>>>>>
>>>>> --
>>>>> Best Regards,
>>>>> Ayan Guha
>>>>
>>>>
>>>>
>>>> --
>>>> ---
>>>> Takeshi Yamamuro
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>
>
>
> --
> Best Regards,
> Ayan Guha
Re: Sqoop on Spark
Posted by ayan guha <gu...@gmail.com>.
One of the reason in my mind is to avoid Map-Reduce application completely
during ingestion, if possible. Also, I can then use Spark stand alone
cluster to ingest, even if my hadoop cluster is heavily loaded. What you
guys think?
On Wed, Apr 6, 2016 at 3:13 PM, Jörn Franke <jo...@gmail.com> wrote:
> Why do you want to reimplement something which is already there?
>
> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>
> Hi
>
> Thanks for reply. My use case is query ~40 tables from Oracle (using index
> and incremental only) and add data to existing Hive tables. Also, it would
> be good to have an option to create Hive table, driven by job specific
> configuration.
>
> What do you think?
>
> Best
> Ayan
>
> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
> wrote:
>
>> Hi,
>>
>> It depends on your use case using sqoop.
>> What's it like?
>>
>> // maropu
>>
>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>
>>> Hi All
>>>
>>> Asking opinion: is it possible/advisable to use spark to replace what
>>> sqoop does? Any existing project done in similar lines?
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>
>>
>>
>> --
>> ---
>> Takeshi Yamamuro
>>
>
>
>
> --
> Best Regards,
> Ayan Guha
>
>
--
Best Regards,
Ayan Guha
Re: Sqoop on Spark
Posted by Jörn Franke <jo...@gmail.com>.
Why do you want to reimplement something which is already there?
> On 06 Apr 2016, at 06:47, ayan guha <gu...@gmail.com> wrote:
>
> Hi
>
> Thanks for reply. My use case is query ~40 tables from Oracle (using index and incremental only) and add data to existing Hive tables. Also, it would be good to have an option to create Hive table, driven by job specific configuration.
>
> What do you think?
>
> Best
> Ayan
>
>> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com> wrote:
>> Hi,
>>
>> It depends on your use case using sqoop.
>> What's it like?
>>
>> // maropu
>>
>>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>> Hi All
>>>
>>> Asking opinion: is it possible/advisable to use spark to replace what sqoop does? Any existing project done in similar lines?
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>
>>
>>
>> --
>> ---
>> Takeshi Yamamuro
>
>
>
> --
> Best Regards,
> Ayan Guha
Re: Sqoop on Spark
Posted by Takeshi Yamamuro <li...@gmail.com>.
It worths trying it though, you might hit many technical issues;
for example, I'm not sure that spark completely supports all the Oracle
dialects (e.g., types) in JDBCRelation.
Also, I'm not sure spark can handle queries that you have used in the use
case.
At least, you just have to check if spark supports queries and types.
// maropu
On Wed, Apr 6, 2016 at 1:47 PM, ayan guha <gu...@gmail.com> wrote:
> Hi
>
> Thanks for reply. My use case is query ~40 tables from Oracle (using index
> and incremental only) and add data to existing Hive tables. Also, it would
> be good to have an option to create Hive table, driven by job specific
> configuration.
>
> What do you think?
>
> Best
> Ayan
>
> On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
> wrote:
>
>> Hi,
>>
>> It depends on your use case using sqoop.
>> What's it like?
>>
>> // maropu
>>
>> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>>
>>> Hi All
>>>
>>> Asking opinion: is it possible/advisable to use spark to replace what
>>> sqoop does? Any existing project done in similar lines?
>>>
>>> --
>>> Best Regards,
>>> Ayan Guha
>>>
>>
>>
>>
>> --
>> ---
>> Takeshi Yamamuro
>>
>
>
>
> --
> Best Regards,
> Ayan Guha
>
--
---
Takeshi Yamamuro
Re: Sqoop on Spark
Posted by ayan guha <gu...@gmail.com>.
Hi
Thanks for reply. My use case is query ~40 tables from Oracle (using index
and incremental only) and add data to existing Hive tables. Also, it would
be good to have an option to create Hive table, driven by job specific
configuration.
What do you think?
Best
Ayan
On Wed, Apr 6, 2016 at 2:30 PM, Takeshi Yamamuro <li...@gmail.com>
wrote:
> Hi,
>
> It depends on your use case using sqoop.
> What's it like?
>
> // maropu
>
> On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
>
>> Hi All
>>
>> Asking opinion: is it possible/advisable to use spark to replace what
>> sqoop does? Any existing project done in similar lines?
>>
>> --
>> Best Regards,
>> Ayan Guha
>>
>
>
>
> --
> ---
> Takeshi Yamamuro
>
--
Best Regards,
Ayan Guha
Re: Sqoop on Spark
Posted by Takeshi Yamamuro <li...@gmail.com>.
Hi,
It depends on your use case using sqoop.
What's it like?
// maropu
On Wed, Apr 6, 2016 at 1:26 PM, ayan guha <gu...@gmail.com> wrote:
> Hi All
>
> Asking opinion: is it possible/advisable to use spark to replace what
> sqoop does? Any existing project done in similar lines?
>
> --
> Best Regards,
> Ayan Guha
>
--
---
Takeshi Yamamuro