I want to change an existing table, that contains text format, into orc format. I was able to do it by: (1) creating a table in orc format manually having the partitions and then, (2) using the INSERT OVERWRITE statement to populate the table.
I am trying to use CTAS (Create Table... AS Select...) statement for this. Is there any way I can include the dynamic partitioning with CTAS statement? So, if my text data set has multiple partitions (for example: year and month), can I point this in CTAS statement directly?
The format might be something like this:
CREATE TABLE TEST_TABLE
STORED AS ORC
WITH PARTITION(year, month)
LOCATION '/<my_location>'
tblproperties ("orc.compress"="SNAPPY")
AS SELECT * FROM <existing_table>;
Any idea please?
Note: The reason I'm interested for CTAS statement is as: using this statement, I don't really need to point out each and every columns name. But if I create an orc based table and then populate it manually, I had to indicate all the columns while creating the table. This is okay; but, not a good idea if my existing table contains a lot of columns.
Answer
Not supported
hive> create table t partitioned by (p int) as select 1 as i;
FAILED: SemanticException [Error 10068]:
CREATE-TABLE-AS-SELECT does not support partitioning in the target table