Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 79223181D7 for ; Tue, 11 Aug 2015 05:39:55 +0000 (UTC) Received: (qmail 71564 invoked by uid 500); 11 Aug 2015 05:39:53 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 71483 invoked by uid 500); 11 Aug 2015 05:39:53 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 71473 invoked by uid 99); 11 Aug 2015 05:39:53 -0000 Received: from Unknown (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 11 Aug 2015 05:39:53 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 410FB1839B4 for ; Tue, 11 Aug 2015 05:39:53 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 3.9 X-Spam-Level: *** X-Spam-Status: No, score=3.9 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, FREEMAIL_REPLY=1, HTML_MESSAGE=3, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id ERCzJySagQ6y for ; Tue, 11 Aug 2015 05:39:43 +0000 (UTC) Received: from mail-ig0-f172.google.com (mail-ig0-f172.google.com [209.85.213.172]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTPS id 5AB21205B3 for ; Tue, 11 Aug 2015 05:39:43 +0000 (UTC) Received: by igbpg9 with SMTP id pg9so84338188igb.0 for ; Mon, 10 Aug 2015 22:39:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=BZ6LLnxyIAXu1miavBEEs2SA7slLYjpnB6z3JPb8jm0=; b=YVfEMFaVV0Rp7eOxsP20U7f0MbJd9sg6rypkFcGNzz629kLHjE5wuQ82YPX7IMomqJ yWKLMvWhn622ond6rNrR5X7iL4xsQo65Yy8I/PLvc6EaY5HSRlawBesVfaCsDfWFEw/D ShQsk2yK7zPBqEK9Nxg7DyhFn9JCjs9FSVPncRU4kqb3orzFhmDhelySiTqDYRhsxsTl d5WXQqROIqH8pdsBq7Qaa3p+iIFzyhIzLA8xWh1BHwO4t1jWKbQkWrdV+5d5IUxLENdR 8fM/SUQ7GQhoxKL8c3gq780pKMOhHwQ+N3bK87k0J1suD/m+lwVOFUrKwCacURAsxSDw EN8Q== MIME-Version: 1.0 X-Received: by 10.50.73.98 with SMTP id k2mr7022538igv.96.1439271582802; Mon, 10 Aug 2015 22:39:42 -0700 (PDT) Received: by 10.64.10.201 with HTTP; Mon, 10 Aug 2015 22:39:42 -0700 (PDT) In-Reply-To: References: <737D5E42ADDB2C4E96DAF6A8900D75FE71FE8135@szxema508-mbs.china.huawei.com> Date: Tue, 11 Aug 2015 11:09:42 +0530 Message-ID: Subject: Re: How to create new table like existing with an extra column in single query. From: venkatesh b To: "user@hive.apache.org" Content-Type: multipart/alternative; boundary=089e01184e1a6da8c0051d0287b2 --089e01184e1a6da8c0051d0287b2 Content-Type: text/plain; charset=UTF-8 Thanks Adam. As mentioned we have a need that in a single query it should be done, since ours is an automated query generation and no manual interactions are done. We need a query like *Create table if not exists newTable like oldTable, newColumn datatype* *Without copying data from oldTable. * On Monday, August 10, 2015, LaStrange, Adam wrote: > How about: > > > > create table XXX like YYY; > > alter table XXX add columns (new_column int); > > > > *From:* venkatesh b [mailto:venkateshmailinglist@gmail.com > ] > *Sent:* Monday, August 10, 2015 9:28 AM > *To:* Wangwenli > *Cc:* user > *Subject:* Re: How to create new table like existing with an extra column > in single query. > > > > Thanks Wenli for quick reply. > > > > Yes, we have a requirement exactly the same which you mentioned. > > > > I have tried the query which you provided but got an error saying that > > "Invalid column reference for newColumn possible columns are ......" > > > > > > > > Thanks > > Venkatesh > > On Monday, August 10, 2015, Wangwenli > wrote: > > something like this? > > create table newTable as select oldTable.*, newCol1,newCol2 from oldTable; > > > ------------------------------ > > wenli > > Regards > > > > *From:* venkatesh b > > *Date:* 2015-08-10 20:54 > > *To:* user > > *Subject:* How to create new table like existing with an extra column in > single query. > > Hello, > > > > In hive we got a need that we need to create a new table like the old > table with an extra column at run time. This must be done in a single > query. No second query with alter table statement. > > > > Query like: > > > > Create table new_table like old_table , new_column datatype; > > > > Please help me out, searched in many forums but not found the solution. > > Please inform me if any other information is needed. > > > > Thanks & Regards > > Venkatesh > > > ------------------------------ > > IMPORTANT: The information contained in this email and/or its attachments > is confidential. If you are not the intended recipient, please notify the > sender immediately by reply and immediately delete this message and all its > attachments. Any review, use, reproduction, disclosure or dissemination of > this message or any attachment by an unintended recipient is strictly > prohibited. Neither this message nor any attachment is intended as or > should be construed as an offer, solicitation or recommendation to buy or > sell any security or other financial instrument. Neither the sender, his or > her employer nor any of their respective affiliates makes any warranties as > to the completeness or accuracy of any of the information contained herein > or that this message or any of its attachments is free of viruses. > --089e01184e1a6da8c0051d0287b2 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks Adam.

As mentioned we have a need that in a singl= e query it should be done, since ours is an automated query generation and = no manual interactions are done.=C2=A0

We need a q= uery like=C2=A0

Create table i= f not exists=C2=A0newTable like oldTable, newColumn datatype
<= div>
Without copying data from oldTable.=C2=A0<= /div>


On Monday, August 10, 2015, LaStrange, Adam <Adam.LaStrange@sig.com> wrote:

How about:<= /span>

=C2=A0

create table XXX like YYY= ;

alter table XXX add colum= ns (new_column int);

=C2=A0

From: venkates= h b [mailto:venkateshmailinglist@gmail.co= m]
Sent: Monday, August 10, 2015 9:28 AM
To: Wangwenli
Cc: user
Subject: Re: How to create new table like existing with an extra col= umn in single query.

=C2=A0

Thanks Wenli for quick reply.

=C2=A0

Yes, we have a requirement=C2=A0exactly the same whi= ch you mentioned.

=C2=A0

I have tried the query which you provided but got an= error saying that

"Invalid column reference for newColumn possibl= e columns are ......"

=C2=A0

=C2=A0

=C2=A0

Thanks

Venkatesh

On Monday, August 10, 2015, Wangwenli <wangwenli= @huawei.com> wrote:

something like this?

create table newTable as select oldTable.*, newCol1,= newCol2 from oldTable;

=C2=A0

wenli

Regards

=C2=A0

From:=C2=A0venkatesh b

Date:=C2=A02015-08-10=C2=A020:54

To:=C2=A0user

Subject:=C2=A0How to create new table = like existing with an extra column in single query.

Hello,

=C2=A0

In hive we got a need that we need to create a new t= able like the old table with an extra column at run time. This must be done= in a single query. No second query with alter table statement.

=C2=A0

Query like:=C2=A0

=C2=A0

Create table new_table like old_table , new_column d= atatype;=C2=A0

=C2=A0

Please help me out, searched in many forums but not = found the solution.

Please inform me if any other information is needed.=

=C2=A0

Thanks & Regards

Venkatesh



IMPORTANT: The information contained in this email and/or its attachments i= s confidential. If you are not the intended recipient, please notify the se= nder immediately by reply and immediately delete this message and all its a= ttachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an uninte= nded recipient is strictly prohibited. Neither this message nor any attachm= ent is intended as or should be construed as an offer, solicitation or reco= mmendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor= any of their respective affiliates makes any warranties as to the complete= ness or accuracy of any of the information contained herein or that this me= ssage or any of its attachments is free of viruses.
--089e01184e1a6da8c0051d0287b2--