How to set default value for a certain field

[mahender bigdata]

Hi,

is there Ideal solution in Hive to specify default values at schema 
level. Currently we are using *COALESCE *operator in converting null 
values to default value, this would require reading entire table. But it 
would be nice if some one has different approach of setting default 
values for Columns @ Schema level.


Thanks in advance

Re: TBLPROPERTIES K/V Comprehensive List

[Edward Capriolo]

There is no comprehensive list, each serde could use the parameters for
whatever it desires while other serde's use none at all.

On Fri, Feb 19, 2016 at 3:23 PM, mahender bigdata <
mahender.bigd...@outlook.com> wrote:

> +1, Any information available ?
>
> On 2/10/2016 1:26 AM, Mathan Rajendran wrote:
>
>> Hi ,
>>
>> Is there any place where I can see a list of Key/Value Pairs used in Hive
>> while creating a Table.
>>
>> I went through the code and find the java doc
>> hive_metastoreConstants.java is having few constants list but not the
>> complete list.
>>
>>
>> Eg. Compression like orc.compression and other properties are missing.
>>
>>
>> Regards,
>> Madhan
>>
>
>

Re: TBLPROPERTIES K/V Comprehensive List

[mahender bigdata]

+1, Any information available ?

On 2/10/2016 1:26 AM, Mathan Rajendran wrote:

Hi ,

Is there any place where I can see a list of Key/Value Pairs used in 
Hive while creating a Table.


I went through the code and find the java 
doc hive_metastoreConstants.java is having few constants list but not 
the complete list.



Eg. Compression like orc.compression and other properties are missing.


Regards,
Madhan

Re: Hive query on Tez slower than on MR (fails in some cases) ..

[Gopal Vijayaraghavan]

Hi,

> Here's the Tez DAG swimlane. Haven't gotten vertex.py to work.. will
>send that too soon.

Pretty clear that the map-side is fine - splitting sort buffers isn't
bothering this at all.

We want to over-partition Reducer 7 and possibly have all of them pick the
total # of reducers dynamically

set hive.exec.parallel=false; -- bad idea on Tez

set hive.tez.auto.reducer.parallelism=true; -- decide on total # of
reducers dynamically
set hive.tez.min.partition.factor=0.1;

set hive.tez.max.partition.factor=10;

set tez.shuffle-vertex-manager.min-src-fraction=0.9; -- slow start min
(reducer counts are picked at this point)
set tez.shuffle-vertex-manager.max-src-fraction=0.99;

set tez.runtime.report.partition.stats=true;

(experimental!! - I'm still testing this for machine failure tolerance)

set tez.runtime.pipelined-shuffle.enabled=true;


Cheers,
Gopal

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: External table returns no result.

[Gabriel Balan]

Hi 


It's not enough to make dirs in hdfs. You need to let the metastore know you're 
adding partitions. 
Try to Recover Partitions (MSCK REPAIR TABLE) . 




hth 
Gabriel Balan 
The statements and opinions expressed here are my own and do not necessarily 
represent those of Oracle Corporation. 

- Original Message - 
From: amrit.jan...@goibibo.com 
To: hue-u...@cloudera.org, user@hive.apache.org 
Sent: Friday, February 19, 2016 2:21:29 AM GMT -05:00 US/Canada Eastern 
Subject: External table returns no result. 



Hi, 

Trying to run queries over HDFS data using Hive external table. 


Created a table using the following syntax but select * from stats returns no 
result. 





CREATE EXTERNAL TABLE `stats`( 
`filename` string, 
`ts` string, 
`type` string, 
`module` string, 
`method` string, 
`line` string, 
`query` string, 
`qt` string, 
`num_results` string, 
`result_count` int, 
`search_time` string, 
`millis` float, 
`ip` string) 
PARTITIONED BY ( 
`years` bigint, 
`months` bigint, 
`days` bigint, 
`hours` int) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
STORED AS INPUTFORMAT 
'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
LOCATION 
'hdfs:// nmlgo1912:8020/user/goibibo/external/logs/provider=stats ' 


The folder structure is as given below, there are multiple bzip2 files residing 
inside hours folder containing required data. 





/user/goibibo/external/logs/provider=stats/years=2016/months=201602/days=20160202/hours=01/
 { 1.bzip2, 2.bzip2 ...} 


Also, if table is created without partition and we point LOCATION directly to 
any particular hour everything works fine. Issue is with the partitioned table. 


Hive 0.13 ( CDH 5.3 ) 


Please help. -- 










Regards, 
Amrit 
DataPlatform Team 

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: What is the real meaning of negative value in Vertex.

[mahender bigdata]

Thanks Prasanth for sharing information

On 2/18/2016 3:23 PM, Prasanth Jayachandran wrote:

Negative values are failed attempts count.

First number if completed task count.
Second number is running task count.
Third number if failed task attempts count.
Last number is total task count.

: (+,-)/

Thanks
Prasanth
On Feb 18, 2016, at 5:08 PM, Kevin Vasko > wrote:


Typically when I have seen this the jobs were failing. Is yours 
completing successfully?


-Kevin

On Feb 18, 2016, at 4:58 PM, mahender bigdata 
mailto:mahender.bigd...@outlook.com>> 
wrote:



Hi ,
Can any one throw some information on below results of TEZ. What is 
the real meaning of negative value in Vertex.Please explain or share 
the link

Map 1: 0(+77,-1)/122  Map 2: 1/1
Map 1: 0(+77,-2)/122  Map 2: 1/1
Map 1: 0(+77,-3)/122  Map 2: 1/1
…….
……
Map 1: 0(+77,-171)/122 Map 2: 1/1
Map 1: 0(+77,-172)/122 Map 2: 1/1
Map 1: 0(+77,-174)/122 Map 2: 1/1
Map 1: 0(+77,-176)/122 Map 2: 1/1
Map 1: 0(+77,-177)/122 Map 2: 1/1
Map 1: 0(+77,-179)/122 Map 2: 1/1
Map 1: 0(+77,-180)/122 Map 2: 1/1
Map 1: 0(+77,-181)/122 Map 2: 1/1
Map 1: 0(+77,-183)/122 Map 2: 1/1
Map 1: 0(+77,-185)/122 Map 2: 1/1

What might be reasons for getting negative values in Map1 executing 
in TEX HIVe 1.2 version.

Thanks in advance

Re: What is the real meaning of negative value in Vertex.

[mahender bigdata]

Thanks Gopal.


On 2/18/2016 3:24 PM, Gopal Vijayaraghavan wrote:

Hi,

If you use the newer in.place.progress UI, it will look much better as we
have legends [1] which also shows killed tasks (due to pre-emption or to
prevent DAG dead-locks).


Map 1: 0(+77,-185)/122 Map 2: 1/1

0(+77, -185)/122 = 0 tasks completed, 77 running, 185 failed attempts
(will retry), total 122

Cheers,
Gopal
[1] - http://people.apache.org/~gopalv/LLAP.gif

  

Re: Apache sqoop and hive

[Divya Gehlot]

sqoop import-all-tables \
  --connect "jdbc:mysql://host_name:3306/db_name" \
  --username=username \
  --password=password \
  --warehouse-dir=/user/hive/warehouse/hive_db.db

Have you tried this




On 19 February 2016 at 12:17, Archana Patel  wrote:

>
> hi  , Divya
>
> Actually i am able to import single table but want to import whole
> database into hive.
> I did this.
>
> sqoop import --connect jdbc:mysql://localhost:3306/sample--username
> root -P--table demo--hive-import--hive-table default.demo -m 1
> --driver com.mysql.jdbc.Driver
>
>
> 
> From: Divya Gehlot [divya.htco...@gmail.com]
> Sent: Friday, February 19, 2016 8:37 AM
> To: user@hive.apache.org
> Subject: Re: Apache sqoop and hive
>
> Can you please post the steps how are you doing it ?
>
>
>
> On 18 February 2016 at 19:19, Archana Patel  archa...@vavni.com>> wrote:
> hi,
>
> I am trying to import all tables from mysql to hive by using apache sqoop
> but its having problem. And i have added mysql-connector-java-5.0.8-bin.jar
> also still having problem. Can anybody help on this who has tried this
> already or any idea.
>
> Thanks ,
> Archana Patel
> skype(live:archana961)
>
>

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
>