Re: DB2 DDL to Hive DDL conversion **Need Help**

[Dmitry Tolpeko]

Abhi,

The approach is quite simple and straightforward:

1) HPL/SQL is available since Hive 2.0 released a few days ago, so if you
are using a previous Hive version (or Impala, Spark SQL, Presto etc) you
can take the tool at http://hplsql.org/download

2) Follow http://hplsql.org/start to set it up

3) Execute in a similar way as you execute Hive CLI but use hplsql:


hplsql -f db2_ddl_script.sql

It will parse your DDL and execute statements one by one. Of course I do
not expect it will seamlessly run from the first shot, may be I need to
extend the parser to deal with DB2 issues, so please contact me directly at
dmtolp...@gmail.com about issues. I will try to help.

Thanks,

Dmitry



On Fri, Feb 19, 2016 at 8:35 PM, abhishek  wrote:

> Hi Dmitry,
>
> You understood my requirements. Our requirement is to create tables in
> hive using DB2 DDL script. Means converting DB2 DDL into hive DDL.
> I think HPL/SQL tool will work. Can you please share the details and user
> guide of the tool.
>
> Abhi
>
> Sent from my iPhone
>
> On Feb 19, 2016, at 12:55 AM, Mohit Durgapal 
> wrote:
>
> Have you considered using Sqoop? If not, then please have a look at the
> following links:
>
>
> https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html#_importing_data_into_hive
>
> http://stackoverflow.com/questions/17064144/how-do-i-use-sqoop-for-importing-data-from-a-relational-db-to-sandbox-hive
>
> On Fri, Feb 19, 2016 at 1:04 PM, Abhishek Singh 
> wrote:
>
>> Hi,
>>
>> We have almost 1000 DB2 RDBMS tables and for those we have the DDLs
>> (script) available.
>> We are looking for a way to convert all these DB2 DDLs into Hive DDL
>> without writing Hive DDL statements for each and every table. Means, is
>> there an
>> automated tool available to do this?  If not, then can someone please
>> guide me if we have to write code then what exactly needs to be done step
>> by step. or any simple way to avoid lots of manual work.
>>
>> Thanks
>>
>> Abhi
>>
>
>

Re: DB2 DDL to Hive DDL conversion **Need Help**

[abhishek]

Hi Dmitry, 

You understood my requirements. Our requirement is to create tables in hive 
using DB2 DDL script. Means converting DB2 DDL into hive DDL. 
I think HPL/SQL tool will work. Can you please share the details and user guide 
of the tool. 

Abhi 

Sent from my iPhone

> On Feb 19, 2016, at 12:55 AM, Mohit Durgapal  wrote:
> 
> Have you considered using Sqoop? If not, then please have a look at the 
> following links:
> 
> https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html#_importing_data_into_hive
> http://stackoverflow.com/questions/17064144/how-do-i-use-sqoop-for-importing-data-from-a-relational-db-to-sandbox-hive
> 
>> On Fri, Feb 19, 2016 at 1:04 PM, Abhishek Singh  
>> wrote:
>> Hi, 
>> 
>> We have almost 1000 DB2 RDBMS tables and for those we have the DDLs (script) 
>> available. 
>> We are looking for a way to convert all these DB2 DDLs into Hive DDL
>> without writing Hive DDL statements for each and every table. Means, is 
>> there an 
>> automated tool available to do this?  If not, then can someone please 
>> guide me if we have to write code then what exactly needs to be done step
>> by step. or any simple way to avoid lots of manual work. 
>> 
>> Thanks 
>> 
>> Abhi
> 

Re: DB2 DDL to Hive DDL conversion **Need Help**

[Abhishek Singh]

Chandeep,

My target tables are existing DB2 tables and we have to move data from Hive
staging tables to target DB2 tables using informatica BDE.
Means, we are looking for one to one mapping between hive and DB2 metadata.
As DB2 tables are existing we are looking for a reverse
re engineering to have the hive DDL created using existing DB2 DDL to avoid
lots of manual work.

Abhi

On Fri, Feb 19, 2016 at 9:59 AM, Chandeep Singh  wrote:

> Tables can be imported directly into Hive using Sqoop with the following
> flag --hive-import.
>
> Once you have the tables in Hive you can get their create DDL scripts
> using SHOW CREATE TABLE ;
>
> On Feb 19, 2016, at 5:31 PM, Mohit Durgapal 
> wrote:
>
> If he can import the scripts in db2 rdbms then it can create equivalent
> hive ddl script using sqoop.
>
> On Friday 19 February 2016, Dmitry Tolpeko  wrote:
>
>> Abhi needs to convert SQL scripts so I am afraid Sqoop will not help.
>>
>> Abhi, do you need to get equivalent Hive scripts or creating tables in
>> Hive will be enough (without having scripts)? The new HPL/SQL tool is
>> designed to execute existing DDL (created for any database), convert on the
>> fly and create tables in Hive.
>>
>> Will it be a good solution for you? I tested HPL/SQL using Oracle, SQL
>> Server and some DB2 DDL. If there are issues I can extend the tool, contact
>> me.
>>
>> Dmitry
>>
>>
>> On Fri, Feb 19, 2016 at 11:55 AM, Mohit Durgapal > > wrote:
>>
>>> Have you considered using Sqoop? If not, then please have a look at the
>>> following links:
>>>
>>>
>>> https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html#_importing_data_into_hive
>>>
>>> http://stackoverflow.com/questions/17064144/how-do-i-use-sqoop-for-importing-data-from-a-relational-db-to-sandbox-hive
>>>
>>> On Fri, Feb 19, 2016 at 1:04 PM, Abhishek Singh 
>>> wrote:
>>>
 Hi,

 We have almost 1000 DB2 RDBMS tables and for those we have the DDLs
 (script) available.
 We are looking for a way to convert all these DB2 DDLs into Hive DDL
 without writing Hive DDL statements for each and every table. Means, is
 there an
 automated tool available to do this?  If not, then can someone please
 guide me if we have to write code then what exactly needs to be done
 step
 by step. or any simple way to avoid lots of manual work.

 Thanks

 Abhi

>>>
>>>
>>
>

Re: DB2 DDL to Hive DDL conversion **Need Help**

[Chandeep Singh]

Tables can be imported directly into Hive using Sqoop with the following flag 
--hive-import.

Once you have the tables in Hive you can get their create DDL scripts using 
SHOW CREATE TABLE ;

> On Feb 19, 2016, at 5:31 PM, Mohit Durgapal  wrote:
> 
> If he can import the scripts in db2 rdbms then it can create equivalent hive 
> ddl script using sqoop.
> 
> On Friday 19 February 2016, Dmitry Tolpeko  > wrote:
> Abhi needs to convert SQL scripts so I am afraid Sqoop will not help.
> 
> Abhi, do you need to get equivalent Hive scripts or creating tables in Hive 
> will be enough (without having scripts)? The new HPL/SQL tool is designed to 
> execute existing DDL (created for any database), convert on the fly and 
> create tables in Hive. 
> 
> Will it be a good solution for you? I tested HPL/SQL using Oracle, SQL Server 
> and some DB2 DDL. If there are issues I can extend the tool, contact me.
> 
> Dmitry
> 
> 
> On Fri, Feb 19, 2016 at 11:55 AM, Mohit Durgapal  > wrote:
> Have you considered using Sqoop? If not, then please have a look at the 
> following links:
> 
> https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html#_importing_data_into_hive
>  
> 
> http://stackoverflow.com/questions/17064144/how-do-i-use-sqoop-for-importing-data-from-a-relational-db-to-sandbox-hive
>  
> 
> 
> On Fri, Feb 19, 2016 at 1:04 PM, Abhishek Singh  > wrote:
> Hi, 
> 
> We have almost 1000 DB2 RDBMS tables and for those we have the DDLs (script) 
> available. 
> We are looking for a way to convert all these DB2 DDLs into Hive DDL
> without writing Hive DDL statements for each and every table. Means, is there 
> an 
> automated tool available to do this?  If not, then can someone please 
> guide me if we have to write code then what exactly needs to be done step
> by step. or any simple way to avoid lots of manual work. 
> 
> Thanks 
> 
> Abhi
> 
> 

Re: DB2 DDL to Hive DDL conversion **Need Help**

[Mohit Durgapal]

If he can import the scripts in db2 rdbms then it can create equivalent
hive ddl script using sqoop.

On Friday 19 February 2016, Dmitry Tolpeko  wrote:

> Abhi needs to convert SQL scripts so I am afraid Sqoop will not help.
>
> Abhi, do you need to get equivalent Hive scripts or creating tables in
> Hive will be enough (without having scripts)? The new HPL/SQL tool is
> designed to execute existing DDL (created for any database), convert on the
> fly and create tables in Hive.
>
> Will it be a good solution for you? I tested HPL/SQL using Oracle, SQL
> Server and some DB2 DDL. If there are issues I can extend the tool, contact
> me.
>
> Dmitry
>
>
> On Fri, Feb 19, 2016 at 11:55 AM, Mohit Durgapal  > wrote:
>
>> Have you considered using Sqoop? If not, then please have a look at the
>> following links:
>>
>>
>> https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html#_importing_data_into_hive
>>
>> http://stackoverflow.com/questions/17064144/how-do-i-use-sqoop-for-importing-data-from-a-relational-db-to-sandbox-hive
>>
>> On Fri, Feb 19, 2016 at 1:04 PM, Abhishek Singh > > wrote:
>>
>>> Hi,
>>>
>>> We have almost 1000 DB2 RDBMS tables and for those we have the DDLs
>>> (script) available.
>>> We are looking for a way to convert all these DB2 DDLs into Hive DDL
>>> without writing Hive DDL statements for each and every table. Means, is
>>> there an
>>> automated tool available to do this?  If not, then can someone please
>>> guide me if we have to write code then what exactly needs to be done step
>>> by step. or any simple way to avoid lots of manual work.
>>>
>>> Thanks
>>>
>>> Abhi
>>>
>>
>>
>

Re: DB2 DDL to Hive DDL conversion **Need Help**

[Dmitry Tolpeko]

Abhi needs to convert SQL scripts so I am afraid Sqoop will not help.

Abhi, do you need to get equivalent Hive scripts or creating tables in Hive
will be enough (without having scripts)? The new HPL/SQL tool is designed
to execute existing DDL (created for any database), convert on the fly and
create tables in Hive.

Will it be a good solution for you? I tested HPL/SQL using Oracle, SQL
Server and some DB2 DDL. If there are issues I can extend the tool, contact
me.

Dmitry


On Fri, Feb 19, 2016 at 11:55 AM, Mohit Durgapal 
wrote:

> Have you considered using Sqoop? If not, then please have a look at the
> following links:
>
>
> https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html#_importing_data_into_hive
>
> http://stackoverflow.com/questions/17064144/how-do-i-use-sqoop-for-importing-data-from-a-relational-db-to-sandbox-hive
>
> On Fri, Feb 19, 2016 at 1:04 PM, Abhishek Singh 
> wrote:
>
>> Hi,
>>
>> We have almost 1000 DB2 RDBMS tables and for those we have the DDLs
>> (script) available.
>> We are looking for a way to convert all these DB2 DDLs into Hive DDL
>> without writing Hive DDL statements for each and every table. Means, is
>> there an
>> automated tool available to do this?  If not, then can someone please
>> guide me if we have to write code then what exactly needs to be done step
>> by step. or any simple way to avoid lots of manual work.
>>
>> Thanks
>>
>> Abhi
>>
>
>

Re: DB2 DDL to Hive DDL conversion **Need Help**

[Mohit Durgapal]

Have you considered using Sqoop? If not, then please have a look at the
following links:

https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html#_importing_data_into_hive
http://stackoverflow.com/questions/17064144/how-do-i-use-sqoop-for-importing-data-from-a-relational-db-to-sandbox-hive

On Fri, Feb 19, 2016 at 1:04 PM, Abhishek Singh 
wrote:

> Hi,
>
> We have almost 1000 DB2 RDBMS tables and for those we have the DDLs
> (script) available.
> We are looking for a way to convert all these DB2 DDLs into Hive DDL
> without writing Hive DDL statements for each and every table. Means, is
> there an
> automated tool available to do this?  If not, then can someone please
> guide me if we have to write code then what exactly needs to be done step
> by step. or any simple way to avoid lots of manual work.
>
> Thanks
>
> Abhi
>

DB2 DDL to Hive DDL conversion **Need Help**

[Abhishek Singh]

Hi,

We have almost 1000 DB2 RDBMS tables and for those we have the DDLs
(script) available.
We are looking for a way to convert all these DB2 DDLs into Hive DDL
without writing Hive DDL statements for each and every table. Means, is
there an
automated tool available to do this?  If not, then can someone please
guide me if we have to write code then what exactly needs to be done step
by step. or any simple way to avoid lots of manual work.

Thanks

Abhi