Difficulties in executing lag function

Hi, I have a orders table created in Hive. create table orders ( order_date TIMESTAMP, product_id INT, qty INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; I wish to execute a query similar to the following : select product_id, order_date, lag (order_date,1) over (ORDER BY order_date) AS pr

Re: Question regarding nested complex data type

look at it the other around if you want. knowing an array of a two element struct is topologically the same as a map - they darn well better be the same. :) On Thu, Jun 20, 2013 at 7:00 PM, Dean Wampler wrote: > It's not as "simple" as it seems, as I discovered yesterday, to my > surprise. I

Re: Question regarding nested complex data type

It's not as "simple" as it seems, as I discovered yesterday, to my surprise. I created a table like this: CREATE TABLE t ( name STRING, stuff ARRAY>); I then used an insert statement to see how Hive would store the records, so I could populate the real table with another process. Hive used

Re: INSERT non-static data to array?

My understanding is that LATERAL VIEW goes the other direction: takes an array and makes it into separate rows.  I use that a lot.  But I also need to go the other way sometimes: take a bunch of rows and squeeze them down into an array.  Please correct me if I'm missing something. ___

Re: INSERT non-static data to array?

i think you could select into as sub query and then use lateral view.not exactly the same but something similar could be done,. On Thursday, June 20, 2013, Michael Malak wrote: > I've created > https://issues.apache.org/jira/browse/HIVE-4771 > > to track this issue. > > > - Original Message

Re: INSERT non-static data to array?

I've created https://issues.apache.org/jira/browse/HIVE-4771 to track this issue. - Original Message - From: Michael Malak To: "user@hive.apache.org" Cc: Sent: Wednesday, June 19, 2013 2:35 PM Subject: Re: INSERT non-static data to array? The example code for inline_table() there has

Re: Run queries from external files as subqueries

Quick and dirty way to do such thing would be to use some kind of preprocessor. To avoid writing one, you could use e.g. the one from GCC, with just a little help from sed: gcc -E -x c query.hql -o- | sed '/#/d' > preprocessed.hql hive -f preprocessed.hql Where query.hql can contain for e

Re: Run queries from external files as subqueries

I am afraid that there is no automatic way of doing so. But that would be the same answer whether the question is about hive or any relational database. (I would be glad to have counter examples.) You might want to look at oozie in order to manage worflow. But the creation of the worflow is manual

Run queries from external files as subqueries

Hi, While working on some complex queries with multiple level of subqueries, I'm wonder if it is possible in Hive to refactor these subqueries into different files and instruct the enclosing query to execute these files. This way these subqueries can potentially be reused by other questions or j

Re: "show table" throwing strange error

Can u try from your ubuntu command prompt $> hive -e "show tables" From: Mohammad Tariq mailto:donta...@gmail.com>> Reply-To: "user@hive.apache.org" mailto:user@hive.apache.org>> Date: Thursday, June 20, 2013 4:28 AM To: user mailto:user@hive.apache.org>> Subject: Re:

Re: Hive External Table issue

i agree. conclusion: unless you're some kind of hive guru use a directory location and get that to work before trying to get clever with file locations - especially when you see an error message about "not a directory and unable to create it" :) Walk before you run good people. On Thu, Jun 20,

Re: Hive External Table issue

Ramki, I was going through that thread before as Sanjeev said it worked so I was doing some experiment as well. As you I too had the impression that Hive tables are associated with directories and as pointed out I was wrong. Basically the idea of pointing a table to a file as mentioned on that th

Re: Hive External Table issue

Nitin, Can you go through the thread with subject "S3/EMR Hive: Load contents of a single file" on Tue, 26 Mar, 17:11> at http://mail-archives.apache.org/mod_mbox/hive-user/201303.mbox/thread?1 This gives the whole discussion about the topic of table location pointing to a filename vs. direct

unsubscribe

Re: Hive External Table issue

Also see this JIRA https://issues.apache.org/jira/browse/HIVE-951 I think issue you are facing is due to the JIRA On Thu, Jun 20, 2013 at 11:41 PM, Nitin Pawar wrote: > Mark has answered this before > > http://stackoverflow.com/questions/11269203/when-creating-an-external-table-in-hive-can-i-po

Re: Hive External Table issue

1. I was under the impression that you cannot refer the table location to a file. But, it looks like it works. Please see the discussion in the thread http://mail-archives.apache.org/mod_mbox/hive-user/201303.mbox/% 3c556325346ca26341b6f0530e07f90d96017084360...@gbgh-exch-cms.sig.ads%3e 2. It the

Re: Hive External Table issue

Mark has answered this before http://stackoverflow.com/questions/11269203/when-creating-an-external-table-in-hive-can-i-point-the-location-to-specific-fil If this link does not answer your question, do let us know On Thu, Jun 20, 2013 at 11:33 PM, sanjeev sagar wrote: > Two issues: > > 1. I've

Re: Hive External Table issue

Two issues: 1. I've created external tables in hive based on file location before and it work without any issue. It don't have to be a directory. 2. If there are more than one file in the directory, and you create external table based on directory then how the table knows that which file it need

Re: Hive External Table issue

in hive when you create table and use the location to refer hdfs path, that path is supposed to be a directory. If the directory is not existing it will try to create it and if its a file it will throw an error as its not a directory thats the error you are getting that location you referred is a

Re: Hive External Table issue

I did mention in my mail the hdfs file exists in that location. See below In HDFS: file exists hadoop fs -ls /user/flume/events/request_logs/ ar1.vgs.mypoints.com/13-06-13/FlumeData.1371144648033 Found 1 items -rw-r--r-- 3 hdfs supergroup 2242037226 2013-06-13 11:14 /user/flume/events/req

Re: Hive External Table issue

MetaException(message:hdfs:// h1.vgs.mypoints.com:8020/user/flume/events/request_logs/ar1.vgs.mypoints.com/13-06-13/FlumeData.1371144648033 is not a directory or unable to create one) it clearly says its not a directory. Point to the dictory and it will work On Thu, Jun 20, 2013 at 10:52 PM, s

Re: Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

Good eyes Ramki! thanks this "directory" in place of filename appears to be working. The script is getting loaded now using the "Attempt two" i.e. the hivetry/classification_wf.py as the script path. thanks again. stephenb 2013/6/20 Ramki Palle > In the *Attempt two, *are you not supposed

Hive External Table issue

Hello Everyone, I'm running into the following Hive external table issue. hive> CREATE EXTERNAL TABLE access( > host STRING, > identity STRING, > user STRING, > time STRING, > request STRING, > status STRING, > s

Re: Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

In the *Attempt two, *are you not supposed to use "hivetry" as the directory? May be you should try giving the full path " /opt/am/ver/1.0/hive/hivetry/classifier_wf.py" and see if it works. Regards, Ramki. On Thu, Jun 20, 2013 at 9:28 AM, Stephen Boesch wrote: > > Stephen: would you be will

Re: Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

Stephen: would you be willing to share an example of specifying a "directory" as the add "file" target?I have not seen this working I have attempted to use it as follows: *We will access a script within the "hivetry" directory located here:* hive> ! ls -l /opt/am/ver/1.0/hive/hivetry/class

Re: Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

yeah. the archive isn't unpacked on the remote side. I think add archive is mostly used for finding java packages since CLASSPATH will reference the archive (and as such there is no need to expand it.) On Thu, Jun 20, 2013 at 9:00 AM, Stephen Boesch wrote: > thx for the tip on "add " where is

Re: Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

thx for the tip on "add " where is directory. I will try that. 2013/6/20 Stephen Sprague > i personally only know of adding a .jar file via add archive but my > experience there is very limited. i believe if you 'add file' and the file > is a directory it'll recursively take everything undern

Re: Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

i personally only know of adding a .jar file via add archive but my experience there is very limited. i believe if you 'add file' and the file is a directory it'll recursively take everything underneath but i know of nothing that inflates or un tars things on the remote end automatically. i would

Re: Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

To demonstrate this is not necessarily a path issue - but instead an issue with the "archive" is not unpacked - I have created a zip file containing a python script in its root directory. The archive is added to hive and then an attempt is made to invoke the python script within a transform quer

Re: Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

@Stephen: given the 'relative' path for hive is from a local downloads directory on each local tasktracker in the cluster, it was my thought that if the archive were actually being expanded then somedir/somefileinthearchive should work. I will go ahead and test this assumption. In the meantim

Re: Question regarding nested complex data type

you only get three. field separator, array elements separator (aka collection delimiter), and map key/value separator (aka map key delimiter). when you nest deeper then you gotta use the default '^D', '^E' etc for each level. At least that's been my experience which i've found has worked succes

Re: Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

what would be interesting would be to run a little experiment and find out what the default PATH is on your data nodes. How much of a pain would it be to run a little python script to print to stderr the value of the environmental variable $PATH and $PWD (or the shell command 'pwd') ? that's of c

Re: Question regarding nested complex data type

Thanks a lot for your reply, Stephen. To answer your question - I was not aware of the fact that we could use delimiter (in my example, '|') for first level of nesting. I tried now and it worked fine. My next question - Is there any way to provide delimiter in DDL for second level of nesting? Than

Re: Question regarding nested complex data type

its all there in the documentation under "create table" and it seems you got everything right too except one little thing - in your second example there for 'sample data loaded' - instead of '^B' change that to '|' and you should be good. That's the delimiter that separates your two array elements

Re: Hive select shows null after successful data load

hooray! over one hurdle and onto the next one. So something about that one nested array caused the problem. very strange. I wonder if there is a smaller test case to look at as it seems not all arrays break it since i see one for the attribute "values". As to the formatting issue i don't beli

Is there a mechanism similar to hadoop -archive in hive (add archive is not apparently)

We have a few dozen files that need to be made available to all mappers/reducers in the cluster while running hive transformation steps . It seems the "add archive" does not make the entries unarchived and thus available directly on the default file path - and that is what we are looking for. T

Re: "show table" throwing strange error

Thank you for the response ma'am. It didn't help either. Warm Regards, Tariq cloudfront.blogspot.com On Thu, Jun 20, 2013 at 8:43 AM, Sunita Arvind wrote: > Your issue seems familiar. Try logging out of hive session and re-login. > > Sunita > > > On Wed, Jun 19, 2013 at 8:53 PM, Mohammad Tariq

Question regarding nested complex data type

Hi All, I have 2 questions about complex data types in nested composition. 1 >> I did not find a way to provide delimiter information in DDL if one or more column has nested array/struct. In this case, default delimiter has to be used for complex type column. Please let me know if this is a limit