Re: hqlsql on hive partition table

[Dmitry Tolpeko]

Can you please create a Jira ticket, and put code sample, not screenshot
there.

Thanks,
Dmitry

On Tue, Jan 29, 2019 at 9:08 AM 陈贵龙  wrote:

> Hi，
>How are you . i have  a question in how to use hqlsql on hive partition
> table
> when I use Hqlsql on hive Partition table,Why get the key Word  to
> function .Thank.
>
>
>
>
>
>

Re: About hplsql

[Dmitry Tolpeko]

Hi,

If you run Spark thrift server, you can just use an JDBC connection to
connect to Spark SQL directly. Edit hplsql-site.xml and add your connection
there. See http://hplsql.org/connections for details.

Thanks,
Dmitry

On Fri, Dec 28, 2018 at 6:43 AM 郑 洁锋  wrote:

> Hi,
> We see that your hplsql product introduction says support for
> spark-sql, but the document only has hive-on-spark, so how to support
> spark-sql separately?
>
>

Re: Re: How to use HPL/SQL connect with impala and Hive

[Dmitry Tolpeko]

Looks like you are trying to connect not through HiveServer2.

You should use:


  hplsql.conn.default
  hive2conn



  hplsql.conn.hive2conn

org.apache.hive.jdbc.HiveDriver;jdbc:hive2://:1;;


Thanks,
Dmitry

On Thu, Nov 23, 2017 at 10:32 AM, 梁凯 <liang...@yaspeed.com> wrote:

> Dear，
>
> Thanks for your help. I try to connect to hive1.1 ，the error output as
> follows：
>
> ./hplsql -e "select * from wxy.test1"
> SLF4J: Class path contains multiple SLF4J bindings.
> SLF4J: Found binding in [jar:file:/opt/cloudera/
> parcels/CDH-5.13.0-1.cdh5.13.0.p0.29/jars/avro-tools-1.7.6-
> cdh5.13.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in [jar:file:/opt/cloudera/
> parcels/CDH-5.13.0-1.cdh5.13.0.p0.29/jars/slf4j-log4j12-1.
> 7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in [jar:file:/opt/cloudera/
> parcels/CDH-5.13.0-1.cdh5.13.0.p0.29/jars/pig-0.12.0-cdh5.
> 13.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in [jar:file:/opt/cloudera/
> parcels/CDH-5.13.0-1.cdh5.13.0.p0.29/jars/slf4j-simple-1.7.
> 5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
> explanation.
> SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
> 2017-11-23 14:42:56,683 INFO  [main] jdbc.Utils (Utils.java:parseURL(295))
> - Supplied authorities: datanode221:1
> 2017-11-23 14:42:56,689 INFO  [main] jdbc.Utils (Utils.java:parseURL(383))
> - Resolved authority: datanode221:1
>
>
> Exception in thread "main" java.lang.IncompatibleClassChangeError: class
> org.apache.hive.service.cli.thrift.TCLIService$Client has interface
> org.apache.thrift.TServiceClient as super class
> at java.lang.ClassLoader.defineClass1(Native Method)
> at java.lang.ClassLoader.defineClass(ClassLoader.java:763)
> at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
> at java.net.URLClassLoader.defineClass(URLClassLoader.java:467)
> at java.net.URLClassLoader.access$100(URLClassLoader.java:73)
> at java.net.URLClassLoader$1.run(URLClassLoader.java:368)
> at java.net.URLClassLoader$1.run(URLClassLoader.java:362)
> at java.security.AccessController.doPrivileged(Native Method)
> at java.net.URLClassLoader.findClass(URLClassLoader.java:361)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
> at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
> at org.apache.hive.jdbc.HiveConnection.(HiveConnection.java:170)
> at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105)
> at java.sql.DriverManager.getConnection(DriverManager.java:664)
> at java.sql.DriverManager.getConnection(DriverManager.java:247)
> at org.apache.hive.hplsql.Conn.openConnection(Conn.java:209)
> at org.apache.hive.hplsql.Conn.getConnection(Conn.java:162)
> at org.apache.hive.hplsql.Conn.executeQuery(Conn.java:58)
> at org.apache.hive.hplsql.Exec.executeQuery(Exec.java:560)
> at org.apache.hive.hplsql.Exec.executeQuery(Exec.java:569)
> at org.apache.hive.hplsql.Select.select(Select.java:75)
> at org.apache.hive.hplsql.Exec.visitSelect_stmt(Exec.java:1021)
> at org.apache.hive.hplsql.Exec.visitSelect_stmt(Exec.java:52)
> at org.apache.hive.hplsql.HplsqlParser$Select_stmtContext.accept(
> HplsqlParser.java:15050)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(
> AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:1013)
> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:52)
> at org.apache.hive.hplsql.HplsqlParser$StmtContext.
> accept(HplsqlParser.java:1023)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(
> AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.HplsqlBaseVisitor.visitBlock(
> HplsqlBaseVisitor.java:28)
> at org.apache.hive.hplsql.HplsqlParser$BlockContext.
> accept(HplsqlParser.java:454)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(
> AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:920)
> at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:52)
> at org.apache.hive.hplsql.HplsqlParser$ProgramContext.
> accept(HplsqlParser.java:395)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(
> AbstractParseTreeVisitor.java:42)
> at org.apache.hive.hplsql.Exec.run(Exec.java:775)
> at org.apache.hive.hplsql.Exec.run(Exec.java:751)
> at org.apache.hive.hplsql.Hplsql.main(Hplsql.java:23)
>
>
>
> 在 2017-11-22 20:48:39，"Dmitry Tolpeko" <dmtolp...@gmail.com> 写道：
>
> >exeception : java.lang.incompatible class change error
>
> Can you please send the entire error 

Re: How to use HPL/SQL connect with impala and Hive

[Dmitry Tolpeko]

>exeception : java.lang.incompatible class change error

Can you please send the entire error output?

>How to  make a connection between HPL/SQL and impala

Connection to Impala is not pre-defined yet, so you have to create a new
connection, see http://hplsql.org/connections

Thanks,
Dmitry

On Wed, Nov 22, 2017 at 1:09 PM, 梁凯  wrote:

> Hello，
> I'm a Hadoop Development Engineer, and I'm excited to find your tools,
> because it's what I've been looking for, but there are some problems with
> the installation, and I hope you can help . Thank you!
>
> Question 1: did I install HPL/SQL in hive1.1? I tried, installed it , but
> when I was going to connect to hive with HPL/SQL, It can't work
> exeception : java.lang.incompatible class change error
> Question 2: I have a impala cluster, and now want to use HPL/SQL on
> impala, How to  make a connection between HPL/SQL and impala.
>
> Regards.
>
>

Re: Use case for Temporary or Volatile Table - Need to update fields in the hive table records

[Dmitry Tolpeko]

Read about temporary tables at
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TemporaryTables
and decide whether you need them or not :)

Volatile is a just syntax support for compatibility with Teradata.

Thanks,
Dmitry

On Mon, Oct 2, 2017 at 2:17 AM, Srinivas Alavala <srinivas.alav...@rcggs.com
> wrote:

> Hi Dmitry,
>
> I am trying to understand the use cases for Temporary and Volatile tables.
> Can I write the updated bulk data in them and create a new hive table at
> the end? I guess I can use spark to do the ETL if hplsql is NOT the right
> tool.
>
> Thanks & Regards,
> Srini Alavala
>
> -Dmitry Tolpeko <dmtolp...@gmail.com> wrote: -
> To: Srinivas Alavala <srinivas.alav...@rcggs.com>
> From: Dmitry Tolpeko <dmtolp...@gmail.com>
> Date: 10/01/2017 03:19AM
> Cc: user@hive.apache.org
> Subject: Re: Hplsql Cursor Loop Java Error - Resolved
>
> Thanks! It should not throw NPE anyway, I will create a ticket for
>
> Exception in thread "main" java.lang.NullPointerException
> at org.apache.hive.hplsql.Select.getIntoCount(Select.java:405)
>
> Thanks,
> Dmitry
>
> On Sun, Oct 1, 2017 at 1:43 AM, Srinivas Alavala <
> srinivas.alav...@rcggs.com> wrote:
>
>>
>> I forgot to add the END LOOP statement. It is working fine.
>>
>> Thanks,
>> Srini Alavala
>>
>> -Srinivas Alavala/RCGIT wrote: -
>> To: user@hive.apache.org
>> From: Srinivas Alavala/RCGIT
>> Date: 09/30/2017 05:37PM
>> Cc: dmtolp...@gmail.com
>> Subject: Hplsql Cursor Loop Java Error
>>
>> Hi Guys,
>>
>> I am very excited about hplsql. I have started writing code in Cloudera
>> CDH 5.12.X environment with Kerberos security. I am getting jave error
>> while I am trying to execute Cursor loop statement as shown below. How do I
>> troubleshoot this error? Trace didn't show much info.
>>
>> I want to find out if this is production ready. Please guide us.
>>
>>
>> CREATE PROCEDURE getPromoteList(IN position STRING, OUT status STRING)
>> BEGIN
>>
>> FOR cur IN
>> (SELECT customer_cid, customer_first_name FROM Table_Name where
>> prowess_period_id=201601 and customer_pay_title = 'Value')
>> LOOP
>> DBMS_OUTPUT.PUT_LINE(cur.customer_cid);
>> END;
>>
>> DECLARE status STRING;
>> CALL getPromoteList(201601,status);
>> PRINT status;
>>
>>
>> Ln:1 CREATE FUNCTION hello
>> Ln:1 CREATE PROCEDURE getPromoteList
>> Ln:10 DECLARE status STRING
>> Ln:11 EXEC PROCEDURE getPromoteList
>> Ln:11 SET PARAM position = 201601
>> Ln:11 SET PARAM status = null
>> Ln:5 SELECT
>> Ln:5 
>> 
>> [main] INFO org.apache.hive.jdbc.Utils - Supplied authorities:
>> *
>> [main] INFO org.apache.hive.jdbc.Utils - Resolved authority:
>> 
>> [main] WARN org.apache.hadoop.util.NativeCodeLoader - Unable to load
>> native-hadoop library for your platform... using builtin-java classes where
>> applicable
>> Open connection: j*
>> 
>> Starting query
>> Query executed successfully (38.97 sec)
>> Ln:5 SELECT completed successfully
>> Exception in thread "main" java.lang.NullPointerException
>> at org.apache.hive.hplsql.Select.getIntoCount(Select.java:405)
>> at org.apache.hive.hplsql.Select.select(Select.java:88)
>> at org.apache.hive.hplsql.Exec.visitSelect_stmt(Exec.java:1021)
>> at org.apache.hive.hplsql.Exec.visitSelect_stmt(Exec.java:52)
>> at org.apache.hive.hplsql.HplsqlParser$Select_stmtContext.
>> accept(HplsqlParser.java:15050)
>> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChil
>> dren(AbstractParseTreeVisitor.java:70)
>> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:1013)
>> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:52)
>> at org.apache.hive.hplsql.HplsqlParser$StmtContext.accept(
>> HplsqlParser.java:1023)
>> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChil
>> dren(AbstractParseTreeVisitor.java:70)
>> at org.apache.hive.hplsql.HplsqlBaseVisitor.visitBlock(HplsqlBa
>> seVisitor.java:28)
>> at org.apache.hive.hplsql.HplsqlParser$BlockContext.accept(
>> HplsqlParser.java:454)
>> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChil
>> dren(AbstractParseTreeVisitor.java

Re: Hplsql Cursor Loop Java Error - Resolved

[Dmitry Tolpeko]

Thanks! It should not throw NPE anyway, I will create a ticket for

Exception in thread "main" java.lang.NullPointerException
at org.apache.hive.hplsql.Select.getIntoCount(Select.java:405)

Thanks,
Dmitry

On Sun, Oct 1, 2017 at 1:43 AM, Srinivas Alavala  wrote:

>
> I forgot to add the END LOOP statement. It is working fine.
>
> Thanks,
> Srini Alavala
>
> -Srinivas Alavala/RCGIT wrote: -
> To: user@hive.apache.org
> From: Srinivas Alavala/RCGIT
> Date: 09/30/2017 05:37PM
> Cc: dmtolp...@gmail.com
> Subject: Hplsql Cursor Loop Java Error
>
> Hi Guys,
>
> I am very excited about hplsql. I have started writing code in Cloudera
> CDH 5.12.X environment with Kerberos security. I am getting jave error
> while I am trying to execute Cursor loop statement as shown below. How do I
> troubleshoot this error? Trace didn't show much info.
>
> I want to find out if this is production ready. Please guide us.
>
>
> CREATE PROCEDURE getPromoteList(IN position STRING, OUT status STRING)
> BEGIN
>
> FOR cur IN
> (SELECT customer_cid, customer_first_name FROM Table_Name where
> prowess_period_id=201601 and customer_pay_title = 'Value')
> LOOP
> DBMS_OUTPUT.PUT_LINE(cur.customer_cid);
> END;
>
> DECLARE status STRING;
> CALL getPromoteList(201601,status);
> PRINT status;
>
>
> Ln:1 CREATE FUNCTION hello
> Ln:1 CREATE PROCEDURE getPromoteList
> Ln:10 DECLARE status STRING
> Ln:11 EXEC PROCEDURE getPromoteList
> Ln:11 SET PARAM position = 201601
> Ln:11 SET PARAM status = null
> Ln:5 SELECT
> Ln:5 
> 
> [main] INFO org.apache.hive.jdbc.Utils - Supplied authorities:
> *
> [main] INFO org.apache.hive.jdbc.Utils - Resolved authority:
> 
> [main] WARN org.apache.hadoop.util.NativeCodeLoader - Unable to load
> native-hadoop library for your platform... using builtin-java classes where
> applicable
> Open connection: j*
> 
> Starting query
> Query executed successfully (38.97 sec)
> Ln:5 SELECT completed successfully
> Exception in thread "main" java.lang.NullPointerException
> at org.apache.hive.hplsql.Select.getIntoCount(Select.java:405)
> at org.apache.hive.hplsql.Select.select(Select.java:88)
> at org.apache.hive.hplsql.Exec.visitSelect_stmt(Exec.java:1021)
> at org.apache.hive.hplsql.Exec.visitSelect_stmt(Exec.java:52)
> at org.apache.hive.hplsql.HplsqlParser$Select_stmtContext.accept(
> HplsqlParser.java:15050)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.
> visitChildren(AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:1013)
> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:52)
> at org.apache.hive.hplsql.HplsqlParser$StmtContext.
> accept(HplsqlParser.java:1023)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.
> visitChildren(AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.HplsqlBaseVisitor.visitBlock(
> HplsqlBaseVisitor.java:28)
> at org.apache.hive.hplsql.HplsqlParser$BlockContext.
> accept(HplsqlParser.java:454)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.
> visitChildren(AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.Exec.visitBegin_end_block(Exec.java:930)
> at org.apache.hive.hplsql.Exec.visitBegin_end_block(Exec.java:52)
> at org.apache.hive.hplsql.HplsqlParser$Begin_end_
> blockContext.accept(HplsqlParser.java:549)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.
> visitChildren(AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.HplsqlBaseVisitor.visitProc_
> block(HplsqlBaseVisitor.java:56)
> at org.apache.hive.hplsql.HplsqlParser$Proc_blockContext.accept(
> HplsqlParser.java:756)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(
> AbstractParseTreeVisitor.java:42)
> at org.apache.hive.hplsql.functions.Function.visit(
> Function.java:754)
> at org.apache.hive.hplsql.functions.Function.execProc(
> Function.java:244)
> at org.apache.hive.hplsql.Exec.visitCall_stmt(Exec.java:1797)
> at org.apache.hive.hplsql.Exec.visitCall_stmt(Exec.java:52)
> at org.apache.hive.hplsql.HplsqlParser$Call_stmtContext.
> accept(HplsqlParser.java:3191)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.
> visitChildren(AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:1013)
> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:52)
> at org.apache.hive.hplsql.HplsqlParser$StmtContext.
> accept(HplsqlParser.java:1023)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.
> visitChildren(AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.HplsqlBaseVisitor.visitBlock(
> 

Hive table for a single file: CREATE/ALTER TABLE differences

[Dmitry Tolpeko]

​
​I accidentally noticed​​ one feature:
(it is well know
​n​
that in CREATE TABLE you must specify a directory for the table LOCATION
otherwise you get: "Can't make directory for path 's3n://dir/file' since it
is a file.")

But at the same time, ALTER TABLE SET LOCATION 's3n://dir/file' works fine.
​ SELECT also reads data from the single file only. ​

I see this in Hive 1.0.0-amzn-4.

Is this just a bug and
​will
 be fixed
​ some day​
(or maybe already fixed) or it is for some reason, and will stay?

Thanks,
Dmitry

Re: hplsql tool not supporting NON EQUI JOIN

[Dmitry Tolpeko]

Prabhu,

Can you please post the statement?

Dmitry

On Thu, Jan 19, 2017 at 7:38 PM,  wrote:

> Hi there,
>
> This is Prabhu Sekar from Cognizant UK.
> We are working on an initiative where in we need to migrate 100+ complex
> stored procedures from SQL to Hive.
> While researching, we came across hplsql.org and found it to be very
> exciting tool and thought it would be of great help to us to migrate the
> procedures seamlessly.
> But when we attempt to execute procedures with non equi joins, it throws
> an error like "*FAILED: Error in semantic analysis: Both Left and Right
> Aliases **Encountered in..*"
> Could you please let us know if there is an alternate solution or a work
> around to sort out this issue without changing the existing procedure?
>
> Thanks,
> Prabhu
> This e-mail and any files transmitted with it are for the sole use of the
> intended recipient(s) and may contain confidential and privileged
> information. If you are not the intended recipient(s), please reply to the
> sender and destroy all copies of the original message. Any unauthorized
> review, use, disclosure, dissemination, forwarding, printing or copying of
> this email, and/or any action taken in reliance on the contents of this
> e-mail is strictly prohibited and may be unlawful. Where permitted by
> applicable law, this e-mail and other e-mail communications sent to and
> from Cognizant e-mail addresses may be monitored.
>

Re: Run HPL/SQL on CDH 5.7 with Hive 1.1

[Dmitry Tolpeko]

Looks like a Hive issue executing truncate. Can you please add --trace
option to command line, maybe it shows more details. Did you try to execute
truncate on this table in Hive CLI? Does it work? Is it managed or external
table?

Thanks,
Dmitry

On Wed, Nov 30, 2016 at 5:46 PM, Su Changfeng <changfeng...@outlook.com>
wrote:

> Hi Dmitry Tolpeko and Hive Experts
>
> My company  wants to migrate some ETL processes written in Oracle PL/SQL
> to Hive or something else.
>
> Because we don't have any component running on our Hadoop cluster
> which supports PL/SQL, wa wanna try  HPL/SQL for our PL/SQL migration.
>
> Our Hadoop is running on CDH express and the version is 5.8.1 with Hive
> 1.1, and the HPL/SQL I used is the latest version 0.3.17 (choose this
> version is just because it is the only version supports "Truncate Table"
> DDL operation, and our ETLs have lot of "Truncate Table" statements.
>
> But unfortunately "Truncate Table" does not work as expected and it runs
> into exception (SQL is as below)
>
> *REATE OR REPLACE Procedure p_M_TJB_BAL_INF()*
>
> *BEGIN*
>
> *  PRINT 'DELETE_TABLES excuting' ;*
>
> * Truncate table TEST_DB.TEST5;*
>
> * INSERT INTO TEST_DB.TEST5 VALUES ('1','C'),('2','D'),*
> * ('3','C'),('4','D');*
>
> * select * from TEST_DB.test5;*
>
> *END;*
>
> *call p_M_TJB_BAL_INF('2016-11-30');*
>
>
> ...
>
> pen connection: jdbc:hive2://localhost:1 (287 ms)
>
> Starting SQL statement
>
> Unhandled exception in HPL/SQL
>
> java.sql.SQLException: Error while processing statement: FAILED: Execution
> Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
> Exception while processing
>
> at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:279)
> ...
>
>
> But after I changing the "Truncate Table" into drop/re-create table DDL,
> it works very well.
>
> *drop table if exists test_db.test5;*
>
> *create table test_db.test5(id int ,names string )*
>
> *clustered by (id) into 2 buckets Stored as textfile;*
>
> Do you have any idea or input? Any comment is appreciated.
>
> --
>
> Thanks & Regards,
>
> Kevin
>
>

Re: Issues regarding HPLSQL tool

[Dmitry Tolpeko]

Ainhoa,

Did you provide correct username and password to connect to Hive server?
The problem I see is that it cannot launch a MapReduce job. SELECT * FROM
tab does not require a MR job so it works fine, while when you add WHERE
clause it fails.

Thanks,
Dmitry

On Tue, Nov 29, 2016 at 2:33 PM, Ainhoa Benitez <abeni...@corenetworks.es>
wrote:

> This is the error log:
>
>
> [training@miguel ~]$ hplsql -e "SELECT * FROM movies where movieid <5";
> SLF4J: Class path contains multiple SLF4J bindings.
> SLF4J: Found binding in [jar:file:/opt/cloudera/
> parcels/CDH-5.9.0-1.cdh5.9.0.p0.23/jars/slf4j-log4j12-1.7.
> 5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in [jar:file:/opt/cloudera/
> parcels/CDH-5.9.0-1.cdh5.9.0.p0.23/jars/pig-0.12.0-cdh5.9.
> 0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in [jar:file:/opt/cloudera/
> parcels/CDH-5.9.0-1.cdh5.9.0.p0.23/jars/slf4j-simple-1.7.5.
> jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in [jar:file:/opt/cloudera/
> parcels/CDH-5.9.0-1.cdh5.9.0.p0.23/jars/avro-tools-1.7.6-
> cdh5.9.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
> explanation.
> SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
> 16/11/29 10:49:26 INFO jdbc.Utils: Supplied authorities: localhost:1
> 16/11/29 10:49:26 INFO jdbc.Utils: Resolved authority: localhost:1
> Open connection: jdbc:hive2://localhost:1 (690 ms)
> Starting query
> Unhandled exception in HPL/SQL
> java.sql.SQLException: Error while processing statement: FAILED: Execution
> Error, return code 1 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
> at org.apache.hive.jdbc.HiveStatement.execute(
> HiveStatement.java:279)
> at org.apache.hive.jdbc.HiveStatement.executeQuery(
> HiveStatement.java:375)
> at org.apache.hive.hplsql.Conn.executeQuery(Conn.java:63)
> at org.apache.hive.hplsql.Exec.executeQuery(Exec.java:554)
> at org.apache.hive.hplsql.Exec.executeQuery(Exec.java:563)
> at org.apache.hive.hplsql.Select.select(Select.java:74)
> at org.apache.hive.hplsql.Exec.visitSelect_stmt(Exec.java:993)
> at org.apache.hive.hplsql.Exec.visitSelect_stmt(Exec.java:51)
> at org.apache.hive.hplsql.HplsqlParser$Select_stmtContext.accept(
> HplsqlParser.java:14249)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.
> visitChildren(AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:985)
> at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:51)
> at org.apache.hive.hplsql.HplsqlParser$StmtContext.
> accept(HplsqlParser.java:998)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.
> visitChildren(AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.HplsqlBaseVisitor.visitBlock(
> HplsqlBaseVisitor.java:28)
> at org.apache.hive.hplsql.HplsqlParser$BlockContext.
> accept(HplsqlParser.java:438)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.
> visitChildren(AbstractParseTreeVisitor.java:70)
> at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:893)
> at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:51)
> at org.apache.hive.hplsql.HplsqlParser$ProgramContext.
> accept(HplsqlParser.java:381)
> at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(
> AbstractParseTreeVisitor.java:42)
> at org.apache.hive.hplsql.Exec.run(Exec.java:753)
> at org.apache.hive.hplsql.Exec.run(Exec.java:729)
> at org.apache.hive.hplsql.Hplsql.main(Hplsql.java:23)
> [training@miguel ~]$
> [training@miguel ~]$ ssh bea
> The authenticity of host 'bea (10.164.79.119)' can't be established.
> RSA key fingerprint is f8:ce:3a:a0:92:23:3d:e7:3f:e1:42:50:4b:17:de:7d.
> Are you sure you want to continue connecting (yes/no)? yes
> Warning: Permanently added 'bea,10.164.79.119' (RSA) to the list of known
> hosts.
> Last login: Mon Nov 28 17:50:38 2016 from 10.164.77.156
>
>
> 2016-11-29 12:31 GMT+01:00 Dmitry Tolpeko <dmtolp...@gmail.com>:
>
>> Please post as text message to user@ list.
>>
>> On Tue, Nov 29, 2016 at 2:02 PM, Ainhoa Benitez <abeni...@corenetworks.es
>> > wrote:
>>
>>> Hello Dmitry,
>>>
>>> I attach a screenshot with the error (On the top is the query I executed)
>>>
>>> Thanks
>>>
>>> Ainhoa
>>>
>>>
>>> 2016-11-29 11:30 GMT+01:00 Dmitry Tolpeko <dmtolp...@gmail.com>:
>>>
>>>> Ainhoa,
>>>>
>>>> Can you please post the e

Re: Issues regarding HPLSQL tool

[Dmitry Tolpeko]

Ainhoa,

Can you please post the entire script? Also try to add --trace option to
see which query was actually executed in the database.

Thanks,
Dmitry

On Tue, Nov 29, 2016 at 11:44 AM, Ainhoa Benitez 
wrote:

> Good morning,
>
> my name is Ainhoa. I am starting to use the HPLSQL tool and up to now it
> was working fine. However, my issue has to do when using a simple where
> clause, does this tool accept to write a where clause?
>
> For example, the problem is the following:
>
> -I have a table named "test" with several fields. I make a select clause
> such as: "select * from test limit 3" and it displays the result with no
> result. I try to make another query selecting particular fields of the
> table and no problem.
>
> Nevertheless, the issue comes when I try to do some of the following
> queries:
>
> -Select count(*) from test;
>
> -Select * from test where movieid <5;
>
> I do not have any idea why the problem keeps occuring or how to solve it.
> Is there any possibility to make a query with where conditions? If so, how?
> And about count all fields query, what  could the problem be?
>
> Thanks so much for your help!!!
>
> Ainhoa
>
> Segun el Articulo 5 de la L.O.P.D, le informamos que sus datos constan en
> un fichero titularidad de CORE NETWORKS, S.L., cuya finalidad es la gestion
> administrativa. Podra ejercer su derecho de acceso, rectificacion,
> cancelacion y oposicion mediante correo postal a C/ Serrano Galvache, 56,
> Edificio Olmo, 1 Planta - C.P. 28033 (MADRID), o enviando un correo
> electrónico a i...@corenetworks.es.
>

Re: How to pass the TestHplsqlDb test in hive-hplsql?

[Dmitry Tolpeko]

Please try to find them in ./ql/src/test/queries/clientpositive directory
(see topn.q file for example).

Thanks,
Dmitry

On Mon, Aug 1, 2016 at 11:34 AM, Zhenyi Zhao <emsn1...@gmail.com> wrote:

> Hi Dmitry,
>
> Thank you for your answer.  you said “*src* and *sample_07* are
> sample tables supplied with Hive” ， where can I find this infomation of
> these tables.
>
> Emerson
>
> 2016-08-01 16:27 GMT+08:00 Dmitry Tolpeko <dmtolp...@gmail.com>:
>
>> Hi Emerson,
>>
>> I did not commit TestHplsqlDb.java since Apache Pre-commit test starts
>> executing it, and I did not manage how to pass it (there are connection
>> errors). I can commit it as java_ to prevent execution, or someone can help
>> with connection errors.
>>
>> Some table DDLs are here:
>> https://github.com/apache/hive/blob/master/hplsql/src/test/queries/db/schema.sql
>>
>> *src* and *sample_07* are sample tables supplied with Hive. By the way,
>> *src* table is used in many Hive tests.
>>
>> Thanks,
>> Dmitry
>>
>>
>>
>> On Mon, Aug 1, 2016 at 6:48 AM, Zhenyi Zhao <emsn1...@gmail.com> wrote:
>>
>>> Hi all,
>>>
>>> There is a unit test class named "TestHplsqlDb.java"  I  found at
>>> http://www.hplsql.org/downloads/hplsql-0.3.17-src.zip. But I want to
>>> know why this class dose't exsit at
>>> https://github.com/apache/hive/tree/master/hplsql.
>>>
>>> Now I want to test the hplsql‘s compatibility with hive datasource, but
>>> the tests failed.
>>>
>>> And my question is how to pass the "TestHplsqlDb.java"  test , I found
>>> there are some tables required by the tests , like  src , sample_07, src_dt
>>>  and so on. But I could not find them to pass the test. How to init the
>>> test env?
>>>
>>> I' am waiting for your response. Thank you very much~
>>>
>>> Emerson
>>>
>>
>>
>

Re: How to pass the TestHplsqlDb test in hive-hplsql?

[Dmitry Tolpeko]

Hi Emerson,

I did not commit TestHplsqlDb.java since Apache Pre-commit test starts
executing it, and I did not manage how to pass it (there are connection
errors). I can commit it as java_ to prevent execution, or someone can help
with connection errors.

Some table DDLs are here:
https://github.com/apache/hive/blob/master/hplsql/src/test/queries/db/schema.sql

*src* and *sample_07* are sample tables supplied with Hive. By the way,
*src* table is used in many Hive tests.

Thanks,
Dmitry



On Mon, Aug 1, 2016 at 6:48 AM, Zhenyi Zhao  wrote:

> Hi all,
>
> There is a unit test class named "TestHplsqlDb.java"  I  found at
> http://www.hplsql.org/downloads/hplsql-0.3.17-src.zip. But I want to know
> why this class dose't exsit at
> https://github.com/apache/hive/tree/master/hplsql.
>
> Now I want to test the hplsql‘s compatibility with hive datasource, but
> the tests failed.
>
> And my question is how to pass the "TestHplsqlDb.java"  test , I found
> there are some tables required by the tests , like  src , sample_07, src_dt
>  and so on. But I could not find them to pass the test. How to init the
> test env?
>
> I' am waiting for your response. Thank you very much~
>
> Emerson
>

Re: if else condition in hive

[Dmitry Tolpeko]

Hi Raj,

Hive hpl/sql component can be used to achieve such functionality (see
www.hplsql.org for docs), now you can run it as a separate tool, and I hope
some day it will be available from Hive or Beeline CLI.

Thanks,
Dmitry

On Tue, Jun 21, 2016 at 2:22 PM, raj hive  wrote:

> Hi friends,
>
> INSERT,UPDATE,DELETE commands are working fine in my Hive environment
> after changing the configuration and all. Now, I have to execute a query
> like below sql  in hive.
>
> If exists(select * from tablename where columnname=something)
>   update table set column1=something where columnname=something
>  else
>   insert into tablename values ...
>
> Can any one help me how to do it in Hive?
>
> Thanks
> Raj
>

Re: SELECT without FROM

[Dmitry Tolpeko]

Thank you, Shannon!

On Fri, Mar 11, 2016 at 4:47 AM, Shannon Ladymon <slady...@gmail.com> wrote:

> It looks like FROM was made optional in Hive 0.13.0 with HIVE-4144
> <https://issues.apache.org/jira/browse/HIVE-4144> (thanks Alan Gates to
> pointing us to the grammar file and Sushanth Sowyman for helping track this
> down). A note has been added to the wiki
> <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-SelectSyntax>
> about this.
>
> Dmitry, you said it didn't work in your Hive 0.13 version, but it seems
> like the patch was applied to 0.13.0. You might want to take a look at
> HIVE-4144 and see if that patch was applied to your version.
>
> -Shannon Ladymon
>
> On Wed, Mar 9, 2016 at 2:13 AM, Mich Talebzadeh <mich.talebza...@gmail.com
> > wrote:
>
>> hm. Certainly it worked if I recall correctly on 0.14, 1.2.1 and now on 2
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * 
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>>
>> On 9 March 2016 at 10:08, Dmitry Tolpeko <dmtolp...@gmail.com> wrote:
>>
>>> Not sure. It does not work in my Hive 0.13 version.
>>>
>>> On Wed, Mar 9, 2016 at 1:06 PM, Mich Talebzadeh <
>>> mich.talebza...@gmail.com> wrote:
>>>
>>>> I believe it has always been there
>>>>
>>>> Dr Mich Talebzadeh
>>>>
>>>>
>>>>
>>>> LinkedIn * 
>>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>>
>>>>
>>>>
>>>> http://talebzadehmich.wordpress.com
>>>>
>>>>
>>>>
>>>> On 9 March 2016 at 09:58, Dmitry Tolpeko <dmtolp...@gmail.com> wrote:
>>>>
>>>>> Mich,
>>>>>
>>>>> I now that. I just want to trace when it was added to Hive.
>>>>>
>>>>> Dmitry
>>>>>
>>>>> On Wed, Mar 9, 2016 at 12:56 PM, Mich Talebzadeh <
>>>>> mich.talebza...@gmail.com> wrote:
>>>>>
>>>>>> ASAIK any database does that!
>>>>>>
>>>>>> 1> set nocount on
>>>>>> 2> select @@version
>>>>>> 3> select 1 + 1
>>>>>> 4> go
>>>>>>
>>>>>>
>>>>>>
>>>>>>  
>>>>>> -------
>>>>>>  Adaptive Server Enterprise/15.7/EBF 21708 SMP SP110
>>>>>> /P/x86_64/Enterprise Linux/ase157sp11x/3546/64-bit/FBO/Fri Nov  8 
>>>>>> 05:39:38
>>>>>> 2013
>>>>>>
>>>>>>  ---
>>>>>>2
>>>>>>
>>>>>>
>>>>>> Dr Mich Talebzadeh
>>>>>>
>>>>>>
>>>>>>
>>>>>> LinkedIn * 
>>>>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>>> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>>>>
>>>>>>
>>>>>>
>>>>>> http://talebzadehmich.wordpress.com
>>>>>>
>>>>>>
>>>>>>
>>>>>> On 9 March 2016 at 09:50, Dmitry Tolpeko <dmtolp...@gmail.com> wrote:
>>>>>>
>>>>>>> I noticed that Hive allows you to execute SELECT without FROM clause
>>>>>>> (tested in Hive 0.14, Hive 1.2.1):
>>>>>>>
>>>>>>> SELECT 1+1;
>>>>>>>
>>>>>>> In which version was it added (is there a Jira)? I see that it is
>>>>>>> not mentioned in docs
>>>>>>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
>>>>>>>
>>>>>>> So the question whether it is official and will not be removed in
>>>>>>> future.
>>>>>>>
>>>>>>> Thanks,
>>>>>>>
>>>>>>> Dmitry
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

Re: [ANNOUNCE] New Hive Committer - Wei Zheng

[Dmitry Tolpeko]

Congratulations, Wei!

On Thu, Mar 10, 2016 at 5:48 AM, Chao Sun  wrote:

> Congratulations!
>
> On Wed, Mar 9, 2016 at 6:44 PM, Prasanth Jayachandran <
> pjayachand...@hortonworks.com> wrote:
>
>> Congratulations Wei!
>>
>> On Mar 9, 2016, at 8:43 PM, Sergey Shelukhin > > wrote:
>>
>> Congrats!
>>
>> From: Szehon Ho >
>> Reply-To: "user@hive.apache.org" <
>> user@hive.apache.org>
>> Date: Wednesday, March 9, 2016 at 17:40
>> To: "user@hive.apache.org" <
>> user@hive.apache.org>
>> Cc: "d...@hive.apache.org" <
>> d...@hive.apache.org>, "w...@apache.org
>> " >
>> Subject: Re: [ANNOUNCE] New Hive Committer - Wei Zheng
>>
>> Congratulations Wei!
>>
>> On Wed, Mar 9, 2016 at 5:26 PM, Vikram Dixit K  vik...@apache.org>> wrote:
>> The Apache Hive PMC has voted to make Wei Zheng a committer on the Apache
>> Hive Project. Please join me in congratulating Wei.
>>
>> Thanks
>> Vikram.
>>
>>
>>
>

Re: SELECT without FROM

[Dmitry Tolpeko]

Not sure. It does not work in my Hive 0.13 version.

On Wed, Mar 9, 2016 at 1:06 PM, Mich Talebzadeh <mich.talebza...@gmail.com>
wrote:

> I believe it has always been there
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> On 9 March 2016 at 09:58, Dmitry Tolpeko <dmtolp...@gmail.com> wrote:
>
>> Mich,
>>
>> I now that. I just want to trace when it was added to Hive.
>>
>> Dmitry
>>
>> On Wed, Mar 9, 2016 at 12:56 PM, Mich Talebzadeh <
>> mich.talebza...@gmail.com> wrote:
>>
>>> ASAIK any database does that!
>>>
>>> 1> set nocount on
>>> 2> select @@version
>>> 3> select 1 + 1
>>> 4> go
>>>
>>>
>>>
>>>  
>>> ---
>>>  Adaptive Server Enterprise/15.7/EBF 21708 SMP SP110
>>> /P/x86_64/Enterprise Linux/ase157sp11x/3546/64-bit/FBO/Fri Nov  8 05:39:38
>>> 2013
>>>
>>>  ---
>>>2
>>>
>>>
>>> Dr Mich Talebzadeh
>>>
>>>
>>>
>>> LinkedIn * 
>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>
>>>
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>>
>>> On 9 March 2016 at 09:50, Dmitry Tolpeko <dmtolp...@gmail.com> wrote:
>>>
>>>> I noticed that Hive allows you to execute SELECT without FROM clause
>>>> (tested in Hive 0.14, Hive 1.2.1):
>>>>
>>>> SELECT 1+1;
>>>>
>>>> In which version was it added (is there a Jira)? I see that it is not
>>>> mentioned in docs
>>>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
>>>>
>>>> So the question whether it is official and will not be removed in
>>>> future.
>>>>
>>>> Thanks,
>>>>
>>>> Dmitry
>>>>
>>>
>>>
>>
>

Re: SELECT without FROM

[Dmitry Tolpeko]

Mich,

I now that. I just want to trace when it was added to Hive.

Dmitry

On Wed, Mar 9, 2016 at 12:56 PM, Mich Talebzadeh <mich.talebza...@gmail.com>
wrote:

> ASAIK any database does that!
>
> 1> set nocount on
> 2> select @@version
> 3> select 1 + 1
> 4> go
>
>
>
>  
> ---
>  Adaptive Server Enterprise/15.7/EBF 21708 SMP SP110 /P/x86_64/Enterprise
> Linux/ase157sp11x/3546/64-bit/FBO/Fri Nov  8 05:39:38
> 2013
>
>  ---
>2
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> On 9 March 2016 at 09:50, Dmitry Tolpeko <dmtolp...@gmail.com> wrote:
>
>> I noticed that Hive allows you to execute SELECT without FROM clause
>> (tested in Hive 0.14, Hive 1.2.1):
>>
>> SELECT 1+1;
>>
>> In which version was it added (is there a Jira)? I see that it is not
>> mentioned in docs
>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
>>
>> So the question whether it is official and will not be removed in future.
>>
>> Thanks,
>>
>> Dmitry
>>
>
>

SELECT without FROM

[Dmitry Tolpeko]

I noticed that Hive allows you to execute SELECT without FROM clause
(tested in Hive 0.14, Hive 1.2.1):

SELECT 1+1;

In which version was it added (is there a Jira)? I see that it is not
mentioned in docs
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

So the question whether it is official and will not be removed in future.

Thanks,

Dmitry

Re: Hive-2.0.1 Release date

[Dmitry Tolpeko]

Mich,

To avoid confusion: HPLSQL is shipped with Hive 2.0 (and will be shipped
with all further versions). So you can use it with Hive on Tez, Hive on MR,
and Hive on Spark. But now it is a separate tool: hplsql (like hive CLI,
beeline CLI). What Alan meant is that it is not integrated to Hive
*parser*, so there are 2 parsers now: Hive SQL and HPLSQL.

HPLSQL acts as a SQL-skin, supports many SQL and PL/SQL dialects in order
to avoid code conversions (there are a lot of legacy code to be ported to
Hive), so it is verbose. I am not sure if it makes sense to merge it with
Hive SQL parser, but definitely integration with Hive/beeline CLI,
metastore (persistent store of procedures/functions), JDBC/ODBC will be
helpful.

Alan, I am ready to work on this, but we need to define the direction first
so we have good and consistent design.

Thanks,

Dmitry

On Tue, Mar 1, 2016 at 3:08 AM, Mich Talebzadeh 
wrote:

> Thanks. I believe Alan Gate mentioned that HPLSQL is not yet integrated
> into Hive 2.0.May be later?
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> On 1 March 2016 at 00:05, Sergey Shelukhin  wrote:
>
>> HPLSQL is available as part of Hive 2.0. I am not sure to which extent
>> the integration goes as I wasn’t involved in that work.
>> As far as I understand HPLSQL and Hive on Spark are kind of orthogonal…
>>
>> Hive 2.0.1 is purely a bug fix release for Hive 2.0; Hive 2.1 will be the
>> next feature release if some major feature is missing.
>>
>> From: Mich Talebzadeh 
>> Reply-To: "user@hive.apache.org" 
>> Date: Monday, February 29, 2016 at 15:53
>> To: "user@hive.apache.org" 
>> Subject: Re: Hive-2.0.1 Release date
>>
>> Hi Sergey,
>>
>> Will HPLSQL be part of 2.0.1.release?
>>
>> I am using 2.0 and found Hive on Spark to be much more stable.
>>
>> Thanks
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * 
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> *
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>>
>> On 29 February 2016 at 23:46, Sergey Shelukhin 
>> wrote:
>>
>>> Hi. It will be released when some critical mass of bugfixes is
>>> accumulated. We already found some issues that would be nice to fix, so it
>>> may be some time in March. Is there a particular fix that interests you?
>>>
>>> From: Oleksiy MapR 
>>> Reply-To: "user@hive.apache.org" 
>>> Date: Monday, February 29, 2016 at 00:43
>>> To: "user@hive.apache.org" 
>>> Subject: Hive-2.0.1 Release date
>>>
>>> Hi all!
>>>
>>> Are you planing to release Hive-2.0.1? If yes, when it probably may be?
>>>
>>> Thanks,
>>> Oleksiy.
>>>
>>
>>
>

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**

[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: [ANNOUNCE] Apache Hive 2.0.0 Released

[Dmitry Tolpeko]

Great! Thank you, Sergey!

On Tue, Feb 16, 2016 at 10:44 PM, Sergey Shelukhin 
wrote:

> The Apache Hive team is proud to announce the the release of Apache Hive
> version 2.0.0.
>
> The Apache Hive (TM) data warehouse software facilitates querying and
> managing large datasets residing in distributed storage. Built on top of
> Apache Hadoop (TM), it provides:
>
> * Tools to enable easy data extract/transform/load (ETL)
>
> * A mechanism to impose structure on a variety of data formats
>
> * Access to files stored either directly in Apache HDFS (TM) or in other
> data storage systems such as Apache HBase (TM)
>
> * Query execution via Apache Hadoop MapReduce and Apache Tez frameworks.
>
> For Hive release details and downloads, please visit:
> https://hive.apache.org/downloads.html
>
> Hive 2.0.0 Release Notes are available here:
> https://issues.apache.org/jira/secure/ReleaseNote.jspa?version=12332641
> jectId=12310843
>
> We would like to thank the many contributors who made this release
> possible.
>
> Regards,
>
> The Apache Hive Team
>
>
>

Re: January Hive User Group Meeting

[Dmitry Tolpeko]

If possible please share the slides after the meeting.

Thank you!

Dmitry

On Wed, Jan 20, 2016 at 8:45 PM, Xuefu Zhang  wrote:

> Hi all,
>
> As a reminder, the meeting will be held tomorrow as scheduled. Please
> refer to the meetup page[1] for details. Looking forward to meeting you all!
>
> Thanks,
> Xuefu
>
> [1] http://www.meetup.com/Hive-User-Group-Meeting/events/227463783/
>
> On Wed, Dec 16, 2015 at 3:38 PM, Xuefu Zhang  wrote:
>
>> Dear Hive users and developers,
>>
>> Hive community is considering a user group meeting[1] January 21, 2016
>> at Cloudera facility in Palo Alto, CA. This will be a great opportunity
>> for vast users and developers to find out what's happening in the
>> community and share each other's experience with Hive. Therefore, I'd urge
>> you to attend the meetup. Please RSVP and the list will be closed a few
>> days ahead of the event.
>>
>> At the same time, I'd like to solicit light talks (15 minutes max) from
>> users and developers. If you have a proposal, please let me or Thejas know.
>> Your participation is greatly appreciated.
>>
>> Sincerely,
>> Xuefu
>>
>> [1] http://www.meetup.com/Hive-User-Group-Meeting/events/227463783/
>>
>
>

Re: HPLSQL Error

[Dmitry Tolpeko]

Pavan,

Hadoop jars are not in the CLASSPATH. You can edit hplsql file (this is a
bash script) and specify proper locations for your Hadoop installation.

Dmitry

On Mon, Dec 14, 2015 at 6:34 AM, Pavan Kumar  wrote:

> ./hplsql -e "select * from txn_ds_platter limit 1";
> Exception in thread "main" java.lang.NoClassDefFoundError:
> org/apache/hadoop/conf/Configuration
> at java.lang.ClassLoader.defineClass1(Native Method)
> at java.lang.ClassLoader.defineClass(ClassLoader.java:800)
> at
> java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
> at java.net.URLClassLoader.defineClass(URLClassLoader.java:449)
> at java.net.URLClassLoader.access$100(URLClassLoader.java:71)
> at java.net.URLClassLoader$1.run(URLClassLoader.java:361)
> at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
> at java.security.AccessController.doPrivileged(Native Method)
> at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
> at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
> at org.apache.hive.hplsql.Exec.init(Exec.java:673)
> at org.apache.hive.hplsql.Exec.run(Exec.java:635)
> at org.apache.hive.hplsql.Hplsql.main(Hplsql.java:23)
> Caused by: java.lang.ClassNotFoundException:
> org.apache.hadoop.conf.Configuration
> at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
> at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
> at java.security.AccessController.doPrivileged(Native Method)
> at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
> at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
> ... 15 more
>
>
> Please help me..
> Thanking You.
>

Re: Building Rule Engine/ Rule Transformation

[Dmitry Tolpeko]

Mahender,

Please try Hive HPL/SQL tool first. It will be included to Hive 2.0, but
now it is available at hplsql.org. The tool attempts to implement
procedural SQL for Hive. It is supporting SQL Server syntax as well. If
there are any issues please contact me by email.

Dmitry

On Sat, Nov 28, 2015 at 11:26 PM, Mahender Sarangam <
mahender.bigd...@outlook.com> wrote:

>  Hi team,
>
> We need expert input to discuss how to implement Rule engine in hive. Do
> you have any references available to implement rule in hive/pig.
>
>
> We are migrating our Stored Procedures into Multiple Hive query but it is
> becoming complex in maintenance,  Hive is not Procedural Language, so we
> could not write IF ELSE logic or any kind of procedural language. Can any
> one suggest us which HDP technology can be helpful for Procedural language
> replacement, we are thinking of PIG, Can it  becomes best example to
> perform rule/data transformation.
>
> Our data is in Structure format table with around 250 columns, We are
> having Rules like Update Columns based on lookup table values, delete rows
> which doesn't satisfy the condition, update statement include update of
> multiple columns like case etc and some date conversions columns. Suggest
> us best way to implement this rule engine.  previously we have used our SQL
> Server for Rule engine, now we have migrated application to Big Data, we
> are looking for any references available to perform this rule
> transformation.
>
>
> Some of our Finding are
>
>
>1. Make use of Hive Stream
>2. Writing PIG.
>
>
>
> We are .NET developers, Can we think of writing .EXE application and
> stream row wise data to .EXE and apply rules on top of  each row. Will it
> be nicer solution or Is it better to implement in PIG, but implementing
> doesn't fetch me much benefit when compared with Hive. Can you please
> comment on above approaches please.
>
> Thanks,
> Mahender
>

Re: Better way to do UDF's for Hive

[Dmitry Tolpeko]

In case of single string input Java UDF can be easier to write: accept
string parameter, lookup hash map and return. In case of Python you have to
use TRANSFORM clause and handle all columns, so it will be hard to reuse
your Python script as the code may depend on the column position.

One other possible option is to move state_from_city data into a separate
table and use a map-side join.

Dmitry

On Thu, Oct 1, 2015 at 4:11 PM, Daniel Lopes 
wrote:

> Hi,
>
> I'd like to know the good way to do a a UDF for a single field, like
>
> SELECT
>   tbl.id AS id,
>   tbl.name AS name,
>   tbl.city AS city,
>   state_from_city(tbl.city) AS state
> FROM
>   my_db.my_table tbl;
>
> *Native Java*? *Python *over *Hadoop* *Streaming*?
>
> I prefer Python, but I don't know how to do in a good way.
>
> Thanks,
>
> *Daniel Lopes, B.Eng*
> Data Scientist - BankFacil
> CREA/SP 5069410560
> 
> Mob +55 (18) 99764-2733 
> Ph +55 (11) 3522-8009
> http://about.me/dannyeuu
>
> Av. Nova Independência, 956, São Paulo, SP
> Bairro Brooklin Paulista
> CEP 04570-001
> https://www.bankfacil.com.br
>
>

Re: Organising Hive Scripts

[Dmitry Tolpeko]

Charles,

Not sure what you can do in Hive CLI right now, but consider a new Hive
HPL/SQL component that will be included to new Hive versions and that
currently you can compile and run separately, see
https://github.com/apache/hive/tree/master/hplsql or www.hplsql.org

It supports include files, user defined procedures and functions, exception
based exception handling, flow of control statements and so on. It is new,
under active development so any feedback is welcome.

Dmitry

On Fri, Sep 11, 2015 at 8:32 PM, Charles Mean 
wrote:

> Hello,
>
> I am working with a huge hive script that I would like to improve its
> organisation to a better maintenance in the future.
> Looking into this issue, I did not found any kind of include or something
> to split my script into some smaller parts.
> So, Is there some sort of pattern that is implemented to improve script
> structure ?
>

Re: PL/HQL - Procedural SQL-on-Hadoop

[Dmitry Tolpeko]

Hi everyone,

PL/HQL 0.3.1 released today. There are some features I hope you find
interesting:

1) Stored procedures and SQL user-defined functions

CREATE PROCEDURE and CREATE FUNCTION statements are supported so you can
define procedures and functions in your scripts, and when added to .plhqlrc
they can look as real stored objects.

2) Calling PL/HQL functions from Hive queries

You can use PL/HQL functions in Hive queries. PL/HQL automatically puts
function definitions to distributed cache, registers Hive UDF and executes
functions. You can also call PL/HQL functions from Hive CLI if you manually
register UDF and execute ADD FILE for PL/HQL scripts. See,
http://plhql.org/udf

3) Multiple connections

You can work with multiple databases from a single PL/HQL script. For
example, run queries in Hive, but lookup and log to RDBMS.
MAP OBJECT statement allows you to assign a table to a connection (again
you can do that in plhqlrc). Then PL/HQL will automatically use appropriate
connection.

In the next releases, procedural features will be extended to cover more
existing dialects, on-the-fly SQL conversion will be improved. Also
federation features will be added - like saving query results to RDBMS
(actually any JDBC source), use RDBMS tables as map-join tables in Hive
queries and so on.

Early adopters are very welcome. Please contact me at dmtolp...@gmail.com

Thanks,

Dmitry Tolpeko
PL/HQL - Procedural SQL-on-Hadoop, www.plhql.org


On Fri, Mar 6, 2015 at 6:23 PM, Ronak Bhatt ronakb...@gmail.com wrote:

 plhql is a great initiative and will certainly help with more adoption of
 the eco-system...Good job Dimtry

 -r

 *-r*




 On Fri, Mar 6, 2015 at 1:07 AM, Dmitry Tolpeko dmtolp...@gmail.com
 wrote:

 run dos2unix for plhql file. Sorry for this issue, it will be fixed.

 Dmitry

 On Fri, Mar 6, 2015 at 11:27 AM, r7raul1...@163.com r7raul1...@163.com
 wrote:

 I type plhql --version     but meet error bash: /usr/plhql/plhql:
 /bin/bash^M: bad interpreter: No such file or directory

 --
 r7raul1...@163.com


 *From:* Dmitry Tolpeko dmtolp...@gmail.com
 *Date:* 2015-02-28 04:36
 *To:* user user@hive.apache.org
 *Subject:* PL/HQL - Procedural SQL-on-Hadoop
 Let me introduce PL/HQL, an open source tool that implements procedural
 SQL on Hadoop. It is going to support all major procedural syntaxes. The
 tool can be used with any SQL-on-Hadoop solution.

 Motivation:

- Writing the driver code using well-known procedural SQL (not bash)
that enables Hadoop to even more wider audience
- Allowing dynamic SQL, iterations, flow-of-control and SQL
exception handling
- Facilitating migration of RDBMS workload to Hadoop

 Plans (besides extending syntax):


- Supporting CREATE PROCEDURE/FUNCTION/PACKAGE to reuse code
- Allowing connections to multiple databases (i.e. lookup tables in
relational databases)
- On-the-fly SQL conversion (SELECT i.e.), compatibility layers

 More details can be found at http://www.plhql.org/

 It is just the first release PL/HQL 0.01 to show that such project
 exists and get any initial feedback.

 Thank you,

 Dmitry Tolpeko

Re: Hive documentation update for isNull, isNotNull etc.

[Dmitry Tolpeko]

I also recently realized that NVL function is available, but not documented
:(

Dmitry Tolpeko

--
PL/HQL - Procedural SQL-on-Hadoop - www.plhql.org


On Sat, Apr 18, 2015 at 12:22 AM, Moore, Douglas 
douglas.mo...@thinkbiganalytics.com wrote:

   I'm having major trouble finding documentation on hive functions isNull
 and isNotNull.
 At first I was assuming the function just wasn't available, now I believe
 these functions are not documented.

  I believe that the LanguageManual+UDF#LanguageManualUDF-Built-inFunctions
 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inFunctions
  is
 the right wiki page:

  Interestingly, I found a HIVE-521 (resolved in 0.4) to be the first
 reference for isNull within JIRA.

  Is Lefty volunteering for this? If not I can make the edits if given
 permissions.

  Thanks
 Douglas

Re: PL/HQL - Procedural SQL-on-Hadoop

[Dmitry Tolpeko]

run dos2unix for plhql file. Sorry for this issue, it will be fixed.

Dmitry

On Fri, Mar 6, 2015 at 11:27 AM, r7raul1...@163.com r7raul1...@163.com
wrote:

 I type plhql --version     but meet error bash: /usr/plhql/plhql:
 /bin/bash^M: bad interpreter: No such file or directory

 --
 r7raul1...@163.com


 *From:* Dmitry Tolpeko dmtolp...@gmail.com
 *Date:* 2015-02-28 04:36
 *To:* user user@hive.apache.org
 *Subject:* PL/HQL - Procedural SQL-on-Hadoop
 Let me introduce PL/HQL, an open source tool that implements procedural
 SQL on Hadoop. It is going to support all major procedural syntaxes. The
 tool can be used with any SQL-on-Hadoop solution.

 Motivation:

- Writing the driver code using well-known procedural SQL (not bash)
that enables Hadoop to even more wider audience
- Allowing dynamic SQL, iterations, flow-of-control and SQL exception
handling
- Facilitating migration of RDBMS workload to Hadoop

 Plans (besides extending syntax):


- Supporting CREATE PROCEDURE/FUNCTION/PACKAGE to reuse code
- Allowing connections to multiple databases (i.e. lookup tables in
relational databases)
- On-the-fly SQL conversion (SELECT i.e.), compatibility layers

 More details can be found at http://www.plhql.org/

 It is just the first release PL/HQL 0.01 to show that such project exists
 and get any initial feedback.

 Thank you,

 Dmitry Tolpeko

Re: PL/HQL - Procedural SQL-on-Hadoop

[Dmitry Tolpeko]

Venkat,

The goal of this project is to execute existing PL/SQL in Hive as much as
possible, not to migrate. In case when some design restrictions are faced
the code has to be redesigned, but hopefully most of the remaining code
remained untouched, no need to convert everything to bash/Python etc.

Dmitry

On Tue, Mar 3, 2015 at 4:39 AM, Venkat, Ankam ankam.ven...@centurylink.com
wrote:

  Is there a simple way to migrate from PL/SQL to PL/HQL?



 Regards,

 Venkat



 *From:* Dmitry Tolpeko [mailto:dmtolp...@gmail.com]
 *Sent:* Friday, February 27, 2015 1:36 PM
 *To:* user@hive.apache.org
 *Subject:* PL/HQL - Procedural SQL-on-Hadoop



 Let me introduce PL/HQL, an open source tool that implements procedural
 SQL on Hadoop. It is going to support all major procedural syntaxes. The
 tool can be used with any SQL-on-Hadoop solution.



 Motivation:

- Writing the driver code using well-known procedural SQL (not bash)
that enables Hadoop to even more wider audience
- Allowing dynamic SQL, iterations, flow-of-control and SQL exception
handling
- Facilitating migration of RDBMS workload to Hadoop

  Plans (besides extending syntax):



- Supporting CREATE PROCEDURE/FUNCTION/PACKAGE to reuse code
- Allowing connections to multiple databases (i.e. lookup tables in
relational databases)
- On-the-fly SQL conversion (SELECT i.e.), compatibility layers

  More details can be found at http://www.plhql.org/



 It is just the first release PL/HQL 0.01 to show that such project exists
 and get any initial feedback.



 Thank you,



 Dmitry Tolpeko
   This communication is the property of CenturyLink and may contain
 confidential or privileged information. Unauthorized use of this
 communication is strictly prohibited and may be unlawful. If you have
 received this communication in error, please immediately notify the sender
 by reply e-mail and destroy all copies of the communication and any
 attachments.

Re: PL/HQL - Procedural SQL-on-Hadoop

[Dmitry Tolpeko]

Are there any github link for source code?
Will be added soon

Any there any documents for writing the sproc?
Please see the reference at plhql.org. Briefly, just put your code without
enclosing CREATE PROC/FUNC statements (they will be supported soon)

What's performance than regular HQL statements?
It is comparable as the tool holds Hive JDBC connection and just submits
queries (but faster comparing with lauching hive from bash for each query)

Is there a plan to support spark SQL?
Now you can use any SQL-on-Hadoop configuring a proper JDBC connection.

Dmitry

On Sat, Feb 28, 2015 at 3:45 AM, DU DU will...@gmail.com wrote:

 Are there any github link for source code?
 Any there any documents for writing the sproc?
 What's performance than regular HQL statements?
 Is there a plan to support spark SQL?
 will

 On Fri, Feb 27, 2015 at 3:36 PM, Dmitry Tolpeko dmtolp...@gmail.com
 wrote:

 Let me introduce PL/HQL, an open source tool that implements procedural
 SQL on Hadoop. It is going to support all major procedural syntaxes. The
 tool can be used with any SQL-on-Hadoop solution.

 Motivation:

- Writing the driver code using well-known procedural SQL (not bash)
that enables Hadoop to even more wider audience
- Allowing dynamic SQL, iterations, flow-of-control and SQL exception
handling
- Facilitating migration of RDBMS workload to Hadoop

 Plans (besides extending syntax):


- Supporting CREATE PROCEDURE/FUNCTION/PACKAGE to reuse code
- Allowing connections to multiple databases (i.e. lookup tables in
relational databases)
- On-the-fly SQL conversion (SELECT i.e.), compatibility layers

 More details can be found at http://www.plhql.org/

 It is just the first release PL/HQL 0.01 to show that such project exists
 and get any initial feedback.

 Thank you,

 Dmitry Tolpeko




 --
 Thanks,
 Will

PL/HQL - Procedural SQL-on-Hadoop

[Dmitry Tolpeko]

Let me introduce PL/HQL, an open source tool that implements procedural SQL
on Hadoop. It is going to support all major procedural syntaxes. The tool
can be used with any SQL-on-Hadoop solution.

Motivation:

   - Writing the driver code using well-known procedural SQL (not bash)
   that enables Hadoop to even more wider audience
   - Allowing dynamic SQL, iterations, flow-of-control and SQL exception
   handling
   - Facilitating migration of RDBMS workload to Hadoop

Plans (besides extending syntax):


   - Supporting CREATE PROCEDURE/FUNCTION/PACKAGE to reuse code
   - Allowing connections to multiple databases (i.e. lookup tables in
   relational databases)
   - On-the-fly SQL conversion (SELECT i.e.), compatibility layers

More details can be found at http://www.plhql.org/

It is just the first release PL/HQL 0.01 to show that such project exists
and get any initial feedback.

Thank you,

Dmitry Tolpeko

Re: Multitable insert does not work with ORCFiles

[Dmitry Tolpeko]

Thank you, Prasanth. If you file a Jira please post its number here for
tracking.

Dmitry

On Fri, Oct 17, 2014 at 8:59 PM, Prasanth Jayachandran 
pjayachand...@hortonworks.com wrote:

 Hi Dmitry

 Yes. I can confirm that this is an issue. But the issue seems to be with
 vectorized execution and not with ORC. If I disable vectorization the query
 seems to work fine even for ORC tables. I will dig through the JIRAs to see
 if this is a known issue else I will file a bug. Thanks for reporting.

 - Prasanth


 On Thu, Oct 16, 2014 at 1:30 AM, Dmitry Tolpeko dmtolp...@gmail.com
 wrote:

 Hi,

 With ORCFiles data inserted into the first table only. Hive 0.13 test:

  create table orc1
   stored as orc
   tblproperties (orc.compress=ZLIB)
   as
 select rn
 from
 (
   select cast(1 as int) as rn from dual
   union all
   select cast(100 as int) as rn from dual
   union all
   select cast(1 as int) as rn from dual
 ) t;


 create table orc_rn1 (rn int);
 create table orc_rn2 (rn int);
 create table orc_rn3 (rn int);

 from orc1 a
 insert overwrite table orc_rn1 select a.* where a.rn  100
 insert overwrite table orc_rn2 select a.* where a.rn = 100 and a.rn 
 1000
 insert overwrite table orc_rn3 select a.* where a.rn = 1000;

 select * from orc_rn1
 union all
 select * from orc_rn2
 union all
 select * from orc_rn3;
  
 Result (only one row):
 1

 If I change orc1 to SequenceFile everything works fine (the last query
 returns 3 rows). Can please someone check this? Is it a known issue?

 Thanks,

 Dmitry Tolpeko



 CONFIDENTIALITY NOTICE
 NOTICE: This message is intended for the use of the individual or entity
 to which it is addressed and may contain information that is confidential,
 privileged and exempt from disclosure under applicable law. If the reader
 of this message is not the intended recipient, you are hereby notified that
 any printing, copying, dissemination, distribution, disclosure or
 forwarding of this communication is strictly prohibited. If you have
 received this communication in error, please contact the sender immediately
 and delete it from your system. Thank You.

Multitable insert does not work with ORCFiles

[Dmitry Tolpeko]

Hi,

With ORCFiles data inserted into the first table only. Hive 0.13 test:

create table orc1
  stored as orc
  tblproperties (orc.compress=ZLIB)
  as
select rn
from
(
  select cast(1 as int) as rn from dual
  union all
  select cast(100 as int) as rn from dual
  union all
  select cast(1 as int) as rn from dual
) t;


create table orc_rn1 (rn int);
create table orc_rn2 (rn int);
create table orc_rn3 (rn int);

from orc1 a
insert overwrite table orc_rn1 select a.* where a.rn  100
insert overwrite table orc_rn2 select a.* where a.rn = 100 and a.rn  1000
insert overwrite table orc_rn3 select a.* where a.rn = 1000;

select * from orc_rn1
union all
select * from orc_rn2
union all
select * from orc_rn3;

Result (only one row):
1

If I change orc1 to SequenceFile everything works fine (the last query
returns 3 rows). Can please someone check this? Is it a known issue?

Thanks,

Dmitry Tolpeko