Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Mich Talebzadeh]

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



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  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  > 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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> *
>>
>>
>>
>> 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  wrote:
>>
>>> Will CREATE TABLE sales5 AS SELECT * FROM SALES; not work for you?
>>>
>>> On Thu, Aug 4, 2016 at 5:05 PM, Nagabhushanam Bheemisetty <
>>> nbheemise...@gmail.com> 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
>>> 
>>> Latest news  at Handy
>>> Handy just raised $50m
>>> 
>>>  led
>>> by Fidelity
>>>
>>>
>>
>
> Want to work at Handy? Check out our culture deck and open roles
> 
> Latest news  at Handy
> Handy just raised $50m
> 
>  led
> by Fidelity
>
>

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Marcin Tustin]

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 
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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> 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  wrote:
>
>> Will CREATE TABLE sales5 AS SELECT * FROM SALES; not work for you?
>>
>> On Thu, Aug 4, 2016 at 5:05 PM, Nagabhushanam Bheemisetty <
>> nbheemise...@gmail.com> 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
>> 
>> Latest news  at Handy
>> Handy just raised $50m
>> 
>>  led
>> by Fidelity
>>
>>
>

-- 
Want to work at Handy? Check out our culture deck and open roles 

Latest news  at Handy
Handy just raised $50m 

 led 
by Fidelity

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Mich Talebzadeh]

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



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  wrote:

> Will CREATE TABLE sales5 AS SELECT * FROM SALES; not work for you?
>
> On Thu, Aug 4, 2016 at 5:05 PM, Nagabhushanam Bheemisetty <
> nbheemise...@gmail.com> 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
> 
> Latest news  at Handy
> Handy just raised $50m
> 
>  led
> by Fidelity
>
>

RE: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Markovitz, Dudu]

It won’t help him since ‘*’ represent all columns including the partition 
columns which he wants to exclude.

Dudu

From: Marcin Tustin [mailto:mtus...@handybook.com]
Sent: Sunday, August 07, 2016 3:17 PM
To: user@hive.apache.org
Subject: Re: Crate Non-partitioned table from partitioned table using CREATE 
TABLE .. LIKE

Will CREATE TABLE sales5 AS SELECT * FROM SALES; not work for you?

On Thu, Aug 4, 2016 at 5:05 PM, Nagabhushanam Bheemisetty 
<nbheemise...@gmail.com<mailto:nbheemise...@gmail.com>> 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]

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Marcin Tustin]

Will CREATE TABLE sales5 AS SELECT * FROM SALES; not work for you?

On Thu, Aug 4, 2016 at 5:05 PM, Nagabhushanam Bheemisetty <
nbheemise...@gmail.com> 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 

Latest news  at Handy
Handy just raised $50m 

 led 
by Fidelity

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Nagabhushanam Bheemisetty]

The problem is my destination table won't contain partition related
columns. Option 2 in your example.
On Sat, Aug 6, 2016 at 12:03 PM Markovitz, Dudu 
wrote:

> Hi
>
>
>
> Should your destination table contain the source partitions values?
>
>
>
> e.g.
>
>
>
> Assuming this is the source table:
>
>
>
> *create table src (cust_id int,cust_first_name string,cust_last_name
> string) partitioned by (yr string,mn string,dt string);*
>
>
>
> Should the destination table look like
>
>
>
> *create table dst (cust_id int,cust_first_name string,cust_last_name
> string,yr string,mn string,dt string);*
>
>
>
> or like
>
>
>
> *create table dst (cust_id int,cust_first_name string,cust_last_name
> string);*
>
>
>
>
>
> I think I can achieve the first option.
>
>
>
> Dudu
>
>
>
>
>
>
>
> *From:* Nagabhushanam Bheemisetty [mailto:nbheemise...@gmail.com]
> *Sent:* Friday, August 05, 2016 12:05 AM
> *To:* user@hive.apache.org
> *Subject:* Crate Non-partitioned table from partitioned table using
> CREATE TABLE .. LIKE
>
>
>
> 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.
>

RE: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Markovitz, Dudu]

Hi

Should your destination table contain the source partitions values?

e.g.

Assuming this is the source table:

create table src (cust_id int,cust_first_name string,cust_last_name string) 
partitioned by (yr string,mn string,dt string);

Should the destination table look like

create table dst (cust_id int,cust_first_name string,cust_last_name string,yr 
string,mn string,dt string);

or like

create table dst (cust_id int,cust_first_name string,cust_last_name string);


I think I can achieve the first option.

Dudu



From: Nagabhushanam Bheemisetty [mailto:nbheemise...@gmail.com]
Sent: Friday, August 05, 2016 12:05 AM
To: user@hive.apache.org
Subject: Crate Non-partitioned table from partitioned table using CREATE TABLE 
.. LIKE

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.

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Mich Talebzadeh]

Ok

Does it matter whether the table you create is accessible to Hive?

You can read your hive table in Spark assuming you know the table name

// Read hive table. This one is partitioned
scala> val s = HiveContext.table("oraclehadoop.sales")
s: org.apache.spark.sql.DataFrame = [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), year: int, month: int]

//Create an external table in parquet format from that table and save it
without the partitioned columns ( year: int, month: int)  in an external
directory
scala> val s2 = s.select('prod_id, 'cust_id, 'time_id, 'channel_id,
'promo_id, 'quantity_sold, 'amount_sold)
s2: org.apache.spark.sql.DataFrame = [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)]
scala> s2.write.mode("overwrite").parquet("/data/stg/newtable/sales5")

HTH


Dr Mich Talebzadeh



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*



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 4 August 2016 at 23:21, Nagabhushanam Bheemisetty  wrote:

> Yes you are correct that is just meta copy and I need only that but
> without partition:(
>
> On Thu, Aug 4, 2016 at 5:15 PM Mich Talebzadeh 
> wrote:
>
>> yes but that essentially copies the metadata and leaves the partition
>> there with no data. it is just an image copy. won't help this case
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * 
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> *
>>
>>
>>
>> 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 4 August 2016 at 23:08, Nagabhushanam Bheemisetty <
>> nbheemise...@gmail.com> wrote:
>>
>>> Well you can create using like.
>>>
>>>
>>> CREATE EXTERNAL TABLE sales5 LIKE sales;
>>>
>>>
>>> On Thu, Aug 4, 2016 at 5:06 PM Mich Talebzadeh <
>>> mich.talebza...@gmail.com> wrote:
>>>
 Which process creates the master table in Hive as an external table?
 There must be a process that creates the master table as external table?
 Hive knows about the schema of that table. It is in Hive metastore.

 You cannot create an external table with CREATE EXTERNAL TABLE AS ...

 hive> CREATE EXTERNAL TABLE sales5 AS SELECT * FROM SALES;
 FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot
 create external table

 Dr Mich Talebzadeh



 LinkedIn * 
 https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
 *



 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 4 August 2016 at 22:56, Nagabhushanam Bheemisetty <
 nbheemise...@gmail.com> wrote:

> I only get the table names that I need to ingest. So I don't know the
> master table schema upfront.
>
> Yes the new table based on master table which is partitioned but new
> table should not be partitioned and should not have partition column.
>
> On Thu, Aug 4, 2016 at 4:54 PM Mich Talebzadeh <
> mich.talebza...@gmail.com> wrote:
>
>> Do you know the existing table schema? The new table schema will be
>> based on that table without partitioning?
>>
>>
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * 
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> *
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>> *Disclaimer:* 

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Nagabhushanam Bheemisetty]

Yes you are correct that is just meta copy and I need only that but without
partition:(

On Thu, Aug 4, 2016 at 5:15 PM Mich Talebzadeh 
wrote:

> yes but that essentially copies the metadata and leaves the partition
> there with no data. it is just an image copy. won't help this case
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> 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 4 August 2016 at 23:08, Nagabhushanam Bheemisetty <
> nbheemise...@gmail.com> wrote:
>
>> Well you can create using like.
>>
>>
>> CREATE EXTERNAL TABLE sales5 LIKE sales;
>>
>>
>> On Thu, Aug 4, 2016 at 5:06 PM Mich Talebzadeh 
>> wrote:
>>
>>> Which process creates the master table in Hive as an external table?
>>> There must be a process that creates the master table as external table?
>>> Hive knows about the schema of that table. It is in Hive metastore.
>>>
>>> You cannot create an external table with CREATE EXTERNAL TABLE AS ...
>>>
>>> hive> CREATE EXTERNAL TABLE sales5 AS SELECT * FROM SALES;
>>> FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot
>>> create external table
>>>
>>> Dr Mich Talebzadeh
>>>
>>>
>>>
>>> LinkedIn * 
>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> *
>>>
>>>
>>>
>>> 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 4 August 2016 at 22:56, Nagabhushanam Bheemisetty <
>>> nbheemise...@gmail.com> wrote:
>>>
 I only get the table names that I need to ingest. So I don't know the
 master table schema upfront.

 Yes the new table based on master table which is partitioned but new
 table should not be partitioned and should not have partition column.

 On Thu, Aug 4, 2016 at 4:54 PM Mich Talebzadeh <
 mich.talebza...@gmail.com> wrote:

> Do you know the existing table schema? The new table schema will be
> based on that table without partitioning?
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> 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 4 August 2016 at 22:05, Nagabhushanam Bheemisetty <
> nbheemise...@gmail.com> 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.
>>
>
>
>>>
>

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Mich Talebzadeh]

yes but that essentially copies the metadata and leaves the partition there
with no data. it is just an image copy. won't help this case

Dr Mich Talebzadeh



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*



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 4 August 2016 at 23:08, Nagabhushanam Bheemisetty  wrote:

> Well you can create using like.
>
>
> CREATE EXTERNAL TABLE sales5 LIKE sales;
>
>
> On Thu, Aug 4, 2016 at 5:06 PM Mich Talebzadeh 
> wrote:
>
>> Which process creates the master table in Hive as an external table?
>> There must be a process that creates the master table as external table?
>> Hive knows about the schema of that table. It is in Hive metastore.
>>
>> You cannot create an external table with CREATE EXTERNAL TABLE AS ...
>>
>> hive> CREATE EXTERNAL TABLE sales5 AS SELECT * FROM SALES;
>> FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot
>> create external table
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * 
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> *
>>
>>
>>
>> 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 4 August 2016 at 22:56, Nagabhushanam Bheemisetty <
>> nbheemise...@gmail.com> wrote:
>>
>>> I only get the table names that I need to ingest. So I don't know the
>>> master table schema upfront.
>>>
>>> Yes the new table based on master table which is partitioned but new
>>> table should not be partitioned and should not have partition column.
>>>
>>> On Thu, Aug 4, 2016 at 4:54 PM Mich Talebzadeh <
>>> mich.talebza...@gmail.com> wrote:
>>>
 Do you know the existing table schema? The new table schema will be
 based on that table without partitioning?



 Dr Mich Talebzadeh



 LinkedIn * 
 https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
 *



 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 4 August 2016 at 22:05, Nagabhushanam Bheemisetty <
 nbheemise...@gmail.com> 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.
>


>>

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Nagabhushanam Bheemisetty]

Well you can create using like.


CREATE EXTERNAL TABLE sales5 LIKE sales;


On Thu, Aug 4, 2016 at 5:06 PM Mich Talebzadeh 
wrote:

> Which process creates the master table in Hive as an external table? There
> must be a process that creates the master table as external table?  Hive
> knows about the schema of that table. It is in Hive metastore.
>
> You cannot create an external table with CREATE EXTERNAL TABLE AS ...
>
> hive> CREATE EXTERNAL TABLE sales5 AS SELECT * FROM SALES;
> FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot
> create external table
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> 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 4 August 2016 at 22:56, Nagabhushanam Bheemisetty <
> nbheemise...@gmail.com> wrote:
>
>> I only get the table names that I need to ingest. So I don't know the
>> master table schema upfront.
>>
>> Yes the new table based on master table which is partitioned but new
>> table should not be partitioned and should not have partition column.
>>
>> On Thu, Aug 4, 2016 at 4:54 PM Mich Talebzadeh 
>> wrote:
>>
>>> Do you know the existing table schema? The new table schema will be
>>> based on that table without partitioning?
>>>
>>>
>>>
>>> Dr Mich Talebzadeh
>>>
>>>
>>>
>>> LinkedIn * 
>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> *
>>>
>>>
>>>
>>> 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 4 August 2016 at 22:05, Nagabhushanam Bheemisetty <
>>> nbheemise...@gmail.com> 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.

>>>
>>>
>

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Mich Talebzadeh]

Which process creates the master table in Hive as an external table? There
must be a process that creates the master table as external table?  Hive
knows about the schema of that table. It is in Hive metastore.

You cannot create an external table with CREATE EXTERNAL TABLE AS ...

hive> CREATE EXTERNAL TABLE sales5 AS SELECT * FROM SALES;
FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot
create external table

Dr Mich Talebzadeh



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*



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 4 August 2016 at 22:56, Nagabhushanam Bheemisetty  wrote:

> I only get the table names that I need to ingest. So I don't know the
> master table schema upfront.
>
> Yes the new table based on master table which is partitioned but new table
> should not be partitioned and should not have partition column.
>
> On Thu, Aug 4, 2016 at 4:54 PM Mich Talebzadeh 
> wrote:
>
>> Do you know the existing table schema? The new table schema will be based
>> on that table without partitioning?
>>
>>
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * 
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> *
>>
>>
>>
>> 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 4 August 2016 at 22:05, Nagabhushanam Bheemisetty <
>> nbheemise...@gmail.com> 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.
>>>
>>
>>

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Nagabhushanam Bheemisetty]

I only get the table names that I need to ingest. So I don't know the
master table schema upfront.

Yes the new table based on master table which is partitioned but new table
should not be partitioned and should not have partition column.

On Thu, Aug 4, 2016 at 4:54 PM Mich Talebzadeh 
wrote:

> Do you know the existing table schema? The new table schema will be based
> on that table without partitioning?
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> 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 4 August 2016 at 22:05, Nagabhushanam Bheemisetty <
> nbheemise...@gmail.com> 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.
>>
>
>

Re: Crate Non-partitioned table from partitioned table using CREATE TABLE .. LIKE

[Mich Talebzadeh]

Do you know the existing table schema? The new table schema will be based
on that table without partitioning?



Dr Mich Talebzadeh



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*



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 4 August 2016 at 22:05, Nagabhushanam Bheemisetty  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.
>