FAQ
Hi,

When using the INSERT OVERWRITE TABLE ... FROM SELECT syntax, you can
specify partition information for the inserts. However, these partition
values can only be literals. This seems too restrictive. For example, I have
a query like this:
SELECT month, day, count(1) FROM tab1 GROUP BY month, day;

When I insert into tab2 from the above select statement, I'd like the month
and day values to be used as partitions for the insert. Is there any way of
doing this? May be I'm trying to do this the wrong way?

Thanks,
Vijay

Search Discussions

  • Ashish Thusoo at Sep 5, 2009 at 8:23 am
    yes this is restrictive. There is a JIRA open for this but we have not fixed this yet... The only possible work around for this particular case is to use the multi group by format and list out all the 12 partitions in your case...

    FROM tab1
    INSERT OVERWRITE TABLE tab2 PARTITION(month=1) SELECT day, count(1) WHERE month=1 GROUP BY day
    INSERT OVERWRITE TABLE tab2 PARTITION(month=2) SELECT day, count(1) WHERE month=2 GROUP BY day
    ....
    INSERT OVERWRITE TABLE tab2 PARTITION(month=12) SELECT day, count(1) WHERE month=12 GROUP BY day;

    Ashish


    ________________________________________
    From: Vijay [techvd@gmail.com]
    Sent: Friday, September 04, 2009 9:09 PM
    To: hive-user@hadoop.apache.org
    Subject: Inserting data into Hive Tables from queries

    Hi,

    When using the INSERT OVERWRITE TABLE ... FROM SELECT syntax, you can specify partition information for the inserts. However, these partition values can only be literals. This seems too restrictive. For example, I have a query like this:
    SELECT month, day, count(1) FROM tab1 GROUP BY month, day;

    When I insert into tab2 from the above select statement, I'd like the month and day values to be used as partitions for the insert. Is there any way of doing this? May be I'm trying to do this the wrong way?

    Thanks,
    Vijay

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categorieshive, hadoop
postedSep 5, '09 at 4:09a
activeSep 5, '09 at 8:23a
posts2
users2
websitehive.apache.org

2 users in discussion

Ashish Thusoo: 1 post Vijay: 1 post

People

Translate

site design / logo © 2022 Grokbase