date:20160510

Re: Create external table

2016-05-10 Thread Mich Talebzadeh

yes but table then exists correct I mean second time

did you try


*use default;*

*drop table if exists trips;*


it is still within Hive metadata registered as an existing table.





Dr Mich Talebzadeh



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*



http://talebzadehmich.wordpress.com



On 11 May 2016 at 06:16, Margus Roo  wrote:

> Hi
>
> Thanks for your answer.
>
> ---
>
> At first I create an empty hdfs directory (if directory is empty I did not
> have problems before too).
>
> [margusja@hadoopnn1 ~]$ hdfs dfs -mkdir /user/margusja/trips
>
> [margusja@hadoopnn1 ~]$ beeline -f create_externat_table_trips.hql -u
> "jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_h...@testhadoop.com"
> 
> --verbose
> WARNING: Use "yarn jar" to launch YARN applications.
> issuing: !connect jdbc:hive2://hadoopnn1.example.com:2181,
> hadoopnn2.example.com:2181,
> hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_h...@testhadoop.com
> '' [passwd stripped]
> Connecting to jdbc:hive2://hadoopnn1.example.com:2181,
> hadoopnn2.example.com:2181,
> hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_h...@testhadoop.com
> Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
> Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> use default;
> Getting log thread is interrupted, since query is done!
> No rows affected (1.225 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> drop table if exists
> trips;
> Getting log thread is interrupted, since query is done!
> No rows affected (0.159 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> CREATE EXTERNAL TABLE
> `TRIPS`(
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `bike_nr` string,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `duration` int,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `start_date` string,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `start_station` string,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `end_station` string)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> PARTITIONED BY (
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `year` int,
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `month` string)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> ROW FORMAT DELIMITED
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   FIELDS TERMINATED BY
> ','
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   LINES TERMINATED BY
> '\n'
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> STORED AS INPUTFORMAT
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>
> 'org.apache.hadoop.mapred.TextInputFormat'
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> OUTPUTFORMAT
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>
> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> LOCATION
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   '/user/margusja/trips';
> Getting log thread is interrupted, since query is done!
> No rows affected (0.067 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> desc trips;
> Getting log thread is interrupted, since query is done!
>
> +--+---+---+--+
> | col_name |   data_type   |comment
> |
>
> +--+---+---+--+
> | bike_nr  | string|
> |
> | duration | int   |
> |
> | start_date   | string|
> |
> | start_station| string|
> |
> | end_station  | string|
> |
> | year | int   |
> |
> | month| string|
> |
> |  | NULL  | NULL
> |
> | # Partition Information  | NULL  | NULL
> |
> | # col_name   | data_type | comment
> |
> |  | NULL  | NULL
> |
> | year | int   |
> |
> | month| string|
> |
>
> +--+---+---+--+
> 13 rows selected (0.46 seconds)
> 0: jdbc:hive2://hadoopnn1.example.com:2181,hado> !exit
> Closing: 0: jdbc:hive2://hadoopnn1.example.com:2181,
> hadoopnn2.example.com:2181,
> hadoopdn1.host.ee:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_h...@testhadoop.com
>
>
> But In case I try:
>

Re: Create external table

2016-05-10 Thread Margus Roo


Hi

Thanks for your answer.

---

At first I create an empty hdfs directory (if directory is empty I did 
not have problems before too).


[margusja@hadoopnn1 ~]$ hdfs dfs -mkdir /user/margusja/trips

[margusja@hadoopnn1 ~]$ beeline -f create_externat_table_trips.hql -u 
"jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_h...@testhadoop.com" 
--verbose

WARNING: Use "yarn jar" to launch YARN applications.
issuing: !connect 
jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_h...@testhadoop.com 
'' [passwd stripped]
Connecting to 
jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_h...@testhadoop.com

Connected to: Apache Hive (version 1.2.1.2.3.4.0-3485)
Driver: Hive JDBC (version 1.2.1.2.3.4.0-3485)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> use default;
Getting log thread is interrupted, since query is done!
No rows affected (1.225 seconds)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> drop table if exists trips;
Getting log thread is interrupted, since query is done!
No rows affected (0.159 seconds)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> CREATE EXTERNAL TABLE 
`TRIPS`(

0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `bike_nr` string,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `duration` int,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `start_date` string,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `start_station` string,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> `end_station` string)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> PARTITIONED BY (
0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `year` int,
0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   `month` string)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> ROW FORMAT DELIMITED
0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   FIELDS TERMINATED BY ','
0: jdbc:hive2://hadoopnn1.example.com:2181,hado>   LINES TERMINATED BY '\n'
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> STORED AS INPUTFORMAT
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> 
'org.apache.hadoop.mapred.TextInputFormat'

0: jdbc:hive2://hadoopnn1.example.com:2181,hado> OUTPUTFORMAT
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> 
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

0: jdbc:hive2://hadoopnn1.example.com:2181,hado> LOCATION
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> '/user/margusja/trips';
Getting log thread is interrupted, since query is done!
No rows affected (0.067 seconds)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> desc trips;
Getting log thread is interrupted, since query is done!
+--+---+---+--+
| col_name |   data_type   | comment|
+--+---+---+--+
| bike_nr  | string |   |
| duration | int |   |
| start_date   | string |   |
| start_station| string |   |
| end_station  | string |   |
| year | int |   |
| month| string |   |
|  | NULL  | NULL  |
| # Partition Information  | NULL  | NULL  |
| # col_name   | data_type | comment   |
|  | NULL  | NULL  |
| year | int |   |
| month| string |   |
+--+---+---+--+
13 rows selected (0.46 seconds)
0: jdbc:hive2://hadoopnn1.example.com:2181,hado> !exit
Closing: 0: 
jdbc:hive2://hadoopnn1.example.com:2181,hadoopnn2.example.com:2181,hadoopdn1.host.ee:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;principal=hive/_h...@testhadoop.com



But In case I try:

 CREATE EXTERNAL TABLE default.st1_test_margusja (
  original STRING,
  rsyslog_timestamp STRING,
  rsyslog_url STRING,
  rsyslog_appname STRING,
  rsyslog_pos5 STRING,
  ts STRING,
  url STRING,
  username STRING,
ip_address STRING,
log_level STRING,
content STRING
  )
  COMMENT 'Dealgate logs raw data'
  PARTITIONED BY (year STRING, month STRING, day STRING)
  ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
  WITH SERDEPROPERTIES ('input.regex'='([^\\s]+ ([^\\s]+) ([^\\s]+) 
([^\\s]+) ([^\\s]+) [^\\s]+ 
[^\

RE: Any difference between LOWER and LCASE

2016-05-10 Thread Markovitz, Dudu

Hi

According to documentation LCASE is a synonym for LOWER.
From what I've seen in the source code, it seems right.

https://github.com/apache/hive/blob/f089f2e64241592ecf8144d044bec8a0659ff422/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java

system.registerGenericUDF("lower", GenericUDFLower.class);
system.registerGenericUDF("lcase", GenericUDFLower.class);


Please verify that you've run the exact same queries.
If you still see an issue, please share the relevant DDL (table/tables 
definition) and a small subset of data so I would be able to reproduce it.

Thanks

Dudu

-Original Message-
From: mahender bigdata [mailto:mahender.bigd...@outlook.com] 
Sent: Wednesday, May 11, 2016 1:55 AM
To: user@hive.apache.org
Subject: Any difference between LOWER and LCASE

Hi Team,

Is there any difference between LOWER and LCASE functions in Hive. For one of 
the query, when we are using LOWER in where condition, it is failing to match 
record. When we changed to LCASE, it started matching. 
I surprised to see differences in LOWER and LCASE. Can any one know why there 2 
function for same functionality. Is there any thing to do with any special or 
Unicode characters where Lower and LCASE differs in functionality


/MS

RE: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

2016-05-10 Thread Markovitz, Dudu

You’re welcome

Dudu

From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Wednesday, May 11, 2016 1:43 AM
To: user@hive.apache.org
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions 
that are top level conjuncts are allowed


Thanks Dudu, I made modification as per our requirement. ur query helped me to 
modify as per our requirement.

On 5/4/2016 10:57 AM, Markovitz, Dudu wrote:
Hi

The syntax is not Hive specific but SQL ANSI/ISO.
In a series of “JOIN … ON …” any “ON” can (but not necessarily have to) refer 
any of its preceding tables, e.g. –

select … from t1 join t2 on … *1 … join t3 on … *2 … join t4 on … *3 …
*1  The 1st “ON” can refer tables t1 & t2
*2  The 2nd “ON” can refer tables t1, t2 & t3
*3  The 3rd “ON” can refer tables t1, t2, t3 & t4

In our query the “… group by … > 1” combined with “b2.col1 is null” implements 
the functionality of the “not exists” from the original query.
The rest of the query stays quite the same.

Dudu

From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Wednesday, May 04, 2016 7:39 PM
To: user@hive.apache.org
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions 
that are top level conjuncts are allowed


Thanks Dudu,

Can you help me in parsing below logic, I see First you are starting join of 
table1 with result set of Group by > 1 and perform left join with table2, how 
can we get reference a. alias of joined result or will hive pickup "a" column 
from table 1 and 3 column in table2.



thanks in advance



On 5/3/2016 11:24 AM, Markovitz, Dudu wrote:
Forget about the BTW…
Apparently hive behaves like sqlite in that matter and not like other databases

hive> select 1 from table1 having 1=1;
FAILED: SemanticException HAVING specified without GROUP BY

From: Markovitz, Dudu [mailto:dmarkov...@paypal.com]
Sent: Tuesday, May 03, 2016 8:36 PM
To: user@hive.apache.org
Subject: RE: Unsupported SubQuery Expression '1': Only SubQuery expressions 
that are top level conjuncts are allowed

I left out the filter on column Col2 in order to simplify the test case.
The following query is logically equal to your original query.

BTW –
You don’t need the GROUP BY A.Col1 part in your original query

Dudu

create table Table1 (Col1 int,Col3 int);
create table Table2 (Col1 int,Col3 int);

insert into Table1 values (10,1),(20,2),(40,4),(60,7),(80,8);
insert into Table2 values (10,1),(30,2),(20,3),(50,4),(40,5),(40,6),(70,7);


select  *



fromtable1  a



left join  (select  col1



fromtable2



group bycol1



having  count(*) > 1

)

b2



  onb2.col1  =

a.col1



left join   table2  b



on  a.col3  =

b.col3



and b2.col1 is null

;

10   1  NULL 10   1
20   2  NULL 30   2
40   4  40   NULL NULL
60   7  NULL 70   7
80   8  NULL NULL NULL

From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Tuesday, May 03, 2016 4:02 PM
To: user@hive.apache.org
Subject: Re: Unsupported SubQuery Expression '1': Only SubQuery expressions 
that are top level conjuncts are allowed


Updated..

select A.Col1,A.Col2B.Col3

From Table1 A
LEFT OUTER JOIN Table2 B
ON  A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 
HAVING COUNT(*)>1 )
 AND (CASE WHEN ISNULL(A.Col2,'\;')  = '\;' THEN 'NOT-NULL' ELSE 'NULL' 
END) = B.Col2)
On 5/2/2016 10:52 PM, Markovitz, Dudu wrote:
Hi

Before dealing the issue itself, can you please fix the query?
There are 3 aliased tables - Table1 (A), Table2 (B)  & Table2 (mb) but you’re 
using additional 2 aliases – ma & adi1.

Thanks

Dudu

select A.Col1,A.Col2B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON  A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP BY A.Col1 
HAVING COUNT(*)>1 )
 AND (CASE WHEN ISNULL(A.Col2,'\;')  = '\;' THEN 'NOT-NULL' ELSE 'NULL' 
END) = B.Col2)





From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Tuesday, May 03, 2016 4:22 AM
To: user@hive.apache.org
Subject: Unsupported SubQuery Expression '1': Only SubQuery expressions that 
are top level conjuncts are allowed


Hi,

Is there a way to implement  not exists in Hive. I'm using Hive 1.2. I'm 
getting below error

"Unsupported SubQuery Expression '1': Only SubQuery expressions that are top 
level conjuncts are allowed"

Query:



select A.Col1,A.Col2B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON  A.Col3= B.C

Hive cte Alias problem

2016-05-10 Thread mahender bigdata


Hi,

I see peculiar difference while querying using CTE where I'm aliasing 
one of column in table to another column name in same table. Instead of 
getting values of source column, hive returns NULLS i.e column 8 values


withcte_temp as

(

select  a.COLUMN1, a.Column2,a.Column2 asColumn8,ID

from a whereCoalesce(ltrim(rtrim(a.COLUMN1)) ,'') <> ''

ANDCoalesce(ltrim(rtrim(a.COLUMN2)), '') <> ''

ANDCoalesce(ltrim(rtrim(a.COLUMN2)), '') <> ''

ANDa.COLUMN8 ISNULL

anda.ID=100

)

select* fromcte_temp ;


_*Results*_

cte_temp.column1,cte_temp.column2,cte_temp.column8,ID

Row1,UK,,49

Row5,UP,,49


From the above query, Col2 has not null values and I'm filtering on 
Col8 =null, I'm aliasing Col2 has Col8. Whenever I perform Select * from 
CTE, I see instead of showing Col2 values, it is showing Col8 values. Is 
it bug with Hive.



When i run query using SQL SELECT only, it is working fine.


select  a.COLUMN1, a.Column2,a.Column2 asColumn8,ID

from a whereCoalesce(ltrim(rtrim(a.COLUMN1)) ,'') <> ''

ANDCoalesce(ltrim(rtrim(a.COLUMN2)), '') <> ''

ANDCoalesce(ltrim(rtrim(a.COLUMN2)), '') <> ''

ANDa.COLUMN8 ISNULL

anda.ID=100

Results

_cte_temp.column1,cte_temp.column2,cte_temp.column8,ID_

Row1,UK,test,49

Row5,UP,test,49

Please let me know whether it is problem with CTE.

/Mahender

Re: Create external table

2016-05-10 Thread Mich Talebzadeh

Try this simple external table creation in beeline (check first that that
it connects OK)























*use default;drop table if exists trips;CREATE EXTERNAL TABLE `TRIPS`(
`bike_nr` string,  `duration` int,  `start_date` string,  `start_station`
string,  `end_station` string)PARTITIONED BY (  `year` int,  `month`
string)ROW FORMAT DELIMITED  FIELDS TERMINATED BY ','  LINES TERMINATED BY
'\n'STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION
'/test/text/trips';desc trips;!exit*

run the code
bl -f create_externat_table_trips.hql

No rows affected (0.018 seconds)
CREATE EXTERNAL TABLE `TRIPS`(
  `bike_nr` string,
  `duration` int,
  `start_date` string,
  `start_station` string,
  `end_station` string)
PARTITIONED BY (
  `year` int,
  `month` string)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '/test/text/trips'
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO  : Completed compiling
command(queryId=hduser_20160511001257_b2b612dd-d007-406d-a49c-83e8352a6ac1);
Time taken: 0.009 seconds
INFO  : Executing
command(queryId=hduser_20160511001257_b2b612dd-d007-406d-a49c-83e8352a6ac1):
CREATE EXTERNAL TABLE `TRIPS`(
  `bike_nr` string,
  `duration` int,
  `start_date` string,
  `start_station` string,
  `end_station` string)
PARTITIONED BY (
  `year` int,
  `month` string)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '/test/text/trips'
INFO  : Starting task [Stage-0:DDL] in serial mode
INFO  : Completed executing
command(queryId=hduser_20160511001257_b2b612dd-d007-406d-a49c-83e8352a6ac1);
Time taken: 0.028 seconds
INFO  : OK
+--+---+---+--+
| col_name |   data_type   |comment|
+--+---+---+--+
| bike_nr  | string|   |
| duration | int   |   |
| start_date   | string|   |
| start_station| string|   |
| end_station  | string|   |
| year | int   |   |
| month| string|   |
|  | NULL  | NULL  |
| # Partition Information  | NULL  | NULL  |
| # col_name   | data_type | comment   |
|  | NULL  | NULL  |
| year | int   |   |
| month| string|   |
+--+---+---+--+
13 rows selected (0.13 seconds)
0: jdbc:hive2://rhes564:10010/default> Closing: 0:
jdbc:hive2://rhes564:10010/default


HTH

Dr Mich Talebzadeh



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*



http://talebzadehmich.wordpress.com



On 10 May 2016 at 21:25, Margus Roo  wrote:

> Hi again
>
> I opened hive (an old client)
>
> And exactly the same create external table  location [paht in hdfs to
> place where are loads of files] works and the same DDL does not work via
> beeline.
>
> Margus (margusja) Roohttp://margus.roo.ee
> skype: margusja+372 51 48 780
>
> On 10/05/16 23:03, Margus Roo wrote:
>
> Hi
>
> Can someone explain or provide documentation how Hive creates external
> tables?
>
> I have problem with creating external table in case I am pointing location
> in hdfs in to directory where are loads of files. Beeline just hangs or
> there will be other errors.
>
> In case I point location in to the empty directory then hive creates table.
>
>
> So does hive looks into files during creating external table?
>
> I can not find any documentation explaining it.
>
> --
> Margus (margusja) Roohttp://margus.roo.ee
> skype: margusja+372 51 48 780
>
>
>

Any difference between LOWER and LCASE

2016-05-10 Thread mahender bigdata


Hi Team,

Is there any difference between LOWER and LCASE functions in Hive. For 
one of the query, when we are using LOWER in where condition, it is 
failing to match record. When we changed to LCASE, it started matching. 
I surprised to see differences in LOWER and LCASE. Can any one know why 
there 2 function for same functionality. Is there any thing to do with 
any special or Unicode characters where Lower and LCASE differs in 
functionality



/MS

Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

2016-05-10 Thread mahender bigdata

Thanks Dudu, I made modification as per our requirement. ur query helped 
me to modify as per our requirement.



On 5/4/2016 10:57 AM, Markovitz, Dudu wrote:


Hi

The syntax is not Hive specific but SQL ANSI/ISO.

In a series of “JOIN … ON …” any “ON” can (but not necessarily have 
to) refer any of its preceding tables, e.g. –


select … from t1 join t2 on … ^*1 … join t3 on … ^*2 … join t4 on … ^*3 …

^*1 The 1st “ON” can refer tables t1 & t2^

^*2 The 2nd “ON” can refer tables t1, t2 & t3^

^*3 The 3rd “ON” can refer tables t1, t2, t3 & t4^

^

In our query the “… *group* *by*… > 1” combined with “b2.col1 *is* 
*null*” implements the functionality of the “not exists” from the 
original query.


The rest of the query stays quite the same.

Dudu

*From:*mahender bigdata [mailto:mahender.bigd...@outlook.com]
*Sent:* Wednesday, May 04, 2016 7:39 PM
*To:* user@hive.apache.org
*Subject:* Re: Unsupported SubQuery Expression '1': Only SubQuery 
expressions that are top level conjuncts are allowed


Thanks Dudu,

Can you help me in parsing below logic, I see First you are starting 
join of table1 with result set of Group by > 1 and perform left join 
with table2, how can we get reference a. alias of joined result or 
will hive pickup "a" column from table 1 and 3 column in table2.


thanks in advance

On 5/3/2016 11:24 AM, Markovitz, Dudu wrote:

Forget about the BTW…

Apparently hive behaves like sqlite in that matter and not like
other databases

hive> select 1 from table1 having 1=1;

FAILED: SemanticException HAVING specified without GROUP BY

*From:*Markovitz, Dudu [mailto:dmarkov...@paypal.com]
*Sent:* Tuesday, May 03, 2016 8:36 PM
*To:* user@hive.apache.org 
*Subject:* RE: Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed

I left out the filter on column Col2in order to simplify the test
case.

The following query is logically equal to your original query.

**

BTW –

You don’t need the GROUP BY A.Col1 part in your original query

Dudu

**

*create**table*Table1(Col1int,Col3int)*;*

*create**table*Table2(Col1int,Col3int)*;*

*insert**into*Table1*values*(10,1),(20,2),(40,4),(60,7),(80,8)*;*


*insert**into*Table2*values*(10,1),(30,2),(20,3),(50,4),(40,5),(40,6),(70,7)*;*

*select**

*from*table1a

*left**join*  (*select*col1

*from*table2

*group**by*col1

*having*count(*) >1

)

b2

*on*b2.col1=

a.col1

*left**join*table2b

*on*a.col3=

b.col3

*and*b2.col1*is**null*

*;*


10 1  NULL 10   1

20 2  NULL 30   2

40 4  40   NULL NULL

60 7  NULL 70   7

80 8  NULL NULL NULL

*From:*mahender bigdata [mailto:mahender.bigd...@outlook.com]
*Sent:* Tuesday, May 03, 2016 4:02 PM
*To:* user@hive.apache.org 
*Subject:* Re: Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed

Updated..

select A.Col1,A.Col2B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1 GROUP
BY A.Col1 HAVING COUNT(*)>1 )
 AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN 'NOT-NULL'
ELSE 'NULL' END) = B.Col2)

On 5/2/2016 10:52 PM, Markovitz, Dudu wrote:

Hi

Before dealing the issue itself, can you please fix the query?

There are 3 aliased tables - Table1 (A), Table2 (B)  & Table2
(mb) but you’re using additional 2 aliases – ma & adi1.

Thanks

Dudu

select A.Col1,A.Col2B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 B WHERE B.Col1= A.Col1
GROUP BY A.Col1 HAVING COUNT(*)>1 )
 AND (CASE WHEN ISNULL(A.Col2,'\;') = '\;' THEN
'NOT-NULL' ELSE 'NULL' END) = B.Col2)

*From:*mahender bigdata [mailto:mahender.bigd...@outlook.com]
*Sent:* Tuesday, May 03, 2016 4:22 AM
*To:* user@hive.apache.org 
*Subject:* Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed

Hi,

Is there a way to implement  not exists in Hive. I'm using
Hive 1.2. I'm getting below error

"Unsupported SubQuery Expression '1': Only SubQuery
expressions that are top level conjuncts are allowed"

_Query:_

select A.Col1,A.Col2B.Col3

From Table1 A

LEFT OUTER JOIN Table2 B
ON  A.Col3= B.Col3
AND NOT EXISTS(SELECT 1 FROM Table2 mb WHERE ma.Col1=
adi1.Col1 GROUP BY ma.Col1 HAVING COUN

Re: Create external table

2016-05-10 Thread Margus Roo


Hi again

I opened hive (an old client)

And exactly the same create external table  location [paht in hdfs 
to place where are loads of files] works and the same DDL does not work 
via beeline.


Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780

On 10/05/16 23:03, Margus Roo wrote:


Hi

Can someone explain or provide documentation how Hive creates external 
tables?


I have problem with creating external table in case I am pointing 
location in hdfs in to directory where are loads of files. Beeline 
just hangs or there will be other errors.


In case I point location in to the empty directory then hive creates 
table.



So does hive looks into files during creating external table?

I can not find any documentation explaining it.

--
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780

Create external table

2016-05-10 Thread Margus Roo


Hi

Can someone explain or provide documentation how Hive creates external 
tables?


I have problem with creating external table in case I am pointing 
location in hdfs in to directory where are loads of files. Beeline just 
hangs or there will be other errors.


In case I point location in to the empty directory then hive creates table.


So does hive looks into files during creating external table?

I can not find any documentation explaining it.

--
Margus (margusja) Roo
http://margus.roo.ee
skype: margusja
+372 51 48 780

Re: Create external table

Re: Create external table

RE: Any difference between LOWER and LCASE

RE: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Hive cte Alias problem

Re: Create external table

Any difference between LOWER and LCASE

Re: Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed

Re: Create external table

Create external table

10 matches

Site Navigation

Mail list logo

Footer information