CREATE EXTERNAL TABLE with clause in Hive

I would like to know if it is possible to create an external table in Hive according to a condition (I mean a WHERE) ?

3 Answers
3

You cannot create an external table with Create Table As Select (CTAS) in Hive. But you can create the external table first and insert data into the table from any other table with your filter criteria. Below is an example of creating a partitioned external table stored as ORC and inserting records into that table.

CREATE EXTERNAL TABLE `table_name`(
`column_1` bigint,
`column_2` string)
PARTITIONED BY (
`partition_column_1` string,
`partition_column_2` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
'$dataWarehouseDir/table_name'
TBLPROPERTIES (
'orc.compress'='ZLIB');

set hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE table_name PARTITION(partition_column_1, partition_column_2)
SELECT column_1, column_2, partition_column_1, partition_column_2 FROM Source_Table WHERE column = "your filter criteria here";


Hive cannot create an external table with CTAS.

CTAS has these restrictions:

1.The target table cannot be a partitioned table.

2.The target table cannot be an external table.

3.The target table cannot be a list bucketing table.

Reference:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableAsSelect(CTAS)

Alternately,
You can create an external table and insert into the table with select query.

You can create and external table by separating out the select statement with where clause.First create the external table and then use insert overwrite to external table using select with a where clause.

CREATE EXTERNAL TABLE table_name
STORED AS TEXTFILE
LOCATION '/user/path/table_name';

INSERT OVERWRITE TABLE table_name
SELECT * FROM Source_Table WHERE column="something";


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.