The managed/external table is non-issue since you can create the table as
managed and after that alter it to external.
ALTER TABLE t SET TBLPROPERTIES('EXTERNAL'='TRUE')
Dudu
From: Mich Talebzadeh [mailto:[email protected]]
Sent: Sunday, August 07, 2016 5:02 PM
To: Marcin Tustin <[email protected]>
Cc: user <[email protected]>
Subject: Re: Crate Non-partitioned table from partitioned table using CREATE
TABLE .. LIKE
The source table is a partitioned external table and the target table is also
external but not partitioned.
A straight create table as .. from external table won't work
hive> CREATE EXTERNAL TABLE sales5 AS SELECT * FROM SALES;
FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot create
external table
Does not work for external tables
The other option is to use LIKE
CREATE EXTERNAL TABLE sales6 LIKE sales;
That will copy the schema image with existing partitions to new table with no
data
hive> show create table sales6;
OK
CREATE EXTERNAL TABLE `sales6`(
`prod_id` bigint,
`cust_id` bigint,
`time_id` timestamp,
`channel_id` bigint,
`promo_id` bigint,
`quantity_sold` decimal(10,0),
`amount_sold` decimal(10,0))
PARTITIONED BY (
`year` int,
`month` int)
-----
Which is not that useful
HTH
Dr Mich Talebzadeh
LinkedIn
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
http://talebzadehmich.wordpress.com
Disclaimer: Use it at your own risk. Any and all responsibility for any loss,
damage or destruction of data or any other property which may arise from
relying on this email's technical content is explicitly disclaimed. The author
will in no case be liable for any monetary damages arising from such loss,
damage or destruction.
On 7 August 2016 at 14:39, Marcin Tustin
<[email protected]<mailto:[email protected]>> wrote:
Yes, but a create table unpartitioned as select * from partitioned will create
an unpartitioned table with all the data in partitioned table. It won't lose
the partition column, but nowhere do I see a need for that column to be removed.
On Sun, Aug 7, 2016 at 9:25 AM, Mich Talebzadeh
<[email protected]<mailto:[email protected]>> wrote:
Hi Marcin,
The thread owner question was
"Hi I've a scenario where I need to create a table from partitioned table but
my destination table should not be partitioned. I won't be knowing the schema
so I cannot create manually the destination table. By the way both tables are
external tables."
This can be easily achieved through Spark by reading the Hive external table
(assuming that the thread owner knows its name and the Hive database name :))
into a DF
DF will display all the column names and a filter on it can get rid of the
partition columns.
New table can be created without those two columns and of course will not be
partitioned.
HTH
Dr Mich Talebzadeh
LinkedIn
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
http://talebzadehmich.wordpress.com
Disclaimer: Use it at your own risk. Any and all responsibility for any loss,
damage or destruction of data or any other property which may arise from
relying on this email's technical content is explicitly disclaimed. The author
will in no case be liable for any monetary damages arising from such loss,
damage or destruction.
On 7 August 2016 at 13:17, Marcin Tustin
<[email protected]<mailto:[email protected]>> wrote:
Will CREATE TABLE sales5 AS SELECT * FROM SALES; not work for you?
On Thu, Aug 4, 2016 at 5:05 PM, Nagabhushanam Bheemisetty
<[email protected]<mailto:[email protected]>> wrote:
Hi I've a scenario where I need to create a table from partitioned table but my
destination table should not be partitioned. I won't be knowing the schema so I
cannot create manually the destination table. By the way both tables are
external tables.
Want to work at Handy? Check out our culture deck and open
roles<http://www.handy.com/careers>
Latest news<http://www.handy.com/press> at Handy
Handy just raised
$50m<http://venturebeat.com/2015/11/02/on-demand-home-service-handy-raises-50m-in-round-led-by-fidelity/>
led by Fidelity
[http://marketing-email-assets.handybook.com/smalllogo.png]
Want to work at Handy? Check out our culture deck and open
roles<http://www.handy.com/careers>
Latest news<http://www.handy.com/press> at Handy
Handy just raised
$50m<http://venturebeat.com/2015/11/02/on-demand-home-service-handy-raises-50m-in-round-led-by-fidelity/>
led by Fidelity
[http://marketing-email-assets.handybook.com/smalllogo.png]
